title: Fastest way to generate Excel from datagridview with formatting
body: <p>I have a code to <strong>export data</strong> from <code>datagridview</code> to Excel sheet but the problem is it is very slow because it is <strong>inserting data and formatting each cell</strong>.</p>
  
  <p>How can I improve performance of this operation?</p>
  
  <p>Below is my code</p>
  
  <pre><code>public static void ExcelExport(DataGridView Dg, string TypePass)
  {
      Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
      ExcelApp.Application.Workbooks.Add(Type.Missing);
      Excel_12.ApplicationClass oExcel_12 = null;           //Excel_12 Application
      Excel_12.Workbook oBook = null;                       // Excel_12 Workbook
      Excel_12.Sheets oSheetsColl = null;                   // Excel_12 Worksheets collection
      Excel_12.Worksheet oSheet = null;                     // Excel_12 Worksheet
      Excel_12.Range oRange = null;                         // Cell or Range in worksheet
      Object oMissing = System.Reflection.Missing.Value;
      oExcel_12 = new Excel_12.ApplicationClass();
      oExcel_12.UserControl = true;
      oBook = oExcel_12.Workbooks.Add(oMissing);
      oSheetsColl = oExcel_12.Worksheets;
      oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
  
      oRange = (Excel_12.Range)oSheet.Cells[1, 1];
      oRange.Value2 = "";
      oRange.Font.Name = "Tahoma";
      oRange.Font.Size = 12;
      (oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
      (oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
  
      if (TypePass.Trim().Length &gt; 0)
      {
          oRange = (Excel_12.Range)oSheet.Cells[2, 1];
          oRange.Value2 = TypePass;
          oRange.Font.Name = "Tahoma";
          oRange.Font.Size = 10;
      }
  
      int c = 0;
  
      if (Dg.ColumnHeadersVisible == true)
      {
          for (int j = 0; j &lt; Dg.Columns.Count; j++)
          {
              if (Dg.Columns[j].Visible == true)
              {
                  oRange = (Excel_12.Range)oSheet.Cells[4, c + 1];
                  oRange.Value2 = Dg.Columns[j].HeaderText + "  ";
                  oRange.Font.Bold = true;
                  oRange.Font.Name = "Tahoma";
                  oRange.Font.Size = 9;
                  (oRange).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                  (oRange).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Teal);
                  oExcel_12.Columns.AutoFit();
                  c++;
              }
          }
      }
  
      c = 0;
  
      for (int i = 0; i &lt; Dg.Rows.Count; i++)
      {
  
          for (int j = 0; j &lt; Dg.Columns.Count; j++)
          {
              if (Dg.Columns[j].Visible == true)
              {
                  oRange = (Excel_12.Range)oSheet.Cells[i + 5, c + 1];
                  if (Dg[j, i].Value == null)
                  {
                      oRange.Value2 = " ";
                  }
                  else
                  {
                      oRange.Value2 = Dg[j, i].Value.ToString().Replace('\n', ' ') + "  ";
                  }
  
                  oRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                  oRange.Font.Name = "Tahoma";
                  oRange.Font.Size = 8;
                  oExcel_12.Columns.AutoFit();
                  // oRange.NumberFormat = "dd/MM/yyyy";
                  c++;
              }
          }
          c = 0;
      }
  
      oExcel_12.Visible = true;
      oBook = null;
      oExcel_12 = null;
      GC.Collect();
  }
  </code></pre>