DateTime Culture Info not respected

Aspose.Cells Product Family
1

When I create a new workbook with LoadOptions those load options don’t appear to be properly respected.

LoadOptions wbLoadOptions = new LoadOptions() {
CultureInfo = new System.Globalization.CultureInfo(“en-AU”),
LanguageCode = CountryCode.Australia,
Region = CountryCode.Australia
};

var sourceWb = new Workbook(“c:\temp\test.xls”, wbLoadOptions);

sourceWB.sourceWb.Worksheets[0].Cells[“A2”]

{Aspose.Cells.Cell [ A2; ValueType : IsDateTime; Value : 3/26/2019 ]}
: 0
: {Aspose.Cells.Row}
: { }
: false
: {Aspose.Cells.Cells}
BoolValue: ‘sourceWb.Worksheets[source].Cells[“A2”].BoolValue’ threw an exception of type ‘Aspose.Cells.CellsException’
Column: 0
ContainsExternalLink: false
DateTimeValue: {26/03/2019 12:00:00 AM}
DisplayStringValue: “3/26/2019”
DoubleValue: 43550
FloatValue: 43550
Formula: null
FormulaLocal: null
HtmlString: “<Font Style=“FONT-FAMILY: Calibri;FONT-SIZE: 11pt;COLOR: #000000;”>3/26/2019”
IntValue: 43550
IsArrayFormula: false
IsArrayHeader: false
IsErrorValue: false
IsFormula: false
IsInArray: false
IsInTable: false
IsMerged: false
IsSharedFormula: false
IsStyleSet: true
IsTableFormula: false
Name: “A2”
NumberCategoryType: Date
R1C1Formula: null
Row: 1
SharedStyleIndex: 63
StringValue: “3/26/2019”
StringValueWithoutFormat: “43550”
Type: IsDateTime
Value: {26/03/2019 12:00:00 AM}
Worksheet: {Aspose.Cells.Worksheet[ 26032019 ]}

As you can see even though I have set all the LoadOptions to Australia the StringValue of the date has the day and the month inverted (US Format). What am I missing?

2

@SimonT,

Please share your sample file with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

test.zip (7.3 KB)

4

@SimonT,

I have changed settings of my system including Region, Regional Format and Locale Settings to English (Australia) format with which MS Excel displays the values as 26/03/2019 in Column A. When I use below code then “26/03/2019” is displayed on console which is the same format as MS Excel.

LoadOptions wbLoadOptions = new LoadOptions()
{
    CultureInfo = new System.Globalization.CultureInfo("en-AU"),
    LanguageCode = CountryCode.Australia,
    Region = CountryCode.Australia
};
var sourceWb = new Workbook("test.xls", wbLoadOptions);
Console.WriteLine(sourceWb.Worksheets[0].Cells["A2"].StringValue);

I have tested it with Aspose.Cells for .NET 19.3.2. Can you please check with this latest version and let us know your feedback.

Aspose.Cells19.3.2 For .Net4.0.Zip (4.9 MB)
Aspose.Cells19.3.2 For .Net2.Zip (4.8 MB)
Aspose.Cells19.3.2 For .Net2_AuthenticodeSigned.Zip (4.8 MB)

5

@ahsaniqbalsidiqui

Thank you for the quick response, my settings are all already set to Australia as you would expect and when I open the workbook the date is displayed in the same format. I am using Apose.Cells v 18.8 though so perhaps I can try upgrading to 19.3.2 and see if that fixes the issue.

6

@ahsaniqbalsidiqui

I tried upgrading to 19.3 via NuGet package manager but I run into dependency issues System.Drawing.Common now and can’t use it?

7

@ahsaniqbalsidiqui

I’ve been able to better reproduce the issue. I gave you a file that doesn’t reproduce the issue apologies. Please take the file attached and don’t open it or save it. It should print out the date in the American format. If you then open the workbook and save it with your locale set to Australian and run the code again it should produce the date correctly. It seems the problem is related to maybe the locale the workbook was produced on in the 1st place?
test.zip (56.1 KB)

8

@SimonT,

I have tried this sample file (test.xls without opening/saving it ) using your sample code but afraid to share that I could not reproduce the issue yet. It is showing me the date in Australian format as mentioned in the LoadOptions settings given below:

Aspose.Cells.LoadOptions wbLoadOptions = new Aspose.Cells.LoadOptions()
{
    CultureInfo = new System.Globalization.CultureInfo("en-AU"),
    LanguageCode = CountryCode.Australia,
    Region = CountryCode.Australia
};

When I set the LoadOptions to USA (given below), your code displays American format date as expected.

Aspose.Cells.LoadOptions wbLoadOptions = new Aspose.Cells.LoadOptions()
{
	CultureInfo = new System.Globalization.CultureInfo("en-US"),
	LanguageCode = CountryCode.USA,
	Region = CountryCode.USA
};

You may please try this scenario again with latest version mentioned above and share your feedback.

Regarding the dependencies issue, you may please install System.Drawing.Common via NuGet package manager as it is required now in newer versions.