ExcelAddin Data binding RRS feed

  • Question

  • Hi,

    I'm using VS2015 VSTO excel add-in

    Can someone please help me with data bindings... I'm looking for an example to get data from database and display it on excel.


    Thursday, March 9, 2017 5:42 PM


  • Hi StSingh,

    please refer code below.

    private void  PopulateFromSql()
            // DataTable Construction with Adapter and Connection 
            var conn = new  SqlConnection(textBoxCS.Text);
            var strSql = richTextBoxSQLEdit.Text;
            var da = new  SqlDataAdapter(strSql, conn);
            var dt = new  System.Data.DataTable();
            // Define the active Worksheet
            var sht = Globals.ThisAddIn.Application.ActiveSheet as  Worksheet;
            var rowCount = 0;
            progressBarGetData.Minimum = 1;
            progressBarGetData.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
                    progressBarGetData.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
        catch (Exception ex)
            throw new  Exception(ex.ToString());


    Get data from SQL server using VSTO into Excel

    below is another example in VB

    Function ImportSQLtoRange:

    Public Function ImportSQLtoRange(ByVal connectionString As String, _
        ByVal commandText As String, ByVal target As Excel.Range) As Integer
            Dim ws As Excel.Worksheet = target.Worksheet
            Dim arr(0, 0) As Object
            ExecuteOleDbDataReader(connectionString, commandText, AddressOf OleDbDataReaderToArray, arr)
            Dim rowCount As Long = arr.GetLength(1)
            Dim colCount As Integer = arr.GetLength(0)
            Dim r As Excel.Range   ' The complete target range with arr dimensions
            With ws
                r = .Application.Range( _
                    .Cells(target.Row, target.Column), _
                    .Cells(target.Row + rowCount - 1, target.Column + colCount - 1) _
            End With
            ' This method works very fast
            r.Value = ws.Application.WorksheetFunction.Transpose(arr)
            ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, r, , Excel.XlYesNoGuess.xlYes)
            ImportSQLtoRange = 0
        Catch ex As Exception
            ImportSQLtoRange = 1
            LastErrorMessage = ex.Message
        End Try
    End Function

    more code example are available in link below.

    Excel-SQL Server Import-Export using Visual Studio Tools for Office



    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

    Friday, March 10, 2017 9:13 AM