oracle 如何在 Toad for Data Analyst 中将结果导出到 Excel 的不同选项卡？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2135798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export the result into different tabs of Excel in Toad for Data Analyst?
提问by Sudha Parajulee
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 工作簿的不同工作表中？谢谢
回答by Zoki
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 执行一些小技巧。
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.
编写第一个查询并在 Toad 中执行，然后右键单击查询结果数据网格并选择“导出数据集...”，在 Excel 格式下选择“Excel 实例”并单击确定。它将打开 Excel 并添加一张包含查询数据的工作表。
Repeat same process for second query and it will add another sheet to same document and fill with data from second query.
对第二个查询重复相同的过程，它会将另一个工作表添加到同一文档并填充来自第二个查询的数据。
After you executed all queries and added it to Excel save excel document.
执行完所有查询并将其添加到 Excel 后，保存 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.
如果您想完全自动执行此操作，则可以使用另一种解决方案来创建具有多个工作表的单个 Excel 文档，这些工作表加载了来自不同查询的数据。购买第三方 PL/SQL 包 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;
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.
它可以生成 Excel 文件并将其存储到 Oracle 目录中，或者您可以将生成的 Excel 文件放入 PL/SQL BLOB 变量中，以便您可以将其存储到表中或创建自己的过程来分发文件，例如将其发送到电子邮件。
More details you can find on products documentation/examples page: http://www.oraexcel.com/examples
您可以在产品文档/示例页面上找到更多详细信息：http: //www.oraexcel.com/examples
Cheers
干杯
回答by APC
I don't think this functionality exists in TOAD.
我认为 TOAD 中不存在此功能。
The usual solution for exporting straight from PL/SQL to Excel - Tom Kyte's OWA_SYLK wrapper for the SYLK api - only works with single worksheets. There are a couple of alternative solutions.
直接从 PL/SQL 导出到 Excel 的常用解决方案 - Tom Kyte 用于 SYLK api 的 OWA_SYLK 包装器 - 仅适用于单个工作表。有几种替代解决方案。
Sanjeev Sapre has his get_xl_xml package. As the name suggests it uses XML to undertake the transformation. Find out more.
Sanjeev Sapre 有他的 get_xl_xml 包。顾名思义，它使用 XML 进行转换。 了解更多。
Jason Bennett has written a PL/SQL object which generates an Excel XML document. Find out more.
Jason Bennett 编写了一个生成 Excel XML 文档的 PL/SQL 对象。 了解更多。
回答by Babu
You no longer need to write code to output data for multiple sheets. As long as your SQL has queries identified clearly (with semicolons), TDA or now TDP will automatically dump data for different SQLs in different worksheets.
您不再需要编写代码来输出多张工作表的数据。只要您的 SQL 有明确标识的查询（使用分号），TDA 或现在的 TDP 将自动转储不同工作表中不同 SQL 的数据。
回答by Jimmy
I have Toad for Data Analyst 2.6. I use the keyword GO between queries.
我有 Toad for Data Analyst 2.6。我在查询之间使用关键字 GO。
Select * from tableA;
GO
Select * from tableB;
This creates two tabs in Excel.
这将在 Excel 中创建两个选项卡。
