Tuesday, January 10, 2006

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

No comments:

CTRL+Z does not generate EOF in Windows 10

In Windows 10, when I was trying to generate an EOF for a Java program, the CTRL+Z did not work. After doing some research (and help from f...