Adding string values as series of chart instead of numbers

Dear Team,

I have a scenario where String values should be added as values of series for a chart.
Eg: 10, 5, 12, NA, 7 - these are the values to be added as a series of chart.

Once after adding as series I can see ’ 0 ’ for ‘NA’.

I want to know whether we can add both numbers and strings as values of series for a chart.

Regards,
Sanjeev

Hi Sanjeev,


Thank you for contacting Aspose support.

As per my testing, the string NA will be treated as 0 for the given scenario. Please provide us a spreadsheet showing your desired results, that you may create manually with Excel application. Upon reviewing it, we will try to mimic the same with Aspose.Cells APIs.

Hi,

Thanks for using Aspose.Cells.

Excel charts don’t accept string values. You may convert string values to zeros. Please check the following code in .NET as well as Java.


Here are the output charts created by the given sample code.


Dear Shakeel Faiz,

Thanks for the update.
Even I agree that in series values if we change a value to NA it will be converted to zero.

But I’ve a scenario where the datalabel for the value >=1000 should be changed to NA.

I’m able to change the text of data label with value 1,558 to NA manually in excel.
Please find attached the same excel for your reference.

Could you please help me to edit the text of the datalabel with value 1,558 to NA programatically.

Regards,
Sanjeev

Hi,


Please see the following sample code for your reference for your requirements:
e.g
Sample code:

//Create a new Workbook.
Workbook workbook = new Workbook(“Adding+NA+as+datalabel.xlsx”);

//Get the designer chart in the worksheet.
Worksheet sheet = workbook.getWorksheets().get(“Chart”);

//Get the first chart in the sheet.
Chart chart = sheet.getCharts().get(0);

//Get the data labels in the first (only) data series of the second data point.
DataLabels datalabels = chart.getNSeries().get(0).getPoints().get(1).getDataLabels();

//Change the text of the label.
datalabels.setText(“NA”);

//Save the excel file.
workbook.save(“out1.xlsx”);

Hope, this helps a bit.

Thank you.

Dear Amjad Sahi,

Thanks for the code snippet.

Here for the datalabel 1,538.0 I have to make it as 1538.0 and apply some condition check like if its >=1000 then only I should change it to “NA”.

May I know how to read the value 1,538.0 using code. Is there any method like getValue()/ getText() is available ?

Note: My requirement is as follows:
1) Reading 1,538.0
2) Making it as 1538.0
3) If (1538.0>=1000.0) then change to "NA"

Regards,
Sanjeev

Hi,

Thanks for your posting and using Aspose.Cells.

You should use getYValue() property of chart points. And then decide which of the returned value crosses your limit and then set the data label of that chart point to NA as shown by Amjad Sahi in the previous post.

Please see the following sample code and its console output for your reference.

Java
Workbook wb = new Workbook(“Adding+NA+as+datalabel.XLSX”);

Worksheet ws = wb.getWorksheets().get(0);

Chart ch = ws.getCharts().get(0);

ch.calculate();

ChartPointCollection col = ch.getNSeries().get(0).getPoints();

for(int i=0; i<col.getCount(); i++)
{
System.out.println(col.get(i).getYValue());
}

Console Output
1.9939152078290099
1558.9276896261415
-17.33063706687055
-22.186888179196014
-24.857984727849367