java - Cannot create new excel sheet in a workbook using apache POI -

i trying copy multiple files in singe excel file. each sheet in excel file carry contents of 1 file. , there around 6 files need copy. resultant file should contain 6 sheets. when run code 1 sheet generated single file. tried debugging not figure out reason.

this code.

 public static void main(string[] args) throws ioexception {      // todo auto-generated method stub     createsingleexcelfile cef = new createsingleexcelfile();     cef.fileiterator();  }   public void fileiterator() throws ioexception{      file dir = new file("path files copy");     file[] dir_listing = dir.listfiles();     hssfworkbook my_wb = new hssfworkbook();                             //creating output stream copy files in combined.xls     bufferedoutputstream bos= new bufferedoutputstream(new fileoutputstream("path of resultant excel sheet"));     //traversing through list of files     for(file file: dir_listing){         //file: file copied.         add_in_excel(my_wb,bos,file);         system.out.println("in file :" + file.getname());     }     bos.close();     system.out.println("files copied"); }    private void add_in_excel(hssfworkbook copy_wb, bufferedoutputstream bos,file file) throws ioexception {     // todo auto-generated method stub     //creating new sheet in copy workbook     hssfsheet mysheet =  copy_wb.createsheet(file.getname());      bufferedinputstream bis = new bufferedinputstream(new fileinputstream(file));     hssfworkbook workbook = new hssfworkbook(bis);     cellstyle cs = copy_wb.createcellstyle();     cs.setwraptext(true);         hssfsheet sheet = null;     hssfrow row = null;     hssfcell cell = null;                    hssfrow myrow = null;     hssfcell mycell = null;     int sheets = workbook.getnumberofsheets();               int frow = 3;     int lrow = 0;     int count_row=0;      //traversing through sheets in 'file'     (int isheet = 0; isheet < sheets; isheet++) {                            sheet = workbook.getsheetat(isheet);                         if (sheet != null) {                                                   lrow = sheet.getlastrownum();             (int irow = frow; irow <= lrow; irow++) {                 row = sheet.getrow(irow);                 //creating row in new sheet                 myrow = mysheet.createrow(count_row++);                 if (row != null) {                                                (int icell = 0; icell < 4; icell++) {                           //creating column in new sheet                         cell = row.getcell(icell);                         mycell = myrow.createcell(icell);                                                         mycell.setcellstyle(cs);                         //setting cell type , adding data in each cell                         if (cell != null ) {                                                             mycell.setcelltype(cell.getcelltype());                             switch (cell.getcelltype()) {                             case hssfcell.cell_type_blank:                                                                      mycell.setcellvalue("");                                 break;                              case hssfcell.cell_type_boolean:                                 mycell.setcellvalue(cell.getbooleancellvalue());                                 break;                              case hssfcell.cell_type_error:                                 mycell.setcellerrorvalue(cell.geterrorcellvalue());                                 break;                              case hssfcell.cell_type_formula:                                 mycell.setcellformula(cell.getcellformula());                                 break;                              case hssfcell.cell_type_numeric:                                 if(hssfdateutil.iscelldateformatted(cell))                                     mycell.setcellvalue(cell.getdatecellvalue());                                 else                                     mycell.setcellvalue(cell.getnumericcellvalue());                                 break;                              case hssfcell.cell_type_string:                                 mycell.setcellvalue(cell.getstringcellvalue());                                 break;                             default:                                 mycell.setcellformula(cell.getcellformula());                             }                                                            }                     }                 }             }         }     }     bis.close();                copy_wb.write(bos);         } 

i have reduced main problem:

import org.apache.poi.hssf.usermodel.*;  import java.io.fileoutputstream; import java.io.ioexception; import java.io.bufferedoutputstream;  class createsingleexcelfile {   public static void main(string[] args) throws ioexception {   createsingleexcelfile cef = new createsingleexcelfile();   cef.fileiterator();  }  //this actual doing:  public void fileiterator() throws ioexception{   hssfworkbook my_wb = new hssfworkbook();                           bufferedoutputstream bos= new bufferedoutputstream(new fileoutputstream("copiedwb.xls"));   for(int = 0; < 3; i++){    add_in_excel(my_wb, bos,"file" + i);    system.out.println("in file :" + "file" + i);   }   bos.close(); //closing bufferedoutputstream. resulting file contains bytes 3 complete xls files.   }   private void add_in_excel(hssfworkbook copy_wb, bufferedoutputstream bos, string file) throws ioexception {   hssfsheet mysheet =  copy_wb.createsheet(file);   copy_wb.write(bos); //write copy_wb 1 new added sheet bufferedoutputstream without closing it. writing xls file complex. not work properly. append bytes complete xls workbook onto stream.  } } 

you write copy_wb 1 new added sheet bufferedoutputstream without closing stream. writing xls file complex. not work properly. append bytes complete xls workbook @ first 1, 2 , @ last 3 sheets onto stream. each time complete xls workbook file.

after adding sheets, close bufferedoutputstream. stream , resulting file contains bytes 3 complete xls files. first 1 sheet, second 2 sheets , third 3 sheets. if opened excel first read.

this work, not recommend.

//this work, not recommend  public void fileiterator() throws ioexception{   hssfworkbook my_wb = new hssfworkbook();                           for(int = 0; < 3; i++){    bufferedoutputstream bos= new bufferedoutputstream(new fileoutputstream("copiedwb.xls")); // creating new bufferedoutputstream each call of add_in_excel    add_in_excel(my_wb, bos,"file" + i);    system.out.println("in file :" + "file" + i);   }  }   private void add_in_excel(hssfworkbook copy_wb, bufferedoutputstream bos, string file) throws ioexception {   hssfsheet mysheet =  copy_wb.createsheet(file);   copy_wb.write(bos);   bos.close(); //write copy_wb 1 new added sheet bufferedoutputstream , close it.  } 

creating new bufferedoutputstream each call of add_in_excel. write copy_wb 1 new added sheet bufferedoutputstream , close it. each write , close creates new complete xls file 1 sheet more. since has same name overwrite existing file.

but why writing complete workbook each time new sheet added?

so do:

 public void fileiterator() throws ioexception{   hssfworkbook my_wb = new hssfworkbook();                           bufferedoutputstream bos= new bufferedoutputstream(new fileoutputstream("copiedwb.xls")); //create bufferedoutputstream fileiterator method   for(int = 0; < 3; i++){    add_in_excel(my_wb, "file" + i);    system.out.println("in file :" + "file" + i);   }   my_wb.write(bos);   bos.close();  //write , close bufferedoutputstream once after have added sheets.  }   private void add_in_excel(hssfworkbook copy_wb, string file) throws ioexception {   hssfsheet mysheet =  copy_wb.createsheet(file);  } 

create bufferedoutputstream fileiterator method. don't pass add_in_excel. write , close bufferedoutputstream once after have added sheets.