OK, I have put together a script to show both the version I have working (using
clipboard & paste) and the version
that does not work (using pure OLE Automation). See the prerequisites listed
at the top of the script and the
requirement for the target spreadsheet. The script is initially set up to use
the paste version so you can test that
everything else works. To switch it to the OLE version just look in the verb
writeData2WB for these two lines:
data xlwriter2 towhr
NB. data xlwriter3 destRange
and move the NB. to the other line like this:
NB. data xlwriter2 towhr
data xlwriter3 destRange
To use it, create sample array to send to the spreadsheet such as:
TestVar=:<each i.10 4 NB. my version of clipfmt can handle level 2 boxed
arrays
and then execute it with:
writeData2WB TestVar;1 2;'h:\ed.cox\Shared\XLReportGen\Test.xlsx';3
Obviously, replace the path to the target file to point to your own target file.
Given what I am seeing I am becoming convinced that the OLE method as I have
here can not work because no
matter what I do the entire test variable gets sent to each cell in the target
range. I think I am going to have to
research using an API.
-Ed
----script begins below----------------
NB. TestExcelOLE.ijs
NB. There are 3 prerequisites to make xlutil work for Excel 2007 xlsx files:
NB. 1) Create a jmacros.xlsm in ~Examples\ole\excel\ by:
NB. a) Open a new workbook and Save As jmacros.xlsm
NB. b) Open Visual Basic in Excel and create Module1 and
NB. paste in the text from ~Examples\ole\excel\jmacros.txt
NB. c) Save the workbook and close
NB. 2) In ~Examples\ole\excel\xlutil.ijs find this line:
NB. JMACROS=: jpath '~system\examples\ole\excel\jmacros.xls'
NB. and change it to:
NB. JMACROS=: jpath '~system\examples\ole\excel\jmacros.xlsm'
NB. 3) In ~Examples\ole\excel\xlutil.ijs find this line in verb xlopen:
NB. wd 'cc xl oleautomation:excel.application'
NB. and change it to:
NB. wd 'cc xl oleautomation:Excel.application'
NB. This example assumes you have created a blank target Excel file
NB. with three worksheets: Sheet1, Sheet2 & Sheet3, with Sheet1
NB. selected, and then saved. The example will attempt to write
NB. to Sheet3 (the 3rd sheet). Having Sheet1 selected to start
NB. with will test the scripts ability to activate the target sheet.
require 'winlib strings'
require '~Examples\ole\excel\xlutil.ijs'
NB.* fixXLFileNm: ensure we have properly qualified and formatted Excel file
name.
fixXLFileNm=: 3 : 0 NB. Borrowed from OLEExcel.ijs
flnm=. dltb y NB. Remove excess spaces.
flnm=. flnm,>(-.(toupper _5{.flnm)-:suf)#suf=. '.XLSX'
if. -.':'e.flnm do. NB. OLE requires disk name: is one
drv=. guessDriveOfFile flnm=. y NB. already specified?
flnm=. drv,flnm NB. Drive:\path
end.
flnm=. quoteIfSp flnm NB. Quote name if there are spaces in
it.
NB.EG flnm=. fixXLFileNm y
)
writeData2WB=: 3 : 0 NB. Borrowed from OLEExcel.ijs
'data whr updfl wsnum'=. y NB. Line to write, row&col, file name,
worksheet num.
xlid 'wb' [ xlget 'base workbooks' [ xlopen ''
xlcmd 'wb open ',fixXLFileNm updfl
wbnm=. '"',(xlget 'temp name'),'"' NB. Workbook name
xlid 'ws' [ xlget 'temp worksheets' NB. Set and worksheet name
wscount=. ".xlget 'ws count' NB. Count how many sheets
xlget 'ws item ',":wsnum NB. Sheet by number.
xlid 'sh1'
xlcmd 'sh1 activate'
wsnm=. '"',(xlget 'sh1 name'),'"' NB. Set where (Book, sheet,
towhr=. wbnm,' ',wsnm,' ',":whr NB. row, col) to write line.
destRange=.'$'(~:#])(XLCellXlate whr),':', XLCellXlate ($data)+<:whr
data xlwriter2 towhr
NB. data xlwriter3 destRange
xlexit '' NB. Exit and save workbook.
NB.EG writeData2WB TestVar;1 2;'h:\ed.cox\Shared\XLReportGen\Test.xlsx';3
)
NB. Custom version of xlwriter that uses custom ver of clipfmt
NB. x=level 1 or 2 boxed data to write
NB. y='"book" "sheet" row column'
xlwriter2=: 4 : 0
if. 0=*/$x do. x=. (1,_1{.$x)$,<'<No Data>' end.
s=. _2{.1 1,$x
wdclipwrite clipfmt2 x
7 xljmacro 'jwriter ',y,' ',":s
)
NB. Custom version of xlwriter that uses true OLE Automation instead of pasting
from clipboard
NB. x=level 1 or 2 boxed data to write
NB. y='row column'
xlwriter3=: 4 : 0
if. 0=*/$x do. x=. (1,_1{.$x)$,<'<No Data>' end.
xlget 'sh1 range ',y NB. wd 'oleget me osheet range ', y
xlid 'range' NB. wd 'oleid me range'
xlset 'range value *', clipfmt2 x NB. wd 'oleset me range value *', clipfmt2 x
)
clipfmt2=: 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.
if. 1=L. y do. NB. Level 1 box, trailing spaces will go to Excel
y=. ,&TAB @ ": &.>y
y=. ;,&c...@}: &.><@;"1 y
else. NB. Level 2 box, trailing spaces can have been be pre-trimmed
from each cell
'r c'=. $y
y=. >@;": each>@,((r*c+1)$(|.2,c-1)#1j1 1)#!.(<<TAB) (,y,. <<CRLF)
end.
elseif. 1 do.
y=. ;,&CRLF @ ": &.><"1 y
y=. '-' (I. y='_') } y
y=. TAB (I. y=' ') } y
end.
y
)
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.
XLCellXlate=: 3 : 0 NB. Borrowed from OLEExcel.ijs
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
)
NB. End of script
_________________________________________________
This message is for the designated recipient only and may contain privileged,
proprietary
or otherwise private information. If you have received it in error, please
notify the sender
immediately and delete the original. Any other use of the email by you is
prohibited.
Dansk - Deutsch - Espanol - Francais - Italiano - Japanese - Nederlands - Norsk
- Portuguese
Svenska: www.carefusion.com/legal/email
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
