Export DataGridView to Excel in C#


This code example demonstrates how to export data from a DataGridView control to an Excel document using C#.

About Export DataGridView to Excel in C#


Before you write code, you must add a reference to the Microsoft Excel object library.

Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.

Now here is my Button click event handler where I create Excel object and document, get data from DataGridView and add rows and columns to the document.

Sample Code:

private void button1_Click_1(object sender, EventArgs e)

{
// creating Excel Application

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

// creating new WorkBook within Excel application

Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

// creating new Excelsheet in workbook

Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

// see the excel sheet behind the program

app.Visible = true;

// get the reference of first sheet. By default its name is Sheet1.

// store its reference to worksheet

worksheet = workbook.Sheets["Sheet1"];

worksheet = workbook.ActiveSheet;

// changing the name of active sheet

worksheet.Name = "Exported from gridview";

// storing header part in Excel

for(int i=1;i {
worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
}

// storing Each row and column value to excel sheet

for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
{
for(int j=0;j {
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();

}
}
// save the application

workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Exit from the application

app.Quit();
}
Note this part of code gets data from DataGridView and fills cells.

// storing Each row and column value to excel sheet

for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
{
for(int j=0;j {
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();

}
}



I have taken dataGridView1.Rows.Count-1, because in datagridview it contains empty row at the last. (See in the figure of datagridview.)

I hope you like this article. Feel free to post questions or comments.

Reference: http://www.c-sharpcorner.com/UploadFile/hrojasara/2904/


Comments

Author: Karunagara Pandi22 Nov 2011 Member Level: Gold   Points : 0

Hi,

Why the two FOR loops are not finished properly? I don't understand...

Author: Karunagara Pandi22 Nov 2011 Member Level: Gold   Points : 0

Why the two FOR loops are not finished properly? I don't understand.

Author: Bijit22 Jun 2012 Member Level: Gold   Points : 3

Hi try this code..


protected void btngdExport_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=ExportToWord.doc");
Response.Charset = "";
Response.ContentType = "application/vnd.word";
System.IO.StringWriter strWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlTextWriter = new HtmlTextWriter(strWriter);
Gridview1.AllowPaging = false;
int pageIndex = Gridview1.PageIndex;
Gridview1.DataSource = dt;
Gridview1.DataBind();
Gridview1.PageIndex = pageIndex;
Gridview1.RenderControl(htmlTextWriter);
Response.Write(strWriter.ToString());
strWriter.Dispose();
htmlTextWriter.Dispose();
Response.End();
Gridview1.AllowPaging = true;
Gridview1.DataSource = dt;
Gridview1.DataBind();
Gridview1.PageIndex = pageIndex;
}



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: