/* Extract data from an excel xlsx file                                      */
/* Sahananda    April 2026                                                   */
/* This is demonstration code - no warantee of operability exists            */
/* feel free to learn from, use, develop, amend or ignore it as you see fit  */

/* ========================================================================= */
::requires "BSF.CLS"                    -- for Access to POI proxies
::requires "json.cls"                   -- to prepare data for passing
/* ========================================================================= */
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
/* ========================================================================= */
::class xlSheetPOIParser                                              public
/* ========================== class  methods =============================== */
::Attribute sheetsinFile get                                          class --
use arg file

   OFile     = .bsf~new("java.io.File",file)
   OFStream  = .bsf~new("java.io.FileInputStream", OFile)
   workbook  = .bsf~new("org.apache.poi.xssf.usermodel.XSSFWorkbook", OFstream)

      sheets = .array~new
      do sh = 0 to workBook~getNumberOfSheets()-1
         sheetName = workBook~getSheetAt(sh)~getSheetName()
         sheets~append(sheetname)
      end

   OFStream~close

RETURN sheets
/* ========================= instance methods ============================== */
::attribute columnsByTitle              -- column objects by title
::attribute columnsByReference          -- column objects by reference
::attribute rows                        -- an array of directories (rows)
::attribute continue_after_empty_rows?  -- stop looking for data on empty row?
::attribute sheet                       -- POI instance of sheet

::attribute json     GET
expose rows

   if \rows~isA(.array) then self~processRows

   if arg(1,'e')
   then if arg(1)~datatype('W'), rows~hasIndex(arg(1))
        then RETURN .json~new~toJson(rows[arg(1)])   -- return a single row
        else RETURN .nil                             -- nothing to return
   else      RETURN .json~new~toJson(rows)           -- return all rows

::attribute tableTxt GET
expose rows columnsByTitle columnsByReference
use arg col_separator = '|', row_separator = '-'

   if \rows~isA(.array) then self~processRows

   allColsRefs = columnsByReference~allIndexes~sort
   out        = .array~new(rows~items + 1)
   headers    = .array~new(allColsRefs~items)
   underline  = .mutableBuffer~new
   do ref over allColsRefs
      header  = columnsByReference~at(ref)
      width   = header~width
      headers~append(header~title)
      underline~append(row_separator~copies(width)||col_separator)
   end /* DO */
   out~append(headers~makestring('l',col_separator))
   out~append(underline~delete(underline~length,1))

   do row over rows
      rowArr = .array~new(allColsRefs~items)
      do ref over allColsRefs
         header  = columnsByReference~at(ref)
         width   = header~width
         title   = header~title
         if header~alignment = 'r'
         then rowArr~append(row[title~strip]~right(width))
         else rowArr~append(row[title~strip]~strip~left(width))
      end /* DO */
      out~append(rowArr~makestring('l',col_separator))
   end /* DO */
RETURN out~makestring('l',.endofline)

/* ------------------------------------------------------------------------- */
::method init
expose  worksheetFile  sheetname  continue_after_empty_rows? rows sheet
use arg worksheetFile, sheetname, continue_after_empty_rows? = .false

   OFile     = .bsf~new("java.io.File",worksheetFile)
   OFStream  = .bsf~new("java.io.FileInputStream", OFile)
    workbook = .bsf~new("org.apache.poi.xssf.usermodel.XSSFWorkbook", OFstream)
   OFStream~close

   Osheet = self~getSheet(workbook, sheetName)
   if Osheet~isNil then RETURN -1   --<<<<<<<-- create error exit

   self~initHeaders(Osheet~getRow(0))

   rows = .nil             -- rows becomes array when processrows method is run

/* ------------------------------------------------------------------------- */
::method initHeaders
expose columnsByTitle columnsByReference
use arg Orow

   columnsByTitle      = .stringtable~new
   columnsByReference  = .stringtable~new

   do Ocell over Orow~iterator
      colRef = self~cellRef2colRef(Ocell~getReference)
      value  = Ocell~toString
      if value = '' then value = 'column_'||colRef
      value  = value~changeStr('0a'x,' ')~space
      column = .xlSheetParser_Column~new(colRef, value)
      columnsByTitle~put(    column, value )
      columnsByReference~put(column, colRef)
   end /* DO */

RETURN 0

/* ------------------------------------------------------------------------- */
::method getSheet
expose sheet

use arg workbook, targetSheetName
   sheet = .nil
   do sh = 1 to workBook~getNumberOfSheets()
      sheetname = workBook~getSheetAt(sh-1)~getSheetName()
      if sheetname = targetSheetName
      then do
         sheet = workBook~getSheetAt(sh-1)
         leave                                                             --v|
      end /* DO */
   end /* DO */

RETURN sheet


/* ------------------------------------------------------------------------- */
::method processRows
/* processRows is separated from init to allow filters to be added           */
expose sheet columnsByReference rows

   rows = .array~new

   row1?   = .true

   rowIterator = sheet~iterator
   do row over rowIterator                                                  --
      if row1? = .true      -- step over the header row
      then do
         row1? = .false
         ITERATE                                                           --^|
      end /* DO */

      row_data = .directory~new

      -- iterate over each column
      cellIterator = row~cellIterator
      do cell over cellIterator
         cellref = cell~getReference
         colref  = self~cellRef2colRef(cellRef)
         value = cell~toString

         header = columnsByReference~at(colRef)   -- fetch header object

         do formatter over header~formatter       -- apply format(s) if any
            if hasMethod(formatter,'format')
            then value = formatter~format(value)
         end /* DO */

         title = header~title(value) -- swap value,txt so max length maintained
         row_data~put(value,title)         -- pop it in row indexed by title
      end

      rows~append(row_data)

   end

RETURN 0

/* ------------------------------------------------------------------------- */
::method addFormat
/* addFormat allows for values to be formatted during parsing                */
/* a formatter can be any class that implements a method called 'format'     */
/* that accepts a single string argument and returns the formatted form of   */
/* that argument.                                                            */
expose columnsByTitle
use arg title, formatters
   -- allow for many formats on one column
   if \formatters~isA(.array) then formatters = .array~of(formatters)

   do formatter over formatters
      if \hasMethod(formatter,'format') | formatter~isA(.string)
      then RAISE Syntax 88.900 array -
            ('Formatter must present format method, found' formatter)
   end /* DO */

   column = columnsByTitle~at(Title)
   if column~isNil
   then do
      msg = 'formatter(s) could not be added to' title '- column not found!'
      if .bsf.dialog~dialogBox(msg,,                        -
                               "Error adding Format",,,     -
                               .list~of("Ignore", "Abort"), -
                               "Ignore"                     )
      then RAISE syntax 88.900 array (msg)
      else RETURN                                                          -->|
   end

   -- add the format
   do formatter over formatters
      column~formatter~append(formatter)
   end /* DO */

/* ------------------------------------------------------------------------- */
::method cellRef2colRef                                              Private
/* Return the alphabetic part of an excel cell reference ie: A1 => A         */
use arg cell
RETURN cell~translate('','0123456789')~strip

/* ========================================================================= */
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
/* ========================================================================= */
::class xlSheetParser_Column                                        Private
/* ========================================================================= */
::Attribute alignment set
expose alignment alignment_is_locked?
use arg align, lock? = .true
    select
       when alignment~left(1)~lower = 'l', alignment_is_locked? = .false
          then alignment = 'l'
       when alignment~left(1)~lower = 'r', alignment_is_locked? = .false
          then alignment = 'r'
       otherwise nop
    end /* select */
    if lock? = .true then alignment_is_locked? = .true

::Attribute alignment get
expose alignment
RETURN alignment
::Attribute alignment_is_locked?
::Attribute formatter
::Attribute reference
::Attribute title get
/*    whilst fetching header text we maintain the column width and alignment */
expose title width alignment alignment_is_locked?
   if arg(1,'e')
   then do
      width = max(width,length(arg(1)))
      if alignment_is_locked? = .false, arg(1)~datatype('n') = .false
      then alignment = 'l'
      ret = title
   end /* DO */
   else if alignment = 'l'   -- if no arg passed return title aligned in width
        then ret = title~left(width)
        else ret = title~right(width)

RETURN ret


::Attribute width
/* ------------------------------------------------------------------------- */
::method init
/* ------------------------------------------------------------------------- */
 expose reference  title width alignment alignment_is_locked? formatter
use arg reference, title

   width = title~length
   alignment = 'r'                   -- any non-numeric value will flip this
   alignment_is_locked? = .false     -- will lock if set explicitly
   formatter = .array~new

/* ========================================================================= */
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
   -------------------------------------------------------------------------
/* ========================================================================= */
::routine hasMethod
/* as formatter may be a BSF proxy - enhance object class hasMethod method   */
use arg obj, method

   if obj~isA(.bsf)
   then do
      clz        = obj~getClass
      clzMethods = clz~getMethods
      do clzMethod over clzMethods
         if method~caselessEquals(clzMethod~getName)
         then RETURN .true                                                 -->|
      end /* DO */
      RETURN         .false                                                -->|
   end /* DO */
   else RETURN       obj~hasMethod(method)                                 -->|

