Export DataGridView To Excel In C#

My application looks like Figure 1 where you can see I have some data in a DataGridView control on a Windows Forms application. I also have a Button called Export to Excel. When you click Export To Excel button, the application will export DataGridView data to an Excel document.

ExportDGVToExcelImg1.jpg
Figure 1

The Excel document will look like Figure 2.

ExportDGVToExcelImg2.jpg
Figure 2

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

  1. private void button1_Click_1(object sender, EventArgs e) {  
  2.     // creating Excel Application  
  3.     Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();  
  4.     // creating new WorkBook within Excel application  
  5.     Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);  
  6.     // creating new Excelsheet in workbook  
  7.     Microsoft.Office.Interop.Excel._Worksheet worksheet = null;  
  8.     // see the excel sheet behind the program  
  9.     app.Visible = true;  
  10.     // get the reference of first sheet. By default its name is Sheet1.  
  11.     // store its reference to worksheet  
  12.     worksheet = workbook.Sheets["Sheet1"];  
  13.     worksheet = workbook.ActiveSheet;  
  14.     // changing the name of active sheet  
  15.     worksheet.Name = "Exported from gridview";  
  16.     // storing header part in Excel  
  17.     for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) {  
  18.         worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;  
  19.     }  
  20.     // storing Each row and column value to excel sheet  
  21.     for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {  
  22.         for (int j = 0; j < dataGridView1.Columns.Count; j++) {  
  23.             worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  24.         }  
  25.     }  
  26.     // save the application  
  27.     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);  
  28.     // Exit from the application  
  29.     app.Quit();  
  30. }  

Note this part of code gets data from DataGridView and fills cells.

  1. // storing Each row and column value to excel sheet  
  2. for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {  
  3.     for (int j = 0; j < dataGridView1.Columns.Count; j++) {  
  4.         worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  5.     }  
  6. }  

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.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now