Re: Multidimensional array as parameter

Hello,

I am not a (very) experienced developer with Jawin myself, but I do have a
suggestion for you that you may find helpful. Often, when the Jawin API
doesn't support some functionality I need, I'll create a new Visual Basic
project (assuming you have Visual Basic 6.0 or Visual Studio or otherwise)
that does as much native-side work as possible, wrap that into a function or
subprocedure, and expose that in an ActiveX DLL which depends on
MSVBVM60.dll (the Visual Basic virtual machine) plus any other references
you're using (in your case, Excel's COM dll.)

Once you've accomplished that,  just use System.loadLibrary for MSVBVM60.dll
or include it in your classpath; then generate Jawin wrappers for your
simple COM DLL you've created in VB. This can be done in any COM language,
but I like VB because you can use a VBA.Collection to wrap almost anything,
including a multidimensional array.

1. Generate a COM wrapper for the _Collection dispatch and the Collection
CoClass from the Jawin code generator. This object is in MSVBVM60.dll
(invariably this is in %windir%\system32 for any useful Windows system since
win98.)

2. Since the Collection class encapsulates VB Objects (which translate
directly to Java Objects) you should be able to use Java to instantiate a
single _Collection instance, and use its Add method natively to add
one-dimensional Java arrays to it. As I understand it, Jawin SHOULD
automatically do any necessary translation from Java arrays into VB arrays
before passing the members of the Collection, which thus far could be ANY
java.lang.Object, into the native COM framework. Make sure however that any
types you use in the one-dimensional arrays are either primitives, String,
or a native type that you already have a Jawin wrapper for.

3. Pass this _Collection as one of two parameters to your Visual Basic DLL.
The other parameter could be the Excel.Worksheet object you want to add the
data to, and any other locational info you need (for instance, the top-left
Cell of the range to fill.)

4. Your implementation of the Visual Basic DLL will unwrap the Collection as
a one-dimensional set of one-dimensional arrays; by definition, a 2d matrix.
Since this is all done on the native side with a single Jawin call, there is
no JNI overhead involved in the iterative filling of the spreadsheet -- your
only performance hit might be a slight one from using the Collection object,
which is a LITTLE hefty. However, I suspect that if you are using huge
amounts of data, the runtime of using an optimized native code versus
hundreds of Jawin calls is decidedly in favor of the native approach. If you
wrote this using C++ linked to Excel rather than the Visual Basic VM, you
could get significantly more performance, too.

I'm not sure in particular about the assumption I make in point #2, that
Jawin will convert the Java arrays to VB arrays, but I believe it will do
so. If not, you may be able to subclass VBA.Collection (C++ may be required
here) to be strongly-typed to accept only arrays as its members, not generic
VB Objects. This would force Jawin to generate Java code that makes the JNI
bridge more obvious to Jawin at runtime.

If I'm totally off base here I hope I'm told otherwise -- my use of Jawin is
ongoing and I may be faced with this exact problem in the future.

Thanks,
Sean

-----Original Message-----
From: Discussion of Java/Win32/COM integration with Jawin
[mailto:[email protected]] On Behalf Of Frank Bos
Sent: Wednesday, January 04, 2006 10:49 AM
To: [email protected]
Subject: [JAWIN] Multidimensional array as parameter

Hi,

I'm trying to use Jawin to fill an Excel sheet using a Java application.
For my application it is important to use "bulk" methods to set the values
for all cells in a range, because otherwise it will get too slow.

According to the Excel api, it should be possible to use a two-dimensional
array to set the "Value" property of a "Range" object. Unfortunately I
haven't been able to get this to work using Jawin. If I use a (one
dimensional) array, then it is possible to set a complete row of values
using a single Jawin call, but that is not good enough because my
application will output many rows to Excel (and not so many columns).

Example code:

public class JawinTest {
  public static void main(String args[]) {
    try {
      Ole32.CoInitialize();

      DispatchPtr app = new DispatchPtr("Excel.Application");
      app.put("Visible", true);
      DispatchPtr books = (DispatchPtr)app.get("Workbooks");
      DispatchPtr book = (DispatchPtr) books.invoke("Add");
      DispatchPtr sheets = (DispatchPtr)book.get("Worksheets");
      DispatchPtr sheet = (DispatchPtr)sheets.get("Item", new Integer(1));

      // Create a range which contains 3 cells
      DispatchPtr range1 = (DispatchPtr)sheet.get("Range", "A1:A3");

      // This will fill cells A1, A2 and A3 with "TestValue"
      range1.put("Value", "TestValue");

      // The three cells get value "TestValue1".
      // The other values get ignored!
      DispatchPtr range2 = (DispatchPtr)sheet.get("Range", "B1:B3");
      range2.put("Value2", new String[]
         {"TestValue1", "TestValue2", "TestValue3"});

      // Try a 2-dimensional array instead. Cells remain empty.
      DispatchPtr range3 = (DispatchPtr)sheet.get("Range", "C1:C3");
      range3.put("Value", new String[][] {
        {"TestValue1"},
        {"TestValue2"},
        {"TestValue3"}});

      // Maybe this way? Nope, doesn't work either.
      range3.put("Value", new String[][] {  {"TestValue1", "TestValue2",
"TestValue3"}});

      // Try filling a row instead. That works!
      // The three cells get different values.
      DispatchPtr range4 = (DispatchPtr)sheet.get("Range", "D1:F1");
      range4.put("Value", new String[]
        {"TestValue1", "TestValue2", "TestValue3"});

      Ole32.CoUninitialize();
    } catch (COMException exc) {
      exc.printStackTrace();
    }
  }
}

Am I doing something wrong or does Jawin have no support for 2d arrays? It
would be great if anyone can help me with this!

-- Frank

NOTICE: This communication, including any attachment, contains information
that may be confidential or privileged,
and is intended solely for the entity or individual to whom it is addressed.
If you are not the intended recipient, please notify
 the sender at once, and you should delete this message and are hereby
notified that any disclosure, copying, or distribution
of this message is strictly prohibited. Nothing in this email, including any
attachment, is intended to be a legally binding signature.