Create HSSF WorkBook in Java

Let us create a new excel file and write data in it. Following is the API which we will use for this purpose.

Source :
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public static void createHSSFWorkBook() throws IOException,FileNotFoundException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");

Map data = new HashMap();
data.put("1", new Object[] {"Emp No.", "Name", "Salary"}); //Adding Object[] array in each key object
data.put("2", new Object[] {1d, "John", 1500000d});
data.put("3", new Object[] {2d, "Sam", 800000d});
data.put("4", new Object[] {3d, "Dean", 700000d});

Set keyset = data.keySet();
int rownum = 0;
for (String key : keyset) {                               //Looping Keys in Map
   Row row = sheet.createRow(rownum++);
   Object [] objArr = data.get(key);
   int cellnum = 0;
   for (Object obj : objArr) {                          //Looping each Object[] array
       Cell cell = row.createCell(cellnum++);
       if(obj instanceof Date)
           cell.setCellValue((Date)obj);                  //Setting values
       else if(obj instanceof Boolean)
           cell.setCellValue((Boolean)obj);
       else if(obj instanceof String)
           cell.setCellValue((String)obj);
       else if(obj instanceof Double)
           cell.setCellValue((Double)obj);
   }
}
FileOutputStream out =
           new FileOutputStream(new File("new.xls"));
   workbook.write(out);
   out.close();
   System.out.println("Excel written successfully..");
}
public static void main(String[] args) throws IOException,FileNotFoundException {
createHSSFWorkBook();
}

By Jayakar.

XSSF WorkBook Example in Java

Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.

XSSFWorkBook Example

Below jar files with same version are required.

poi-3.7-beta1.jar

poi-ooxml-3.7-beta1.jar

poi-ooxml-schemas-3.7-beta1.jar

xmlbeans-2.5.0.jar

xmlbeans-xmlpublic-2.4.0.jar

Source Code

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelGenerator {

       public static void main(String[] args) throws IOException {

              try {

                     FileInputStream file = new FileInputStream(new File("Test.xlsx"));

                     @SuppressWarnings("resource")

                     XSSFWorkbook workbook = new XSSFWorkbook(file);

                     XSSFSheet sheet = workbook.getSheetAt(0);

                     Iterator rowIterator = sheet.iterator();

                     while (rowIterator.hasNext()) {

                           Row row = rowIterator.next();

                           Iterator cellIterator = row.cellIterator();

                           while (cellIterator.hasNext()) {

                                  Cell cell = cellIterator.next();

                                  switch (cell.getCellType()) {

                                  case Cell.CELL_TYPE_BOOLEAN:

                                         System.out.print(cell.getBooleanCellValue() + "\t\t");

                                         break;

                                  case Cell.CELL_TYPE_NUMERIC:

                                         System.out.print(cell.getNumericCellValue() + "\t\t");

                                         break;

                                  case Cell.CELL_TYPE_STRING:

                                         System.out.print(cell.getStringCellValue() + "\t\t");

                                         break;

                                  }

                           }

                           System.out.println("");

                     }

                     file.close();

                     FileOutputStream out = new FileOutputStream(

                                  new File("C:\\test.xls"));

                     workbook.write(out);

                     out.close();

              } catch (FileNotFoundException e) {

                     e.printStackTrace();

              } catch (IOException e) {

                     e.printStackTrace();

              }

       }

}

Output:

Emp Id        Name          Salary       

1.0           John          20000.0             

2.0           Dean          42000.0             

3.0           Sam           28000.0             

4.0           Cass          60000.0             

By Jayakar