Faster way of storing Excel worksheet to system.data.datatable using C# -

i trying find faster way read xml file can opened in excel 2010. cannot read xml file using readxml method because contains workbook, style, cell, data , other tags. approach open in excel data on sheet 2 only. sample file contains 9,000+ rows , takes 2mins 49secs store in datatable. actual file has 25,000+ rows. have tried:

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 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                 (i = 0; < lastrow; i++)                 {                     datarow excelrow = dt.newrow();                      (int j = 0; j < 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());             }         } 

without reading excel part, insertion of data using bulk copy takes couple of seconds (0.5secs 449 rows).

for others encountering same issue, did was:

• save xml xlsx file
• use oledb read xlsx file
• store in dataset using oledbadapter (fill() method)
• bulk insert

here code used (change connection string):

stopwatch s = new stopwatch(); s.start(); string ssheetname = null; string sconnection = null; system.data.datatable sheetdata = new system.data.datatable(); system.data.datatable dttableslist = default(system.data.datatable); oledbconnection oleexcelconnection = default(oledbconnection); sconnection = "provider=microsoft.ace.oledb.12.0;data source=" + @"c:\users\yourusername\documents\visual studio 2012\projects\testxmlparser\testxmlparser\bin\debug\consolidatedssmfiles.xlsx" + ";extended properties=\"excel 8.0;hdr=yes;imex=1\""; oleexcelconnection = new oledbconnection(sconnection); oleexcelconnection.open(); dttableslist = oleexcelconnection.getschema("tables");  if (dttableslist.rows.count > 0) { ssheetname = dttableslist.rows[0]["table_name"].tostring(); } dttableslist.clear(); dttableslist.dispose();  if (!string.isnullorempty(ssheetname)) { oledbdataadapter sheetadapter = new oledbdataadapter("select * [test$]", oleexcelconnection); sheetadapter.fill(sheetdata); } s.stop(); var duration = s.elapsed;   oleexcelconnection.close(); datagridview1.datasource = sheetdata; messagebox.show(sheetdata.rows.count.tostring()+"rows - "+ duration.tostring()); 

this reads 25000+ rows of excel data datable in approx. 1.9 2.0 seconds.