CODEWITHSUNDEEP
javaapache-poi
smriti
smriti

Reputation: 1144

Excel sheet writing JAVA+POI API

I am using following POI api to write on excel through java code

public static HSSFWorkbook sampleWorkbook = new HSSFWorkbook();
public static HSSFSheet sampleDataSheet = sampleWorkbook.createSheet("ABC");

It is creating a sheet in excel with name of ABC. That is ok My Question is I need to add/create another sheet with name of "XYZ" in same workbook. How do I do that? As if I write something like this 

public static HSSFSheet sampleDataSheet = sampleWorkbook.createSheet("XYZ");

it would override the first one(ABC)..

Upvotes: 1

Views: 1810

Answers (3)

Manisha Anand
Manisha Anand

Reputation: 1

writing in excel

public class Writesheet 
{

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

 {

  //Create blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook(); 
  //Create a blank sheet
  XSSFSheet spreadsheet = workbook.createSheet( 
  " Employee Info ");
  XSSFSheet spreadsheet1 = workbook.createSheet( 
          " Employee Info1 ");
  XSSFSheet spreadsheet2 = workbook.createSheet( 
          " Employee Info2 ");
  XSSFSheet spreadsheet3 = workbook.createSheet( 
          " Employee Info3 ");
  XSSFSheet spreadsheet4 = workbook.createSheet( 
          " Employee Info4 ");
  XSSFSheet spreadsheet5 = workbook.createSheet( 
          " Employee Info5 ");


  XSSFSheet spreadsheet9 = workbook.createSheet( 

  //Create row object
//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" });
  empinfo.put( "2", new Object[] { "tp1", "Gopal", "Technical Manager" });
  empinfo.put( "3", new Object[] {  "tp2", "Manisha", "Proof Reader" });
  empinfo.put( "4", new Object[] { "tp3", "Masthan", "Technical Writer" });
  empinfo.put( "5", new Object[] {  "tp4", "Satish", "Technical Writer" });
  empinfo.put( "6", new Object[] { "tp5", "Krishna", "Technical Writer" });

  Map < String, Object[] > empinfo1 =  new TreeMap < String, Object[] >();
  empinfo1.put( "1", new Object[] { "tp6", "Gop", "Technical Manager" });
  empinfo1.put( "2", new Object[] {  "tp7", "Mani", "Proof Reader" });
  empinfo1.put( "3", new Object[] { "tp8", "than", "lyricist" });
  empinfo1.put( "4", new Object[] {  "tp9", "Sat", " Writer" });
  empinfo1.put( "5", new Object[] { "tp10", "Krish", "Technical Writer" });

  Map < String, Object[] > empinfo2 =  new TreeMap < String, Object[] >();
  empinfo2.put( "1", new Object[] { "tp11", "nayan", " Manager" });
  empinfo2.put( "2", new Object[] {  "tp12", "priyanka", " developer" });
    empinfo2.put( "3", new Object[] { "tp13", "pradyot", "Technical Writer" });
  empinfo2.put( "4", new Object[] {  "tp14", "manisa", "developer" });
  empinfo2.put( "5", new Object[] { "tp15", "limca", "Technical Writer" });

  //Iterate over data and write to sheet
 func1( spreadsheet,empinfo);

 func1( spreadsheet1,empinfo1);

 func1( spreadsheet2,empinfo2);


 func1( spreadsheet3,empinfo);

 func1( spreadsheet4,empinfo1);

 func1( spreadsheet5,empinfo2);

  //Write the workbook in file system
  FileOutputStream out = new FileOutputStream( 
  new File("Writesheet2.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println( 
  "Writesheet.xlsx written successfully" );
  workbook.close();

  }




 static void func1(XSSFSheet var,Map<String, Object[]> var1)
   {


   XSSFRow row;
Set < String > keyid = var1.keySet();
   int rowid = 0;
   for (String key : keyid)
   {

    row = var.createRow(rowid++);
       Object [] objectArr = var1.get(key);
       int cellid = 0;
       for (Object obj : objectArr)
        {
           Cell cell = row.createCell(cellid++);
           cell.setCellValue((String)obj);
        }
   }
 }

 }

Upvotes: 0

Reimeus
Reimeus

Reputation: 159754

Just to clarify, you can assign the same variable name to 2 different sheets, and those 2 sheets will be created. However, this is not good practice as you lose the handle to the first sheet created.

The code:

public static HSSFSheet sampleDataSheet = sampleWorkbook.createSheet("ABC");

suggests that you are creating a new sheet in the class member section. This is not advised as dependent variables such as sampleWorkbook may not have been initialised.

A method for sheet/Excel file creation could look like:

private void createExcelFile() {
   HSSFWorkbook workbook = new HSSFWorkbook();
   HSSFSheet sampleDataSheet1 = workbook.createSheet("ABC");
   HSSFSheet sampleDataSheet2 = workbook.createSheet("XYZ");
   // save to disk
}

For more see:

public HSSFSheet createSheet()

Upvotes: 2

Jo&#227;o Silva
Jo&#227;o Silva

Reputation: 91299

It will not override the first one, it will create a new sheet, but you need to assign it to a different variable:

HSSFSheet firstSheet = sampleWorkbook.createSheet("ABC");
HSSFSheet secondSheet = sampleWorkbook.createSheet("XYZ");

Here's the documentation:

public XSSFSheet createSheet(java.lang.String sheetname)

Create a new sheet for this Workbook and return the high level representation. Use this to create new sheets.

Upvotes: 3

Related Questions