Up to Discussions in JExcel API

This Question is Answered

1 "correct" answer available (4 pts) 2 "helpful" answers available (2 pts)
6 Replies Last post: Dec 12, 2007 2:18 AM by Peter Whittaker  
Peter Whittaker  
Peter Whittaker
16 posts since
Oct 4, 2007
Currently Being Moderated

Nov 8, 2007 11:13 AM

Remove my Native Shapes and VB macros

First of all can I say how pleased I have been with all the support from Team Dev. This support really is what makes the difference in de-risking projects for us developers. Thank you.

 

Having managed now to create native Dropdown lists with associated VBA macros acting as listeners, I need to be able to search for all Dropdowns on a worksheet and remove them in order to prevent an accumulation of such objects over multiple saves of the Excel workbook.

 

Ive looked at uiWorksheet.getPeer().getShapes() but the return class is T which I presume is an obsfucated class.

 

Alternaitvely if I could search for a named dropdown list returning a Shape, I presume I could then delete it. However Im unsure how to find a named Shape.

 

 

Many thanks in advance for your help

 

 

 

Pete

Igor Novikov TeamDev Ltd.
Igor Novikov
225 posts since
Apr 24, 2006
Currently Being Moderated
1. Nov 8, 2007 7:44 PM in response to: Peter Whittaker
Re: Remove my Native Shapes and VB macros

Hi Peter,

 

To avoid obfuscation issue you can try using jexcel library with complete MS Excel stubs:

 

ftp://ftp.teamdev.com/updates/jexcel-1.1-full.jar

 

To remove all shapes on worksheet try using following code:

 


Shapes shapes = uiWorksheet.getPeer().getShapes()
int num = (int) shapes.getCount().getValue();
for (int i = 0; i < num; i++) 
{
     shapes.item(new Variant(i + 1)).delete();
}

 

To check shape name, getName() method can be used:

 


shapes.item(new Variant(i + 1)).getName();

 

I hope this helps.

 

Sincerely,

 

Igor Novikov

Peter Whittaker  
Peter Whittaker
16 posts since
Oct 4, 2007
Currently Being Moderated
2. Nov 30, 2007 5:09 PM in response to: Igor Novikov
Re: Remove my Native Shapes and VB macros

Hi

 

I have included the jexcel-1.1-full.jar.

On executing the following snipett of code:

 

try{

                    // First remove all objects created by CtXL

                    Shapes shapes = uiWorksheet.getPeer().getShapes();

                    int num = (int) shapes.getCount().getValue();

                    for(int i = 0; i < num; i++)

                         shapes.item(new Variant(i + 1)).delete();

 

                    workbook.save();

               }

               catch(Exception e)

               {

                   e.printStackTrace();

               }

 

 

I get the following exception:

 

com.jniwrapper.win32.com.ComException: COM object method returns error code: 0x800401F0; CO_E_NOTINITIALIZED (CoInitialize has not been called.)

        at com.jniwrapper.win32.com.impl.IUnknownImpl.invokeStandardVirtualMethod(SourceFile:681)

        at com.jniwrapper.win32.excel.impl._WorksheetImpl.getShapes(_WorksheetImpl.java:602)

        at com.ym.ui.ctx.CtXLView.saveWorkbook(CtXLView.java:628)

        at com.ym.ui.ctx.ContextView$ActionFactory$saveWorkbookAction.actionPerformed(ContextView.java:539)

        at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:324)

        at com.ulcjava.base.shared.internal.ReflectionUtilities.invokeMethod(ReflectionUtilities.java:48)

        at com.ulcjava.base.application.ULCProxy.dispatchEvent(ULCProxy.java:172)

        at com.ulcjava.base.application.ULCProxy.processActionEvent(ULCProxy.java:153)

        at com.ulcjava.base.application.ULCProxy$ULCProxyDispatcher.processActionEvent(ULCProxy.java:16)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:324)

        at com.ulcjava.base.shared.internal.ReflectionUtilities.invokeMethod(ReflectionUtilities.java:48)

        at com.ulcjava.base.application.ULCProxy.a(ULCProxy.java:173)

        at com.ulcjava.base.application.ULCProxy.handleEvent(ULCProxy.java:33)

        at com.ulcjava.base.application.ULCProxy$ULCProxyDispatcher.handleEvent(ULCProxy.java:11)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:324)

        at com.ulcjava.base.shared.internal.ReflectionUtilities.invokeMethod(ReflectionUtilities.java:48)

        at com.ulcjava.base.application.ULCProxy.d(ULCProxy.java:118)

        at com.ulcjava.base.application.ULCProxy.handleRequest(ULCProxy.java:123)

        at com.ulcjava.base.server.ULCSession.a(ULCSession.java:157)

        at com.ulcjava.base.server.ULCSession.processRequests(ULCSession.java:304)

        at com.ulcjava.base.development.DevelopmentContainerAdapter.processRequests(DevelopmentContainerAdapter.java:0)

        at com.ulcjava.base.development.DevelopmentConnector.sendRequests(DevelopmentConnector.java:12)

        at com.ulcjava.base.client.UISession$k_.run(UISession$k_.java:76)

        at java.lang.Thread.run(Thread.java:534)

 

Please could you help explain how I can resolve this issue.

 

 

Many thanks

 

 

 

Pete

Serge Piletsky TeamDev Ltd.
Serge Piletsky
670 posts since
Apr 24, 2006
Currently Being Moderated
3. Nov 30, 2007 5:48 PM in response to: Peter Whittaker
Re: Remove my Native Shapes and VB macros

Hi Peter,

 

You just need to invoke this code in OleMessageLoop of the JExcel Application, Worksheet or Workbook object, for example:

 


   object.getOleMessageLoop().doInvokeAndWait(new Runnable() {
      public void run() {
         // your code
      }
   }

 

-Serge

Peter Whittaker  
Peter Whittaker
16 posts since
Oct 4, 2007
Currently Being Moderated
4. Dec 4, 2007 1:20 PM in response to: Serge Piletsky
Re: Remove my Native Shapes and VB macros

Hi Serge

 

Thanks for the last reply.

I now have a different problem in that I use the following code to find a named shape on a worksheet when I first open a workbook which has been previously saved containing a working Drop Down list. However it finds the default names for the shapes eg "Drop Down #" and not the name that was allocated to it when it was created prior to saving the spreadsheet.

If I try and create a new Shape with the original name I get an exception.

 

THis is the code that tries to find the named shape. From the trace statement only default names for shapes are found!!:

        Runnable runnable = new Runnable()

        {

            public void run()

            {

                Shapes shapes = uiWorksheet.getPeer().getShapes();

                Shape  shape  = null;

                String name   = null;

                int num = (int) shapes.getCount().getValue();

                for(int i = 0; i < num-1; i++)

                {

                     shape = shapes.item(new Variant(i + 1));

                     if(shape!=null)

                         try

                         {

                             name = shape.getName().toString();

                             Message.putTrace("CtXL.findListControl() shape: "+name);

 

                         }catch(Exception e){}

                }

            }

        };

        try

        { Application app = application;

          app.getOleMessageLoop().doInvokeAndWait(runnable);  }

        catch (InterruptedException e)

        { e.printStackTrace();}

        catch (InvocationTargetException e)

        { e.printStackTrace();}

 

 

 

This is the exception generated when I try to create a new shape with the same name:

java.lang.reflect.InvocationTargetException

        at com.jniwrapper.win32.MessageLoopThread.doInvokeAndWait(MessageLoopThread.java:197)

        at com.ym.ui.ctx.CtXLView.runNativeCombobox(CtXLView.java:2052)

        at com.ym.ui.ctx.CtXLView.cellSelected(CtXLView.java:1864)

        at com.ym.ui.ctx.CtXLView.access$1800(CtXLView.java:123)

        at com.ym.ui.ctx.CtXLView$20.eventFired(CtXLView.java:1983)

        at com.canoo.ulc.community.pollingtimer.application.EventDispatcher.dispatchEventToRegisteredListeners(EventDispatcher.java:65)

        at com.canoo.ulc.community.pollingtimer.application.EventDispatcher.dispatchEvents(EventDispatcher.java:45)

        at com.ym.ui.ctx.CtXLView$WorksheetEventLogger.selectionChanged(CtXLView.java:2255)

        at com.jniwrapper.win32.jexcel.AppEventsServerImpl.sheetSelectionChange(AppEventsServerImpl.java:166)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:324)

        at com.jniwrapper.win32.com.server.IDispatchServer.invoke(SourceFile:209)

        at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:324)

        at com.jniwrapper.win32.com.server.CoInterfaceVTBL$VirtualMethodCallback.b(SourceFile:239)

        at com.jniwrapper.win32.com.server.CoInterfaceVTBL$VirtualMethodCallback.callback(SourceFile:142)

        at com.jniwrapper.Callback.enterPoint(SourceFile:222)

        at com.jniwrapper.Function.invokeCFunc(Native Method)

        at com.jniwrapper.FunctionCall.a(SourceFile:126)

        at com.jniwrapper.FunctionCall.call(SourceFile:34)

        at com.jniwrapper.Function.invoke(SourceFile:164)

        at com.jniwrapper.Function.invoke(SourceFile:188)

        at com.jniwrapper.win32.MessageLoopThread$LoopThread.run(MessageLoopThread.java:489)

Caused by: com.jniwrapper.win32.automation.AutomationException: COM object method returns error code: 0x80020009; DISP_E_EXCEPTION (Exception occurred.)

        at com.jniwrapper.win32.com.impl.IUnknownImpl.invokeStandardVirtualMethod(SourceFile:681)

        at com.jniwrapper.win32.automation.impl.IDispatchImpl.invoke(SourceFile:112)

        at com.jniwrapper.win32.automation.Automation$InvocationHelper.a(SourceFile:769)

        at com.jniwrapper.win32.automation.Automation$InvocationHelper.a(SourceFile:760)

        at com.jniwrapper.win32.automation.Automation$InvocationHelper.c(SourceFile:748)

        at com.jniwrapper.win32.automation.Automation$InvocationHelper.setProperty(SourceFile:728)

        at com.jniwrapper.win32.automation.Automation.setProperty(SourceFile:371)

        at com.jniwrapper.win32.automation.Automation.setProperty(SourceFile:397)

        at com.jniwrapper.win32.automation.Automation.setDispatchProperty(SourceFile:449)

        at com.jniwrapper.win32.excel.impl.ShapeImpl.setName(ShapeImpl.java:367)

        at com.ym.ui.ctx.CtXLView$21.run(CtXLView.java:2023)

        at com.jniwrapper.win32.MessageLoopThread.doInvokeAndWait(MessageLoopThread.java:193)

        ... 25 more

 

 

Thanks for your help

 

Pete

Igor Novikov TeamDev Ltd.
Igor Novikov
225 posts since
Apr 24, 2006
Currently Being Moderated
5. Dec 6, 2007 6:13 PM in response to: Peter Whittaker
Re: Remove my Native Shapes and VB macros

Hi Peter,

 

We have slightly modified your code sample. It works like a sharm :

 


import com.jniwrapper.win32.jexcel.Worksheet;
import com.jniwrapper.win32.jexcel.ui.JWorkbook;
import com.jniwrapper.win32.excel.Shape;
import com.jniwrapper.win32.excel.Shapes;
import com.jniwrapper.win32.automation.types.Variant;
import javax.swing.*;
import java.io.File;

public class ShapesNameSample {
       public static void main(String[] args) throws Exception
       {
        final JWorkbook _workbook = new JWorkbook(new File("c://combo.xls"));
        final Worksheet sheet = _workbook.getWorksheet("test");

        final JFrame frame = new JFrame();
        frame.setContentPane(_workbook);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(800,800);
        frame.setVisible(true);


        sheet.getApplication().getOleMessageLoop().doInvokeLater(new Runnable()
        {
            public void run()
            {
                Shapes shapes = sheet.getPeer().getShapes();
                Shape shape = null;
                String name = null;
                int num = (int) shapes.getCount().getValue();
                for(int i = 0; i < num; i++)
                {
                     shape = shapes.item(new Variant(i+1));
                     if(shape!=null)
                         try
                         {
                             name = shape.getName().toString();
                             System.out.println("CtXL.findListControl() shape" + i + ": " + name);

                         }catch(Exception e){}
                }
            }
        });
       }
}

 

combo.xls file you can find in attachment. Console output of this sample will be following:

 


CtXL.findListControl() shape0: MyCombo
CtXL.findListControl() shape1: AnotherMyComboBox
CtXL.findListControl() shape2: ComboBox1
CtXL.findListControl() shape3: ComboBox2
CtXL.findListControl() shape4: ComboBox3
CtXL.findListControl() shape5: LastComboBox4

 

I hope this helps.

 

Sincerely,

 

Igor Novikov

Attachments:
Peter Whittaker  
Peter Whittaker
16 posts since
Oct 4, 2007
Currently Being Moderated
6. Dec 12, 2007 2:18 AM in response to: Peter Whittaker
Re: Remove my Native Shapes and VB macros

Thanks for that.

 

Works just fine, though I have had a problem inserting shape.delete(); into the code you provided imediately after the System.out.println()

I was sort of expecting the comboboxes to disapear butthere was no effect.

 

 

Kind regards

 

 

Pete

More Like This

  • Retrieving data ...