How to Set Number Format for Cells in Chart's Data Table

Aspose.Cells Product Family
1

I am setting the values for a series using an array (i.e., {1000, 2000, 3000, etc.), rather than using a range on the spreadsheet.

I would like to have these values shown in the chart’s data table as $1,000, $2,000, $3,000. Is there any way to do that?

2

@FireWave,

Thanks for providing us details.

Please create your chart with your desired data table in MS Excel manually and save the file to provide us here, we will check and help you on how to do it via Aspose.Cells APIs.

3

I am adding the series as follows:

ExcelChart.zip (13.8 KB)

worksheet.Charts[0].NSeries.Add("{10000000, 20000000, 30000000, 40000000, 45000000, 145000000}", true)

4

@FireWave

Thanks for using Aspose APIs.

Please let us know, how do you set number format of the data table in Microsoft Excel. We are unable to find any such option in Microsoft Excel 2016. Please see the following screenshot for your reference.

5

Excel will use the cell format if you populate the chart series using a range of data from the spreadsheet. However, I am adding the series directly in code using an array, as I have shown.

I was hoping that there was a way to format the numbers when I insert them directly into the series.

I don’t see a way to do this directly in Excel either.

6

@FireWave,

Thanks for your reply.

Well, as you cannot apply formatting directly to the Data Table of the chart in MS Excel manually, so Aspose.Cells cannot achieve your task.

7

@FireWave

Please try to use formatted series as below

worksheet.Charts[0].NSeries.Add("{$100,00000, $200,00000, $300,00000, $400,00000, $450,00000, $145,000000}", true)

and check if it makes any difference and resolves your issue.

8

@FireWave

We have logged a New Feature request for this issue to support it in our future releases. Once, the fix is available for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45695 - Set Number Format for Cells in Charts Data Table
9

Yes, I tried this. The $ sign completely throws Excel and you end up with an empty array.
If you use just the commas, everything delimited by a comma is treated as a separate array element.
The only that that this appears to work is to create a range of cells, format the cells appropriately, and then reference the cells for the Series.

10

@FireWave,

As we have logged a ticket with an id “CELLSNET-45695” for your requirements. So, kindly spare us some time, we will try to support the feature and you don’t have to create a formatted range of cells to refer to data series anymore.

Once we have an update on the issue, we will update you here.

11

Thank you. That feature would be helpful.

12

@FireWave,

Please try our latest version/fix: Aspose.Cells for .NET v17.9.5.

Please set the format after adding series.
e.g
Sample code:

Chart c = worksheet.Charts[0];
c.NSeries.Add("{10000000, 20000000, 30000000, 40000000, 45000000, 145000000}", true)
c.NSeries[0].ValuesFormatCode = "$#,##0";
//If you want the number format should not be taken affect for value axis, please set to different number format.
//c.ValueAxis.TickLabels.NumberFormat = "";

Let us know your feedback.

13

The issues you have found earlier (filed as CELLSNET-45695) have been fixed in Aspose.Cells for .NET 17.10.

14

Yes. That works. Thank you very much.

15

@FireWave

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.