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
