使用 Office Online Server (OOS) 設定報表伺服器來裝載 Excel 活頁簿Configure your report server to host Excel workbooks using Office Online Server (OOS)

除了在入口網站中檢視 Power BI 報表之外,商務使用者現在也可以在 Power BI 報表伺服器中執行與 Excel 活頁簿相同的作業,讓他們有一個位置可以發佈和檢視其自助 Microsoft BI 內容。In addition to viewing Power BI reports in the web portal, business users can now do the same with Excel workbooks in Power BI Report Server, giving them a single location to publish and view their self-service Microsoft BI content.

注意

這是 2017 年 8 月 Preview 版本所含的預覽功能。This is a preview feature included in the August 2017 preview release. 如需詳細資訊,請參閱 Power BI 報表伺服器的新功能For more information, see What's new in Power BI Report Server.

要從報表伺服器入口網站檢視的 Excel 報表。

這是利用 Office Online Server (OOS) 所完成。This is accomplished by making use of Office Online Server (OOS).

準備伺服器以執行 Office Online ServerPrepare server to run Office Online Server

在將執行 Office Online Server 的伺服器上,執行這些程序。Perform these procedures on the server that will run Office Online Server. 此伺服器必須是 Windows Server 2012 R2 或 Windows Server 2016。This server must be Windows Server 2012 R2 or Windows Server 2016. Windows Server 2016 需要 Office Online Server 2017 年 4 月或更新版本。Windows Server 2016 requires Office Online Server April 2017 or later.

安裝 Office Online Server 的必要軟體Install prerequisite software for Office Online Server

  1. 以系統管理員身分開啟 Windows PowerShell 提示,並執行此命令來安裝必要角色和服務。Open the Windows PowerShell prompt as an administrator and run this command to install the required roles and services.

    Windows Server 2012 R2:Windows Server 2012 R2:

    Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices,NET-Framework-Features,NET-Framework-Core,NET-HTTP-Activation,NET-Non-HTTP-Activ,NET-WCF-HTTP-Activation45,Windows-Identity-Foundation,Server-Media-Foundation
    

    Windows Server 2016:Windows Server 2016:

    Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,NET-Framework-Features,NET-Framework-45-Features,NET-Framework-Core,NET-Framework-45-Core,NET-HTTP-Activation,NET-Non-HTTP-Activ,NET-WCF-HTTP-Activation45,Windows-Identity-Foundation,Server-Media-Foundation
    

    系統提示時,請重新啟動伺服器。If prompted, restart the server.

  2. 安裝下列軟體:Install the following software:

安裝 Office Online ServerInstall Office Online Server

如果您打算使用任何利用外部資料存取的 Excel Online 功能 (例如 Power Pivot),請注意 Office Online Server 必須位在與其使用者相同的 Active Directory 樹系中,以及您打算使用 Window 型驗證存取的任何外部資料來源。If you plan to use any Excel Online features that utilize external data access (such as Power Pivot), note that Office Online Server must reside in the same Active Directory forest as its users as well as any external data sources that you plan to access using Windows-based authentication.

  1. Volume Licensing Service Center (VLSC) 下載 Office Online Server。Download Office Online Server from the Volume Licensing Service Center (VLSC). 下載位於 VLSC 入口網站的這些 Office 產品下方。The download is located under those Office products on the VLSC portal. 基於開發目的,您可以從 MSDN 訂閱者下載中下載 OOS。For development purposes, you can download OOS from MSDN subscriber downloads.
  2. 執行 Setup.exe。Run Setup.exe.
  3. 在 [閱讀 Microsoft 軟體授權條款] 頁面上,選取 [我接受這份合約],然後選取 [繼續]。On the Read the Microsoft Software License Terms page, select I accept the terms of this agreement and select Continue.
  4. 在 [選擇檔案位置] 頁面上,選取您要安裝 Office Online Server 檔案的資料夾 (例如,C:\Program Files\Microsoft Office Web Apps),然後選取 [立即安裝]。On the Choose a file location page, select the folder where you want the Office Online Server files to be installed (for example,C:\Program Files\Microsoft Office Web Apps) and select Install Now. 如果您指定的資料夾不存在,則安裝程式會建立它。If the folder you specified doesn’t exist, Setup creates it for you.

    建議您在系統磁碟機上安裝 Office Online Server。We recommend that you install Office Online Server on the system drive.

  5. 安裝程式完成安裝 Office Online Server 時,請選取 [關閉]。When Setup finishes installing Office Online Server, select Close.

安裝 Office Web Apps Server 的語言套件 (選擇性)Install language packs for Office Web Apps Server (optional)

Office Online Server Language Pack 可讓使用者以多國語言檢視網頁 Office 檔案。Office Online Server Language Packs let users view web-based Office files in multiple languages.

若要安裝語言套件,請遵循下列步驟。To install the language packs, follow these steps.

  1. Microsoft 下載中心下載 Office Online Server Language Pack。Download the Office Online Server Language Packs from the Microsoft Download Center.
  2. 執行 wacserverlanguagepack.exeRun wacserverlanguagepack.exe.
  3. 在 [Office Online Server Language Pack 精靈] 的 [閱讀 Microsoft 軟體授權條款] 頁面上,選取 [我接受這份合約],然後選取 [繼續]。In the Office Online Server Language Pack Wizard, on the Read the Microsoft Software License Terms page, select I accept the terms of this agreement and select Continue.
  4. 安裝程式完成安裝 Office Online Server 時,請選取 [關閉]。When Setup finishes installing Office Online Server, select Close.

部署 Office Online ServerDeploy Office Online Server

建立 Office Online Server 伺服器陣列 (HTTPS)Create the Office Online Server farm (HTTPS)

使用 New-OfficeWebAppsFarm 命令,來建立包含單一伺服器的新 Office Online Server 伺服器陣列,如下列範例所示。Use the New-OfficeWebAppsFarm command to create a new Office Online Server farm that consists of a single server, as shown in the following example.

New-OfficeWebAppsFarm -InternalUrl "https://server.contoso.com" -ExternalUrl "https://wacweb01.contoso.com" -CertificateName "OfficeWebApps Certificate"

參數Parameters

  • –InternalURL 是執行 Office Online Server 之伺服器的完整網域名稱 (FQDN),例如 http://<伺服器服名稱>.contoso.com。–InternalURL is the fully qualified domain name (FQDN) of the server that runs Office Online Server, such as http://servername.contoso.com.
  • –ExternalURL 是可在網際網路上存取的 FQDN。–ExternalURL is the FQDN that can be accessed on the Internet.
  • –CertificateName 是憑證的易記名稱。–CertificateName is the friendly name of the certificate.

建立 Office Online Server 伺服器陣列 (HTTP)Create the Office Online Server farm (HTTP)

使用 New-OfficeWebAppsFarm 命令,來建立包含單一伺服器的新 Office Online Server 伺服器陣列,如下列範例所示。Use the New-OfficeWebAppsFarm command to create a new Office Online Server farm that consists of a single server, as shown in the following example.

New-OfficeWebAppsFarm -InternalURL "http://servername" -AllowHttp

參數Parameters

  • –InternalURL 是執行 Office Online Server 之伺服器的名稱,例如 http://<伺服器服名稱>。–InternalURL is the name of the server that runs Office Online Server, such as http://servername.
  • –AllowHttp 設定伺服器陣列使用 HTTP。–AllowHttp configures the farm to use HTTP.

確認已成功建立 Office Online Server 伺服器陣列Verify that the Office Online Server farm was created successfully

建立伺服器陣列之後,伺服器陣列的詳細資料會顯示在 Windows PowerShell 提示字元中。After the farm is created, details about the farm are displayed in the Windows PowerShell prompt. 若要確認已正確安裝和設定 Office Online Server,請使用網頁瀏覽器存取 Office Online Server 探索 URL,如下列範例所示。To verify that Office Online Server is installed and configured correctly, use a web browser to access the Office Online Server discovery URL, as shown in the following example. 探索 URL 是設定 Office Online Server 伺服器陣列時所指定的 InternalUrl 參數,後接 /hosting/discovery,例如:The discovery URL is the InternalUrl parameter you specified when you configured your Office Online Server farm, followed by /hosting/discovery, for example:

<InternalUrl>/hosting/discovery

如果 Office Online Server 如預期運作,您應該會在網頁瀏覽器中看到 Web Application Open Platform Interface Protocol (WOPI) 探索 XML 檔案。If Office Online Server works as expected, you should see a Web Application Open Platform Interface Protocol (WOPI)-discovery XML file in your web browser. 該檔案的前幾行應該類似下列範例:The first few lines of that file should resemble the following example:

<?xml version="1.0" encoding="utf-8" ?> 
- <wopi-discovery>
- <net-zone name="internal-http">
- <app name="Excel" favIconUrl="<InternalUrl>/x/_layouts/images/FavIcon_Excel.ico" checkLicense="true">
<action name="view" ext="ods" default="true" urlsrc="<InternalUrl>/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&>" /> 
<action name="view" ext="xls" default="true" urlsrc="<InternalUrl>/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&>" /> 
<action name="view" ext="xlsb" default="true" urlsrc="<InternalUrl>/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&>" /> 
<action name="view" ext="xlsm" default="true" urlsrc="<InternalUrl>/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&>" /> 

設定 Excel 活頁簿大小上限Configure Excel workbook maximum size

Power BI 報表伺服器中所有檔案的檔案大小上限為 100 MB。The maximum file size for all files in Power BI Report Server is 100 MB. 若要保持同步,您需要在 OOS 中手動設定此項目。To stay in sync with that, you need to manually set this in OOS.

Set-OfficeWebAppsFarm -ExcelWorkbookSizeMax 100

搭配使用 EffectiveUserName 與 Analysis ServicesUsing EffectiveUserName with Analysis Services

針對利用 EffectiveUserName 之 Excel 活頁簿內的連線,允許 Analysis Services 的即時連線。To allow for live connections to Analysis Services, for connections within an Excel workbook that make use of EffectiveUserName. 為了讓 OOS 利用 EffectiveUserName,您需要針對 Analysis Services 執行個體,以系統管理員身分新增 OOS 伺服器的電腦帳戶。For OOS to make use of EffectiveUserName, you will need to add the machine account of the OOS server as an administrator for the Analysis Services instance. 需要有 Management Studio for SQL Server 2016 或更新版本,才能執行此作業。Management Studio for SQL Server 2016 or later is needed to do this.

在 Excel 活頁簿內,目前只支援內嵌 Analysis Services 連線。Only embedded Analysis Services connections are currently supported within an Excel workbook. 使用者的帳戶必須擁有連線至 Analysis Services 的權限,因為對使用者進行 Proxy 處理的能力無法使用。The user's account will need to have permission to connect to Analysis Services as the ability to proxy the user is not available.

在 OOS 伺服器上,執行下列 PowerShell 命令。Run the following PowerShell commands on the OOS Server.

Set-OfficeWebAppsFarm -ExcelUseEffectiveUserName:$true
Set-OfficeWebAppsFarm -ExcelAllowExternalData:$true
Set-OfficeWebAppsFarm -ExcelWarnOnDataRefresh:$false

設定資料模型的 Power Pivot 執行個體Configure a Power Pivot instance for data models

安裝 Analysis Services Power Pivot 模式執行個體,可讓您使用要使用 Power Pivot 的 Excel 活頁簿。Installing an Analysis Services Power Pivot mode instance lets you work with Excel workbooks that are using Power Pivot. 確定執行個體名稱是 POWERPIVOTMake sure that the instance name is POWERPIVOT. 針對 Analysis Services Power Pivot 模式執行個體,以系統管理員身分新增 OOS 伺服器的電腦帳戶。Add the machine account of the OOS server as an administrator, for the Analysis Services Power Pivot mode instance. 需要有 Management Studio for SQL Server 2016 或更新版本,才能執行此作業。Management Studio for SQL Server 2016 or later is needed to do this.

為了讓 OOS 使用 Power Pivot 模式執行個體,請執行下列命令。For OOS to use the Power Pivot mode instance, run the following command.

New-OfficeWebAppsExcelBIServer -ServerId <server_name>\POWERPIVOT

如果您尚未允許外部資料,請從上述的 Analysis Services 步驟中執行下列命令。If you did not already allow external data, from the Analysis Services step above, run the following command.

Set-OfficeWebAppsFarm -ExcelAllowExternalData:$true

防火牆考量Firewall considerations

若要避免防火牆問題,您可能需要開啟連接埠 2382 和 2383。To avoid firewall issues, you may need to open the ports 2382 and 2383. 您也可以針對 Power Pivot 執行個體,將 msmdsrv.exe 新增為應用程式防火牆原則。You can also add the msmdsrv.exe, for the Power Pivot instance, as an application firewall wall policy.

設定 Power BI 報表伺服器以使用 OOS 伺服器Configure Power BI Report Server to use the OOS Server

在 [網站設定]的 [一般] 頁面上,輸入 OOS 探索 URL。On the General page of Site settings, enter the OOS discovery url. OOS 探索 URL 是 InternalUrl,在部署 OOS 伺服器時使用,後接 /hosting/discoveryThe OOS discovery url is the InternalUrl, used when deploying the OOS server, followed by /hosting/discovery. 例如,http://servername/hosting/discovery 是針對 HTTP。For example, http://servername/hosting/discovery, for HTTP. https://server.contoso.com/hosting/discovery 是針對 HTTPS。And, https://server.contoso.com/hosting/discovery for HTTPS.

若要到達 [網站設定],請選取右上方的齒輪圖示,然後選取 [網站設定]。To get to Site settings, select the gear icon in the upper right and select Site settings.

只有具有 [系統管理員] 角色的使用者才會看到 Office Online Server 探索 URL 設定。Only a user with the System Administrator role will see the Office Onlien Server discovery url setting.

Power BI 報表伺服器的網站設定。

輸入探索 URL 並選取 [套用] 之後,在入口網站內選取 Excel 活頁簿時應該會在入口網站內顯示入口網站。After you enter the discovery url, and select Apply, selecting an Excel workbook, within the web portal, should display the workbook within the web portal.

限制與考量Limitations and considerations

  • 在 Power BI 報表伺服器內檢視 Excel 活頁簿的能力目前為 Preview。The ability to view Excel workbooks within Power BI Report Server is currently in preview.
  • 您會有活頁簿的唯讀功能。You will have read only capability with workbooks.

後續步驟Next steps

系統管理員手冊Administrator handbook
快速入門︰安裝 Power BI 報表伺服器Quickstart: Install Power BI Report Server
安裝報表產生器Install Report Builder
下載 SQL Server Data Tools (SSDT)Download SQL Server Data Tools (SSDT)

有其他問題嗎?More questions? 嘗試在 Power BI 社群提問Try asking the Power BI Community