Regional settings / differenes when adding data series to charts

tokebreer2 · February 22, 2011, 6:36am

Need to build quite a few “quick and dirty” charts in aspose.excel,
so we wanne add the data directly to the chart without using ranges.

The machine has XP and regional settings “Danish”.
This means
Decimal separator is “,”
Thousand separator is “.”
Separator in Excel in formulas is “;”

So for example a of a sum funtion looks like this: “=SUM(E15;D19;B23)”

In Excel I can add a dataset to a chart like this:
Exampe 1
=SERIES(“MySeriesWithIntegers”;{1\2\3};{10\20\35};1)

Example 2 (notice we now use numbers with decimals)
=SERIES(“MySeriesWithDecimals”;{1,5\2,5\3,5};{10\20\35};2)

Notice the the separator between the values in a series is now “" !!! (a semicolon can also be specified, but Excel changes it to a “")
The examples above works in Excel.

Now I wanna rebuild the same chart from Aspose, but this f***** up.
The example with the intergers works (like this:
”={1,2,3}”
ect.

but if I wanna reproduce example 2, where I use a comma (“,”) as the decimal separator, Excel treants i.e. “1,5” (which is one-point-five in DK) as two values (1 and 5).

Is I use a period (“.”) as the decimal separator, Excel treants i.e.
“1.5” (which is 15) as as one value - its now respecting the danish settings, where “.” is thousand separator.

If I use “;” as the separator between the individual values from aspose , say like
chartSeries.XValues = "{1,5;2,5;3,5};
I get errors. The same happens if I use "" as the separator (yes, I have escaped it properly).

Have tried messing around with the workbook.settings.LanguageCode and Regional stuff, but Danish doesnt appear. Using german, which has the same decimal separator and thousand separator doesnt solve thing.

Bottom line is, that its not possible for me to add decimals numbers to a chart

Suggestions?
Kind regards,
Toke

NB: Have alsoed tried stuff from

Problems setting explicit string values for the NSeries.CategoryData - #2 by amjad.sahi - Free Support Forum - aspose.com

which I can make work - but not my example(s) above.

amjad.sahi · February 22, 2011, 8:10am

Hi Toke,

I am afraid, you have to set your formulas in US English style as Aspose.Cells only follows it while setting formulas. So, please change your code accordingly for setting the formulas in English (US Style/Settings)

For reference, check the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-formulas-notice-for-nonenglish-users.html

Thank you.

tokebreer2 · February 22, 2011, 11:11am

I've alrdeay tried that :)

It works with the comma (",") instead for the semicolon for normal formulas - BUT - for the charts, where a series of values is inserted in a chart

SomeSeries.XValues = "{1.2, 3.5}";

Above is not working with DK regional settings - try youself to see :)

Any fixes?

:)

toke

amjad.sahi · February 22, 2011, 11:36am

Hi,

Thanks for providing further details and feedback.

I have logged your feature request (regarding Danish Regional Settings regarding chart series, formulas) into our issue tracking system with an id: CELLSNET-24674. We will analyze and check if we can add Danish setting in CountryCode enumeration.

Thank you.

amjad.sahi · February 22, 2011, 11:40am

Hi,

By the way could you apply German settings if it can solve your issue for the time being. e.g
Workbook workbook = new Workbook();
workbook.Settings.LanguageCode = CountryCode.Germany;
workbook.Settings.Region = CountryCode.Germany;
//…

And, we will continue to look into your issue as we already logged it into our issue tracking system.

Thank you.

tokebreer2 · February 23, 2011, 2:50am

Already tried that before I sent the first mail, that didnt work either

But just to be sure we got things right:

XP stays on regional setting DK (or German), but workbook is made with the settings above (German). If so, I have tried, and it didnt work either.

The values in the C# code sent to aspose is (dumped from the QuickWatch windows):
chartSeries.Values “{142,142,8}” string
This is supposed to be two values (142 and 142.8) but if period is used, its interpreated as thousand separator.

A sceendump from excel is also attateced which shows the the above is interpreated as three values (142, 142 and 8), not two

The problem is I need to able to supply another field separator to aspose depending on culture FOR CHARTS ONLY (for the normal formulas, comma works flawless)… For DK, I have to be able to use “” or “;”

Toke

amjad.sahi · February 23, 2011, 7:36am

Hi,

Thanks for providing us further details.

We will analyze your issue which is already logged (as you know).

Thank you.

amjad.sahi · February 24, 2011, 3:26am

Hi,

Please try Aspose.Cells for .NET v5.3.0.1. We have fixed your issue now.

Let us know if it works fine for you accordingly.

Thank you.

tokebreer2 · February 24, 2011, 7:07am

Doesnt seem to work - get
={-1.#QNAN,-1.#QNAN}
for two values with decimals.

Just to be sure:
In the Workbook, which values should I use for
Settings.CountryCode
Settings.Region
Field separator? (semicolon, comma, backslash)

And which culturinfo should I then use while formatting to make this work? (that means, which decimal separator ?)

tokebreer2 · February 24, 2011, 9:46am

Btw, to ease your debugging:
(XP Pro, VS2010, da-DK settings).

When I assign to a NSeries like this:
chartSeries.XValues = string.Format("{{{0}}}", string.Join(fieldSeperator, xVals));
the of the string.format expression itself evalutates to this in the immediate window: (just for the experiment, the values in the array are formatted to en-US)
string.Format("{{{0}}}", string.Join(fieldSeperator, xVals)) “{94.1,104}” string
Plz notice the period after 94.

BUT
if I, one line furher down evaluate via the quickwatch feature in VS I get:
chartSeries.XValues “{94,1,104}” string
Notict the comma after 94!

Could this be why problems arise? It seems like Aspose re-formats the assigned string value when returning it in VS ? If so, Aspose sees a different amount of numbers depending on whether any of them has decimals or not…

The mystery continues
Toke

amjad.sahi · February 25, 2011, 2:30am

Hi,

Thanks for your feedback.

We did notice the issue you have mentioned. We will figure it out very soon.

thank you.

amjad.sahi · February 25, 2011, 5:12am

Hi,

Please try the Aspose.Cells for .NET v5.3.0.2 , we have fixed your issue.
The XValues should return “{94.1,104}” string now.

Could you try it and give us your feedback.

Thank you.

tokebreer2 · February 25, 2011, 8:11am

Not working - not getting errors while assisning values in C# code, but the chart is still not displaying correctly - a few times Excel wont even open

Tried

Workbook.LanguageCode and region set to german, Culturinfo set to
german when calling ToString on numbers. Decimal separator is hence “,”,
thousand separator is “.”, field separator when assisning to chart
object: “;”

Values are as expected when assigning to chart object.

Excel gives messagebox "Excel found unreadable content in
“Spreadsheet.xlsx”. Do you want to recover the contenst of this
workbook?

If “yes” is chosen, the entire chart is removed (along with a messagebox which links to an xml file about which parts have been removed - which is of no help)
Exchanged the field separator from “;” to “/” (properly escaped). Same result as above

3)

Workbook.LanguageCode and region set to USA, Culturinfo set to en-US when calling ToString on numbers. Decimal separator is hence “.”,
thousand separator is “,”, field separator when assisning to chart
object: “,”

Values are as expected when assigning to chart object - but not when looking at them in the immediate window. Here two values 142 and 142.8 becomes “{142,142,8}” (notice that the point-eight has become comma-eight.

Once again Excel complaints when opening (samme message as before), but the chart is kept in the workbook. Looking at the datasat for the series for the chart, the 142 and 142.8 becomes:
={142,-1.#QNAN}

FYI, its running on a Danish machine, so the CI on the thread saving the workbook to disc is danish! Could this cause problems?
Dump of my threading info:;
{System.Threading.Thread}
base {System.Runtime.ConstrainedExecution.CriticalFinalizerObject}: {System.Threading.Thread}
ApartmentState: STA
CurrentCulture: {da-DK}
CurrentUICulture: {en-US}
ExecutionContext: {System.Threading.ExecutionContext}
IsAlive: true
IsBackground: false
IsThreadPoolThread: false
ManagedThreadId: 9
Name: null
Priority: Normal
ThreadState: Running

Of course, when opening up the worksheet in Excel, its also done on a danish thread.

Toke

tokebreer2 · February 25, 2011, 8:58am

Just to try some more things out I tried the following:
1) Made a simple one-sheet excel file (in excel), which holds a chart with the values entered directly.
The formula bar then shows:
=SERIES(“Test”;{1\2,5\3};{10\20\30,5};1)

The chart is displayed correctly.
If I open workbook in aspose, the data show are:
{1,2,5,3}
This is an error, since we now have 4 values instad of 3 (1, 2.5 and 3)

If I resave the workbook under another name, Excel complains the same way as in the earlier post (compaints about data when opening)

He’res the funny part however: If I reopen the original Excel file in Excel, Excel also f****s up!!!

Really annoying.

Q: Would the Workbook.Settings.ConvertNumericData make a difference?

amjad.sahi · February 25, 2011, 11:33am

Hi,

Thanks for your feedback.

We will get back to you soon.

Thanks for being patient!

shakeel.faiz · February 27, 2011, 8:50pm

Hi,

The formula and chart data source only support US Culturinfo. We will look into how to support different separator in formulas and chart data.

We are trying to support other Culturinfo in Cell.StringValue, so it will return string value according to region Worbook.Settings.Region.

1), 2), 3)

a) If chart data source could refer to the cell, please simply set the data source as “A1:A10” or (A1,B3) and check the following codes:

b) If chart data source is constant value, Please simply use Cell.DoubleValue, not use Cell.StringValue.

See following code:

Workbook workBook = new Workbook();

Cells cells = workBook.Worksheets[0].Cells;

cells[“A1”].PutValue(4.5);

cells[“A2”].PutValue(5.6);

int index = workBook.Worksheets[0].Charts.Add(ChartType.Column, 0, 0, 10, 5);

Chart chart = workBook.Worksheets[0].Charts[index];

StringBuilder sb = new StringBuilder();

sb.Append(“{”);

sb.Append(cells[“A1”].DoubleValue.ToString(System.Globalization.CultureInfo.InvariantCulture));

sb.Append(“,”);

sb.Append(cells[“A2”].DoubleValue.ToString(System.Globalization.CultureInfo.InvariantCulture));

sb.Append(“}”);

chart.NSeries.Add(sb.ToString(), true);

workBook.Save(@“D:\FileTemp\dest.xls”);

4) We now have 4 values instead of 3 (1, 2.5 and 3)

Please post your template file.

5) Would the Workbook.Settings.ConvertNumericData make a difference?

It could not help anything.

It’s used to convert a string value to a number value.

6) If MS Excel could not open the generated file, please post it here. We will check it soon.