Excel chart not showing Swiss thousand Separate value

Aspose.Cells Product Family
1

Hi,


I am creating chart with the values from DB using Aspose.,

Here, I am using Swiss value (10’000.00 - with single quote as thousand separator) to draw chart in Excel.,But the chart is not getting read the value.

If other format (10,000.00 - with comma separator it is working)

Kindly inform me how to format the value and lake chart using Swiss value.,


2

Hi Thiri,


Thank you for contacting Aspose support.

First of all, please note that Aspose.Cells APIs do not provide the ability to establish a connection with database or pull data to populate worksheets. You must be fetching and holding the data in some temporary container before importing it to worksheet, and it is possible that the number formatting of the data has been disturbed in this phase. It would be of great help in understanding your presented scenario if you can provide us an executable standalone sample application along with its dependencies (input/output spreadsheets, assemblies & other resources) to show the problem. We will check it soon to assist you further in this regard.
3
Hi.,

I do store the value from DB in a temp container and draw the chart., That is ok.,

The chart is coming fine with the thousand separator (,) value eg: 10,000.00.,

But the chart does not plot the value for this value eg: 10'000.00 ( Single quotes as thousand separator).

I have attached the sample out put excel which plot chart based on the record.,

But the value highlighted in red does not plot in chart area.,

Thanks in advance


4

Hi again,


Thank you for the sample spreadsheet. Please note, you can assign the desired format to the chart’s axis labels. Please check the following piece of code which applies the custom format #’##0.00 to the Value Axis labels.

C#

Workbook workbook = new Workbook(@“sample+op.xlsx”);
Worksheet sheet = workbook.Worksheets[“Sampe OP”];
Chart chart = sheet.Charts[“Chart 1”];
chart.ValueAxis.TickLabels.NumberFormat = “#’##0.00”;
chart.Calculate();
workbook.Save(@“output.xlsx”);
5

Hi,

Thanks ., But that is not what i am looking for.,

The chart is not generated for the type ‘Expat’ for this case being the thousand separator value having single quotes., (Currency -Swiss franc)

The code you mentioned will do only for Axis value., And that too will fail for the case, if the value less than 1000. Eg: '750.00 (Leading single quotes for less than 1000 value)

Here, I am looking for to plot the chart based on the values present in the table in excel.,
The values having single quotes as thousand operator which could not produce the chart.,

Please revert for more information., if needed




6

Hi again,


Please share your expected results, that you may create manually using Excel application and share it here for elaboration purposes. If you can achieve your desired results using Excel, we can mimic the same with Aspose.Cells APIs.

Regarding the pattern to show single quote as thousand separator, I have looked further into it during the weekend, and unfortunately, I was not able to find/devise a custom pattern that could eliminate the leading single quote(s) if value is smaller than thousand under US regional settings. However, as discussed earlier, you can handle it in Excel by overriding the thousand separator settings as discussed here as well as using WorkbookSetting.NumberGroupSeparator property in code.

7

Hi.,


I have enclosed the Excel with sample value.,

Please check it once.,

8

Hi Thiri,


Thank you for sharing the desired results.

Please check the attached book1.xlsx containing the desired data as well as the following piece of code which make use of book1.xlsx to create the desired chart with correct number formatting. Please note, I have set the custom separator in Excel application as shown in the attached snapshot whereas the code sets the number format to the range B2:C5 as #,##0.00. However, when final spreadsheet is viewed in Excel the data representation is desired, that is; single quote is placed as thousand separator. Moreover, the value axis labels do not show the leading single quote if value is less than thousand.

C#

var book = new Workbook(@“book1.xlsx”);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
var style = book.CreateStyle();
style.Custom = “#,##0.00”;
cells.CreateRange(“B2:C5”).ApplyStyle(style, new StyleFlag() { NumberFormat = true });
var index = sheet.Charts.Add(ChartType.Column, 11, 1, 42, 22);
var chart = sheet.Charts[index];
chart.SetChartDataRange(“A2:C5”, true);
chart.NSeries[0].Name = “Expat”;
chart.NSeries[1].Name = “Local”;
book.Save(@“output.xlsx”);