create or replace package XLSX_Fill_Examples is
--v4.9.0
--
procedure loadDocument;
procedure setCellValue;
procedure getCellValue;
procedure insertRowsAfter;
procedure insertRowAfter;
procedure getRow;
procedure getRows;
procedure duplicateRow;
procedure deleteRow;
procedure deleteRows;
procedure clearRow;
--
--end of the package header
--
end XLSX_Fill_Examples;
/
create or replace package body XLSX_Fill_Examples is
--v4.9.0
--required table
/*
create table store_blob
(
  blob_file    blob,
  created_date date,
  filename     varchar2(255)
);
*/
--utils
function getXLSXasBLOB(
  p_filename varchar2
  ) return blob as
  l_xlsx blob;
begin
 --select from a table
  select blob_file
    into l_xlsx
  from store_blob
  where filename = p_filename;
  --return 
  return l_xlsx;
end;
--
procedure saveXLSX(
  p_filename varchar2,
  p_xlsx blob
  ) is
begin
  --delete
  delete from store_blob
    where filename = p_filename;
  --insert
  insert into store_blob
    (blob_file, created_date, filename)
  values
    (p_xlsx, sysdate, p_filename);
  commit;
end;
--loadDocument: minimal or skeleton
procedure loadDocument
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --
  --no action yet
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'loadDocument.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--setCellValue: set value of the cell
procedure setCellValue
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,       -- Worksheet ID
    p_row => 1,      -- Row ID
    p_cell => 'B',   -- Column name
    p_data => 'Init' -- String in the cell
    );
  --set value of the number cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,       -- Worksheet ID
    p_row => 4,      -- Row ID
    p_cell => 'B',   -- Column name
    p_data => 123.45 -- Number in the cell
    );  
  --set value of the date cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,    -- Worksheet ID
    p_row => 7,  -- Row ID
    p_cell => 'B',   -- Column name
    p_data => sysdate -- Date in the cell
    );  
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'setCellValue.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--getCellValue: get and set value of the cell
procedure getCellValue
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
  l_value varchar2(255 char);
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --get value
  l_value := ploffx_xlsx.getCellValue(
    p_ws => 1,       -- Worksheet ID
    p_row => 1,      -- Row ID
    p_cell => 'B'   -- Column name
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,       -- Worksheet ID
    p_row => 1,      -- Row ID
    p_cell => 'B',   -- Column name
    p_data => 'Init' || l_value -- String in the cell
    );
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'getCellValue.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--insertRowsAfter: insert new rows after the specific row
procedure insertRowsAfter
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
  l_new_row_id pls_integer;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --insert new rows
  l_new_row_id := ploffx_xlsx.insertRowsAfter(
    p_ws => 1,       -- Worksheet ID
    p_row => 1,      -- Row ID
    p_num => 3       -- number of new rows
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => l_new_row_id, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 'newvalue' -- String in the cell
    );
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'insertRowsAfter.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--insertRowAfter: insert one new row after the specific row
procedure insertRowAfter
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
  l_new_row_id pls_integer;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --insert row
  l_new_row_id := ploffx_xlsx.insertRowAfter(
    p_ws => 1,       -- Worksheet ID
    p_row => 1      -- Row ID
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => l_new_row_id, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 'newvalue' -- String in the cell
    );
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'insertRowAfter.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--getRow: get an existing row and insert this row after the specific row
procedure getRow
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
  l_new_row_id pls_integer;
   l_data ploffx_sheet.CT_row;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --get row
  l_data := ploffx_xlsx.getRow(
    p_ws => 1,       -- Worksheet ID
    p_row => 2      -- Row ID
    );
  --insert this row  
  l_new_row_id := ploffx_xlsx.insertRowAfter(
    p_ws => 1,       -- Worksheet ID
    p_row => 4,      -- Row ID
    p_data => l_data -- row with data 
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => l_new_row_id, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 'newvalue' -- String in the cell
    );
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'getRow.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--getRows: get existing rows and insert these rows after the specific row
procedure getRows
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
  l_data ploffx_sheet.CT_SheetData;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --get row
  l_data := ploffx_xlsx.getRows(
    p_ws => 1,       -- Worksheet ID
    p_row => 3,      -- Row ID
    p_num => 3       --number of the rows
    );
  --insert this row  
  ploffx_xlsx.insertRowsAfter(
    p_ws => 1,       -- Worksheet ID
    p_row => 9,      -- Row ID
    p_data => l_data -- row with data 
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => 10, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 'newvalue' -- String in the cell
    );
  --set value of the number cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => 11, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 66.77 -- String in the cell
    );  
  --set value of the date cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => 12, -- Row ID
    p_cell => 'B',         -- Column name
    p_data => sysdate -- String in the cell
    );  
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'getRows.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--duplicateRow: get an existing row and insert this row after the specific row
procedure duplicateRow
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --duplicate row
  ploffx_xlsx.duplicateRow(
    p_ws => 1,       -- Worksheet ID
    p_row => 2      -- Row ID
    );
  --set value of the character cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,             -- Worksheet ID
    p_row => 3,   -- Row ID
    p_cell => 'B',         -- Column name
    p_data => 'newvalue' -- String in the cell
    );
  --
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'duplicateRow.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--deleteRow: delete the specific row
procedure deleteRow
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --delete row
  ploffx_xlsx.deleteRow(
    p_ws => 1,       -- Worksheet ID
    p_row => 2      -- Row ID
    );
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'deleteRow.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--deleteRows: delete rows
procedure deleteRows
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --delete row
  ploffx_xlsx.deleteRows(
    p_ws => 1,       -- Worksheet ID
    p_row => 2,      -- Row ID
    p_num => 3       --number of the rows
    );
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'deleteRows.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--clearRow: delete the specific row content
procedure clearRow
  is
  l_input_xlsx blob;
  l_result_xlsx blob;
begin
  --get XLSX file as BLOB variable
  l_input_xlsx := getXLSXasBLOB('fill_test1.xlsx');
  --init ploffx_xlsx package variables
  ploffx_xlsx.init;
  --load XLSX file into the PL/OFFX internal structure
  ploffx_xlsx.loadDocument(l_input_xlsx);
  --delete row
  ploffx_xlsx.clearRow(
    p_ws => 1,       -- Worksheet ID
    p_row => 2      -- Row ID
    );

--set value of the number cell 
  ploffx_xlsx.setCellValue(
    p_ws => 1,       -- Worksheet ID
    p_row => 2,      -- Row ID
    p_cell => 'A',   -- Column name
    p_data => 22 -- number in the cell
    );    
    
  --save the result into BLOB var
  ploffx_xlsx.getDoc(p_blob_file => l_result_xlsx);
  --save to the table
  saveXLSX(
    p_filename => 'clearRow.xlsx',
    p_xlsx => l_result_xlsx
    );
end;
--
--end of the package body
--
end XLSX_Fill_Examples;
/
