Adding Macro using Excel POI

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?

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

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();
    }
}

Select allOpen in new window

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

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

Thanks for the responses for_yan and objects - the business requirement we have is to display it in a browser window.

I doubt its possible sorry