Text function doesn't recognise localized format_text

Aspose.Cells Product Family
1

On a machine that has Regional Settings and Location set to something different then English/US, for instance French/France do the following steps:

Create an Excel Object;
In cell A1 write a date ( E.G. 30/06/2013 );
In cell A2 add a formula like ‘=texte(A1,“jj/mm/aa”)’ - This will generate a date format with the value “30/06/13”;

The text rendered by Aspose.Cells for the cell “A2” is "jj/Julliet/aa"as opposed to the expected one.
I’ve attached the Date.xlsx file and the following piece of code to reproduce the issue:

var wb = new Workbook(@“C:\Date.xlsx”);
wb.CalculateFormula();

Console.WriteLine("A1: " + wb.Worksheets[0].Cells[“A1”].StringValue);
Console.WriteLine("A2: " + wb.Worksheets[0].Cells[“A2”].StringValue);
Console.WriteLine("A3: " + wb.Worksheets[0].Cells[“A3”].StringValue);

Please advise,

Lucian Nistor

2

Hi Lucian,

Thanks for your posting and using Aspose.Cells.

The formula in cell A2 is =Text(A1;“jj/mmmm/aa”) and it is not =Text(A1;“jj/mm/aa”) as you can see in the screenshot.

The formula in cell A3 is =Text(A1;“dd/mmmm/yy”) as you can see in screenshot but MS-Excel could not evaluate it.

I have set the local settings to French(France) and took these screenshots.

Aspose.Cells can recognize mm or mmmm but it cannot recognize jj and aa.

Aspose.Cells can only work with US setting of date formats.

Both MS-Excel and Aspose.Cells cannot recognize the local formulas. As you can see, MS-Excel is not able to evaluate the formula in cell A3. Once, you change the settings to English(United States), MS-Excel could not evaluate the formula in cell A2.


3

Hello,

I’m adding more details to highlight the actual problem. The issue occurs when running Aspose on a French Environment, i.e. Microsoft Windows in French and Microsoft Office in French.

I’ve attached:
- 2 print-screens showing the Microsoft Windows (French) Regional and Language Settings - note the “j” and “a” markers from the Date settings, and the French Office language settings
- a print-screen showing how Excel calculates the TestFile.xlsx, showing it does correctly understand the “j” and “a” OS regional and language settings
- a zip containing a console application which we’ve tested on both EN and French environments
- 2 print-screens with the outpus of both of the runs of the console app.

The problem is that on the French environment, Aspose does not seem to correctly understand the “j” and “a” OS regional and language settings when performing CalculateFormula.

As you can see in the test console app, we’ve tried setting the FR CultureInfo before performing the last CalculateFormula but the result is the same:
- for A2 it produces “jj juillet mercredi”, instead of “30 juillet 2014” as Excel calculates in French environment
- for A3 it produces “jj/07/aa”, instead of “30/07/14” as Excel calculates in French environment.

The TestFile.xlsx used is present in the AsposeIssue.zip archive.

We are using Aspose.Cells version 7.7.2.0.

Any feedback will be highly appreaciated.

Thank you,
Mihai Andrei
Sr Software Engineer
IBM Romania

4

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

When I open your source file into MS-Excel in US settings, then I get the same results as given by your console application. So, Aspose.Cells works in US settings. Please see the screenshot for your reference.

However, I have logged an investigation ticket for this issue in our database. We will look into it in detail and provide you a feedback. Once, there is some update for you, we will let you know asap.

Screenshot:

5

Hello,

As per my previous post’s screen-shots, you can easily acknowledge that Aspose.Cells behaves different than Excel, when running on a machine where Microsoft Office and Microsoft Windows are installed with other than US language packs.

We are looking forward for your analysis, we need some feedback in order to be able to respond to our French customer regarding this issue.

Thank you,
Mihai Andrei

6

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

Sure, once we will have some update for you, we will let you know asap.

7

Hello,

Can you please let us know the number for the investigation ticket you are referring, we need to log it in our bug tracking system.

Thank you,
Mihai

8

Hi,


Well, the ticket id is: “CELLSNET-42465”.

Thank you.
9

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells
for .NET v7.7.2.6 and let us know your feedback.

10

Hello,

We’ve tested against the v7.7.2.6 version, and the TEXT function seems to behave correctly on a French environment (OS/Office).

We’ve also successfully tested against Aspose.Cells version 8.0.0.0. Can you confirm the fix is also included in this version ?

Thank you,
Mihai Andrei

11

Hi Mihai,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your testing results are good with the latest fix. Yes, these fixes will also be included in the v8.0.0.0. All newer versions include the fixes of older version.

Let us know if you encounter any other issue, we will be glad to look into it and help you further.