Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XLSExport: A CLR procedure to export proc results to Excel

By Bret Lowery,

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.

 

Total article views: 8651 | Views in the last 30 days: 25
 
Related Articles
FORUM

Permissions to execute sp_procedure_params_rowset

execute of sp_procedure_params_rowset return empty recordset

FORUM

Help with frustrating BCP call and stored procedure params

BCP + Stored Procedure queryout with SP params - "quotes" are killing me !!!!

FORUM

Convert String DataType to Date datatype in DTS

Convert String DataType to Date datatype in DTS

FORUM

convert the datatype string to date

convert the datatype string to date

FORUM

convert datatype DT_STRING to DT_DBDATE in ssis

convert datatype DT_STRING to DT_DBDATE in ssis

Tags
c#    
excel    
export    
sqlclr    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones