Get data from SQL server using VSTO into Excel
Overview
Sometimes it is necessary to 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 to 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
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());
}
}
Grab the Download Sample here
References
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.