CREATE OR REPLACE PACKAGE as_read_xlsx IS -- ******************************************************************************************** -- Program : AS_READ_XLSX -- Purpose : Package for Reading data from Excel. -- |------- -------- --------------- --------------------------------------------------------------------------------------- TYPE tp_one_cell IS RECORD ( sheet_nr NUMBER (2), sheet_name VARCHAR (4000), row_nr NUMBER (10), col_nr NUMBER (10), cell VARCHAR2 (100), cell_type VARCHAR2 (1), string_val VARCHAR2 (4000), number_val NUMBER, date_val DATE, formula VARCHAR2 (4000) ); TYPE tp_all_cells IS TABLE OF tp_one_cell; -- FUNCTION READ ( p_xlsx BLOB, p_sheets VARCHAR2 := NULL, p_cell VARCHAR2 := NULL ) RETURN tp_all_cells PIPELINED; -- FUNCTION file2blob (p_dir VARCHAR2, p_file_name VARCHAR2) RETURN BLOB; -- END as_read_xlsx; / CREATE OR REPLACE PACKAGE BODY as_read_xlsx IS -- FUNCTION READ ( p_xlsx BLOB, p_sheets VARCHAR2 := NULL, p_cell VARCHAR2 := NULL ) RETURN tp_all_cells PIPELINED IS t_date1904 BOOLEAN; TYPE tp_date IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER; t_xf_date tp_date; t_numfmt_date tp_date; t_numfmt_text tp_date; TYPE tp_strings IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER; t_strings tp_strings; t_sheet_ids tp_strings; t_sheet_names tp_strings; t_r VARCHAR2 (32767); t_s VARCHAR2 (32767); t_val VARCHAR2 (32767); t_t VARCHAR2 (400); t_nr NUMBER; t_c PLS_INTEGER; t_x PLS_INTEGER; t_xx PLS_INTEGER; t_ns VARCHAR2 (200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'; t_nd DBMS_XMLDOM.domnode; t_nl DBMS_XMLDOM.domnodelist; t_nl2 DBMS_XMLDOM.domnodelist; t_nl3 DBMS_XMLDOM.domnodelist; t_one_cell tp_one_cell; t_ndoc DBMS_XMLDOM.domdocument; -- FUNCTION blob2node (p_blob BLOB) RETURN DBMS_XMLDOM.domnode IS BEGIN IF p_blob IS NULL OR DBMS_LOB.getlength (p_blob) = 0 THEN RETURN NULL; END IF; IF NOT DBMS_XMLDOM.isnull (t_ndoc) THEN DBMS_XMLDOM.freedocument (t_ndoc); END IF; t_ndoc := DBMS_XMLDOM.newdomdocument (XMLTYPE (p_blob, NLS_CHARSET_ID ('AL32UTF8') ) ); RETURN DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc)); EXCEPTION WHEN OTHERS THEN DECLARE t_nd DBMS_XMLDOM.domnode; t_clob CLOB; t_dest_offset INTEGER; t_src_offset INTEGER; t_lang_context NUMBER := DBMS_LOB.default_lang_ctx; t_warning INTEGER; BEGIN DBMS_LOB.createtemporary (t_clob, TRUE, DBMS_LOB.CALL); t_dest_offset := 1; t_src_offset := 1; DBMS_LOB.converttoclob (t_clob, p_blob, DBMS_LOB.lobmaxsize, t_dest_offset, t_src_offset, NLS_CHARSET_ID ('AL32UTF8'), t_lang_context, t_warning ); t_ndoc := DBMS_XMLDOM.newdomdocument (t_clob); t_nd := DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc) ); DBMS_LOB.freetemporary (t_clob); RETURN t_nd; END; END; -- FUNCTION blob2num (p_blob BLOB, p_len INTEGER, p_pos INTEGER) RETURN NUMBER IS BEGIN RETURN UTL_RAW.cast_to_binary_integer (DBMS_LOB.SUBSTR (p_blob, p_len, p_pos ), UTL_RAW.little_endian ); END; -- FUNCTION little_endian (p_big NUMBER, p_bytes PLS_INTEGER := 4) RETURN RAW IS BEGIN RETURN UTL_RAW.SUBSTR (UTL_RAW.cast_from_binary_integer (p_big, UTL_RAW.little_endian ), 1, p_bytes ); END; -- FUNCTION col_alfan (p_col VARCHAR2) RETURN PLS_INTEGER IS BEGIN RETURN ASCII (SUBSTR (p_col, -1)) - 64 + NVL ((ASCII (SUBSTR (p_col, -2, 1)) - 64) * 26, 0) + NVL ((ASCII (SUBSTR (p_col, -3, 1)) - 64) * 676, 0); END; -- FUNCTION get_file (p_zipped_blob BLOB, p_file_name VARCHAR2) RETURN BLOB IS t_tmp BLOB; t_ind INTEGER; t_hd_ind INTEGER; t_fl_ind INTEGER; t_encoding VARCHAR2 (10); t_len INTEGER; BEGIN t_ind := DBMS_LOB.getlength (p_zipped_blob) - 21; LOOP EXIT WHEN t_ind < 1 OR DBMS_LOB.SUBSTR (p_zipped_blob, 4, t_ind) = HEXTORAW ('504B0506'); -- End of central directory signature t_ind := t_ind - 1; END LOOP; -- IF t_ind <= 0 THEN RETURN NULL; END IF; -- t_hd_ind := blob2num (p_zipped_blob, 4, t_ind + 16) + 1; FOR i IN 1 .. blob2num (p_zipped_blob, 2, t_ind + 8) LOOP IF UTL_RAW.bit_and (DBMS_LOB.SUBSTR (p_zipped_blob, 1, t_hd_ind + 9 ), HEXTORAW ('08') ) = HEXTORAW ('08') THEN t_encoding := 'AL32UTF8'; -- utf8 ELSE t_encoding := 'US8PC437'; -- IBM codepage 437 END IF; IF p_file_name = UTL_I18N.raw_to_char (DBMS_LOB.SUBSTR (p_zipped_blob, blob2num (p_zipped_blob, 2, t_hd_ind + 28 ), t_hd_ind + 46 ), t_encoding ) THEN t_len := blob2num (p_zipped_blob, 4, t_hd_ind + 24); -- uncompressed length IF t_len = 0 THEN IF SUBSTR (p_file_name, -1) IN ('/', '\') THEN -- directory/folder RETURN NULL; ELSE -- empty file RETURN EMPTY_BLOB (); END IF; END IF; -- IF DBMS_LOB.SUBSTR (p_zipped_blob, 2, t_hd_ind + 10) = HEXTORAW ('0800') -- deflate THEN t_fl_ind := blob2num (p_zipped_blob, 4, t_hd_ind + 42); t_tmp := HEXTORAW ('1F8B0800000000000003'); -- gzip header DBMS_LOB.COPY (t_tmp, p_zipped_blob, blob2num (p_zipped_blob, 4, t_hd_ind + 20), 11, t_fl_ind + 31 + blob2num (p_zipped_blob, 2, t_fl_ind + 27) -- File name length + blob2num (p_zipped_blob, 2, t_fl_ind + 29) -- Extra field length ); DBMS_LOB.append (t_tmp, UTL_RAW.CONCAT (DBMS_LOB.SUBSTR (p_zipped_blob, 4, t_hd_ind + 16 ) -- CRC32 , little_endian (t_len) -- uncompressed length ) ); RETURN UTL_COMPRESS.lz_uncompress (t_tmp); END IF; -- IF DBMS_LOB.SUBSTR (p_zipped_blob, 2, t_hd_ind + 10) = HEXTORAW ('0000') -- The file is stored (no compression) THEN t_fl_ind := blob2num (p_zipped_blob, 4, t_hd_ind + 42); DBMS_LOB.createtemporary (t_tmp, TRUE, DBMS_LOB.CALL); DBMS_LOB.COPY (t_tmp, p_zipped_blob, t_len, 1, t_fl_ind + 31 + blob2num (p_zipped_blob, 2, t_fl_ind + 27) -- File name length + blob2num (p_zipped_blob, 2, t_fl_ind + 29) -- Extra field length ); RETURN t_tmp; END IF; END IF; t_hd_ind := t_hd_ind + 46 + blob2num (p_zipped_blob, 2, t_hd_ind + 28) -- File name length + blob2num (p_zipped_blob, 2, t_hd_ind + 30) -- Extra field length + blob2num (p_zipped_blob, 2, t_hd_ind + 32); -- File comment length END LOOP; -- RETURN NULL; END; -- BEGIN t_one_cell.cell_type := 'S'; t_one_cell.sheet_name := 'This doesn''t look like an Excel (xlsx) file to me!'; t_one_cell.string_val := t_one_cell.sheet_name; IF DBMS_LOB.SUBSTR (p_xlsx, 4, 1) != HEXTORAW ('504B0304') THEN PIPE ROW (t_one_cell); RETURN; END IF; t_nd := blob2node (get_file (p_xlsx, 'xl/workbook.xml')); IF DBMS_XMLDOM.isnull (t_nd) THEN PIPE ROW (t_one_cell); RETURN; END IF; t_date1904 := LOWER (DBMS_XSLPROCESSOR.valueof (t_nd, '/workbook/workbookPr/@date1904', t_ns ) ) IN ('true', '1'); t_nl := DBMS_XSLPROCESSOR.selectnodes (t_nd, '/workbook/sheets/sheet', t_ns); FOR i IN 0 .. DBMS_XMLDOM.getlength (t_nl) - 1 LOOP t_sheet_ids (i + 1) := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '@r:id', 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"' ); t_sheet_names (i + 1) := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '@name'); END LOOP; DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd)); t_nd := blob2node (get_file (p_xlsx, 'xl/styles.xml')); t_nl := DBMS_XSLPROCESSOR.selectnodes (t_nd, '/styleSheet/numFmts/numFmt', t_ns ); FOR i IN 0 .. DBMS_XMLDOM.getlength (t_nl) - 1 LOOP t_val := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '@formatCode' ); IF ( INSTR (t_val, 'dd') > 0 OR INSTR (t_val, 'mm') > 0 OR INSTR (t_val, 'yy') > 0 ) THEN t_numfmt_date (DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '@numFmtId' ) ) := TRUE; END IF; END LOOP; t_numfmt_date (14) := TRUE; t_numfmt_date (15) := TRUE; t_numfmt_date (16) := TRUE; t_numfmt_date (17) := TRUE; t_numfmt_date (22) := TRUE; t_nl := DBMS_XSLPROCESSOR.selectnodes (t_nd, '/styleSheet/cellXfs/xf/@numFmtId', t_ns ); FOR i IN 0 .. DBMS_XMLDOM.getlength (t_nl) - 1 LOOP t_val := DBMS_XMLDOM.getnodevalue (DBMS_XMLDOM.item (t_nl, i)); t_xf_date (i) := t_numfmt_date.EXISTS (t_val); t_numfmt_text (i) := t_val = '49'; END LOOP; DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd)); t_nd := blob2node (get_file (p_xlsx, 'xl/sharedStrings.xml')); IF NOT DBMS_XMLDOM.isnull (t_nd) THEN t_x := 0; t_xx := 5000; LOOP t_nl := DBMS_XSLPROCESSOR.selectnodes (t_nd, '/sst/si[position()>="' || TO_CHAR (t_x * t_xx + 1) || '" and position()<=" ' || TO_CHAR ((t_x + 1) * t_xx) || '"]', t_ns ); EXIT WHEN DBMS_XMLDOM.getlength (t_nl) = 0; t_x := t_x + 1; FOR i IN 0 .. DBMS_XMLDOM.getlength (t_nl) - 1 LOOP t_c := t_strings.COUNT; t_strings (t_c) := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '.'); IF t_strings (t_c) IS NULL THEN t_strings (t_c) := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '*/text()' ); IF t_strings (t_c) IS NULL THEN t_nl2 := DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.item (t_nl, i), 'r/t/text()' ); FOR j IN 0 .. DBMS_XMLDOM.getlength (t_nl2) - 1 LOOP t_strings (t_c) := t_strings (t_c) || DBMS_XMLDOM.getnodevalue (DBMS_XMLDOM.item (t_nl2, j ) ); END LOOP; END IF; END IF; END LOOP; END LOOP; END IF; DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd)); t_nd := blob2node (get_file (p_xlsx, 'xl/_rels/workbook.xml.rels')); FOR i IN 1 .. t_sheet_ids.COUNT LOOP t_sheet_ids (i) := DBMS_XSLPROCESSOR.valueof (t_nd, '/Relationships/Relationship[@Id="' || t_sheet_ids (i) || '"]/@Target', 'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"' ); IF SUBSTR (t_sheet_ids (i), 1, 4) = '/xl/' THEN -- thanks Lilian Arnaud t_sheet_ids (i) := SUBSTR (t_sheet_ids (i), 5); END IF; END LOOP; DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd)); FOR i IN 1 .. t_sheet_ids.COUNT LOOP IF ( p_sheets IS NULL OR INSTR (':' || p_sheets || ':', ':' || TO_CHAR (i) || ':') > 0 OR INSTR (':' || p_sheets || ':', ':' || t_sheet_names (i) || ':') > 0 ) THEN t_one_cell.sheet_nr := i; t_one_cell.sheet_name := t_sheet_names (i); t_nd := blob2node (get_file (p_xlsx, 'xl/' || t_sheet_ids (i))); t_nl3 := DBMS_XSLPROCESSOR.selectnodes (t_nd, '/worksheet/sheetData/row' ); FOR r IN 0 .. DBMS_XMLDOM.getlength (t_nl3) - 1 LOOP t_nl2 := DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.item (t_nl3, r), 'c' ); FOR j IN 0 .. DBMS_XMLDOM.getlength (t_nl2) - 1 LOOP t_one_cell.date_val := NULL; t_one_cell.number_val := NULL; t_one_cell.string_val := NULL; t_r := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j), '@r', t_ns ); t_val := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j), 'v' ); -- see Changelog 2013-02-19 formula column t_one_cell.formula := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j), 'f' ); -- see Changelog 2013-02-18 type='str' t_t := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j), '@t' ); IF t_t IN ('str', 'inlineStr', 'e') THEN t_one_cell.cell_type := 'S'; t_one_cell.string_val := t_val; ELSIF t_t = 's' THEN t_one_cell.cell_type := 'S'; IF t_val IS NOT NULL THEN t_one_cell.string_val := t_strings (TO_NUMBER (t_val)); END IF; ELSE t_s := DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j), '@s' ); t_nr := TO_NUMBER (t_val, CASE WHEN INSTR (t_val, 'E') = 0 THEN TRANSLATE (t_val, '.012345678,-+', 'D999999999' ) ELSE TRANSLATE (SUBSTR (t_val, 1, INSTR (t_val, 'E') - 1 ), '.012345678,-+', 'D999999999' ) || 'EEEE' END, 'NLS_NUMERIC_CHARACTERS=.,' ); IF t_s IS NOT NULL AND t_xf_date.EXISTS (TO_NUMBER (t_s)) AND t_xf_date (TO_NUMBER (t_s)) THEN t_one_cell.cell_type := 'D'; IF t_date1904 THEN t_one_cell.date_val := TO_DATE ('01-01-1904', 'DD-MM-YYYY') + t_nr; ELSE t_one_cell.date_val := TO_DATE ('01-03-1900', 'DD-MM-YYYY') + (t_nr - 61); END IF; ELSE IF t_s IS NOT NULL AND t_numfmt_text.EXISTS (TO_NUMBER (t_s)) AND t_numfmt_text (TO_NUMBER (t_s)) THEN t_one_cell.cell_type := 'S'; t_one_cell.string_val := t_val; ELSE t_one_cell.cell_type := 'N'; t_nr := ROUND (t_nr, 14 - SUBSTR (TO_CHAR (t_nr, 'TME'), -3) ); t_one_cell.number_val := t_nr; END IF; END IF; END IF; t_one_cell.row_nr := LTRIM (t_r, RTRIM (t_r, '0123456789')); t_one_cell.col_nr := col_alfan (RTRIM (t_r, '0123456789')); t_one_cell.cell := t_r; IF p_cell IS NULL OR t_r = UPPER (p_cell) THEN PIPE ROW (t_one_cell); END IF; END LOOP; END LOOP; DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd)); END IF; END LOOP; DBMS_XMLDOM.freedocument (t_ndoc); t_xf_date.DELETE; t_numfmt_date.DELETE; t_strings.DELETE; t_sheet_ids.DELETE; t_sheet_names.DELETE; RETURN; END; -- FUNCTION file2blob (p_dir VARCHAR2, p_file_name VARCHAR2) RETURN BLOB IS file_lob BFILE; file_blob BLOB; BEGIN file_lob := BFILENAME (p_dir, p_file_name); DBMS_LOB.OPEN (file_lob, DBMS_LOB.file_readonly); DBMS_LOB.createtemporary (file_blob, TRUE, DBMS_LOB.CALL); DBMS_LOB.loadfromfile (file_blob, file_lob, DBMS_LOB.lobmaxsize); DBMS_LOB.CLOSE (file_lob); RETURN file_blob; EXCEPTION WHEN OTHERS THEN IF DBMS_LOB.ISOPEN (file_lob) = 1 THEN DBMS_LOB.CLOSE (file_lob); END IF; IF DBMS_LOB.istemporary (file_blob) = 1 THEN DBMS_LOB.freetemporary (file_blob); END IF; RAISE; END; -- END as_read_xlsx;