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

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 Apach开发者_运维知识库e 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.)

---

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.

---

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

---

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

---

One little hacky way to do this is to make your macros run automatically when the workbook is opened. This can be done from vba in ThisWorkbook module. Then you can open excel with Desktop.getDesktop().open(new File(filePath)); and with apache POI and FileInputStream just get the result of 'process' .