使用 Excel Services 共用 OLE DB 或 ODBC 連線 (SharePoint Server 2013)Share an OLE DB or ODBC connection using Excel Services (SharePoint Server 2013)

摘要:了解如何使用 Excel 來建立及共用 OLE DB 或 ODBC 連線人員可用來建立資料模型、 報表、 計分卡和儀表板。Summary: Learn how to use Excel to create and share OLE DB or ODBC connections that people can use to create data models, reports, scorecards, and dashboards.

您可以使用 Excel 來建立 OLE DB 或 ODBC 連線,然後與其他人共用這些連線。OLE DB 或 ODBC 連線時十分有用連線至資料來源,例如 Excel 活頁簿、 舊版資料庫或非 Microsoft 資料庫。當您可以將資料連線上傳至 SharePoint Server 2013 中的 Excel Services 信任的資料連線庫時,資料連線是可供您與其他用來建立資料模型、 報表、 計分卡和儀表板。根據使用的特定資料來源、 人員可以輕鬆地重新整理 Excel Services 活頁簿中的資料使顯示最新資訊。You can use Excel to create OLE DB or ODBC connections and then share those connections with others. An OLE DB or ODBC connection is useful for connecting to data sources, such as Excel workbooks, legacy databases, or non-Microsoft databases. When you can upload a data connection to an Excel Services trusted data connection library in SharePoint Server 2013, the data connection is available for you and others to use to create data models, reports, scorecards, and dashboards. Depending on the particular data source that is used, people can easily refresh data in Excel Services workbooks so that the most current information is displayed.

開始之前Before you begin

在開始進行此工作之前,請先檢閱下列先決條件的相關資訊:Before you begin this task, review the following information about prerequisites:

  • 您必須使用 Excel 2016 和 SharePoint Server 2013。You must be using Excel 2016 and SharePoint Server 2013.

  • Excel Services 必須設定成包含信任的資料連線庫與受信任的文件庫。理想狀況下,您必須商務智慧中心網站設定您可以使用資料連線及活頁簿。如需詳細資訊,請參閱 < Configure SharePoint Server 2013 的商務智慧中心Excel Services must be configured to include a trusted data connections library and a trusted documents library. Ideally, you'll have a Business Intelligence Center site configured that you can use for your data connections and workbooks. For more information, see Configure a Business Intelligence Center in SharePoint Server 2013.

  • 您必須至少參與權限指派在 SharePoint Server 2013。You must have at least Contribute permissions assigned in SharePoint Server 2013.

  • 您將會需要 SharePoint 管理員提供您有關如何為組織使用的資料庫設定資料驗證的資訊。這會影響您連接至不同資料來源的方式。You will need information from a SharePoint administrator about how data authentication is configured for the databases your organization uses. This can affect how you connect to different data sources.

  • 您將會需要您想要使用之資料來源的相關資訊。尤其是,您必須知道資料來源名稱、使用者名稱和密碼,才能連接至資料來源。You will need information about the data source that you want to use. In particular, you must know the data source name, user name, and password to connect to the data source.

  • 如果您打算要發佈活頁簿包含資料模型至 SharePoint Server 2013、 Excel Services 必須設定為支援資料模型。如需詳細資訊,請參閱 < Configure Excel Services in SharePoint Server 2013 PreviewIf you plan to publish workbooks that contain data models to SharePoint Server 2013, Excel Services must be configured to support data models. For more information, see Configure Excel Services in SharePoint Server 2013 Preview.

步驟 1:使用 Excel 來建立 OLE DB 或 ODBC 資料連線Step 1: Use Excel to create OLE DB or ODBC data connections

您可以使用 Excel 來建立 OLE DB 或 ODBC 連線。這可讓您連線至許多不同的資料來源。包括 Microsoft SQL Server、 Microsoft Access 及 Oracle 資料庫雖然有許多其他人。您可以使用數種方法之一來建立 OLE DB 或 ODBC 資料連線:You can use Excel to create an OLE DB or ODBC connection. This enables you to connect to lots of different data sources. Examples include Microsoft SQL Server, Microsoft Access, and Oracle databases, although there are many others. You can create an OLE DB or ODBC data connection by using one of several methods:

  • 您可以在 Excel 中使用資料連線精靈來建立連線。(這是要使用的建議的方法)。You can create a connection by using the Data Connection Wizard in Excel. (This is the recommended method to use.)

  • 您可以在 Excel 中使用 Microsoft 查詢精靈來建立連線。You can create a connection by using a Microsoft Query Wizard in Excel.

使用下列程序來建立 OLE DB 或 ODBC 資料連線Use the following procedures to create OLE DB or ODBC data connections

在 Excel 中使用資料連線精靈來建立連線To create a connection by using the Data Connection Wizard in Excel

  1. 在 Excel資料] 索引標籤的 [取得外部資料] 群組中,按一下 [從其他來源],然後選取從資料連線精靈]In Excel, on the Data tab, in the Get External Data group, click From Other Sources, and then select From Data Connection Wizard.

    [資料連線精靈] 隨即開啟。The Data Connection Wizard opens.

  2. 您想要連線至何種資料來源?清單中,選擇資料來源,然後再按 [下一步In the What kind of data source do you want to connect to? list, choose a data source, and then click Next.

  3. 根據您選取的資料來源種類,指定該資料來源的必要資訊。Depending on the kind of data source that you selected, specify the necessary information for that data source.

    提示

    您指定的資訊會根據資料來源而異。例如,摘要資料的資料來源需要連結或位置資料摘要與登入認證,而 Excel 活頁簿的資料來源需要您找出儲存活頁簿。連絡資料庫管理員以取得您想要建立的連線的特定詳細資料。The information that you specify varies according to the data source. For example, a Data Feed data source requires a link or location to a data feed and logon credentials, whereas an Excel workbook data source requires you to locate where the workbook is stored. Contact a database administrator for specific details regarding the connection that you want to create.

  4. 在 [選取資料庫及資料表] 頁面中選取包含您想要將資料的資料庫] 清單中,選取您想要使用的資料庫。請勿按下一個尚未。On the Select Database and Table page, in the Select the database that contains the data that you want list, select the database that you want to use. Do not click Next yet.

  5. 在 [選取資料庫及資料表] 頁面上選取 (或多個資料表) 您要使用,然後按 [下一步On the Select Database and Table page, select the table (or tables) that you want to use, and then click Next.

  6. 在 [儲存資料連線檔案和完成] 頁面上執行下列步驟:On the Save Data Connection File and Finish page, take the following steps:

  7. 在 [檔案名稱] 方塊中,保留或變更預設檔案名稱。In the File Name box, keep or change the default file name.

  8. 在 [描述] 方塊中輸入資料連線的簡短描述。In the Description box, type a brief description for the data connection.

  9. 在 [易記名稱] 方塊中,保留預設名稱或輸入檔案的新名稱。In the Friendly Name box, keep the default name or type a new name for file.

  10. 在 [搜尋關鍵字] 方塊中輸入一些單字或片語可協助使用者尋找資料連線發佈至 SharePoint Server 2013 時。In the Search Keywords box, type some words or phrases that will help users find the data connection when it is published to SharePoint Server 2013.

  11. Excel Services] 旁按一下 [驗證設定...]。Next to Excel Services, click Authentication Settings….

  12. 選取 [] 及 [確定]Select None, and then click OK.

  13. 按一下 [完成] 以關閉 [儲存資料連線檔案和完成] 頁面。Click Finish to close the Save Data Connection File and Finish page.

  14. 在 [匯入資料] 頁面上按一下 [只建立連線,並再按一下 [確定]On the Import Data page, click Only Create Connection, and then click OK.

  15. 重複步驟 1-7,直到建立完您想要的所有資料連線。Repeat steps 1-7 until you have created all the data connections that you want.

如果無法建立您想使用資料連線精靈] 在 Excel 中,您可以嘗試使用的 Microsoft 查詢精靈來建立連線的連線。這是適用於連線至舊的資料庫。不過,您所建立的連線可能不支援的 Excel services。請連絡 SharePoint 管理員若要確認您建立的連線會支援,讓使用者可以重新整理 Excel Services 檔案中的資料。下列程序說明如何使用 Microsoft 查詢精靈所建立的連線。If cannot create the connection that you want by using the Data Connection Wizard in Excel, you can try to create the connection by using a Microsoft Query wizard. This is useful for connecting to older databases. However, the connection that you create might not be supported in Excel Services. Contact a SharePoint administrator to verify that the connection that you create is supported so that people can refresh data in Excel Services files. The following procedure describes how to create a connection by using a Microsoft Query wizard.

在 Excel 中使用的 Microsoft 查詢精靈來建立連線To create a connection by using a Microsoft Query wizard in Excel

  1. 在 Excel 中,[資料] 索引標籤的 [取得外部資料]群組中,按一下 [從其他來源],然後選取從 Microsoft 查詢In Excel, on the Data tab, in the Get External Data group, click From Other Sources, and then select From Microsoft Query.

    [選擇資料來源] 對話方塊隨即顯示。The Choose Data Source dialog box appears.

  2. 使用 [資料庫查詢OLAP Cube ] 索引標籤來指定您想要使用的資料來源。Use the Databases, Queries, or OLAP Cubes tab to specify the data source that you want to use.

    提示

    您指定的資訊會根據資料來源而異。例如,如果您選取的 Access 資料庫或 Excel 檔案,系統將提示您瀏覽至資料庫或在您的電腦上的檔案。或者,如果您選擇使用未列出的資料來源,提示您建立的資料來源,並指定的資料來源、 位置及認證以連線至該類型。連絡資料庫管理員以取得您想要建立的連線的特定詳細資料。The information that you specify varies according to the data source. For example, if you select an Access database or an Excel file, you'll be prompted to navigate to the database or file on your computer. Or, if you choose to use a data source that is not listed, you'll be prompted to create a data source and then specify the kind of data source, location, and credentials to connect to it. Contact a database administrator for specific details regarding the connection that you want to create.

  3. 在 [匯入資料] 頁面上按一下 [只建立連線,並再按一下 [確定]On the Import Data page, click Only Create Connection, and then click OK.

  4. 重複步驟 1-3,直到建立完您想要的所有資料連線。Repeat steps 1-3 until you have created all the data connections that you want.

根據預設,資料連線會儲存在文件庫中的 [我的資料來源] 資料夾中您的電腦上。By default, the data connection is saved in the My Data Sources folder in the Documents library on your computer.

步驟 2:將資料連線上傳至 SharePoint ServerStep 2: Upload data connections to SharePoint Server

建立資料連線之後,下個步驟就是將它上傳到資料連線庫。建議您使用商務智慧中心網站來儲存及管理商務智慧內容,例如資料連線。After data connections are created, the next step is to upload it to a data connection library. We recommend that you use a Business Intelligence Center site to store and manage business intelligence content, such as data connections.

注意

如果您不使用商務智慧中心網站,請確定您使用指定為信任位置的 Excel services 資料連線庫。如需詳細資訊,請參閱 < Manage Excel Services 信任的資料連線庫 (SharePoint Server 2013)If you are not using a Business Intelligence Center site, make sure that you use a data connection library that is specified as a trusted location in Excel Services. For more information, see Manage Excel Services trusted data connection libraries (SharePoint Server 2013).

資料連線上傳至 SharePoint ServerTo upload a data connection to SharePoint Server

  1. 開啟網頁瀏覽器,並瀏覽至包含您要使用之資料連線庫的 SharePoint 網站。Open a web browser and navigate to the SharePoint site that contains the data connection library that you want to use.

    如果您使用商務智慧中心,網址 (URL) 的格式類似於http://servername/sites/bicenter。If you are using a Business Intelligence Center, the website address (URL) resembles http://servername/sites/bicenter.

  2. 按一下 [網站內容] 以檢視清單和文件庫可供該網站。Click Site Contents to view the lists and libraries that are available for that site.

  3. 按一下 [資料連線] 以開啟該文件庫。Click Data Connections to open that library.

  4. 按一下 [新增項目以開啟 [新增文件] 對話方塊。Click New Item to open the Add a document dialog box.

  5. 按一下 [瀏覽]以開啟 [選擇要上傳檔案] 對話方塊。Click Browse to open the Choose File to Upload dialog box.

  6. 假設資料連線會儲存在其預設位置中,按一下 [文件庫、 按一下 [文件並連按兩下 [我的資料來源Assuming the data connection is saved in its default location, click Libraries, click Documents, and then double-click My Data Sources.

  7. 選取您要上傳的 ODC 檔案,然後按一下 [開啟Select the ODC file that you want to upload, and then click Open.

  8. 在 [新增文件] 對話方塊中,按一下 [確定]In the Add a document dialog box, click OK.

    資料連線] 表單隨即開啟。A Data Connections form opens.

  9. 在 [資料連線] 表單中,指定下列設定:In the Data Connections form, specify the following settings:

    • 在 [內容類型] 區段中,確認已選取 [ Office 資料連線檔案In the Content Type section, confirm that Office Data Connection File is selected.

    • 在 [名稱] 方塊中,保留或變更 ODC 檔案的檔案名稱。In the Name box, keep or change the file name of the ODC file.

    • 在 [標題] 方塊中,保留或變更 ODC 檔案的標題。In the Title box, keep or change the title of the ODC file.

    • 在 [描述] 方塊中輸入資料連線的說明。In the Description box, type a description of the data connection.

    • 在 [關鍵字] 方塊中輸入一個或多個字或詞。此資訊使用搜尋查詢探索資料連線。In the Keywords box, type one or more words or phrases. This information is used by search queries to discover the data connection.

      然後按一下 [儲存]。Then click Save.

      此資料連線隨即新增至文件庫。請針對您要共用的每個資料連線重複此程序。The data connection is added to the library. Repeat this procedure for each data connection that you want to share.

另請參閱See also

概念Concepts

使用 Excel 與 Excel Services (SharePoint Server 2013) 共用資料連線Share data connections by using Excel and Excel Services (SharePoint Server 2013)

支援的 Excel Services (SharePoint Server 2013) 資料來源Data sources supported in Excel Services (SharePoint Server 2013)