Reading contents of Excel using java code


There are two good choices for reading & writing Microsoft Excel Spreadsheet files from Java, in a platform independent way, – jexcelapi and Jakarta POI (HSSF). Both of them provide nice interface to access Excel data structure and even generate new spreadsheet.

Comparison of JExcelAPI with Jakarta-POI (HSSF)

1. JExcelAPI is clearly not suitable for important data. It fails to read several files. Even when it reads it fails on cells for unknown reasons. In short JExcelAPI isn’t suitable for enterprise use.

2. HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2002) file format. It is a mature product and was able to correctly and effortlessly read excel data generated from various sources, including non-MS Excel products like Open Office, and for various versions of Excel. It is very robust and well featured. Highly recommended.

3. Performance was never a consideration in our tests because a) data integrity is the single most important factor and b) there didn’t appear to be any significant performance difference while running the tests; both of them were very fast. We didn’t bother to time it for the above reasons.

How to read Excel Excel Spreadsheet from Java using Jakarta POI (HSSF)

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.FileInputStream;

/**
* This is a sample to Read an Excel Sheet using Jakarta POI API
*
* @author Nitin Aggarwal
* @version 1.0
*/
public class ReadExcel {
/** Location where the Excel has to be read from. Note the forward Slash */
public static String fileToBeRead = “c:/work/JPOI/Read.xls”;

public static void main(String argv[]) {
try {
// Create a work book reference
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
fileToBeRead));
// Refer to the sheet. Put the Name of the sheet to be referred from
// Alternative you can also refer the sheet by index using
// getSheetAt(int index)
// HSSFSheet sheet = workbook.getSheet(“sheet1”);
HSSFSheet sheet = workbook.getSheetAt(0);
// Reading the TOP LEFT CELL
HSSFRow row = sheet.getRow(0);
// Create a cell ate index zero ( Top Left)
HSSFCell cell = row.getCell((short) 0);
// Type the content
System.out.println(“THE TOP LEFT CELL–> ”
+ cell.getRichStringCellValue());

} catch (Exception e) {
System.out.println(“!! Bang !! xlRead() : ” + e);
}

}

}

Things to keep in mind while using Jakarta POI (HSSF)

  • getPhysicalNumberOfRows() returns the physical number of rows which may be more than the actual (logical) number of rows. The same goes for getPhysicalNumberOfCells().
  • You should check for nulls when fetching the HSSFRow and HSSFCell objects as shown.
  • Remember that Excel tables are often sparsely populated. So choose your data structures accordingly.
  • POI accesses the data by sheet. In JExcelAPI you can directly access the data in any row and column.

In order to write to an excel file using java code click here

15 comments

  1. I am running the code . But it is raising an exception stating that Unable to read entire header; 22 bytes read; expected 512 bytes.
    Can u please help me……………………??????
    Thanks in advance.
    I want to read the contents of the cell

  2. Hi Meera, Looking at the issue you are facing, i would advice you to recreate a fresh excel file and try to read that. Because it might have to do with the excel file you are using.
    Its working fine for everybody else and I haven’t received any other complaints, still I would be happy to resolve your query.

    In case you still face the issue, share your error stack trace. this will provide me a better insight of the problem.

  3. Hello, I have already wrote a program to read data from an Excel file using POI API. Now the problem is I want to dump that read data into Oracle database. Can u provide me code to do that. The following is the code I have written for reading the data. Please guide if I have done anything wrong.

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;

    import java.io.*;
    import java.util.*;
    import java.sql.*;
    /**
    * This java program is used to read the data from a Excel file and display them
    * on the console output.
    *
    * @author Sandeep
    */
    public class POIExcelReader
    {
    /** Creates a new instance of POIExcelReader */
    public POIExcelReader ()
    {
    try
    {
    Class.forName(“oracle.jdbc.driver.OracleDriver”);
    Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”, “scott”, “tiger”);
    Statement stmt=con.createStatement();
    }
    catch(Exception e)
    {
    e.printStackTrace();
    }
    }
    /**
    * This method is used to display the Excel content to command line.
    * @param xlsPath
    */
    @SuppressWarnings (“unchecked”)
    public void displayFromExcel (String xlsPath)
    {
    InputStream inputStream = null;
    try
    {
    inputStream = new FileInputStream (xlsPath);
    }
    catch (FileNotFoundException e)
    {
    System.out.println (“File not found in the specified path.”);
    e.printStackTrace ();
    }
    POIFSFileSystem fileSystem = null;
    try
    {
    fileSystem = new POIFSFileSystem (inputStream);
    HSSFWorkbook workBook = new HSSFWorkbook (fileSystem);
    HSSFSheet sheet = workBook.getSheetAt (0);
    Iterator rows = sheet.rowIterator ();
    while (rows.hasNext ())
    {
    HSSFRow row = rows.next ();
    // once get a row its time to iterate through cells.
    Iterator cells = row.cellIterator ();
    while (cells.hasNext ())
    {
    HSSFCell cell = cells.next ();
    // Now we will get the cell type and display the values accordingly.
    System.out.println();
    switch (cell.getCellType ())
    {
    case HSSFCell.CELL_TYPE_NUMERIC :
    {
    // cell type numeric.
    System.out.print (cell.getNumericCellValue ()+”\t”);
    break;
    }
    case HSSFCell.CELL_TYPE_STRING :
    {
    // cell type string.
    HSSFRichTextString richTextString = cell.getRichStringCellValue();
    System.out.print (richTextString.getString()+”\t”);
    break;
    }
    default :
    {
    // types other than String and Numeric.
    System.out.println (“Type not supported.”);
    break;
    }
    }
    System.out.println();
    }
    }
    }
    catch (IOException e)
    {
    e.printStackTrace ();
    }
    }
    public static void main (String[] args)
    {
    POIExcelReader poiExample = new POIExcelReader ();
    String xlsPath = “e:/data.xls”;
    poiExample.displayFromExcel(xlsPath);
    }
    }

  4. hi nithin i have a small problem while reading the excel sheet data what iam trying to do is trying to read the the date values from one cell and time values from another cell but as both are using the same class so iam getting the values in time format or date format iam not able to get how to differentiate the both the below is my sample code thanks in advance

    if (HSSFDateUtil.isCellDateFormatted(cell)) {
    // format in form of M/D/YY

    cal.setTime(HSSFDateUtil.getJavaDate(num));
    cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
    cellText = cal.get(Calendar.MONTH)+1 + “/” +cal.get(Calendar.DAY_OF_MONTH) + “/” +cellText;

    // cal.setTime(cell.getDateCellValue());

    datetext = cal.get(Calendar.HOUR) + “:” +cal.get(Calendar.MINUTE) + “:” +cal.get(Calendar.SECOND);

    }

  5. Can u breif the idea that how a time value can be read from excel. I will make it little bit clear. The excel column with format time. For example i have value as ‘1:12:23 AM’. The HSSFRow is raeding that value as a date itself. So my time ‘1:12:23 AM’ becomes 1899-12-12. So is there any way i can handle this situation?

  6. Hi Sujit,

    I did this years ago and can’t remember the details but surely you will have to use a custome date formatter to pick it in the desired format.

  7. Thats fantastic mate, it would be good for organisations willing to pay for the product though… as its a licensed product.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s