使用PL / SQL创建xls文件而不通过xml

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来实现这一目标?

3 个解决方案

#1

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.

使用包头中的好例子非常简单。

#2

You can use a Java Stored Procedure

您可以使用Java存储过程

http://docs.oracle.com/cd/B28359_01/java.111/b31225/chfive.htm

and Apache Poi

和Apache Poi

http://poi.apache.org/spreadsheet/index.html

to create actual Spreadsheets and store those in the lob field, and to return to the user

创建实际的Spreadsheets并将其存储在lob字段中,并返回给用户

#3

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

#1