Faster way to insert rows into Excel spreadsheet in C# using oleDbCommand? -

so looking faster insert method spreadsheets on server. right depends on data being used takes 20 seconds insert 100 rows single spreadsheet. understand why it's doing it, because inserting spreadsheet causes excel shift rows each time new row inserted. more rows being added single spreadsheet, longer take. tested theory , correct, created 100 spreadsheets , randomly inserted 1000 rows spread across them all. took around 60 seconds complete. inserting same 1000 rows single spreadsheet took on 5 minutes complete. here code below:

 string connectionstring = string.format(@"provider=microsoft.ace.oledb.12.0;" +                             "data source={0};extended properties='excel 12.0;hdr=yes'", file);             aspose.cells.workbook wb = new aspose.cells.workbook(filetemplate);             aspose.cells.worksheetcollection sheets = wb.worksheets;             aspose.cells.worksheet sheet = wb.worksheets[0];          wb.save(file);              combinedcount = 0;             counter = 0;             foreach (datarowview drv in view)//check each row in our simplified view ebid             {                 if (combinedlist[combinedcount][1] == "") //if empty goes brandies sheet                 {                      sheet.cells.insertrow(2);                     using (oledbconnection cn = new oledbconnection(connectionstring))                     {                         cn.open();                         oledbcommand cmd1 = new oledbcommand("insert [" + combinedlist[combinedcount][0] + "$] " + //"+sheetcnt+"                              "([reporting retailer ebid],[outlet basf id],[retailer ot],[mapped grower id],[mapped grower],[ship grower],[bill grower],[transaction id],[product id],[product description],[quantity],[invoice no],[previously sent],[comments])" +                              "values(@value1,@value2,@value3,@value4,@value5,@value6,@value7,@value8,@value9,@value10,@value11,@value12,@value13,@value14)", cn);                         cmd1.parameters.addwithvalue("@value1", drv[0]);//retailer ebid                         cmd1.parameters.addwithvalue("@value2", drv[1]);//outlet basf                         cmd1.parameters.addwithvalue("@value3", drv[13]);//retailer ot                         cmd1.parameters.addwithvalue("@value4", drv[2]);//mapped g id                         cmd1.parameters.addwithvalue("@value5", drv[10]);//mapped g                         cmd1.parameters.addwithvalue("@value6", drv[11]);//ship g                         cmd1.parameters.addwithvalue("@value7", drv[12]);//bill g                         cmd1.parameters.addwithvalue("@value8", drv[3]);//trans id                         cmd1.parameters.addwithvalue("@value9", drv[4]);//prod id                         cmd1.parameters.addwithvalue("@value10", drv[5]);//prod desc                         cmd1.parameters.addwithvalue("@value11", drv[6]);//quantity                         cmd1.parameters.addwithvalue("@value12", drv[7]);//invoice no                         cmd1.parameters.addwithvalue("@value13", drv[8]);//prev sent                         cmd1.parameters.addwithvalue("@value14", drv[9]);//comments                         cmd1.executenonquery();                         cn.close();                     }                 }             } 

well, if need import data data source (e.g datatable, arrays, list, etc.) ms excel worksheet or export data worksheet fill datatable or array, etc. in 1 go, may try relevant aspose.cells apis (which efficient) task. should check specific methods of cells class (e.g cells.importdatatable, cells.exportdatatable, etc. - there other useful methods too.) requirements. moreover, datatable inserted in such way other contents shifted under table accordingly.

1) see line of code importing data table worksheet:

//importing contents of datatable worksheet starting "a1" cell, //where true specifies column names of datatable added //the worksheet header row worksheet.cells.importdatatable(datatable, true, "a1"); 

2) see sample code segment on exporting data worksheet fill datatable:

//exporting contents of first 7 rows , 2 columns (a1:b7) starting 1st cell datatable datatable datatable = worksheet.cells.exportdatatable(0, 0, 7, 2, true); 

i working developer/evangelist @ aspose.