[SOLVED] fill a custom excel template from sql server proc with multiple result sets?

Issue

A stored proc (SQL Server 2008) returns multiple result sets.

An Excel (.xls) file with custom formatting – not a generic workbook, not a spreadsheet built on the fly – has particular cells on particular
worksheets where I need to correctly “paste” each appropriate result set
from the stored proc. The worksheets designated for holding data need to
receive the data, and then other worksheets in the workbook will display the data
with a high degree of formatting and with charts.

For example:
result set 1 needs to be pasted in a worksheet named ‘data01’
and beginning at cell B2;

result set 2 needs to be pasted ‘data01’
and beginning at cell K2;

result set 3 needs to be pasted in a worksheet named ‘data02’
and beginning at cell B2…

What are some approaches for tackling this problem
in a .NET environment? I have not found examples
or lessons which duplicate this scenario.

Update:
Essentially I’m wondering if it’s possible to do what
SpreadsheetGear does with an excel template, without
having to pay thousands for a third party tool.
http://www.spreadsheetgear.com/support/samples/excel.aspx

Solution

On Zach Hunter’s blog,
http://zachhunter.net/

he has a number of posts on how to make user of NPOI.
http://npoi.codeplex.com/

In particular, these two articles were invaluable:
"Use NPOI to populate an Excel template"
http://www.zachhunter.net/2010/05/npoi-excel-template/

"Improved NPOI ExportToExcel Function"
http://www.zachhunter.net/2010/06/improved-npoi-exportdatatabletoexcel-function/

Answered By – mg1075

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *