Bug 53105 - Summing an entire row of an .xlsx spreadsheet fails
Summary: Summing an entire row of an .xlsx spreadsheet fails
Status: RESOLVED WORKSFORME
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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-04-19 08:20 UTC by malquitob
Modified: 2015-09-03 19:21 UTC (History)
0 users



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

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.

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.
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).