Bug 53105 - Summing an entire row of an .xlsx spreadsheet fails
Summary: Summing an entire row of an .xlsx spreadsheet fails
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-04-19 08:20 UTC by malquitob
Modified: 2015-09-03 19:21 UTC (History)
0 users

Spreadsheet for use with the test case. (48.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-04-19 08:20 UTC, malquitob

Note You need to log in before you can comment on or make changes to this bug.
Description malquitob 2012-04-19 08:20:20 UTC
Created attachment 28640 [details]
Spreadsheet for use with the test case.


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:


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 {

  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) {
    } catch (InvalidFormatException e) {
    } finally {
      try {
      } catch (IOException e) {

    // 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.
Comment 1 malquitob 2012-04-19 08:22:25 UTC
Apologies once again for the typo; 16384.0.0 should read 16384.0 :embarrassed:
Comment 2 Dominik Stadler 2015-09-03 19:21:58 UTC
This actually works for me with the current trunk-version (3.13dev).