Range and Variant Array

Help
sparrow
2012-07-18
2013-05-20
  • sparrow
    sparrow
    2012-07-18

    Hi all

    I want to mimic this VBA code in Java using JACOB:

    Dim myArray As Variant
    myArray = Worksheets("Sheet1").range("B2:C17")
    

    I can't work out how to get JACOB to return the Variant array. Returning a single Variant is simple enough, but this has me stumped.

    Any help appreciated.

     
  • sparrow
    sparrow
    2012-07-19

    OK, another day's research leads me to SafeArrays.
    Has anyone passed a SafeArray from Excel to Java using a JACOB SafeArray?

    What I'm trying to do is the following:
    The user is going to select a range of cells, click a button in the toolbar and I need to pass that selected range back to Java as an array for processing.
    The reason for using an array is that we need to read each value in each cell of the selected range. Using something like Application.getSelection() works, but reading the Variant from each cell individually is incredibly resource intensive.

    Any help appreciated.

     
  • motron
    motron
    2012-07-19

    VBA :
    Property Range(Cell1, ) As Range
        schreibgeschützt
        Element von Excel.Worksheet

    I assume you will get a Dispatch for the range to work further on it. (no array of whatever)

    from com.jacob.samples.office.ExcelDispatchTest.main(String):

    Dispatch sheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
    Dispatch a1 = Dispatch.invoke(sheet, "Range", Dispatch.Get,
    new Object { "A1" }, new int).toDispatch(

    );

    so try
    Dispatch myArrayRange = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object { "B2", "C17" }, new int).toDispatch();
    Dispatch.put(myArrayRange , "Value", "3.456");

    have fun

     
  • sparrow
    sparrow
    2012-07-19

    Thanks for the reply. Google eventually led me to these two pages:

    http://www.codeforge.com/read/100972/safearray.java__html

    Object cell = Dispatch.invoke(sheet,"Range",Dispatch.Get,new Object {"A1:D1000"},new int).toDispatch();
    SafeArray array = Dispatch.get(cell,"Value").toSafeArray();

    http://lists.luaforge.net/pipermail/luacom-developers/2009-September/000013.html

      SafeArray a = worksheet.UsedRange.Value
      SafeArray sA = new SafeArray(Variant.VariantString, new int  {0},new int  {….});

    Using a combination of those, I worked out that I can do the following:

            SafeArray array = null;
            try
            {
                array = Application.getSelection().getValue().toSafeArray();
            }
            catch (ComFailException e)
            {
                /* Only 1 cell has been selected. */
            }
            if (array != null)
            {
                Log.debug("dim=" + array.getNumDim());
                Log.debug("start row=" + array.getLBound(1));
                Log.debug("start col=" + array.getLBound(2));
                Log.debug("end row=" + array.getUBound(1));
                Log.debug("end col=" + array.getUBound(2));
            }
    

    We have written some wrappers, so:
    Application.getSelection returns the selected range.
    getValue returns the Variant of the range. This is a Variant array of some sort when a range of cells is selected. I've not looked at what is returned in any detail yet.
    toSafeArray is JACOB code in com.jacob.com.Variant.toSafeArray(). It requires an array of Variants otherwise it throws ComFailException.

    That's where I'm up to right now. Next up is working through the array to see if it contains what I need. :-)