Scripts/xlutil

This has been dropped from the current (8.06) J installation but may still be useful.

NB. Microsoft Excel OLE utils (J as client, Excel as server)
NB.
NB. These utils create:
NB.   xlauto      parent window for Excel OLE automation object
NB.   xl          child control for Excel OLE automation object
NB.   wb          workbooks object
NB.
NB. main definitions:
NB.   xlopen      create Excel OLE automation object
NB.   xlshow      show/hide Excel OLE automation object
NB.   xlexit      exit Excel OLE automation object (saves)
NB.   xlquit      quit Excel OLE automation object (no save)
NB.
NB.   xlget       cover for oleget     - get object
NB.   xlset       cover for oleset     - set object parameter
NB.   xlcmd       cover for olemethod  - invoke method
NB.   xlid        cover for oleid      - assign id to current position
NB.
NB. read/write definitions. In each case, if "book sheet" is not given,
NB. it defaults to the current book and sheet.
NB.   xlread      read cell
NB.                 xlread 'book sheet row col'
NB.                 xlread 'row col'    NB. default book sheet
NB.   xlreadr     read range
NB.                 xlreadr 'book sheet row col rows cols'
NB.   xlwrite     write cell
NB.                 value xlwrite 'book sheet row col'
NB.   xlwriter    write range
NB.                 values xlwriter 'book sheet row col'
NB.
NB.   xlsetchart  set chart range
NB.
NB.   xlfmt       format numeric data as character delimited by LF
NB.   xlunfmt     unformat numeric data
NB.
NB. utilities:
NB.   xljmacro    run macro from jmacros.xls
NB.   xlwbws      return active workbook, worksheet
NB.   xlbox       box argument
NB.
NB. utilities from standard J library:
NB.   clipfmt     format data for clipboard
NB.   clipunfmt   unformat data read from clipboard
NB.
NB. requires Excel spreadsheet jmacros.xls. If necessary,
NB. create this using jmacros.txt.
NB. See: system\examples\ole\excel\excel.txt

JMACROS=: jpath '~system\examples\ole\excel\jmacros.xls'

NB. =========================================================
xlget=: wd@('psel xlauto;oleget xl '&,)
xlset=: wd@('psel xlauto;oleset xl '&,)
xlcmd=: wd@('psel xlauto;olemethod xl '&,)

xlid=: wd@('psel xlauto;oleid xl '&,)

NB. =========================================================
NB. xlexit
xlexit=: 3 : 0
try. xljmacro 'jexit' catch. end.
try. wd 'psel xlauto;pclose' catch. end.
empty''
)

NB. =========================================================
xlfmt=: 3 : 0
y=. ;,&LF@": each ,y
'-' (I. y='_') } y
)

NB. =========================================================
xlunfmt=: 3 : 0
,".;._2 y
:
(x&".);._2 y
)

NB. =========================================================
NB. xlgetobj   - get object given full name
xlgetobj=: 3 : 0
r=. ''
q=. <;._2 y,'.'
w=. >0{q
wb=. w,' ',>1{q
q=. 2}.q
while. #q do.
  d=. >0{q
  if. '!'=d do.
    r=. r,'olemethod ',wb,' item ',1}.d,';',LF
  else.
    r=. r,'oleget ',wb,' ',d,';',LF
  end.
  wb=. w,' temp'
  q=. 1}.q
end.
r
)

NB. =========================================================
NB. xljmacro
NB. [#of arguments] xljmacro 'argumentlist'
NB. defaults book and sheet
xljmacro=: ''&$: : (4 : 0)
if. #x do.
  arg=. xlbox y
  if. x=2+#arg do.
    bs=. '"',&.>(xlwbws ''),&.>'"' NB. Book & Sheet names can include ' '
    arg=. ({.arg),bs,}.arg
  end.
  if. x~:#arg do.
    wdinfo 'Excel OLE';'Invalid number of arguments to ',":,>{.arg
    return.
  end.
  arg=. ;: ^:_1 arg
else.
  arg=. y
end.
jm=. JMACROS }.~ >: JMACROS i: PATHSEP_j_
xlcmd 'base run ',jm,'!',arg
)

NB. =========================================================
NB. xlopen   - create Excel OLE automation object
NB. creates: parent to hold oleautomation child:
NB.          excel application object (loads Excel)
NB.          workbooks object as 'wb'
NB. loads jmacros.xls (create this if need be).
xlopen=: 3 : 0
if. -. 1 e. ('xlauto',LF) E. wd 'qp' do.
  if. 0 = 1!:4 :: 0: <JMACROS do.
    wdinfo 'xlopen';'First create spreadsheet ',JMACROS
    return.
  end.
  wd 'pc xlauto'
  wd 'cc xl oleautomation:excel.application'
  wd 'oleget xl base workbooks;oleid xl wb'
  wd 'olemethod xl wb open ',quoteIfSp JMACROS
end.
empty''
)

NB. =========================================================
NB. xlread  'book sheet row col'
xlread=: 3 : 0
5 xljmacro 'jread ',y
)

NB. =========================================================
NB. xlreadr 'book sheet row col rows cols'
xlreadr=: 3 : 0
7 xljmacro 'jreadr ',y
clipunfmt wdclipread''
)

NB. =========================================================
NB. xlquit
xlquit=: 3 : 0
try. xljmacro 'jquit' catch. end.
try. wd 'psel xlauto;pclose' catch. end.
empty''
)

NB. =========================================================
NB. xlsetchart 'chart sheet!range'
xlsetchart=: xljmacro@('jsetchart '&,)

NB. =========================================================
NB. xlshow '' or xlshow 1  -  make visible
NB. xlshow 0  -  hide
xlshow=: 3 : 0
y=. {.y,1
xlset 'base visible ',":y
)

NB. =========================================================
NB. xlusedrange return range of cells actually used on sheet.
NB. for example: xlusedrange 'book sheet' DM 2002 11 13
xlusedrange=: 3 : 0
3 xljmacro 'jusedrange ',y
)

NB. =========================================================
NB. xlwbws ''     return active workbook, worksheet
xlwbws=: 3 : 0
xlget 'base activeworkbook'
b=. xlget 'temp name'
xlget 'base activesheet'
s=. xlget 'temp name'
b;s
)

NB. =========================================================
NB. value xlwrite 'book sheet row col'
xlwrite=: 4 : 0
6 xljmacro 'jwrite ',y,' *',,":x
)

NB. =========================================================
NB. values xlwriter 'book sheet row col rows cols'
NB. values is a boxed or numeric array or rank 0, 1 or 2
NB.
xlwriter=: 4 : 0
s=. _2{.1 1,$x
wdclipwrite clipfmt x
7 xljmacro 'jwriter ',y,' ',":s
)

NB. =========================================================
NB.*clipfmt v format data for clipboard
NB. format array of rank 0 1 or 2 for clipboard.
NB. columns are separated by TAB, rows by CRLF.
clipfmt=: 3 : 0
if. 0 e. $y do. '' return. end.
t=. 3!:0 y
if. 2=t do.
  y=. ,y,"1 CRLF
elseif. 32<:t do.
  y=. ,&TAB @ ": &.>y
  y=. ;,&CRLF@}: &.><@;"1 y
elseif. 1 do.
  y=. ;,&CRLF @ ": &.><"1 y
  y=. '-' (I. y='_') } y
  y=. TAB (I. y=' ') } y
end.
y
)

NB. =========================================================
NB.*clipunfmt v unformat data read from clipboard
NB. returns boxed matrix from clipboard result,
NB. recognizing TAB and CRLF as separators.
NB. characters are not converted to numbers.
NB. note this is not a true inverse of clipfmt.
NB. e.g. try:  clipunfmt clipfmt i.5 6
clipunfmt=: (<;._2~ e.&(9 10{a.));.2 @ toJ

NB. =========================================================
NB.*xlbox v box argument
xlbox=: 3 : 0
whs=. 8 9 10 13 32{a.
del=. 197 255{a.
dat=. ' ',y
msk=. ~:/\ dat e. del
mqt=. 2: +./\ 0: , 2: | +/\ @ (=&'"')
mquote=. -. mqt dat
msk=. mquote *. msk
ndx=. 1 i.~ msk < dat='*'
end=. < }. ndx }. dat
dat=. ndx{.dat
msk=. (ndx {. mquote) *.(ndx {. msk) < dat e. whs
dat=. (msk <;._1 dat) , end
a: -.~ dat -. each <del
)

NB.* quoteIfSp: surround name with '"'s if embedded spaces
quoteIfSp=: ((]-.&'"') (] , ,) '"' #~ ' ' e. ])
quoteIfSp_test_=: 3 : 0
   ans=. '"one space"';'NOSPACE';'"Spaces but already quoted"'
   assert. ans-:quoteIfSp&.>'one space';'NOSPACE';'"Spaces but already quoted"'
)