Cell date format globalization issue

We’ve found some cells with date value and
custom date formats were not formatted as expected.
This strange behavior is seen when non-us-ascii character
is used in the custom format string.

Attached is a sample xls file,
and a preview image generated by Aspose.Cells.NET.
You’ll see some differnces between expected (excel) format and
the actual (aspose) format.

It seems that you have your original formatting code ,
and don’t use the stock function provided by Microsoft
in some cases, (maybe in East-Asian locale ?)

Will you check if you can make use of Microsoft’s
globalized date formatting mechanism ?
http://msdn.microsoft.com/en-us/goglobal/bb688124.aspx


Thank you.


Hi
Kunikazu,


Thank you for using Aspose.Cells.

Can you please share your simple runnable code with us to reproduce the issue? Also, please download and try this latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback. We will investigate the issue as soon as possible and assist you further. We appreciate your cooperation in this regard.

Hi Kashif,

Thank you for your reply.

I tried Aspose.Cells for .NET v7.2.2.3, and attached result data .

Would you please check this file ?

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version)

You need to set the region of the workbook as Japan if it is not your default locale for running your application.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version)


Thank you for your quick fix. It works fine, now we are happy enough.

BTW,
I’ve added some test cells with other east asian locale .
I can’t say it is correct or wrong .
you’d better have it checked by natives .

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is now resolved. If you get any other question or face some issues, please feel free to post, we will help you asap.

Hi,


Can you post an example on how to set locale ID, so the datetime format of a column or cell use Chinese short date format. I was trying set cultureinfo for the workbook, but it failed to format the data as expected. Here is the code I used:

Workbook wb = new Workbook();
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo(2052);
wb.Settings.CultureInfo = ci;
wb.Worksheet.Cells[“O2”].PutValue(DateTime.Now);

Style style = wb.Worksheet.Cells[“O2”].GetStyle();

style.Number = 14;

//Applying the style to the cell
wb.Worksheet.Cells[“O2”].SetStyle(style);
wb.Save(FileName + “.xlsx”, SaveFormat.Xlsx)


Thanks in advance for your help.

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use the following code and it should work fine. Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)

I have attached the output xlsx and the output pdf file for your reference.

C#


Workbook wb = new Workbook();

System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo(2052);

wb.Settings.CultureInfo = ci;


wb.Settings.Region = CountryCode.China;

wb.Settings.LanguageCode = CountryCode.China;


wb.Worksheets[0].Cells[“O2”].PutValue(DateTime.Now);


Style style = wb.Worksheets[0].Cells[“O2”].GetStyle();


style.Custom = “m/d;@”;

style.CultureCustom = “m/d;@”;


//Applying the style to the cell

wb.Worksheets[0].Cells[“O2”].SetStyle(style);


wb.Save(“output.pdf”, SaveFormat.Pdf);

wb.Save(“output.xlsx”, SaveFormat.Xlsx);



hi
Shakeel,


Thank you so much for the quick reply. For the short date display in Chinese format, here is what it should look like if I set locale to Chinese(PRC) in Excel . Can we use Aspose.cells to achieve that?

2012年9月17日

Thanks!

Hi,

Thanks for using Aspose.Cells for .NET.

I think, the above code was perfect, once you will open the output xlsx file in Chinese environment, it will show you date format in the Chinese format.

However, if you explicitly want to set the date format in Chinese settings, then try the following code.

I have attached the output xlsx file and the screenshot for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A1”];


cell.PutValue(DateTime.Now);


Style style = cell.GetStyle();

style.CultureCustom = “yyyy"年"m"月"d"日”;@";

style.Custom = “yyyy"年"m"月"d"日”;@";

cell.SetStyle(style);


worksheet.Cells.SetColumnWidth(0, 20);


workbook.Save(“output.xlsx”);

Screenshot:

hi
Shakeel


Thanks for the help. If I understand it correctly, if I want to show the datetime in other langugage format using Aspose.cells for excel export, while my computer has English environment, I have to use the custom style, instead of the style.Number property, right?

Thanks again for your response.

Feizhi
Hi,

Yes you are right.

Commonly the built-in formattings are locale-depended. You can confirm this mannually in ms excel. Such as choose the formatting as the simple date (built in number 14), and save the excel file. Then open this file in ms excel with different locale settings, you will get different formatted values in ms excel(the symbol and sequence of year/month/day).

To specify the fixed characters to format the cell value, you should use custom formatting string to quote the special characters you want to embed.