locked
Date Conversion RRS feed

  • 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.
    Wednesday, November 28, 2018 4:24 PM

All replies

  • 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

    Thursday, November 29, 2018 2:30 AM
  • 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();

    Friday, November 30, 2018 7:45 PM
  • 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

    Monday, December 3, 2018 1:31 AM