create or replace package ploffx_workbook
authid current_user is
--v5.9
--
-- Namespaces
ns_ssml constant plx_sc.normalv2 := 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
ns_rel constant plx_sc.normalv2 := 'http://schemas.openxmlformats.org/officeDocument/2006/relationships';
ns_rel_pref constant varchar2(1 char) := 'r';
--
c_full_ns constant plx_sc.normalv2 := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
c_filename constant plx_sc.normalv2 := 'xl/workbook.xml';
-- Tag
t_workbook     constant plx_sc.normalv2 := 'workbook';
t_fileVersion  constant plx_sc.normalv2 := 'fileVersion';
t_workbookPr   constant plx_sc.normalv2 := 'workbookPr';
t_bookViews    constant plx_sc.normalv2 := 'bookViews';
t_workbookView constant plx_sc.normalv2 := 'workbookView';
t_sheets       constant plx_sc.normalv2 := 'sheets';
t_sheet        constant plx_sc.normalv2 := 'sheet';
t_calcPr       constant plx_sc.normalv2 := 'calcPr';
t_definedName  constant plx_sc.normalv2 := 'definedName';
t_definedNames constant plx_sc.normalv2 := 'definedNames';
-- XML
g_xml plx_sc.type_xdoc;
/******************************************************************/
--Structures
-- CT_DefinedName -> XString
subtype ST_Name is plx_sc.normalv2;
c_filterDatabase constant ST_Name := '_xlnm._FilterDatabase';
-- ST_SheetStat
subtype ST_SheetState is plx_sc.normalv2;
c_visible    constant ST_SheetState := 'visible';
c_hidden     constant ST_SheetState := 'hidden';
c_veryHidden constant ST_SheetState := 'veryHidden';
-- ST_Objects
subtype ST_Objects is plx_sc.normalv2;
c_all constant ST_Objects := 'all';
c_none constant ST_Objects := 'none';
c_placeholders constant ST_Objects := 'placeholders';
-- ST_UpdateLinks
subtype ST_UpdateLinks is plx_sc.normalv2;
c_always constant ST_UpdateLinks := 'always';
c_never constant ST_UpdateLinks := 'never';
c_userSet constant ST_UpdateLinks := 'userSet';
-- ST_Visibility
subtype ST_Visibility is plx_sc.normalv2;
--c_hidden constant ST_Visibility := 'hidden';  --multiple
--c_veryHidden constant ST_Visibility := 'veryHidden'; --multiple
--c_visible constant ST_Visibility := 'visible'; --multiple
-- ST_CalcMode
subtype ST_CalcMode is plx_sc.normalv2;
c_auto constant ST_CalcMode := 'auto';
c_autoNoTable constant ST_CalcMode := 'autoNoTable';
c_manual constant ST_CalcMode := 'manual';
-- ST_RefMode
subtype ST_RefMode is plx_sc.normalv2;
c_A1 constant ST_RefMode := 'A1';
c_R1C1 constant ST_RefMode := 'R1C1';
-- CT_Sheet
type CT_Sheet is record (
  tag plx_sc.normalv2,
  name plx_sc.normalv2, -- xsd:Xstring
  sheetId plx_sc.normalv2, -- xsd:unsignedInt
  state ST_SheetState,
  id plx_sc.normalv2 -- r:id
  );
-- CT_Sheets
type t_sheets_ is table of CT_Sheet;
v_sheets_empty t_sheets_ := t_sheets_();
-- CT_FileVersion
type CT_FileVersion is record(
  tag plx_sc.normalv2,
  appName plx_sc.normalv2, -- xsd:string
  lastEdited plx_sc.normalv2, -- xsd:string
  lowestEdited plx_sc.normalv2, -- xsd:string
  rupBuild plx_sc.normalv2, -- xsd:string
  codeName plx_sc.normalv2 -- ST_Guid
  );
-- CT_BookView
type CT_BookView is record(
  tag plx_sc.normalv2,
  visibility ST_Visibility,
  minimized plx_sc.normalv2, -- xsd:boolean
  showHorizontalScroll plx_sc.normalv2, -- xsd:boolean
  showVerticalScroll plx_sc.normalv2, -- xsd:boolean
  showSheetTabs plx_sc.normalv2, -- xsd:boolean
  xWindow plx_sc.normalv2, -- xsd:int
  yWindow plx_sc.normalv2, -- xsd:int
  windowWidth plx_sc.normalv2, -- xsd:unsignedInt
  windowHeight plx_sc.normalv2, -- xsd:unsignedInt
  tabRatio plx_sc.normalv2, -- xsd:unsignedInt
  firstSheet plx_sc.normalv2, -- xsd:unsignedInt
  activeTab plx_sc.normalv2, -- xsd:unsignedInt
  autoFilterDateGrouping plx_sc.normalv2 -- xsd:boolean
  );
-- CT_BookViews
type t_bookViews_ is table of CT_BookView;
v_bookViews_empty t_bookViews_ := t_bookViews_();
-- CT_CalcPr
type CT_CalcPr is record(
  tag plx_sc.normalv2,
  calcId plx_sc.normalv2, -- xsd:unsignedInt
  calcMode ST_CalcMode,
  fullCalcOnLoad plx_sc.normalv2, -- xsd:boolean
  refMode ST_RefMode,
  iterate plx_sc.normalv2, -- xsd:boolean
  iterateCount plx_sc.normalv2, -- xsd:unsignedInt
  iterateDelta plx_sc.normalv2,
  fullPrecision plx_sc.normalv2, -- xsd:boolean
  calcCompleted plx_sc.normalv2, -- xsd:boolean
  calcOnSave plx_sc.normalv2, -- xsd:boolean
  concurrentCalc plx_sc.normalv2, -- xsd:boolean
  concurrentManualCount plx_sc.normalv2, -- xsd:unsignedInt
  forceFullCalc plx_sc.normalv2 -- xsd:boolean
  );
-- CT_WorkbookPr
type CT_WorkbookPr is record(
  tag plx_sc.normalv2,
  date1904 plx_sc.normalv2, -- xsd:boolean
  showObjects ST_Objects,
  showBorderUnselectedTables plx_sc.normalv2, -- xsd:boolean
  filterPrivacy plx_sc.normalv2, -- xsd:boolean
  promptedSolutions plx_sc.normalv2, -- xsd:boolean
  showInkAnnotation plx_sc.normalv2, -- xsd:boolean
  backupFile plx_sc.normalv2, -- xsd:boolean
  saveExternalLinkValues plx_sc.normalv2, -- xsd:boolean
  updateLinks ST_UpdateLinks,
  codeName plx_sc.normalv2,
  hidePivotFieldList plx_sc.normalv2, -- xsd:boolean
  showPivotChartFilter plx_sc.normalv2, -- xsd:boolean
  allowRefreshQuery plx_sc.normalv2, -- xsd:boolean
  publishItems plx_sc.normalv2, -- xsd:boolean
  checkCompatibility plx_sc.normalv2, -- xsd:boolean
  autoCompressPictures plx_sc.normalv2, -- xsd:boolean
  refreshAllConnections plx_sc.normalv2, -- xsd:boolean
  defaultThemeVersion plx_sc.normalv2 -- xsd:unsignedInt
  );
-- CT_DefinedName
type CT_DefinedName is record (
  tag plx_sc.normalv2,
  name plx_sc.normalv2, -- ST_Xstring
  localSheetId plx_sc.normalv2, -- xsd:unsignedInt
  hidden plx_sc.normalv2, -- xsd:boolean
  val plx_sc.normalv2 -- xml tag value
  );
--
type t_definedName_ is table of CT_DefinedName;
v_definedName_empty t_definedName_ := t_definedName_();
-- CT_DefinedNames
type CT_DefinedNames is record (
  tag plx_sc.normalv2,
  definedName t_definedName_
  );
-- CT_Workbook
type CT_Workbook is record (
  tag plx_sc.normalv2,
  calcPr CT_CalcPr,
  fileVersion CT_FileVersion,
  workbookPr CT_WorkbookPr,
  definedNames CT_DefinedNames,
  bookViews t_bookViews_, -- bookViews
  sheets t_sheets_ --sheet
  );
--
v_workbook_empty CT_Workbook;
/******************************************************************/
-- workbook
procedure startWorkbook(
  p_tag varchar2 default t_workbook
  );
--
procedure endWorkbook(
  p_tag varchar2 default t_workbook
  );
-- fileVersion
procedure fileVersion(
  p_tag varchar2 default t_fileVersion,
  p_appName varchar2 default null,
  p_lastEdited varchar2 default null,
  p_lowestEdited varchar2 default null,
  p_rupBuild varchar2 default null,
  p_codeName varchar2 default null
  );
-- workbookPr
procedure workbookPr(
  p_tag varchar2 default t_workbookPr,
  p_date1904 varchar2 default null,
  p_showObjects varchar2 default null,
  p_showBorderUnselectedTables varchar2 default null,
  p_filterPrivacy varchar2 default null,
  p_promptedSolutions varchar2 default null,
  p_showInkAnnotation varchar2 default null,
  p_backupFile varchar2 default null,
  p_saveExternalLinkValues varchar2 default null,
  p_updateLinks varchar2 default null,
  p_codeName varchar2 default null,
  p_hidePivotFieldList varchar2 default null,
  p_showPivotChartFilter varchar2 default null,
  p_allowRefreshQuery varchar2 default null,
  p_publishItems varchar2 default null,
  p_checkCompatibility varchar2 default null,
  p_autoCompressPictures varchar2 default null,
  p_refreshAllConnections varchar2 default null,
  p_defaultThemeVersion varchar2 default null
  );
-- bookViews
procedure startbookViews(
  p_tag varchar2 default t_bookViews
  );
--
procedure endbookViews(
  p_tag varchar2 default t_bookViews
  );
-- workbookView
procedure startworkbookView(
  p_tag varchar2 default t_workbookView,
  p_visibility varchar2 default null,
  p_minimized varchar2 default null,
  p_showHorizontalScroll varchar2 default null,
  p_showVerticalScroll varchar2 default null,
  p_showSheetTabs varchar2 default null,
  p_xWindow varchar2 default null,
  p_yWindow varchar2 default null,
  p_windowWidth varchar2 default null,
  p_windowHeight varchar2 default null,
  p_tabRatio varchar2 default null,
  p_firstSheet varchar2 default null,
  p_activeTab varchar2 default null,
  p_autoFilterDateGrouping varchar2 default null
  );
--
procedure endworkbookView(
  p_tag varchar2 default t_workbookView
  );
-- sheets
procedure startsheets(
  p_tag varchar2 default t_sheets
  );
--
procedure endsheets(
  p_tag varchar2 default t_sheets
  );
-- sheet
procedure sheet(
  p_tag varchar2 default t_sheet,
  p_name varchar2,
  p_sheetId varchar2,
  p_state varchar2 default null,
  p_id varchar2
  );
-- calcPr
procedure calcPr(
  p_tag varchar2 default t_calcPr,
  p_calcId varchar2,
  p_calcMode varchar2 default null,
  p_fullCalcOnLoad varchar2 default null,
  p_refMode varchar2 default null,
  p_iterate varchar2 default null,
  p_iterateCount varchar2 default null,
  p_iterateDelta varchar2 default null,
  p_fullPrecision varchar2 default null,
  p_calcCompleted varchar2 default null,
  p_calcOnSave varchar2 default null,
  p_concurrentCalc varchar2 default null,
  p_concurrentManualCount varchar2 default null,
  p_forceFullCalc varchar2 default null
  );
-- definedName
procedure startdefinedName(
  p_tag varchar2 default t_definedName,
  p_name varchar2,
  p_localSheetId varchar2 default null,
  p_hidden varchar2 default null
  );
--
procedure enddefinedName(
  p_tag varchar2 default t_definedName
  );
-- definedNames
procedure startdefinedNames(
  p_tag varchar2 default t_definedNames
  );
--
procedure enddefinedNames(
  p_tag varchar2 default t_definedNames
  );

/******************************************************************/
-- Create structure
------------------------------------------
-- workbook: CT_Workbook
function cr_workbook(
  p_tag varchar2 default t_workbook,
  p_calcPr CT_CalcPr default null,
  p_fileVersion CT_FileVersion default null,
  p_workbookPr CT_WorkbookPr default null,
  p_bookViews t_bookViews_ default null, -- bookViews
  p_sheets t_sheets_ default null --sheet
  ) return CT_Workbook;
------------------------------------------
-- calcPr: CT_CalcPr
function cr_CalcPr(
  p_tag varchar2 default t_calcPr,
  p_calcId plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_calcMode ST_CalcMode default null,
  p_fullCalcOnLoad plx_sc.normalv2 default null,
  p_refMode ST_RefMode default null,
  p_iterate plx_sc.normalv2 default null,
  p_iterateCount plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_iterateDelta plx_sc.normalv2 default null,
  p_fullPrecision plx_sc.normalv2 default null,
  p_calcCompleted plx_sc.normalv2  default null,
  p_calcOnSave plx_sc.normalv2 default null,
  p_concurrentCalc plx_sc.normalv2 default null,
  p_concurrentManualCount plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_forceFullCalc plx_sc.normalv2 default null
  ) return CT_CalcPr;
------------------------------------------
-- fileVersion: CT_FileVersion
function cr_fileVersion(
  p_tag varchar2 default t_fileVersion,
  p_appName plx_sc.normalv2 default null, -- xsd:string
  p_lastEdited plx_sc.normalv2 default null, -- xsd:string
  p_lowestEdited plx_sc.normalv2 default null, -- xsd:string
  p_rupBuild plx_sc.normalv2 default null, -- xsd:string
  p_codeName plx_sc.normalv2 default null -- ST_Guid
  ) return CT_FileVersion;
------------------------------------------
-- workbookPr: CT_WorkbookPr
function cr_workbookPr(
  p_tag varchar2 default t_workbookPr,
  p_date1904 plx_sc.normalv2 default null,
  p_showObjects ST_Objects default null,
  p_showBorderUnselectedTables plx_sc.normalv2 default null,
  p_filterPrivacy plx_sc.normalv2 default null,
  p_promptedSolutions plx_sc.normalv2 default null,
  p_showInkAnnotation plx_sc.normalv2 default null,
  p_backupFile plx_sc.normalv2 default null,
  p_saveExternalLinkValues plx_sc.normalv2 default null,
  p_updateLinks ST_UpdateLinks default null,
  p_codeName plx_sc.normalv2 default null,
  p_hidePivotFieldList plx_sc.normalv2 default null,
  p_showPivotChartFilter plx_sc.normalv2 default null,
  p_allowRefreshQuery plx_sc.normalv2 default null,
  p_publishItems plx_sc.normalv2 default null,
  p_checkCompatibility plx_sc.normalv2 default null,
  p_autoCompressPictures plx_sc.normalv2 default null,
  p_refreshAllConnections plx_sc.normalv2 default null,
  p_defaultThemeVersion plx_sc.normalv2 default null -- xsd:unsignedInt
  ) return CT_WorkbookPr;
------------------------------------------
-- workbookView: CT_BookView
function cr_workbookView(
  p_tag varchar2 default t_workbookView,
  p_visibility ST_Visibility default null,
  p_minimized plx_sc.normalv2 default null,
  p_showHorizontalScroll plx_sc.normalv2 default null,
  p_showVerticalScroll plx_sc.normalv2 default null,
  p_showSheetTabs plx_sc.normalv2 default null,
  p_xWindow plx_sc.normalv2 default null, -- xsd:int
  p_yWindow plx_sc.normalv2 default null, -- xsd:int
  p_windowWidth plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_windowHeight plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_tabRatio plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_firstSheet plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_activeTab plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_autoFilterDateGrouping plx_sc.normalv2 default null
  ) return CT_BookView;
-- bookViews: t_bookViews_
procedure add_bookView(
  p_table in out t_bookViews_,
  p_workbookView CT_BookView
  );
------------------------------------------
-- sheet: CT_Sheet
function cr_sheet(
  p_tag varchar2 default t_sheet,
  p_name plx_sc.normalv2 default null, -- xsd:Xstring
  p_sheetId plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_state ST_SheetState default null,
  p_id plx_sc.normalv2 default null -- r:id
  ) return CT_Sheet;
-- sheets: t_sheets_
procedure add_sheet(
  p_table in out t_sheets_,
  p_sheet CT_Sheet
  );
------------------------------------------
-- defineNames: CT_DefinedName
function cr_definedName(
  p_tag varchar2 default t_definedName,
  p_name plx_sc.normalv2 default null, -- ST_Xstring
  p_localSheetId plx_sc.normalv2 default null, -- xsd:unsignedInt
  p_hidden plx_sc.normalv2 default null, -- xsd:boolean
  p_val plx_sc.normalv2 default null -- xml tag value
  ) return CT_DefinedName;
-- definedName: t_definedNames_
procedure add_definedName(
  p_table in out t_definedName_,
  p_definedName CT_DefinedName
  );
------------------------------------------
-- definedNames: CT_DefinedNames
function cr_definedNames(
  p_tag varchar2 default t_definedNames,
  p_definedName t_definedName_ default null
  ) return CT_DefinedNames;
/******************************************************************/
-- Generate XML part
------------------------------------------
-- calcPr: CT_CalcPr
procedure gen_calcPr(
  p_calcPr CT_CalcPr
  );
------------------------------------------
-- fileVersion: CT_FileVersion
procedure gen_fileVersion(
  p_fileVersion CT_FileVersion
  );
------------------------------------------
-- workbookPr: CT_WorkbookPr
procedure gen_workbookPr(
  p_workbookPr CT_WorkbookPr
  );
------------------------------------------
-- workbookView: CT_BookView
procedure gen_bookView(
  p_bookView CT_BookView
  );
------------------------------------------
-- bookViews: t_bookViews_
procedure gen_bookViews(
  p_bookViews t_bookViews_
  );
------------------------------------------
-- sheet: CT_Sheet
procedure gen_sheet(
  p_sheet CT_Sheet
  );
------------------------------------------
-- sheets: t_sheets_
procedure gen_sheets(
  p_sheets t_sheets_
  );
------------------------------------------
-- definedName: CT_DefinedName
procedure gen_definedName(
  p_definedName CT_DefinedName
  );
------------------------------------------
-- definedNames: CT_DefinedNames
procedure gen_definedNames(
  p_definedNames CT_DefinedNames
  );
------------------------------------------
-- workbook: CT_workbook
procedure gen_workbook(
  p_workbook CT_Workbook
  );
------------------------------------------
-- GEN XML
procedure gen_xml(
  p_workbook CT_Workbook
  );
--
function parse(
  p_file clob
  ) return CT_Workbook;
--
end ploffx_workbook;
/

