@Override public void generateReport(ReportConfig reportConfig) throws ReportGenerationException { try { LOGGER.info("starting report generation"); long start = System.currentTimeMillis(); LOGGER.debug("using config: " + reportConfig); Workbook workbook = getWorkbook(reportConfig); workbookPopulator.populateWorkbookWithData(reportConfig, workbook); LOGGER.info("updating formulas"); XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook); FileOutputStream fileOut = new FileOutputStream(reportConfig.getDestinationFile()); workbook.write(fileOut); fileOut.close(); String timeTaken = new SimpleDateFormat("mm:ss").format(new Date(System.currentTimeMillis() - start)); LOGGER.info("report successfully generated in " + timeTaken); } catch (IOException | InvalidFormatException e ) { LOGGER.error("unabe to generate report!"); throw new ReportGenerationException(e); } }
public void loadExcelTypeXLSX(File f){ final File f1 = f; new Thread(){ File f = f1; public void run(){ int length = (int)f.length(); JDialog d = new JDialog((Frame)null, "Loading File"); JPanel p = new JPanel(new BorderLayout()); p.setBorder(BorderFactory.createEmptyBorder(1, 1, 1, 1)); d.setLocationRelativeTo(null); JProgressBar pb = new JProgressBar(0,length); p.add(new JLabel("Loading " + (length / 1000) + " kb file"), BorderLayout.NORTH); p.add(pb); d.getContentPane().add(p); d.pack(); d.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE); d.setVisible(true); d.setAlwaysOnTop(true); //String excelPath = f1.getPath(); //XSSFWorkbook workbook = new XSSFWorkbook(excelPath); omitCount = 0; try { FileInputStream fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); if (workbook.getNumberOfSheets()<0) {return;} //System.out.println(workbook.getNumberOfSheets()); StringBuffer sb2 = new StringBuffer(); if(workbook.getSheetAt(0) != null){ String sheetname = workbook.getSheetName(0); // XSSFExcelExtractor extract = null; XSSFSheet worksheet = workbook.getSheetAt(0); // Check the columns usually first row of titles for columns. Just incase check next row. int numCol = worksheet.getRow(0).getLastCellNum(); int numCol1 = worksheet.getRow(1).getLastCellNum(); if (numCol1 > numCol) { numCol = numCol1; } XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator(workbook); for (int i = 0; i < worksheet.getLastRowNum()+1; i++) { for (int j = 0; j < numCol; j++){ formulaEvaluator.evaluateInCell(worksheet.getRow(i).getCell(j)); sb2.append(worksheet.getRow(i).getCell(j) + "\t"); pb.setValue(pb.getValue()+ (((""+worksheet.getRow(i).getCell(j)).length()*2) + 38)); pb.repaint(); } sb2.append("\n"); try { if(worksheet.getRow(i).getCell(0).getStringCellValue().startsWith("#")) { omitCount ++; } } catch (IllegalStateException ix) {} //System.out.println(worksheet.getRow(1).getCell(1)+ " R " + worksheet.getLastRowNum() + " c " + worksheet.getRow(1).getLastCellNum()); //String excelText = extract.getText().replaceFirst(sheetname+"\n", ""); //input.setText(excelText); } name.setText(f.getName().substring(0, f.getName().lastIndexOf('.'))); //System.out.println(sb2.toString()); input.setText(sb2.toString()); System.out.println("Detected " + omitCount + " commented row(s). Commented data rows will not be plotted."); detectMessage.setText("<html>Detected <b>" + omitCount + " </b> commented row(s).<br>Commented data rows <b>will not</b> be plotted.<br>Click Refresh to view updated count.</html>"); pack(); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } d.dispose(); System.out.println("data ready"); } }.start(); }
@Documentation( value = "Insert a table from an Excel .xlsx file.", params = { @Param(name = "uri", value = "The Excel .xlsx file uri, it can be relative to the template"), @Param(name = "sheetName", value = "The sheet name"), @Param(name = "topLeftCellAdress", value = "The top left cell address"), @Param(name = "bottomRightCellAdress", value = "The bottom right cell address"), @Param(name = "languageTag", value = "The language tag for the locale"), }, result = "insert the table", examples = { @Example(expression = "'excel.xlsx'.asTable('Feuil1', 'C3', 'F7', 'fr-FR')", result = "insert the table from 'excel.xlsx'"), } ) // @formatter:on public MTable asTable(String uriStr, String sheetName, String topLeftCellAdress, String bottomRightCellAdress, String languageTag) throws IOException { final MTable res = new MTableImpl(); final URI xlsxURI = URI.createURI(uriStr, false); final URI uri = xlsxURI.resolve(templateURI); try (XSSFWorkbook workbook = new XSSFWorkbook(uriConverter.createInputStream(uri));) { final FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); final XSSFSheet sheet = workbook.getSheet(sheetName); if (sheet == null) { throw new IllegalArgumentException(String.format("The sheet %s doesn't exists in %s.", sheetName, uri)); } else { final Locale locale; if (languageTag != null) { locale = Locale.forLanguageTag(languageTag); } else { locale = Locale.getDefault(); } final DataFormatter dataFormatter = new DataFormatter(locale); final CellAddress start = new CellAddress(topLeftCellAdress); final CellAddress end = new CellAddress(bottomRightCellAdress); int rowIndex = start.getRow(); while (rowIndex <= end.getRow()) { final XSSFRow row = sheet.getRow(rowIndex++); if (row != null) { final MRow mRow = new MRowImpl(); int cellIndex = start.getColumn(); while (cellIndex <= end.getColumn()) { final XSSFCell cell = row.getCell(cellIndex++); if (cell != null) { final MStyle style = getStyle(cell); final MElement text = new MTextImpl(dataFormatter.formatCellValue(cell, evaluator), style); final Color background = getColor(cell.getCellStyle().getFillForegroundColorColor()); final MCell mCell = new MCellImpl(text, background); mRow.getCells().add(mCell); } else { mRow.getCells().add(createEmptyCell()); } } res.getRows().add(mRow); } else { final int length = end.getColumn() - start.getColumn() + 1; res.getRows().add(createEmptyRow(length)); } } } } return res; }
public ExcelFormulaEvaluator (@Nonnull final Workbook aWB, @Nullable final IStabilityClassifier aStability) { m_aEvaluator = aWB instanceof HSSFWorkbook ? new HSSFFormulaEvaluator ((HSSFWorkbook) aWB, aStability) : XSSFFormulaEvaluator.create ((XSSFWorkbook) aWB, aStability, null); }
private void initWorkbook(InputStream in) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(in); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); evaluator.setIgnoreMissingWorkbooks(true); init(workbook, evaluator); }
