File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Swing / AWT / SWT and the fly likes need help to perform actions in excel through java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Swing / AWT / SWT
Bookmark "need help to perform actions in excel through java" Watch "need help to perform actions in excel through java" New topic
Author

need help to perform actions in excel through java

Ravikanth thota
Greenhorn

Joined: Apr 09, 2012
Posts: 24
hi all,
i need help to perform actions in excel through java..

for example.--- after opening excel, clicking on insert menu & clicking on table option or picture option etc ... for this we il do manually through our mouse or keyboard.
but here i have to write a java code .. by executing that java code .. table option action or picture option action should be performed..

can any please help me in doing this?

thank you..
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 10535
    
    9

Welcome to the Ranch.

Check out the java.awt.Robot class for mouse movement etc

If you want to manipulate the data then have a look at the options available


[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]
Ravikanth thota
Greenhorn

Joined: Apr 09, 2012
Posts: 24
thank you for your reply..

here i should not use mouse pointer to perform actions .. i should perform actions programatically.
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 10535
    
    9

rk thota wrote:thank you for your reply..

here i should not use mouse pointer to perform actions .. i should perform actions programatically.

Did you take a look at the Robot class like I suggested
Ravikanth thota
Greenhorn

Joined: Apr 09, 2012
Posts: 24
Robot class which you sujested is also very helpful to me .. but iam not much aware of this robot class..
can you please send me the small sample code .. to move mouse pointer in excel, after opening excel through java..
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 10535
    
    9

Sorry. The ranch is NotACodeMill and we don't send code.

Check out the API documents for all the methods in the Robot class you can use to move the mouse, click etc. If you run into a problem, show us your SSCCE code , tell us what problem you are having, and we will guide you in the right direction
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

We're using the JACOB (Java COM Bridge) library. It allows to use Excel's scripting capabilities (the VBA) via the COM (Component Object Model) interface to be used from Java. You generally need to design the VBA code you'd like to execute and then use JACOB to do so. It's quite hard to use if you don't have good knowledge of VBA though, and even if you do, it is marred with problems. We're generally using it to open files in Excel and apply some formatting on them, and to interactively export/import selected parts of Excel sheets from/to our application.
Ravikanth thota
Greenhorn

Joined: Apr 09, 2012
Posts: 24
Martin Vajsar wrote:We're using the JACOB (Java COM Bridge) library. It allows to use Excel's scripting capabilities (the VBA) via the COM (Component Object Model) interface to be used from Java. You generally need to design the VBA code you'd like to execute and then use JACOB to do so. It's quite hard to use if you don't have good knowledge of VBA though, and even if you do, it is marred with problems. We're generally using it to open files in Excel and apply some formatting on them, and to interactively export/import selected parts of Excel sheets from/to our application.


thank you martin.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

You're welcome.

If you decide to go this path, I'd have one more advice: use only one thread to invoke all JACOB actions from. The COM interface associates various things with current thread and it can be a mess if you call it from several different threads. (It should be possible to use JACOB safely with multiple threads, but I was not able to work it out from existing documentation.)
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
Why would you want to do this in Java instead of VBScript? That seems much more natural.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Tim Moores wrote:Why would you want to do this in Java instead of VBScript? That seems much more natural.

I don't know exactly what the OP intends, but in our case, one of the functionalities is that a user marks an area in Excel, hits a button in our Swing app and we load up and process the values from the marked area. We actually ported the application from Visual Basic, so this was quite natural (though painful) thing to do.

Since the process is a bit fragile (throwing an exception now and then, though we hope to alleviate that by only making calls from one thread), I'd be interested in other possibilities. Could I do this with VBScript somehow?
Ravikanth thota
Greenhorn

Joined: Apr 09, 2012
Posts: 24
Martin Vajsar wrote:
Tim Moores wrote:Why would you want to do this in Java instead of VBScript? That seems much more natural.

I don't know exactly what the OP intends, but in our case, one of the functionalities is that a user marks an area in Excel, hits a button in our Swing app and we load up and process the values from the marked area. We actually ported the application from Visual Basic, so this was quite natural (though painful) thing to do.

Since the process is a bit fragile (throwing an exception now and then, though we hope to alleviate that by only making calls from one thread), I'd be interested in other possibilities. Could I do this with VBScript somehow?


hi martin and tim... actually what i want to say is... i have an enterprise excel which contains some proprietary buttons(developed using .net).. here, without opening or interacting with that excel directly, i have to perform actions on those buttons using java. that is, i have to write a code in java so that the proprietary buttons should work same as when we clicked that buttons manually. so i need an approach how my java layer will interact with the excel(.net layer).???
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Generally, JACOB allows you to do what you could otherwise do with Excel's VBA. Try to record a macro in Excel and invoke the addin. This will create a VBA procedure that should replay your actions; if this works, you should be able to call this from JACOB. I have doubts it will work though the addin would have to integrate itself with Excel's scripting engine and I'm not sure this is commonly done.

You could even call the recorded macro itself from JACOB, which could help you save some work, but you'd need to configure the Excel (or digitally sign the file with the macro) so that you don't get confirmation dialogs when you try to open the macro from Java/JACOB.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: need help to perform actions in excel through java