none
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.

    Thanks

    Thursday, March 9, 2017 5:42 PM

Answers

  • Hi StSingh,

    please refer code below.

    private void  PopulateFromSql()
    
    {
    
        try
    
        {
    
            // DataTable Construction with Adapter and Connection 
    
            var conn = new  SqlConnection(textBoxCS.Text);
    
            var strSql = richTextBoxSQLEdit.Text;
    
            conn.Open();
    
            var da = new  SqlDataAdapter(strSql, conn);
    
            var dt = new  System.Data.DataTable();
    
            da.Fill(dt);
    
            // 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
    
                    progressBarGetData.Refresh();
    
                }
    
            }
    
        }
    
        catch (Exception ex)
    
        {
    
            throw new  Exception(ex.ToString());
    
        }
    
    }

    Reference:

    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
    
        Try
            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

    Regards

    Deepak


    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.


    Friday, March 10, 2017 9:13 AM
    Moderator