使用 Office Online Server (OOS) 将报表服务器配置为托管 Excel 工作簿Configure your report server to host Excel workbooks using Office Online Server (OOS)

除了在 Web 门户中查看 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 月预览版本中的预览功能。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.

从报表服务器 Web 门户查看的 Excel 报表。

这是通过使用 Office Online Server (OOS) 实现的。This is accomplished by making use of Office Online Server (OOS).

准备要运行 Office Online Server 的服务器Prepare 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

如果你打算使用任何利用外部数据访问(例如 Power Pivot)的 Excel Online 功能,请注意,Office Online Server 必须与其用户以及你打算使用基于 Windows 的身份验证访问的任何外部数据源位于同一个 Active Directory 林中。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. 批量许可服务中心 (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 服务器安装语言包(可选)Install language packs for Office Web Apps Server (optional)

Office Online Server 语言包可让用户以多种语言查看基于 Web 的 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 语言包。Download the Office Online Server Language Packs from the Microsoft Download Center.
  2. 运行 wacserverlanguagepack.exe。Run wacserverlanguagepack.exe.
  3. 在 Office Online Server 语言包向导中的“阅读 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://servername.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 是在 Internet 上可访问的 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://servername。–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 是否已正确安装和配置,请使用 Web 浏览器访问 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 浏览器中看到 Web 应用程序开放平台接口协议 (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

实现实时连接到 Analysis Services 以及利用 EffectiveUserName 的 Excel 工作簿中的连接。To allow for live connections to Analysis Services, for connections within an Excel workbook that make use of EffectiveUserName. 为了使 OOS 能够利用 EffectiveUserName,需要将 OOS 服务器的计算机帐户添加为 Analysis Services 实例的管理员。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 的权限,因为代理用户的功能不可用。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. 请确保实例名称是 POWERPIVOT。Make sure that the instance name is POWERPIVOT. 将 OOS 服务器的计算机帐户添加为 Analysis Services Power Pivot 模式实例的管理员。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/discovery。The 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 并选择“应用”,在 Web 门户中选择 Excel 工作簿时,应在 Web 门户中显示该工作簿。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 工作簿的功能。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