https://issues.apache.org/bugzilla/show_bug.cgi?id=53101
Bug #: 53101
Summary: FormulaEvaluator incorrectly evaluates sum over cell
range > 255
Product: POI
Version: 3.8
Platform: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
AssignedTo: [email protected]
ReportedBy: [email protected]
Classification: Unclassified
Created attachment 28636
--> https://issues.apache.org/bugzilla/attachment.cgi?id=28636
Spreadsheet for use with the test case.
Overview:
Summing a range of cells greater the 255 gives an incorrect result.
Steps to reproduce:
Create an .xlsx, fill a range of columns, eg B1:IZ1 with a value, say "1"
Insert a formula to sum these cells, ie SUM(B1:IZ1)
Using POI, read the spreadsheet and evaluate the formula.
Expected result: 259.0, actual result: 258.0
Example test case:
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.assertEquals;
public class PoiTest {
@Test
public void evaluateExcelUsingPoiApiOnly_expectCorrectEvaluation() {
// Arrange
InputStream inputStream =
this.getClass().getResourceAsStream("/test.xlsx");
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(inputStream);
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// Act
// evaluate SUM('Skye Lookup Input'!B2:IZ2), cells in range B2:IZ2 each
contain "1"
FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
double numericValue =
evaluator.evaluate(workbook.getSheetAt(0).getRow(0.getCell(0)).getNumberValue();
// Assert
assertEquals(259.0, numericValue, 0.0);
}
}
Attached is a test.xlsx
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
