none
Performance VSTO Excel Addin - Loading 9M record from SQL

    Question

  • Hi,

    I'm using VS-2015 and VSTO - Excel Addin template to create a excel addin to populate excel sheet and update tbl if there are any changes...

    My problem has 2 parts

    1- Getting data from SQL server in a fastest / efficient way.

    2- Populating excel with this data. ( as of now i'm doing it cell by cell is there a quicker way?)

    I'm loading approx 9M records on a excel sheet... When I use power query it loads within 10-15 secs while if I'm creating a excel addin it takes ages to load the data..

    Can some one suggest a faster and better way to populate excel addin? 

    Please note: This is just a POC

    progressbar = new ProgressBar();
    
                    var sqlConnection = ConfigurationManager.ConnectionStrings["SqlDBConnection"].ConnectionString;
                    var conn = new SqlConnection(sqlConnection);
                    conn.Open();
    
                    SqlCommand sqlcmd = new SqlCommand("Select * from table1", conn);
                    var da = new SqlDataAdapter(sqlcmd);
                    var dt = new System.Data.DataTable();
                    da.Fill(dt);
    
                    // Define the active Worksheet
                    var sht = Globals.ThisAddIn.Application.ActiveSheet as Worksheet;
    
                    var rowCount = 0;
                    progressbar.Minimum = 1;
                    progressbar.Maximum = dt.Rows.Count;
    
                    // Loop thrue the Datatable and add it to Excel
                    foreach (DataRow dr in dt.Rows)
                    {
                        rowCount += 1;
                        for (var i = 1; i < dt.Columns.Count + 1; i++)
                        {
                            // Add the header the first time through 
                            if (rowCount == 2)
                            {
                                // Add the Columns using the foreach i++ to get the cell references
                                if (sht != null) sht.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                            }
                            // Increment value in the Progress Bar
                            progressbar.Value = rowCount;
                            // Add the Columns using the foreach i++ to get the cell references
                            if (sht != null) sht.Cells[rowCount, i] = dr[i - 1].ToString();
                            // Refresh the Progress Bar
                            //progressbar.refesh();
                        }
                    }

    Any suggestions please.

    Thanks for the help.




    Friday, March 17, 2017 1:55 PM

Answers

  • Hello,

    Try below code snippet, modify the writing part to Excel, instead of writing each cell, we can take use of range.value property.

    FYI~ the maximum rows allowed in Excel are 1,048,576 rows by 16,384 columns (Check Excel Limitations @ https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2016-2013),

     // Define the active Worksheet
                var sht = Globals.ThisAddIn.Application.ActiveSheet as excel.Worksheet;
    
                var rowCount = 0;
                progressbar.Minimum = 1;
                progressbar.Maximum = dt.Rows.Count;
    
                //// Loop thrue the Datatable and add it to Excel
                //foreach (DataRow dr in dt.Rows)
                //{
                //    rowCount += 1;
                //    for (var i = 1; i < dt.Columns.Count + 1; i++)
                //    {
                //        // Add the header the first time through 
                //        if (rowCount == 2)
                //        {
                //            // Add the Columns using the foreach i++ to get the cell references
                //            if (sht != null) sht.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                //        }
                //        // Increment value in the Progress Bar
                //        progressbar.Value = rowCount;
                //        // Add the Columns using the foreach i++ to get the cell references
                //        if (sht != null) sht.Cells[rowCount, i] = dr[i - 1].ToString();
                //        // Refresh the Progress Bar
                //        //progressbar.refesh();
                //    }
                //}
    
                //Instead of writing each cell, Preapre a dynamic array and write to excel in one shot --> VR
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        arr[r, c] = dr[c];
                    }
                }
                excel.Range c1 = (excel.Range)sht.Cells[1, 1];
                excel.Range c2 = (excel.Range)sht.Cells[dt.Rows.Count, dt.Columns.Count];
                excel.Range range = sht.get_Range(c1, c2);
                range.Value = arr;
                conn.Close();
                //Instead of writing each cell, create a array and write once to excel --< VR

    • Marked as answer by StSingh Wednesday, March 22, 2017 4:12 PM
    Wednesday, March 22, 2017 3:36 PM

All replies

  • Hello,

    I suggest you could try to use ADO connection to link SQL database and import it into Excel.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 2:53 AM
    Moderator
  • Thanks Celeste

    I tried using ADO.net entity framework but its not even closer to the earlier approach probably it could be because of converting to list i.e. type casting..


    • Edited by StSingh Tuesday, March 21, 2017 11:53 AM
    Tuesday, March 21, 2017 11:17 AM
  • I have also tried using a dataset https://msdn.microsoft.com/en-us/library/cc668212.aspx

    But if the table has records more than a million it throws exception.... has anyone else faced a similar problem ?

    Tuesday, March 21, 2017 5:12 PM
  • Hello,

    Try below code snippet, modify the writing part to Excel, instead of writing each cell, we can take use of range.value property.

    FYI~ the maximum rows allowed in Excel are 1,048,576 rows by 16,384 columns (Check Excel Limitations @ https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2016-2013),

     // Define the active Worksheet
                var sht = Globals.ThisAddIn.Application.ActiveSheet as excel.Worksheet;
    
                var rowCount = 0;
                progressbar.Minimum = 1;
                progressbar.Maximum = dt.Rows.Count;
    
                //// Loop thrue the Datatable and add it to Excel
                //foreach (DataRow dr in dt.Rows)
                //{
                //    rowCount += 1;
                //    for (var i = 1; i < dt.Columns.Count + 1; i++)
                //    {
                //        // Add the header the first time through 
                //        if (rowCount == 2)
                //        {
                //            // Add the Columns using the foreach i++ to get the cell references
                //            if (sht != null) sht.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                //        }
                //        // Increment value in the Progress Bar
                //        progressbar.Value = rowCount;
                //        // Add the Columns using the foreach i++ to get the cell references
                //        if (sht != null) sht.Cells[rowCount, i] = dr[i - 1].ToString();
                //        // Refresh the Progress Bar
                //        //progressbar.refesh();
                //    }
                //}
    
                //Instead of writing each cell, Preapre a dynamic array and write to excel in one shot --> VR
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        arr[r, c] = dr[c];
                    }
                }
                excel.Range c1 = (excel.Range)sht.Cells[1, 1];
                excel.Range c2 = (excel.Range)sht.Cells[dt.Rows.Count, dt.Columns.Count];
                excel.Range range = sht.get_Range(c1, c2);
                range.Value = arr;
                conn.Close();
                //Instead of writing each cell, create a array and write once to excel --< VR

    • Marked as answer by StSingh Wednesday, March 22, 2017 4:12 PM
    Wednesday, March 22, 2017 3:36 PM
  • Thanks VR-I

    I'm getting error on this line 

     var sht = Globals.ThisAddIn.Application.ActiveSheet as excel.Worksheet;

    Would it be something like ?

    var sht = Globals.ThisAddIn.Application.ActiveSheet as Worksheet;


    Apart from this I also tried another approach and it seems to work okay but if I push a more than 100K record it gives up :(

    Alternate approoch

     var dt = new System.Data.DataTable();
                    dt.Load(dreader);
    
                    
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
                    worksheet.Cells.ClearContents();
                                    
                    // Create a workhseet host item.
                    Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                    if (extendedWorksheet.Controls.IndexOf("BDetails")>-1)
                    {
                        extendedWorksheet.Controls.Remove("BDetails");                    
                    }
    
                    Microsoft.Office.Interop.Excel.Range cell = extendedWorksheet.Range["$A$1:$AK$5"];
                    
                    this.sListObject = extendedWorksheet.Controls.AddListObject(cell, "BDetails");
                    this.sListObject.AutoSetDataBoundColumnHeaders = true;               
    
                    this.sListObject.SetDataBinding(
                                dt, columns list);



    • Edited by StSingh Wednesday, March 22, 2017 4:21 PM
    Wednesday, March 22, 2017 4:20 PM
  • Yes StSingh thats correct.

    in your code, it should be like below line.

    var sht = Globals.ThisAddIn.Application.ActiveSheet as Worksheet;

    Best Regards,

    VR

    Thursday, March 23, 2017 6:38 AM