The data in the concerned sheet X is loaded from Datatable.
The problem I have is when I work on the origin file and enter the new data manually it works fine: the charts/pivot tables refresh perfectly after generating the PDF,
but when I copy this file to another file and load the new data from the data table then refresh my PivotTables/charts
the refresh works only on the Excel file (sure because IsRefreshOnLoad = true
) but when I generate pdf I find only the PivotTable data updated
. Any help with how could I refresh the charts in the PDF file, please?
My code :
- Code: Select all
//fileToTreat is the original file
//fileName the destination
File.Copy(fileToTreat, fileName, true);
FileInfo file = new FileInfo(fileName);
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
excelWorkBook.Worksheets.Delete("Commandes");
ExcelWorksheet ws = excelWorkBook.Worksheets.Add("Commandes");
ws.Cells["A1"].LoadFromDataTable(dtCde, true);
excelPackage.SaveAs(new FileInfo(fileName));
}
Workbook workbook = new Workbook();
workbook.ConverterSetting.ClearCacheOnConverted = true;
workbook.LoadFromFile(fileName);
foreach (Worksheet wb in workbook.Worksheets)
{
var pt = wb.PivotTables;
foreach (Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable item in pt)
{
item.Cache.IsRefreshOnLoad = true;
item.CalculateData();
}
var tables = wb.Charts;
foreach (Chart chart in tables)
{
chart.RefreshChart();
}
}
workbook.SaveToFile(path + "\\" + outFile, ExcelVersion.Version2016);
workbook.SaveToFile(path + "\\" + outFile, FileFormat.PDF);
Thanks
