How to call a Macro from Apache POI library in Java?

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

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.

Answer 2

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