How to download SQL data in excel format using asp net

0 votes

My data is being downloaded in an excel format. The following code executes flawlessly, however, the format is corrupted.

The file format and extension don't match is the error I receive. The file can be dangerous or corrupt. Don't open it unless you are confident in its source. Do you still intend to open it? When you say Yes, the file opens.
Please examine the code and explain what changes I need to do in order to obtain my data (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());
Nov 14 in Others by Kithuzzz
• 20,660 points
48 views

1 answer to this question.

0 votes

Solution

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();
                            }
                        }
answered Nov 14 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
70 views
0 votes
0 answers
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
6,145 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
106 views
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

You need to re-think this… the while ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
291 views
0 votes
1 answer

Export DataTable to Excel File

Add Interop References. First we need to ...READ MORE

answered Jun 9 in JQuery by gaurav
• 22,040 points
109 views
0 votes
0 answers

Error converting data type nvarchar to datetime SQL Server

I encountered this error: Error converting data type ...READ MORE

Aug 15 in Database by Kithuzzz
• 20,660 points
812 views
0 votes
1 answer

Merge and export Excel/Word/PDF to PDF

Use GroupDocs.Merger for .NET API to merge Word, Excel, ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
82 views
0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
136 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP