search

nissl-lab / npoi

a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.
Apache License 2.0
5.71k stars 1.43k forks source link

DataConsolidateFunction mapping to wrong ST_DataConsolidateFunction #697

Closed davidthemaster30 closed 2 years ago

davidthemaster30 commented 2 years ago

When using pivotTable.AddColumnLabel(DataConsolidateFunction.COUNT, 5) the expected result should be a pivottable with a new datafield with a subtotal of "count", however it is mapped to "countNums".

The reason being ST_DataConsolidateFunction is an enum with values starting at 0 and DataConsolidateFunction are static classes starting with 1.

Here is where the conversion occurs: https://github.com/nissl-lab/npoi/blob/c71d44c2f0cd326b5188c26bf8c9cb1dd69f61a4/ooxml/XSSF/UserModel/XSSFPivotTable.cs#L367

DataConsolidateFunction.COUNT.Value = 2 (ST_DataConsolidateFunction)(1) = count (ST_DataConsolidateFunction)(2) = countNums

Possible solutions:

  1. start enum ST_DataConsolidateFunction mapping at 1 public enum ST_DataConsolidateFunction{ average = 1, count = 2, countNums = 3, //etc }
  2. Change dataField.subtotal = (ST_DataConsolidateFunction)(function.Value); to dataField.subtotal = (ST_DataConsolidateFunction)(function.Value-1);

Current workaround is to force the correct ST_DataConsolidateFunction after using pivotTable.AddColumnLabel(DataConsolidateFunction.COUNT, 5): definitions.dataFields.dataField[0].subtotal = ST_DataConsolidateFunction.count;

tonyqus commented 2 years ago

This is the original code in Java. dataField.setSubtotal(STDataConsolidateFunction.Enum.forInt(function.getValue()));

Looks POI also has the same problem.

tonyqus commented 2 years ago

Do you have an Excel file that I can use to reproduce this issue?

davidthemaster30 commented 2 years ago

I do not, because I've never gotten a working pivotTable that didn't crash Excel.

tonyqus commented 2 years ago

I accept your second solution: dataField.subtotal = (ST_DataConsolidateFunction)(function.Value-1);

The pivot table in NPOI still needs a few test to make it work. Thank you for your issue. This can definitely help NPOI.

tonyqus commented 2 years ago

I finally change the values of DataConsolidateFunction in order to align with enum values of ST_DataConsolidateFunction. https://github.com/nissl-lab/npoi/blob/6b1d17fa20097f49bbd4632fd1b9e700201c6b88/main/SS/UserModel/DataConsolidateFunction.cs#L29