Setting ShowValue doesn't seem to print values using the right currency format using Aspose.Cells in .NET

Luc_Charbonneau · November 1, 2016, 5:21pm

Hi,

When using the following

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
serie.DataLabels.Number = 5; //Currency rounded to dollar
}

on charts is there any reason why the printed values don’t follow the regional settings / CultureInfo?

I have everything set to French - Canada so the expected format would be
10 000 $

Yet I always end up with
$10 000

I’ve tried forcing everything to Canadian French on both
CurrentThread.CurrentCulture
and
workbook.Settings.CultureInfo

Yet it still won’t work, from what I can see by saving the Excel workbook Number = 5 forces a specific custom format of “$# ##0;($# ##0)” on the DataLabel

Which doesn’t work, I would expect to use the “Currency” category with 0 decimals instead which would work, is there any other way to achieve that?

I also messed around with workbook.Settings.Region and workbook.Settings.LanguageCode which still wouldn’t produce the expected results, by the way the type for LanguageCode seems weird (public CountryCode LanguageCode … ) some countries have more than one official language, Canada being one of them I’m not sure how the LanguageCode should be used then, I’m not sure it’s relevant here but I figured I’d mention it in case it is.

Worst case scenario I’ll force the format with DataLabels.NumberFormat but I’d rather follow the regional settings if at all possible.

Note that doing the same thing on a Cell works as expected, so it’s really just the DataLabel that seems to be handled in a different fashion.

Thank you for your help

PS: Here is the code snippet I used, I also attached the expected results and a print screen of what I’m currently seeing

System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo(3084, true); //French - Canada
System.Threading.Thread.CurrentThread.CurrentCulture = System.Threading.Thread.CurrentThread.CurrentUICulture;

Workbook workbook = new Workbook();
workbook.Settings.CultureInfo = System.Threading.Thread.CurrentThread.CurrentUICulture;

int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];

worksheet.Cells[“A1”].PutValue(2000);

Style s = worksheet.Cells[“A1”].GetStyle();
s.Number = 5;
worksheet.Cells[“A1”].SetStyle(s);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
serie.DataLabels.Number = 5; //Currency rounded to dollar
}

workbook.Save(“cultureIssue_FR_CA.xlsx”);

babar.raza · November 1, 2016, 11:28pm

Hi Luc,

Thank you for contacting Aspose support.

Please note, the DataLabels.Number property can accept an integer which corresponds to the built-in number formats provided by English version of Excel application. As your desired format is not one of the built-in formats so we suggest you to use the DataLabels.NumberFormat property which could accept a pattern in string format. Please check the following piece of code and its resultant spreadsheet as attached.

C#

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
// serie.DataLabels.Number = 5; //Currency rounded to dollar
serie.DataLabels.NumberFormat = “# ##0_)$;(# ##0 $)”;
}

Luc_Charbonneau · November 2, 2016, 9:11am

Hi,

I used “French - Canada” as an example as it’s one of the languages we use the most, but our application can be used in multiple languages and countries. In that context using the regional settings directly is preferable.

Using NumberFormat would force me provided specific formats for all of them which is not ideal.

It’s somewhat incoherent on your side that using the property number on a Cell with
Style s = worksheet.Cells[“A1”].GetStyle();
s.Number = 5;
worksheet.Cells[“A1”].SetStyle(s);

Works as expected but the same properly on another entity (DataLabels.Number) doesn’t

In the chart, the Y axis shows the values properly, it’s really just the DataLabel that doesn’t work as expected. It’s something you should probably improve in the future

Now as for my issue I guess I’m stuck providing all the different NumberFormat my application can use, not really what I was hoping for, but it’ll do for now.

Thank you

babar.raza · November 3, 2016, 1:45am

Hi Luc,

Thank you for the explanation.

I have performed more tests by changing the locale of my machine to French-Canada and I have noticed that if the code does not set the number format for the DataLabels separately, the culture set for the workbook takes effect for the Chart’s DataLabels as well. Please check the following piece of code and its resultant spreadsheet as attached. Please confirm if you are able to see the same results on French version of Excel application, and if they are acceptable.

C#

System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo(3084, true); //French - Canada
System.Threading.Thread.CurrentThread.CurrentCulture = System.Threading.Thread.CurrentThread.CurrentUICulture;

Workbook workbook = new Workbook();
workbook.Settings.CultureInfo = System.Threading.Thread.CurrentThread.CurrentUICulture;

int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];
worksheet.Cells[“A1”].PutValue(2000);
Style s = worksheet.Cells[“A1”].GetStyle();
s.Number = 5;
worksheet.Cells[“A1”].SetStyle(s);
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
// serie.DataLabels.Number = 5; //Currency rounded to dollar
}

workbook.Save(dir + “culture_FR_CA.xlsx”);

Luc_Charbonneau · November 3, 2016, 10:34am

Hi,

It works in Excel
workbook.Save(“cultureIssue_FR_CA.xlsx”)

but not when doing
chart.ToImage(“cultureIssue_FR_CA.png”);

Which I’ll need, I didn’t expect both results to be different…

For now I worked around the issue by using CurrentCulture.NumberFormat.Currency* elements, I still need to make some adjustments but overall I should be able to get it working.

It seems that unless I force the format on the cells (Style.Custom) and
datalabels (DataLabels.NumberFormat) I can’t get the right format when
using chart.ToImage (even for the cases where it shows up properly in
Excel)

I’ve attached updated code as well as outputs, in the excel files I added a printscreen of what I’m seeing.

babar.raza · November 4, 2016, 1:50am

Hi Luc,

Thank you for the explanation. I have logged an investigative ticket based on the fact that setting the culture for Workbook results correct format in spreadsheet format, however, saving the same chart to image does not show the desired results. Following is the ticket details for your future reference.

CELLSNET-44892: Culture settings for Workbook does not take effect for Chart’s DataLabels using Chart.ToImage

Please spare us little time to further investigate the matter and revert back with updates in this regard.

shakeel.faiz · November 6, 2016, 9:10pm

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-44892 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

shakeel.faiz · November 8, 2016, 3:38am

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.10.10.0 and let us know your feedback.

aspose.notifier · November 11, 2016, 4:53am

The issues you have found earlier (filed as CELLSNET-44892) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

Luc_Charbonneau · November 21, 2016, 2:27pm

The issue as provided as been fixed (when explicitly providing the style on all cells) but there is still one more context where I get the same issue

If I just use workbook.DefaultStyle = style; instead I still have the same issue where the excel worksheet and the generated ToImage file don’t match, the Excel File is OK and shows the numbers in the right format, but the PNG file does not, they should be identical.

I can work around the issue by manually setting the format on all the cells of my worksheet so it’s not critical but it’s still a bug that should be fixed in my opinion

System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo(3084, true); //French - Canada
System.Threading.Thread.CurrentThread.CurrentCulture = System.Threading.Thread.CurrentThread.CurrentUICulture;

Console.WriteLine("Aspose Version Info: " + CellsHelper.GetVersion());

Workbook workbook = new Workbook();
workbook.Settings.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;

int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];

worksheet.Cells[“A1”].PutValue(2000);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
}

Style style = workbook.CreateStyle();
style.Number = 5;
workbook.DefaultStyle = style;

chart.ToImage(“cultureIssue_FR_CA_Workbook.DefaultStyle.png”);
workbook.Save(“cultureIssue_FR_CA_Workbook.DefaultStyle.xlsx”);

EDIT:
Turns out that even setting the style on the cell doesn’t work completely either, the values displayed on the values axis are still problematic and I don’t think those can be set manually like the DataLabels

Note that I used Aspose.Cells 16.11.1.0 for these tests

I updated the test project with the lastest changes, for the most part I’d expect the result when using ToImage to be identical to what is shown in the Excel. All xlsx files are OK, the png images are not except for the first one “cultureIssue_FR_CA_01_WorkbookCultureInfoOnly.png”

shakeel.faiz · November 21, 2016, 11:14pm

Hi,

Thanks for your sample code and using Aspose.Cells.

We have tested this issue with the following sample code (which is same as yours) with the latest version:
Aspose.Cells for .NET v16.11.3 and found the issue. PNG image of the chart does not match with MS-Excel chart.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

CELLSNET-44926 - PNG image of the chart does not match with MS-Excel chart

I have also attached the output excel file and png chart image generated at my end for a reference.

C#

Workbook workbook = new Workbook(FileFormatType.Xlsx);
// workbook.Settings.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;

Style style = workbook.CreateStyle();
style.Number = 5;
workbook.DefaultStyle = style;

int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];

worksheet.Cells[“A1”].PutValue(2000);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
}

chart.Calculate();

chart.ToImage(“cultureIssue_FR_CA_Workbook.DefaultStyle.png”);
workbook.Save(“cultureIssue_FR_CA_Workbook.DefaultStyle.xlsx”);

amjad.sahi · November 24, 2016, 3:43am

Hi,

Please try our latest version/fix of Aspose.Cells for .NET as we have fixed your issue “CELLSNET-44926” now:

Aspose.Cells for .NET v16.11.5 for .NET 2.0 (attached)

Aspose.Cells for .NET v16.11.5 for .NET 4.0 (attached)

(please pick any fix/version for your underlying targeted .NET framework version)

Let us know your feedback.

Thank you.

Luc_Charbonneau · November 24, 2016, 12:23pm

Hi,

The issue is resolved for all the provided test cases except one,
I will use the other methods so for my uses the current version is sufficient

The remaining issue is when the following method is used to set the format

serie.DataLabels.Number = 5; //Currency rounded to dollar

I’ve attached the resulting files linked to this method as well of a printscreen of what I’m seeing in Excel

The .png image should match the prinscreen

If you want to reproduce the issue the regional settings of the computer should be set to French - Canada

Thank you

shakeel.faiz · November 24, 2016, 9:10pm

Hi,

Thanks for your posting and using Aspose.Cells.

I have found the issue as highlighted in the screenshot. Is this the issue you are facing? In the excel file $ is after 2000 but in the png image, it is before 2000. I have also changed settings to French(Canada) before the execution of the code.

C#

Workbook workbook = new Workbook(FileFormatType.Xlsx);

Style style = workbook.CreateStyle();
style.Number = 5;
workbook.DefaultStyle = style;

int sheetIndex = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[sheetIndex];

worksheet.Cells[“A1”].PutValue(2000);

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 10, 0, 20, 10);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add(“A1”, true);

foreach (Aspose.Cells.Charts.Series serie in chart.NSeries)
{
serie.DataLabels.ShowValue = true;
}

chart.Calculate();

chart.ToImage(“cultureIssue_FR_CA_Workbook.DefaultStyle-sha.png”);
workbook.Save(“cultureIssue_FR_CA_Workbook.DefaultStyle-sha.xlsx”);

Luc_Charbonneau · November 24, 2016, 9:27pm

That is the issue (dollar sign on the work side, and the thousand separator should be a space instead of a comma)

But make sure to use : 16.11.5.0, I think the code you wrote works OK on it, I’m no longer at work so I can’t validate but I tested earlier today and most of the cases I initially submitted are fixed the only issue that was left after 16.11.5.0 was when using

serie.DataLabels.Number = 5; //Currency rounded to dollar,

to explicity set the format on the datalabel and not the worksheet

The project in the CurrencyFormatOnChartVsCultureV2.zip file I posted in a previous post can be used to reproduce the issue, specifically the part that generates the cultureIssue_FR_CA_03_AllNumber5.png file

shakeel.faiz · November 24, 2016, 10:11pm

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this issue and logged it in our database for a fix.

This issue has been logged as

CELLSNET-44941 - Culture settings are not applying on output chart image

aspose.notifier · December 13, 2016, 4:41am

The issues you have found earlier (filed as CELLSNET-44926) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

shakeel.faiz · December 23, 2016, 3:30am

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.12.3 and let us know your feedback.

Please note the above fix is compiled with .NET 2.0, if you need the fix compiled with .NET 4.0, then please download Aspose.Cells for .NET v16.12.3 (.NET 4.0).

aspose.notifier · January 12, 2017, 4:53am

The issues you have found earlier (filed as CELLSNET-44941) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

aspose.notifier · February 7, 2019, 4:31pm

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan