逐步解說:建立 Excel 的第一個檔層級自訂Walkthrough: Create your first document-level customization for Excel

本入門逐步解說將示範如何建立 Microsoft Office Excel 的文件層級自訂。This introductory walkthrough shows you how to create a document-level customization for Microsoft Office Excel. 只有在特定的活頁簿開啟時,才能使用您在這種方案中建立的功能。The features that you create in this kind of solution are available only when a specific workbook is open. 您不能使用文件層級自訂來進行應用程式層級的變更,例如在任何活頁簿開啟時顯示新功能區索引標籤。You cannot use a document-level customization to make application-wide changes, for example, displaying a new Ribbon tab when any workbook is open.

**** 適用對象:-本主題資訊適用於 Excel 的文件層級專案。Applies to: The information in this topic applies to document-level projects for Excel. 如需詳細資訊,請參閱 依 Office 應用程式和專案類型提供的功能For more information, see Features available by Office application and project type.

本逐步解說將說明下列工作:This walkthrough illustrates the following tasks:

  • 建立 Excel 活頁簿專案。Creating an Excel workbook project.

  • 將文字加入 [Visual Studio 設計工具] 中裝載的工作表。Adding text to a worksheet that is hosted in the Visual Studio designer.

  • 撰寫可使用 Excel 物件模型的程式碼,該程式碼會在自訂工作表開啟時將文字加入此工作表。Writing code that uses the object model of Excel to add text to the customized worksheet when it is opened.

  • 建置和執行專案來進行測試。Building and running the project to test it.

  • 清除完成的專案,將不需要的組建檔案和安全性設定從開發電腦上移除。Cleaning up the completed project to remove unnecessary build files and security settings from your development computer.

    注意

    在下列指示的某些 Visual Studio 使用者介面項目中,您的電腦可能會顯示不同的名稱或位置:Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. 您所擁有的 Visual Studio 版本以及使用的設定會決定這些項目。The Visual Studio edition that you have and the settings that you use determine these elements. 如需詳細資訊,請參閱將 IDE 個人化For more information, see Personalize the IDE.

必要條件Prerequisites

您需要下列元件才能完成這個逐步解說:You need the following components to complete this walkthrough:

建立專案Create the project

在 Visual Studio 中建立新的 Excel 活頁簿專案To create a new Excel workbook project in Visual Studio

  1. 啟動 Visual StudioVisual StudioStart Visual StudioVisual Studio.

  2. [檔案] 功能表上,指向 [開新檔案] ,然後按一下 [專案]On the File menu, point to New, and then click Project.

  1. 在範本窗格中,展開 [Visual C#] Deploying Office Solutions 或 [Visual Basic] ,然後展開 [Office/SharePoint] 。In the templates pane, expand Visual C# or Visual Basic, and then expand Office/SharePoint.

  2. 在展開的 [ Office/SharePoint ] 節點下,選取 [ VSTO 增益集 ] 節點。Under the expanded Office/SharePoint node, select the VSTO Add-ins node.

  3. 在專案範本清單中,選擇 Excel VSTO 活頁簿專案。In the list of project templates, choose an Excel VSTO Workbook project.

  4. 在 [ 名稱 ] 方塊中,輸入 FirstWorkbookCustomizationIn the Name box, type FirstWorkbookCustomization.

  5. 按一下 [確定]。Click OK.

  6. 從 [ Visual Studio Tools for Office 專案] Wizard 中選取 [建立新檔],然後按一下 [確定]Select Create a new document from the Visual Studio Tools for Office Project Wizard, and click OK.

  1. 在 [ 建立新專案 ] 對話方塊中,選取 [ Excel VSTO 活頁簿 ] 專案。On the Create a New Project dialog select the Excel VSTO Workbook project.

    注意

    您也可以在 [ 建立新專案 ] 對話方塊頂端的 [搜尋] 文字方塊中輸入 Office 產品的名稱,以搜尋範本。You can also search for templates by typing the name of the Office product in the Search text box at the top of the Create a New Project dialog. 例如,輸入 excel 會顯示 excel 的所有專案範本。For example, typing Excel will show you all of the project templates for Excel. 您也可以在 [專案類型] 下拉式清單方塊中選取 [ office ],以篩選範本清單,只顯示 office 的範本。You can also filter the list of templates to show only the templates for Office by selecting Office in the Project type dropdown box. 如需詳細資訊,請參閱 在 Visual Studio 中建立新專案For more information see Create a new project in Visual Studio.

  2. 按 [下一步] 。Click Next.

  3. 在 [設定您的新專案] 對話方塊的 [名稱] 方塊中輸入 FirstWorkbookCustomization ,然後按一下 [建立]。Type FirstWorkbookCustomization in the Name box on the Configure your new project dialog and click Create.

  4. 從 [ Visual Studio Tools for Office 專案] Wizard 中選取 [建立新檔],然後按一下 [確定]Select Create a new document from the Visual Studio Tools for Office Project Wizard, and click OK.

  • Visual StudioVisual Studio 建立 FirstWorkbookCustomization 專案,並將下列檔案加入至專案。creates the FirstWorkbookCustomization project, and adds the following files to the project.

  • FirstWorkbookCustomization,表示專案中的 Excel 活頁簿。FirstWorkbookCustomization.xlsx - Represents the Excel workbook in the project. 包含所有工作表和圖表。Contains all the worksheets and charts.

  • Sheet1 (Visual Basic 的 .vb 檔案或 Visual c # 的 .cs 檔案 ) -提供活頁簿中第一個工作表的設計介面和程式碼的工作表。Sheet1 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the first worksheet in the workbook. 如需詳細資訊,請參閱 工作表主專案For more information, see Worksheet host item.

  • Sheet2 (Visual Basic 的 .vb 檔案或 Visual c # 的 .cs 檔案 ) -此工作表提供活頁簿中第二個工作表的設計介面和程式碼。Sheet2 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the second worksheet in the workbook.

  • 適用于 Visual c # 的 Visual Basic 或 .cs 檔案的 Sheet3 (.vb 檔 ) -提供活頁簿中第三個工作表的設計介面和程式碼的工作表。Sheet3 (.vb file for Visual Basic or .cs file for Visual C#) - A worksheet that provides the design surface and the code for the third worksheet in the workbook.

  • 適用于 Visual Basic 的 ThisWorkbook (.vb 檔案或 Visual c # 的 .cs 檔案 ) -包含活頁簿層級自訂的設計介面和程式碼。ThisWorkbook (.vb file for Visual Basic or .cs file for Visual C#) - Contains the design surface and the code for workbook-level customizations. 如需詳細資訊,請參閱活頁 簿主專案For more information, see Workbook host item.

    Sheet1 程式碼檔案會在此設計工具中自動開啟。The Sheet1 code file is opened automatically in the designer.

在設計工具中關閉並重新開啟工作表Close and reopen worksheets in the designer

如果您在開發專案時有意或無意地關閉設計工具中的活頁簿或工作表,您都可以將它重新開啟。If you deliberately or accidentally close a workbook or a worksheet in the designer while you are developing your project, you can reopen it.

使用設計工具關閉並重新開啟工作表To close and reopen a worksheet in the designer

  1. 按一下設計工具視窗的 [ 關閉 ] 按鈕 (X) ,關閉活頁簿。Close the workbook by clicking the Close button (X) for the designer window.

  2. 方案總管 中,以滑鼠右鍵按一下 Sheet1 程式碼檔案,然後按一下 [ 視圖設計 工具]。In Solution Explorer, right-click the Sheet1 code file, and click View Designer.

    - 或 -- or -

    方案總管 中,按兩下 Sheet1 程式碼檔案。In Solution Explorer, double-click the Sheet1 code file.

在設計工具中將文字加入工作表Add text to a worksheet in the designer

您可以修改設計工具中開啟的工作表,藉此設計自訂的使用者介面 (UI)。You can design the user interface (UI) of your customization by modifying the worksheet that is open in the designer. 例如,您可以將文字加入儲存格、套用公式,或加入 Excel 控制項。For example, you can add text to cells, apply formulas, or add Excel controls. 如需如何使用設計工具的詳細資訊,請參閱 Visual Studio 環境中的 Office 專案For more information about how to use the designer, see Office projects in the Visual Studio environment.

使用設計工具將文字加入工作表To add text to a worksheet by using the designer

  1. 在設計工具中開啟的工作表中,選取 [儲存格 A1],然後輸入下列文字。In the worksheet that is open in the designer, select cell A1, and then type the following text.

    This text was added by using the designer.This text was added by using the designer.

警告

如果您將這行文字加入至儲存格 A2,此範例中的其他程式碼將會覆寫此文字。If you add this line of text to cell A2, it will be overwritten by other code in this example.

以程式設計方式將文字加入工作表Add text to a worksheet programmatically

接著,將程式碼加入 Sheet1 程式碼檔案。Next, add code to the Sheet1 code file. 新程式碼會使用 Excel 物件模型,將第二行文字加入活頁簿。The new code uses the object model of Excel to add a second line of text to the workbook. 根據預設,Sheet1 程式碼檔案包含下列產生的程式碼:By default, the Sheet1 code file contains the following generated code:

  • Sheet1 類別的部分定義,此定義代表該工作表的程式設計模型,而且會提供 Excel 物件模型的存取。A partial definition of the Sheet1 class, which represents the programming model of the worksheet and provides access to the object model of Excel. 如需詳細資訊,請查看 工作表主專案Word 物件模型總覽For more information, Worksheet host item and Word object model overview. Sheet1 類別的其餘部分則定義於您不應修改的隱藏程式碼檔中。The remainder of the Sheet1 class is defined in a hidden code file that you should not modify.

  • Sheet1_StartupSheet1_Shutdown 事件處理常式。The Sheet1_Startup and Sheet1_Shutdown event handlers. 當 Excel 載入和卸載您的自訂時,會呼叫這些事件處理常式。These event handlers are called when Excel loads and unloads your customization. 請使用這些事件處理常式,在自訂載入時將它初始化,以及在自訂卸載時清除它所用的資源。Use these event handlers to initialize your customization when it is loaded, and to clean up resources used by your customization when it is unloaded. 如需詳細資訊,請參閱 Office 專案中的事件For more information, see Events in Office projects.

使用程式碼將第二行文字加入工作表To add a second line of text to the worksheet by using code

  1. 方案總管 中,以滑鼠右鍵按一下 [ Sheet1],然後按一下 [ 視圖程式碼]。In Solution Explorer, right-click Sheet1, and then click View Code.

    程式碼檔案隨即在 Visual Studio 中開啟。The code file opens in Visual Studio.

  2. 以下列程式碼取代 Sheet1_Startup 事件處理常式。Replace the Sheet1_Startup event handler with the following code. 當 Sheet1 開啟時,此程式碼會將第二行文字加入工作表。When Sheet1 is opened, this code adds a second line of text to the worksheet.

    private void Sheet1_Startup(object sender, System.EventArgs e)
    {
        Microsoft.Office.Tools.Excel.NamedRange nr =
            this.Controls.AddNamedRange(this.Range["A2"], "NamedRange1");
        nr.Value2 = "This text was added by using code";
    }
    
    Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        Dim nr As Microsoft.Office.Tools.Excel.NamedRange = _
            Me.Controls.AddNamedRange(Me.Range("A2"), "NamedRange1")
        nr.Value2 = "This text was added by using code"
    End Sub
    

測試專案Test the project

測試您的活頁簿To test your workbook

  1. F5 建置及執行專案。Press F5 to build and run your project.

    當您建置專案時,程式碼會編譯為與活頁簿相關聯的組件。When you build the project, the code is compiled into an assembly that is associated with the workbook. Visual Studio 會將活頁簿複本和組件置於專案的建置輸出資料夾中,而且會設定開發電腦中的安全性設定以執行自訂。Visual Studio puts a copy of the workbook and the assembly in the build output folder for the project, and it configures the security settings on the development computer to enable the customization to run. 如需詳細資訊,請參閱 建立 Office 方案For more information, see Build Office solutions.

  2. 確認活頁簿中出現下列文字:In the workbook, verify that you see the following text.

    This text was added by using the designer.This text was added by using the designer.

    This text was added by using code.This text was added by using code.

  3. 關閉活頁簿。Close the workbook.

清除專案Clean up the project

當您完成專案開發時,必須移除建置輸出資料夾中的檔案和建置程序建立的安全性設定。When you finish developing a project, you should remove the files in the build output folder and the security settings created by the build process.

清除開發電腦上已完成的專案To clean up the completed project on your development computer

  1. 在 Visual Studio 中,按一下 [建置] 功能表上的 [清除方案] 。In Visual Studio, on the Build menu, click Clean Solution.

後續步驟Next steps

現在您已經建立 Excel 的基本文件層級自訂,可以從下列主題進一步了解如何開發自訂:Now that you have created a basic document-level customization for Excel, you can learn more about how to develop customizations from these topics:

  • 您可以在檔層級自訂中執行的一般程式設計工作: 檔層級自訂程式General programming tasks that you can perform in document-level customizations: Program document-level customizations.

  • 適用于 Excel 的檔層級自訂專屬的程式設計工作: excel 方案Programming tasks that are specific to document-level customizations for Excel: Excel solutions.

  • 使用 Excel 的物件模型: excel 物件模型總覽Using the object model of Excel: Excel object model overview.

  • 自訂 Excel 的 UI,例如,將自訂索引標籤加入功能區,或建立您自己的動作窗格: OFFICE UI 自訂Customizing the UI of Excel, for example, by adding a custom tab to the Ribbon or creating your own actions pane: Office UI customization.

  • 使用 Visual Studio 中的 Office 開發工具提供的擴充 Excel 物件,執行無法使用 Excel 物件模型執行的工作 (例如,在檔上裝載 managed 控制項,以及使用 Windows Forms 資料系結模型將 Excel 控制項系結至資料) : 使用擴充物件自動化 excelUsing extended Excel objects provided by Office development tools in Visual Studio to perform tasks that are not possible by using the Excel object model (for example, hosting managed controls on documents and binding Excel controls to data by using the Windows Forms data binding model): Automate Excel by using extended objects.

  • 建立和調試 Excel 的檔層級自訂: 組建 Office 方案Building and debugging document-level customizations for Excel: Build Office solutions.

  • 部署適用于 Excel 的檔層級自訂: 部署 Office 方案Deploying document-level customizations for Excel: Deploy an Office solution.

另請參閱See also