I am working on a form that will allow my users to choose a spread sheet and copy its data to a Sqlite database. My Sqlite query is transaction and happens in about 1 second but the portion of my code that is not running right is where it loops through the excel range and creates my insert statements. It is a nested for loop running over all the columns in the selected range and all the rows. This whole process for a spreadsheet with 360 rows and 43 columns takes about 4 minutes to run. I initially thought it was the sqlite insert so I converted it to a bulk insert. Once I dug in a bit and stepped through the code I realized it is the for loop causing this. Here is my code:
int counter = 1;
string contents = null;
string bulkInsert = null;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
contents = contents + Convert.ToString("'" + xlRange.Cells[i, j].Value2) + "',";
//MessageBox.Show(xlRange.Cells[i, j].Value2.ToString());
}
contents = contents.Remove(contents.Length - 1);
bulkInsert = bulkInsert + "INSERT INTO DeferralInput VALUES (" + contents + "); ";
contents = "";
}
bulkInsert = "BEGIN TRANSACTION; " + bulkInsert + " COMMIT;";
xlApp.Quit();
ExecuteQuery(bulkInsert);
If anyone has any thoughts on how to speed this up or if I did something wrong I am open to try anything. Thanks
Aucun commentaire:
Enregistrer un commentaire