need a quicker way to select data and paste it in an excel sheet

Question

It took me a while to just find a way to select data from my database and then paste it in an excel spreadsheet. Now that I've found it, it runs dirt slow. Like I said before, I've looked at a lot of different ways to accomplish this but have not been able to correctly implement any of them except for this. I'm not married to this option but it is the only one I could get to work. Could someone help me out by suggesting a quicker way to accomplish this simple task? Please see my code below.

Record850 rec850 = new Record850();
List<Record850> lst850records = new List<Record850>();
//SqlConnection connStr = new SqlConnection("Server = 172.18.211.76; Database = Processstage; User Id = brendon.davies; Password = mypassword;");
SqlConnection conn = new SqlConnection("Server = 172.18.211.76; Database = Processstage; User Id = brendon.davies; Password = mypassword;");

         //SqlConnection sqlConnection1 = new SqlConnection(conn);
         SqlCommand cmd = new SqlCommand();
         SqlDataReader reader;

         cmd.CommandText = Select_850;
         cmd.CommandType = CommandType.Text;
         cmd.Connection = conn;
         conn.Open();
         Record850 reco850 = new Record850();

         string strComplete = "";

         reader = cmd.ExecuteReader();
         while (reader.Read())
         {
             try
             {

                 reco850.OrgName = reader.GetString(0);
                 reco850.WholeSalerAccountDivisionCode = reader.GetString(1);
                 reco850.File_Process_Name = reader.GetString(2);
                 reco850.Pur_Ord_Num_BEG03 = reader.GetString(3);
                 reco850.File_Process_ID = reader.GetInt64(4);
                 reco850.CECode = reader.GetString(5);
                 reco850.CEName = reader.GetString(6);
                 reco850.Modified_Date = reader.GetDateTime(7);
                 lst850records.Add(reco850);
                 reco850 = new Record850();

             }
             catch(Exception e)
             {
                 Console.WriteLine(e.ToString());
             }


         }
         conn.Close();

         eWorkSheet = (Excel.Worksheet)oSheets.get_Item("850_Template");
         eWorkSheet.Activate();



         int int850counter = 0;
         int int850RowCounter = 3;
         foreach (Record850 r850 in lst850records)
         {
             strComplete = lst850records[int850counter].OrgName + "\t" +
                           lst850records[int850counter].WholeSalerAccountDivisionCode + "\t" +
                           lst850records[int850counter].File_Process_Name + "\t" +
                           lst850records[int850counter].Pur_Ord_Num_BEG03 + "\t" +
                           lst850records[int850counter].File_Process_ID + "\t" +
                           lst850records[int850counter].CECode + "\t" +
                           lst850records[int850counter].CEName+ "\t" +
                           lst850records[int850counter].Modified_Date;


             CR = (Excel.Range)eWorkSheet.Cells[int850RowCounter,3];
             Clipboard.SetText(strComplete);
             CR.Select();
             eWorkSheet.Paste(CR, false);
             Clipboard.Clear();
             int850RowCounter++;
             int850counter++;
             strComplete = ""
}

Code doesn't look too bad; what seems to be the problem? Are you getting an error? — John Wu, Feb 14 '17 at 23:03
suggestions: 1) use EPPlus; 2) hide excel while populating for speed-up 3) don't use the clipboard, write directly into the cells for speed-up — Cee McSharpface, Feb 14 '17 at 23:11
Don't use the clipboard at all. Put the data into a variant array of variant and use a range. There's an example of doing so in Delphi [here](http://stackoverflow.com/a/16642049/62576). — Ken White, Feb 14 '17 at 23:26
Is there any reason which does not allow you to move the code in foreach to while loop where you read the records. Moreover if it is a simple excel sheet then you could also generate a CSV out of the records which can be opened as excel. — Amit, Feb 14 '17 at 23:35
It doesn't look like you're doing anything complicated here. Is there any reason you're not using Excel's built-in ability to [get external data from a SQL database](https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad)? — saarp, Feb 15 '17 at 07:59

score 1 · Answer 1 · answered Feb 15 '17 at 00:40

Here's an alternative that uses the EPPlus approach, much faster.

        try {
            var app = new ExcelPackage(new FileInfo(SOURCE_PATH));
            var ws = app.Workbook.Worksheets["850_Template"];
            int row = 3;
            using (SqlConnection cn = new SqlConnection(CN_STR)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataReader dr = cmd.ExecuteReader()) {
                        while (dr.Read())
                        {
                            ws.SetValue(row, 1, (string) dr["OrgName"]);
                            ws.SetValue(row, 2, (string) dr["WholeSalerAccountDivisionCode"]);
                            ws.SetValue(row, 3, (string) dr["File_Process_Name"]);
                            ws.SetValue(row, 4, (string) dr["Pur_Ord_Num_BEG03"]);
                            ws.SetValue(row, 5, (long) dr["File_Process_ID"]);
                            ws.SetValue(row, 6, (string) dr["CECode"]);
                            ws.SetValue(row, 7, (string) dr["CEName"]);
                            ws.SetValue(row, 8, (DateTime) dr["Modified_Date"]);
                            row++;
                        }
                    }
                }
                cn.Close();
            }
            app.Save();
        } catch (Exception ex) {
            Console.WriteLine(ex.Message);
        }

score 1 · Accepted Answer · answered Feb 15 '17 at 01:15

Here's an answer that also runs quickly, just about the same as the use of EPPlus above. Do not attempt the cell by cell approach (commented out below) as that indeed runs at a snail's pace by comparison.

    [STAThread]
    static void Main(string[] args)
    {
        Excel.Application xl = null;
        try {
            xl = new Excel.Application();
            xl.ScreenUpdating = false;
            xl.Visible = false;
            xl.UserControl = false;
            var wb = xl.Workbooks.Open(SOURCE_PATH);
            var ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.Item["850_Template"];
            StringBuilder sb = new StringBuilder();
            //int row = 3;
            // var a1 = ws.Range["A1", Missing.Value];
            using (SqlConnection cn = new SqlConnection(CN_STR)) {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(SQL, cn)) {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataReader dr = cmd.ExecuteReader()) {
                        while (dr.Read())
                        {
                            sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\r\n",
                                (string)dr["OrgName"],
                                (string)dr["WholeSalerAccountDivisionCode"],
                                (string)dr["File_Process_Name"],
                                (string)dr["Pur_Ord_Num_BEG03"],
                                (long)dr["File_Process_ID"],
                                (string)dr["CECode"],
                                (string)dr["CEName"],
                                (DateTime)dr["Modified_Date"]
                                );
                            //a1.Offset[row, 0].Value = (string)dr["OrgName"];
                            //a1.Offset[row, 1].Value = (string)dr["WholeSalerAccountDivisionCode"];
                            //a1.Offset[row, 2].Value = (string)dr["File_Process_Name"];
                            //a1.Offset[row, 3].Value = (string)dr["Pur_Ord_Num_BEG03"];
                            //a1.Offset[row, 4].Value = (long)dr["File_Process_ID"];
                            //a1.Offset[row, 5].Value = (string)dr["CECode"];
                            //a1.Offset[row, 6].Value = (string)dr["CEName"];
                            //a1.Offset[row, 7].Value = (DateTime)dr["Modified_Date"];
                            //row++;
                        }
                    }
                }
                cn.Close();
            }
            Clipboard.SetText(sb.ToString(),
                TextDataFormat.Text);
            var rng = ws.Range["A3", Missing.Value];
            rng.Select();
            ws.Paste(rng, Missing.Value);
            Clipboard.Clear();
            wb.Save();
            wb.Close();
            xl.Quit();
        } catch (Exception ex) {
            Console.WriteLine(ex.Message);
            if (xl != null) {
                xl.ScreenUpdating = true;
                xl.Visible = true;
                xl.UserControl = true;
            }
        }
    }

Tahbaza, I had to modify your answer a little bit but in the end it worked like a charm. Thanks a lot! — Brendon.Davies, Feb 19 '17 at 16:22
Great, btw, you should mark this answer as accepted (checkmark) if this is indeed your accepted answer. Welcome to SO. — Tahbaza, Feb 21 '17 at 15:08

need a quicker way to select data and paste it in an excel sheet

2 Answers2