oracle数据库中,用SQL Select直接訪問訪問存儲在blob中的excel數據

 在我的工作中,经常需要访问Excel数据或者导入到oracle数据库中

常用的做法是:
   在前端程序中是逐行访问,读取数据.
   在oracle后端也可以处理.还更加方便

我封装了一个通用的package utl_excel.用来用SQL直接查询.

範例1:  excel数据存放在exceldata的blob_data栏位
   select  *
    from  exceldata a,
    table( utl_excel.get_xls_table(blob_data) ) xx
    where a.name='MSOFFICE.EXCEL';

範例2:  直接读取http或者ftp上的excel文件,    utl_lob是另外一个通用package
  select  *
    from    table(
             utl_excel.get_xls_table(utl_lob.url2blob('http://192.168.1.11/data/test.xls')  ) )


原理:
  java使用 jxl.jar 訪問excel ,用pl/sql封装成package

oracle 封装代碼如下,

    CREATE OR REPLACE PACKAGE utl_excel AS
    /****************************************************************************
       Ver Date Author Description
       --------- ---------- --------------- --------------------------------------------
       1.0 2013-11-05 gangjh 1. 使用java jxl.jar 訪問blob的excel 2000數據.
                                                   返回table類型數據.
                                                   不支持xlsx,和ods格式
    *******************************************************************************/

      SUBTYPE ctxHandle IS binary_integer;
      subtype celltype is varchar2(2000) ;

      type cell_val is record (
      sheet int,
      row_idx int,
      col_idx int,
      data celltype
      ) ;
      type xls_data is table of cell_val ;
      c_max_cols constant pls_integer := 20;
      type xls_row is record (
        sheet int ,
        row_idx int ,
        col0 celltype,
        col1 celltype,
        col2 celltype,
        col3 celltype,
        col4 celltype,
        col5 celltype,
        col6 celltype,
        col7 celltype,
        col8 celltype,
        col9 celltype,
        col10 celltype,
        col11 celltype,
        col12 celltype,
        col13 celltype,
        col14 celltype,
        col15 celltype,
        col16 celltype,
        col17 celltype,
        col18 celltype,
        col19 celltype
      );
      
      type xls_table is table of xls_row ;
     


    /****************************************
     with xt as
    ( select a.id,a.name ,xx.*
        from xmldata a,
        table( utl_excel.get_xls_val(blob_temp) ) xx
        where a.name='MSOFFICE.EXCEL'
    )

    select id,name,sheet,row_idx,
    max(decode(col_idx,0,data,'')) col0,
    max(decode(col_idx,1,data,'')) col1,
    max(decode(col_idx,2,data,'')) col2
    from xt
    group by id,name,sheet,row_idx;
    ************************/
      
      function get_xls_val(data in blob) RETURN xls_data PIPELINED ;


    /*********************************************
    ex1:
      select a.id,xx.*,a.name
        from xmldata a,
        table( utl_excel.get_xls_table(blob_temp) ) xx
        where a.name='MSOFFICE.EXCEL' and id=23 and sheet=4

    ex2:
    select * from table(utl_excel.get_xls_table(utl_lob.url2blob('ftp://172.17.2.43/pub/test001.xls')))
        
    ************************************/

    -- p_sheet := -1 : select all sheet
      function get_xls_table(p_data in blob, p_sheet int default -1 ) RETURN xls_table PIPELINED ;



    END utl_excel;
    /
    CREATE OR REPLACE PACKAGE body utl_excel AS
    excel_parsing_error EXCEPTION;
        pragma EXCEPTION_INIT(excel_parsing_error, -29532);
    /**************** java implement ****************************/
      FUNCTION newContext(data IN blob) RETURN ctxHandle
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.newcontext(oracle.sql.BLOB) return int';

      PROCEDURE closeContext(ctx IN ctxHandle)
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.closecontext(int)';

      procedure select_sheet(ctx IN ctxHandle ,sheet in binary_integer)
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.select_sheet(int, int)';

      function getSheets(ctx IN ctxHandle) return binary_integer
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.getSheets(int) return int';

      function getRows(ctx IN ctxHandle, sheet binary_integer) return binary_integer
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.getRows(int, int) return int';

      function getColumns(ctx IN ctxHandle, sheet binary_integer) return binary_integer
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.getColumns(int, int) return int';

      FUNCTION getValue(ctx IN ctxHandle, sheet binary_integer, p_row in binary_integer, p_col in binary_integer) RETURN varchar2
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.gettext(int, int, int, int) return java.lang.String';

      function getSheetCount(ctx IN ctxHandle) return binary_integer
      as LANGUAGE JAVA NAME
       'corp.excel.Excelobj.getSheetCount(int) return int';

    /*********************************************/

    function get_xls_val(data in blob) RETURN xls_data PIPELINED
    is
    ctx utl_excel.ctxHandle ;
    r int ;
    sheet_cnt pls_integer ;
    row_cnt pls_integer;
    col_cnt pls_integer;
    cell cell_val ;
    begin
      ctx := newcontext(data) ;
      
      sheet_cnt := getSheets(ctx) ;
      for s in 0.. sheet_cnt-1 loop --多個excel sheet
        cell.sheet := s;
        row_cnt := getrows(ctx, s) ;
        col_cnt := getColumns(ctx, s) ;
        for r in 0..row_cnt-1 loop --sheet中抓每一行
          cell.row_idx :=r ;
          for c in 0..col_cnt-1 loop --每行的每一列
            cell.col_idx := c;
            cell.data := getvalue(ctx,s, r,c) ;
            pipe row (cell);
          end loop;
        end loop;
      end loop;
      closeContext(ctx) ;
      return;
      
     exception
        when NO_DATA_NEEDED then
            utl_excel.closeContext(ctx) ;
    end get_xls_val;


    procedure get_xls_row_val(p_row in out xls_row, ctx ctxHandle, sheet pls_integer,row_idx pls_integer, maxcol pls_integer) is
      v_cellval celltype ;
    begin
      for c in 0..maxcol-1 loop
        v_cellval := getvalue(ctx, sheet, row_idx, c) ;
        case
          when c=0 then p_row.col0 := v_cellval ;
          when c=1 then p_row.col1 := v_cellval ;
          when c=2 then p_row.col2 := v_cellval ;
          when c=3 then p_row.col3 := v_cellval ;
          when c=4 then p_row.col4 := v_cellval ;
          when c=5 then p_row.col5 := v_cellval ;
          when c=6 then p_row.col6 := v_cellval ;
          when c=7 then p_row.col7 := v_cellval ;
          when c=8 then p_row.col8 := v_cellval ;
          when c=9 then p_row.col9 := v_cellval ;
          when c=10 then p_row.col10 := v_cellval ;
          when c=11 then p_row.col11 := v_cellval ;
          when c=12 then p_row.col12 := v_cellval ;
          when c=13 then p_row.col13 := v_cellval ;
          when c=14 then p_row.col14 := v_cellval ;
          when c=15 then p_row.col15 := v_cellval ;
          when c=16 then p_row.col16 := v_cellval ;
          when c=17 then p_row.col17 := v_cellval ;
          when c=18 then p_row.col18 := v_cellval ;
          when c=19 then p_row.col19 := v_cellval ;
        end case;
      end loop;
    end ;




    function get_xls_table(p_data in blob, p_sheet int) RETURN xls_table PIPELINED
    is
    ctx utl_excel.ctxHandle ;
    r int ;
    row_cnt pls_integer;
    col_cnt pls_integer;
    sheet_cnt pls_integer ;
    vrow xls_row ;
    begin
      
      ctx := utl_excel.newcontext(p_data) ;
      sheet_cnt := getSheets(ctx) ;
      
      <<loop_sheet>>
      for s in 0.. sheet_cnt-1 loop --多個excel sheet
        --?
        if (p_sheet = -1 or p_sheet = s) then
        

          vrow.sheet := s ;
          
          row_cnt := getrows(ctx, s) ;
          
          col_cnt := getColumns(ctx, s) ;
          if col_cnt >c_max_cols then
            col_cnt := c_max_cols ;
          end if ;
          
          <<loop_rows>>
          for r in 0..row_cnt-1 loop --抓每一行數據
            vrow.row_idx := r;
            get_xls_row_val(vrow, ctx, s, r, col_cnt ) ;
            pipe row (vrow);
          end loop loop_rows;
        end if ;
        
      end loop loop_sheet;

      utl_excel.closeContext(ctx) ;
      
      exception
        when NO_DATA_NEEDED then
            utl_excel.closeContext(ctx) ;
        when excel_parsing_error then
            utl_excel.closeContext(ctx) ;
            raise_application_error(-20013, 'open excel error');
    end get_xls_table;




    END utl_excel;
    /

java 代碼 Excelobj.java

    create or replace and compile java source named "corp/excel/Excelobj" as
    package corp.excel;
    //import sqlj.runtime.ref.DefaultContext;
    //import sqlj.runtime.ConnectionContext;
    import oracle.CartridgeServices.ContextManager;
    import jxl.Workbook;
    import jxl.Sheet;
    import jxl.Cell;

    import java.io.IOException ;
    import java.sql.SQLException;
    import oracle.CartridgeServices.CountException ;
    import jxl.read.biff.BiffException ;
    import oracle.CartridgeServices.InvalidKeyException ;

    /**************
    2014-02-14 重新封裝jxl.Workbook
    ***************/
    public class Excelobj
    {
      public static int newcontext(oracle.sql.BLOB data)
      throws SQLException, CountException, IOException, BiffException
      {
        Workbook xls = Workbook.getWorkbook( data.getBinaryStream() ) ;
        return ContextManager.setContext(xls) ;
      }

      public static int newcontext(oracle.sql.BFILE data)
      throws SQLException, CountException ,IOException, BiffException
      {
        int r ;
        Workbook xls = Workbook.getWorkbook( data.getBinaryStream() ) ;
        return ContextManager.setContext(xls) ;
      }


     public static int getSheets(int ctx )
      throws SQLException, CountException, InvalidKeyException
      {
        Workbook xls = (Workbook) ContextManager.getContext(ctx) ;
        return xls.getNumberOfSheets();
      }



     public static int getRows(int ctx, int sheet)
      throws SQLException, CountException, InvalidKeyException
      {
        Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
        Sheet xls_sheet = xls.getSheet(sheet) ;
        return xls_sheet.getRows();
      }

     public static int getColumns(int ctx, int sheet)
      throws SQLException, CountException, InvalidKeyException
      {
        Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
        Sheet xls_sheet = xls.getSheet(sheet);
        return xls_sheet.getColumns() ;
      }


      public static String gettext(int ctx, int sheet, int row, int col)
      throws SQLException, CountException, InvalidKeyException
      {
        Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
        Sheet xls_sheet = xls.getSheet(sheet);
        Cell vcell = xls_sheet.getCell(col,row) ;
        return vcell.getContents();
      }


      public static int getcnt(int ctx)
      throws SQLException, CountException, InvalidKeyException
      {
        return ContextManager.count ;
      }


      public static int closecontext(int ctx)
      throws SQLException, CountException, InvalidKeyException
      {
        Workbook xls = (Workbook) ContextManager.clearContext(ctx) ;
        xls.close() ;
        xls = null;

        return ContextManager.count ;
      }
    }
    /

注意事项:
  1. oracle 10g的jdk是1.4
  jxl版本不能用最新的.  我用的是jxl -2 .6 .jar

2.  装入jar请用 loadjava
  loadjava -u user/pass  -v -f jxl-2.6.jar

3. pb装入excel到blob请用updateblob

4. Excel 2007可以用as_xlsx_read读取,我的思路参考as_xlsx_read后些出来的
  http://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/
  select * from table ( as_read_xlsx . read ( as_read_xlsx . file2blob ( 'DOC' , 'Book1.xlsx' ) ) );


欢迎和大家一起交流oracle技术.

                                                   阿飞
                                              2014/02/21