How to read excel 2003 (.xls) file using Apache POI?
Before you start with how to read excel 2003 file using apache poi, suggested pre-requisites to read –
Introduction to Apache POI
How to set up Apache POI in java – selenium project.
To start with the article, about how to read an excel file using Apache POI, Pre-rwquisite is, you have configured Apache POI in your project. If not, refer above link.
As we saw earlier, HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. Whereas XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
Let us understand about excel first.
First find out the excel file, known as Workbook here. Then within a workbook, we have multiple sheets. Go to the desired sheet using sheet name / sheet index. Then go to row & column position & get the cell data.
Below are the steps to Read excel sheet
1. Create object of the File, provide file path where your excel sheet is placed. Make sure that Excel file is in 2003 format (.xls format).
2. Get the file in FileInputStream which we will use for reading excel sheet.
3. Now use HSSFWorkbook class to get the desired workbook from FileInputStream.
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
4. Get the required sheet using class HSSFSheet (of the above workbook).
HSSFSheet sheet=wb.getSheetAt(0);
5. Now get the data from sheet using object created by HSSFSheet.
Look at the below example.
There are two ways
1. If the excel sheet has only String data (or one type of data)
2. If the excel sheet has combination of data like String /int/Boolean etc.
Consider the First point when Excel sheet has only one type of data. Below ScreenShot shows the input excel file.
Observe the below piece of code, which will be used to read excel file data (have only one type of data – String here)
package apachePOI;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Read2003FormatExcel {
public static void main(String[] args) throws Exception {
//location of your file - filepath
String filepath = "C:UsersPrakashDesktopTestData.xls";
//Create File object with above file path
File srcFile = new File(filepath);
//Create FileInputStream for above srcFile - to read file
FileInputStream fis = new FileInputStream(srcFile);
//Excel file is nothing but workbook, so create workbook object.
HSSFWorkbook wb = new HSSFWorkbook(fis);
//Go to the desired sheet, here index 0 means first tab
HSSFSheet sheet = wb.getSheetAt(0);
//get total number of rows & columns so that we can iterate over it.
int TotalNumOfRows = sheet.getLastRowNum()+1;
int TotalNumOfCols = sheet.getRow(0).getLastCellNum();
//Iterate over the sheet, using total number of rows and columns
for (int i = 0; i < TotalNumOfRows; i++ ){
for (int j = 0; j < TotalNumOfCols; j++){
//get the cell data using row number and column number of the cell.
HSSFRichTextString cellData = sheet.getRow(i).getCell(j).getRichStringCellValue();
System.out.println(cellData);
}
}
}
}
Now, in Another case, when Excel sheet contain all type of data, example – String / int / Boolean etc.
Observe below piece of code – to read data from excel sheet containing all type of data (can be used even if excel sheet has one type of data)
package apachePOI;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFRow;
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 class Read2003FormatExcelUsingIterator {
public static void main(String[] args) throws Exception {
//location of your file - filepath
String filepath = "C:UsersPrakashDesktopTestData.xls";
//Create File object with above file path
File srcFile = new File(filepath);
//Create FileInputStream for above srcFile - to read file
FileInputStream fis = new FileInputStream(srcFile);
//Excel file is nothing but workbook, so create workbook object.
HSSFWorkbook wb = new HSSFWorkbook(fis);
//Go to the desired sheet, here index 0 means first tab
HSSFSheet sheet = wb.getSheetAt(0);
//get total number of rows & columns so that we can iterate over it.
int TotalNumOfRows = sheet.getLastRowNum()+1;
int TotalNumOfCols = sheet.getRow(0).getLastCellNum();
Iterator<Row> itr = sheet.rowIterator();
while (itr.hasNext()){
Row row = itr.next();
Iterator<Cell> itrCell = row.cellIterator();
while (itrCell.hasNext()){
Cell cell = itrCell.next();
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.println("cell type is Numeric: "+cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println("cell type is String: "+cell.getStringCellValue());
break;
}
}
}
}
}
Hope This Helps !!!! do post your questions if any !!!
