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  authid definer 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(4000 byte) ; -- limit for oracle 10g

  SUBTYPE error_code_type IS BINARY_INTEGER;
  SUBTYPE error_message_type IS VARCHAR2(1000);

  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 ,
    col_a  celltype,
    col_b  celltype,
    col_c  celltype,
    col_d  celltype,
    col_e  celltype,
    col_f  celltype,
    col_g  celltype,
    col_h  celltype,
    col_i  celltype,
    col_j  celltype,
    col_k  celltype,
    col_l  celltype,
    col_m  celltype,
    col_n  celltype,
    col_o  celltype,
    col_p  celltype,
    col_q  celltype,
    col_r  celltype,
    col_s  celltype,
    col_t  celltype
  );

  type xls_table is table of xls_row ;


PROCEDURE  raise_error_if_failure
  (
    error_code    ERROR_CODE_TYPE,
    error_message ERROR_MESSAGE_TYPE
  ) ;


 function number_to_date(v number) return date ;

/****************************************
 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_table2 PIPELINED ;
  PRAGMA RESTRICT_REFERENCES(get_xls_table, WNDS, TRUST);*/

  function get_xls_table(p_data in blob, p_sheet int default -1 ) RETURN xls_table PIPELINED ;
/*
  PRAGMA RESTRICT_REFERENCES(xls_table, WNDS, TRUST);
*/


END utl_excel;


/
CREATE OR REPLACE PACKAGE body utl_excel AS
  excel_parsing_error   EXCEPTION;
    pragma          EXCEPTION_INIT(excel_parsing_error, -29532);

  SUCCESS CONSTANT BINARY_INTEGER := 0;


/**************** java implement ****************************/
  FUNCTION newContext_java(data IN blob, error_code OUT ERROR_CODE_TYPE, error_message OUT error_message_type) RETURN ctxHandle
  as LANGUAGE JAVA NAME
   'corp.excel.ExcelReader.newcontext(oracle.sql.BLOB, int[], java.lang.String[]) return int';

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

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

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


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

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

 -- for cell data length >4000 byte
  procedure  getClob(ctx IN ctxHandle, p_row in binary_integer, p_col in binary_integer, p_clob in out nocopy clob)
  as LANGUAGE JAVA NAME
   'corp.excel.ExcelReader.getClob(int,  int, int, oracle.sql.CLOB[])';

--  Oracle has a limit of 4000 bytes for a VARCHAR2, >4000 will by trim
  FUNCTION getString(ctx IN ctxHandle, p_row in binary_integer, p_col in binary_integer) RETURN varchar2
  as LANGUAGE JAVA NAME
   'corp.excel.ExcelReader.getString(int, int, int) return java.lang.String';

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

/*********************************************/
 function number_to_date(v number) return date is
 begin
   --2個閏年 ?? --excel中Date返回的是number
   return to_date('190001','YYYYMM') + v - 2 ;
 end ;

  PROCEDURE  raise_error_if_failure(error_code    ERROR_CODE_TYPE,
                                    error_message ERROR_MESSAGE_TYPE)
  IS
  BEGIN
    IF error_code <> SUCCESS THEN
      raise_application_error(error_code, error_message);
    END IF;
  END;

  FUNCTION newContext(data IN blob) RETURN ctxHandle
  IS
   error_code    ERROR_CODE_TYPE;
   error_message ERROR_MESSAGE_TYPE;
   v_ctx ctxHandle;
  BEGIN
    v_ctx := newContext_java(data, error_code, error_message);
    raise_error_if_failure(error_code, error_message);
    return v_ctx;
  end ;


  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 ;
  v_ret binary_integer;
  begin
    ctx := newcontext(data) ;

    sheet_cnt := getSheets(ctx) ;
    for s in 0.. sheet_cnt-1 loop --多個excel sheet
      cell.sheet := s;
      v_ret := selectSheet(ctx, s) ;

      row_cnt := getrows(ctx) ;
      col_cnt := getColumns(ctx) ;
      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 := getString(ctx, 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, row_idx pls_integer, maxcol pls_integer) is
  v_cellval  celltype ;
  procedure fill_cell(v_cellval celltype, c pls_integer) is
  begin
    case
      when c=0 then p_row.col_a := v_cellval ;
      when c=1 then p_row.col_b := v_cellval ;
      when c=2 then p_row.col_c := v_cellval ;
      when c=3 then p_row.col_d := v_cellval ;
      when c=4 then p_row.col_e := v_cellval ;
      when c=5 then p_row.col_f := v_cellval ;
      when c=6 then p_row.col_g := v_cellval ;
      when c=7 then p_row.col_h := v_cellval ;
      when c=8 then p_row.col_i := v_cellval ;
      when c=9 then p_row.col_j := v_cellval ;
      when c=10 then p_row.col_k := v_cellval ;
      when c=11 then p_row.col_l := v_cellval ;
      when c=12 then p_row.col_m := v_cellval ;
      when c=13 then p_row.col_n := v_cellval ;
      when c=14 then p_row.col_o := v_cellval ;
      when c=15 then p_row.col_p := v_cellval ;
      when c=16 then p_row.col_q := v_cellval ;
      when c=17 then p_row.col_r := v_cellval ;
      when c=18 then p_row.col_s := v_cellval ;
      when c=19 then p_row.col_t := v_cellval ;
    end case;
  end;
begin
  for c in 0..maxcol-1 loop
    v_cellval := getString(ctx, row_idx, c) ;
    fill_cell(v_cellval, c) ;
  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 ;
v_ret binary_integer;
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 := null; -- 需先清空數據,可能不同sheet資料列不一樣多
      vrow.sheet := s + 1 ;

      v_ret := selectSheet(ctx, s) ;
      row_cnt := getrows(ctx) ;
      col_cnt := getColumns(ctx) ;

      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 + 1;
        get_xls_row_val(vrow, ctx, 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'||sqlerrm);
end get_xls_table;



END utl_excel;
/

java 代碼 Excelobj.java

create or replace and compile java source named "corp/excel/ExcelReader" as
package corp.excel;
import oracle.CartridgeServices.ContextManager;
import java.util.Date;
import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;
import jxl.NumberCell;
import jxl.DateCell;
import jxl.CellType;
import java.io.IOException ;
import java.io.Writer;
import java.io.StringWriter;
import java.io.PrintWriter;
import java.sql.SQLException;
import oracle.CartridgeServices.CountException ;
import jxl.read.biff.BiffException ;
import oracle.CartridgeServices.InvalidKeyException ;
import java.text.SimpleDateFormat;
import java.text.DateFormat;
import java.util.Calendar;
import java.math.BigDecimal;
import java.util.HashSet;

/**************
2014-04-01 重新封裝jxl.Workbook

 int[] errorCode,  errorCode=0 沒有錯誤,
 String[] errorMessage  錯誤信息
***************/

public class ExcelReaderObj
{
   public jxl.Workbook xls =null;
   public jxl.Sheet active_sheet =null;
}



public class ExcelReader
{
  public static final int SUCCESS = 0;
  public static final int FAILURE = -20013;

  public static final String[] DateFormat = new String[] {
         "yyyy/mm/dd",
         "YYYYMMDD",
         "M\\月D\\日",
         "YYYY/M/D",
         "m\"月\"d\"日\""
         };

  public static HashSet xlsDateFormat = new HashSet();

  private static boolean contains(String[] array, String key)
  {
    java.util.Arrays.sort(array);
    return java.util.Arrays.binarySearch(array, key) >= 0;
  }

  private static int SQLfailure(SQLException e, int[] errorCode, String[] errorMessage)
  {
      errorCode[0] = e.getErrorCode();
      errorMessage[0] = e.getMessage();
      return FAILURE;
  }

  private static String ExceptionString(Exception e)
  {
      Writer writer = new StringWriter();
      PrintWriter printWriter = new PrintWriter(writer);
      e.printStackTrace(printWriter);
      return writer.toString();
  }

  private static int Failure(Exception e, int[] errorCode, String[] errorMessage)
  {
      errorCode[0] = -20013 ;
      errorMessage[0] = ExceptionString(e) ;
      return FAILURE;
  }

  public static int newcontext(java.io.InputStream data, int[] errorCode, String[] errorMessage)
  {
    errorCode[0] = SUCCESS ;

    for (int i=0; i< DateFormat.length; i++) {
       xlsDateFormat.add(DateFormat[i]);
    }

    try {
        ExcelReaderObj xlsobj = new ExcelReaderObj();
        xlsobj.xls = Workbook.getWorkbook( data ) ;
        return ContextManager.setContext(xlsobj) ;
    } catch (Exception e) {
      return Failure(e, errorCode, errorMessage);
    }
  }


  public static int newcontext(oracle.sql.BLOB data, int[] errorCode, String[] errorMessage)
  {
    try {
       return newcontext( data.getBinaryStream(), errorCode, errorMessage);
    } catch (SQLException e) {
      return SQLfailure(e, errorCode, errorMessage);
    }
  }

  public static int newcontext(oracle.sql.BFILE data, int[] errorCode, String[] errorMessage)
  {
    try {
        return newcontext( data.getBinaryStream(), errorCode, errorMessage);
    } catch (SQLException e) {
      return SQLfailure(e, errorCode, errorMessage);
    }
  }

  public static int closecontext(int ctx)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj) ContextManager.clearContext(ctx) ;
    xlsobj.xls.close() ;
    xlsobj = null;
    return ContextManager.count ;
  }


  public static int getProper(int ctx, String key, int[] v_int, String[] v_String)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj) ContextManager.getContext(ctx) ;

    v_int[0] = 0;
    v_String[0] = "";

    if (key.equals("Sheets"))
    {
      v_int[0] = xlsobj.xls.getNumberOfSheets();
    }
    else if (key.equals("rowCount"))
    {
      v_int[0] = xlsobj.active_sheet.getRows();
    }
    else if (key.equals("columnsCount"))
    {
      v_int[0] = xlsobj.active_sheet.getColumns();
    }
    else if (key.equals("sheetName"))
    {
      v_String[0] = xlsobj.active_sheet.getName();
    }


    return 0;
  }

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

 public static int selectSheet(int ctx, int sheet)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;
    xlsobj.active_sheet = xlsobj.xls.getSheet(sheet);
    return 0 ;
  }

 public static int getRows(int ctx)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;
    return xlsobj.active_sheet.getRows();
  }

 public static int getColumns(int ctx)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;
    return xlsobj.active_sheet.getColumns() ;
  }


  public static void getClob(int ctx, int row, int col, oracle.sql.CLOB[] outval)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;
    Cell vcell = xlsobj.active_sheet.getCell(col,row) ;
    outval[0].setString(1, vcell.getContents()) ;
  }





    private static String dayAddition(int num, String vformat)
    {
      try {
//         vformat = "yyyy-MM-dd"
          SimpleDateFormat timeformat = new SimpleDateFormat(vformat);
          java.util.Date date = timeformat.parse("1900-1-1");
          Calendar a = Calendar.getInstance();
          a.setTime(date);
          a.add(Calendar.DATE, (num-2)); //為什麼要 (num-2) ?
          return timeformat.format(a.getTime());
        } catch (java.text.ParseException e)
        {
          return "Parse Date Error";
        }
    }

    private static String getOracleString(String str)
    {
        if (str.length()<1200) { //不會超過4000 byte
           return str ;
        }

        int len = ( str.length() > 4000 ) ? 4000 : str.length();

        byte v_bytes[] = str.substring(0, len).getBytes() ;
        if (v_bytes.length <= 4000) {
            return str;
        } else
        {
//            byte[] buf = new byte[4000];
//            System.arraycopy(v_bytes, 0, buf, 0, 4000);
            return new String(v_bytes, 0, 4000) ;
        }
    }

    private static String getDoubleString(double var1)
    {
       String s = Double.toString(var1) ;

       if(s.substring(s.length()-2).equals(".0")) //截斷 .0
       {
           return s.substring(0, s.length()-2);
       }else{
           return s;
       }
    }

    private static String getFormat(Cell cell)
    {
       jxl.format.CellFormat  vcf = cell.getCellFormat() ;
       try {
         if (vcf != null) {
            return vcf.getFormat().getFormatString();
         }
       } catch (java.lang.NullPointerException e) {
       }
       return "";
    }



/*****************************************
    Oracle has a limit of 4000 bytes for VARCHAR2
******************************************/
  public static String getString(int ctx, int row, int col)
  throws CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;

    Cell cell = xlsobj.active_sheet.getCell(col,row) ;
    CellType ctype = cell.getType();
    String vformat = getFormat(cell);

    if ( ctype == CellType.LABEL )
    {
        return getOracleString(cell.getContents()) ;
    }
    else if (ctype == CellType.NUMBER || ctype == CellType.NUMBER_FORMULA)
    { //直接getContents, number型會丟失精度

       NumberCell nc = (NumberCell)cell;
/*****
 excel內部存放的是 serial number 型的日期,從1900到現在天數
****/
//     contains(xlsDateFormat, vformat)
       if ((ctype == CellType.NUMBER) && (xlsDateFormat.contains(vformat)))
       {
          return dayAddition((int)nc.getValue(), vformat);
       }

//       return getDoubleString(nc.getValue());
       return cell.getContents();
    }
    else if (ctype == CellType.DATE || ctype == CellType.DATE_FORMULA )
    {
//     vformat =  "yyyy-mm-dd" ;
        DateFormat format1 = new SimpleDateFormat("yyyy/MM/dd");
        return format1.format( ((DateCell)cell).getDate() );
    }
    else
    {
        return getOracleString(cell.getContents()) ;
    }
  }


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



// ???need fix
  public void getValue(int ctx, int row, int col,
  int[] datatype,
  String[] v_String,
  Date[] v_Date
  )
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;

    Cell cell = xlsobj.active_sheet.getCell(col,row) ;
    //直接getContents, number型會丟失精度
    if(cell.getType()==CellType.NUMBER ||cell.getType()==CellType.NUMBER_FORMULA)
    {
       NumberCell nc=(NumberCell)cell;
       String s=Double.toString(nc.getValue()) ;
       v_String[0] = s ;

    } else
    {
        v_String[0] = getOracleString(cell.getContents()) ;
    }

  }


  private static String NumberToDate(long s)
  {
         String rtn= "1900-01-01";
         try{
          SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
          java.util.Date date1 = new java.util.Date();
          date1 = format.parse("1900-01-01");
          long i1 = date1.getTime();

/****************
  ?里要?去2，(s-2) 不然日期?提前2天，具体原因不清楚，
  估?和java??是?1970-01-01?始有?
  而excel里面的?算是?1900-01-01?始
***********/
          i1= i1/1000+( (s-2)*24*3600);
          date1.setTime(i1*1000);
          rtn=format.format(date1);
         }catch(Exception e){
          rtn= "1900-01-01";
         }
         return rtn;

  }

}

注意事项:
  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/24修改
當長度>4000時截斷
                                                   阿飞
                                              2014/02/21