Index was out of range pivot table.CalculateData method

Aspose.Cells Product Family
1

i have a problem when I want to calculate the pivot table in the following example excel file.
I always get the following exception “Index was out of range. Must be non-negative and less than the size of the collection. (Parameter ‘index’)” when I call the method “CalculateData”.
I am using the current version 24.12 and greater of Aspose.Cells. Mine is a framework console application.

This was reported fixed in the thread below, but apparently is is broken again.

Pivot table - Index was out of range - Free Support Forum - aspose.com

2

@mlittle300

It seems you are encountering an “Index was out of range” exception when trying to calculate a pivot table using the CalculateData method in Aspose.Cells. This issue has been reported previously and was noted as fixed, but it appears to have resurfaced in the current version you are using (24.12).

To troubleshoot this issue, you can try the following steps:

  1. Ensure Data Integrity: Make sure that the data source for your pivot table is intact and that there are no missing or corrupted entries. Sometimes, issues with the underlying data can lead to exceptions when calculating pivot tables.

  2. Refresh Data: Before calling CalculateData, ensure that you are refreshing the pivot table data. This can be done using the RefreshData method. Here’s a sample code snippet that demonstrates how to refresh and calculate pivot tables:

    using Aspose.Cells;
using Aspose.Cells.Pivot;

// Load the workbook
Workbook workbook = new Workbook("path_to_your_file.xlsx");
Worksheet sheet = workbook.Worksheets[0];

// Refresh and calculate all pivot tables in the sheet
foreach (PivotTable pivotTable in sheet.PivotTables)
{
    pivotTable.RefreshData();
    pivotTable.CalculateData();
}

// Save the workbook
workbook.Save("output_file.pdf", SaveFormat.Pdf);

  3. Check for Updates: Since you mentioned that this issue was previously fixed, it might be worth checking if there are any newer versions of Aspose.Cells available that address this problem. Sometimes, bugs are reintroduced in later versions, and a patch may be released.

  4. Review Related Forum Posts: You can also check the following forum thread for additional insights or similar issues reported by other users: Pivot table - Index was out of range.

If the problem persists after trying these steps, consider reaching out to Aspose support with a minimal reproducible example of your Excel file and the code you are using. This will help them diagnose the issue more effectively.

Feel free to ask if you have any further questions or need additional assistance!

3

I have tried all of the above.

  1. Data seems OK and I can provide along with a sample program that reproduces the error.
  2. I am calling refresh data
  3. I have upgraded to Aspose.Cell 25.3
  4. I see that this bug was original reported in March of 2021 and if it was fix, it is broken again.

I have a small program that I can submit which reproduces the error. How can I submit my program?

4

@mlittle300
By testing on the latest version v25.3 using the previously provided sample files and the following sample code, we were able to run the program normally without any exceptions. Please refer to the attachment. result.zip (60.6 KB)

Console.WriteLine("Pivot example");

var workbook = new Workbook(filePath + "Example.xlsx");

foreach (var workSheet in workbook.Worksheets)
{
    foreach (var pivotTable in workSheet.PivotTables)
    {
        pivotTable.RefreshData();

        pivotTable.CalculateData();
    }
}
workbook.Save(filePath + "out_net.xlsx");
Console.WriteLine("Finish");

If you still have questions, please provide your test code and sample files, and we will check them soon.

5

I would have provided my test code, but I did not see any way to update to uploaded it to your site. I believe the exact reason we received the error when calling the pivotTable.Calculate method is because we were binding to native Excel row\column data instead of an Excel table as you are doing in your example. Does this sound consistent with how your product is designed?

Please let me know how I can upload code in case I have a future need to do so.

6

@mlittle300 ,

To help us evaluate and reproduce your issue, please provide runnable sample code or a standalone sample application. Compress the resource files or application into a ZIP archive and upload it here. When replying, use the “Upload” button/icon in the reply frame to attach your files. To reduce the project’s size, you may exclude the Aspose.Cells.Dll file. Alternatively, you can upload the archive to a file-sharing service (e.g., Dropbox, Google Drive) and share the download link here. Ensure the link does not require login credentials to access the files.