CODEWITHSUNDEEP
javaexcelapache-poi
Mrunal
Mrunal

Reputation: 80

Cannot create new excel sheet in a workbook using apache POI

I am trying to copy multiple files in a singe excel file. Each sheet in that excel file will carry contents of one file. And there are around 6 files that I need to copy. So the resultant file should contain 6 sheets. But when I run my code only 1 sheet is generated for a single file. I tried debugging it but could not figure out the reason.

This is my 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 for files to copy");
    File[] dir_listing = dir.listFiles();
    HSSFWorkbook my_wb = new HSSFWorkbook();                        
    //creating an output stream to copy all files in combined.xls
    BufferedOutputStream bos= new BufferedOutputStream(new FileOutputStream("path of resultant excel sheet"));
    //traversing through a list of files
    for(File file: dir_listing){
        //file: file to be copied.
        add_in_excel(my_wb,bos,file);
        System.out.println("In file :" + file.getName());
    }
    bos.close();
    System.out.println("Files are copied");
}


 private void add_in_excel(HSSFWorkbook copy_wb, BufferedOutputStream bos,File file) throws IOException {
    // TODO Auto-generated method stub
    //creating a new sheet in the 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 the 'file'
    for (int iSheet = 0; iSheet < sheets; iSheet++) {                   
        sheet = workbook.getSheetAt(iSheet);                
        if (sheet != null) {                                      
            lRow = sheet.getLastRowNum();
            for (int iRow = fRow; iRow <= lRow; iRow++) {
                row = sheet.getRow(iRow);
                //creating row in the new sheet
                myRow = mySheet.createRow(count_row++);
                if (row != null) {                           
                    for (int iCell = 0; iCell < 4; iCell++) {  
                        //creating a column in the new sheet
                        cell = row.getCell(iCell);
                        myCell = myRow.createCell(iCell);                                
                        myCell.setCellStyle(cs);
                        //setting cell type and 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);        
}

Upvotes: 0

Views: 4270

Answers (2)

Axel Richter
Axel Richter

Reputation: 61945

I have reduced this to the 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 is what you actual doing:
 public void fileIterator() throws IOException{
  HSSFWorkbook my_wb = new HSSFWorkbook();                        
  BufferedOutputStream bos= new BufferedOutputStream(new FileOutputStream("copiedWB.xls"));
  for(int i = 0; i < 3; i++){
   add_in_excel(my_wb, bos,"file" + i);
   System.out.println("In file :" + "file" + i);
  }
  bos.close(); //closing the BufferedOutputStream. The resulting file contains bytes for 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 the copy_wb with one new added sheet into the BufferedOutputStream without closing it. But writing a XLS file is complex. So this will not work properly. It will append bytes for a complete XLS workbook onto the stream.
 }
}

You write the copy_wb with one new added sheet into the BufferedOutputStream without closing the stream. But writing a XLS file is complex. So this will not work properly. It will append bytes for a complete XLS workbook with at first 1, then 2 and at last 3 sheets onto the stream. But each time a complete XLS workbook file.

After adding all sheets, you close the BufferedOutputStream. The stream and also the resulting file contains bytes for 3 complete XLS files. The first with 1 sheet, the second with 2 sheets and the third with 3 sheets. If opened with Excel only the first will be read.

This will work, but is not recommend.

//This will work, but is not recommend
 public void fileIterator() throws IOException{
  HSSFWorkbook my_wb = new HSSFWorkbook();                        
  for(int i = 0; i < 3; i++){
   BufferedOutputStream bos= new BufferedOutputStream(new FileOutputStream("copiedWB.xls")); // creating a new BufferedOutputStream for 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 the copy_wb with one new added sheet into the BufferedOutputStream and close it.
 }

Creating a new BufferedOutputStream for each call of add_in_excel. Write the copy_wb with one new added sheet into the BufferedOutputStream and close it. So each write and close creates a new complete XLS file with one sheet more. Since it has the same name it will overwrite the existing file.

But why writing the complete workbook each time a new sheet is added?

So this is what I would do:

 public void fileIterator() throws IOException{
  HSSFWorkbook my_wb = new HSSFWorkbook();                        
  BufferedOutputStream bos= new BufferedOutputStream(new FileOutputStream("copiedWB.xls")); //create the BufferedOutputStream only for the fileIterator method
  for(int i = 0; i < 3; i++){
   add_in_excel(my_wb, "file" + i);
   System.out.println("In file :" + "file" + i);
  }
  my_wb.write(bos);
  bos.close();  //write into and close the BufferedOutputStream only once after you have added all sheets.
 }

 private void add_in_excel(HSSFWorkbook copy_wb, String file) throws IOException {
  HSSFSheet mySheet =  copy_wb.createSheet(file);
 }

Create the BufferedOutputStream only for the fileIterator method. Don't pass it to the add_in_excel. Write into and close the BufferedOutputStream only once after you have added all sheets.

Upvotes: 1

Praba
Praba

Reputation: 1381

Since you haven't mentioned any exception or stacktraces, I'm going to take wild guess - For every new excel file in the directory, you are doing

HSSFWorkbook workbook = new HSSFWorkbook(bis);

And at the end of reading each sheet (all rows and cells), you move on to the next sheet and so on until all sheets are created and are in memory. Then you write out the workbook itself to the output stream via

copy_wb.write(bos);

[I know this something you know but in case someone comes in the future, this will make it easier for them to understand what's going on without spending the time]

I'm thinking the first time you say workbook.write(outputstream) the contents are written. But you have not closed the stream and you have a entire work book written out. The next time you want to write another workbook to the same stream, I don't really know what would happen. Shouldn't be rather adding sheets to your current work book (rather than writing multiple workbooks to the same output stream)?

I'd suggest create the target workbook (if it doesn't exist) and write the sheets (not the workbook itself) of your source workbooks. This could be a work around, but unless I can debug what's the deal with multiple workbooks to the same output stream, I can't really suggest a fix for the current problem.

Upvotes: 2

Related Questions