inputXLS

Purpose: Convert Excel Spreadsheet to OpenEdge temp-table records.

procedure inputXLS:
   define input parameter pfile_name as character no-undo.
   define input parameter pcolumns   as integer no-undo.
   define input parameter prows      as integer no-undo.
   define output parameter pxls_no   as integer no-undo.
   define output parameter ptrans_ok as logical initial false no-undo.
   define output parameter perr_msg  as character initial "" no-undo.
end procedure.

Where:
pfile_name is an input parameter as a string value for the absolute or relative pathed Excel Spreadsheet. Must be an xls or xlsx formatted Excel Spreadsheet.
pcolumns is an input parameter as an integer value for the maximum number of columns to import from the spreadsheet. Set to 0 if unknown.
prows is an input parameter as an integer value for the maximum number of rows to import from the spreadsheet. Set to 0 if unknown.
pxls_no is an output parameter as an integer value for the imported xls instance number. Multiple spreadsheets can be imported to temp-table records with separate xls instance numbers.
ptrans_ok is an output parameter as a logical value for the success or failure of the procedure.
perr_msg is an output parameter as a character for any error messages when the procedure failes.

Each record of the temp-table tt_excel is a specific cell in the spreadsheet.
Definition:

define {1} temp-table tt_excel no-undo
   field xls_no          as integer    label "XLS"        format ">>9"
   field sheet_no        as integer    label "SHEET"      format ">>9"
   field row_no          as integer    label "ROW"        format ">>,>>9"
   field column_no       as integer    label "COLUMN"     format ">>,>>9"
   field cell_name       as character  label "NAME"       format "x(10)"
   field cell_value      as character  label "VALUE"      format "x(20)"
   field cell_properties as character  label "PROPERTIES" format "x(10)"
   field is_formula      as logical    label "FUNCTION?"  format "yes/no" initial no

   index idx_prim as primary unique
      xls_no
      sheet_no
      row_no
      column_no

   index idx_name
      xls_no
      sheet_no
      cell_name
   .

Each record of the temp-table tt_excel_sheet is a specific worksheet in the spreadsheet.
Definition:

define {1} temp-table tt_excel_sheet no-undo
   field xls_no           as integer   label "XLS"   format ">>9"
   field sheet_no         as integer   label "SHEET" format ">>9"
   field sheet_name       as character label "NAME"  format "x(30)"
   field sheet_properties as character label "PROPERTIES" format "x(10)"

   field wb_no            as integer initial ?
   field ws_no            as integer initial ?

   index idx_prim as primary unique
      xls_no
      sheet_no

   index idx_name as unique
      xls_no
      sheet_name
   .

Example:

define variable wxls_no   as integer no-undo.
define variable wtrans_ok as logical no-undo.
define variable werr_msg  as logical no-undo.
{xls.i}

run inputXLS(input "C:\workspace\joe.xlsx",
             input 15,
             input 0,
             output wxls_no,
             output wtrans_ok,
             output werr_msg).
if not xtrans_ok then do:
   message werr_msg view-as alert-box.error.
   return.
end.

BUILD-LOOP:
for each tt_excel_sheet
   where tt_excel_sheet.xls_no     = wxls_no
     and tt_excel_sheet.sheet_name = "Sheet1"
no-lock, each tt_excel
   where tt_excel.xls_no           = tt_excel_sheet.xls_no
     and tt_excel.sheet_no         = tt_excel_sheet.sheet_no
     and tt_excel.cell_value       <> ?
no-lock
break by tt_excel.xls_no
      by tt_excel.sheet_no
      by tt_excel.row_no
      by tt_excel.column_no
on error undo build-loop, leave build-loop
on endkey undo build-loop, leave build-loop:
   if first-of(tt_excel.row_no) then do:
      assign worder_no  = ""
             worder_amt = 0.
   end.

   if tt_excel.column_no = 1 then
      assign worder_no = tt_excel.cell_value.
   else if tt_excel.column_no = 2 then
      assign worder_amt = decimal(tt_excel.cell_value).

   if last-of(tt_excel.row_no) then do:
      create tt_order.
      assign tt_order.order_no  = worder_no
             tt_order.order_amt = worder_amt.
   end.
end.  /* OF BUILD-LOOP */