Post
Ask Question

C# Export DataGridView to Excel Sheet

  1. using Microsoft.Office.Interop.Excel;  
  2. //Create a Button btnExport on your form and write following code on its Click Event   
  3. private void btnExport_Click(object sender, EventArgs e)   
  4. {   
  5.     SaveFileDialog sfd = new SaveFileDialog();   
  6.     DialogResult drSaveFile = sfd.ShowDialog();  
  7.     try   
  8.     {   
  9.         if (drSaveFile == System.Windows.Forms.DialogResult.OK)   
  10.         {  
  11.             ApplicationClass ExcelApp = new ApplicationClass();   
  12.             ExcelApp.Application.Workbooks.Add(Type.Missing);   
  13.           
  14.             //ExcelApp.ActiveWorkbook.FileFormat = XlFileFormat.xlExcel8;   
  15.             // Change properties of the Workbook   
  16.             ExcelApp.Columns.ColumnWidth = 20;   
  17.   
  18.             // Storing header part in Excel   
  19.             for (int i = 1; i < datagridview.Columns.Count + 1; i++)   
  20.             {   
  21.                 ExcelApp.Cells[1, i] = datagridview.Columns[i - 1].HeaderText;   
  22.             }   
  23.   
  24.             // Storing Each row and column value to excel sheet   
  25.             for (int i = 0; i < datagridview.Rows.Count; i++)   
  26.             {   
  27.                 for (int j = 0; j < datagridview.Columns.Count; j++)   
  28.                 {   
  29.                     if (j == 2 || j == 5)   
  30.                     {   
  31.                         ExcelApp.Cells[i + 2, j + 1] = "'" + datagridview.Rows[i].Cells[j].Value.ToString();   
  32.                     }   
  33.                     else   
  34.                     {   
  35.                         ExcelApp.Cells[i + 2, j + 1] = datagridview.Rows[i].Cells[j].Value.ToString();   
  36.                     }   
  37.                 }   
  38.             }   
  39.   
  40.             //Save Copy by giving file Path   
  41.             //ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\" + FileName);   
  42.   
  43.             //OR using SaveFileDialog   
  44.             ExcelApp.ActiveWorkbook.SaveCopyAs(sfd.FileName);   
  45.               
  46.             //OR even you can use SaveAs function   
  47.             //ExcelApp.ActiveWorkbook.SaveAs(sfd.FileName, XlFileFormat.xlExcel8, null, null, null,   
  48.             // null, XlSaveAsAccessMode.xlShared, null, null, null, null, null);   
  49.             ExcelApp.ActiveWorkbook.Saved = true;   
  50.             ExcelApp.Quit();   
  51.         }   
  52.     }   
  53.     catch (Exception ex)   
  54.     {   
  55.         MessageBox.Show("ERROR: " + ex.Message);   
  56.     }   
  57. }