Read excel document in java code using Apache POI library.

Lets consider below sample.xlsx

Download Apache POI jars from following link https://poi.apache.org/download.html
Latest version 3.16 has been used in this example. Below are jars needed in classpath.


Read the workbook and first sheet (tab) using getSheetAt(0) as below:

XSSFWorkbook workbook = new XSSFWorkbook(file);		 
XSSFSheet tab1 = workbook.getSheetAt(0);

Iterate through each row and for each row, iterate through each cell. While reading each cell, verify the data in the cell is Numeric to read it as numeric and if not consider it as string.
There are more type of cellTypes available to use based on the data expected.

if(cell.getCellTypeEnum().compareTo(CellType.NUMERIC) == 0){
	return cell.getNumericCellValue() + "";
}else{
	return cell.getStringCellValue();
}

Complete class ExcelRead.java can be found below;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRead {

	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		
		readExcelDoc("C:/temp/sample.xlsx");
	}	
	
	public static void readExcelDoc(String filePath) throws IOException{
		FileInputStream file = new FileInputStream(new File(filePath));
        
		//Get the workbook object
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		 
		//Get first tab (sheet) from the workbook
		XSSFSheet tab1 = workbook.getSheetAt(0);
		 
		//Get iterator to all the rows in current sheet
		Iterator rowItr = tab1.iterator();
		int rowIndex = 1;
		while(rowItr.hasNext()){
			Row row = rowItr.next();
			if(rowIndex==1){
				rowIndex++;
				continue; // Skip header row
			}
			Iterator cellIterator = row.cellIterator();
			int cellIndex = 0;
			while(cellIterator.hasNext()){					
				Cell cell = cellIterator.next();
				if(cellIndex == 0){
					System.out.println("S.No = "+getCellValue(cell));
				}else if(cellIndex == 1){
					System.out.println("Software = " +getCellValue(cell));
				}else{
					System.out.println("Version = " +getCellValue(cell));
				}
				
				cellIndex++;
			}
		}				
	}
	
	private static String getCellValue(Cell cell){
		if(cell.getCellTypeEnum().compareTo(CellType.NUMERIC) == 0){
			return cell.getNumericCellValue() + "";
		}else{
			return cell.getStringCellValue();
		}
	}
}

If you execute above code, below will be the console output:

S.No = 1.0
Software = Java
Version = 1.8
S.No = 2.0
Software = Eclipse
Version = Kepler

 
Additional Details…
Exception in thread “main” org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

If you come across above error; this means you are using old class (HSSF) to read latest MS Office excel document. Change your code to use XSSF library (which is explained above post).

For further reading refer Java Programming Category posts.

Share this post

Read excel document in java code
Tagged on:     

Leave a Reply

Your email address will not be published. Required fields are marked *