List of usage examples for org.apache.poi.ss.usermodel Sheet setForceFormulaRecalculation
void setForceFormulaRecalculation(boolean value);
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-7] : ? ?? //from w w w . j a va 2 s . co m */ @Test public void testUseTemplate1() throws Exception { StringBuffer sb = new StringBuffer(); StringBuffer sbResult = new StringBuffer(); sb.append(fileLocation).append("/template/").append("template.xls"); sbResult.append(fileLocation).append("/").append("testUseTemplate1.xls"); Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, }; try { Workbook wb = excelService.loadExcelTemplate(sb.toString()); Sheet sheet = wb.getSheetAt(0); // set 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; Cell cell = row.getCell(j); if (sample_data[i][j] instanceof String) { cell.setCellValue(new HSSFRichTextString((String) sample_data[i][j])); } else { cell.setCellValue((Double) sample_data[i][j]); } } } // ? sheet.setForceFormulaRecalculation(true); excelService.createWorkbook(wb, sbResult.toString()); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sbResult.toString()); Sheet sheetT = wbT.getSheetAt(0); for (int i = 0; i < sample_data.length; i++) { Row row = sheetT.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { Cell cell = row.getCell(j); LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]); if (sample_data[i][j] == null) { assertEquals(cell.getCellType(), Cell.CELL_TYPE_BLANK); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue())); } else { assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString()); } } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testUseTemplate end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-7] : ? ?? /* w w w . ja v a 2s. c om*/ */ @Test public void testUseTemplate1() throws Exception { StringBuffer sb = new StringBuffer(); StringBuffer sbResult = new StringBuffer(); sb.append(fileLocation).append("/template/").append("template.xlsx"); sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx"); Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, }; try { XSSFWorkbook wb = null; wb = excelService.loadExcelTemplate(sb.toString(), wb); Sheet sheet = wb.getSheetAt(0); // set 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; Cell cell = row.getCell(j); if (sample_data[i][j] instanceof String) { cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j])); } else { cell.setCellValue((Double) sample_data[i][j]); } } } // ? sheet.setForceFormulaRecalculation(true); excelService.createWorkbook(wb, sbResult.toString()); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheetAt(0); for (int i = 0; i < sample_data.length; i++) { Row row = sheetT.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { Cell cell = row.getCell(j); LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]); if (sample_data[i][j] == null) { assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue())); } else { assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString()); } } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testUseTemplate end...."); } }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Clone the sheet at the passed index and replace values as needed *//*from w w w .ja v a2s . c o m*/ public Sheet addSheet(Workbook wb, SheetToAdd sheetToAdd, Set<String> usedSheetNames, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); Sheet sheet = sheetToAdd.getSheet(); sheet.setForceFormulaRecalculation(true); int sheetIndex = wb.getSheetIndex(sheet); // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the workbook String sheetName = EvaluationUtil.evaluateExpression(sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix).toString(); sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames); wb.setSheetName(sheetIndex, sheetName); usedSheetNames.add(sheetName); log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex); // Iterate across all of the rows in the sheet, and configure all those that need to be added/cloned List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>(); int totalRows = sheet.getPhysicalNumberOfRows(); int rowsFound = 0; for (int rowNum = 0; rowsFound < totalRows && rowNum < 50000; rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases Row currentRow = sheet.getRow(rowNum); if (log.isDebugEnabled()) { log.debug("Handling row: " + ExcelUtil.formatRow(currentRow)); } if (currentRow != null) { rowsFound++; } // If we find that the row that we are on is a repeating row, then add the appropriate number of rows to clone String repeatingRowProperty = getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections); if (repeatingRowProperty != null) { String[] dataSetSpanSplit = repeatingRowProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData()); int numRowsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numRowsToRepeat; i++) { Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i)); if (repeatNum == 1 && row != null && row != currentRow) { rowsFound++; } Map<String, Object> newReplacements = getReplacementData(sheetToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); rowsToAdd.add(new RowToAdd(row, newReplacements)); if (log.isDebugEnabled()) { log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow); } } } if (numRowsToRepeat > 1) { rowNum += numRowsToRepeat - 1; } } else { rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData())); if (log.isDebugEnabled()) { log.debug("Adding row: " + ExcelUtil.formatRow(currentRow)); } } } // Now, go through all of the collected rows, and add them back in for (int i = 0; i < rowsToAdd.size(); i++) { RowToAdd rowToAdd = rowsToAdd.get(i); if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) { Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections); if (log.isDebugEnabled()) { log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow)); } } } return sheet; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
void recalculateAllWorkbookFormulas() { if (data.wb instanceof XSSFWorkbook) { // XLSX needs full reevaluation FormulaEvaluator evaluator = data.wb.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = data.wb.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); }/*from w w w. ja v a 2 s . c o m*/ } } } } else if (data.wb instanceof HSSFWorkbook) { // XLS supports a "dirty" flag to have excel recalculate everything when a sheet is opened for (int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = ((HSSFWorkbook) data.wb).getSheetAt(sheetNum); sheet.setForceFormulaRecalculation(true); } } else { String forceRecalc = getVariable(STREAMER_FORCE_RECALC_PROP_NAME, "N"); if ("Y".equals(forceRecalc)) { data.wb.setForceFormulaRecalculation(true); } } }