.NET Component for Excel 2003 spreadsheet generation using templates for formatting

Question

The problem my application is trying to solve is this:

Currently, several stored procedures are run, the data is manually copied out of the SQL results into a blank spreadsheet, and reporting analysts format the data. This process has been deemed too long and costly.

The ideal solution (in my mind) is a .NET application with a component that can take a stored procedure result and write it to a specific cell range in a worksheet template (maintaining template formatting) and then I can just collate the results into workbooks in the .NET application.

Are there any such applications out there? Even better, does anyone have any better solutions?

Thanks for your consideration.

Answer 1

What I use to set the data

get the data in the form of

object[,] excelSpreadsheetData

then apply the data by setting

public void SetWorksheetData(long rowCount, long columnCount, object[,] excelSpreadsheetData, int startingRow, int startingCol, Worksheet worksheet)
        {
            if (rowCount == 0 || columnCount == 0) return;
            //set the region data.
            object m_objOpt = Missing.Value;
            Range cellRange = worksheet.get_Range(ExcelGeneratorUtils.ExcelColumnFromNumber(startingCol) + startingRow, m_objOpt);
            cellRange = cellRange.get_Resize(rowCount, columnCount);
            cellRange.set_Value(m_objOpt, excelSpreadsheetData);
        }

This should keep the template formatting, and sets the data in one go, much faster than setting it cell, by cell.

Hope that helps