create or replace package ploffx_xls_parser
authid current_user is

/**
<config>
  <pck-name>PL/OFFX XLS Parser</pck-name>
  <pck-title>XLS Parser</pck-title>
  <pck-version>v5.9</pck-version>
  <header-prc>plsqlplus_doc_xml_header</header-prc>
  <header-size>10</header-size>
  <footer-prc>plsqlplus_doc_xml_footer</footer-prc>
  <footer-size>10</footer-size>
 </config>
*/

/**
<h1>Introduction</h1>
<br/>
<p>
XLS Parser is a PL/SQL based binary file parser and data extractor.
Use Parser to quickly and easily extract data from Microsoft Office files.
</p>
<br/>
<h2>Installation</h2>
<p>
System requirements: Oracle 10g RDBMS Release 2 or higher. Oracle 10g Express Edition is supported.
</p>
<p>
XLS Parser is a collection of PL/SQL packages. "install.sql" creates all required objects.
</p>
<br/>
<h2>Using Parser</h2>
<p>
Essentially,XLS Parser is a series of calls to Parser procedures and functions from a PL/SQL script, package, procedure or function.
If you are not familiar with the PL/SQL programming language, study the Oracle documentation.
</p>
<br/>
<h2>Contact</h2>
<p>
Contact us at: <a>mailto:info@plpdf.com</a>
</p>
<newpage/>
*/

/**
<h1>Types</h1>
<h2>t_cell</h2>
<p>t_cell record contains information about an XLS cell data.</p>
<p>rowspan: Row span number</p>
<p>colspan: Column span number</p>
<p>data_type: data tpye of cell: text, number, date or boolean</p>
<p>cell_text: if data_type is text then it containts data of the cell. If data_type is number, date or boolean then containts converted data.</p>
<p>cell_number: if data_type is number then it containts data of the cell</p>
<p>cell_date: if data_type is date then it containts data of the cell</p>
<p>cell_boolean: if data_type is boolean then it containts data of the cell</p>
<p>format_string: XLS format string. Uses if data_type is number or date</p>
<br/>

<h2>t_sheet</h2>
<p>Containts information about an XLS Sheet and Cells of this Sheet</p>
<p>name: Name of the sheet</p>
<p>maxRow: maximun index of Rows</p>
<p>maxCol: maximum index of Columns</p>
<p>numRows: number of Rows</p>
<p>numCols: number of Columns</p>
<p>cells: cells of the sheet, indexed by row and column</p>
<br/>

<h2>t_sheets</h2>
<p>Containts Sheets of the XLS file</p>
<br/>
<newpage/>
*/
type t_cell is record(
  rowspan       number,
  colspan       number,
  data_type     plx_sc.normalv2,
  cell_text     plx_sc.maxv2,
  cell_number   number,
  cell_date     date,
  cell_boolean  boolean,
  cell_id       varchar2(7),
  format_string plx_sc.normalv2
  );

type t_cell_arr1 is table of t_cell index by pls_integer;
type cell_arr2 is table of t_cell_arr1 index by pls_integer;

type t_sheet is record(
  name    plx_sc.normalv2,
  state    plx_sc.normalv2, --v4.9.1
  maxRow  number,
  maxCol  number,
  numRows number,
  numCols number,
  cells   cell_arr2
  );

type t_sheets is table of t_sheet index by pls_integer;

/**
 <h1>Procedures</h1>
 <br/>
*/

/**
<name>GetVersion</name>
<type>function</type>
<desc>
Gets the version number of PL/XLS package.
</desc>
<version>v1.0.0</version>
<params>-</params>
<return>
  <return-def>varchar2</return-def>
  <return-desc>number of current version</return-desc>
</return>
*/
function getVersion
  return varchar2;

/**
<name>ParseXLS</name>
<type>function</type>
<desc>
Gets the data from an XLS (binary or XLSX).
</desc>
<version>v1.0.1</version>
<params>
   <param>
   <param-def>p_xls blob</param-def>
   <param-desc>Original binary XLS</param-desc>
  </param>
  <param>
   <param-def>p_DateTextFormat varchar2 default null</param-def>
   <param-desc>Format string. Uses for convert Date cell to cell_text.</param-desc>
  </param>
  <param>
   <param-def>p_NumberTextFormat varchar2 default null</param-def>
   <param-desc>Format string. Uses for convert Number cell to cell_text</param-desc>
  </param>
  <param>
   <param-def>p_DateTextNlsparam varchar2 default null</param-def>
   <param-desc>NLS parameter. Uses for convert Date cell to cell_text</param-desc>
  </param>
  <param>
   <param-def>p_NumberTextNlsparam varchar2 default null</param-def>
   <param-desc>NLS parameter. Uses for convert Number cell to cell_text</param-desc>
  </param>
</params>
<return>
  <return-def>t_sheets</return-def>
  <return-desc>data of the binary XLS</return-desc>
</return>
*/
function ParseXLS(
  p_xls  in out nocopy blob,
  p_DateTextFormat     varchar2 default null,
  p_NumberTextFormat   varchar2 default null,
  p_DateTextNlsparam   varchar2 default null,
  p_NumberTextNlsparam varchar2 default null
  ) return t_sheets;
--
end ploffx_xls_parser;
/

