Adding Macro using Excel POI

abuyusuf
abuyusuf used Ask the Experts™
on
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

http://support.microsoft.com/default.aspx?scid=kb;en-us;129153

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

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2011
Awarded 2011

Commented:
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?

Author

Commented:
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.
Thanks
Awarded 2011
Awarded 2011

Commented:
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:
http://apache-poi.1045710.n5.nabble.com/Add-Macro-through-POI-td2287660.html

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(){
              try{
                  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");
                  oCell.setCellValue(oTextString);

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

              } catch(Exception ex){
                  System.out.println(ex.toString());
                  ex.printStackTrace();
              }

    }

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

Open in new window

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
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.
Thanks
Awarded 2011
Awarded 2011
Commented:

But maybe  you could create xls file on your server and then post the link
to the user on the browser?
Then it would probably work.

So if you steam Excel content to the browser directly, it shows the Excel but does not recognize the macro? I see.

 
Mick BarryJava Developer
Top Expert 2010

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

Author

Commented:
Thanks for the responses for_yan and objects - the business requirement we have is to display it in a browser window.
Mick BarryJava Developer
Top Expert 2010

Commented:
I doubt its possible sorry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial