For large amount of data and speed is important, then use the range
copy/pastespecial with clipfmt.
A simple demo.
f1=: 3 : 0
wd 'clipcopy *', clipfmt 10000 26$'abc';1.23;123
(wd ::0:) 'psel xlauto;pclose'
wd 'pc xlauto'
wd 'cc me oleautomation:excel.application'
wd 'oleget me base workbooks;oleid me wb'
wd 'olemethod me wb open *', 'c:\test.xls'
wd 'oleget me base activeworkbook;oleid me awb'
wd 'oleget me awb activesheet;oleid me aws'
wd 'oleget me aws range a1:z10000'
wd 'olemethod me temp select'
wd 'olemethod me temp pastespecial'
wd 'olemethod me awb save'
wd 'olemethod me awb close'
wd 'olemethod me base quit'
wd 'pclose'
)
Beware if J oleautomation will limit the amount of data or not.
I guess you have to adapt clipfmt for negative numbers.
Leigh J. Halliwell wrote:
Dear J Forum:
First, thank you, Devon, for your answer. I will experiment with your Excel
script. However, I'm wondering whether it might be simpler to convert my
rank-2 boxed array to characters, e.g., with clipfmt, whether or not I pass
it to the clipboard (Question 2 below)
About this I have two questions:
1) clipfmt does not change the J negative sign to the usual hyphen, so Excel
can't recognize negative numbers. I can adapt clipfmt to make the
substitution; but is there already a subroutine or primitive that does this?
2) should I pass the formatted array to the clipboard (wdclipwrite), or does
an OLE subroutine (e.g., jcmd, get, getB, getM) allow me to pass it directly
to the specified cell of my active worksheet?
Thanks for the help.
Sincerely,
Leigh
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Devon McCormick
Sent: Thursday, March 22, 2007 12:42 AM
To: Programming forum
Subject: Re: [Jprogramming] Passing a J Array to Excel
Does something like this work (from jmacros.xls):
data xlwriter 'book1 sheet1 1 1 2 3'
to write 2x3 data to sheet1 of book1 starting at position (1,1);
I believe you can elide the second pair of numbers to have it
get the size from the data. In fact this may be necessary.
For example:
load 'OLE' NB. My OLEExcel.ijs script
coinsert 'excel'
]data=: 2 3$'Here is';'some text';'combined with numbers';1;2;3
+-------+---------+---------------------+
|Here is|some text|combined with numbers|
+-------+---------+---------------------+
|1 |2 |3 |
+-------+---------+---------------------+
xlcmd 'wb open C:\Temp\databook.xls' [ xlopen ''
]bn=. xlget 'temp name' [ xlget 'base activeworkbook'
databook.xls
data xlwriter_excel_ 'databook.xls Sheet1 2 2'
1
xlexit''
Trying it just now, it looks like this works OK.
On 3/22/07, Leigh J. Halliwell <[EMAIL PROTECTED]> wrote:
Dear J Forum:
I'd like to transfer a rank-2 boxed array from J to an Excel
worksheet. The
boxes of the J array may contain numeric, character, and empty data, e.g.:
JArray =. 2 3 $ 0; 'word'; ] a:, 'abc'; _4; 3.14
Apparently 'jcmd' and the various 'get' functions do not allow for mixed
types of data, nor for empty boxes. I've managed to create the following
VBA subroutine that transfers the array one cell at a time, but it is very
slow. Moreover, if the Excel range is being overwritten, the aces will
not
clear what's already in the cells. Is there a better solution?
Sub GetJTable(Jvar As String, r As Integer, c As Integer)
'A J Table is a boxed array of rank 2
'Writes the array to the current worksheet beginning at cell r,c
Dim i, ridx, j, cidx, m, n As Integer
js.Do "TableDim =. 2 {. (,& 1 1) $" & Jvar
js.Do "table =. TableDim $ ," & Jvar
m = jcmd("0{TableDim"): n = jcmd("1{TableDim")
js.Do "tblsel =. {&table"
ridx = r - 1
For i = 0 To m - 1
ridx = ridx + 1: cidx = c - 1
js.Do "rowsel =. { & (tblsel " & i & ")"
For j = 0 To n - 1
cidx = cidx + 1
js.Do "x =. > rowsel " & j
js.GetB "x", v
ActiveSheet.Cells(ridx, cidx).Value = v
Next j: Next i
End Sub
Would the solution be simpler if, except for column headings in the first
row, each column were exclusively numeric or character (with aces
allowed)?
Thanks for helping.
Sincerely,
Leigh
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
--
regards,
bill
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
