How to add new sheets to existing excel workbook using apache POI?

Question

I am trying to write List data into multiple excel sheet in one work book. like for first list, the code will create new workbook and create new sheet for list[1], for second list it will create new sheet in existing workbook and so on. so i wrote below code. but it doesnt work and i am able to see only first sheet for list[1]. can someone help me to provide any alternate resolutions?

the below code i have written

    ArrayList<List<String>> tempresultdata=this.getSummaryList();
    HSSFWorkbook workbook = new HSSFWorkbook();
    String fileName="Path\\To\\XLS";
    File file = new File(fileName);
    FileOutputStream out;           
    if(!file.exists()) // This will create new workbook with new sheet if it doesnt exists{

                HSSFSheet mySheet = workbook.createSheet(sheetname);
                writeExcel(mySheet,tempresultdata);
    } else // This add new sheet to above created workbook {
            try {
                HSSFWorkbook myWorkBook = (HSSFWorkbook) WorkbookFactory.create(file);
                workbook=myWorkBook;
                HSSFSheet mySheet = (HSSFSheet) workbook.createSheet(sheetname);
                writeExcel(mySheet,tempresultdata);                 
            } catch (InvalidFormatException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }   
    try{
        out = new FileOutputStream(fileName,true);
        workbook.write(out);
        out.close();
        }catch(Exception e){ 
            e.printStackTrace();
        }

Thanks, Priyank Shah

it is user defined fuctions. that mapped content of list to excel cells. — Priyank Shah, Sep 17 '12 at 12:39
You're only ever adding one sheet, so surely having only one sheet in the output is to be expected? — Gagravarr, Jan 07 '13 at 14:28
@PriyankShah I am facing the same problem for creating multiple sheets in existing excel, can you keep the working application for exporting data to excel in multiple using servlets. — spt, Nov 13 '13 at 07:01

score 13 · Answer 1 · answered Feb 28 '13 at 23:58

If file does not exist then this code creates new file and also creates sample sheet1 but if file exists then it adds new sheet to existing excel file.

    HSSFWorkbook workbook = null;
    File file = new File(context.getExternalFilesDir(null), "Sample.xls");
    FileOutputStream fileOut = new FileOutputStream(file);

    if (file.exists()) {
        try {
            workbook = (HSSFWorkbook)WorkbookFactory.create(file);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        HSSFSheet sheet = workbook.createSheet("Sample sheet2");
    }
    else{
        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sample sheet1");
    }
    workbook.write(fileOut);
    fileOut.close();

score 2 · Answer 2 · answered Sep 17 '12 at 12:58

2

Looks like you are only writing out the sheet not the workbook

answered Sep 17 '12 at 12:58

John B

32,493
6
77
98

yes, I am writing new sheet, i want to add that sheet to workbook. – Priyank Shah Sep 17 '12 at 13:29
My point is that you are passing `sheet` to `writeExcel` so I am assuming it is only writing out the sheet not the book. – John B Sep 17 '12 at 14:00
Hi John, But at the end this code will write entire sheet to that work book. try{ out = new FileOutputStream(fileName,true); workbook.write(out); out.close(); }catch(Exception e){ e.printStackTrace(); } – Priyank Shah Sep 18 '12 at 08:54
I see that you have a `List>` and you are trying to create a new sheet for each outter list. However, I do not see where you looping through the outter list creating a new sheet per list. Seems that you only create one sheet even if there are 8 lists. – John B Sep 18 '12 at 10:37

score 2 · Answer 3 · edited Jan 11 '17 at 18:37

While working with existing workbook , You should not create a file object , Just need to Provide the File Location to FileInputStream object and pass it's reference to workbook object, then do whatever you want to do with the existing file. To add a sheet just use create Method command

public void AddsheetintoExistingworkbook(String sheetname) throws IOException, InvalidFormatException{

    //***************************Add a sheet into Existing workbook***********************************************


 String path="C:\\Workspace\\Selenium_2.53\\src\\InputFiles\\Temp.xlsx";
 fileinp = new FileInputStream(path);
 workbook = new XSSFWorkbook(fileinp);
 workbook.createSheet(sheetname);

 fileOut = new FileOutputStream(path);
    workbook.write(fileOut);
    fileOut.close();
 System.out.println("File is written successfully");    
}

score 1 · Answer 4 · answered Feb 23 '14 at 07:42

1

You can use Bhaskar's code to work things out and to use WorkbookFactory you will probably need to donwload poi-ooxml-3.7.jar file.

Here is the link for you:

http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi-ooxml/3.7

answered Feb 23 '14 at 07:42

class Android

762
1
6
17

score 1 · Answer 5 · answered Feb 29 '16 at 09:27

To add more than one Excel sheet to a new Excel file, those sheets need to be created from one workbook, and written once to the file output stream

import java.io.*;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class JustATest {
   public static void main(String[] args) throws Exception  {       

        XSSFWorkbook AWorkbook = new XSSFWorkbook(); //Create blank workbook 

        for (int i = 0; i < 10; i++) {
            XSSFSheet spreadsheet = AWorkbook.createSheet(" Employee "+i);

            // the follwing code is to create dummy data in sheets 
            // is similar to the one at tutorialPoint.com
            XSSFRow row;            
            //This data needs to be written (Object[])
            Map < String, Object[] > empinfo =    new TreeMap< String, Object[] >();
            empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
            for (int j = 0; j < i; j++) {
                empinfo.put( j+2+"", new Object[] {  j+2+"", "Fadel K", "Technical Manager" });                
            }          
            //Iterate over data and write to sheet
            Set<String> keyid = empinfo.keySet();
            int rowid = 0;
            for (String key : keyid){
               row = spreadsheet.createRow(rowid++);
               Object [] objectArr = empinfo.get(key);
               int cellid = 0;
               for (Object obj : objectArr){
                  Cell cell = row.createCell(cellid++);
                  cell.setCellValue((String)obj);
               }
            }            
            // dummy data creation over.
        }       

        // here you write all sheets at once, by writing the entier workbook
        FileOutputStream out = new FileOutputStream(new File("AllData.xlsx"));
        AWorkbook.write(out);
        out.close();               
   }
}

How to add new sheets to existing excel workbook using apache POI?

5 Answers5

Linked