How to download an Excel FIle (.xlsx) from a DataTable object in C#-CodePudding

I want to make a button that makes the download of a DataTable object using an HTTP request. I already have this code that can print .xls files by changing the contentType as indicated in comment. However, this does not work with xlsx file.

        private void DownloadExcel(DataSet ds, string excelFileNameNowDateTime)
    {
        System.IO.StringWriter tw = new System.IO.StringWriter(); ;
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

        System.Web.UI.WebControls.DataGrid dgGrid = new System.Web.UI.WebControls.DataGrid();

        dgGrid.DataSource = ds.Tables[0];

        // Get the HTML for the control.
        dgGrid.HeaderStyle.Font.Bold = false;
        dgGrid.DataBind();
        dgGrid.RenderControl(hw);

        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        //string contentType = "application/vnd.ms-excel";
        Response.ContentType = contentType;
        this.EnableViewState = true;
        Response.Clear();
        //Response.ContentEncoding = System.Text.Encoding.Default;
        Response.AddHeader("content-disposition", String.Format(@"attachment; filename={0}", excelFileNameNowDateTime   ".xlsx"));

        Response.Write(tw.ToString());
        CloseHTTPResponse();
    }

While opening the file is giving me the error

Excel cannot open the file .xlsx because the file format is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Any idea on why this does not work?

CodePudding user response：

It looks to me like you're trying to send HTML contents with .xlsx extension. This won't work since XLSX is fundamentally not HTML, but zipped XML with quite specific (and convoluted) schema. The simplest way to achieve your goal would be to use one of the existing 3rd party libraries that can generate XLSX on the fly. I personally had used ClosedXML for a similar task, but there are many other options at NuGet.

CodePudding user response：

This code doesn't produce an Excel file, it produces an HTML file with a fake extension. Excel won't be fooled, it will try to import this HTML file using the user locale's defaults. This will easily lead to problems if the decimal separator is different or the cells contain text that interferes with HTML.

There's no reason for such code. XLSX is a ZIP package containing well-formed XML files. You can use the Open XML SDK to create Excel files at a low level or you can use libraries like EPPlus (57M downloads), ClosedXML (27M), NPOI (22M) and more, to create real Excel files.

With EPPlus, creating an Excel file from a DataTable is really a single command, sheet.Cells.LoadFromDataTable(dt);

public byte[] Export(DataTable dt)
{
    using (var p = new ExcelPackage())
    {
        var ws = p.Workbook.Worksheets.Add("SomeSheet");
        var table=ws.Cells.LoadFromDataTable(dt);
        var excelData = package.GetAsByteArray();
        return excelData;
    }
}

After that, you write the output to the Response stream :

var bytes = Export(dt);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", $"attachment;filename={excelFileNameNowDateTime}.xlsx");
Response.BinaryWrite(bytes);
Response.End();