Utility class to read excel file in java and return rows as list

In this tutorial we gonna make a utility class that accepts file as argument ( .xlsx file ), parse it and return rows as List. You can make use of this class in your project to read excel file and get the contents. For this first we need Apache POI. Apache POI is the pure Java API for reading and writing Excel files in both formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later).

To use apache POI in your java project :

• Download the latest release of the library
• Extract the zip file and add the appropriate JAR files to your project’s class path:
• If you are operating only on Excel 2003 format, poi-VERSION.jar is enough.
• If you are operating on Excel 2007 format, then include the following:
• poi-ooxml-VERSION.jar
• poi-ooxml-schemas-VERSION.jar
• xmlbeans-VERSION.jar

Apache POI basic's guide

HSSF	Denotes the API is for working with Excel 2003 and earlier
XSSF	Denotes the API is for working with Excel 2007 and later
Workbook	High level representation of an Excel workbook
Sheet	High level representation of an Excel worksheet
Row	High level representation of a row in a spreadsheet
Cell	High level representation of a cell in a row

Class Implementation

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Takes the excel data parse it
 * It return the list of rows
 * Note : the first row is skipped because of the Header
 * @author Saket
 *
 */
public class Upload {

 private File file;

 public Upload(File file){
  this.file = file;
 }

 public ArrayList<ArrayList<Object>> extractAsList(){
  
  ArrayList<ArrayList<Object>> list = new ArrayList<ArrayList<Object>>();
  int maxDataCount =0;
  try{
       FileInputStream file = new FileInputStream(this.file);
      
      // Create Workbook instance holding reference to .xlsx file
      XSSFWorkbook workbook = new XSSFWorkbook(file);

      // Get first/desired sheet from the workbook
      XSSFSheet sheet = workbook.getSheetAt(0);

      // Iterate through each rows one by one
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
    
          Row row = rowIterator.next();
       
          //Skip the first row beacause it will be header
          if (row.getRowNum() == 0) {
     
              maxDataCount = row.getLastCellNum();
              continue;
     }
    
    /**
     * if row is empty then break the loop,do not go further
     */
    if(this.isRowEmpty(row,maxDataCount)){
     //Exit the processing
     break;
    }
    
    ArrayList<Object> singleRows = new ArrayList<Object>();
    
    // For each row, iterate through all the columns
    for(int cn=0; cn<maxDataCount; cn++) {

        Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
     
        switch (cell.getCellType()) {
         
            case Cell.CELL_TYPE_NUMERIC:
        
                                         if(DateUtil.isCellDateFormatted(cell)){
                                             singleRows.add( new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()) );
                                         }else
                                             singleRows.add(cell.getNumericCellValue());
                                         break;
        
            case Cell.CELL_TYPE_STRING:
                                         singleRows.add(cell.getStringCellValue());
                                         break;
      
            case Cell.CELL_TYPE_BLANK : singleRows.add(null);break;
      
            default : singleRows.add(cell.getStringCellValue());
        }

     }
     list.add(singleRows);
   }
   file.close();
   workbook.close();   
  } catch (Exception e) {  e.printStackTrace();}
  
  return list;
 }

 public boolean isRowEmpty(Row row,int lastcellno) {
     for (int c = row.getFirstCellNum(); c < lastcellno; c++) {
         Cell cell = row.getCell(c,Row.CREATE_NULL_AS_BLANK);
         if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
             return false;
     }
     return true;
 }
}

How to use this class

File excelFile = new File("imported.xlsx"); // Change the location and file name as per yours
Upload uploaded = new Upload(alreadyFile);
ArrayList<ArrayList<Object>> list = uploaded.extractAsList(); // Rows in excel will be returned as list

Now you got the excel rows as list, you can iterate through each row and get the desired cell value
For ex:

for(ArrayList<Object> singleRow : list){
    
    String firstCellVale = (String ) singleRow.get(0); // Get me the value of first column

}