Visual Studio Tools for Office (VSTO) and SAS Integration

Software Used

Microsoft Office 2003 SP1
Visual Studios 2005
SAS version 9.13

Excel Project

1. Start Visual Studios 2005 and create a new Office project, specifying Excel
2. Enable VS2005 to allow access to the VBA engine (security)

3. On the right hand side of the screen, right-click ThisWorkbook.cs, select View Code

Here is the complete code listing. Replace the existing code in ThisWorkbook.cs with the following:

using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace SasToExcel
{
public partial class ThisWorkbook
{
private Excel.Worksheet xlSheet = null;
private Excel.Chart xlChart = null;

//The @ sign in front of a string means ignore special characters
string sasLibrary = @"C:\Program Files\SAS\SAS 9.1\core\sashelp";
string sasDataSet = "retail";

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
ResetWorkbook();
DataTable dt = LoadSasDataSet();
CreateWorksheet();
LoadAndFormatData(dt);
CreateChart();
}

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
}

#region VSTO Designer generated code

///

/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisWorkbook_Startup);
this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
}

#endregion

private void ResetWorkbook()
{
// Get rid of all but the original worksheet.

try
{
ThisApplication.DisplayAlerts = false;

foreach (Excel.Worksheet ws in Globals.ThisWorkbook.Worksheets)
if (ws != ThisApplication.ActiveSheet)
{
ws.Delete();
}
foreach (Excel.Chart cht in Globals.ThisWorkbook.Charts)
cht.Delete();
}
finally
{
ThisApplication.DisplayAlerts = true;
}
}

private DataTable LoadSasDataSet()
{
DataTable dt = new DataTable();
OleDbConnection sas = new OleDbConnection("Provider=sas.LocalProvider; Data Source=" + sasLibrary);
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = sasDataSet;
OleDbDataReader sasRead = sasCommand.ExecuteReader();
dt.Load(sasRead);
return dt;
}

private void CreateWorksheet()
{
xlSheet = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.Add(Type.Missing, Globals.ThisWorkbook.ActiveSheet,
Type.Missing, Type.Missing);

xlSheet.Name = "Sas Retail Sample";

// Copy field names to Excel.
// Bold the column headings.
Excel.Range rng = (Excel.Range)xlSheet.Cells[1, 1];
rng.Formula = "Year";
rng.Font.Bold = true;

rng = (Excel.Range)xlSheet.Cells[1, 2];
rng.Formula = "Sales";
rng.Font.Bold = true;
}

private void LoadAndFormatData(DataTable dt)
{
int row;
// Copy the data in from the SqlDataReader.
// Start at row 2.
row = 1;
foreach (DataRow dr in dt.Rows)
{
row += 1;
xlSheet.Cells[row, 1] = dr["Year"];
xlSheet.Cells[row, 2] = dr["Sales"];
}

// Format the columns.
((Excel.Range)xlSheet.Columns[1, Type.Missing]).AutoFit();

Excel.Range rng = (Excel.Range)xlSheet.Columns[2, Type.Missing];
rng.NumberFormat = "0.00";
rng.AutoFit();
}

private void CreateChart()
{
// Now create the chart.
Excel.Chart xlChart = (Excel.Chart) Globals.ThisWorkbook.Charts.
Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);

Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1];
xlChart.ChartWizard(cellRange.CurrentRegion,
Excel.Constants.xl3DBar, Type.Missing,
Excel.XlRowCol.xlColumns, 1, 2, false,
xlSheet.Name, Type.Missing, Type.Missing,
Type.Missing);

// Apply some formatting to the chart.
xlChart.Name = xlSheet.Name + " Chart";

Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);
grp.GapWidth = 20;
grp.VaryByCategories = true;
xlChart.ChartTitle.Font.Size = 16;
xlChart.ChartTitle.Shadow = true;
xlChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}

}
}

4. Press F5 to build and see the results