使用 PL/SQL 创建 xls 文件而无需通过 xml
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12733336/
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
Create xls file using PL/SQL without going through xml
提问by user1720948
My system is developed in APEX/Oracle 11g, and I want to create an xls file directly without having to create an xml file. The system currently creates an xml file which can then be saved to xls format, but the user, who is very picky, does not like the Windows 7 warning when one tries to open the xml file (Excel warning that the format of the file does not match its extension). Is there any way to use Oracle PL/SQL from within APEX to accomplish this?
我的系统是APEX/Oracle 11g开发的，想直接创建xls文件，不用创建xml文件。系统当前创建了一个xml文件，然后可以保存为xls格式，但是非常挑剔的用户不喜欢尝试打开xml文件时的Windows 7警告（Excel警告该文件的格式与其扩展名不匹配）。有什么方法可以在 APEX 中使用 Oracle PL/SQL 来完成此操作？
回答by Andrew
Morten Braten has put together a great PLSQL resource page: http://code.google.com/p/plsql-utils/
Morten Braten 整理了一个很棒的 PLSQL 资源页面：http: //code.google.com/p/plsql-utils/
Specifically Anton Scheffer has shared his package AS_XLSX which would meet your APEX needs: http://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/
特别是 Anton Scheffer 分享了他的包 AS_XLSX，它可以满足您的 APEX 需求：http://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/
Very simple to use with good examples in the package header.
使用包头中的好示例非常简单。
回答by Bestija
You can use the OraExcel package to generate real xlsx files (in binary form). With that package you can format cells and apply styles just like in Excel.
您可以使用 OraExcel 包生成真正的 xlsx 文件（以二进制形式）。使用该软件包，您可以像在 Excel 中一样格式化单元格并应用样式。
It has a simple API where you can create Excel files step by step and describe every cell how you want to look like.
它有一个简单的 API，您可以在其中逐步创建 Excel 文件并描述每个单元格的外观。
When you finish your Excel file there is an option to generate xlsx file to PL/SQL BLOB variable which can be returned to APEX and downloaded.
完成 Excel 文件后，可以选择将 xlsx 文件生成为 PL/SQL BLOB 变量，该变量可以返回到 APEX 并下载。
There are no warnings that are annoying to your customers.
没有令您的客户烦恼的警告。
You can write a simple function, like the one below, to create an excel spreadsheet and return it to APEX:
您可以编写一个简单的函数（如下所示）来创建 Excel 电子表格并将其返回给 APEX：
CREATE OR REPLACE FUNCTION get_excel RETURN BLOB IS
my_blob BLOB;
BEGIN
ora_excel.new_document;
ora_excel.add_sheet('My sheet');
ora_excel.query_to_sheet('SELECT field1, field2, field2 FROM my_table');
ora_excel.save_to_blob(my_blob);
RETURN my_blob;
END;
There are more examples on: http://www.oraexcel.com/examples
有更多的例子：http: //www.oraexcel.com/examples
Cheers
干杯
回答by Jayson Lorenzen
You can use a Java Stored Procedure
您可以使用 Java 存储过程
http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm
http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm
and Apache Poi
和阿帕奇Poi
http://poi.apache.org/spreadsheet/index.html
http://poi.apache.org/spreadsheet/index.html
to create actual Spreadsheets and store those in the lob field, and to return to the user
创建实际的电子表格并将其存储在 lob 字段中，然后返回给用户
