! fnxlsutl.bsi [107] - Various XLS{X} utility functions (modeled on fncsvutl.bsi)
!------------------------------------------------------------------------
!EDIT HISTORY
!Version 1.0:-
! [107] 21-Apr-26 / jdm / Add Fn'XLS'Input'Array()
! [106] 23-Sep-23 / jdm / Combine the lib init / file load / book create 
!                           into one func; switch to libxl64.bsi; add 
!                           warrning about unitialized private issue in test routine
! [105] 12-Sep-23 / jdm / Minor tweaking Fn'XLS'Input'Ordmap()
! [104] 06-Sep-23 / jdm / Add keywords (for sosfuncidx), minor modernization
! [103] 08-May-23 / jdm / add Fn'XLS'Input'Gridmap()
! [102] 30-Nov-21 / jdm / add protectpw$ option to Output'Ordmap to protect the sheet
! [101] 19-Sep-21 / jdm / add Fn'XLS'Output'Ordmap(), set up private vars
! [100] 18-Sep-21 / jdm / created
!------------------------------------------------------------------------
!REQUIREMENTS
!   Compiler 1036, A-Shell 6.5.1741.0 
!   AXL (LIBXL)
!   Compil /P{X} for named params
!NOTES
!   Compile this module as follows to create a test program
!   .COMPIL FNXLSVUTL.BSI/X:2/M/PX/C:FNXLSUTL_TEST=1
!
!KEYWORDS: excel axl xls 
!
!PUBLIC FUNCTIONS
!   Fn'XLS'Input'GridmapII($grid(), fspec$) - input XLS{X} into gridmap [103]
!   Fn'XLS'Input'Ordmap($map() {,fspec$, ...} ) - input XLS{X} row into ordmap
!   Fn'XLS'Output'Ordmap($map() {,fspec$, ...} ) - output ordmap to XLS{X} row
!   Fn'XLS'Input'Array(ary(),fspec$,row...) - input a row into an array [107]
!------------------------------------------------------------------------


!------------------------------------------------------------------------

++ifndef INC_FNXLSUTL_BSI
define INC_FNXLSUTL_BSI = 1

++include'once ashinc:ashell.def
++include'once ashinc:types.def         ! 
++include'once sosfunc:fnextch.bsi
++include'once sosfunc:fnexplode.bsi    ! 
++include'once sosfunc:fnfqfs.bsi       ! 
++include'once sosfunc:fnlogarc.bsi     ! 
++include'once libxl:libxl64.bsi        ! [106]

defstruct ST_XLS_COL_INFO               ! 
    map2 maxwidth,b,2                   ! max width of col
    map2 title$,s,30                    ! title (first row)
    map2 cformat$,s,10                  ! xtree cformat codes (S unless ...?)
endstruct 

++pragma PRIVATE_BEGIN                  ! [101]
    ! local static variables to retain between calls
    map1 CSVUTL'Private
        map2 Libxl'Loaded,BOOLEAN
        map2 hBook,BookHandle           ! handle to workbook structure in memory
        map2 hSheet,SheetHandle         ! handle to sheet structure in memory
        map2 MaxRow,i,4                 ! # rows
        map2 MaxCol,i,2                 ! # columns
        map2 CurRow,i,4                 ! current row
        map2 Updated,BOOLEAN            ! .TRUE if spreadsheet updated
        map2 InxSpec$,T_NATIVEPATH      ! name of spreadsheet opened for input
        map2 OutxSpec$,T_NATIVEPATH     ! name of spreadsheet to save (if updated)
        map2 ProtectPW$,s,20            ! [102] password to protect updated sheet with
    dimx ColNames(0),x,0, auto_extend   ! column name -> col #
    trace.print (99,"xlsutl") "FNXLSUTL.BSI Private Init"   ! [106] 
++pragma PRIVATE_END

!-----------------------------------------------------------------------
!Function:
!   Input XLS{X} line into ordmap 
!Params:
!   $map() ordmap() [byref out] - ary(column) -> value (or col # for header line)
!
!   fspec$ (str) [in] - if specified, file is loaded into memory via LIBXL
!                       and we return $map(ColNames) -> col#'s instead of the
!                       normal $map(ColNames) -> colvalues.
!
!   banner$ (str) [out] - optional banner will be returned here (on first call
!                           only) if present
!
!   isblank (BOOLEAN) [out] - set if the entire line is blank (saves caller from
!                               having to check each column)
!
!   row (num) [in/out] - on input, row # to read (0 or omit for next)
!                      - on output, row # just read
!
!   terminate (BOOLEAN) [in] - if .TRUE, close the file and release/unload
!
!Returns:
!   # of fields input in line (0 for eof; note that fields can all be blank - see isblank)
!   -1 unable to load libxl library
!   -2 invalid file extension
!   -3 unable to create book handle
!   -4 unable to load file
!   -5 unable to get a sheet handle
!Globals:
!   CSVUTL'Private 
!Notes:
!   Modeled on Fn'CSV'Input'Ordmap() in fncsvutl.bsi, except here we have the extra
!   complications of the XLS{X} loading, use of Fn'LibXL'xxx functions, possibility
!   of a banner, ability to seek, etc.  
!
!   We do assume however that the Excel file is "well structured", meaning that
!   it looks pretty much like a CSV except for a possible banner on first row.
!
!   Function is responsible for opening/closing/loading/unloading file and LIBXL library.
!
!   Initial call to open must specify the fspec; returned $map contains colname -> col#.  
!   (Since the ordmap is stored in alphabetical order, this initial version of the map 
!    would be the only way for the caller to determine the physical order, assuming it cares.)
!
!   Subsequent calls return $map(colname) -> value
!
!   Function clears the $map() on each call.
!
!   To abort the operation of reading the file (without reading to the end),
!   set the terminate flag. 
!
!   Upon hitting end of input, or the terminate flag, if file has not been
!   updated it is simply closed. Otherwise, the original file is renamed
!   ("-orig" appended to its name) and the updated file saved in its place.
!
!   In normal usage, caller should omit the row parameter, which is mainly
!   for applications that need to seek to specific row numbers, and/or if
!   the caller is also using Fn'XLS'Output'Ordmap() to update the spreadsheet.
!   If specified, it should be set either to 0 (interpreted as next row),
!   or to the previously returned row + 1. Ignored on input for the first
!   call (i.e. when file is opened for first time).
!
!
!   See the test routine at the bottom of this file.
!---------------------------------------------------------------------
Function Fn'XLS'Input'Ordmap($map() as ordmap(varstr;varstr), &
                             fspec$="" as T_NATIVEPATH:inputonly, &
                             isblank=.FALSE as BOOLEAN:outputonly, &
                             banner$="" as s0:outputonly, &
                             row=0 as i4, &
                             terminate=.FALSE as BOOLEAN:inputonly) as i2

    
    ! local dynamic variables initialized on each call
    map1 locals
        map2 i,i,2
        map2 dlflags,T_BITFLAGS32
        map2 bookformat,b,2
        map2 rowfirst,i,4
        map2 colfirst,i,4
        map2 rowlast,i,4
        map2 collast,i,4
        map2 mergeidx,i,4
        
    dimx colvalues(0),s,0,auto_extend   ! [106] was x

    ! if terminate close / unload library
    if terminate then
        if Libxl'Loaded then
            call fn'xls'release'and'unload()
        endif
        exitfunction        
    endif

    if not Libxl'Loaded then

        .fn = fn'xls'load'lib'and'file(fspec$=fspec$) 
        if .fn < 0 exitfunction
        
       	trace.print (99,"xlsutl") "sheetcount: " + Fn'LibXL'GetSheetCount()

        hSheet = Fn'LibXL'GetSheet(1)
        trace.print (99,"xlsutl") "GetSheet(1)",hSheet

        if .isempty(hSheet) then         ! [106] if unable to get a sheet handle
            .fn = -5
            call fn'xls'release'and'unload()
            exitfunction
        endif

        MaxCol = Fn'LibXL'SheetGetLastCol(hSheet) - 1
        MaxRow = Fn'LibXL'SheetGetLastRow(hSheet) - 1
        CurRow = 1
        trace.print (99,"xlsutl") MaxCol, MaxRow
        
        ! check if there is a banner
        trace.print (99,"xlsutl") "SheetGetMergeCount: " + Fn'LibXL'SheetGetMergeCount(hSheet)
        if Fn'LibXL'SheetGetMergeCount(hSheet) then
            mergeidx = Fn'LibXL'SheetGetMergeIdx(hsheet=hSheet,idx=1,rowfirst=rowfirst,rowlast=rowlast, & 
                        colfirst=colfirst,collast=collast)
            trace.print (99,"xlsutl") mergeidx, rowfirst, rowlast, colfirst
            if mergeidx then
                if rowfirst=1 and colfirst=1 then
                    banner$ = Fn'LibXL'ReadValue$(row=1,col=1)
                    CurRow = 2      ! skip over banner
                endif
            endif
        endif
        xputarg @banner$

        ! init the ColNames array
        ! here we simulate the input csv #ch, colname() 
        ! by reading each of the cells individually into the array
        ! input csv #ch, ColNames()
        for i = 1 to MaxCol
            ColNames(i) = Fn'LibXL'ReadValue$(row=CurRow, col=i)
            if ColNames(i) # "" then
                isblank = .FALSE
            endif
        next i
        xputarg @isblank
            
        .fn = .extent(ColNames())
        
        ! return map of ColNames -> col#'s (may or may not be interesting to caller)
        .clear $map()
        for i = 1 to .fn
            $map(ColNames(i)) = i
        next i
        row = CurRow        ! [105] 
        exitfunction
        
    else                    ! input next line into map
        
        if row then         ! [101] if row specified, use it
            CurRow = row
        else                ! else get next
            CurRow += 1
            row = CurRow
        endif
        
        if CurRow <= MaxRow then
            
            ! here we simulate the input csv #ch, colvalues() 
            ! by reading each of the cells individually into the array
            ! input csv #ch, colvalues()
            isblank = .TRUE
            for i = 1 to MaxCol
                trace.print (99,"xlsutl") i,CurRow
                colvalues(i) = Fn'LibXL'ReadValue$(row=CurRow, col=i)
                if colvalues(i) # "" then
                    isblank = .FALSE
                endif
            next i
            xputarg @isblank
            
            .fn = .extent(colvalues())
            
            ! return map of ColNames -> colvalues
            .clear $map()
            for i = 1 to .fn
                if i <= .extent(ColNames()) then
                    $map(ColNames(i)) = colvalues(i)
                else                                            ! line had more fields than header!
                    $map("Unknown column "+i) = colvalues(i)
                endif
            next i

        else                    ! we're done
            
            ! if the file has been updated, we need to save it ...
            if Updated then
                if OutxSpec$ = "" then
                    xcall MSGBOX,"Workbook has been updated but no new filename specified." &
                        + " Updates will be discarded", "Workbook Update Error", &
                        MBTN_OK, MBICON_STOP, MBMISC_TOPMOST
                else
                    if ProtectPW$ # "" then     ! [102] 
                        call Fn'LibXL'SheetSetProtect(bprotect=.TRUE, password$=ProtectPW$) 
                    endif
                    call Fn'LibXL'SaveBook(OutxSpec$)
                endif
            endif
            
            .fn = 0
            call fn'xls'release'and'unload()
            exitfunction
        endif
        
    endif
$EXIT:                          ! [105] return row regardless    
    xputarg @row                ! [101] return row just read
    
EndFunction


!-----------------------------------------------------------------------
!Function:
!   Output from ordmap to XLS{X} row
!Params:
!   $map() ordmap() [byref in] - ary(column) -> value
!
!   fspec$ (str) [out] - If specified, this will be the name of the updated
!                        spreadsheet when saved (must differ from original).
!                        If not specified, default is to append -updF NOT
!   banner$ (str) [in] - if not "", output the banner and ignore $map()
!                        (row should be set to 0, insert set if no prior banner)
!
!   insert (BOOLEAN) [in] - if .TRUE, insert new row. 
!
!   row (num) [in] - row # to write (base 1)
!
!   protectpw$ (str) [in] - if not "", set the ProtectPW$ field so that the 
!                           input routine will protect the sheet if updated. [102] 
!
!
!Returns:
!   # of fields input in line (0 for eof; note that fields can all be blank - see isblank)
!   -1 library not loaded (must use input routine first)
!   -2 invalid file extension (for saved file)
!   -3 invalid column name in $map for update
!Globals:
!Notes:
!   Modeled on Fn'CSV'Input'Ordmap() in fncsvutl.bsi, except here we have the extra
!   complications of the XLS{X} loading, use of Fn'LibXL'xxx functions, possibility
!   of a banner, ability to seek, etc.  
!
!   We do assume however that the Excel file is "well structured", meaning that
!   it looks pretty much like a CSV except for a possible banner on first row.
!
!   Function is responsible for opening/closing/loading/unloading file and LIBXL library.
!
!   Initial call to open must specify the fspec; returned $map contains colname -> col#.  
!   (Since the ordmap is stored in alphabetical order, this initial version of the map 
!    would be the only way for the caller to determine the physical order, assuming it cares.)
!
!   Subsequent calls return $map(colname) -> value
!
!   Function clears the $map() on each call.
!
!   To abort the operation of reading the file (without reading to the end),
!   set the terminate flag.
!
!   In normal usage, caller should omit the row parameter, which is mainly
!   for applications that need to seek to specific row numbers, and/or if
!   the caller is also using Fn'XLS'Output'Ordmap() to update the spreadsheet.
!   If specified, it should be set either to 0 (interpreted as next row),
!   or to the previously returned row + 1. Ignored on input for the first
!   call (i.e. when file is opened for first time).
!
!   See the test routine at the bottom of this file.
!---------------------------------------------------------------------
Function Fn'XLS'Output'Ordmap($map() as ordmap(varstr;varstr), &
                             fspec$="" as T_NATIVEPATH:inputonly, &
                             banner$="" as s0:outputonly, &
                             row=0 as i4:inputonly, protectpw$="" as s20:inputonly) as i2

    
    ! local dynamic variables initialized on each call
    map1 locals
        map2 i,i,2
        map2 col,i,2
        map2 colname$,s,60
        map2 newvalue$,s,0
        map2 hformat,FormatHandle
        map2 oldvalue$,s,0
        
    dimx colvalues(0),x,0,auto_extend

    if not Libxl'Loaded then
        .fn = -1
        exitfunction
    endif
    
    if row = 0 then     ! if row not specified, use current
        row = CurRow 
    endif

    if fspec$ # "" then
        fspec$ = Fn'FQFS$(fspec$)
        if fspec$[-5,-1] # ".xlsx" and fspec$[-4,-1] # ".xls" then
            .fn = -1
            exitfunction
        endif
        OutxSpec$ = fspec$      ! save in private area for Fn'XLS'Input'Ordmap()
    endif
    
    ! write out only the columns that have data...
    foreach $$i in $map()
        colname$ = .key($$i)
        newvalue$ = $$i
        
        ! translate colname$ to #
        col = 0
        for i = 1 to .extent(ColNames())
            if ColNames(i) = colname$ then
                col = i
                exit
            endif
        next i
        if col = 0 then
            .fn = -3        ! invalid col name (let caller handle it)
            xcall MSGBOX, "Invalid column name in Fn'XLS'Output'Map(): " + colname$ &
                +chr(13) + "Column update skipped.", &
                "Spreadsheet Update Error", MBTN_OK, MBICON_STOP, MBMISC_TOPMOST
            repeat          ! go on to next column
        endif
        
        oldvalue$ = Fn'LibXL'ReadValue$(row=row, col=col, hformat=hformat)
        
        i = Fn'LibXL'Write(row=row,col=col,value=newvalue$,hformat=hformat)
        if .fn >= 0 and i # 0 then
            .fn += 1                ! count successful writes
            Updated = .TRUE         ! let Fn'XLS'Input'Ordmap() know to save it
            ProtectPW$ = protectpw$ ! [102] set pw protect
        endif

    next $$i
    
EndFunction

![103]
!-----------------------------------------------------------------------
!Function:
!   Input XLS{X} entire file into a gridmap(int;int;varstr)
!Params:
!   $gridii() gridmap(int;int;varstr) [byref out] 
!
!   fspec$ (str) [in] - input file (.XLS or .XLSX)
!   sheetno (num) [in] - optional sheet # (default 1)
!Returns:
!   # of rows input
!   -1 unable to load libxl library
!   -2 invalid file or file extension
!   -3 unable to create book handle
!   -4 unable to load file
!   -5 unable to get a sheet handle (invalid sheetno?)
!Globals:
!   CSVUTL'Private 
!Notes:
!   If the first row of the file contains any merged cells, we treat it
!   as row 0 (possibly a banner, maybe just top level column headers). 
!
!   As is the custom with gridmap(int;int;varstr), row #1 values are treated as
!   column names.
!
!   Other than the two assumptions above, we can handle pretty much any kind
!   spreadsheet. (The number of columns does not have to be uniform, there can
!   be null cells, etc.)
!
!   Function is responsible for opening/closing/loading/unloading file and LIBXL library.
!
!   See the test routine at the bottom of this file.
!---------------------------------------------------------------------
Function Fn'XLS'Input'GridmapII($gridii() as gridmap(int;int;varstr), &
                             fspec$ as T_NATIVEPATH:inputonly, &
                             sheetno=1 as i2:inputonly) as i2

    ! local dynamic variables initialized on each call
    map1 locals
        map2 i,i,2
        map2 rowfirst,i,4
        map2 colfirst,i,4
        map2 rowlast,i,4
        map2 collast,i,4
        map2 mergeidx,i,4
        map2 gridrow,i,4
        map2 row,i,4
        map2 col,i,2
        
    .fn = fn'xls'load'lib'and'file(fspec$=fspec$) 
    if .fn < 0 exitfunction
        
    trace.print (99,"xlsutl") "sheetcount: " + Fn'LibXL'GetSheetCount()

    sheetno = ife(sheetno,1)        ! convert 0 to 1
    hSheet = Fn'LibXL'GetSheet(sheetno)
    if .isempty(hSheet) then
        .fn = -5
        exitfunction
    endif
    
    trace.print (99,"xlsutl") "GetSheet()",sheetno,hSheet
    if hSheet = 0 then          ! abort if unable to get a sheet handle
        .fn = -5
        call fn'xls'release'and'unload()
        exitfunction
    endif

    MaxCol = Fn'LibXL'SheetGetLastCol(hSheet) - 1
    MaxRow = Fn'LibXL'SheetGetLastRow(hSheet) - 1
    CurRow = 1
    trace.print (99,"xlsutl") MaxCol, MaxRow
    
    gridrow = 1         ! starting grid row
    
    ! check if there is a banner
    trace.print (99,"xlsutl") "SheetGetMergeCount: " + Fn'LibXL'SheetGetMergeCount(hSheet)
    if Fn'LibXL'SheetGetMergeCount(hSheet) then
        mergeidx = Fn'LibXL'SheetGetMergeIdx(hsheet=hSheet,idx=1,rowfirst=rowfirst,rowlast=rowlast, & 
                    colfirst=colfirst,collast=collast)
        trace.print (99,"xlsutl") mergeidx, rowfirst, rowlast, colfirst
        if mergeidx then
            if rowfirst=1 then      ! if any mergeed cells on row 1
                gridrow = 0         ! treat this as grid row 0
            endif
        endif
    endif

    ! now just loop through all the cells on the sheet
    for row = 1 to MaxRow
        for col = 1 to MaxCol
            $gridii(gridrow,col) =  Fn'LibXL'ReadValue$(row=row,col=col)
            trace.print (99,"xlsutl2") row, col, $gridii(gridrow, col)
        next col
        gridrow += 1
    next row
    .fn = MaxRow
            
$EXIT:                      
    if Libxl'Loaded then
        call fn'xls'release'and'unload()
    endif
    
EndFunction


!-----------------------------------------------------------------------
!Function:
!   Input XLS{X} line into an array, simulating INPUT CSV #CH, ARY()
!Params:
!   $ary() s,0,auto_extend [byref out] - ary(column) -> value (or col # for header line)
!
!   fspec$ (str) [in] - if specified, file is loaded into memory via LIBXL
!                       and we return $map(ColNames) -> col#'s instead of the
!                       normal $map(ColNames) -> colvalues.
!
!   isblank (BOOLEAN) [out] - set if the entire line is blank (saves caller from
!                               having to check each column)
!
!   banner$ (str) [out] - optional banner will be returned here (on first call
!                           only) if present
!
!   row (num) [in/out] - on input, row # to read (0 or omit for next)
!                      - on output, row # just read
!
!   terminate (BOOLEAN) [in] - if .TRUE, close the file and release/unload
!
!Returns:
!   # of fields input in line (0 for eof; note that fields can all be blank - see isblank)
!   -1 unable to load libxl library
!   -2 invalid file extension
!   -3 unable to create book handle
!   -4 unable to load file
!   -5 unable to get a sheet handle
!Globals:
!   CSVUTL'Private 
!Notes:
!   Similar to Fn'XLS'Input'Map() but simpler. 
!
!   Merged cells act essentially as if they were not merged (first one contains
!   a value, the others are blank)
!
!   Dealing with a banner is left to the caller (probably by analyzing the
!   first row)
!
!   Function is responsible for opening/closing/loading/unloading file and LIBXL library.
!
!   Initial call to open must specify the fspec. (file gets opened, remains open
!   until called with the terminate flag).  Each call returns ary() (reset each time)
!
!   To abort the operation of reading the file (without reading to the end),
!   set the terminate flag. 
!
!   Upon hitting end of input, or the terminate flag, if file has not been
!   updated it is simply closed. Otherwise, the original file is renamed
!   ("-orig" appended to its name) and the updated file saved in its place.
!
!   In normal usage, caller should omit the row parameter, which is mainly
!   for applications that need to seek to specific row numbers.
!   If specified, it should be set either to 0 (interpreted as next row),
!   or to the previously returned row + 1. Ignored on input for the first
!   call (i.e. when file is opened for first time).
!
!   See the test routine at the bottom of this file.
!---------------------------------------------------------------------
Function Fn'XLS'Input'Ary(ary() as s0, &
                             fspec$="" as T_NATIVEPATH:inputonly, &
                             isblank=.FALSE as BOOLEAN:outputonly, &
                             banner$="" as s0:outputonly, &                             
                             row=0 as i4, &
                             terminate=.FALSE as BOOLEAN:inputonly) as i2

    ! local dynamic variables initialized on each call
    map1 locals
        map2 i,i,2
        map2 dlflags,T_BITFLAGS32
        map2 bookformat,b,2
        map2 rowfirst,i,4
        map2 colfirst,i,4
        map2 rowlast,i,4
        map2 collast,i,4
        map2 mergeidx,i,4
        map2 mrgcnt,i,4
        map2 idx,i,4
        
    static map1 s_max'merge'row,i,2     ! highest row # with merged cells
    
    ! if terminate close / unload library
    if terminate then
        if Libxl'Loaded then
            call fn'xls'release'and'unload()
        endif
        exitfunction        
    endif

    if not Libxl'Loaded then

        .fn = fn'xls'load'lib'and'file(fspec$=fspec$) 
        if .fn < 0 exitfunction
        
       	trace.print (99,"xlsutl") "sheetcount: " + Fn'LibXL'GetSheetCount()

        hSheet = Fn'LibXL'GetSheet(1)
        trace.print (99,"xlsutl") "GetSheet(1)",hSheet

        if .isempty(hSheet) then         ! [106] if unable to get a sheet handle
            .fn = -5
            call fn'xls'release'and'unload()
            exitfunction
        endif

        MaxCol = Fn'LibXL'SheetGetLastCol(hSheet) - 1
        MaxRow = Fn'LibXL'SheetGetLastRow(hSheet) - 1
        CurRow = 0

        ! check if there is a banner
        trace.print (99,"xlsutl") "SheetGetMergeCount: " + Fn'LibXL'SheetGetMergeCount(hSheet)
        mrgcnt = Fn'LibXL'SheetGetMergeCount(hSheet) 
        if mrgcnt > 0 then 
            mergeidx = Fn'LibXL'SheetGetMergeIdx(hsheet=hSheet,idx=1,rowfirst=rowfirst,rowlast=rowlast, & 
                        colfirst=colfirst,collast=collast)
            trace.print (99,"xlsutl") mergeidx, rowfirst, rowlast, colfirst
            if mergeidx then
                if rowfirst=1 and colfirst=1 then
                    banner$ = Fn'LibXL'ReadValue$(row=1,col=1)
                    CurRow = 1      ! skip over banner
                endif
            endif
        endif
        xputarg @banner$

        ! identify the max row with merged cells
        s_max'merge'row = 0
        for idx = 1 to mrgcnt
            if Fn'LibXL'SheetGetMergeIdx(hsheet=hSheet,idx=idx,rowfirst=rowfirst,rowlast=rowlast, & 
                        colfirst=colfirst,collast=collast) then

                trace.print (99,"xlsutl") mergeidx, rowfirst, rowlast, colfirst
                s_max'merge'row = s_max'merge'row max rowfirst
            endif
        next idx
        
        trace.print (99,"xlsutl") MaxCol, MaxRow, s_max'merge'row
        if banner$ # "" then
            exitfunction
        endif
    endif
    
    if row then         ! [101] if row specified, use it
        CurRow = row
    else                ! else get next
        CurRow += 1
        row = CurRow
    endif
    
    trace.print (99,"xlsutl") CurRow, MaxRow
    
    if CurRow <= MaxRow then
        
        ! here we simulate the input csv #ch, ary() 
        ! by reading each of the cells individually into the array
        ! input csv #ch, ary()
        isblank = .TRUE
        .clear ary()
        for i = 1 to MaxCol

            ary(i) = Fn'LibXL'ReadValue$(row=CurRow, col=i)

!>!            ! detect if row,col is in a merged area
!>!            ! (probably not necessary since all but first cell will probably be blank)
!>!            if row <= s_max'merge'row then
!>!                ! detect if column about to be read is part of a merged block
!>!                if Fn'LibXL'SheetGetMergeXY(hsheet=hSheet,rowfirst=rowfirst,rowlast=rowlast, &
!>!                            row=CurRow,col=i, &
!>!                            colfirst=colfirst,collast=collast) then     ! yes
!>!                    if row = rowfirst and col > colfirst then
!>!                        ary(i) = ""
!>!                    endif
!>!                endif
!>!            endif
            trace.print (99,"xlsutl") i,CurRow
            if ary(i) # "" then
                isblank = .FALSE
            endif
        next i
        xputarg @isblank
        
        .fn = .extent(ary())
        
    else                    ! we're done
        
        ! if the file has been updated, we need to save it ...
        if Updated then
            if OutxSpec$ = "" then
                xcall MSGBOX,"Workbook has been updated but no new filename specified." &
                    + " Updates will be discarded", "Workbook Update Error", &
                    MBTN_OK, MBICON_STOP, MBMISC_TOPMOST
            else
                if ProtectPW$ # "" then     ! [102] 
                    call Fn'LibXL'SheetSetProtect(bprotect=.TRUE, password$=ProtectPW$) 
                endif
                call Fn'LibXL'SaveBook(OutxSpec$)
            endif
        endif
        
        .fn = 0
        call fn'xls'release'and'unload()
        exitfunction
    endif
        
$EXIT:                          ! [105] return row regardless    
    xputarg @row                ! [101] return row just read
    
EndFunction


!---------------------------------------------------------------------
!Function:
!   initialize the AXL library, load the specified file into hBook
!Params:
!   fspec$ (str) [in] - input file (.XLS or .XLSX)
!Returns:
!   status:
!    0 success
!   -1 unable to load libxl library
!   -2 invalid file or file extension
!   -3 unable to create book handle
!   -4 unable to load file
!Globals:
!   Libxl'Loaded
!   hBook   
!Notes:
!---------------------------------------------------------------------
private function fn'xls'load'lib'and'file(fspec$ as T_NATIVEPATH:inputonly) as i2

    map1 locals
        map2 dlflags,T_BITFLAGS32
        map2 bookformat,b,2
        
    if ucs(fspec$[-5,-1]) = ".XLSX" then
        bookformat = XLBT_XLSX
    elseif ucs(fspec$[-4,-1]) = ".XLS" then
        bookformat = XLBT_XLS
    else
        .fn = -2
        exitfunction
    endif

    if lookup(fspec$)=0 then
        .fn = -2
        exitfunction
    endif

    trace.print (99,"xlsutl") "Loading/initializing libxl library..." 
    dlflags = DLF_STATUS_BASERR or DLF_STATUS_BASERR
    dlflags = dlflags or DLF_PRINT_ERRORS		! print status/syserr errors to screen
    dlflags = dlflags or DLF_ASCII
    if Fn'LibXL'Load(dlflags=dlflags) then
        .fn = -1
        exitfunction
    endif
    
    Libxl'Loaded = .TRUE

    trace.print (99,"xlsutl") "Create book handle",bookformat
    hBook = Fn'LibXL'CreateBook(bookformat,rcbase=1)
    if hBook = 0         ! abort if unable to create book
        .fn = -3
        call fn'xls'release'and'unload()
        exitfunction
    endif

    if Fn'LibXL'LoadBook(fspec$) = 0 then
        .fn = -4
        call fn'xls'release'and'unload()
        exitfunction
    endif

endfunction
!---------------------------------------------------------------------
!Function:
!   release the book and sheet (if applicable) and unload the libary
!Params:
!Returns:
!   nothing
!Globals:
!   Libxl'Loaded
!   hSheet
!   hBook
!Notes:
!---------------------------------------------------------------------
private function fn'xls'release'and'unload() as i2
            
    if not .isempty(hSheet) then            ! [106]
        call LibXL'ReleaseSheet(hSheet)
    endif
    if not .isempty(hBook) then             ! [106] 
        call LibXL'ReleaseBook(hBook)
    endif
    if Libxl'Loaded then
        call Fn'LibXL'Unload()
        Libxl'Loaded = .FALSE
    endif

EndFunction

++endif

!=======================================================================
! Test / Sample Code
!=======================================================================

++ifdef FNXLSUTL_TEST 

++message Compiling test program for Fnxlsutl.bsi()...

++include'once sosfunc:fnminasver.bsi

dimx $xlsmap, ordmap(varstr;varstr)
dimx $gridii, gridmap(int;int;varstr)           ! [103] 

dimx colinfo(0),ST_XLS_COL_INFO,auto_extend        

map1 test'vars
    map2 columns,i,2
    map2 lincnt,i,2
    map2 op,i,2
    map2 sortcolname$,s,64
    map2 xlspec$,T_NATIVEPATH   
    map2 banner$,s,0
    map2 isblank,BOOLEAN
    map2 rows,i,4
    map2 sheetno,i,2
    
    ? "Testing Fnxlsutl.bsi routines..."
    ?
    input "1) Fn'XLS'Input'Ordmap(), 2) Fn'XLS'Input'Gridmap(): ",op
    if op = 0 end
        
    input line "XLS{X} file spec: ",xlspec$
    
    if op = 1 then
        ! [106]
        ? "WARNING: If compiled prior to compiler version 1037, this operation may result"
        ? "in an unitialized DIMX error due to the compiler's failure to init the private"
        ? "area in the main module. If so, either recompile or use the separate "
        ? "FNXLSUTL.BP[907,11] test program."
        ?
        ? "Calling Fn'XLS'Input'Ordmap($xlsmap(),""";xlspec$;""",banner$) to init column array..."
        columns = Fn'XLS'Input'Ordmap($xlsmap(), fspec$=xlspec$, banner$=banner$)
        ? tab(4);"Column count: ";columns
        ? tab(4);"Banner: ";banner$
        
        if columns > 0 then        
            foreach $$i in $xlsmap()
                ? tab(4);"Col #";$$i; tab(15);": ";.key($$i)
            next $$i
            ?
            stop
            ?
            ? "Inputting lines from file into map..."
            do 
                lincnt += 1
                columns = Fn'XLS'Input'Ordmap($xlsmap(),isblank=isblank)
                ? "line";lincnt;": ";columns;"columns input..."
                if isblank then
                    ? tab(3);"<blank input line>"
                else
                    foreach $$i in $xlsmap()
                        ? tab(3);"$xlsmap(";.key($$i);") ";tab(30);" ==> [";$$i;"]"
                    next $$i
                endif
                input "ENTER for next line or -1 to terminate: ",op
                if op = -1 then
                    call Fn'XLS'Input'Ordmap($xlsmap(), terminate=.TRUE)
                    exit
                endif
                ?
            loop until columns < 1
            ? "End of test"             ! note: file closed by function
        endif
    else
        input "Enter sheet # to input into grid [1]: ", sheetno
        ? "Calling Fn'XLS'Input'Gridmap($gridii(),""";xlspec$;""",";sheetno;") ..."
        rows = Fn'XLS'Input'GridmapII($gridii()=$gridii(), fspec$=xlspec$, sheetno=sheetno)
        ? rows;" rows input"
        if rows > 0 then 
            if .maxrow($gridii()) < rows then
                ? "Including banner in row zero"
            endif
            op = 0
            input "1) to create CSV version for viewing: ",op
            if op then
                open #2, "gridutltst.csv", output
                writecd #2, $gridii()
                close #2
                xcall EZTYP, "gridutltst.csv"
                kill "gridutltst.csv"
            endif
        elseif rows < 0 then
            ? "See fnxlsutl.bsi source for error status info"
        endif
        ? "End of test"
    endif
            
++message Use RUN FNXLSUTL to run test
++endif