We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Refresh pivot table's data results in corrupted excel file

Hello,

When I change a workbook’s data and refresh the pivot table attached to it, the result is a corrupted excel file.

The code used to perform this operation:

 foreach (var item in filesPath)
                            {
                                Workbook workbookPivot = new Workbook(item);
                                Worksheet worksheet = workbookPivot.Worksheets[1];
                                PivotTable pvtTable = worksheet.PivotTables[0];
                                //Take the first cell and the last cell name to refresh the pivot table
                                Worksheet worksheetsd = workbookPivot.Worksheets[0];
                                string firstCellName = worksheetsd.Cells.FirstCell.Name;
                                string lastCellName = worksheetsd.Cells.LastCell.Name;
                                pvtTable.ChangeDataSource(new string[] { worksheet.Name + firstCellName + ":" + lastCellName });

                                //removes column corresponding to the column in the config
                                string columnToRemove = 
                                workbookPivot.Worksheets[0].Cells[this.configuration.ColumnToRemovePivot + "1"].Value.ToString();
                                pvtTable.RemoveField(PivotFieldType.Row, columnToRemove);

                                pvtTable.RefreshDataFlag = true;
                                pvtTable.RefreshData();
                                pvtTable.CalculateData();
                                pvtTable.RefreshDataFlag = false;
                                //Delete all rows outside of pivot table
                                worksheet.Cells.DeleteRows(pvtTable.RowRange.EndRow + 2, worksheet.Cells.LastCell.Row);
                                workbookPivot.Save(item, SaveFormat.Xlsx);
                            }

thank you for your help,

Regards

@Papotame,

Thanks for the sample code.

Well, we need your template file (input file) used in your code to evaluate your issue precisely. Also, which corresponding column name in the config you are talking about? Please also give us complete details about your variables/objects data and output (corrupted) file to trace the issue.

Thank you.

I find the solution:

first refresh then change the data source and again refresh.

It’s solved :slight_smile:

@Papotame,

Good to know that you have sorted out your issue now. Feel free to contact us anytime if you find any other issue or queries, we will be happy to assist you soon.

Thank you.