Introduction

NPOI is a .NET project which can read and write read/write xls, doc, and ppt files without the need for a Microsoft Office installation or license. Formerly on CodePlex, the project is now located on Googlecode at http://code.google.com/p/npoi/.

In this CLR example, I'll use NPOI to create a procedure that takes a stored procedure name and list of parameters and parameter values for that procedure, and outputs an Excel spreadsheet that contains the execution results of that proc to the passed .XLS file specification. The advantage to a CLR solution is that (in addition to lacking an MS Office install or license requirement) ActiveX and COM components are not required on your otherwise pristine SQL Server to accomplish this task.

This procedure supports output data types of CHAR, NCHAR, VARCHAR, NVARCHAR, all numerics, DATE, TIME, DATETIME, DATETIME2, BIT, and BOOLEAN. Other datatype outputs are not currently supported, but are easily added if needed.

The CLR Procedure

The declaration for my CLR procedure looks like this:

 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void xsp_Export_Excel(
    [SqlFacet(MaxSize = 128)] SqlString ProcName,
    SqlXml XMLParams,
    SqlInt32 CommandTimeout,
    [SqlFacet(MaxSize = 1024)] SqlString FileSpec,
    SqlBoolean OverwriteIfExists,
    SqlBoolean HeaderRow,
    [SqlFacet(MaxSize = 32)] SqlString SheetName
 )

The parameters are:

ProcName
The name of the stored procedure whose results you want to write to the Excel spreadsheet.

XMLParams
An XML-formatted list of parameters that the procedure takes. For example, if my stored procedure takes three parameters -- CustomerID (an int), UserName (varchar(80)), and CurrentDateTime (a datetime) -- and I want to output data for Customer 12345, UserName "Blaque Jacque Shellaque", and a current datetime of 2011-06-10 08:00:00, I pass this XML as a string:

<params>
   <param name="CustomerID" value="12345" />
   <param name="UserName" value="Blaque Jacque Shellaque" />
   <param name="CurrentDateTime" value="2011-06-10 08:00:00" />
</params>

Note that the ordering does not matter; this will work too:

<params>
 <param name="CurrentDateTime" value="2011-06-10 08:00:00" />
 <param name="UserName" value="Blaque Jacque Shellaque" />
 <param name="CustomerID" value="12345" />
</params>

And if any parameters have default values, you can just omit them from the XML entirely to use the default:

<params>
  <param name="CustomerID" value="12345" />
  <param name="UserName" value="Blaque Jacque Shellaque" />
</params>

CommandTimeout
Specifies the CommandTimeout in seconds to use when executing the procedure. Zero means no timeout.

FileSpec
Specifies the full path, filename, and extension of the Excel file you want to write. This must be a location local to the SQL Server server itself or a network location accessible from it.

OverwriteIfExists
1=Overwrite the Excel file if it exists, 0= don't overwrite it and throw an error if it is found.

HeaderRow
1=Write a header row to the Excel spreadsheet with the name of the result set columns as the headers, 0=no header row.

SheetName
Name to apply to the sheet created in the spreadsheet in which your results will appear.

The CLR Procedure

The procs starts by making a few sanity checks:

//check for empty parameters
if (ProcName.Value == string.Empty)
 throw new Exception("Procedure name value is missing.");

// parse filespec
FileInfo fi = new FileInfo(FileSpec.Value);
string filePath = fi.DirectoryName;
string fileName = fi.Name;

if (filePath == string.Empty)
 throw new Exception("Missing file path location.");

if (fileName == string.Empty)
 throw new Exception("Missing name of file.");

// does excel spreadsheet already exist?
if (fi.Exists == true && OverwriteIfExists.IsFalse)
 throw new Exception("File already exists, and OverwriteIfExists was specified as false.");

Next, I create a DataSet in which we will load the called procedure's results, and open a context connection to prepare to execute the procedure:

DataSet exportData = new DataSet();

using (SqlConnection conn = new SqlConnection("context connection=true"))
{

   SqlCommand getOutput = new SqlCommand();
   getOutput.CommandText = ProcName.ToString(); 
   getOutput.CommandType = CommandType.StoredProcedure;
   getOutput.CommandTimeout = CommandTimeout.Value;

Parsing the XML parameters and adding them to the SQLCommand object is deceptively simple:

      string paramName;
      string paramValue;
      using (XmlReader parms = XMLParams.CreateReader())
      { 
         while(parms.Read()
         { 
            if (parms.Name == "param")
            {
               paramName = parms.GetAttribute("name");
               paramValue = parms.GetAttribute("value");
               getOutput.Parameters.AddWithValue(paramName, paramValue);
            }
         }
      }

Next, I open my connection, execute the procedure, and populate a DataTable with the results:

     getOutput.Connection = conn;
     conn.Open();
     SqlDataAdapter da = new SqlDataAdapter(getOutput);
     DataTable dt = new DataTable();
     int rc = da.Fill(dt);

Once I have results, and there was no error executing the proc, I check to see if the spreadsheet exists and OverwriteIfExists = 1. If both conditions are TRUE, I delete the existing spreadsheet. By putting this step here, I avoid overwriting the spreadsheet if the proc I want to execute fails for any reason -- I only overwrite if the proc execution was successful.

     if (fi.Exists == true && OverwriteIfExists == true)
     {
       fi.Delete();
       if (fi.Exists == true)
          throw new Exception("Unable to overwrite the existing file!");
     }

Now the juicy part. I create a new spreadsheet object using NPOI, and a FileStream object to stream the data to the file:

 HSSFWorkbook hssfworkbook = new HSSFWorkbook();
 FileStream fs = new FileStream(FileSpec.ToString(), FileMode.CreateNew);

Next, I created a new sheet in the spreadsheet object just created, and add a header row if it was requested. NPOI exposes HSSFSheet, HSSFRow, and HSSFCell objects for this purpose. I set the header row cell type to a string, and the header row cell value to the name of the corresponding column from the DataTable:

 HSSFSheet sh = hssfworkbook.CreateSheet(SheetName.ToString());
 int RowOffset = 0;
 if (HeaderRow.Value == true)
 {
     RowOffset = 1;
     HSSFRow headerrow = sh.CreateRow(0);
     for (int h = 0; h < dt.Columns.Count; h++)
     {
        HSSFCell headercell = headerrow.CreateCell(h);
        headercell.SetCellType(HSSFCell.CELL_TYPE_STRING);
        headercell.SetCellValue(dt.Columns[h].ColumnName);
     }
}

Now I enumerate through the DataTable rows, create a matching row in HSSFSheet, and populate each cell in the row with the data. I use string formatted cells for datetimes; you might want to change this to actual Excel datetime formats for your purposes. Also, I replace string and boolean null values with the literal string "NULL", for clarity.

for (int r = 0; r < dt.Rows.Count; r++)
{
  HSSFRow row = sh.CreateRow(r + RowOffset);
  string val = "";
  for (int c = 0; c < dt.Columns.Count; c++)
  {
     HSSFCell cell = row.CreateCell(c);
     if (
         dt.Columns[c].DataType == System.Type.GetType("System.Int")
      || dt.Columns[c].DataType == System.Type.GetType("System.Int16")
      || dt.Columns[c].DataType == System.Type.GetType("System.Int32")
      || dt.Columns[c].DataType == System.Type.GetType("System.Int64")
      || dt.Columns[c].DataType == System.Type.GetType("System.Numeric")
      || dt.Columns[c].DataType == System.Type.GetType("System.Double")
      || dt.Columns[c].DataType == System.Type.GetType("System.Real")
    )
    {
       cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
       if (dt.Rows[r].ItemArray[c] != DBNull.Value)
       {
          cell.SetCellValue(Convert.ToInt32(dt.Rows[r].ItemArray[c]));
       }
    }
    else if (
        dt.Columns[c].DataType == System.Type.GetType("System.String")
     || dt.Columns[c].DataType == System.Type.GetType("System.DateTime")
    )
    {
       cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
       if (dt.Rows[r].ItemArray[c] == DBNull.Value)
       {
          cell.SetCellValue("NULL");
       }
       else
       {
          cell.SetCellValue(dt.Rows[r].ItemArray[c].ToString());
       }
    }
    else if (
       dt.Columns[c].DataType == System.Type.GetType("System.Boolean"))
    {
       if (dt.Rows[r].ItemArray[c] == DBNull.Value)
       {
          cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
          cell.SetCellValue("NULL");
       }
       else
       {
         cell.SetCellType(HSSFCell.CELL_TYPE_BOOLEAN);
         cell.SetCellValue(Convert.ToBoolean(dt.Rows[r].ItemArray[c]));
       }
    }
    else
    {
       string errMessage = "An invalid or unsupported datatype '" + dt.Columns[c].DataType.ToString() + "' was encountered in the result set of the procedure. Supported SQL types are: Bit, Boolean, DateTime, Date, Time, all numeric types, and String.";
       throw new Exception(errMessage);
    }
  }
}

And finally, we write the results to the target file, and close the FileStream, and that's it!

hssfworkbook.Write(fs);
fs.Close();

Other Possibilities

These are just some of the many options available in NPOI. You can even control cell color, shading, borders, fonts, font sizes, formulas, etc. from within your CLR code or using parameters passed to the procedure (imagine green/yellow/red shading applied to the cell based on the data value in it). This is left as an exercise for the Gentle Reader.

Download

Please read the disclaimer below, and understand that I do not provide any support for this software, source or binaries, including but not limited to installation, configuration, maintenance, and tuning. If you're good with that, then you can download the complete binaries and source code here.

Disclaimer

The program (source code AND binaries) is provided to you as is, without warranty. There is no warranty for the program, expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose and non infringement of third party rights. The entire risk as to the quality and performance of the program is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair and correction.