Exception when refreshing pivot table containing field with date grouping

Aspose.Cells Product Family
1

test excel file.zip (9.8 KB)

The attached excel file contains a pivot table with a date field that is grouped by year and month. This works fine in Excel, but causes an exception in code when attempting to refresh. This occurs with the latest 17.8 release of Aspose.Cells for .NET.

The exception that is being thrown is System.FormatException. It also says “Input string was not in a correct format”. I believe it may have to do with the PivotItems that get created for the field such as “>2017”

    Dim excel As New Aspose.Cells.Workbook("c:\temp\test.xlsx")
    Dim sheet As Aspose.Cells.Worksheet = excel.Worksheets(0)
    Dim pivot As Aspose.Cells.Pivot.PivotTable = sheet.PivotTables(0)

    pivot.RefreshData()         'this works
    pivot.CalculateData()       'this doesn't work
    sheet.RefreshPivotTables()  'this also doesn't work
2

@chrism.peloton

We were able to observe the exception as per your description and logged this issue in our database for investigation and for a fix. Once, there is some fix or any other news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-45657 - Input string was not in a correct format - Exception on Pivot.CalculateData() method

Exception:

System.FormatException was unhandled
  HResult=-2146233033
  Message=Input string was not in a correct format.
  Source=mscorlib
  StackTrace:
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
       at     . (Int32  , PivotField  , PivotItem  , Int32  )
       at     . (      , PivotFieldCollection  , Int32  , Boolean  ,      )
       at     . (     )
       at     . (     )
       at Aspose.Cells.Pivot.PivotTable.CalculateData()
3

This issue is currently holding back a commercial release of our software. If there is a known programmatic work-around that can be applied immediately, we would very much appreciate having that provided. Thank you.

5

@chrism.peloton

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-45657 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

6

test excel file 2.zip (42.9 KB)

Using the original test file I uploaded, the problem is resolved with Aspose.Cells for .NET 17.9. However, I am still seeing the issue with a different file which I have attached.

7

@chrism.peloton,

Thanks for providing us template file.

Thanks for your confirmation on your issue was fixed with v17.9.
However, the same issue can be reproduced using your newly attached file. I used the following sample code with your new template file:
e.g
Sample code:

Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook("e:\\test2\\test.xltm");

            foreach (Worksheet sheet in excel.Worksheets)
            {
                foreach (PivotTable pt in sheet.PivotTables)
                {
                    pt.RefreshData();
                    pt.CalculateData();//error occurred for the first worksheet's pivot table.
                    sheet.RefreshPivotTables();
                }
            }

I have reopened your issue. Our concerned developer from product team will look into it soon.

8

@chrism.peloton

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSNET-45657 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

9

@chrism.peloton

Please download and try the latest fix and let us know your feedback.

*Aspose.Cells for .NET (Latest Version)

10

The issues you have found earlier (filed as CELLSNET-45657) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.