Jakarta POI Frame work
POI stands for Poor Obfuscation Implementation. It is Jakarta framework.
It is useful for reading and writing into Microsoft Excel Sheets and Word document.
The mature API from Jakarta POI is the HSSF API (Horrible Spread Sheet Format ) with accesses Microsoft Excel documents.
POI Terminology:
1. POIFS : Poor Obfuscation Implementation File System - Java APIs for reading and writing OLE (Object
Linking and Embedding) to compound document formats.
2. HSSF : Horrible Spread Sheet Format - Java API to read from and write into Microsoft Excel.
3. HDF : Horrible Document Format - Java API to read from and write into Microsoft Word 97.
4. HPSF : Horrible Property Set Format - Java API for reading property sets using (only) Java.
Create An Excel Document:-
Steps:
1. create a WorkBook:
HSSFWorkbook workBook = new HSSFWorkbook();
2. create a new work sheet in the workbook and name the worksheet "test excel".
HSSFSheet sheet = workBook.createSheet("Test Excel");
3. create a new row in the sheet.
HSSFRow row = sheet.createRow((short)0);
4. create a cell in the row
HSSFCell cell = row.createCell((short)0);
5. put some data in the cell
cell.setCellValue(" Welcome to Excel");
6. write the workbook into the file system
workbook.write(fileOutputStream);
Read Data From the Excel Document:
1. create a new excel document reference:
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filetoberead));
2. refer to the sheet - by default the first sheet in the excel document is ate reference 0
HSSFSheet sheet = workbook.getSheetAt(0); or
by name
HSSFSheet sheet = workbook.getSheet("sheet1");
3. refer to a row
HSSFRow row = sheet.getRow(0);
4. refer to the cell in the row
HSSFCell cell = row.getCell((short)0);
5. get the values in that cell
cell.getStringCellValue();
Code Samples:
1. writing to excel using java code :
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class CreateXL{
public static String outputFile = "D:/work/java-excel.xls";
public static void main(String[] args){
try{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow((short)0);
HSSFCell cell = row.createCell((short)0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("rams"); // or cell.setCellValue(1234567.0);
FileOutputStream fout = new FileOutputStream(outputFile);
workbook.write(fout);
fout.flush();
fout.close();
}catch(Exception e){
e.printStackTrace();
}
}
}