When we attempt to call the following code below is is failing in the pivot. However, it is inconsistent on what sheet it fails in, etc. It’s as if the call to ASPOSE pivot is corrupting memory or resources. This same logic works for smaller files, but not these. The format and template is the same. The only difference is the data within. However, when I open these files I am able to refresh the tables within without a problem.
I attempted to attach a zip containing the files; however, they are too large to attach. I can send via email once contact is made. All attempts were made using ASPOSE.Cells (8.8.2.0).
public static byte[] RefreshWorkbookPivotTables(byte[] fileBytes)
{
MemoryStream memStream = new MemoryStream();
memStream.Write(fileBytes, 0, fileBytes.Length);
memStream.Position = 0;
AsposeLicense.LoadLicense();
Workbook workbook = new Workbook(memStream);
// Iterate Worksheets and Refresh/Recalculate any Pivot-Tables present in the Workbook
foreach (Aspose.Cells.Worksheet worksheet in workbook.Worksheets)
{
if (worksheet.PivotTables.Count > 0)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
try
{
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.RefreshDataFlag = false;
pivotTable.CalculateData();
pivotTable.RefreshDataOnOpeningFile = true;
}
catch (Exception ex)
{
Console.WriteLine("Report: RefreshPivots error on Worksheet: " + worksheet.Name + " pivot table: " + pivotTable.Name);
Console.WriteLine("Error: " + ex);
}
}
}
if (worksheet.Charts.Count > 0)
{
foreach (Aspose.Cells.Charts.Chart chart in worksheet.Charts)
{
try
{
chart.RefreshPivotData();
chart.Calculate();
}
catch (Exception ex)
{
Console.WriteLine("PMT Generate Report: Chart RefreshPivotData error on Worksheet: " + worksheet.Name + " chart: " + chart.Name);
Console.WriteLine(ex.Message);
}
}
}
}
//Save Workbook to Memory Stream
workbook.Save(memStream, Aspose.Cells.SaveFormat.Auto);
fileBytes = memStream.ToArray();
using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
{
workbook.Save(memoryStream, Aspose.Cells.SaveFormat.Auto);
fileBytes = memoryStream.ToArray();
}
return fileBytes;
}