Date Conversion

Question

User245475216 posted

I am exporting a datatable to xlsx file and having trouble with date field. I see correct date in the Datatable but when exporting to spreadsheet(using MemorySteam), I see different value.

//Adding Data from Datatable to worksheet and saving

                    foreach (DataRow dataRow in dt1.Rows)

                    {

                        String[] rowData = new string[] { dt1.Rows[0].ToString() }; 

                        workSheet.Row(1).Style.Font.Bold = true;

                        workSheet.Cells["A1"].LoadFromDataTable(dt1, true);

                    }

    var stream = new MemoryStream(pck.GetAsByteArray());

                stream.WriteTo(Response.OutputStream);

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AddHeader("Content-Disposition", "attachment;filename=file_export_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");

                Response.Flush();

                Response.End();

for eg: 5/7/2018 displays as 43227. Please suggest, where I am doing wrong.

Answer 1

User-893317190 posted

Hi prasadbvm,

I have tested your code , but I couldn't reproduce your problem.

Have you done other things before the code you have posted?

Please clear other content before exporting datatable.

public void ExporttoExcel(DataTable table, string filename)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx");


    using (ExcelPackage pack = new ExcelPackage())
    {
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
        ws.Cells["A1"].LoadFromDataTable(table, true);
        var ms = new System.IO.MemoryStream();
        pack.SaveAs(ms);
        ms.WriteTo(HttpContext.Current.Response.OutputStream); 
    }

    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();

}

If it doesn't work,could you show more of your related code?

At last , if you want to show your code in this forum, please click the {;}  icon in the toolbar and past your code in the pop up .

Best regards,

Ackerly Xu

Answer 2

User245475216 posted

Thanks for your response. The code works. The problem, I have is with one column (Date field). After the file is downloaded, manually formatting woks fine. Below is the sample code. 

            //Creating Excel Package
            using (ExcelPackage pck = new ExcelPackage())
            {
                string filename = "test.xlsx";
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(filename);
                DataTable table = new DataTable();
                table.Columns.Add("No", typeof(int));
                table.Columns.Add("Date", typeof(DateTime));
                table.Rows.Add(1, DateTime.Now);
                table.Rows.Add(2, DateTime.Now);
                DataTable dt1 = table;
                ws.Cells["A1"].LoadFromDataTable(dt1, true);
                var ms = new System.IO.MemoryStream();
                pck.SaveAs(ms);
                ms.WriteTo(HttpContext.Current.Response.OutputStream);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();

Answer 3

User-893317190 posted

Hi prasadbvm,

You could set the format of your cells.

Below is my code.

 string filename = "test.xlsx";
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add(filename);
                        DataTable table = new DataTable();
                        table.Columns.Add("No", typeof(int));
                        table.Columns.Add("Date", typeof(DateTime));
                        table.Rows.Add(1, DateTime.Now);
                        table.Rows.Add(2, DateTime.Now);
                        DataTable dt1 = table;
                        ws.Cells["A1"].LoadFromDataTable(dt1, true);
                        var ms = new System.IO.MemoryStream();
                    ws.Cells[2,2,3,2].Style.Numberformat.Format = "yyyy-MM-dd";//format the date 
                    //the first parameter is the start row, the second parameter is start column
                     // the third parameter is the end row, the fourth parameter is end column

Best regards,

Ackerly Xu