C# - OpenOfficeXML Date Format

Question

I am having an issue when passing some data to an Excel spreadsheet, using OOX. Thing is, everything works great, except the dates, which are shown in a numeric value instead of the datetime format. Here's my code:

private object ProcessXLSFile(PayeesWorkingHoursFullDataDTO payeesWorkingHoursFullDataDTO)
    {
        DataTable dt = Functions.ObjectToDataTable(payeesWorkingHoursFullDataDTO.ListWorkingHours);

        ExcelPackage pck = new ExcelPackage();

        //Create the worksheet
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("WorkSheet1");

        //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
        ws.Cells["A1"].LoadFromDataTable(dt, true);

        string _range = "G2:G" + (dt.Rows.Count + 1).ToString();
        using (ExcelRange col = ws.Cells[_range])
        {
            col.Style.Numberformat.Format = "#,##0.00";
            col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        }

        _range = "J2:J" + (dt.Rows.Count + 1).ToString();
        using (ExcelRange col = ws.Cells[_range])
        {
            col.Style.Numberformat.Format = "dd/MM/yyyy";
            col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        }

        _range = "T2:T" + (dt.Rows.Count + 1).ToString();
        using (ExcelRange col = ws.Cells[_range])
        {
            col.Style.Numberformat.Format = "dd-MM-yyyy";
            col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        }

        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment; filename=Payee-Grid.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.End();

        throw new NotImplementedException();
    }

These lines are the ones where the columns should be converted to datetime:

_range = "J2:J" + (dt.Rows.Count + 1).ToString();
        using (ExcelRange col = ws.Cells[_range])
        {
            col.Style.Numberformat.Format = "dd/MM/yyyy";
            col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        }

But that's definitely not working.

This is the code where I create the DataTable:

public static DataTable ObjectToDataTable<T>(this IList<T> data)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }

Debugging the columns I have been able to see that, up to the lines where I format the columns to "dd-MM-yyyy", the date information is still in datetime format. I have tried everything, but I really don't seem to be able to get this to work properly. Do you guys have any idea on how to make it work?

Answer 1

The problem was with the width of the columns. For some reason, if the width was too narrow, all text in the cells was converted to ####, weird thing. I figured it out because it was only a visual glitch, the values in the cells where the right ones. Using AutoFit() in all the cells of the grid solved the problem. Thanks everyone!