.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

In this question I asked for something similar. The difference is that I want something to generate an XML file that's interpreted by Excel as a spreadsheet. If you could send the data to an XML file, then transform the XML to SpreadsheetML, then this might solve your problem. SpreadsheetXM does have a few limitations, though. It won't require Excel to be installed and the transformation can be real fast. And if you use Excel 2007 then there's a second XML format that you could use, the Microsoft Office XML format. Basically, it's slow because I suspect that your current code is interacting with Excel through COM.

Answer 2

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

Answer 3

You can always use Excel Automation through interop. I have done this on occasion when the desired output is Excel. Its surprisingly quick, you have to be careful though to clean up after yourself and not leave instances of Excel running. Here is a brief sample.

        using Excel = Microsoft.Office.Interop.Excel;

        Excel.ApplicationClass _Excel;
        Excel.Workbook WB;
        Excel.Worksheet WS;


        _Excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
        WB = _Excel.Workbooks.Add(System.Reflection.Missing.Value);

        WS = (Excel.Worksheet)WB.Worksheets[1]; 
        WS.Name = "Test";

         try
        {

            int row;
            int col;

            WS.Cells[++row, col] = "COL1";
            WS.Cells[row, ++col] = "COL2";

            WS.get_Range("A1", "A2").Font.Bold = true;
            WS.get_Range("A1", "A2").HorizontalAlignment                     =Excel.XlHAlign.xlHAlignCenter;

            WS.Cells[++row, col] = "Customer";
            WS.Cells[++row, col] = "Expenses" 

            WS.get_Range("A1", "B1").Font.Bold = true;


            WS.Columns.AutoFit();
            WS.Rows.AutoFit();

            WS.Activate();
           _Excel.Visible = true;
        }
        catch (Exception ex)
        {
            WB.Close(false, Type.Missing, Type.Missing);
            _Excel.Quit();
            throw;
        }
        finally
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();



            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WS);


            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WB);

            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_Excel);


        }

Answer 4

SpreadsheetGear for .NET will let you open Excel workbooks, plug in values and save the workbook.

You can see Excel Reporting (i.e., export to Excel) samples here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC