I’m creating a chart in excell but when opening the file while the chart has the correct range set it does not have the correct data displayed.
chart.SetChartDataRange("=Sheet1!" + dataSheet.Cells[index, 0].Name + ":" + dataSheet.Cells[index + 2, column].Name, false);//=Sheet1!$A$1:$J$3
While setting the same range again manually does update the chart.
I’ve tried the following:
chart.RefreshPivotData();
chart.Calculate();
What am I missing?
Thanks for your query.
Please share your sample file and code snippet (runnable) with us for our testing. We will reproduce the problem and provide our feedback after analysis.
Code below:
var wb = new Workbook();
var pres = new Presentation();
var dataSheet = wb.Worksheets[0];
for (int i = 0; i < 300; i += 3)
{
int id = wb.Worksheets.Add(SheetType.Chart);
int cId = wb.Worksheets[id].Charts.Add(ChartType.Column, 1, 1, 5, 10);
var chartt = wb.Worksheets[id].Charts[cId];
string range = writeChartData(dataSheet, chartt, i);
}
wb.Save(@"...100Charts.xlsx");
private static string writeChartData(Worksheet dataSheet, Chart chart, int index)
{
var date = new DateTime(2019, 01, 01);
var limit = new DateTime(2019, 01, 10);
var column = 0;
dataSheet.Cells[index + 1, 0].Value = "indicator 1";
dataSheet.Cells[index + 2, 0].Value = "indicator 2";
while (date < limit)
{
//dataSheet.Cells[index + 0, column + 1].SetStyle(new Style() { Custom = "dd.mm.yyyy" });
dataSheet.Cells[index + 0, column + 1].Value = date;
dataSheet.Cells[index + 1, column + 1].Value = random.Next(100);
dataSheet.Cells[index + 2, column + 1].Value = random.Next(100);
column++;
date = date.AddDays(1);
}
chart.NSeries.Clear();
chart.SetChartDataRange("=Sheet1!" + dataSheet.Cells[index, 0].Name + ":" + dataSheet.Cells[index + 2, column].Name, false);
chart.RefreshPivotData();
chart.Calculate();
chart.ChartObject.UpdateSelectedValue();
return dataSheet.Cells[index, 0].Name + ":" + dataSheet.Cells[index + 2, column].Name;
}Thank you for providing sample code.
We are testing your code and need little assistance about the following:
You mentioned that when you set the same range again manually, it updates the chart. This we are not able to test. Please simplify the example more with less iterations (if possible) and provide us the steps in detail to test the above mentioned manual procedure. It will help us to observe the problem and provide our feedback at the earliest.
Well excel doesn’t let you apply the same range twice but if you edit the chart range (even if it amounts to deleting a char and rewriting it) it should apply. But what I meant by what I said is that if I were to set the range in excel it would populate the chart the way I want it while doing it using ASPOSE does not.
Please provide us an expected output file created by Excel. Also provide us some sample code using Aspose.Cells where you try to create the exact same output as created by Excel but getting wrong output. It will help us to compare the expected file created by Excel with program output file and provide our feedback.