This project has moved. For the latest updates, please go here.

Set cell format to "Date"

Nov 21, 2012 at 3:42 PM

This looks like a great library and really powerful. I have one requirement for which I've seen a variety of work-arounds suggested, but no solution.

When the end-user opens the workbook and navigates to the cell in question (say: E2), the formatting drop-down in the Number area of the ribbon must say "Date", not "General" or "Custom".

How do I do that?

Editor
Nov 21, 2012 at 4:15 PM

There is a list of all ( I think so ) built-in number formats for OOXML here > http://polymathprogrammer.com/2011/02/15/built-in-styles-for-excel-open-xml/

Just set any of them. Ex.:

 

            FileInfo  arq = new FileInfo(@"c:\test.xlsx");

            using (ExcelPackage p = new ExcelPackage(arq))
            {

                var ws = p.Workbook.Worksheets.Add("w1");

                ws.Cells[1,1].Value = DateTime.Now;
                ws.Cells[1, 1].Style.Numberformat.Format = "mm-dd-yy";

                p.Save();
            
            }

Nov 21, 2012 at 4:33 PM

Thank you, kindly. I had not realized that would do the trick -- it sure looks like a hack. I just confirmed that that does, indeed, show up as "Date" in the resulting Excel sheet.

Nov 22, 2012 at 11:39 AM

Thank you for posting this list. of built-in Excel styles.

I noticed that the Accounting format is not in this list, yet when I set the numberformat.format to the following string it does seem to match the Excel format for Accounting:

_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)

Reading his blog post, I wonder whether this will be available to users in other regions/localizations

Editor
Nov 22, 2012 at 11:51 AM

humm, I think all non custom styles are culture-variant. After run my example, when I opened it, Excel still displayed it as "dd-MM-yyyy" (brazilian format).

Feb 4, 2014 at 11:36 AM
Use this


worksheet.Cells[1,1].Formula = "=Date(" +DateTime.Now + ")";
worksheet.Cells[1, 1].Style.Numberformat.Format = "mm-dd-yy";
Feb 24, 2015 at 3:14 AM
Edited Feb 24, 2015 at 3:17 AM
If I'm using a reflected collection (e.g. via Worksheet::Cells.LoadFromCollection<T>), how do I specify the Date format for a column?

Or perhaps there's a way to specify a global format for all Date columns? e.g. yyyy-MM-dd.

Thanks.