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 Name,Gender,Birthday,Email,Number,Country 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

Save the contents of gridview to the excel file?

Answers (4)