oracle 将oracle中的数据提取到excel中的多张表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14690004/
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
Extract data from oracle to multiple sheets in excel
提问by Vivek
Is there any way we can extract data from oracle tables to multiple sheets in an excel?
有什么方法可以将oracle表中的数据提取到excel中的多个工作表中吗？
For example: I have two tables checkpointsand hold. I want data from checkpointsto go to sheet1of MS exceland data from holdto go to sheet2.
例如：我有两个表checkpoints和hold. 我想从数据checkpoints去sheet1的MS excel和数据hold去sheet2。
Is this possible to implement in oracle?
这可以在oracle中实现吗？
Oracle version: Oracle 11G
Oracle version: Oracle 11G
- Edit:I would like to implement this using PL/SQL. This code will be executed as a batch job.
- 编辑：我想使用 PL/SQL 来实现它。此代码将作为批处理作业执行。
回答by Jeffrey Kemp
If the spreadsheet you want to generate is verysimple (i.e. just cells with data, and multiple sheets) you could try the Excel generation API in the Alexandria PL/SQL Library- just grab XLSX_BUILDER_PKGand its dependencies (I don't think there are many) and it should do the job.
如果您要生成的电子表格非常简单（即只有带有数据的单元格和多个工作表），您可以尝试Alexandria PL/SQL 库中的 Excel 生成 API - 只需抓取XLSX_BUILDER_PKG及其依赖项（我认为没有很多)，它应该可以完成这项工作。
I've used it myself to generate simple spreadsheets in XLSX format, and it's all in PL/SQL. It returns the results in a BLOB, and there's another routine in the library for spitting that out with UTL_FILE, if you need the result on the database server.
我自己用它生成了 XLSX 格式的简单电子表格，而且都是 PL/SQL 格式的。它以 BLOB 形式返回结果，如果您需要数据库服务器上的结果，库中有另一个例程用于将其与 UTL_FILE 一起输出。
回答by Alen Oblak
Of course it is possible, but it depends how do you want that to be done.
当然这是可能的，但这取决于您希望如何完成。
1) If you have a reporting tool (Jasper Reports, Oracle BI) you can use that tool to create a report and export the data.
1) 如果您有报告工具（Jasper Reports、Oracle BI），您可以使用该工具创建报告并导出数据。
2) If you have a developer tool (Toad, SQL Developer) you can export the two tables "by hand".
2）如果您有开发工具（Toad、SQL Developer），您可以“手动”导出这两个表。
3) If you know how to write code, than you can use a API to create the Excel file and populate it with the data. You can use PL/SQL, PHP, C, C++ or almost any other language, it just needs a Oracle API and an Excel API.
3) 如果您知道如何编写代码，那么您可以使用 API 来创建 Excel 文件并用数据填充它。您可以使用 PL/SQL、PHP、C、C++ 或几乎任何其他语言，它只需要一个 Oracle API 和一个 Excel API。
回答by ora_excel
There is PL/SQL package that is able to create Excel document with multiple sheets and put data from SQL query to separate sheets.
有一个 PL/SQL 包，它能够创建带有多个工作表的 Excel 文档，并将 SQL 查询中的数据放到单独的工作表中。
Please see example below:
请看下面的例子：
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;
More details you can find here: http://www.oraexcel.com/examples/pl-sql-excel-query-to-sheet-export
您可以在此处找到更多详细信息：http: //www.oraexcel.com/examples/pl-sql-excel-query-to-sheet-export
