Dominik and Team,
I did my very best to create an isolated Test Case.
The attached file does exactly what I do in the production (without
database, formatting and less formulas). But for whatever reason it
never triggers the Exception.
The flow and the business logic is exactly the same. Only difference
is: in the production I read the Detail Sheet from a Database and do
rather large Cash Flow Calculations instead just adding 2 numbers.
Without the database it is useless though.
I do understand that without a reproducible test, nobody can debug the
problem. So I can only leave that open for now and would return if I
was able to get more information.
Warm regards
Andreas
package com.manticore.etl.custom;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.text.MessageFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
public class FormulaEvaluationTest {
public final static Logger LOGGER = Logger.getLogger(FormulaEvaluationTest.class.getName());
public static void main(String[] args) {
try {
for (int i = 0; i<15; i++) {
File file = buildReport();
}
} catch (Exception ex) {
LOGGER.log(Level.SEVERE, "Failed to write the report.", ex);
}
}
public static File buildReport() throws Exception {
Workbook workbook = WorkbookFactory.create(true);
int SHEETS=1000;
Font font = workbook.createFont();
font.setFontName("Roboto Mono");
font.setFontHeightInPoints((short) 9);
DataFormat format = workbook.createDataFormat();
CellStyle amountCellStyle = workbook.createCellStyle();
amountCellStyle.setDataFormat(format.getFormat("#,##0.00;[RED]-#,##0.00"));
amountCellStyle.setFont(font);
amountCellStyle.setWrapText(true);
// Create the main summary sheet with hyperlinks and formulas referencing the detail sheets
{
int r=0;
int c=0;
Sheet sheet = workbook.createSheet("Summary Sheet");
Row row = sheet.createRow(r++);
for (int i=0; i<SHEETS; i++) {
String formulaStr = MessageFormat.format("hyperlink(\"#''Sheet{0}''!C1\", ''Sheet{0}'''!C1)", i);
Cell cell = row.createCell(i);
cell.setCellStyle(amountCellStyle);
cell.setCellFormula(formulaStr);
}
}
// Create the detail sheets, which calculate a total (but do not reference other sheets)
for (int i=0; i<SHEETS; i++) {
int r=0;
int c=0;
Sheet sheet = workbook.createSheet("Sheet" + i);
Row row = sheet.createRow(r++);
Cell cell = row.createCell(c++);
cell.setCellStyle(amountCellStyle);
cell.setCellValue(i*c);
cell = row.createCell(c++);
cell.setCellStyle(amountCellStyle);
cell.setCellValue(i*c);
cell = row.createCell(c++);
cell.setCellStyle(amountCellStyle);
cell.setCellFormula("1E12 * SUM(A1:A2)");
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
try {
// this can throw an ArrayIndexOutOfBoundsException
// at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheet(XSSFEvaluationWorkbook.java:77)
evaluator.evaluateFormulaCell(cell);
} catch (Exception ex) {
LOGGER.log(Level.SEVERE, "Unable to evaluate Cell " + cell.toString(), ex);
}
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
}
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);
for (Row r : sheet) {
for (Cell c : r) {
if (c.getCellType() == CellType.FORMULA) {
try {
// this can throw an ArrayIndexOutOfBoundsException
// at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheet(XSSFEvaluationWorkbook.java:77)
evaluator.evaluateFormulaCell(c);
} catch (Exception ex) {
LOGGER.log(Level.SEVERE, "Unable to evaluate Cell " + c.toString(), ex);
}
}
}
}
for (int i=0; i<SHEETS; i++) {
sheet.autoSizeColumn(i);
}
File file = File.createTempFile("FormulaEvaluationTest", ".xlsx");
try (
FileOutputStream fileOutputStream = new FileOutputStream(file)) {
workbook.setForceFormulaRecalculation(true);
workbook.write(fileOutputStream);
workbook.close();
LOGGER.info( "Wrote file " + file.getAbsolutePath());
}
return file;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
