Update bar chart dynamically

Aspose.Cells Product Family
1

I’m having problem with modifying a bar chart data



I have a workbook with two worksheets



one has the data, raw data



the other is the bar chart, cycle time.



i’m using aspose.cell to populate the raw data worsheet without issue.



however, I want my chart to adjust according to the data range of the data. Here’s a sample of what it can look like.

2

Hi Tan,


Thank you for contacting Aspose Support.

Please call the Chart.Calculate method to refresh the chart based on your existing data source, as it should work for your requirement. Please feel free to write back in case you need our further assistance.
3

I tried that and the chart didn’t refresh with the data range of the table.

4

Hi Tan,

Please check the below provided code snippet that we used to fill in the data into the chart’s data range and then called the Chart.Calculate method to refresh the chart.

C#

var book = new Workbook(myDir + “Median_CT.xlsx”);

var sheet = book.Worksheets[1];

var cells = sheet.Cells;

for (int i = 13; i < 84; i++)

{

cells[i, 0].PutValue("Data " + i);

cells[i, 1].PutValue(i + 100 % 13);

}

sheet = book.Worksheets[0];

var chart = sheet.Charts[0];

chart.Calculate();

book.Save(myDir + “output.xlsx”);

Please note, we have used the latest version of Aspose.Cells for .NET 8.1.0 to perform this test. In case this is not what you require, then please elaborate your question further so we could assist you better in this regard.

5

I’m currently using v 8.0.1.0.

This is what I have in a function to refresh the workbook.

if (ExcelWK != null)
{
foreach (Aspose.Cells.Worksheet worksheet in ExcelWK.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)
{
ErrLog.LogError(new LogException("Report: RefreshPivots error on Worksheet: " + worksheet.Name + " pivot table: " + pivotTable.Name, ex), true, false);
}
}
}

                    if (worksheet.Charts.Count > 0)
                    {
                        foreach (Aspose.Cells.Charts.Chart chart in worksheet.Charts)
                        {
                            try
                            {
                                chart.RefreshPivotData();
                                chart.Calculate();
                            }
                            catch (Exception ex)
                            {
                                ErrLog.LogError(new logException("PMT Generate Report: Chart RefreshPivotData error on Worksheet: " + worksheet.Name + " chart: " + chart.Name, ex), true, false);
                            }
                        }
                    }
                }
                
                //ExcelWK.CalculateFormula();
            }
        }
6

Hi Tan,


Thank you for providing your code snippet. We have tested it on our end while using the latest version of Aspose.Cells 8.1.0, and it is working fine. Please check the below provided complete code that we have used to fill the empty cells of the chart data source and then refreshed it. Please also check the attached snapshot of the chart produced on our end, that is also displayed in our previously shared resultant spreadsheet.

C#

var ExcelWK = new Workbook(myDir + “Median_CT.xlsx”);
var sheet = ExcelWK.Worksheets[1];
var cells = sheet.Cells;
for (int i = 13; i < 84; i++)
{
cells[i, 0].PutValue("Data " + i);
cells[i, 1].PutValue(i + 100 % 13);
}

if (ExcelWK != null)
{
foreach (Aspose.Cells.Worksheet worksheet in ExcelWK.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);
}
}
}
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);
}
}
}
} // ExcelWK.CalculateFormula(); } }

ExcelWK.Save(myDir + “output.xlsx”);

In case these are not your desired results, please manually create your desired results using MS Excel application and share with us. Upon reviewing such sample, we will get a better idea of your requirements and we can be of better assistance to you.
7

I tried using the Aspose.Cell 8.1.0 and it just locks up when it tries to generate the data. Should there be any issue with license from 8.0.1 to 8.1.0?

8

Hi,


There shouldn’t be any issue with the license/subscription if your license is still valid for the latest build. Please check the SubscriptionExpiry tag in your license file by opening it in notepad application. However, if your license has expired, you will not be able to use the latest build i.e; Aspose.Cells for .NET 8.1.0 in licensed mode. In that case you will just get Evaluation Warning on your resultant spreadsheet with no other change in the behavior.

As discussed in your post, if the application hangs with v8.1.0, there could be a bug that have been fixed with latest release. We strongly recommend you to use the latest version whenever possible as the latest release brings many useful improvements as well as new features.

Regarding the original problem, are you able to refresh the chart based on new data source? In case you are still facing issues, we would request you to please share a sample application replicating the problem for our review.
9

For the example you did above, you had the data populated for the same range as the chart selected data range. If you populate only half of the data (data 1- 30), and do the Chart.Calculate(), can you see if that updates the chart?

10

I’ve checked the license and it’s good till 2015.

11

How would I create the chart? When I tried, it created multiple series rather than a single series.

12

phamtq:

For the example you did above, you had the data populated for the same range as the chart selected data range. If you populate only half of the data (data 1- 30), and do the Chart.Calculate(), can you see if that updates the chart?

Hi Tan,

Thank you for writing back.

In case you wish to update the chart based on the reduced data source (to be assigned source is smaller than assigned data source), you have to first resign the chart’s series as show in the following code, and then call the Chart.Calculate method to show to updated chart. Please note, the below code reassign’s the chart’s data source to a range “A2:B13” as displayed in the attached resultant spreadsheet.

C#

var book = new Workbook(myDir + "Median_CT.xlsx");

var chartWorksheet = book.Worksheets[0];

var chart = chartWorksheet.Charts[0];

try

{

var series = chart.NSeries[0];

series.Name = "new";

series.Values = "='raw data'!$B$2:$B$13";

series.XValues = "='raw data'!$A$2:$A$13";

chart.Calculate();

}

catch (Exception ex)

{

Console.WriteLine("chart: " + chart.Name + " Message: " + ex.Message);

}

book.Save(myDir + "output.xlsx");
13

Thanks. That works for me.

14

Hi Tan,


Good to know that you are up and running again. Please feel free to write back in case you need our further assistance.