How to call a Macro from Apache POI library in Jav

Question

I have :

• Macro named "process" that does all the processing work for my worksheets.

• I want my Java code that uses Apache POI, to call this macro so that it can process the sheets for me.

• How can I call a Macro in Apache POI in Java ?

:: Note ::

• I am using import org.apache.poi.ss.usermodel in Apache POI.

• Please, give sample code. (I am new to Apache POI and Java itself.)

Answer 1

You can use other tool like com4j. I created a POC just today and works like a charm. Just follow these steps:

1. Download the last version here
2. Using Apache Ant build the sample: samples\excel

3. With the code generated by tlbimp (it is just a part of com4j), create a small app and write a code like:

   // Starts excel and open an existing workbook
   _Application app = ClassFactory.createApplication();
   app.setVisible(0, true);
   _Workbook wb = app.getWorkbooks().open("c:\\test.xlsx", null, null, null, null, null, null, null,
           null, null, null, null, null, null, null, 0);
   
   // run a macro
   Variant macro = new Variant(Variant.Type.VT_BSTR);
   macro.set("my_macro");
   wb.getApplication().run(macro, null, null, null, null, null, null, null, null, null, null, null,
           null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
           null, null, null, null);
   
   // Save the excel file and quit
   Variant saveBeforeExit = new Variant(Variant.Type.VT_BOOL);
   saveBeforeExit.set(1);
   wb.close(saveBeforeExit, Variant.getMissing(), Variant.getMissing(), 0);
   app.quit();
   

4. Further examples: com4j tutorials

Answer 2

I think, you can't.

Macros

Macros can not be created. However, reading and re-writing files containing macros will safely preserve the macros.

Apache POI Limitations

Answer 3

I don't think this is possible. POI is a library to read/edit Office files, macros are a whole other beast. You would have to implement a Visual Basic for Applications Interpreter into POI to accomplish this. Macros that are already in your Excel workbook are preserved, but you cannot add new ones or call existing ones.