How to set the format of a cell to be MM/DD/YYYY - NOT LOCALIZABLE

[breezback]

I need to format the column to a specific format.
I tried this :

// Set format date
colStyle = mySheet.Cells.Columns[colIndex].Style;
colStyle.Number = 14;
colStyle.Custom = “mm/dd/yyyy”;

The bug is, my client is UK. When he types in a date inside the formatted column for example: 20/04/2001 then excel converts it to 04/20/2001
meaning that the text and the value are different.

But if the client enters 04/20/2001, it seems that the cell is not well-formatted even if the format style is custom dd/mm/yyyy.

2 tests to make:

1. check that if you enter 02/25/2007 in the cell, it’s really the value entered there (check that the value equals the text of the cell).
2. if you drag the cell corner to the cells below it should extend a range of dates like this:

02/25/2007
02/26/2007
02/27/2007
02/28/2007
03/01/2007

In my scenario, the client has U.K regional setting (which is not MM/DD/YYYY but DD/MM/YYYY)
I wish that its regional settings wouldn’t infer in my formatting choice.

Thanks,

[Amjad_Sahi]

Hi,

I am not very clear about your need. Are colStyle.Custom = "mm/dd/yyyy"; or using Number equal to 14 not fit for your need.

Could you elaborate to explain what's wrong with Aspose.Cells generated files which have date formatted cells and comparing it with MS Excel file when you manually create a workbook in MS Excel to format some cells with your required date formats. Do the two results different in any way? Could you provide some example with code. Normally Aspose.Cells works in the same way as MS Excel does.

Thank you.

[breezback]

I’m sorry it seems that the problem is Excel’s when the format set on the column differs from the regional setting. Excel will alter the input and convert it to present it as format choosen.

So if I’m UK and the format on the cell is mm/dd/yyyy (set via Aspose or NOT)
If the user enters 20/04/2001 then the text will show 04/20/2001 but the value will be 20/04/2001

Could you just show me a sample of how I choose set the style to format it as mm/dd/yyyy or dd/mm/yyyy. Just to make sure I’m using Aspose correctly.

The rest is up to Microsoft.

[Amjad_Sahi]

Hi,

Following are some sample codes:

1). To format some cells.

Workbook workbook =new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Style style = workbook.Styles[workbook.Styles.Add()];
style.Custom = "dd/mm/yyyy";
cells["A1"].PutValue ("Name");
cells["A2"].PutValue ("Ali");
cells["A3"].PutValue ("Akram");
cells["A4"].PutValue ("Mike");
cells["A5"].PutValue ("Arnold");

cells["B1"].PutValue ("DOB");
cells["B2"].PutValue ("03-12-75");
cells["B2"].Style = style;
cells["B3"].PutValue ("12-03-73");
cells["B3"].Style = style;
cells["B4"].PutValue ("11-11-80");
cells["B4"].Style = style;
cells["B5"].PutValue ("10-11-72");
cells["B5"].Style = style;

workbook.Save("d:\\test\\out_book.xls");

2). to format the whole column i.e. first column

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Style style;
StyleFlag flag = new StyleFlag();
style = sheet.Cells.Columns[0].Style;
style.Custom = "mm/dd/yyyy";
flag.NumberFormat = true;
sheet.Cells.Columns[0].ApplyStyle(style, flag);
workbook.Save("d:\\test\\colnumberformat.xls");

Thank you.

[breezback]

What is StyleFlag true? It is related to the localization?

[Amjad_Sahi]

Hi,

Well StyleFlag is a struct normally used when you want to retain previous formattings of cells and want to add a few more style attributes or formattings attributes of your choice only.

Thank you.