This article deals with reading data from an Excel file using POI.
In the article Write to Excel Sheet Using POI it has been discussed about how to write "EMPLOYEE DETAILS" into an Excel file using POI api. We can use the same Employee Details excel sheet to read.
Consider the following class which is used to read the contents of an Excel file using POI.
POIExcelReader.java
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
package in.techdive.java.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
public class POIExcelReader
{
public static void main(String[] args)
{
try
{
InputStream input = new FileInputStream("C:\\emp.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
HSSFRow row = (HSSFRow) rows.next();
System.out.println("The Row Number is " + row.getRowNum());
// Iterate over each cell in the row and print out the cell's content
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
HSSFCell cell = (HSSFCell) cells.next();
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
default:
System.out.println("unsuported sell type");
break;
}
}
}
}
catch (IOException ex)
{
ex.printStackTrace();
}
}
}
The above code reads the Excel file; create a workbook out of it. Then it extracts the Excel sheet from the workbook. It then iterates the rows & columns in the Excel sheet and prints the contents of the sheet.
Here is the sample output
EMPLOYEE DETAILS
The Row Number is 5
EMPLOYEE ID
EMPLOYEE NAME
AGE
SALARY
DEPARTMENT
The Row Number is 6
1001.0
George
35.0
50000.0
IT
The Row Number is 7
1002.0
George
41.0
60000.0
FINANCE
The Row Number is 8
1003.0
George
32.0
45000.0
HR
The Row Number is 9
1004.0
George
29.0
40000.0
MARKETING
The Row Number is 10
1005.0
George
38.0
54000.0
SALES