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

摘要:了解您可以使用 Excel 與 Excel Services 中的資料來源的不同種類。Summary: Learn about different kinds of data sources that you can use in Excel and Excel Services.

您可以將資料匯入 Excel 使用大量資料來源與連線。許多,但並非所有資料的 Excel Services 中支援您可以在 Excel 中使用的連線。此外,如果您的組織要使用 Office Web Apps Server,您可以在 Excel 中使用某些資料來源 Excel Web App 中不支援。You can bring data into Excel using lots of data sources and connections. Many, but not all, of the data connections that you can use in Excel are supported in Excel Services. In addition, if your organization is using Office Web Apps Server, some data sources that you can use in Excel are not supported in Excel Web App.

在高層級中下, 表摘要說明 (如 Office Web Apps Server 的一部分) (做為 SharePoint Server 2013 的一部分) 的 Excel Services 和 Excel Web App 中支援的 Excel 資料來源。At a high level, the following table summarizes the Excel data sources that are supported in Excel Services (as part of SharePoint Server 2013) and Excel Web App (as part of Office Web Apps Server).

表: Excel Services 和 Excel Web App 中支援的 Excel 資料來源摘要Table: Summary of Excel data sources that are supported in Excel Services and Excel Web App

Excel 資料來源Excel data source 支援的 Excel services 吗?Supported in Excel Services? 在 Excel Web App 是否支援吗?Supported in Excel Web App?
SQL Server 資料表SQL Server tables
Yes
是,如果環境設定成使用 Secure Store Service 或自動的服務帳戶。Yes, if the environment is configured to use Secure Store Service or an unattended service account.
SQL Server Analysis Services cubeSQL Server Analysis Services cubes
Yes
是,如果環境設定成使用 Secure Store Service 或自動的服務帳戶。Yes, if the environment is configured to use Secure Store Service or an unattended service account.
OLE DB 或 ODBC 資料來源OLE DB or ODBC data source
是,只要連接字串包含連線的使用者名稱和密碼。Yes, as long as the connection string contains a user name and password for the connection.
是,只要連接字串包含連線的使用者名稱和密碼。Yes, as long as the connection string contains a user name and password for the connection.
使用 Excel 建立的資料模型Data model that was created by using Excel
是,假如 Excel Services 設定為使用 SQL Server Analysis Services 資料模型的執行個體。Yes, provided Excel Services is configured to use an instance of SQL Server Analysis Services for data models.
No
Azure Marketplace 資料Azure Marketplace data
No
No
OData 資料OData data
No
No
XML 資料XML data
No
No
存取資料Access data
No
No
文字檔中的資料Data from a text file
No
No

下列各節包含有關如何使用 Excel 與 Excel Services 中的資料的詳細的資訊。The following sections contain more detailed information about how to work with data in Excel and Excel Services.

本文中In this article

在 Excel 中使用外部資料Working with external data in Excel

在 Excel 2016 您可以連線至大量的資料來源。這些包括下列:In Excel 2016, you can connect to lots of data sources. These include the following:

  • SQL Server 資料表SQL Server tables

  • SQL Server Analysis Services cubeSQL Server Analysis Services cubes

  • Azure Marketplace 資料Azure Marketplace data

  • OData 資料OData data

  • XML 資料XML data

  • 存取資料Access data

  • 文字檔資料Text file data

若要將資料匯入 Excel,您可以使用現有的連線,或您可以建立您自己的連線。現有的連線會儲存在電腦上或集中的位置,例如 SharePoint 網站。To bring data into Excel, you can use existing connections, or you can create your own connections. Existing connections can be stored on your computer or in a central location, such as a SharePoint site.

理想狀況下,您將在網站中,您可以使用 Excel 中使用資料的商務智慧中心網站,例如可以存取現有的資料連線的一組。這是如果您打算將活頁簿發佈至商務智慧中心網站中的文件庫尤其實用。一般而言,SharePoint 管理員會將這類位置設定為 Excel services 的信任位置。這可讓您與其他人以重新整理活頁簿中的資料檢視最新的資訊。Ideally, you'll have access to a set of existing data connections in a site, such as a Business Intelligence Center site, that you can use to work with data in Excel. This is especially helpful if you plan to publish a workbook to a documents library in a Business Intelligence Center site. Typically, a SharePoint administrator configures such locations as trusted locations in Excel Services. This makes it possible for you and others to refresh data in your workbooks to view the most current information.

若要在 Excel 中使用現有的資料連線To use an existing data connection in Excel

  1. 在 Excel 中,在 [資料] 索引標籤上選擇 [現有連線]。In Excel, on the Data tab, choose Existing Connections.

  2. 選擇 [以開啟 [選取資料來源] 對話方塊的 [瀏覽更多]。Choose Browse for More to open the Select a Data Source dialog box.

  3. 指定您想要使用、 選取資料來源,然後選擇 [開啟資料來源的位置。Specify the location of the data source that you want to use, select the data source, and then choose Open.

  4. 在 [匯入資料] 頁面上選擇 [您要檢視資料,然後選擇[確定]On the Import Data page, choose how you want to view the data, and then choose OK.

    可用的選項取決於您所使用的特定資料來源。例如,您可能會選擇建立運算列表、 樞紐分析圖、 樞紐分析表報表或 Power View 工作表。The options that are available depend on the particular data source that you are using. For example, you might choose to create a data table, a PivotChart report, a PivotTable report, or a Power View sheet.

如果您沒有現有的連線或您想要建立新的連線,您可以輕鬆地這麼做在 Excel 中。您通常必須知道的名稱儲存資料的位置 (例如伺服器或網站) 及 what 您應該用來連線至資料的驗證方法。例如,若要建立儲存在 SQL Server 表格的連線,您必須知道伺服器、 資料庫以及您要使用的資料表名稱與何種認證用來連線至資料。If you do not have an existing connection or you want to create a new connection, you can easily do this in Excel. You'll typically have to know the name of the location (such as a server or a website) where the data is stored and what authentication method that you should use to connect to the data. For example, to create a connection to a table that is stored in SQL Server, you must know the name of the server, the database, and table that you want to use, and what credentials are used to connect to the data.

建立並使用 Excel 中新的資料連線To create and use a new data connection in Excel

  1. 在 Excel 中,[資料] 索引標籤的 [取得外部資料]群組中,選擇下列選項之一:In Excel, on the Data tab, in the Get External Data group, choose one of the following options:

    • 選擇 [從 Access使用 Access 資料庫中儲存的資料。Choose From Access to use data that is stored in an Access database.

    • 選擇 [從 Web]以使用來自內部或外部網站的資料。Choose From Web to use data from an internal or external website.

    • 選擇 [從文字檔],以使用儲存在文字檔中的資料。Choose From Text to use data that is stored in a text file.

    • 選擇 [從其他來源] 使用 SQL Server、 SQL Server Analysis Services、 Azure Marketplace、 OData、 XML 檔中提供的資料或透過自訂提供者的資料。Choose From Other Sources to use data that is available in SQL Server, SQL Server Analysis Services, Azure Marketplace, OData, an XML file, or data that is available through a custom provider.

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

  2. 指定的 [資料連線精靈] 中,每個步驟所需的資訊] 和 [完成]Specify the information that is required for each step of the Data Connection Wizard, and then click Finish.

  3. 在 [匯入資料] 頁面上選擇 [您要檢視資料,然後選擇[確定]On the Import Data page, choose how you want to view the data, and then choose OK.

    可用的選項取決於您所使用的特定資料來源。例如,您可能會選擇建立運算列表、 樞紐分析圖、 樞紐分析表報表、 Power View 工作表或資料連線。The options that are available depend on the particular data source that you are using. For example, you might choose to create a data table, a PivotChart report, a PivotTable report, a Power View sheet, or just the data connection.

如需如何建立資料連線的詳細資訊,請參閱使用 Excel 與 Excel Services (SharePoint Server 2013) 的共用資料連線For more detailed information about how to create data connections, see Share data connections by using Excel and Excel Services (SharePoint Server 2013).

建立使用外部資料與檢視,例如樞紐分析圖、 樞紐分析表報表或 Power View 檢視 Excel 活頁簿之後您可與他人共用該活頁簿使用例如 SharePoint 網站的網站。根據您的環境可能不支援使用活頁簿中的外部資料連線。是否支援外部資料連線會決定是否可使用活頁簿的資料重新整理與活頁簿是否可檢視瀏覽器視窗中。After you have created an Excel workbook that uses external data and views, such as PivotChart reports, PivotTable reports, or Power View views, you can share that workbook with others by using a site such as a SharePoint site. Depending on your environment, the external data connections that are used in your workbook might not be supported. Whether an external data connection is supported determines whether data refresh is available for the workbook and whether the workbook is viewable in a browser window.

SharePoint Server 的 Excel Services 中支援的外部資料連線External data connections that are supported in Excel Services in SharePoint Server

最多只但並非所有,您可以在 Excel 中使用的連線支援的 Excel services 的資料。這些包括下列資料來源的連線:Most, but not all, of the data connections that you can use in Excel are supported in Excel Services. These include connections to the following data sources:

  • SQL Server 資料表SQL Server tables

  • SQL Server Analysis Services cubeSQL Server Analysis Services cubes

  • OLE DB 和 ODBC 資料來源OLE DB and ODBC data sources

當支援的資料連線時,就表示的人員可以重新整理資料的 Excel Services 使用該資料連線,只要已正確設定 Excel Services。When a data connection is supported, it means that people can refresh data in Excel Services that use that data connection, as long as Excel Services is configured correctly.

資料來源,您可以連線至 Excel 中不支援的 Excel Services 包括 Access 資料庫、 網站內容、 XML 檔、 Azure Marketplace 資料及文字檔。如果您打算使用這種將發佈至 SharePoint Server 2013 的活頁簿中的資料來源,請考慮將 Excel 資料匯入和使用為原生資料的資料。如需詳細資訊,請參閱使用 Excel Services 中的原生資料Data sources that you can connect to in Excel that are not supported in Excel Services include the Access databases, website content, XML files, Azure Marketplace data, and text files. If you plan to use these kinds of data sources in workbooks that you'll publish to SharePoint Server 2013, consider importing data into Excel and using the data as native data. For more information, see Working with native data in Excel Services.

包含 Office Web Apps Server 之 SharePoint 環境中支援的外部資料連線External data connections that are supported in a SharePoint environment that includes Office Web Apps Server

Office Web Apps Server 是線上隨附的 Office Word、 Excel、 PowerPoint 和 OneNote 的應用程式。如果您的組織使用 SharePoint Server 2013 與 Office Web Apps Server,您的組織會使用 Excel Services 或 Excel Web App (Office Web Apps Server 的一部分) 瀏覽器視窗中顯示活頁簿。此決策會影響活頁簿的瀏覽器視窗中轉譯的支援哪些資料來源。Office Web Apps Server is the online companion to Office Word, Excel, PowerPoint, and OneNote applications. If your organization is using SharePoint Server 2013 together with Office Web Apps Server, then your organization is using either Excel Services or Excel Web App (part of Office Web Apps Server) to display workbooks in a browser window. This decision affects which data sources are supported for workbooks rendered in a browser window.

Excel Web App 支援某些,但不是所有安全的外部資料連線類型。Excel Web App 中不支援的 Microsoft Access 資料庫、 網站內容、 XML 檔、 Azure Marketplace 資料及文字檔的資料連線。如果您打算使用這種您將會共用使用 Excel Web App 的活頁簿中的資料來源,請考慮將 Excel 資料匯入和使用為原生資料的資料。Excel Web App supports some, but not all, kinds of secure external data connections. Data connections to Microsoft Access databases, website content, XML files, Azure Marketplace data, and text files are not supported in Excel Web App. If you plan to use these kinds of data sources in workbooks that you'll share using Excel Web App, consider importing data into Excel and using the data as native data.

在 Excel Services 中使用資料模型Working with data models in Excel Services

資料模型是包含多個資料表的資料集。資料模型可用一起將資料導從不同的資料庫以建立可做為資料來源檢視,例如樞紐分析圖、 樞紐分析表報表及 Power View 檢視單一資料庫。可以使用外部資料] 或 [原生資料建立資料模型。如需資料模型的詳細資訊,請參閱PowerPivot: 強大的資料分析與 Excel 中的資料模型A data model is a dataset that consists of multiple tables. Data models are useful for bringing together data from different databases to create a single database that can serve as a data source for views, such as PivotChart reports, PivotTable reports, and Power View views. Data models can be created by using external data or native data. For more information about data models, see PowerPivot: Powerful data analysis and data modeling in Excel.

若要檢視或使用的活頁簿包含資料模型中的 Excel Services、 Excel Services 必須設定為支援資料模型。如需詳細資訊,管理 Excel Services 資料模型設定 (SharePoint Server 2013)。目前 Office Web Apps Server 不支援的資料模型。To view or use a workbook that contains a data model in Excel Services, Excel Services must be configured to support data models. For more information, Manage Excel Services data model settings (SharePoint Server 2013). Currently, Office Web Apps Server does not support data models.

在 Excel Services 中使用原生資料Working with native data in Excel Services

原生資料是匯入 Excel 並不會保持連線至外部資料庫的資料。原生資料也稱為工作表資料或工作表的資料,且是靜態資料或手動更新資料。使用 Excel 活頁簿中的原生資料具有某些優點:Native data is data that is imported into Excel and does not keep connections to external databases. Native data is also known as worksheet data or sheet data, and it is either static data or data that is updated manually. Working with native data in Excel workbooks offers certain advantages:

  • 您可以發佈使用 Excel Services 或 Excel Web App 中不支援的來源資料的活頁簿。You can publish workbooks that use data from sources that are not supported in Excel Services or Excel Web App.

  • 您與其他人可以檢視和互動在瀏覽器視窗中使用原生資料的活頁簿是否活頁簿由 Office Web Apps Server 或 SharePoint Server 2013 轉譯。請注意,資料模型不支援的 Office Web Apps Server,但樞紐分析圖和樞紐分析表報表使用原生資料的支援。You and others can view and interact with workbooks that use native data in a browser window, whether the workbook is rendered by either Office Web Apps Server or SharePoint Server 2013. Note that data models are not supported in Office Web Apps Server, but PivotChart reports and PivotTable reports that use native data are supported.

  • 如果活頁簿不取用超過 10 MB 的磁碟空間,您可以共用或檢視 Office 365 上的活頁簿。這讓可能共用資訊雲端。 」If a workbook does not consume more than 10 MB of disk space, you can share or view the workbook on Office 365. This makes it possible to share information "in the cloud."

另請參閱See also

概念Concepts

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

商務智慧功能的 Excel Service (SharePoint Server 2013)Business intelligence capabilities in Excel Service (SharePoint Server 2013)

其他資源Other Resources

SharePoint Server 中的 Excel Services 資料驗證Data authentication for Excel Services in SharePoint Server