PL/SQL操作EXCEL

Love The Way You Lie 2022-07-12 13:51 149阅读 0赞

PL/SQL操作EXCEL(暂存)测试通过,修改部分,但是整个过程还没有理顺...

PL/SQL操作EXCEL有多种方法，但都不理想。比较了一下，还是通过JAVA的方法比较适用。

1. 使用UTL\_FILE包  
declare  
l\_file utl\_file.file\_type;  
BEGIN  
l\_file :=utl\_file.fopen('MY\_DIR','test1.xls','w');  
utl\_file.put\_line(l\_file,'jobs表导出数据');  
utl\_file.fflush(l\_file);  
end;

说明：此方法操作文本尚可，对于EXCEL这种COM文件并不可行。网络大多数所谓的PL/SQL操作EXCEL， 也是使用cvs

这种简单的格式。如果要读入稍微复杂点的EXCLE模板文件，utl\_file就挂掉了。

2. 使用OLE2

declare  
application OLE2.OBJ\_TYPE;  
workbooks OLE2.Obj\_Type;  
workbook OLE2.Obj\_Type;  
worksheets OLE2.Obj\_Type;  
worksheet OLE2.Obj\_Type;  
args OLE2.List\_Type;  
cell OLE2.Obj\_Type;  
begin  
application:=OLE2.CREATE\_OBJ('Excel.Application');  
OLE2.Set\_Property(application,'Visible','True');  
workbooks := OLE2.GET\_OBJ\_PROPERTY(application,'Workbooks');  
workbook:=OLE2.INVOKE\_OBJ(workbooks,'Add');  
worksheets := OLE2.GET\_OBJ\_PROPERTY(workbook, 'Worksheets');  
worksheet:=OLE2.INVOKE\_OBJ(worksheets,'Add');  
args:=OLE2.CREATE\_ARGLIST;  
OLE2.ADD\_ARG(args, 4);  
OLE2.ADD\_ARG(args, 2);  
cell := OLE2.GET\_OBJ\_PROPERTY(worksheet,'CELLS',args);  
OLE2.DESTROY\_ARGLIST(args); OLE2.Set\_Property(cell, 'Value', 'Hello Excel!');  
args:=OLE2.CREATE\_ARGLIST; OLE2.ADD\_ARG(args, 5);  
OLE2.ADD\_ARG(args, 3);  
cell := OLE2.GET\_OBJ\_PROPERTY(worksheet,'CELLS',args);  
OLE2.DESTROY\_ARGLIST(args);  
OLE2.Set\_Property(cell, 'Value', 'Hello Excel!');  
exception when others then FND\_MESSAGE.DEBUG('ERROR:'||SQLERRM);  
end;

说明：Object Link and embed，有学过VB/VC的同学们应该很熟悉。可惜ORACLE本身并不支持OLE2，OLE2是ORACLE

FORMS集成的一个OLE接口。所以限制此方法的使用范围。另外更重要的OLE2无法在B/S架构中使用，只能在传统的FORM

C/S程序中使用。

3. 使用ORACLE COM函数 这种方法就与通过extproc来实现与excle通信，这种方法直接与window api打交道。大致的

的代码是：ordcom.CreateObject(‘Excel.Application‘, 0, servername,applicationToken); 测试了一下，似乎只能

要求ORACLE的宿主系统必须是window。这也就有严重的局限性了。

4. 使用JAVA存储过程(通过POI包，这也是本文的重点)

4.1至[http://poi.apache.org][http_poi.apache.org] 下载POI包，注意与ORACLE JVM版本匹配(旧版本的POI下载：[http://archive.apache.org/dist/jakarta/poi/release/bin/][http_archive.apache.org_dist_jakarta_poi_release_bin])。至ORACL官网下载JDBC驱动，这一步不是必须的，

一般安装ORACLE时，都有内置了JDBC包。

4.2 在ORACLE注册POI。

4.2.1先将POI包上传至ORACLE服务器。目录为：$ORACLE\_HOME/javavm/lib ，

例：/data/book/bookdb/9.2.0/javavm/lib，必须是javavm/lib这个目录或其下层目录。

4.2.2使用DBMS\_JAVA注册(好像只需要注册第一个即可..)

call dbms\_java.loadjava('-r -v -definer -g public D:/oracle/ora92/javavm/lib/poi-2.5.1-final-20040804.jar');

call dbms\_java.loadjava('-r -v -definer -g public D:/oracle/ora92/javavm/lib/poi-contrib-2.5.1-final-20040804.jar');

call dbms\_java.loadjava('-r -v -definer -g public D:/oracle/ora92/javavm/lib/poi-scratchpad-2.5.1-final-20040804.jar');

注：这个问题折腾了我很久，注册JAR包有两种方法。1.使用DBMS\_JAVA，这种方法要自行上传jar包到服务器上。2.

是使用loadjava。这也是能google到最多的方法，但loadjava在ORACLE9i以后就不单独提供了，而是集成在JDeveloper中

，而可恶的JDeveloper是有版本兼容性的问题。(也折腾我啊,两种方法都试了,不行,最后不知道怎么的就可以,回家干净的ORACLE环境测试)

4.2.3查询一下是否注册成功(注：如果注册成功，STATUS应该为VALID)。

Select OBJECT\_NAME, OBJECT\_TYPE, STATUS, SYS.DBMS\_JAVA.LONGNAME(OBJECT\_NAME) LONGNAME,

LAST\_DDL\_TIME, OBJECT\_ID, uo.created

from sys.user\_objects uo

where object\_type in('JAVA CLASS', 'JAVA SOURCE', 'JAVA RESOURCE', 'JAVA DATA')

and uo.object\_name like '%poi%'

4.3创建JAVA SOURCE(从网上摘录一篇简单实用POI的JAVA类)

create or replace and compile java source named createcells as  
import java.io.FileOutputStream;    
import java.io.IOException;    
import java.util.Date;    
   
import org.apache.poi.hssf.usermodel.HSSFCell;    
import org.apache.poi.hssf.usermodel.HSSFCellStyle;    
import org.apache.poi.hssf.usermodel.HSSFDataFormat;    
import org.apache.poi.hssf.usermodel.HSSFRow;    
import org.apache.poi.hssf.usermodel.HSSFSheet;    
import org.apache.poi.hssf.usermodel.HSSFWorkbook;    
import org.apache.poi.hssf.util.HSSFColor;    
public class CreateCells  
\{  
  /\*\*   
     \* HSSFWorkbook excell的文档对象 HSSFSheet excell的表单 HSSFRow excell的行 HSSFCell   
     \* excell的格子单元 HSSFFont excell字体 HSSFName 名称 HSSFDataFormat 日期格式 HSSFHeader   
     \* sheet头 HSSFFooter sheet尾 HSSFCellStyle cell样式   
     \*/  
  public static void main(String\[\] args)  
  \{  
         HSSFWorkbook wb = new HSSFWorkbook();    
        // 建立新HSSFWorkbook对象    
        HSSFSheet sheet = wb.createSheet("new sheet");    
        // 建立新的sheet对象    
        // Create a row and put some cells in it.Rows are 0 based.    
        HSSFRow row = sheet.createRow((short) 0);    
        // 建立新行    
        // Create a cell and put a value in it.    
        HSSFCell cell = row.createCell((short) 0);    
        // 建立新cell    
        cell.setCellValue(1);// 设置cell的整数类型的值    
        // Or do it on one line.    
        row.createCell((short) 1).setCellValue(1.2);    
        // 设置cell浮点类型的值    
        row.createCell((short) 2).setCellValue("test");    
        // 设置cell字符类型的值    
        row.createCell((short) 3).setCellValue(true);    
        // 设置cell布尔类型的值    
        HSSFCellStyle cellStyle = wb.createCellStyle();    
        // 建立新的cell样式    
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));    
        // 设置cell样式为定制的日期格式    
        HSSFCell dCell = row.createCell((short) 4);    
        dCell.setCellValue(new Date());    
        // 设置cell为日期类型的值    
        dCell.setCellStyle(cellStyle);    
        // 设置该cell日期的显示格式    
        HSSFCell csCell = row.createCell((short) 5);    
        csCell.setEncoding(HSSFCell.ENCODING\_UTF\_16);    
        // 设置cell编码解决中文高位字节截断    
        csCell.setCellValue("中文测试\_Chinese Words Test");    
   
        // 设置背景色    
        HSSFCellStyle style = wb.createCellStyle();    
        style    
                .setFillForegroundColor(new HSSFColor.GREY\_25\_PERCENT()    
                        .getIndex());    
        style    
                .setFillBackgroundColor(new HSSFColor.GREY\_25\_PERCENT()    
                        .getIndex());    
        style.setFillPattern(HSSFCellStyle.SPARSE\_DOTS);    
        HSSFCell cell1 = row.createCell((short) 6);    
        cell1.setCellValue("X");    
        cell1.setCellStyle(style);    
   
        // 设置背景色    
        HSSFCellStyle style1 = wb.createCellStyle();    
        style1.setFillForegroundColor(new HSSFColor.GREY\_40\_PERCENT()    
                .getIndex());    
        style1.setFillBackgroundColor(new HSSFColor.GREY\_40\_PERCENT()    
                .getIndex());    
        style1.setBorderBottom((short) 1);    
        style1.setBorderTop((short) 1);    
        style1.setBorderLeft((short) 1);    
        style1.setBorderRight((short) 1);    
        /\*\*   
         \* 注意这句代码， style1.setFillPattern, 如果你在你的程序中不设置fill pattern,那么   
         \* 你上面设置的前景色和背景色就显示不出来.网络上很多文章都没有设置fillpattern, 不知道那些达人 的机器是不是比我的机器智能很多.   
         \*/   
        style1.setFillPattern(HSSFCellStyle.SPARSE\_DOTS);    
        HSSFCell cell11 = row.createCell((short) 7);    
        cell11.setCellValue("X11");    
        cell11.setCellStyle(style1);    
   
        // 数字格式化    
        HSSFCellStyle st = wb.createCellStyle();    
        // 建立新的cell样式    
        st.setDataFormat(HSSFDataFormat.getBuiltinFormat("\#,\#\#0"));    
        HSSFCell cell12 = row.createCell((short) 8);    
        cell12.setCellValue((double) 10000000);    
        cell12.setCellStyle(st);    
   
        // 设置中西文结合字符串    
        row.createCell((short) 9).setCellType(HSSFCell.CELL\_TYPE\_ERROR);    
        // 建立错误cell    
        // Write the output to a file    
        try\{  
            FileOutputStream fileOut = new FileOutputStream("c:/workbook.xls");  
            wb.write(fileOut);    
            fileOut.close();  
        \}catch(Exception e)\{  
                    System.out.println("poi error :"+e);  
                    e.printStackTrace();  
             \}   
  \}  
\}  
4.5创建调用JAVA类的存储过程

create or replace procedure test\_java  
as language java  
name 'CreateCells.main(java.lang.String\[\])';

4.6测试PL/SQL块

begin

execute test\_java;

end;

5. 使用其他编程语言操作 说明：此方法完全脱离了ORACLE，已超出了ORACLE的范围，本文不讨论

[http_poi.apache.org]: http://poi.apache.org
[http_archive.apache.org_dist_jakarta_poi_release_bin]: http://archive.apache.org/dist/jakarta/poi/release/bin/