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

Question

I have:

• The macro is called a "process" that does all the processing for my worksheets.

• I want my Java code to use the Apache POI to call this macro so that it can process 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 a sample code. (I'm new to Apache POI and Java itself.)

Answer 1

I do not think that's possible. POI is a library for reading / editing Office files, macros are a whole different beast. To do this, you will need to implement Visual Basic for Interpreter applications in the POI. Macros that are already in your Excel workbook are saved, but you cannot add new ones or call existing ones.

Answer 2

You can use another tool, for example com4j . I created POC just today and it works like a charm. Just follow these steps:

• Download the latest version here.
• Using Apache Ant create a sample: samples \ excel

• With the code created by tlbimp (this is just part of com4j), create a small application and write 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(); 

• Other examples: com4j tutorials

Answer 3

I think you can’t.

Macros

Macros cannot be created. However, reading and overwriting files containing macros will safely save macros.

Api POI Limitations