Before you start with how to read excel 2007 file using apache poi article, suggested pre-requisites to read –
Introduction to Apache POI
How to set up Apache POI in java – selenium project.
How to read an excel 2003 (.xls) file using Apache POI
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 XSSFWorkbook class to get the desired workbook from FileInputStream.
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
4. Get the required sheet using class XSSFSheet (of the above workbook).
XSSFSheet sheet=wb.getSheetAt(0);
5. Now get the data from sheet using object created by XSSFSheet. 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.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Read2007FormatExcel {
public static void main(String[] args) throws Exception {
//location of your file - filepath
String filepath = "C:UsersPrakashDesktopTestData.xlsx";
//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.
XSSFWorkbook wb = new XSSFWorkbook(fis);
//Go to the desired sheet, here index 0 means first tab
XSSFSheet 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.
XSSFRichTextString cellData = sheet.getRow(i).getCell(j).getRichStringCellValue();
System.out.println(cellData);
}
}
wb.close();
fis.close();
}
}
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.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 Read2007FormatExcelUsingIterator {
@SuppressWarnings("deprecation")
public static void main(String[] args) throws Exception {
//location of your file - filepath
String filepath = "C:UsersPrakashDesktopTestData.xlsx";
//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.
XSSFWorkbook wb = new XSSFWorkbook(fis);
//Go to the desired sheet, here index 0 means first tab
XSSFSheet sheet = wb.getSheetAt(0);
//get total number of rows & columns so that we can iterate over it.
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;
}
}
}
wb.close();
fis.close();
}
}
Hope This Helps !!!! do post your questions if any !!!