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();
}
}
}
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.