title: Faster way of storing Excel worksheet to system.data.datatable using C#
body: <p>I am trying to find a faster way to read an XML file that can be opened in Excel 2010. I cannot immediately read the XML file using readxml method because it contains Workbook, style, cell, data and other tags. So my approach was to open it in Excel then get the data on sheet 2 only. The sample file contains 9,000+ rows and takes about 2mins 49secs to store in a datatable. The actual file has 25,000+ rows. This is what I have tried:</p>
  
  <pre><code>private void bulkInsert()
          {
              var s = new Stopwatch();
  
              s.Start();
              try
              {
                  KillExcel();
                  GCollector();
                  Excel.Application app = null;
                  app = new Excel.Application();
                  Excel.Worksheet sheet = null;
                  Excel.Workbook book = null;
  
                  book = app.Workbooks.Open(@"my directory for the file");
  
                  sheet = (Worksheet)book.Sheets[2];
                  sheet.Select(Type.Missing);
  
                  var xlRange = (Excel.Range)sheet.Cells[sheet.Rows.Count, 1];
                  int lastRow = (int)xlRange.get_End(Excel.XlDirection.xlUp).Row;
                  int newRow = lastRow + 1;
                  var cellrow = newRow;
                  int columns = sheet.UsedRange.Columns.Count;
                  Excel.Range test = sheet.UsedRange;
                  System.Data.DataTable dt = new System.Data.DataTable();
                  dt.Columns.Add("Node_SegmentName");
                  dt.Columns.Add("Type");
                  dt.Columns.Add("Sub-Type");
                  dt.Columns.Add("Description");
                  dt.Columns.Add("Parameter_DataIdentifier");
                  dt.Columns.Add("RuntimeValue");
                  dt.Columns.Add("Category");
                  dt.Columns.Add("Result");
                  dt.TableName = "SsmXmlTable";
  //slow part
                  for (i = 0; i &lt; lastRow; i++)
                  {
                      DataRow excelRow = dt.NewRow();
  
                      for (int j = 0; j &lt; columns; j++)
                      {
                          excelRow[j] = test.Cells[i + 2, j + 1].Value2;
  
                      }
                      dt.Rows.Add(excelRow);
  
                  }
                  dataGridView1.DataSource = dt;
  
                  DataSet ds = new DataSet();
                  ds.Tables.Add(dt);
                  ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory + String.Format("\\XMLParserOutput{0}.xml", DateTime.Now.ToString("MM-d-yyyy")));
  
  
  
                  DataSet reportData = new DataSet();
                  reportData.ReadXml(AppDomain.CurrentDomain.BaseDirectory + String.Format("\\XMLParserOutput{0}.xml", DateTime.Now.ToString("MM-d-yyyy")));
                  SqlConnection connection = new SqlConnection("Data Source=YOURCOMPUTERNAME\\SQLEXPRESS;Initial Catalog=YOURDATABASE;Integrated Security=True;Connect Timeout=0");
                  connection.Open();
                  SqlBulkCopy sbc = new SqlBulkCopy(connection);
                  sbc.DestinationTableName = "Test";
                  sbc.WriteToServer(reportData.Tables["SsmXmlTable"]);
                  connection.Close();
                  s.Stop();
                  var duration = s.Elapsed;
  
                  MessageBox.Show(duration.ToString() + " bulk insert way");
                  MessageBox.Show(ds.Tables["SsmXmlTable"].Rows.Count.ToString());//439 rows
              }
              catch (Exception ex)
              {
  
                  KillExcel();
                  GCollector();
                  MessageBox.Show(ex.ToString() + i.ToString());
              }
          }
  </code></pre>
  
  <p>Without the reading from Excel part, the insertion of data using bulk copy only takes a couple of seconds (0.5secs for 449 rows).</p>