https://issues.apache.org/bugzilla/show_bug.cgi?id=53105
Bug #: 53105
Summary: Summing an entire row of an .xlsx spreadsheet fails
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 28640
--> https://issues.apache.org/bugzilla/attachment.cgi?id=28640
Spreadsheet for use with the test case.
Overview:
Summing an entire row of an .xlsx gives an incorrect result.
Steps to reproduce:
Create an .xlsx, fill an entire row with a value, say "1"
Insert a formula to sum these cells, ie SUM(A1:XFD1)
Using POI, read the spreadsheet and evaluate the formula.
Expected result: 16384.0.0, actual result: 256.0
NB. Excel automatically rewrites the formula to:
SUM(1:1)
I'm not sure if this is the cause of the issue yet. The local fix applied for
Bug 53101 doesn't resolve this one.
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 PoiEntireRowSumTest {
@Test
public void evaluateExcelAllColumnsUsingPoiApiOnly_expectCorrectEvaluation()
{
// Arrange
InputStream inputStream =
this.getClass().getResourceAsStream("/entire_row_sum_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'!A4:XFD4), cells in range each contain
"1"
FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
double numericValue =
evaluator.evaluate(workbook.getSheetAt(0).getRow(1).getCell(0)).getNumberValue();
// Assert
assertEquals(16384.0, numericValue, 0.0);
}
}
I've attached a spreadsheet for testing.
--
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]
