VSTO Excel 2007: 在插件中包含或嵌入工作簿/工作表

Question

我想在Excel插件中嵌入一个预定义布局的Excel工作表，但是我无法在我的VSTO项目中添加“工作簿”项目项，也无法添加对“Excel工作簿”项目的引用。那么我该怎么做呢？

我的目标是构建一个Excel插件，将新的工作表添加到现有工作簿（这是从SAP下载的）以汇总数据。

Sven

- Sven Sönnichsen

Answer 1

以下是转换为C#的代码：

        // 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!";

- Sven Sönnichsen

Answer 2

创建一个包含工作表的工作簿。如有需要，将工作簿保存为模板。将工作簿嵌入资源中。如何将资源转换为工作簿/工作表取决于文档格式。如果是SpreadSheet XML（XMLSS），那么相对容易。如果是二进制（xls或xlt），则必须操作资源。Excel无法读取流。

使用VSTO Excel Add-In项目和项目资源的示例

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

- AMissico