Click here to Skip to main content
14,930,588 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have data-table which contain no of column also with date with "DATE" datatype. I tried following option

1) 3rd party DLL- ExcelLibrary It works fine if there is no date column in dataset, else it use some dummy value like -65284 instead of date.

C#
ExcelLibrary.DataSetHelper.CreateWorkbook(@"C:\Users\ABC\Documents\Excel\Report123.xls", ds);


2) Use simple export format without using 3rd party DLL as follow

C#
 public void ExportToExcel(System.Data.DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        string filename = "Report123.xls";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();

        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}


3) Use 3rd party DLL EPPlus

C#
public void ExportToExcel(System.Data.DataTable dt)
{
    //clear the response of any junk. This may not be necessary
    Response.Clear();

    //add a header so it has a nice file name
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");

    //Set the MIME type correctly
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    //create a new package, this is the equivalent of an XLSX file.
    var package = new ExcelPackage();

    //Add a new sheet to the workbook
    var sheet = package.Workbook.Worksheets.Add("My Data"); 

    //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    sheet.Cells["A1"].LoadFromDataTable(dt, true); 

    //write the file bytes to the response
    Response.BinaryWrite(package.GetAsByteArray());

    //end the response so we don't send anymore down and corrupt the file
    Response.End();
}


and I use below code to open created

C#
string strConn = hasHeader ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lFilePath + "';Extended Properties=\"Excel 12.0;HDR=Yes\";"
              : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lFilePath + "';Extended Properties=\"Excel 12.0;HDR=No\";";
excel sheet

but facing same error when I read created Excel sheet that "External table is not is expected format".

Please help me. I am facing same problem from a long time.
Posted
Updated 6-Oct-15 3:17am
v3
Comments
Andy Lanng 6-Oct-15 6:22am
   
65284 is not "dummy" data. it is the number of days since 00/01/1900. It's how excel stored dates. (29/09/2078?)

You need to add the formatting yourself. See if there are any format properties in the third party classes
Member 12036327 6-Oct-15 7:16am
   
Ok, Then Can you please help me , how to do formatting , If I used only one line of code
Andy Lanng 6-Oct-15 9:33am
   
First: you need to reply to my comment. Hover over the comment to see the "reply" button. Otherwise I don't get notified and may not reply.
Second: just google: sheet.Column([int?]).Style.Numberformat.Format = "yyyy-mm-dd"
I'll write up the full answer in a few minutes

1 solution

The issue with the dates is because you need to format the date column on the sheet.

If you know which column is a date then you can just use the column index, but then your code is not as generic as it once was. The best idea would be to discover the date fields at run-tim:

C#
public void ExportToExcel(System.Data.DataTable dt)
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var package = new ExcelPackage();
    var sheet = package.Workbook.Worksheets.Add("My Data"); 
 
//region the bit we'll look at:

    //it's easy if you start at column A, but we'll be using index
    sheet.Cells["A1"].LoadFromDataTable(dt, true); 
 
    for(int x = 0; x < dt.Columns.count; x++)
    {
        //in this loop, x is the index. The index of dt columns will match sheet.Columns
         
        //Test the column[x]:
        if(dt.Columns[i].DataType==typeof(DateTime))
        {
            //if so then apply formatting:
            sheet.Column(x).Style.Numberformat.Format = "yyyy-mm-dd"
        }
    }

//endregion

    Response.BinaryWrite(package.GetAsByteArray());
    Response.End();
}


I hope that help ^_^
Andy
   
v2
Comments
Andy Lanng 6-Oct-15 9:51am
   
That's better - finally got the formatting working. Looks like #region has some bugs

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900