List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateAll
void evaluateAll();
From source file:cherry.goods.excel.ExcelReaderTest.java
License:Apache License
@Test public void testRead_FORMULA_NUMERIC() throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // // w w w .j a v a 2 s .co m Sheet sheet = workbook.createSheet(); Row row0 = sheet.createRow(0); row0.createCell(0).setCellFormula("1200+34"); row0.createCell(1).setCellFormula("1200+34.56"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); // try (ExcelReader reader = new ExcelReader(workbook)) { String[] r0 = reader.read(); assertNotNull(r0); assertEquals(2, r0.length); assertEquals("1234", r0[0]); assertEquals("1234.56", r0[1]); assertNull(reader.read()); } } }
From source file:cherry.goods.excel.ExcelReaderTest.java
License:Apache License
@Test public void testRead_FORMULA_STRING() throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // //from ww w .ja v a 2 s . c o m Sheet sheet = workbook.createSheet(); Row row0 = sheet.createRow(0); row0.createCell(0).setCellFormula("\"CELL\"&\"00\""); row0.createCell(1).setCellFormula("\"CELL\"&\"01\""); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); // try (ExcelReader reader = new ExcelReader(workbook)) { String[] r0 = reader.read(); assertNotNull(r0); assertEquals(2, r0.length); assertEquals("CELL00", r0[0]); assertEquals("CELL01", r0[1]); assertNull(reader.read()); } } }
From source file:cherry.goods.excel.ExcelReaderTest.java
License:Apache License
@Test public void testRead_FORMULA_BOOLEAN() throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // /*from w w w . j av a2 s .c o m*/ Sheet sheet = workbook.createSheet(); Row row0 = sheet.createRow(0); row0.createCell(0).setCellFormula("1=1"); row0.createCell(1).setCellFormula("1=0"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); // try (ExcelReader reader = new ExcelReader(workbook)) { String[] r0 = reader.read(); assertNotNull(r0); assertEquals(2, r0.length); assertEquals("true", r0[0]); assertEquals("false", r0[1]); assertNull(reader.read()); } } }
From source file:cherry.goods.excel.ExcelReaderTest.java
License:Apache License
@Test public void testRead_FORMULA_ERROR() throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // /* w w w .j av a 2s . co m*/ Sheet sheet = workbook.createSheet(); Row row0 = sheet.createRow(0); row0.createCell(0).setCellErrorValue((byte) 0); row0.createCell(1).setCellFormula("A1"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); // try (ExcelReader reader = new ExcelReader(workbook)) { String[] r0 = reader.read(); assertNotNull(r0); assertEquals(2, r0.length); assertNull(r0[0]); assertNull(r0[1]); assertNull(reader.read()); } } }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java
License:Apache License
private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output) throws IOException { // Identify the sheets in both workbooks List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB); List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB); // Process each sheet from the excerpt in turn for (int i = 0; i < sheetsToMerge.length; i++) { Sheet source = sourceSheets.get(i); Sheet dest = destSheets.get(i);//from ww w . j a v a 2s . c om for (Row srcR : source) { for (Cell srcC : srcR) { if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA || srcC.getCellType() == Cell.CELL_TYPE_ERROR) { // Don't merge these kinds of cells } else { Row destR = dest.getRow(srcR.getRowNum()); if (destR == null) { // Newly added row to the excerpt file, skip this } else { Cell destC = destR.getCell(srcC.getColumnIndex()); if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) { // Both are empty, don't need to do anything } else { if (destC == null) destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType()); // Sync contents if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) { destC.setCellType(Cell.CELL_TYPE_BLANK); } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) { destC.setCellValue(srcC.getBooleanCellValue()); } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) { destC.setCellValue(srcC.getNumericCellValue()); } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) { destC.setCellValue(srcC.getStringCellValue()); } // Sync formatting rules // TODO } } } } } } // Re-evaluate all the formulas in the destination workbook, now that // we have updated cells in it FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator(); eval.evaluateAll(); // Save the new file fullWB.write(output); }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java
License:Apache License
@Test public void excerptGoesReadOnly() throws Exception { for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) { FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); Sheet s = wb.createSheet("Test"); // Numeric formulas Row r1 = s.createRow(0);//from ww w .j a v a2s. c om Cell c1 = r1.createCell(0); Cell c2 = r1.createCell(1); Cell c3 = r1.createCell(2); Cell c4 = r1.createCell(3); c1.setCellValue(1); c2.setCellValue(2); c3.setCellFormula("A1+B1"); c4.setCellFormula("(A1+B1)*B1"); // Strings, booleans and errors Row r2 = s.createRow(1); Cell c21 = r2.createCell(0); Cell c22 = r2.createCell(1); Cell c23 = r2.createCell(2); Cell c24 = r2.createCell(3); c21.setCellValue("Testing"); c22.setCellFormula("CONCATENATE(A2,A2)"); c23.setCellFormula("FALSE()"); c24.setCellFormula("A1/0"); // Ensure the formulas are current eval.evaluateAll(); // Run the excerpt File tmp = File.createTempFile("test", ".xls"); wb.write(new FileOutputStream(tmp)); ByteArrayOutputStream baos = new ByteArrayOutputStream(); excerpter.excerpt(new int[] { 0 }, tmp, baos); // Check Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(1, newwb.getNumberOfSheets()); s = newwb.getSheetAt(0); r1 = s.getRow(0); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType()); assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001); assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001); assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001); assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001); r2 = s.getRow(1); assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType()); assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType()); assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType()); assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType()); assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue()); assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue()); assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue()); } }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
Report(final ReportSource reportSource, UDFFinder customFunctions, final InputStream template) { if (template == null) this.workbook = new HSSFWorkbook(); else//w w w. ja v a 2 s. c o m try { this.workbook = new HSSFWorkbook(new BufferedInputStream(template)); } catch (IOException e) { throw new RuntimeException("Could not load template for report!"); } if (customFunctions != null) this.workbook.addToolPack(customFunctions); final Set<String> sheetsToHide = new HashSet<String>(); final Set<String> sheetsToDelete = new HashSet<String>(); String previousSheetName = null; Sheet sheet = null; // Iterator over all celldefinitions // this doesn't compile inside Oracle Database VM. You need to import the compiled classes // or use reportSource.iterator() directly for (CellDefinition cellDefinition : reportSource) { Matcher m = null; if (HIDE_SHEET_CELL.equals(cellDefinition.sheetname)) { sheetsToHide.add(cellDefinition.value); } else if (DELETE_SHEET_CELL.equals(cellDefinition.sheetname)) { sheetsToDelete.add(cellDefinition.value); } else if ((m = CLONE_SHEET_CELL.matcher(cellDefinition.sheetname)).matches()) { final String sourceName = m.group(1); final String targetName = m.group(2); final Sheet target = workbook.cloneSheet(workbook.getSheetIndex(sourceName)); workbook.setSheetName(workbook.getSheetIndex(target), targetName); } else { // Create and cache the current sheet. if (previousSheetName == null || !previousSheetName.equals(cellDefinition.sheetname)) { previousSheetName = cellDefinition.sheetname; sheet = getSheet(workbook, cellDefinition.sheetname); } // create, fill and add cell this.addCell(workbook, sheet, cellDefinition); } } // Evaluate all formulas try { final FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); formulaEvaluator.clearAllCachedResultValues(); formulaEvaluator.evaluateAll(); } catch (Exception e) { } // Hide and delete sheets for (String sheetName : sheetsToHide) workbook.setSheetHidden(workbook.getSheetIndex(sheetName), true); for (String sheetName : sheetsToDelete) workbook.removeSheetAt(workbook.getSheetIndex(sheetName)); }
From source file:helpers.Excel.ExcelDataFormat.java
public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) { logger.info("Evaluating formulas."); evaluator.evaluateAll(); logger.info("Done..."); OneExcelSheet onesheet = new OneExcelSheet(); ArrayList<String> headers = null; for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) { Row row = rowIterator.next();//from w w w .j a v a 2 s . com if (headers == null) { headers = new ArrayList<String>(); int coln = 0; for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { try { Cell cell = cellIterator.next(); logger.info("Header:" + cell.getStringCellValue()); String headn = cell.getStringCellValue().replace(" ", ""); headers.add(headn); OneExcelColumn col = new OneExcelColumn(headn, coln); onesheet.columns.add(col); } catch (Exception e) { logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e); } coln++; } } else { ArrayList<Object> newrow = new ArrayList<Object>(); onesheet.data.add(newrow); int coln = 0; //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //Cell cell=cellIterator.next(); //logger.info("Cell type:"+cell.getCellType()); switch (evaluator.evaluateInCell(cell).getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //logger.info(cell.getCellType()+"="+cell.getDateCellValue()); newrow.add(cell.getDateCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[9]++; } else { //logger.info(cell.getCellType()+"="+cell.getNumericCellValue()); newrow.add(cell.getNumericCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; } break; case HSSFCell.CELL_TYPE_FORMULA: int value = evaluator.evaluateFormulaCell(cell); value = cell.getCachedFormulaResultType(); newrow.add(value); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[0]++; break; default: //logger.info(cell.getCellType()+"="+cell.getStringCellValue()); String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8")); newrow.add(cellstr); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; break; } coln++; } } } return onesheet; }
From source file:jgnash.engine.budget.BudgetResultsExport.java
License:Open Source License
public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) { Resource rb = Resource.get(); Workbook wb;/* ww w . j a v a2s . co m*/ String extension = FileUtils.getFileExtension(file.getAbsolutePath()); if (extension.equals("xlsx")) { wb = new XSSFWorkbook(); } else { wb = new HSSFWorkbook(); } CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet Sheet s = wb.createSheet(model.getBudget().getName()); // create header cell styles CellStyle headerStyle = wb.createCellStyle(); // create 2 fonts objects Font amountFont = wb.createFont(); Font headerFont = wb.createFont(); amountFont.setFontHeightInPoints((short) 10); amountFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); DataFormat df = wb.createDataFormat(); // Set the other cell style and formatting headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setDataFormat(df.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); int row = 0; Row r = s.createRow(row); // create period headers for (int i = 0; i < model.getDescriptorList().size(); i++) { Cell c = r.createCell(i * 3 + 1); c.setCellValue( createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription())); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3)); } { int col = model.getDescriptorList().size() * 3 + 1; Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary"))); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2)); } // create results header columns row++; r = s.createRow(row); { Cell c = r.createCell(0); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account"))); c.setCellStyle(headerStyle); for (int i = 0; i <= model.getDescriptorList().size(); i++) { c = r.createCell(i * 3 + 1); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 2); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 3); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining"))); c.setCellStyle(headerStyle); } } // must sort the accounts, otherwise child structure is not correct List<Account> accounts = new ArrayList<>(model.getAccounts()); Collections.sort(accounts); // create account rows for (Account account : accounts) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode()); String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); cs.setIndention((short) (model.getDepth(account) * 2)); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(account.getName())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // add group summary rows for (AccountGroup group : model.getAccountGroupList()) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); amountStyle.setBorderBottom(CellStyle.BORDER_THIN); amountStyle.setBorderTop(CellStyle.BORDER_THIN); amountStyle.setBorderLeft(CellStyle.BORDER_THIN); amountStyle.setBorderRight(CellStyle.BORDER_THIN); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency()); String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(group.toString())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // force evaluation FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } // Save String filename = file.getAbsolutePath(); if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(filename) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(filename) + ".xls"; } try (FileOutputStream out = new FileOutputStream(filename)) { wb.write(out); } catch (Exception e) { Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private File createStockFile(List<ItemDB> list) { int nSize = list.size(); XSSFWorkbook wbs = createStockWorkbook(); XSSFSheet sheetStock = wbs.getSheet("Digital Version"); List<XSSFTable> lTables = sheetStock.getTables(); // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper() .createFormulaEvaluator();/* ww w .j av a 2 s . c o m*/ File fStock = createFilename("STK", ""); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5)); if (!lTables.isEmpty()) { XSSFTable table = lTables.get(0); table.getCTTable() .setRef(new CellRangeAddress(table.getStartCellReference().getRow(), table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(), table.getEndCellReference().getCol()).formatAsString()); XSSFRow row; XSSFCell cell; font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); int nCellRef = table.getStartCellReference().getRow() + 1; for (ItemDB itemdb : list) { row = sheetStock.createRow(nCellRef++); cell = row.createCell(0); cellStyle = cell.getCellStyle(); cell.setCellValue(itemdb.getDblQty()); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(itemdb.getStrMfr()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(itemdb.getStrSKU()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(itemdb.getStrDescrip()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(itemdb.getStrSupplier()); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellValue(itemdb.getStrSupPart()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(itemdb.getDblSalePrice()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(itemdb.getDblCost()); cell.setCellStyle(cellStyle); /*cell = row.createCell(8); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); CellStyle style = wbs.createCellStyle(); style.setDataFormat(wbs.createDataFormat().getFormat("0%")); cell.setCellStyle(style);*/ mainWorkbookEvaluator.evaluateAll(); } try { try (FileOutputStream fileOut = new FileOutputStream(fStock)) { wbs.write(fileOut); return fStock; } } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } return null; }