Avatar of abuyusuf

asked on 

Adding Macro using Excel POI

We are generating an Excel spreadsheet in our application using Apache POI API - the spreadhseet has two tabs with a hyperlink on the first one leading to the second tab. When the hyperlink is clicked the user always gets a dialog box asking him if he would like to save his work - this is very annoying and we need a solution to fix it.

If the Excel spreadsheet was standalone the fix is easy - the following link provides a macro for that


But how can I do this through the POI API - from what I have found it is not possible but if someone has done it and can point me in the right direction that would be great

Microsoft ExcelJava

Avatar of undefined
Last Comment
Mick Barry
Avatar of for_yan
Flag of United States of America image

Does POI allow to modify existing worksheet?
What happens if you first create an empty Excel workbook manually- add visual basic macro to it using microsoft
recommendations - and then when creating your spreadsheet with POI - start from this workbook with embedded macro,
say copy the file every time with java code, and then open it with POI and insert the
contents you need.
Perhaps it will still keep the macro within?
Have you tried it this way?

Avatar of abuyusuf


Hi for_yan
Thanks for your response - the scenario you described in not what I am dealing with exactly - the code we have generates a excel spreadsheet with 2 tabs - one of them has a link the other - when the link is clicked this message pops up - if I had a standalone spreadsheet I think the macro would work but is there anyway to add it via the POI API - I dont think so but just checking if there is a solution out there.
Avatar of for_yan
Flag of United States of America image

I'm still a bit confused. If what I was talking about, really wiorks (and it actually does - see below)
that still seesm to me to be a good workaround for you.
 In your software you generate using POI  this spreadsheet with 2 tabs - correct?
So now you create it in POI. So is there any reason why you couldn't when you start not create
the workbook from the scratch in POI, but rather use the temaplte with empty workbook but with macro already in there
and then add your spreadsheet and your content to it?

I was browsing on this subject and even thinking about testing  it myself,
 and found exactly the same suggestion, and it looks like it works for them:

I understand that it is not scenario you were thinking about, but as
it seems they don't have this capability (in the link above they also state it,
though this link is not the latest, true), so, unless you find direct ability of
adding macros, this may be still usable workaround.

Now I even tested it.
This is my code:

I added macro to Book_test.xls
and then processd it with this code
and now Im have Book_test1.xls which
has the same macro working - and you can change it
- it closes without any questions
import org.apache.poi.hssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class TestPOI {

    public TestPOI(){
                  FileInputStream oFileInputStream=new FileInputStream("c:/temp/Book_test.xls");
                  HSSFWorkbook oWorkbook=new HSSFWorkbook(oFileInputStream);
                  HSSFSheet oWorksheet = oWorkbook.createSheet("New Sheet-1");
                  HSSFRow oRow = oWorksheet.createRow(0);
                  HSSFCell oCell=oRow.createCell((short)0);
                  HSSFRichTextString oTextString=new HSSFRichTextString("Sample String");

                  FileOutputStream oOutputStream = new FileOutputStream("c:/temp/Book_test1.xls");

              } catch(Exception ex){


    public static void main(String [] args){
        new TestPOI();

Open in new window

Avatar of abuyusuf


Thanks for posting that solution - it worked for me BUT my code is exporting the excel spreadsheet to a ServletOutputStream so it can be displayed in a browser.  What I tried to do is read the file I wrote - which does not have the message anymore to a FileInputStream

FileInputStream fin = new FileInputStream("C:\\TEMP\\NEWBOOK.XLS");

and then export it to the ServletOutputStream

byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = fin.read(buffer)) > 0) {
 servletOutputStream.write(buffer, 0, bytesRead);

This works but I still see the message - so this is where I'm stuck now.
Avatar of for_yan
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mick Barry
Mick Barry
Flag of Australia image

you'll need to download the file directly to disk instead of opening it with the browser

Avatar of abuyusuf


Thanks for the responses for_yan and objects - the business requirement we have is to display it in a browser window.
Avatar of Mick Barry
Mick Barry
Flag of Australia image

I doubt its possible sorry
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo