Reading data from Excel file in Java using JExcel API

In this post we will learn how to read data from Excel file in Java.

Java provides an API for reading the data from excel sheet and it is called JExcel API. Java Excel API is a mature, open source java API enabling developers to read, write, and modify Excel spreadsheets dynamically. 

Here is the excel sheet data which we are going to use for reading:

Example.xls

Below is Java code for reading data, here we have tried to read 3 types of data from excel sheet i.e. String, Number and Date using LabelCell, NumberCell and DateCell:

ReadExcel.java

package com.technsolution;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

  private String inputFile;

  public void setInputFile(String inputFile) {
    this.inputFile = inputFile;
  }

  public void read() throws IOException  {
    File inputWorkbook = new File(inputFile);
    Workbook w = null;
    List<String> nameRowList = null;
    List<Double> marksRowList = null;
    List<Date> dateRowList = null;
    LabelCell lc;//for cell having String data
    NumberCell nc;//for cell having Numeric data
    DateCell dc;//for cell having date
    
    try {
      w = Workbook.getWorkbook(inputWorkbook);
      // Get the first sheet
      Sheet sheet = w.getSheet(0);

      // Loop over columns  
      for (int j = 0; j < sheet.getColumns(); j++) {
     Cell cell = sheet.getCell(j, 0);
     System.out.println("\n"+"Column Name => "+cell.getContents());//To print column values
     
     if(cell.getContents().equalsIgnoreCase("Name")){
     nameRowList = new ArrayList<String>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     lc = (LabelCell)styleColorVal;
     nameRowList.add(i, lc.getString());
     System.out.println(cell.getContents()+(i+1)+" :: "+nameRowList.get(i).toString()); //To print the row values for Name column
     }      
     }
     
     if(cell.getContents().equalsIgnoreCase("Marks")){
     marksRowList = new ArrayList<Double>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     nc = (NumberCell)styleColorVal;
     marksRowList.add(i, nc.getValue());
     System.out.println(cell.getContents()+(i+1)+" :: "+marksRowList.get(i).toString()); //To print the row values for Marks column
     }      
     }
     
     if(cell.getContents().equalsIgnoreCase("Date")){
     dateRowList = new ArrayList<Date>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     dc = (DateCell)styleColorVal;
     dateRowList.add(i, dc.getDate());
     System.out.println(cell.getContents()+(i+1)+" :: "+dateRowList.get(i).toString()); //To print the row values for Date column
     }      
     }
         
     
      }
    } catch (BiffException e) {
    e.printStackTrace();
    } catch (Exception ex){
    ex.printStackTrace();
    }finally{
    w.close();
    }
  }

  public static void main(String[] args) throws IOException {
    ReadExcel rd = new ReadExcel();
    rd.setInputFile("D:/Example.xls");
    rd.read();
  } 
} 

OUTPUT

Column Name => Name
Name1 :: Rahul
Name2 :: Kiran
Name3 :: Shweta
Name4 :: Raj
Name5 :: Sahil

Column Name => Marks
Marks1 :: 87.0
Marks2 :: 98.0
Marks3 :: 89.0
Marks4 :: 87.0
Marks5 :: 90.0

Column Name => Date
Date1 :: Thu Dec 20 05:30:00 IST 2012
Date2 :: Tue Aug 20 05:30:00 IST 2013
Date3 :: Sat Mar 20 05:30:00 IST 2010
Date4 :: Wed Jun 20 05:30:00 IST 2012
Date5 :: Mon Feb 20 05:30:00 IST 2012

To use the JExcel API we need to add jxl-2.6.jar which contains all the classes required for reading and writing so don't forget to include it in the project build path.

[Note: JExcel API works with Excel file having extension .xls only and not with .xlsx]