On 9/28/2010 16:35, Cox, Ed wrote:
> I successfully wrote a tool for running SQL queries on a server and dumping
> the results into Excel (2007) spreadsheet using OLE Automation. It works
> great except for one thing...the xlutil.ijs script implements the xlwriter
> verb to paste the data into the spreadsheet. Thus a conflict occurs if I
> want to use copy and paste on my PC while the tool is running.
>
> In studying OLE Automation I found an example of programming via C# that
> appears to write an array directly to a spreadsheet (see code fragment below):
> // Create an array to multiple values at once.
> string[,] saNames = new string[5,2];
>
> saNames[ 0, 0] = "John";
> saNames[ 0, 1] = "Smith";
> saNames[ 1, 0] = "Tom";
> saNames[ 1, 1] = "Brown";
> saNames[ 2, 0] = "Sue";
> saNames[ 2, 1] = "Thomas";
> saNames[ 3, 0] = "Jane";
> saNames[ 3, 1] = "Jones";
> saNames[ 4, 0] = "Adam";
> saNames[ 4, 1] = "Johnson";
>
> //Fill A2:B6 with an array of values (First and Last Names).
> oSheet.get_Range("A2", "B6").Value2 = saNames;
>
> So my question is, does anyone know if this approach works in J? Or is there
> some reason that we are forced to use paste from the clipboard?
>
> Ed Cox
> Principal Data Analyst
> MedMined(tm) Services
>
>
>
> _________________________________________________
>
> 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
As far as I can tell, the J wd/ole interface has limited support for
constructing the variant data type needed to set the individual cell Values of
an Excel Range. The OLE argument that gets sent from J to Excel always seems
to
look like a single element to Excel.
There does not seem to be any provision for transforming a J array into the
type
of variant/safearray argument that some non-J code uses for setting multiple
distinct Values in an Excel Range.
The closest I could get to passing an array from J to Excel in pure J was to
use
this type of approach:
xlopen ''
xlcmd 'wb add'
xlget 'temp worksheets'
xlid 'ws'
xlget 'ws item sheet1'
xlid 'sh1'
booga1=:'Test 1 2 3'
booga2=:'4 3 2 fee'
xlget 'sh1 cells ',": 1,2
xlset 'temp value "',booga1,'"'
xlget 'sh1 cells ',": 2,2
xlset 'temp value "',booga2,'"'
xlget 'sh1 range b1:b2'
xlcmd 'temp texttocolumns , 1 1 0 0 0 0 1'
This sends the data from J to Excel as individual rows and then expands the
columns using texttocolumns with a space delimiter.
Other possible approaches might be to use the pcall interface or to create your
own DLL to perform the J to OLE conversions you need.
--
Cheers,
David Mitchell
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
