This code is very simple and powerful
We Japan APL Association used this more than 10 years
in this script written how to make jmacros.xls from jmacros.txt
m.shimura
NB. ---------cut here------------
NB. ===============================================
NB. get EXCEL use OLE (xlutil.ijs)
NB. New Version /7 Aug.2009 /written by TAKEUCHI Juichiro
NB. slightly modified by SHIMURA Masato
NB. -------------------------------------
NB. make jmacros.xls <-- jmacros.txt
NB. on EXCEL open (tool-> macro->VisualBasic Editor)
NB. 1)on VBEditor (read jmacros.txt) // show nothing
NB. 2)--> close VBEditor
NB. 3) save as (on EXCEL) jmacros.xls
NB. ----------------------------------
NB. if EXCEL cannot find jmacros.xls , regist macro
NB. 4) regist macro on EXCEL (Tool Addin--> search jmacros and check it)
NB. -------------------------
NB. * getexcel ''
NB. awake EXCEL and auto popup
NB. alway using EXCEL sheet_name <TRY>
NB. so asked using sheet TRY or not --> reply YES
NB. -----------------------------
require 'files stdlib'
require jpath '~system\examples\ole\excel\xlutil.ijs'
NB. adjust your directry
getexcel=: 3 : 0
NB. J Client use ole to EXCEL Sever
NB.Filename of Book is TEST and name of Sheet is TRY
NB. Ussage: getexcel ''
wd 'reset'
xlopen ''
xlshow ''
xlcmd 'wb add'
xlcmd 'temp saveas TEST'
xlget 'temp worksheets'
xlid 'ws'
xlget 'ws item sheet1'
xlid 'sh1'
xlset 'sh1 name TRY'
xlcmd 'sh1 activate'
)
getcell=:3 : 0
xlget 'sh1 cells ',":y
xlget 'temp value'
)
putcell=:4 : 0
xlget 'sh1 cells ',":y
xlset 'temp value ',":x
)
datain=:3 : 0
NB. a=.datain 1 1 5 5 (1 1) denotes a location; (5 5) denotes a size
(_2{.y)$,0".>xlreadr 'TEST.xls TRY ',":y
)
dataout=:4 : 0
NB. c dataout 6 6 (6 6) denotes a location of Excell Files
x xlwriter 'TEST.xls TRY ',":y
)
charin=:3 : 0
NB. If you want to read characters, Use the "charin" function
NB. a=.charin 1 1 5 5 (1 1) denotes a location; (5 5) denotes a size
NB. Results are expressed by a Boxed Form
xlreadr 'TEST.xls TRY ',":y
)
readtable=:[:>0:".&.>[:cutopen([: 1!:1 ])-.(13{a.)"_
writetable=:4 : '(,(":x),"1 CRLF)1!:2 y'
NB. Examples; makes a matrix file after reading
NB. from Excell File "Sheet TRY" in "TEST.xls"
NB. a=.datain 3 2 32 13
NB. a writetable<'c:\j503\temp\test.dat'
NB. -------cut here------------------------------------
>
> Message: 6
> Date: Fri, 1 Oct 2010 09:12:18 +0800
> From: bill lam <[email protected]>
> Subject: Re: [Jprogramming] Write array to Excel via OLE Automation
> without using Paste?
> To: [email protected]
> Message-ID: <[email protected]>
> Content-Type: text/plain; charset=us-ascii
>
> I cannot test the scripts, just a few comments.
> 1. macro is default disabled, so that it should be avoided unless really
> needed.
> 2. I don't use xlutil.ijs, and I think using it will not make program more
> portable either.
> 3. instead of setting to <no data> one may clear the cells using
> wd 'olemethod me range clearcontents'
> 4. excel may interpret some text (like 2e3) as number, to force it as text,
> it need to set format,
> wd 'oleset me range numberformat @'
> wd 'oleset me range value *', x
> 5. I googled found that it is now preferable to use value2 than value
>
> --
> regards,
> ====================================================
> GPG key 1024D/4434BAB3 2008-08-24
> gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3
>
>
> ------------------------------
>
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
>
> End of Programming Digest, Vol 61, Issue 2
> ******************************************
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
