Apache POI - Reading Excel sheet using Java

Introduction::

POI, Apache POI - Java API to access Microsoft format files. POI (Poor Obfuscation Implementation) API is a fantastic way for Java programmers to access Microsoft document formats. The POI project consists of APIs for manipulating various file formats based upon Microsoft's OLE 2 Compound Document format using pure Java. In short, you can read and write MS Excel files using Java. An alternate way of generating a spreadsheet is via the Cocoon serializer.

HSSF is the POI Project's pure Java implementation of the Excel '97(-2002) file format. We will see how to read the data from a Excel sheet and display in console using java code in this article.

Features of HSSF

HSSF provides a way to read spreadsheets create, modify, read and write XLS.
Eventmodel api for efficient read-only access.
It provides full usermodel api for creating, reading and modifying XLS files.

Apache POI - Terminology

Before getting in to HSSF, we will see some of the POI-Terminologys

POIFS (Poor Obfuscation Implementation File System): Java APIs for reading and writing OLE (Object Linking and Embedding) 2 compound document formats.
HSSF (Horrible Spreadsheet Format): Java API to read Microsoft Excel.
HDF (Horrible Document Format): Java API to read and write Microsoft Word 97.
HPSF (Horrible Property Set Format): Java API for reading property sets using (only) Java.

Jsp To Create .xls File

we are going to create an excel using java .By going through the steps of this example we can create any number of excel pages.

To make an excel page we can use third party POI APIs. This API is provided by the Apache Jakarta (Jakarta POI - Java API To Access Microsoft Format Files ) Tomcat. You can download POI.jar form Apache Jakarta Project it is open source.

The POI project consists APIs for manipulating various file formats based on Microsoft, OLE 2 compound Document format using pure word files using Java. We can create, read or write MS Excel file using POI.
OLE 2 Compound Document Format is based on files including most Microsoft office files as XLS and DOC.

OUTPUT:

we are going to pass various data types into the cells and rows in the excel sheet using java .You can create any number of cells and rows and also you can pass different data type values into cells.

The package we need to import is :
java.io.*,
org.apache.poi.hssf.usermodel.HSSFSheet,
org.apache.poi.hssf.usermodel.HSSFCell,
org.apache.poi.hssf.usermodel.HSSFRow,
org.apache.poi.hssf.usermodel. HSSFWorkbook

org.apache.poi.hssf.usermodel.HSSFRow class is used to create rows in which we can set the cells and add the values.

org.apache.poi.hssf.usermodel.HSSFCell class is used to create a new cell and add the values into cells.

createRow((short)value):
This method is used to create a new row. The argument "value" (short type) is the number for which we are going to create
the row e.g. createRow((short)2) is for creating 2th row.

createCell((short)value):This method is used to create a new cell. The argument "value" (short type) is the number for which we are going to create the new cell e.g. createCell((short)0) is for creating 0th cell.

setCellValue(values):
This method is used to add the value into the cell. You can add any data types into cell by using this method. In this example, we have used four data types int , float , string and boolean which are being added at 0th ,1st,2nd,3th and 4th cells into 2th row.

In this example, at 0th cell we are passing float values ,at 1st cell we are passing date, at 2nd cell we are passing string ,at 3rd cell we are passing boolean value and at 4th position we are passing HSSFCell.CELL_TYPE_ERROR it will be null value as no error will occur.

The Jsp code of the program:

<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%
try{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short)2);
row.createCell((short) 0).setCellValue(1.1);
row.createCell((short) 1).setCellValue(new Date());
row.createCell((short) 2).setCellValue("a string");
row.createCell((short) 3).setCellValue(true);
row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);
FileOutputStream fileOut = new FileOutputStream
("c:\\excel\\type.xls");
hwb.write(fileOut);
fileOut.close();
out.println("Your excel file has been generated");

  } catch ( Exception ex ) {

  }
%>

-------------------------------------------------------------------------------------------------------------
The output Of Code::