Download source code - 37.6 KB

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

Dock the custom TaskPane as needed "msoCTPDockPositionBottom;".
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

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

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.

Excel VSTO SQL Server Browser