Refresh pivot table problem

Aspose.Cells Product Family
1

Hello


I create pivot table y worksheet 1
I have the data for this pivot in worksheet 2

Wih Aspose I want to select the data from database, paste in worksheet 2 and update de pivot table

But the line ’ pivotTable.CalculateData();’ throws this error: 'Index was outside the bounds of the array’

Without pivotTable.CalculateData(); the daat doesn’t show when open Excel file

The code:
  Aspose.Cells.Workbook _AsposeWorkbook = new Aspose.Cells.Workbook(“Excelpath”);



	DataTable _Dt = UtilesSql.GetDatatable(“SELECT * FROM table”);


_AsposeWorkbook.Worksheets[2].Cells.ImportDataTable(_Dt, true, “A1”);

        <span style="color:blue;">foreach</span> (Aspose.Cells.<span style="color:#2b91af;">Worksheet</span> worksheet <span style="color:blue;">in</span> _AsposeWorkbook.Worksheets)
        { 

            <span style="color:blue;">foreach</span> (Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotTable</span> pivotTable <span style="color:blue;">in</span> worksheet.PivotTables)
            {                  

                pivotTable.RefreshDataFlag = <span style="color:blue;">true</span>;
                pivotTable.RefreshData();
                pivotTable.CalculateData(); //Error
                pivotTable.RefreshDataFlag = <span style="color:blue;">false</span>;

               <span style="font-family:Lucida Sans;font-size:12;font-style:italic;color:green;">         </span>
            }
        }</pre><pre style="font-family: Consolas; font-size: 13px; background: white;">I attach the Exceñ file</pre><pre style="font-family: Consolas; font-size: 13px; background: white;">Can you help me? Thanks</pre></div>
2

Hi,


Thank you for contacting Aspose support.

I have evaluated your presented scenario while using the latest version of Aspose.Cells for .NET 8.6.2.2 and following piece of code to replicate the System.IndexOutOfRangeException on my end. I have logged this incident in our bug tracking system as CELLSNET-44089 for further investigation.

Please note, as I do not have access to your database so I have manually copied a few rows of data onto a new worksheet in your provided XLSM and then imported the data from newly created worksheet to the one that serves as data source to the PivotTable and refreshed it.

C#

var book = new Workbook(“C:/temp/output+(6).xlsm”);
var dataSheet = book.Worksheets[“Sheet1”];
var data = dataSheet.Cells.ExportDataTable(0, 0, dataSheet.Cells.MaxDataRow + 1, dataSheet.Cells.MaxDataColumn + 1);
var pivotData = book.Worksheets[“Hoja3”];
pivotData.Cells.ImportDataTable(data, false, “A1”);
foreach (Aspose.Cells.Worksheet worksheet in book.Worksheets)
{
// worksheet.RefreshPivotTables(); //throws same error
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
pivotTable.RefreshDataOnOpeningFile = true;
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData(); //Error
pivotTable.RefreshDataFlag = false;
}
}
book.Save(“C:/temp/output.xlsx”, SaveFormat.Xlsx);
3

Hello


This example works?
4

Hi,


The code provided above was used to replicate the IndexOutOfRangeException exception on our side. We have provided it here for your future reference, where the ticket logged against this issue is currently pending for analysis, and is in the queue with other priority tasks. We will let you know of our analysis results as soon as we have completed it.
5

Hi,

Thanks for using Aspose.Cells.

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

6

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

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