Oracle pl/sql解析Excel 97/2000文檔,poi版本

之前有使用plsq package 封裝java jxl, 用來解析Microsoft Excel 97/2000文檔.
為對比性能, 又使用apache poi重新封裝了一個,接口一致的版本.

效果圖如下


oracle package poi_hssf,  pl/sql封裝.

CREATE OR REPLACE PACKAGE poi_hssf  authid definer as
/****************************************************************************
   Ver        Date        Author           Description
   ---------  ----------  ---------------  --------------------------------------------
   1.0        2015-04-12   gangjh           1. 使用java  poi hssf  訪問blob的excel 2000數據.
                                               返回table類型數據.

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

  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
  ) ;



/****************************************
 with xt as
(   select a.id,a.name ,xx.*
    from xmldata a,
    table( poi_hssf.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( poi_hssf.get_xls_table(blob_temp) ) xx
    where a.name='MSOFFICE.EXCEL'    and id=23    and sheet=4

ex2:
select * from table(poi_hssf.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 ;

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

  function get_xls_table(p_data in blob, p_sheet int default -1) RETURN xls_table PIPELINED;


  PROCEDURE poitest(vfile varchar2);


END poi_hssf;
/
CREATE OR REPLACE PACKAGE body poi_hssf 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.poi.ExcelUtilWithHSSF.newcontext(oracle.sql.BLOB, int[], java.lang.String[]) return int';

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

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

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


  function getFirstRowNum(ctx IN ctxHandle) return binary_integer
  as LANGUAGE JAVA NAME
   'corp.poi.ExcelUtilWithHSSF.getFirstRowNum(int) return int';

  function getLastRowNum(ctx IN ctxHandle) return binary_integer
  as LANGUAGE JAVA NAME
   'corp.poi.ExcelUtilWithHSSF.getLastRowNum(int) return int';


  function getColumns(ctx IN ctxHandle, prow binary_integer) return binary_integer
  as LANGUAGE JAVA NAME
   'corp.poi.ExcelUtilWithHSSF.getColumns(int, 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.poi.ExcelUtilWithHSSF.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.poi.ExcelUtilWithHSSF.getString(int, int, int) return java.lang.String';

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

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


  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  poi_hssf.ctxHandle ;
  r int ;
  sheet_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) ;

      for r in getFirstRowNum(ctx)..getLastRowNum(ctx) loop --sheet中抓每一行
        col_cnt := getColumns(ctx, r) ;
        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
          poi_hssf.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  poi_hssf.ctxHandle ;
r int ;
col_cnt pls_integer;
sheet_cnt pls_integer ;
vrow  xls_row ;
v_ret binary_integer;
begin

  ctx := poi_hssf.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


      v_ret := selectSheet(ctx, s) ;

      <<loop_rows>>
      for r in getFirstRowNum(ctx)..getLastRowNum(ctx) loop -- 抓每一行數據
        vrow := null; -- 需先清空數據,可能不同sheet資料列不一樣多
        vrow.sheet := s + 1 ;

        col_cnt := least(getColumns(ctx, r), c_max_cols) ;
        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;

  poi_hssf.closeContext(ctx) ;

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




  PROCEDURE poitest(vfile varchar2)
  as LANGUAGE JAVA NAME
   'ExcelUtilWithHSSF.runtest(java.lang.String)';


END poi_hssf;
/

java code

create or replace and compile java source named "corp/poi/ExcelUtilWithHSSF" as
package corp.poi;
import oracle.CartridgeServices.ContextManager;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import java.sql.SQLException;
import java.io.Writer;
import java.io.StringWriter;
import java.io.PrintWriter;
import oracle.CartridgeServices.CountException ;
import oracle.CartridgeServices.InvalidKeyException ;
import java.util.Date;
import java.text.SimpleDateFormat;

/*********************
oracle 10g可以使用poi version: poi-3.2-FINAL-20081019.jar /   poi-bin-3.2-FINAL-20081019.zip
**********************/

public class ExcelReaderObj
{
   public HSSFWorkbook xls =null;
   public HSSFSheet active_sheet =null;
   public HSSFFormulaEvaluator evaluator=null;
}

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

  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 ;

    try {
        ExcelReaderObj xlsobj = new ExcelReaderObj();
      //1.得到Excel工作簿象
         xlsobj.xls = new org.apache.poi.hssf.usermodel.HSSFWorkbook(data);
         xlsobj.evaluator = new HSSFFormulaEvaluator(xlsobj.xls);
        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 = null;
    return ContextManager.count ;
  }


  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.getSheetAt(sheet);
    return 0 ;
  }


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


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




 public static int getColumns(int ctx, int prow)
  throws SQLException, CountException, InvalidKeyException
  {
    ExcelReaderObj  xlsobj = (ExcelReaderObj)ContextManager.getContext(ctx) ;
    HSSFRow row = xlsobj.active_sheet.getRow(prow);
    if (row == null) return 0;

    return row.getLastCellNum();
  }

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

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

        byte v_bytes[] = str.substring(0, len).getBytes() ;
        if (v_bytes.length <= 4000) return str;

//            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.endsWith(".0")) return s.substring(0, s.length()-2); //截斷 .0
       return s;
    }

  public static String getCellValue(HSSFFormulaEvaluator evaluator, HSSFCell cell) {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case HSSFCell.CELL_TYPE_FORMULA:
            return getCellValue(evaluator, evaluator.evaluateInCell(cell));
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date theDate = cell.getDateCellValue();
                return new SimpleDateFormat().format(theDate);
            } else {
                return getDoubleString(cell.getNumericCellValue());
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();
        case HSSFCell.CELL_TYPE_BLANK:
        case HSSFCell.CELL_TYPE_ERROR:
        default:
            return null;
        }
  }


/*****************************************
    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) ;
    HSSFRow row1 = xlsobj.active_sheet.getRow(row);
    HSSFCell cell1 = row1.getCell(col);
    if (cell1 ==null) return null;

    return getOracleString(getCellValue(xlsobj.evaluator, cell1));
  }
}
/

1.
在oracle 10g的jre版本為1.4.2
poi版本最高可以使用poi-3.2-FINAL-20081019.jar,
如在oracle11g,12c中使用,可以使用較高的poi版本.

2.  歡迎大家使用,如發現有bug,請email告知.

阿飛
2015/09/08 整理