Free Support Forum - aspose.com

Excel Data Formating in Date

We are trying to set the Cell formating, basically our requirment is the cell have
Date selected as Category with Type : 'dd-MMM-yyyy' under the Number tab of
Formate Cell Dialog box

code used:
ws.Cells[trCount, tdCount].Style.Number = 15;
// For Date in d-mmm-yy
// still it showing the Category as Custome with type selected d-mmm-yy under the Number tab of
// Formate Cell Dialog box
//http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/list-of-supported-number-formats.html
Thanks in advance.....

This message was posted using Aspose.Live 2 Forum

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We have found your mentioned issue after an initial test. Your issue has been registered in our issue tracking system with issue id CELLSNET-12936.

Thank You & Best Regards,

hi,

Can you provide the direct link for CELLSNET-12936 that will help full..

Thanks again....

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, this id is of our internal issue tracking system (not directly available for users and customers) and is provided to you for reference against your issue. We will further update you about any update regarding your issue in this forum(against your current post).

Thank You & Best Regards,

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, after further looking into your issue we believe that it is a behavior of MS Excel in case you set the NumberFormat = 15 (d-mmm-yy). If you manually set the dateformat as ‘d-mmm-yy’ using the Date Format in MS Excel and then select the custom format, it will show the format as ‘[$-409]d-mmm-yy;@’. So, MS Excel displays NumberFormat =15 as Custom as it sets the format as ‘d-mmm-yy’ and not ‘[$-409]d-mmm-yy;@’.

Thank You & Best Regards,

Hi,

Thanks for reply, then can you provide me the piece of code that let do the followings
1. Value showing at grid in formate dd-mmm-yyyy .
2. After Excel export when we right on the cell -> Formate Cell -> then Category will be Date and Formate will be dd-mmm-yy

should it be possible from any ASPOSE Cell Property or any code piece.


Thanks in advance… :slight_smile:

Hi,

Well, for your need, you may try to use Custom date formatting, see the following sample code:
e.g
Workbook wb = new Workbook();
wb.Worksheets.Clear();
Worksheet ws = wb.Worksheets.Add(“New”);
Style style = wb.Styles[wb.Styles.Add()];
style.Name = “DateStyle”;
style.Custom = “dd-mmm-yyyy”;
ws.Cells[0, 0].PutValue(DateTime.Now);
ws.Cells[0, 0].SetStyle(style);
ws.AutoFitColumn(0);
wb.Save(@“e:\test\customdate.xls”);

Thank you.


Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Also, to show Category as Date, you may set Style.Custom = "[$-409]dd-mmm-yy;@" to get your deisred result. Please see the following sample code in this regard:

Workbook wb = new Workbook();

wb.Worksheets.Clear();

Worksheet ws = wb.Worksheets.Add("New");

Style style = wb.Styles[wb.Styles.Add()];

style.Name = "DateStyle";

style.Custom = "[$-409]dd-mmm-yy;@";

ws.Cells[0, 0].PutValue(DateTime.Now);

ws.Cells[0, 0].SetStyle(style);

ws.AutoFitColumn(0);

wb.Save(@"C:\Test_Temp\customdate.xls");

Thank You & Best Regards,