String Value and Style of Date Time Value is not same as what is set

Dear Aspose,


I wanted to get some display string of a given date (with custom style)

I am able to observe that if I put 31/12/2017
and set the style to custom (dd-mm-yy) in Excel

The result in code:
The display string value is 01-Jan-2017
and the style (CultureCustom) is dd-MMM-yy

I would like to ask if this result intended?

below is the code i used:
Workbook wb = new Workbook(“test date format.xlsx”);
Worksheet ws = wb.Worksheets[0];

Style style = ws.Cells[0, 0].GetStyle();

// Watch
//CultureCustom “dd-MMM-yyyy” string
// ws.Cells[0, 0].DisplayStringValue “01-Jan-2017” string

Please find the file I have used.

Many thanks,
Jonathan Lau

Hi Jonathan,


Thanks for using Aspose.Cells.

Dates & Number formats in Excel are divided into two categories.

1 - Built-in Formats (Style.Number will be >0)
2 - Custom Format (Style.Number will be 0)

If Style.Number is 0, then it means, you are dealing with Custom Format, in this case, you will use Style.Custom property.

You can always create Dates & Number formats in Microsoft Excel and then load them and check the Style.Number and Style.Custom properties in Debug or Quick Watch.

Please see the following sample code, its sample excel file and the output excel file and the console output. It should help you in dealing with issues related to Dates & Number formats.

When you will compare (i.e. see this screenshot) the sample Excel file with output Excel file, you will see the column B values looks exactly similar to column A values.

C#
Workbook wb = new Workbook(“sample.xlsx”);

Worksheet ws = wb.Worksheets[0];

Cells cells = ws.Cells;

for (int i = 0; i < 10; i++)
{
Cell c = cells[i, 0];

//Read cell number format
Style st = c.GetStyle();

if (st.Number == 0)
{
    Debug.WriteLine(c.Name + "----" + st.Custom);
}
else
{
    Debug.WriteLine(c.Name + "----" + st.Number);
}

//Assign the same style to column B cells
Cell c2 = cells[i, 1];
c2.SetStyle(st);

}

wb.Save(“output.xlsx”);


Console Output
A1----16
A2----14
A3----[$-F800]dddd,\ mmmm\ dd,\ yyyy
A4----10
A5----40
A6----40
A7----#,##0.000000_);[Red](#,##0.000000)
A8----([$$-409]* #,##0.0000);([$$-409]* (#,##0.0000);([$$-409]* “-”???);(@_)
A9----[$-409]h:mm:ss\ AM/PM;@
A10----000-00-0000

Hi shakeel,


Thank you very much for the quick response and sample code.

I understand I can find the input style and apply them to another cell.
However, sometimes I will need the display string only and to put it into other strings.

And then everything works if I choose directly from the date category under cell Formatting,
but when I select (or type) a new custom style,
say like the one I attached in this reply, I am seeing display string value that is different from what I expected.

Or is this something that is related to my machine location/region?


Many thanks,
Jonathan

Hi,


Thanks for using Aspose.Cells.

Yes, these things depend upon your Control Panel > Region and Language settings. (see the screenshot)

Besides, you can also programmatically control them by setting the Workbook.Settings.Region property.