Search code examples
c#excelvbaepplus

Set Column Data Format using EPPLUS in C#

Problem

When I export a Microsoft Excel spreadsheet using the EPPLUS library I need to set both the column's data format as well as the cell format such that the resultant dates in the Microsoft Excel spreadsheet can be recognized by Microsoft Excel's formulas that deal with date and time.

What was tried

I can set the cell's format easily using:

currentWorkSheet.Cells["L" + currentRowNumber.ToString()].Style.Numberformat.Format = "m/d/yyyy";
currentWorkSheet.Cells["L" + currentRowNumber.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;

The problem is that even with this number formatting and styling the date in the cell is still not recognized as a date, it is only formatted as a date.

Upon research I found that I need to set the column to have a "Date" data as shown in this image: Image

I tried setting the column's data type as shown but this doesn't work:

currentWorkSheet.Column(10).Style.Numberformat.Format = "Date";

What can I do to get the column's data type to be a date?

Solution

  • I tried it and I found that, if I enter the date format exactly as it's set in my system, then Excel takes it as date value type, otherwise takes it as user defined. My system date format is "dd.MM.yyyy" and as a result of this code in the Excel file cell "A3" has date format, cell "A2" had user defined. You might check this out.

    My testcase was:

    using (ExcelPackage testFile = new ExcelPackage(new System.IO.FileInfo(@"c:\Data\test.xlsx")))
            {
                ExcelWorksheet testSht = testFile.Workbook.Worksheets[1];

                testSht.Cells[1, 1].Value = new DateTime(2017, 1, 1);
                testSht.Cells[2, 1].Style.Numberformat.Format = "dd-mm-yyyy";
                testSht.Cells[2, 1].Formula = "=Date(" + DateTime.Now.Year + "," + DateTime.Now.Month + "," + DateTime.Now.Day + ")";
                testSht.Cells[3, 1].Value = new DateTime(2017, 1, 1);
                testSht.Cells[3, 1].Style.Numberformat.Format = "dd.MM.yyyy";
                testFile.Save();
            }