VSTO Excel 2007: Include or embed a Workbook/Worksheet in an Add-in

Question

I want to include/embed an Excel worksheet with a predefined layout in an Excel Add-in, but I cannot add a project item "Workbook" to my VSTO project nor can I add a reference to a "Excel Workbook" project. So how can I do this?

My aim is to build an Excel Add-in which adds a new worksheet to an existing workbook (which is a download from SAP) to aggregate data.

Sven

Answer 1

Create a workbook that contains the worksheet. Save workbook as a template if needed. Embed workbook as a resource. How you convert resource into workbook/worksheet will depend on the document format. If SpreadSheet XML (XMLSS), it is fairly easy. If binary (xls or xlt) then you will have to manipulate resource. Excel cannot read a stream.

Sample Using VSTO Excel Add-In Project and Project Resource

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        ' Start of VSTO generated code

        Me.Application = CType(Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(GetType(Excel.Application), Me.Application), Excel.Application)

        ' End of VSTO generated code

        'setup a workbook and worksheet for sample code to work
        Dim oWB As Excel.Workbook = Me.Application.Workbooks.Add()
        Dim oWS As Excel.Worksheet = CType(oWB.Worksheets.Add, Excel.Worksheet)

        'create temporary template
        Dim sPath As String = My.Computer.FileSystem.GetTempFileName
        My.Computer.FileSystem.WriteAllBytes(sPath, My.Resources.Book1, False)

        'open with excel
        Dim oTemplate As Excel.Workbook = Me.Application.Workbooks.Add(sPath)

        'specify worksheet from a different workbook
        '   copies the template worksheet into destination workbook
        oTemplate.Worksheets.Copy(oWS)

        'no longer need template
        oTemplate.Close()

        'delete the temporary file
        My.Computer.FileSystem.DeleteFile(sPath)

        'get our worksheet
        Dim oReportWS As Excel.Worksheet = CType(oWB.Worksheets.Item("Template"), Excel.Worksheet)

        'write our data
        CType(oReportWS.Cells(1, 1), Excel.Range).Value2 = "Here I am!"

    End Sub

End Class

Answer 2

Here is the C# converted code:

        // setup a workbook and worksheet for sample code to work
        var oWB = Application.Workbooks.Add(missing);
        var oWS = oWB.Worksheets.Add(missing, missing, 1, missing) as Excel.Worksheet;

        // create temporary template
        var sPath = FileSystem.GetTempFileName();
        FileSystem.WriteAllBytes(sPath, Resource.Template, false);

        // open with excel
        var oTemplate = Application.Workbooks.Add(sPath);

        // specify worksheet from a different workbook
        //   copies the first worksheet into destination workbook
        (oTemplate.Worksheets[1] as Excel.Worksheet).Copy(missing, oWS);

        // no longer need template
        oTemplate.Close(false, missing, missing);

        //delete the temporary file
        FileSystem.DeleteFile(sPath);

        var oReportWS = oWB.Worksheets["Template"] as Excel.Worksheet;

        // write our data
        ((oReportWS.Cells[1, 1]) as Excel.Range).Value2 = "Here I am!";