Say I have an existing excel file with a chart. Is there a way to use markers to populate the chart values or do I have to set each cell value individually by name? What I want to do is take a data collection, loop through it setting the chart values without targeting specific cells by name. Thanks
Hi,
Thank you for contacting Aspose support.
You can use Smart Markers to fill up the worksheet with data. Once the data is available, you can either create a chart or refresh an existing one based on that data. Please check this article for your reference.
Thanks for the reply. With a chart in cells, I have to define the range (columns and rows) that the chart will use for data though. How can I do that programatically? I created a template that had a few columns and that was it. The first column was for categories, the second column was value1 and the third was value2.
So I end up with the following, and I set A1 through C4 as the selected data chart range.
categories value1 value2
I then programatically load the workbook chart, loop through 3 rows of data which produces the following:
categories value1 value2
group1 1 4
group2 3 2
group3 8 9
The chart displays the data but it doesn’t appropriately display the categories, as group1, group2, and group3. It displays them as Point 1, Point 2, and Point 3. Also, ‘categories’ shows up in the legend as a series.
Hi again,
Thank you for writing back,
If you are creating the chart from scratch, I recommend you to check this article for details. In case you face any difficulty, please be kind enough to share your desired chart, which you may create manually in Excel application and share the spreadsheet here for better understanding of your requirements. If you are refreshing the chart with updated data, please share the original spreadsheet containing the source chart as well.
I’ve attached the spreadsheet with chart range. I’m manually creating it as a template. As I mentioned, I’m using this as a template and the code will populate the category names in the categories column and then the data points in the value1 and value2 columns. There will be 3 rows of data points. So like I said the data fills the A1 through C4 and it looks like:
categories value1 value2
group1 1 4
group2 3 2
group3 8 9
The chart displays the categories as Point 1, Point 2, and Point 3 because the group1,group2 and group3 doesn’t exist at the time the chart range is selected. How do I change this so that it displays the categories as group1, group2, group3 once the code populates the rows? Or do I have to manually populate the categories in the template and only populate the data points?
Hi,
Well, you need to change your template file’s (empty chart) a bit for their data series and category data range to be properly set. I have corrected it however in the code and it works fine, see the sample code below for your reference. I have used your Excel file (containing the chart) as a template file, I first inserted your data into respective cells for the chart’s data source. The output file is also attached.
e.g
Sample code:
Workbook workbook = new Workbook(“e:\test2\Sample.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[“A2”].PutValue(“group1”);
worksheet.Cells[“A3”].PutValue(“group2”);
worksheet.Cells[“A4”].PutValue(“group3”);
worksheet.Cells[“B2”].PutValue(1);
worksheet.Cells[“B3”].PutValue(3);
worksheet.Cells[“B4”].PutValue(8);
worksheet.Cells[“C2”].PutValue(4);
worksheet.Cells[“C3”].PutValue(2);
worksheet.Cells[“C4”].PutValue(9);
//Get the existing chart.
Chart chart = worksheet.Charts[0];
//Your existing data series are set wrongly, so I cleared it and re-add.
chart.NSeries.Clear();
chart.NSeries.Add(“B2:C4”, false);
//Set the category data correctly
chart.NSeries.CategoryData = “B1:C1”;
//Change the data series names so these should be shown properly in Legend.
for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Name = worksheet.Cells[i + 1, 0].Value.ToString();
}
workbook.Save(@“e:\test2\out1Sample1.xlsx”);
Hope, this helps a bit.
Thank you.
Thanks for the information. The only thing I don’t like about that implementation is that I have to clear the series which also clears the custom series colors. Sounds like there is no way to get around that.
Hi,
Please note, you can simply insert the new data in the range that has been set as source to an existing chart and refresh the chart to reflect the changes. However, as your chart does not seem to have correct series therefore we have suggested to clear the series collection and re-add it.
In case you have a chart with series correctly defined (as per required representation), you may opt the following snippet to inject the data and refresh the chart.
C#
Workbook workbook = new Workbook(dir + “Sample.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[“A2”].PutValue(“group3”);
worksheet.Cells[“A3”].PutValue(“group4”);
worksheet.Cells[“A4”].PutValue(“group5”);
worksheet.Cells[“B2”].PutValue(100);
worksheet.Cells[“B3”].PutValue(222);
worksheet.Cells[“B4”].PutValue(218);
worksheet.Cells[“C2”].PutValue(412);
worksheet.Cells[“C3”].PutValue(102);
worksheet.Cells[“C4”].PutValue(239);
//Get the existing chart
Chart chart = worksheet.Charts[0];
//Refresh the chart according to new data
chart.Calculate();
workbook.Save(dir + “output.xlsx”);
worksheet.Cells[“A2”].PutValue(“group3”);
worksheet.Cells[“A3”].PutValue(“group4”);
worksheet.Cells[“A4”].PutValue(“group5”);
worksheet.Cells[“B2”].PutValue(100);
worksheet.Cells[“B3”].PutValue(222);
worksheet.Cells[“B4”].PutValue(218);
worksheet.Cells[“C2”].PutValue(412);
worksheet.Cells[“C3”].PutValue(102);
worksheet.Cells[“C4”].PutValue(239);
//Get the existing chart
Chart chart = worksheet.Charts[0];
//Refresh the chart according to new data
chart.Calculate();
workbook.Save(dir + “output.xlsx”);
Hi,
wbd.net:
....The only thing I don't like about that implementation is that I have to clear the series which also clears the custom series colors. Sounds like there is no way to get around that.
There is a workaround to cope with your issue if it suits your needs, see the sample updated code with comments for your reference, I have tested with it and it works fine:
e.g
Sample code:
Workbook workbook = new Workbook("e:\\test2\\Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A2"].PutValue("group1");
worksheet.Cells["A3"].PutValue("group2");
worksheet.Cells["A4"].PutValue("group3");
worksheet.Cells["B2"].PutValue(1);
worksheet.Cells["B3"].PutValue(3);
worksheet.Cells["B4"].PutValue(8);
worksheet.Cells["C2"].PutValue(4);
worksheet.Cells["C3"].PutValue(2);
worksheet.Cells["C4"].PutValue(9);
//Get the existing chart.
Chart chart = worksheet.Charts[0];
//If you don't want to clear the data series, you may try as following:
//Since there are three data series (in total) defined, so you have to specify each data
//range accordingly.
chart.NSeries[0].Values = "B2:C2";
chart.NSeries[1].Values = "B3:C3";
chart.NSeries[2].Values = "B4:C4";
chart.NSeries.CategoryData = "B1:C1";
//Change the data series names so these should be shown properly in Legend.
for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Name = worksheet.Cells[i + 1, 0].Value.ToString();
}
workbook.Save(@"e:\test2\out1Sample1.xlsx");
Hope, this helps a bit.
Thank you.