Scripts/xlutil
< Scripts
Jump to navigation
Jump to search
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"' )