Excel报表

如何使用iTop查询创建Excel报告

Excel的最新版本(Excel 2016,Office 365)提供了两种将Web数据导入Excel的方法。默认的最新版本基于PowerQuery,不支持基本的身份验证,因此不适用于iTop。但是,仍然可以使用旧方法作为可在Excel中启用的额外菜单项。为了使事情更加混乱,两个菜单项都具有完全相同的标签!

  • 确保正确配置了Excel

单击菜单项“数据”“来自Web”。如果您看到与以下对话框类似的对话框(PowerQuery),则不好。

Excel GetPowerQueryDataFromWeb wizard

相反,您应该得到一个类似于以下的对话框:

Excel GetExternalDataFromWeb wizard

要使用旧的GetExternalDataFromWeb向导,请按照下列步骤操作:

转到“文件选项”菜单

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel-import-options-menu.png

单击左侧的“自定义功能区”菜单:

在左上角的下拉列表中,选择“主要选项卡”,在显示的树中,打开项目“数据”,然后选择菜单项“来自Web”

https://www.itophub.io/wiki/media?w=800&tok=be7396&media=2_7_0%3Auser%3Aexcel-import-ribbon-dialog.png

由于旧菜单和新菜单具有完全相同的标签,因此请确保选择正确的菜单:将鼠标移到项目上;显示一个小工具提示,它应显示为“ GetExternalDataFromWeb”,而不是“ GetPowerQueryDataFromWeb”

GetExternalDataFromWeb

要将命令添加到功能区,您必须创建一个新的“组”(使用对话框底部的按钮)。该组使用默认名称命名,但是您可以根据需要将其重命名:

New group

同样,您可以重命名菜单并为其选择图标。将菜单添加到功能区后,应该会看到类似以下内容:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aweb-excel-import-command.png

当您单击自定义菜单时,将出现“来自Web的外部数据”向导:

https://www.itophub.io/wiki/media?w=600&tok=f9262c&media=2_7_0%3Auser%3Anew-web-excel-import-query.png

  • 从iTop导入数据

管理员可以将预定义查询存储在iTo​​p中(菜单“管理查询手册”)。这些查询随后可用于创建Excle报表。查询由数字标识。使用iTop管理员提供的URL在Excel中调用它。例如,下面存储在查询手册中的URL返回用户请求的列表:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report8.png

要在Excel中使用此查询,请在Excel选项卡数据中单击“从Web上”按钮。

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report1.png

显示一个窗口,输入要调用的URL:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report2.png

在“地址”部分输入iTop管理员提供的URL,然后单击“转到”。

显示iTop身份验证窗口。输入您的用户账号和您的密码:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report3.png

单击确定:如果安全弹出窗口提示您。

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report4.png

New Web查询已更新为要导入的列列表:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report5.png

单击导入按钮。 Excel询问您要在何处插入数据:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report6.png

如果您的查询包含参数,Excel将打开多个连续的窗口以输入它们。请注意,日期参数仅支持yyyy-mm-dd格式。

单击“确定”按钮,Excel将显示查询的结果。用于显示列的语言对应于iTop中登录名的默认语言。

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report7.png

要刷新数据,请在Excel的数据选项卡中单击全部刷新按钮:

您可以根据需要使用多个iTop查询,并使用结果使用标准Excel功能动态创建图形或图表。

原贴链接:https://www.itophub.io/wiki/page?id=2_7_0%3Auser%3Aexcel_report


Reports in Excel

How to create an Excel report using an iTop query

Recent versions of Excel (Excel 2016, Office 365) provide two methods for importing Web Data into Excel. The most recent version, which is the default, is based on PowerQuery and does not support Basic Authentication, and thus does not work with iTop. However the old method is still available as an extra menu item that you can enable in Excel. To get things even more confusing, both menu items have exactly the same label!

Make sure that Excel is properly configured

Click on the menu item “Data” / “From Web”. If you get a dialog similar to the one below (PowerQuery) this is not good.

Excel GetPowerQueryDataFromWeb wizard

Instead you should get a dialog similar to this:

Excel GetExternalDataFromWeb wizard

To use the old GetExternalDataFromWeb wizard, follow these steps:

Go to the File / Options menu

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel-import-options-menu.png

Click on the “Customize Ribbon” menu on the left:

In the drop-down list at the top-left, select “Main tabs”, in the tree displayed, open the item “Data” and select the menu item “From Web”

https://www.itophub.io/wiki/media?w=600&tok=61105f&media=2_7_0%3Auser%3Aexcel-import-ribbon-dialog.png

Since the old and the new menus have exactly the same label, make sure that you select the right one: let your mouse over the item ; a small tooltip is displayed, it should read “GetExternalDataFromWeb” and not “GetPowerQueryDataFromWeb”

GetExternalDataFromWeb

To add the command to your Ribbon you must create a new “Group” (using the button at the bottom of the dialog box). The group is named with a default name, but you can rename it if you like:

New group

Similarly, you can rename the menu and choose an icon for it. Once your menu has been added to the ribbon, you should see something similar to this:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aweb-excel-import-command.png

When you click on the custom menu, the “External Data From Web” wizard should appear:

https://www.itophub.io/wiki/media?w=600&tok=f9262c&media=2_7_0%3Auser%3Anew-web-excel-import-query.png

Importing data from iTop

编辑

Administrators can store predefined queries in iTop (menu “Administration / Query Phrasebook”). These queries can then be used to create reports in Excel. A query is identified by a number. It is called in Excel using a URL provided by your iTop administrator. For example, the URL below stored in the query phrasebook, returns a list of User Request:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report8.png

To use this query in Excel, click on the button From Web in the Excel tab Data

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report1.png

A window is displayed to enter the URL to be called:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report2.png

Enter the URL provided by the iTop administrator in the Address part and click on Go.

The iTop authentication window is displayed. Enter your user account and your password:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report3.png

Click on Ok: if you are prompt by security popup windows.

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report4.png

The New Web Query is updated with the list of columns to be imported:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report5.png

Click the Import button. Excel asks you where you want to insert the data:

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report6.png

If your query contains parameters, Excel will open several successive windows to enter them. Be careful, date parameters only support the format yyyy-mm-dd.

Click the OK button and Excel displays the result of the query. The language used for the display of columns corresponds to the default language of your login in iTop.

https://www.itophub.io/wiki/media?media=2_7_0%3Auser%3Aexcel_report7.png

To refresh the data, click on the Refresh All button in the Data tab of Excel:

You can use as many iTop queries as you want and use the results to create graphs or charts dynamically using standard Excel features.

标签:
由 superadmin 在 2020/08/25, 16:07 创建
    

需要帮助?

如果您需要有关XWiki的帮助,可以联系:

深圳市艾拓先锋企业管理咨询有限公司