How to set cell data type

Question

I am trying to set the cell data type, but it seems impossible to do with EPPlus.

If I put a number as a value in a cell, I get the general data type in the exported Excel file.

If NumberFormat cells are given, for example:

workSheet.Cells[range].Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss"; 

Then I still will not get the date or time. The exported Excel file displays the cells as custom data types.

I can only get generic or user data types. I want to set the Cells data type to Time, but I cannot. The problem is that if the data type is not defined, then for some reason the pivot table on another sheet sorts the numbers as strings: (

So, is there a way to set the data type in a cell, please?

Answer 1

Formatting a number is a little strange with Excel. Basically, this is a list of predefined rows, so it matches a cell by cell. To see what this list looks like in EPPlus, check the ExcelNumberFormat.cs class in the source code.

But if you just need Excel to “see” the cell as a specific type in the “Number → Number format” drop-down list on the HOME ribbon, this should do it for you:

 [TestMethod] public void Date_Format_Test() { //http://stackoverflow.com/questions/29473920/how-to-set-cell-data-type var existingFile = new FileInfo(@"c:\temp\temp.xlsx"); if (existingFile.Exists) existingFile.Delete(); using (var pck = new ExcelPackage(existingFile)) { var ws = pck.Workbook.Worksheets.Add("Content"); var date = DateTime.Now; //Raw date value as number ws.Cells["A1"].Value = date; //As "Short Date" ws.Cells["A2"].Value = date; ws.Cells["A2"].Style.Numberformat.Format = "mm-dd-yy"; //As "Time" ws.Cells["A3"].Value = date; ws.Cells["A3"].Style.Numberformat.Format = "[$-F400]h:mm:ss\\ AM/PM"; pck.Save(); } } 

Answer 2

Here are 49 formats listed in EPPlus / EPPlus / Style / ExcelNumberFormat.cs (located here) :

 "General" "0" "0.00" "#,##0" "#,##0.00" "0%" "0.00%" "0.00E+00" "# ?/?" "# ??/??" "mm-dd-yy" "d-mmm-yy" "d-mmm" "mmm-yy" "h:mm AM/PM" "h:mm:ss AM/PM" "h:mm" "h:mm:ss" "m/d/yy h:mm" "#,##0 ;(#,##0)" "#,##0 ;[Red](#,##0)" "#,##0.00;(#,##0.00)" "#,##0.00;[Red](#,#)" "mm:ss" "[h]:mm:ss" "mmss.0" "##0.0" "@" 

Answer 3

This tip is great for setting the cell number type in Excel, which I used

 worksheet.Column(1).Style.Numberformat.Format = "#,##0.000"; 

but I would really like to find some format table in EPPlus and cell types in Excel to get other Excel types like Currency, Percentage, etc.