CODEWITHSUNDEEP
javaapache-poi
Priyank Shah
Priyank Shah

Reputation: 739

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

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

Upvotes: 10

Views: 82870

Answers (5)

Arpan Saini
Arpan Saini

Reputation: 5181

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

Upvotes: 2

Fadel K.
Fadel K.

Reputation: 65

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

Upvotes: 1

class Android
class Android

Reputation: 761

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

Upvotes: 1

Bhaskar
Bhaskar

Reputation: 537

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

Upvotes: 13

John B
John B

Reputation: 32949

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

Upvotes: 2

Related Questions