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