mercredi 25 novembre 2015

slow nested for loop to read excel objects c#

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