How to download SQL data in excel format using asp.net

Question

I'm trying to download my data in excel format . Following code does the work perfectly but format comes out to be corrupted.

Error which I get - The file format and extension don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyways? On confirming Yes, file opens.

Please have to look at the code and help me understand what change I must make to download my data in (xls 97-2003 excel workbook).

 if (dt4.Rows.Count > 0)
        {
            string filename = "DownloadMobileNoExcel.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 = dt4;
            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());

Start using a specialized library for creating Excel files, like [EPPlus](http://stackoverflow.com/documentation/epplus/drafts/98280) for example. All you are doing now is creating a HTML page with an .xls extension. — VDWWD, Feb 27 '18 at 07:51
Possible duplicate of [How to export DataTable to Excel](https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel) — VDWWD, Feb 27 '18 at 07:52

score 1 · Answer 1 · edited Apr 04 '18 at 13:53

Firstly

Download Open Xml Download Close Xml Library

import this Namespaces

using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Configuration;
using System.Data.SqlClient;

try to do that like this

 using (XLWorkbook wb = new XLWorkbook())
                        {
                            wb.Worksheets.Add(dt, "Customers");

                            Response.Clear();
                            Response.Buffer = true;
                            Response.Charset = "";
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                            using (MemoryStream MyMemoryStream = new MemoryStream())
                            {
                                wb.SaveAs(MyMemoryStream);
                                MyMemoryStream.WriteTo(Response.OutputStream);
                                Response.Flush();
                                Response.End();
                            }
                        }

Mudassar Khan has a nice article regarding that here

How to download SQL data in excel format using asp.net

1 Answers1