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
– Priyank Shah, Commented Sep 17, 2012 at 12:39
You're only ever adding one sheet, so surely having only one sheet in the output is to be expected? — Gagravarr
– Gagravarr, Commented Jan 7, 2013 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
– spt, Commented Nov 13, 2013 at 7:01

Bhaskar · Accepted Answer · 2013-02-28 23:58:09Z

13

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

answered Feb 28, 2013 at 23:58

Bhaskar

5375 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John B · Accepted Answer · 2012-09-17 12:58:14Z

2

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

answered Sep 17, 2012 at 12:58

John B

33.1k8 gold badges80 silver badges100 bronze badges

4 Comments

Priyank Shah Over a year ago

yes, I am writing new sheet, i want to add that sheet to workbook.

John B Over a year ago

My point is that you are passing sheet to writeExcel so I am assuming it is only writing out the sheet not the book.

Priyank Shah Over a year ago

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

John B Over a year ago

I see that you have a List<List<String>> 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.

Paul Roub · Accepted Answer · 2017-01-11 18:37:54Z

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

class Android · Accepted Answer · 2014-02-23 07:42:13Z

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, 2014 at 7:42

class Android

7611 gold badge7 silver badges17 bronze badges

Comments

Fadel K. · Accepted Answer · 2016-02-29 09:27:30Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related