Exporting DataGridview To Excel

Anurag Sarkar

Rate me:

4.79/5 (26 votes)

17 Feb 2013CPOL

215.7K

How to export DataGridView data to Excel.

Introduction

This article is for beginners. In this article I am going to explain how to export DataGridView data to Excel.

Background

Initially I was stuck in this problem for a few days then after searching for a few days on Google and on some other sites, I got my answer and here it is. Hope you will like it.

Using the code

References:

using System; 
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.IO;

Function for exporting to Excel:

private void ToCsV(DataGridView dGV, string filename)
        {
            string stOutput = "";
            // Export titles:
            string sHeaders = "";

            for (int j = 0; j < dGV.Columns.Count; j++)
                sHeaders = sHeaders.ToString() + Convert.ToString(dGV.Columns[j].HeaderText) + "\t";
            stOutput += sHeaders + "\r\n";
            // Export data.
            for (int i = 0; i < dGV.RowCount - 1; i++)
            {
                string stLine = "";
                for (int j = 0; j < dGV.Rows[i].Cells.Count; j++)
                    stLine = stLine.ToString() + Convert.ToString(dGV.Rows[i].Cells[j].Value) + "\t";
                stOutput += stLine + "\r\n";
            }
            Encoding utf16 = Encoding.GetEncoding(1254);
            byte[] output = utf16.GetBytes(stOutput);
            FileStream fs = new FileStream(filename, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(output, 0, output.Length); //write the encoded file
            bw.Flush();
            bw.Close();
            fs.Close();
        }

Code for button for exporting to Excel

SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = "export.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
    //ToCsV(dataGridView1, @"c:\export.xls");
    ToCsV(dataGridView1, sfd.FileName); // Here dataGridview1 is your grid view name
}

In my next article I am going to show you how to export multiple DataGridView on the same form using C#. Thanks.

This article was originally posted at http://onurag-dotnetfunda.blogspot.in/2012_10_01_archive.html

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

protected void btnExport_Click(object sender, EventArgs e) { try { string strReport = "_Details_" + ".xls"; ExportDataGrid(dgDetails, strReport); } catch (Exception ex) { throw ex; } }

public void ExportDataGrid(DataGrid oGrid, string exportFile) { try { //Clear the response, and set the content type and mark as attachment Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=\"" + exportFile + "\""); Response.ContentType = "application/excel"; //Clear the character set Response.Charset = ""; //Create a string and Html writer needed for output System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); //Clear the controls from the pased grid ClearControls(oGrid); //Show grid lines oGrid.GridLines = GridLines.Both; //Color header oGrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray; oGrid.Columns[7].Visible = false; //Render the grid to the writer oGrid.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.Flush(); Response.End(); } catch (Exception ex) { throw ex; } }

private void ClearControls(Control control) { //Recursively loop through the controls, calling this method for (int i = control.Controls.Count - 1; i >= 0; i--) { ClearControls(control.Controls[i]); } //If we have a control that is anything other than a table cell if (!(control is TableCell)) { if (control.GetType().GetProperty("SelectedItem") != null) { LiteralControl literal = new LiteralControl(); control.Parent.Controls.Add(literal); try { literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null); } catch { } control.Parent.Controls.Remove(control); } else if (control.GetType().GetProperty("Text") != null) { LiteralControl literal = new LiteralControl(); control.Parent.Controls.Add(literal); literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null); control.Parent.Controls.Remove(control); } } return; }

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Exporting DataGridview To Excel

Introduction

Background

Using the code

License

Comments and Discussions