EPPLUS how to know the format of the Worksheet cell

Question

I am using EPPlus to read excel sheets and load the data into datatable to perform further operations and then save back the modified data to the excel file.

The below code checks if the cell value is a float value then it converts the float value to datetime.

The code works fine when the cell value is a date eg: Invoice Date = 42009 , but it converts the not a date value like eg : amount = 10 to a date.

Is there any way in EPPlus library from which i can determine the format of the cell (i.e General/date/number etc) ?

float floatValue;

if (float.TryParse(Convert.ToString(oSheet.Cells[i, j].Value), out floatValue)
                                && Convert.ToString(oSheet.Cells[i, j].Style.Numberformat.Format).Contains("[$-409]d\\-mmm\\-yy;@"))
  {
      dr[j - 1] = String.Format("{0:d-MMM-yy}", DateTime.FromOADate(floatValue));
  }
  else
  {
       DateTime date;

       if (DateTime.TryParse(Convert.ToString(oSheet.Cells[i, j].Value), out date))
        {
                dr[j - 1] = String.Format("{0:d-MMM-yy}", date);
        }
        else
        {
               dr[j - 1] = Convert.ToString(oSheet.Cells[i, j].Value).Trim();
        }
   }

score 4 · Answer 1 · answered Feb 20 '15 at 13:58

The short answer is if the data in Excel is formatted as a "proper" date field (its format is set to Date and it does not have the triangle in the cell corner) EPPlus will determine that and automatically convert it to a date in the cell store.

Excel stores this information in the XLSX xml files, e.g. sheet1.xml and styles.xml, which you can see by changing the file extension to .ZIP and opening as a compressed folder. So EPPlus will read XML flags and convert the field values to DateTime automatically. If you want to see its gory detail download the EPPlus source code and look at the function private void SetValueFromXml(XmlTextReader xr, string type, int styleID, int row, int col) in the ExcelWorksheet class.

I created a sheet, added a date value, and copy pasted the value to cell B2 but then set the format to number. I then copied B2 to B3 but made the value in B3 a string by putting an ' in front of the value. Like this:

enter image description here

and when I run this unit test everything passes:

[TestMethod]
public void Date_Number_Test()
{
    //http://stackoverflow.com/questions/28591763/epplus-how-to-know-the-format-of-the-worksheet-cell
    var existingFile = new FileInfo(@"c:\temp\datetest.xlsx");

    using (var package2 = new ExcelPackage(existingFile))
    {
        var ws = package2.Workbook.Worksheets["Sheet1"];

        var cell = ws.Cells["B1"];
        Assert.IsTrue(cell.Value is DateTime);

        cell = ws.Cells["B2"];
        Assert.IsTrue(cell.Value is double);

        cell = ws.Cells["B3"];
        Assert.IsTrue(cell.Value is string);
    }
}

But if you are dealing with a sheet with formatting outside your control then I think your approach is correct in that you will have to determine what the "intent" of the numbers are rather then being able to rely on EPPlus (really excel) is reporting them as.

score 0 · Answer 2 · answered Mar 26 '15 at 16:50

0

if you want to know current cell format, why dont you use ExcelNumberFormat class?

var style=oSheet.Cells[i, j].style;
string format=style.Numberformat.Format;

This will give you current cell format, i.e, "YYYY-MM-dddd" , "%#.##", and so on.

answered Mar 26 '15 at 16:50

Javi bl

83
2
7

EPPLUS how to know the format of the Worksheet cell

2 Answers2

Linked