How to create Excel sheet using DataTable and the ExcelLibrary?

Question

I have data-table which contain no of column also with date with "DATE" datatype. I tried following option

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

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

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

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();
    }
}

In above code extracting Excel perfectly , but when we open same excel it gets error that format is wrong.

also I want to read same file in datatable to store in database. when I go to read created excel (by 2nd option) then I get error that external table is not in expected format. If I save as the same file then it works file.

But I dont want to do every time "save As" file. please help me

Updates:

public void ExportToExcel1(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=Reportengg.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("Sheet1");

    //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);
  //  byte[] array = package.GetAsByteArray();
    //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();
}

Answer 1

Your #1 technique is saving the file on the server. Server side code can't save directly to the client file system. You must write the file bytes to the response, then the client's PC will choose what to do with it. Whether they choose to "save as" or just save directly to some Downloads folder is up to their browser settings. I'm not familiar with ExcelLibrary but I imagine they have some sort of API to get the file bytes? Do that. Then write those bytes to the response.

byte[] bytes = GetBytesFromTheirApiSomehow();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "attachment; filename=filename.xlsx");
Response.BinaryWrite(bytes);
Response.End();

---

I took a look at the ExcelLibrary source code. That library doesn't appear to be maintained anymore. Perhaps you should move to a library that's actively maintained, such as EPPlus. An EPPlus implementation might look like this:

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();
}

Answer 2

This worked for me:

        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Report123.xls"));
        Response.ContentType = "application/vnd.ms-excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        GridView1.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();