Excel sheet writing JAVA+POI API

Question

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)..

João Silva · Accepted Answer · 2012-08-19 12:10:25Z

3

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.

answered Aug 19, 2012 at 12:10

João Silva

91.8k29 gold badges156 silver badges158 bronze badges

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

Comments

Reimeus · Accepted Answer · 2012-08-19 12:47:21Z

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

Manisha Anand · Accepted Answer · 2016-05-03 09:38:34Z

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

 }

Collectives™ on Stack Overflow

Excel sheet writing JAVA+POI API

3 Answers 3

Comments

Comments

writing in excel

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

writing in excel

Comments

Your Answer

Sign up or log in

Post as a guest

Related