Issue with Chart Source Data

Aspose.Cells Product Family
1

Hi,

With COM I used to set the chart’s data using the function SetSourceData, which take a Range as parameter.

Here I can only see NSeries.CategoryData way of adding a datasource, which only accepts a string of style “A1:D4” … What if I don’t know the coordinates ? All I have is a Range object, how can I build a chart out of it ?

Thanks

2
Hi,

Yes, you may set the named ranges for the nseries data sources, see the example below:

Sample code:

Workbook workbook = new Workbook();
Worksheet dataSheet = workbook.Worksheets[0];
Cells cells = workbook.Worksheets[0].Cells;
//Put a value into a cell
cells["A1"].PutValue("Region");
cells["A2"].PutValue("France");
cells["A3"].PutValue("Germany");
cells["A4"].PutValue("England");
cells["A5"].PutValue("Sweden");
cells["A6"].PutValue("Italy");
cells["A7"].PutValue("Spain");
cells["A8"].PutValue("Portugal");
cells["B1"].PutValue("Sale");
cells["B2"].PutValue(70000);
cells["B3"].PutValue(55000);
cells["B4"].PutValue(30000);
cells["B5"].PutValue(40000);
cells["B6"].PutValue(35000);
cells["B7"].PutValue(32000);
cells["B8"].PutValue(10000);

//Creating a named range from B2 (start range) to MaxDataRow and MaxDataColumn Dynamically
Range DataRange = dataSheet.Cells.CreateRange("B2", dataSheet.Cells[dataSheet.Cells.MaxDataRow, dataSheet.Cells.MaxDataColumn].Name);
//Setting the name of the named range
DataRange.Name = "__DATA__";
//Creating a named range from A2 (start range) to MaxDataRow and MaxDataColumn Dynamically
Range CatagoryRange = dataSheet.Cells.CreateRange("A2", dataSheet.Cells[dataSheet.Cells.MaxDataRow, 0].Name);
//Setting the name of the named range
CatagoryRange.Name = "__CATG__";

Worksheet sheet = workbook.Worksheets[workbook.Worksheets.Add()];
//Set the name of worksheet
sheet.Name = "Pie";

//Create chart
int chartIndex = 0;
chartIndex = sheet.Charts.Add(ChartType.Pie, 1, 3, 25, 12);
Chart chart = sheet.Charts[chartIndex];
//Set properties of chart title
chart.Title.Text = "Sales By Region";
chart.Title.TextFont.Color = Color.Black;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;

//Set properties of nseries
chart.NSeries.Add("__DATA__", true);
chart.NSeries.CategoryData = "__CATG__";
chart.NSeries.IsColorVaried = true;

for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].DataLabels.IsValueShown = true;
}


//Set the legend position type
chart.Legend.Position = LegendPositionType.Right;
workbook.Save("f:\\test\\outpiechart.xls");

And, if you want to get the range in the template excel file, you may use the following code to retrieve the existing range:

Range range = workbook.Worksheets.GetRangeByName("MyRange");

Thank you.

3

Hi,
Thanks for the quick response !

I’m a bit confused regarding MaxDataRow and MaxDataColumn… Could you explain when they come from and what they are ?

I’ve tried using them but their values are 0.

Hope you can help with this

Thanks

4

Hi,

Thank you for considering Aspose.

Cells.MaxDataRow returns maximum row index of cell which contains data in the worksheet. Cells.MaxDataColumn returns maximum column index of cell which contains data in the worksheet. If you still have any issue regarding them, please share your template file and provide further details about your issue and we will check it soon.

Thank You & Best Regards,