Click here to Skip to main content
Click here to Skip to main content
Go to top

Excel VSTO SQL Server Browser

, 29 Sep 2011
Rate this:
Please Sign up or sign in to vote.
Pull data directly from SQL Server to Excel using C# and VSTO.

Introduction

Sometimes it is necessary use Excel automation in order to grab some information from a specific data source and put it in Excel directly. The objective is to get more control about what you need to do to pull and create automatic processes to the end-user. This article is all about that. Let’s do this with a custom TaskPane.

Background

I am using a Document-Level add-in based on Visual Studio 2008 and was tested grabbing data from SQL Server 2008. It was tested with large amounts of data.

You must write or paste your connection string to the textbox. An example of an accepted one is: “Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;” and then you must write your SQL in a richtextbox.

In order to create a custom TaskPane you must add a UserControl on it; we will see how we can do this in another step.

Using the code

  1. Dock the custom TaskPane as needed "msoCTPDockPositionBottom;".
  2. Modify the "throw new Exception(ex.ToString());" as you need.

Add-in code

Write your code in the ThisAddIn class, which is provided in the ThisAddIn.cs code file. There are two event handlers in the project template code. To run code when the add-in is loaded, add code to the ThisAddIn_Startup event handler. To run code just before the add-in is unloaded, add code to the ThisAddIn_Shutdown event handler.

To add your UserControl to the TaskPane, we have to declare it as private:

private TpSqlEdit _tpSqlEdit; 

To use a custom TaskPane, you have to declare it. I am declaring it as public because I want to grab it from another class.

public Microsoft.Office.Tools.CustomTaskPane TpSqlEditCustomTaskPane; 

Initialize the User Control and add it to the custom TaskPane:

_tpSqlEdit = new TpSqlEdit();
TpSqlEditCustomTaskPane = CustomTaskPanes.Add(_tpSqlEdit, "SQL Editor");

Dock the TaskPane. I am docking it to bottom by default.

TpSqlEditCustomTaskPane.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionBottom;

Next step, make the TaskPane visible.

//Show TaskPane
TpSqlEditCustomTaskPane.Visible = true;

User Control code

Control.png

First we are going to create and populate our DataTable:

// DataTable Construction with Adapter and Connection 
var conn = new SqlConnection(textBoxCS.Text);
var strSql = richTextBoxSQLEdit.Text;
conn.Open();
var da = new SqlDataAdapter(strSql, conn);
ar dt = new System.Data.DataTable();
da.Fill(dt);

Define the active worksheet:

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

Using the DataTable and putting it all together in the active Worksheet

Next step, let’s loop to the DataTable DataRows and increment i to also increment the header's Excel columns as also the values in the cell’s corresponding to each header.

 // 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();
    }
}

Complete UserControl code

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());
    }
} 

Run.png

Points of interest

The connection string reference: http://www.connectionstrings.com/.

Deborah's Developer MindScape: http://msmvps.com/blogs/deborahk/archive/2009/07/23/writing-data-from-a-datatable-to-excel.aspx.

History

  • Version 1.0.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Author

Joao Tito Livio
Software Developer (Senior)
Portugal Portugal
I'am Developing my career in the IT industry and development, having worked specifically in areas related to system administration, database Administration and development. Developed specific skills in developmental screening/support for technical incidents, network management, Server Administration, software development, training, project management and technical advice.

Comments and Discussions

 
QuestionI like it...5 on your count Pinmemberafaz3-Oct-11 20:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 29 Sep 2011
Article Copyright 2011 by Joao Tito Livio
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid