NB. xlextract
NB.
NB. extracts all data from an Excel spreadsheet
NB.
NB. max rows in single worksheet: 10000
NB.
NB. example:
NB.
NB.    load '~Source\examples\ole\excel\xlextract.ijs'
NB.
NB.    extractXLData 'd:\excel\test.xls'
NB. +-------+----------+---------------------------------------------------+
NB. |"Sales"|$A$2:$D$17|+--------------+---------+---------+--------------+|
NB. |       |          ||Revenues: 2004|         |         |              ||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. |       |          ||Month         |Standard |Deluxe   |Total  Revenue||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. |       |          ||January       |$120,450 |$206,850 |$327,300      ||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. |       |          ||February      |$132,440 |$208,550 |$340,990      ||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. |       |          ||March         |$126,900 |$184,140 |$311,040      ||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. |       |          ||TOTALS        |$379,790 |$599,540 |$979,330      ||
NB. |       |          |+--------------+---------+---------+--------------+|
NB. +-------+----------+---------------------------------------------------+
NB.
NB. Script provided by Devon McCormick
NB. 
NB. requires Excel spreadsheet jmacros.xls - see xlutil script.

require '~Source\examples\ole\excel\xlutil.ijs'

CLIPMAX=: 68000                         NB. Approx. max. FP nums clipboard holds

extractXLData=: 3 : 0
NB.* extractXLData: extract all data from Excel spreadsheet.
   xlopen ''
   flnm=. fullname_j_ y.                NB. OLE requires full disk name
   flnm=. quoteIfSp flnm
   xlcmd 'wb open ',flnm                NB. Open workbook
   xlid 'ws' [ xlget 'temp worksheets'  NB. Set and name worksheet
   wscount=. ".xlget 'ws count'         NB. Count how many sheets
   dat=. >get1Worksheet&.>>:i. wscount  NB. Get all sheets, ranges, data
   rc=. wdclipwrite ''                  NB. Clear clipboard to avoid message.
   xlquit ''                            NB. Quit to avoid "Save?" message.
   dat
NB.EG dat=. extractXLData 'C:\EAFE\200211\nyl1112PassiveExecution.xls'
)

get1Worksheet=: 3 : 0
NB.* get1Worksheet: assume workbook already open.
   bkname=. xlget 'temp name' [ xlget 'base activeworkbook'
   bkname=. quoteIfSp bkname
   xlget 'ws item ',":y.                NB. y. is number of worksheet.
   wsname=. 1|.'""',xlget 'temp name'   NB. Quote so spaces in name don't confuse
   bw=. bkname,' ',wsname,' '           NB.  # argumnents passed to jreadr.
   range=. xlusedrange bw               NB. Which cells are populated?
   if. -.+./'ABCDEFGHIJKLMNOPQRSTUVWXYZ'e. toupper range do. NB. No letters means
       range=. ;('$',&.>'A';'IV'),&.>(range='$')<;.1 range   NB.  all columns.
   end.
   if. -.+./'0123456789'e. range do.    NB. If no numerals in range, e.g. $A:$U,
       whc=. range i. ':'               NB.  it means all rows; however, we can't
       range=. (whc {. range),'1',(whc}.range),'10000' NB. read all rows (because
   end.        NB.  of limits of clipboard)
   rc=. wdclipwrite ''                  NB. Clear clipboard to avoid message.
   if. ':'e. range do.                  NB. Is complete range?
       nsr=. translateXLRange range     NB. numeric range notation
       tsz=. */nrc=. >:|-/nsr           NB. Total size of data; num rows, cols.
       rpi=. <.(0{nrc)*<./1,CLIPMAX%tsz NB. Rows per iter'n clipb'd can handle
       sro=. 0                          NB. Starting row offset, column placeholder
       dat=. nrc$a:                     NB. Allocate to final size: faster than appends
       while. sro<0{nrc do.             NB. Get groups of rows at a time.
           rpi=. rpi<.sro-~0{nrc        NB. Don't go past end row
           rc=. (sro,0)+0{nsr           NB. Row, col indexes for next data chunk
           rc=. ":rc,rpi,(<1 1){nsr     NB. Next batch of rows for all cols
           tmp=. xlreadr bw,rc          NB. Read rows
           if. rpi~:#tmp do.            NB. Don't know why this happens but
               'corx rpi xv'=. fixSizeMismatch rpi;#tmp     NB. it does.
               if. corx do.             NB. Expand
                   xv=. (sro$1),xv,1$~((#dat)++/0=xv)-sro+#xv
                   dat=. xv exp dat
               else.                    NB. Compress
                   xv=. (sro$1),xv,1$~(#dat)-sro+#xv
                   dat=. xv#dat
               end.
           end.
           tmp=. ((#tmp),1{$dat){.tmp   NB. Ensure widths match
           dat=. tmp (sro+i.rpi)}dat
           sro=. sro+rpi                NB. Advance to next set of rows.
       end.
       dat=. dat#~0 +./ . ~:|:>#&.>dat  NB. Trim empty rows
   else.                                NB. Incomplete range means no data.
       dat=. ''
   end.
   wsname;range;<dat
)

fixSizeMismatch=: 3 : 0
NB.* fixSizeMismatch: if 2 sizes differ, give expansion or compression vec
NB. to fit 1st to 2nd.
   'rpi nrt'=. y.             NB. rows/iteration & number of rows in tmp
   corx=. xv=. 1              NB. Expansion vec if nothing to do.
   if. rpi~:nrt do.
       if. rpi<nrt do.        NB. More rows of tmp than rpi...
           xv=. nrt{.rpi$1
       else.                  NB. Fewer rows of tmp than rpi...
           xv=. rpi{.nrt$1    NB. Actually is compression vec, not expansion
           corx=. 0           NB. Signal compression, not expansion
       end.
       rpi=. nrt
   end.
   corx;rpi;xv
NB.* 'corx rpi xv'=. fixSizeMismatch rpi;#tmp
)

quoteIfSp=: 3 : 0
NB.* quoteIfSp: surround name with '"'s if embedded spaces
   ifsp=. '"'#~' ' e. y.
   flnm=. ifsp,(y.-.'"'),ifsp
)

translateXLRange=: 3 : 0
NB. translateXLRange: translate between Excel range notation and numeric
NB. equivalent, e.g. '$A$1:$J$10' -> 1 1 10 10, or 27 2 43 26 ->
   if. ' '={.0$y. do.         NB. Excel -> numeric
      >translateXLCellNotation&.><;._1 ':',y.
   else.                      NB. numeric (row,col,row,col) -> Excel
      xlnot=. translateXLCellNotation&.>(<"1) 2 2$,y.
      (>0{xlnot),':',>1{xlnot
   end.
NB.EG    (2 2$9 27 99 54) -: translateXLRange '$AA$9:$BB$99'
NB.EG    '$B$1:$Z$10' -: translateXLRange 1 2 10 26
)

translateXLCellNotation=: 3 : 0
NB.* translateXLCellNotation: translate between origin-1 row-col coordinates
NB. and MS Excel spreadsheet cell designation, e.g. $A$1->1 1, or
NB. $AA$3 -> 3 27; or 1 256 -> '$IV$1'.  Does NOT account for Excel
NB. limit of 256 columns and 65536 rows: will translate an unuseable
NB. address like '$XL$99999' but fails after column 728 ('$ZZ')
NB. because limited to 2 alpha digits.
   alph=. ' ABCDEFGHIJKLMNOPQRSTUVWXYZ' [ nums=. '0123456789'
   adrs=. y.                  NB. NO validity checking on address.
   if. ' '={.0$adrs do.       NB. Excel -> numeric
       adrs=. toupper adrs-.' '     NB. Only want ' ' if leading
       whlets=. adrs e. alph            NB.  intro'd by _2{. below.
       whnums=. adrs e. nums
       col=. ((<:#alph),1)+/ . * _2{.alph i. whlets#adrs
       (". whnums#adrs), col  NB. Row, column order is more natural for us.
   else.                      NB. numeric (row, col) -> Excel
       col=. 0 1+(0,<:#alph)#:<:1{adrs
       col=. ' '-.~'$',col{alph
       col, '$',":0{adrs      NB. Excel stores in column-major order.
   end.
NB.EG    1 27 -: translateXLCellNotation '$AA$1'
NB.EG    '$IV$1' -: translateXLCellNotation 1 256
NB. This function is own inverse, e.g.
NB.EG    cells=. '$A$1';'$Z$1';'$AA$2';'$AZ$2';<'$BA$3'
NB.EG    cells -: translateXLCellNotation&.>translateXLCellNotation&.>cells
)




