Speed Up Oledb writing to Excel

Question

I am trying to write a program which reads, sorts, and rewrites Excel sheets, I have already written to the point that it can do it all, my main concern now is speeding up the write process somehow.

Is there anyway that anyone knows of to do this? Here is my code for writing to the new Excel sheet

foreach (DataRow dr in WriteThis.Rows)
{
    OleDbCommand Command2 = new OleDbCommand();
    string CommText = "INSERT INTO [" + Table + "$] Values(";
    foreach (string s in Columns)
    {
        CommText = CommText + '\u0022' + dr[s] + '\u0022' + ", ";
    }
    CommText = CommText.Remove(CommText.Length - 2);
    CommText += ");";
    //MessageBox.Show(CommText);
    Command2.CommandText = CommText;
    // CommText = CommText.Replace("\\", "");
    Command2.CommandText = CommText;
    Command2.Connection = Conn;
    Conn.Open();
    Command2.ExecuteNonQuery();
    Conn.Close();
}

Not sure if this is what you're looking for but here's one of my earlier answers: http://stackoverflow.com/questions/12807362/writing-to-excel-using-oledb/12807447#12807447 — xxbbcc, Nov 11 '14 at 18:55
Daniel, please notice your question is also suitable for [CodeReview](http://codereview.stackexchange.com) because your current code is working and you are seeking optimization. — , Nov 12 '14 at 08:22

score 0 · Answer 1 · answered Mar 26 '15 at 12:01

take out of the loop the connection opening

Conn.Open();
foreach (DataRow dr in WriteThis.Rows)
{
    OleDbCommand Command2 = new OleDbCommand();
    string CommText = "INSERT INTO [" + Table + "$] Values(";
    foreach (string s in Columns)
    {
        CommText = CommText + '\u0022' + dr[s] + '\u0022' + ", ";
    }
    CommText = CommText.Remove(CommText.Length - 2);
    CommText += ");";
    //MessageBox.Show(CommText);
    Command2.CommandText = CommText;
    // CommText = CommText.Replace("\\", "");
    Command2.CommandText = CommText;
    Command2.Connection = Conn;
    //Conn.Open();
    Command2.ExecuteNonQuery();
    //Conn.Close();
}
Conn.Close();

Hope helps

Speed Up Oledb writing to Excel

1 Answers1