Shift rows does adjust cell/area references in formulas in POI 3.14.
Looking at the change log [1], shifting formulas has been supported
since POI 3.5 beta 5 (Feb 2009). See bug 46663 [2] and bug 46536 [3].
I verified that shifting formulas works as expected in POI 3.14 XSSF.
Is it possible your row/column indices are off by one or swapped? POI
uses 0-based indices.
It sounds like you are testing this in a more complicated application.
Can you try this standalone test:
public Double getCellValue() throws IOException, InvalidFormatException{
Workbook workBook = new XSSFWorkbook()
Sheet sheet = workBook.createSheet();
Row row1 = sheet.createRow(0);
Row row2 = sheet.createRow(1);
Row row3 = sheet.createRow(2);
Cell J1 = row1.createCell(9);
Cell B2 = row2.createCell(1);
Cell B3 = row3.createCell(1);
Cell E2 = row2.createCell(4);
Cell F2 = row2.createCell(5);
Cell E3 = row3.createCell(4);
Cell F3 = row3.createCell(5);
// set cell values for J1, B2:B3, E2:F3
// ...
// Set formula
Cell J2 = row2.createCell(9);
J2.setCellFormula("J1 + SUM(B2:B3) + SUM(E2:F3)");
// Shift rows
// see
https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#shiftRows(int,%20int,%20int)
sheet.shiftRows(0, 2, 1);
// might need to ignore whitespace for this to work. Use
String.replace(" ", "")
assertEquals("J2 + SUM(B3:B4) + SUM(E3:F4)", J2.getCellFormula());
FormulaEvaluator evaluator =
workBook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(J2);
return cellValue.getNumberValue();
}
[1] https://poi.apache.org/changes.html
[2] https://bz.apache.org/bugzilla/show_bug.cgi?id=46663
[3] https://bz.apache.org/bugzilla/show_bug.cgi?id=46536
On Aug 24, 2016 7:27 AM, "[email protected]" <[email protected]> wrote:
>
> Hello guys, I am trying to read a value computed using a formula in an excel
> sheet.
>
> The formula is a simple sum function as follows: J1 + SUM(B2:B3) + SUM(E2:F3)
>
> Using Apache POI 3.14 libraries, I came up with the following snippet of code:
>
> public Double getCellValue() throws IOException, InvalidFormatException{
> Workbook workBook = WorkbookFactory.create(new FileInputStream(new
> File("data.xlsx")));
> Sheet sheet = workBook.getSheetAt(0);
>
> // i only need one specific cell
> // i made sure I am targeting the correct cell
> Row row = sheet.getRow(1);
> Cell cell = row.getCell(9);
>
> // this is where I am stuck
> FormulaEvaluator evaluator =
> workBook.getCreationHelper().createFormulaEvaluator();
> CellValue cellValue = evaluator.evaluate(cell);
> return cellValue.getNumberValue();
> }
>
> The method keeps returning 0.0 instead of the correct computed value.
>
> I initially tried a different approach using cell.getNumericCellValue()
> instead of FormulaEvaluator, however that was giving me the same incorrect
> result, 0.0.
>
> After going through the documentation online, I failed to find an explanation
> to my problem, any insight on the matter would be greatly appreciated.
>
> Update: I moved the cell to a different location on the grid in Excel, now
> the method does return a value, just not the correct one (e.g. 515 instead of
> 800). It was initially at the last row in my table, and whenever I would
> manipulate my table (add a new row of data), I would shift the last row down
> and update the formula accordingly. Now it's position is fixed at the second
> row. I'm not entirely sure why this would make any difference at all, maybe
> something that has to do with the sheet.shiftRows() method?
>
> Cheers.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
