问题描述

有谁知道如何使用数据分析师在 TOAD 中的报表自动化将多个查询的结果导出到同一个 Excel 工作簿的不同工作表中?谢谢

Does anyone know how to export results from more than one query into different sheets of the same Excel workbook using the report automation in TOAD for data analyst? Thank you

推荐答案

我不确定您是否可以使用 Toad 自动执行此操作，但是您可以使用 Excel 执行一个小技巧.

I'm not sure that you can do that with Toad automatically but there is a little trick that you can do with Excel.

编写第一个查询并在Toad中执行，然后右键单击查询结果数据网格并选择导出数据集..."，在Excel格式下选择Excel实例"并单击确定".它将打开 Excel 并添加一张包含查询数据的工作表.

Write first query and execute it in Toad, after that right click on query result data grid and choose "Export dataset...", under Excel format choose "Excel instance" and click OK. It will open Excel and add one sheet with data from your query.

对第二个查询重复相同的过程，它会将另一个工作表添加到同一文档并填充第二个查询的数据.

Repeat same process for second query and it will add another sheet to same document and fill with data from second query.

执行完所有查询并将其添加到 Excel 后，保存 excel 文档.

After you executed all queries and added it to Excel save excel document.

如果您想完全自动执行此操作，您可以使用另一种解决方案来创建包含多个工作表的单个 Excel 文档，这些工作表加载了来自不同查询的数据.购买第三方 PL/SQL 包 ORA_EXCEL.

If you want to do that completely automatically, there is another solution which you can use to create single Excel document with multiple sheets which are loaded with data from different queries. Purchase the third party PL/SQL package, ORA_EXCEL.

这是如何做到这一点的示例:

Here is example how to do that:

BEGIN  
    ORA_EXCEL.new_document;  

    ORA_EXCEL.add_sheet('Employees');  
    ORA_EXCEL.query_to_sheet('select * from employees');  

    ORA_EXCEL.add_sheet('Departments');  
    ORA_EXCEL.query_to_sheet('select * from departments', FALSE);  

    ORA_EXCEL.add_sheet('Locations');  
    ORA_EXCEL.query_to_sheet('select * from locations');  

    -- EXPORT_DIR is an Oracle directory with at least  
    -- write permission  
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');  
END;

它可以生成 Excel 文件并将其存储到 Oracle 目录中，或者您可以将生成的 Excel 文件放入 PL/SQL BLOB 变量中，以便您可以将其存储到表中或创建自己的过程来分发文件，例如将其发送到电子邮件.

It can generate Excel file and store it to Oracle directory, or you can get generated Excel file into PL/SQL BLOB variable so you can store it to table or create your own process to distribute file like sending it to email.

您可以在产品文档/示例页面上找到更多详细信息:http://www.oraexcel.com/examples

More details you can find on products documentation/examples page: http://www.oraexcel.com/examples

干杯

这篇关于如何在 Toad for Data Analyst 中将结果导出到 Excel 的不同选项卡?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！