Setting categorydata

Aspose.Cells Product Family
1

Hi,


I’m using aspose cells to generate an excel sheet and I want to add a Pie chart now.
I’m using following code to add the chart, but the chart.NSeries.CategoryData remains an empty String after assigning the value.

Dim chartIndex As Integer = myWorkSheet.Charts.Add(ASC.ChartType.Pie, _myFocusRowIndex + 5, 1 + StartRange * 3, 15, 15)

Dim chart As ASC.Chart = myWorkSheet.Charts(chartIndex)

Dim range As String = ExcelCol(StartRange) + “3:” + ExcelCol(StartRange + 1) + “3”

chart.NSeries.CategoryData += range

range = ExcelCol(StartRange) + _myFocusRowIndex.ToString + “:” + ExcelCol(StartRange + 1) + _myFocusRowIndex.ToString

chart.NSeries.Add(range, False)

Any idea why this could be?

Kind regards,
Ruben
2

Hi,


It works absolutely fine, no issue what so ever for setting Category data for chart’s data series.
I think there is an issue with your code setting for CategoryData, please check your own code for setting category data range, you may debug it thoroughly.

Here is the sample code, you may refer to it and run it test it, it works fine.

Sample code:

Dim workbook As New Aspose.Cells.Workbook()
Dim sheet As Aspose.Cells.Worksheet = workbook.Worksheets(0)
sheet.Name = “Data”
Dim cells As Aspose.Cells.Cells = workbook.Worksheets(0).Cells
workbook.ChangePalette(Color.Orange, 53)
workbook.ChangePalette(Color.LightBlue, 54)
workbook.ChangePalette(Color.LightCoral, 55)
workbook.ChangePalette(Color.Indigo, 52)
Dim colors() As Color = workbook.Colors
cells(“A1”).PutValue(“Region”)
cells(“A2”).PutValue(“France”)
cells(“A3”).PutValue(“Germany”)
cells(“A4”).PutValue(“England”)
cells(“A5”).PutValue(“Sweden”)
cells(“B1”).PutValue(“Sale”)
cells(“B2”).PutValue(32)
cells(“B3”).PutValue(62)
cells(“B4”).PutValue(06)

Dim sheetIndex As Integer = workbook.Worksheets.Add(Aspose.Cells.SheetType.Chart)
sheet = workbook.Worksheets(sheetIndex)
sheet.Name = “Chart”
Dim chartIndex As Integer = 0
chartIndex = sheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pie, 1, 3, 25, 12)
Dim c As Aspose.Cells.Charts.Chart = sheet.Charts(chartIndex)
c.Legend.TextFont.Name = “Arial”
c.Legend.TextFont.Size = 7
c.Legend.Position = Aspose.Cells.Charts.LegendPositionType.Right
c.ChartArea.BackgroundMode = Aspose.Cells.Charts.BackgroundMode.Transparent

c.NSeries.Add(“Data!B2:B5”, True)
c.NSeries.CategoryData = “Data!A2:A5”

Dim datalabels As Aspose.Cells.Charts.DataLabels
For i As Integer = 0 To c.NSeries.Count - 1
datalabels = c.NSeries(i).DataLabels
datalabels.ShowPercentage = True
datalabels.NumberFormat = “0%”
datalabels.Position = Aspose.Cells.Charts.LabelPositionType.OutsideEnd
datalabels.TextFont.Name = “Arial”
datalabels.TextFont.Size = 7
Next i

workbook.Save(“e:\test2\out_piecharttesting.xlsx”)

If you still find the issue, we need your complete runnable code (same as mine (above)) or a console application to reproduce the issue (you may zip it prior attaching here). Also give us your template Excel files. We will check it soon.

Also we recommend you to use our latest version v7.1.0.

Thank you.
3

As you can see in the code I posted I did follow the example code from this site. I’ve included a console application which shows the problem, please have a look at it.


Kind regards,

Ruben
4

OK, I’ve found it myself now. I have to set the categorydata after I set the data range.


But in the example you’ll find that although I go through a loop to create multiple charts, in the output there is only one chart. Any ideas on that?

Kind regards,

Ruben
5

Hi,


You are getting only one chart, this is due to your own code in the loop. I requested you to kindly debug your own code correctly.

I change your line of code a bit and it will give your 5 charts according to your loop, check the bold line, i.e.

Private Function CreateRatersCharts(ByVal myWorkSheet As Worksheet) As Worksheet
Dim StartRange As Integer
StartRange = _StartColumn + 1

_myFocusRowIndex += 1

For i = 0 To 4
Dim chartIndex As Integer = myWorkSheet.Charts.Add(ChartType.Pie, _myFocusRowIndex + 5, 1 + StartRange * 3, 15 + _myFocusRowIndex + 5, 15 + 1 + StartRange * 2)
Dim chart As Chart = myWorkSheet.Charts(chartIndex)
’…