Outdated data on chart image related to PivotTable

[radoslaw]

My scenario is following:
1) open existing xlsx file with two worksheets ("Data" - contains sample data, "PivotChart" - with pivot table and pivot chart related to data from "Data" worksheet).
2) remove all data from "Data" worksheet and it with new data.
3) save chart image to image file.

Expected result - image file contains chart with new data inserted to "Data" worksheet (step 2).
Current result - image file contains chart with old data (from source xlsx file).

My question is: How force Spire.Xls to refresh PivotTable data and refresh chart related with the PivotTable?

I'm attaching sample xlsx file "PivotTable.xlsx"

I use following code:

Code: Select all

foreach (Worksheet sheet in workbook.Worksheets)
{
    foreach (XlsPivotTable pt in sheet.PivotTables)
    {                   
        pt.Cache.IsInvalidData = true;
        pt.Cache.IsRefreshOnLoad = true;
        pt.Cache.IsUpgradeOnRefresh = true;
    }
}

workbook.CalculateAllValue();


Then

Code: Select all

foreach (Worksheet sheet in workbook.Worksheets)
{
    foreach (XlsPivotTable pt in sheet.PivotTables)
    {                   
        pt.CalculateData();                   
    }
               
    for (int i = 0; i != sheet.Charts.Count; ++i)
    {       
        System.Drawing.Image chartImage = workbook.SaveChartAsImage(sheet, i);
        chartImage.Save($"chart {sheet.Index}-{i}.png", System.Drawing.Imaging.ImageFormat.Png);
    }                     
}


[rachel.lei]

Hello,

Thanks for your inquiry.
I tested your case with the latest Spire.XLS Pack(Hotfix) Version:10.1.8 but found that the data of the generated chart image is updated. Below is the code I used, and I also attached my output for your reference. If you were using an old version, I suggest you try again with the latest version. If I misunderstood, please provide your full testing code to help us investigate your issue more accurately.

Code: Select all

    Workbook wb = new Workbook();
    wb.LoadFromFile(@"PivotTable.xlsx");
    Worksheet sheet1 = wb.Worksheets[0];
    sheet1.Range["D2:D5"].Value = "500";
    sheet1.Range["E2:E5"].Value = "300";

    wb.CalculateAllValue();
    wb.SaveToFile("result.xlsx", FileFormat.Version2013);

    foreach (Worksheet sheet in wb.Worksheets)
    {
        foreach (XlsPivotTable pt in sheet.PivotTables)
        {
            pt.CalculateData();
        }
        for (int i = 0; i != sheet.Charts.Count; ++i)
        {
            System.Drawing.Image chartImage = wb.SaveChartAsImage(sheet, i);
            chartImage.Save(string.Format("chart-{0}-{1}.png",sheet.Index, i), System.Drawing.Imaging.ImageFormat.Png);
        }
    }

Besides, I noticed that there were some problems with the generated PivotChart image, it added an extra column of Grand Total. This issue has been logged into our bug tracking system with the ticket SPIREXLS-2076. If there is any update, I will inform you.

Sincerely,
Rachel
E-iceblue support team

[radoslaw]

With Spire.XLS Pack(Hotfix) Version:10.1.8 it works as you described. Chart image contains refreshed data with unwanted extra column "Grand Total".

I was testing my code using Free Spire.XLS for .NET.

What is the estimated time to fix issue with extra "Grand Total" column (ticket SPIREXLS-2076)?

[rachel.lei]

Hello,

Thanks your feedback and sorry for the late reply as weekend.
Sorry at this moment I can't tell you an estimated time about your issue since our Dev team is on Chinese New Year holiday from 24/1/2020 to 2/2/2020. They will work on this issue once they get back to the office. Apologize for the delay and inconvenience caused.

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Thanks for your patient waiting.
The bug SPIREXLS-2076 has been fixed now. Welcome to download the newly released Spire.XLS Pack(Hotfix) Version:10.3.8 from the following link.
Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/10.3.8

Besides, in this release, we have made some adjustments to chart data updates. If the data source of the chart is modified, please invoke the method "chart.RefreshChart()" to refresh the chart. Below is the corresponding code for reference.

Code: Select all

        //Modify the data source of chart...
        foreach (Chart chart in sheet.Charts)
        {
            chart.RefreshChart();
        }
        //Save to image...

Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Hope you are doing well.
Have you tested the hotfix? Could you please give us some feedback at your convenience?
Thanks in advance.

Sincerely,
Rachel
E-iceblue support team