title: speed performance exporting to excel file using Microsoft.Office.Interop.Excel
body: <p>I am using Microsoft.Office.Interop.Excel to export local database table to excel file. There are five columns. </p>
  
  <p>If rows are 400 it takes about 20 seconds,</p>
  
  <p>If rows are 1200 it takes about 45 seconds,</p>
  
  <p>If rows are 5000 it takes 250-300 seconds.</p>
  
  <p>Is there a way to minimize the time to export? Or this is the maximum performance? If you can suggest to improve my code with respect to speed or suggest some alternative, I will appreciate. As it is working in background worker therefore invoking was necessary. My code is</p>
  
  <pre><code>       int rowCount = oLXTableDataGridView.RowCount;
  
          if (rowCount == 1)
          {
              MessageBox.Show("No data to export.");
  
              return;
  
          }
  
          this.Invoke((MethodInvoker)delegate
                  {
                       this.ExportFilepictureBox.Image = Properties.Resources.Animation;
                       labelexpoertolx.Text = "Preparing to export...";
                  });
  
  
                  object misValue = System.Reflection.Missing.Value;
  
  
                  conn = new SqlCeConnection(@"Data Source=|DataDirectory|\dontdelete.sdf");
                  String selectgroup = "SELECT * FROM OLXTable";
                  int namecol = 1;
                  int cellcol = 2;
                  int emailcol = 3;
                  int citycol = 4;
                  int categorycol = 5;
  
  
                      try
                      {
  
                          Excel.Application xlApp1;
                          Excel.Workbook xlWorkBook;
                          Excel.Worksheet xlWorkSheet;
  
  
                          xlApp1 = new Excel.Application();
                          xlWorkBook = xlApp1.Workbooks.Add(misValue);
  
  
                          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                          // MessageBox.Show("this is file");
  
  
                          xlWorkSheet.Cells[1, namecol].Value2 = "Name";
                          xlWorkSheet.Cells[1, cellcol].Value2 = "Cell No";
                          xlWorkSheet.Cells[1, emailcol].Value2 = "Email";
                          xlWorkSheet.Cells[1, citycol].Value2 = "City";
                          xlWorkSheet.Cells[1, categorycol].Value2 = "Category";
  
                          SqlCeDataReader reader = null;
  
  
                          //conn = new SqlCeConnection(selectnumbers);
                          conn.Open(); //open the connection
                          SqlCeCommand selecectnumberscmd = new SqlCeCommand(selectgroup, conn);
                          reader = selecectnumberscmd.ExecuteReader();
                          int i =  1;
  
                          while (reader.Read())
                          {
                              xlWorkSheet.Cells[i, namecol].Value2 = reader.GetString(1);
                              xlWorkSheet.Cells[i, cellcol].Value2 = reader.GetInt64(2);
                              xlWorkSheet.Cells[i, emailcol].Value2 = reader.GetString(3); ;
                              xlWorkSheet.Cells[i, citycol].Value2 = reader.GetString(4);
                              xlWorkSheet.Cells[i, categorycol].Value2 = reader.GetString(5);
                              i++;
  
                          }
                          conn.Close();
                          xlWorkBook.Close(true, misValue, misValue);
                          xlApp1.Quit();
                          releaseObject(xlWorkSheet);
                          releaseObject(xlWorkBook);
                          releaseObject(xlApp1);
  
  
  
                          this.Invoke((MethodInvoker)delegate
                          {
                              this.ExportFilepictureBox.Image = null;
                              labelexpoertolx.Text = "";
                          });
  
  
                      }
                      catch (Exception e13)
                      {
                          MessageBox.Show("Error Exporting data" + e13.Message);
                          conn.Close();
  
  
                      }
                      Cursor.Current = Cursors.Default;
  </code></pre>
  
  <p>and </p>
  
  <pre><code>private void releaseObject(object obj)
      {
          try
          {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
              obj = null;
          }
          catch (Exception ex)
          {
              obj = null;
              MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
          }
          finally
          {
              GC.Collect();
          }
      }
  </code></pre>