List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:com.compassplus.gui.MainForm.java
private boolean analyzeCell(Workbook wb, Sheet sheet, Row row, Cell cell, ScriptEngine engine, Bindings bindings) {//from w ww . ja v a 2 s . co m try { String formula = cell.getCellFormula(); cell.setCellFormula(formula); } catch (Exception e) { } try { String expr = cell.getStringCellValue(); short type = 0; if (expr.contains("__REMOVE")) { type = __REMOVE; } else if (expr.contains("__INSERT")) { type = __INSERT; } if (type > 0) { try { expr = expr.substring(expr.indexOf("(") + 1); expr = expr.substring(0, expr.lastIndexOf(")")); expr = expr.replaceAll("\\s", ""); if (type == __REMOVE) { cell.setCellValue(""); Object val = null; val = engine.eval(expr, bindings); if (val instanceof Boolean) { return (Boolean) val; } else { throw new Exception("result is not boolean"); } } else if (type == __INSERT) { cell.setCellValue(""); Object val = null; val = engine.eval(expr, bindings); if (!(val instanceof String && ((String) val).equals(""))) { CellStyle cs = wb.createCellStyle(); CellStyle csT = cell.getCellStyle(); cs.cloneStyleFrom(csT); String format = (getCurrentProposalForm().getProposal().getCurrency() .getSymbol() != null ? "\"" + getCurrentProposalForm().getProposal().getCurrency().getSymbol() + "\" " : "") + "#,##0" + (getCurrentProposalForm().getProposal().getCurrency().getSymbol() == null ? " \"" + getCurrentProposalForm().getProposal().getCurrency().getName() + "\"" : ""); if (expr.contains("$PRICE") || expr.contains("$MAN-DAY-RATE")) { cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(format)); } else if (expr.contains("$SUPPORT_RATE")) { cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("0%;-0%")); } else if (expr.contains("$VALUE")) { cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("#,##0")); } cell.setCellStyle(cs); } if (val instanceof Boolean) { cell.setCellValue((Boolean) val); } else if (val instanceof Number) { cell.setCellValue(((Number) val).doubleValue()); } else if (val instanceof String) { cell.setCellValue((String) val); } else { throw new Exception("result type is unknown"); } } } catch (Exception e) { log.error("Bad" + (type == __REMOVE ? " __REMOVE" : (type == __INSERT ? " __INSERT" : "")) + " expression: " + expr); cell.setCellValue(""); } } } catch (Exception e) { } return false; }
From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java
License:Apache License
/** Inserts a value to a Cell based on a value type (class). */ static void populateCellValue(final Cell cell, final ICellValue value) { if (cell == null) { return;// ww w . jav a2 s.c o m } int cellType = resolveCellType(value); cell.setCellType(cellType); switch (cellType) { case CELL_TYPE_BLANK: { break; } case CELL_TYPE_BOOLEAN: { cell.setCellValue((Boolean) value.get()); break; } case CELL_TYPE_NUMERIC: { cell.setCellValue((Double) value.get()); break; } case CELL_TYPE_FORMULA: { try { cell.setCellFormula(((String) value.get()).substring(1)); break; } catch (FormulaParseNameException e) { log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage()); cell.setCellFormula(ErrorEval.NAME_INVALID.getErrorString()); break; } catch (FormulaParseNAException e) { log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage()); cell.setCellFormula(ErrorEval.NA.getErrorString()); break; } } case CELL_TYPE_ERROR: { cell.setCellErrorValue(FormulaError.forString((String) value.get()).getCode()); break; } case CELL_TYPE_STRING: { cell.setCellValue((String) value.get()); break; } default: { throw new CalculationEngineException(String.format("Type of value %s is not supported: %s", value, value.getClass().getSimpleName())); } } }
From source file:com.dituiba.excel.ExcelUtility.java
License:Apache License
public static void copyCell(Cell srcCell, Cell distCell) { distCell.setCellStyle(srcCell.getCellStyle()); if (srcCell.getCellComment() != null) { distCell.setCellComment(srcCell.getCellComment()); }//from w w w .j av a2 s .c o m int srcCellType = srcCell.getCellType(); distCell.setCellType(srcCellType); if (srcCellType == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(srcCell)) { distCell.setCellValue(srcCell.getDateCellValue()); } else { distCell.setCellValue(srcCell.getNumericCellValue()); } } else if (srcCellType == Cell.CELL_TYPE_STRING) { distCell.setCellValue(srcCell.getRichStringCellValue()); } else if (srcCellType == Cell.CELL_TYPE_BLANK) { //nothing } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) { distCell.setCellValue(srcCell.getBooleanCellValue()); } else if (srcCellType == Cell.CELL_TYPE_ERROR) { distCell.setCellErrorValue(srcCell.getErrorCellValue()); } else if (srcCellType == Cell.CELL_TYPE_FORMULA) { distCell.setCellFormula(srcCell.getCellFormula()); } else { //nothing } }
From source file:com.dufeng.core.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;// ww w . j av a2 s . c o m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); //calendar.setTime(fmt.parse("9-Jul")); calendar.setTime(new Date()); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { //calendar.setTime(fmt.parse(data[i][j])); calendar.setTime(new Date()); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "E:/businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.evidon.areweprivateyet.Aggregator.java
License:Open Source License
private void createContent(Workbook wb, Sheet s, String map) { Map<String, String> out = new HashMap<String, String>(); int rownum = 2; int cellnum = 0; // create a merged list of domains. domains.clear();//w w w. j av a 2 s .co m for (String database : results.keySet()) { if (database.equals("baseline")) { Analyzer ra = results.get(database); Map<String, Integer> mapToUse = this.getMap(map, ra); for (String domain : mapToUse.keySet()) { if ((!domains.contains(domain)) && !exclusions.contains(domain)) { domains.add(domain); out.put(domain, ""); } } } } CellStyle numberStyle = wb.createCellStyle(); numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number")); s.setColumnWidth(0, 5000); for (String domain : domains) { cellnum = 0; Row r = s.createRow(rownum); Cell c = r.createCell(cellnum); c.setCellValue(domain); cellnum++; for (String database : results.keySet()) { Analyzer ra = results.get(database); Map<String, Integer> mapToUse = this.getMap(map, ra); c = r.createCell(cellnum); try { if (mapToUse.containsKey(domain)) { c.setCellValue(mapToUse.get(domain)); } else { c.setCellValue(0); } } catch (Exception e) { c.setCellValue(0); } c.setCellStyle(numberStyle); cellnum++; } rownum++; } // Totals. rownum++; cellnum = 1; Row r = s.createRow(rownum); Cell c = r.createCell(0); c.setCellValue("Totals:"); for (int i = 0; i < results.keySet().size(); i++) { c = r.createCell(cellnum); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("SUM(" + getCellLetter(i) + "3:" + getCellLetter(i) + (domains.size() + 2) + ")"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); if (!totals.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) { Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } else { Map<String, String> contents = totals.get(s.getRow(1).getCell(i + 1).getStringCellValue()); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); totals.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } cellnum++; } // Delta/Reduction rownum++; cellnum = 1; r = s.createRow(rownum); c = r.createCell(0); c.setCellValue("Tracking Decrease:"); for (int i = 0; i < results.keySet().size(); i++) { c = r.createCell(cellnum); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("ROUND((100-(" + getCellLetter(i) + (rownum) + "*100/B" + (rownum) + ")),0)"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); if (!decrease.containsKey(s.getRow(1).getCell(i + 1).getStringCellValue())) { Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } else { Map<String, String> contents = decrease.get(s.getRow(1).getCell(i + 1).getStringCellValue()); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(s.getRow(1).getCell(i + 1).getStringCellValue(), contents); } cellnum++; } }
From source file:com.evidon.areweprivateyet.Aggregator.java
License:Open Source License
public void createSpreadSheet() throws Exception { int row = 2, cell = 0, sheet = 0; FileOutputStream file = new FileOutputStream(path + "analysis.xls"); Workbook wb = new HSSFWorkbook(); // content: total content length sheet. Sheet s = wb.createSheet();//from w ww . j a va 2 s .c o m wb.setSheetName(sheet, "Content Length"); this.createHeader(wb, s, "Total Content Length in MB", 0); Row r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); c.setCellValue(results.get(database).totalContentLength / 1024 / 1024); cell++; } row++; cell = 0; r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); if (database.equals("baseline")) { c.setCellValue("Decrease:"); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), "0"); decrease.put(database, contents); } else { c = r.createCell(cell); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(database, contents); } cell++; } sheet++; // When content is created, baseline is used as a base for every entry. For example, // if baseline contained doubleclick.com, this will be output and each other analyzer's // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick. // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never // shown in the spreadsheet or any other results. // so this means if we have tracker/whatever URLs in a non-baseline profile // and these URLs are NOT in the baseline profile, // we wouldn't see those trackers/whatever in the final comparison. // content: HTTP Requests s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Requests"); this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1); this.createContent(wb, s, "requestCountPerDomain"); sheet++; // content: HTTP Set-Cookie Responses s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Set-Cookie Responses"); this.createHeader(wb, s, "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1); this.createContent(wb, s, "setCookieResponses"); sheet++; // content: Cookie Added - Cookie Deleted s = wb.createSheet(); wb.setSheetName(sheet, "Cookies Added-Deleted"); this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1); this.createContent(wb, s, "cookieTotals"); sheet++; // content: Local Storage counts per domain s = wb.createSheet(); wb.setSheetName(sheet, "Local Storage"); this.createHeader(wb, s, "Local Storage counts per domain", 1); this.createContent(wb, s, "localStorageContents"); sheet++; // content: Pretty Chart s = wb.createSheet(); wb.setSheetName(sheet, "Overall"); int rownum = 0, cellnum = 0; // Header r = s.createRow(rownum); Cell c = r.createCell(0); s.setColumnWidth(0, 8000); c.setCellValue( "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)"); rownum++; r = s.createRow(rownum); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellValue(database); CellStyle cs = wb.createCellStyle(); Font f = wb.createFont(); f.setBoldweight(Font.BOLDWEIGHT_BOLD); cs.setFont(f); c.setCellStyle(cs); cellnum++; } CellStyle numberStyle = wb.createCellStyle(); numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number")); // Content for (String type : decrease.get("baseline").keySet()) { cellnum = 0; rownum++; r = s.createRow(rownum); c = r.createCell(cellnum); c.setCellValue(type); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellStyle(numberStyle); double decreaseValue = Double.parseDouble(decrease.get(database).get(type)); if (decreaseValue < 0) decreaseValue = 0; c.setCellValue(decreaseValue); cellnum++; } } /* for (String database : decrease.keySet()) { for (String type : decrease.get(database).keySet()) { System.out.println(database + "|" + type + "|" + decrease.get(database).get(type)); } } */ wb.write(file); file.close(); }
From source file:com.firstonesoft.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from www.jav a 2 s . c om*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Resumen de Horas"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "D://timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.github.camaral.sheeco.exceptions.OriginalCellValue.java
License:Apache License
public void setCellValue(final Cell cell) { switch (type) { case DATE:// w w w.j av a2 s . c om cell.setCellValue((Date) value); break; case NUMERIC: cell.setCellValue(((Number) value).doubleValue()); break; case BOOLEAN: cell.setCellValue((Boolean) value); break; case STRING: cell.setCellValue(String.valueOf(value)); break; case ERROR: cell.setCellErrorValue((Byte) value); break; case FORMULA: cell.setCellFormula((String) value); break; default: } }
From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiWriter.java
License:Open Source License
/** * @return//from w w w .ja va 2 s .c o m */ @Override public String setFormula(final int r, final int c, final String formula) { final Cell cell = this.getOrCreatePOICell(r, c); try { cell.setCellFormula(formula); } catch (final FormulaParseException e) { throw new IllegalArgumentException(e); } return formula; }
From source file:com.github.luischavez.lat.excel.Excel.java
License:Open Source License
protected void createFormula(int index, Row row, CellStyle style, String formula) { Cell cell = row.createCell(index); if (null != style) { cell.setCellStyle(style);//from w w w . jav a 2 s . c o m } cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula(formula); }