Creating Excel spreadsheets using Visual Studio Express for C# and SAS datasets

These steps show you how to make an Excel sheet using the Excel COM object, SAS, and C#. There are other ways to do this but it is a good way to get started.

Here are the steps needed to create the sheets:

Download Visual Studios Express for C# from Microsoft’s website: it is free.

http://lab.msdn.microsoft.com/express/vcsharp/default.aspx


Download and install the OLEDB Providers for SAS from the SAS website:

http://www.sas.com/apps/demosdownloads/oledbproviders_PROD_9.1.3_sysdep.jsp?packageID=000366

Create a directory c:\temp. Copy the shoes dataset from the sashelp files (this is for testing only) and place it in temp. You can find it where sas is installed under core\sashelp.

Once installed, create a new project (Fileà Newà Project). Select console type for now. After you become familiar with the steps, you can build a windows app.


You need to add in a reference to Excel. This basically is telling the project that the Excel classes will be available to your code. Look at the Solution Explorer on the right, right-click References and select Add Reference….Click on the COM tab at the top and then scroll down until you see Microsoft Excel 11.0 Object Library (you may have one slightly different such as 9.0, etc. Just select the Excel library you have).

Paste the following code into the default class that appears:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
// using Excel = Microsoft.Office.Interop.Excel;


namespace SASToExcel
{
class Program
{
static DataTable dt;
static Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
static Excel.Workbook wb;
static Excel.Worksheet ws;
static List regions = new List();

static void Main(string[] args)
{
app.DisplayAlerts = false;
dt = LoadSasDataSet(@"c:\temp", "shoes");
DetermineDistinctValuesForWorksheets();
CreateWorkbooks();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}

public static void DetermineDistinctValuesForWorksheets()
{
Excel.Workbooks wbs = app.Workbooks;
Excel.Workbook wb = wbs.Add(Type.Missing);

foreach (DataRow dr in dt.Rows)
{
if (!regions.Contains(dr["region"].ToString()))
{
regions.Add(dr["region"].ToString()) ;
}
}
}

public static void CreateWorkbooks()
{
Excel.Workbooks wbs = app.Workbooks;
wb = wbs.Add(Type.Missing);

foreach (string region in regions)
{
wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws = (Excel.Worksheet)wb.ActiveSheet;
//Create the worksheet name but clean up invalid values at the same time
ws.Name = region.Replace("/", " ");
Console.WriteLine("Wroksheet added: " + region);
DataRow[] rows = GetSelectedRegion(region);
AddDataToSheets(rows, region);
}

wb.SaveAs(@"c:\temp\SASToExcelSample.xls", Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
}

static DataRow[] GetSelectedRegion(string region)
{
string selectQuery = "region='" + region + "'";
DataRow[] subset = dt.Select(selectQuery);
return subset;
}

public static void AddDataToSheets(DataRow[] rows, string region)
{
int row = 1 ;
//Write Header records
WriteRow(row, 1, "Product");
WriteRow(row, 2, "Subsidiary");
WriteRow(row, 3, "Stores");
WriteRow(row, 4, "Sales");
row++;
foreach(DataRow dr in rows)
{
WriteRow(row, 1, dr["product"].ToString());
WriteRow(row, 2, dr["subsidiary"].ToString());
WriteRow(row, 3, dr["stores"].ToString());
WriteRow(row, 4, dr["sales"].ToString());
row++;
}
}

public static void WriteRow(int row, int col, string value)
{
Excel.Range rng = (Excel.Range)ws.Cells[row, col];
rng.Value2 = value;
}

public static DataTable LoadSasDataSet(string sasLibrary, string sasDataSet)
{
Console.WriteLine("Loading SAS data at library " + sasLibrary + ", dataset " + sasDataSet);
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);
Console.WriteLine("Records read from SAS: " + dt.Rows.Count.ToString());
return dt;
}

}
}

7. Press F5 to build and run the code. You will also have a new program called SasToExcel.exe in the project’s bin\debug folder

8. View your new workbook in the temp directory. It is called SasToExcelSample.xls