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

Question

So I am looking for a faster insert method into spreadsheets on a server. Right now it depends on the data being used but takes about 20 seconds to insert 100 rows into a single spreadsheet. I understand why it's doing it, because inserting into a spreadsheet causes excel to shift the rows each time a new row is inserted. So the more rows being added to a single spreadsheet, the longer it will take. I tested this theory and it was correct, I created about 100 spreadsheets and randomly inserted 1000 rows spread across them all. This took around 60 seconds to complete. Inserting the same 1000 rows into a single spreadsheet took over 5 minutes to complete. Here is my 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 for ebid
            {
                if (combinedList[combinedCount][1] == "") //if its empty it goes into brandies sheet
                {

                    sheet.Cells.InsertRow(2);
                    using (OleDbConnection cn = new OleDbConnection(connectionString))
                    {
                        cn.Open();
                        OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [" + combinedList[combinedCount][0] + "$] " + //"+sheetCnt+"
                             "([Reporting Retailer EBID],[Outlet BASF ID],[Retailer OT],[Mapped Grower ID],[Mapped Grower],[Ship To Grower],[Bill To 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 to g
                        cmd1.Parameters.AddWithValue("@value7", drv[12]);//bill to 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();
                    }
                }
            }

Possible duplicate of [What is the fastest way to insert 100 000 records into an MDB file in C#](https://stackoverflow.com/questions/21850137/what-is-the-fastest-way-to-insert-100-000-records-into-an-mdb-file-in-c-sharp) — Samvel Petrosov, Jul 28 '17 at 14:34
Haven't done this for some time, but I remember that the fastest way I found to get lots of data into Excel quickly, was to set up a data table in one sheet containing all the data, and then to use pivot table in other sheets to extract the data I wanted. This took seconds rather than minutes — Jonathan Willcock, Jul 28 '17 at 14:46
@JonathanWillcock My issue isn't with the sheets. Is there a way to specify the 'next row' in a sense? Was looking to INSERT INTO [" + combinedList[combinedCount][0] + "$a5:g] or something like that — triswill227, Jul 28 '17 at 14:50
Sorry I was just trying to point out that line by line insertion (in databases just as in excel) is always slow. A bulk insert is quicker. I was suggesting therefore that you look at your overall spreadsheet design to see if there isn't an alternative to the INSERT approach. Pivot tables worked for me (I was dealing with c 23,000 rows and 40 odd columns!). — Jonathan Willcock, Jul 28 '17 at 14:55

score 0 · Answer 1 · answered Jul 28 '17 at 17:53

Well, if you need to import data from some data source (e.g DataTable, Arrays, List, etc.) into MS Excel worksheet or export data from worksheet to fill a DataTable or array, etc. in one go, then you may try relevant Aspose.Cells APIs (which are efficient) for the task. You should check specific methods of Cells class (e.g Cells.ImportDataTable, Cells.ExportDataTable, etc. - there are some other useful methods too.) for your requirements. Moreover, your datatable would be inserted in such a way that other contents would be shifted under the table accordingly.

1) See the line of code for importing data table into worksheet:

//Importing the contents of DataTable to the worksheet starting from "A1" cell,
//where true specifies that the column names of the DataTable would be added to
//the worksheet as a header row
worksheet.Cells.ImportDataTable(dataTable, true, "A1");

2) Similarly see the sample code segment on exporting data from worksheet to fill a DataTable:

//Exporting the contents of first 7 rows and 2 columns (A1:B7) starting from 1st cell to DataTable
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 7, 2, true);

I am working as a developer/Evangelist at Aspose.

triswill227 · Accepted Answer · 2017-07-31T18:14:04.240

I figured out how to reduce it tremendously. As I said before, the insert into function causes congestion when dealing with large datasets. So target each cell and use the putvalue function. For example:

                combinedCount = 0;
                counter = 0;
                int testCount = 2;
                foreach (DataRowView drv in view)
                   {
                        if (combinedCount != 0)//if its the first one, there is nothing to compare it to
                        {
                            if (combinedList[combinedCount - 1][0] == combinedList[combinedCount][0])//look at the previous one to check if its the same as the one before it, because we dont want to increment it everytime else it will increment on a different and empty page
                            {
                                testCount++;
                            }
                            else
                            {
                                testCount = 2; //2 is where our first row starts on each page
                            }
                        }
                        else { }
                           Aspose.Cells.Worksheet sheet = wb.Worksheets[combinedList[combinedCount][0]];
                           Aspose.Cells.Cell cell1 = sheet.Cells["A" + testCount];
                           Aspose.Cells.Cell cell2 = sheet.Cells["B" + testCount];
                           Aspose.Cells.Cell cell3 = sheet.Cells["C" + testCount];
                           Aspose.Cells.Cell cell4 = sheet.Cells["D" + testCount];
                           Aspose.Cells.Cell cell5 = sheet.Cells["E" + testCount];
                           Aspose.Cells.Cell cell6 = sheet.Cells["F" + testCount];
                           Aspose.Cells.Cell cell7 = sheet.Cells["G" + testCount];
                           Aspose.Cells.Cell cell14 = sheet.Cells["N" + testCount];
                           Aspose.Cells.Cell cell15 = sheet.Cells["O" + testCount];
                           Aspose.Cells.Cell cell16= sheet.Cells["P" + testCount];
                           Aspose.Cells.Cell cell17 = sheet.Cells["Q" + testCount];
                           Aspose.Cells.Cell cell18 = sheet.Cells["R" + testCount];
                           Aspose.Cells.Cell cell19 = sheet.Cells["S" + testCount];
                           Aspose.Cells.Cell cell20 = sheet.Cells["T" + testCount];

                           cell1.PutValue(drv[0]);
                           cell2.PutValue(drv[1]);
                           cell3.PutValue(drv[13]);
                           cell4.PutValue(drv[2]);
                           cell5.PutValue(drv[10]);
                           cell6.PutValue(drv[11]);
                           cell7.PutValue(drv[12]);
                           cell14.PutValue(drv[3]);
                           cell15.PutValue(drv[4]);
                           cell16.PutValue(drv[5]);
                           cell17.PutValue(drv[6]);
                           cell18.PutValue(drv[7]);
                           cell19.PutValue(drv[8]);
                           cell20.PutValue(drv[9]);  

}

It's a little sloppy but you get the point. 60+ secs went down to a solid 40 seconds, 25 seconds went down to 15. While still not lightning fast, inserting into a spreadsheet will never be super fast.

Another issue I found that took run time down to single digits is the save feature. I removed the save from the foreach loop and put it outside, it still saves properly.

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

2 Answers2