Friday, 12 June 2015

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

}

6 comments:

  1. Can you please give the source code need to telly something.

    ReplyDelete
    Replies
    1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training from India . Nowadays Java has tons of job opportunities on various vertical industry.

      Delete
  2. May i know whoose source code you want?

    ReplyDelete
  3. code for extraction of data from Excel to eclipse console using java with selenium

    ReplyDelete