Save the contents of gridview to the excel file?

Question

I want to save the contents of gridview to the excel file and i am using the following code to done this. It works fine but the excel file it make is of the 0 KB size. please help me to resolve this issue. Thanks in advance...

having the following code with me but it sttil irritate me...
please suggest me other method.....

private void ExportToExcel()
{
HtmlForm form = new HtmlForm();

string attachment = "attachment; filename=AssignedDoctors.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter stw = new StringWriter();

HtmlTextWriter htextw = new HtmlTextWriter(stw);

// put your grid view in here
form.Controls.Add(GridView1);

this.Controls.Add(form);

form.RenderControl(htextw);

Response.Write(stw.ToString());

Response.End();
}

Sharon Thompson · Answer

You must realize that there are some drawbacks with these approaches. The problem with the first approach which uses RenderControl method is that you are actually saving a HTML content into a file with XLS extension. The problem with other suggested approaches is that you are only retrieving the GridView's data source as a DataTable so all the styling gets lost. What you need to do is convert your HTML content into an Excel file in C# . For example you can achieve that pretty easily with this Excel's library for C# and export that real Excel file to ASP.NET's client like the following: StringWriter sw = new StringWriter (); HtmlTextWriter htw = new HtmlTextWriter (sw); GridView1.RenderControl(htw); ExcelFile excel; var options = LoadOptions .HtmlDefault; using ( var htmlStream = new MemoryStream (options.Encoding.GetBytes(sw.ToString()))) excel = ExcelFile .Load(htmlStream options); excel.Save( this .Response GridView.xls );

Sharon White · Answer

My code: private void button1_Click(object sender EventArgs e) { //Create Workbook Workbook workbook = new Workbook(); workbook.CreateEmptySheets(1); Worksheet sheet = workbook.Worksheets[0]; //Insert DataTable to Excel sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource true 2 1 -1 -1); //Save and Launch File workbook.SaveToFile(DataImport.xlsx ExcelVersion.Version2010); System.Diagnostics.Process.Start(workbook.FileName); } private void Form1_Load_1(object sender EventArgs e) { //Load Data from Database to DataGridView string connString = @Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\work\VIP.mdb;Persist Security Info=False;; DataTable dataTable = new DataTable(); using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); string sql = select NameGenderBirthdayEmailNumberCountry from VIP; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql conn); dataAdapter.Fill(dataTable); } this.dataGridView1.DataSource = dataTable; } I use a .NET Excel component to import data.

Lacy Michelle · Answer

You can use a free source free data export component to help u: private void GetData(string selectCommand) { try { // Specify a connection string. Replace the given value with a // valid connection string for a Northwind SQL Server sample // database accessible to your system. String connectionString = txtConnstr.Text.Trim(); // Create a new data adapter based on the specified query. OleDbDataAdapter adpter = new OleDbDataAdapter(selectCommand connectionString); // Create a command builder to generate SQL update insert and // delete commands based on selectCommand. These are used to // update the database. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adpter); // Populate a new data table and bind it to the BindingSource. DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; adpter.Fill(table); bindingSource1.DataSource = table; dataGridView1.DataSource = bindingSource1; // Resize the DataGridView columns to fit the newly loaded content. dataGridView1.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); } catch (SqlException) { MessageBox.Show(To run this example replace the value of the  + connectionString variable with a connection string that is  + valid for your system.); } } private void btnExportForm_Click(object sender EventArgs e) { FormExportData form = new FormExportData(); form.Command = this.txtCmd.Text.Trim(); form.ConnStr = this.txtConnstr.Text.Trim(); if (form.ShowDialog(this)!=DialogResult.OK) { return; } } } } since the whole process can not describe in detail so please see: Export Data from GridView to Excel

Satyapriya Nayak · Answer

Hi Amol Try this... Untitled Page Text=Import to Excel /> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Text; namespace import_the_gridview_data_in_excel { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; protected void Page_Load(object sender EventArgs e) { SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sqlda = new SqlDataAdapter(SELECT * from employee conn); DataSet ds = new DataSet(); sqlda.Fill(ds employee); GridView1.DataSource = ds; GridView1.DataMember = employee; GridView1.DataBind(); } protected void Button1_Click(object sender EventArgs e) { ExportToExcel(Report.xls GridView1); } private void ExportToExcel(string strFileName GridView dg) { Response.Clear(); Response.Buffer = true; Response.ContentType = application/vnd.ms-excel; Response.Charset = ; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); GridView1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } } } Thanks If this post helps you mark it as answer