Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How To Set Cell Data Type

Tags:

types

cell

epplus

I am trying to set data type of cell, but it seems that's impossible to do with EPPlus.

If I place a number as value in cell, I get General data type in exported Excel file.

If set NumberFormat of Cells, for example:

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

Then I still will not get Date or Time. Exported Excel file will show cells as Custom data type.

I can get General or Custom data types only. I want to set Cells' data type to Time, but I can't. The problem is, if data type is not set, then for some reason Pivot table in other sheet is sorting numbers as strings :(

So, is there any way to set data type of cell please?

like image 369
BorisP Avatar asked Apr 06 '15 15:04

BorisP


People also ask

How do I change cell data type?

Select the field (the column) that you want to change. On the Fields tab, in the Properties group, click the arrow in the drop-down list next to Data Type, and then select a data type. Save your changes.

How do I create a datatype for a cell in Excel?

Go to the Formulas tab and select More Functions > Information > TYPE. Select a cell in the worksheet to enter the cell reference. Select OK to complete the function.

What is cell data type?

A cell value can be of one of the following types: empty, numeric, text, Boolean or error. Cell values may have various display formats. For example, a numeric value can be displayed as a decimal number, a percentage or currency value, a date or time value, etc.

How do I change the default cell format in Excel?

Click on the File tab. Select Options at the bottom left. Under General options there is a section for “When creating new workbooks.” Here you can select the option to change the font and font size for all new workbooks.


1 Answers

The number formatting gets a little weird with excel. Basically, it is a list of predefined strings so it does a match cell by cell. To see what that list looks like in EPPlus check out the ExcelNumberFormat.cs class in the source code.

But if you just need to have Excel "see" the cell as a certain type in the Number -> Number Format dropdown in the HOME ribbon, this should get you it:

[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();
    }
}
like image 93
Ernie S Avatar answered Oct 18 '22 09:10

Ernie S