How to Read Excel file using Java

Normally, to read a data in excel, first we should have access to workbook, sheet which we want to read as workbook contains multiple sheets and if you want to read a particular cell we need location of a Cell.

In this article, we will discuss how to access workbook, sheet and a Cell using Jxl library Download jxl jar and add it to build path.

You can also consider usingApache Poi Library to perform read and write operations with excel sheets because of its better documentation, more features, active development, and Excel 2007+ format support.

As we know JXL doesn't support Excel 2007 ".xlsx" file format. It only supports the old BIFF (binary) ".xls" format. Where as Apache POI supports both Excel 2003 - xls and Excel 2007 - xlsx file formats.

To start with gaining access to Workbook, we should always remember the below command:

String FilePath = "d://filepath.xls";
FileInputStream fs = new FileInputStream(FilePath);
Workbook wb = Workbook.getWorkbook(fs);

Or You can also directly send the file as below

Workbook wb = Workbook.getWorkbook(new File("samplefile.xls"));

Now to get the access to the particular sheet, we should use the below command:

Sheet sh = wb.getSheet(0); // this is to get the access to Sheet1. 

If you want to get the access to sheet2, you should specify as below:

Sheet sh = wb.getSheet(1);

You can also get the sheet access by sheet name, you should specify as below:

Sheet sh = wb.getSheet("sheet1");

Now we will get the content in particular location, which will return contents as a string

String CellGetContent = sh.getCell(0,0).getContents();
System.out.println(CellGetContent);

We can also write it as :

System.out.println(sh.getCell(0,0).getContents());

There is an other style to get the cell contents as below:

Cell Row0Col0 = sheet.getCell(0,0);
Cell Row1Col1 = sheet.getCell(1,1);
String FirstRowFirstColumn = Row0Col0.getContents();
String SecondRowSecondColumn = Row1Col1.getcontents();

The below is the input sheet for the example program:

excel sheet

Please find the below code in which we will read a data from excel sheet and print using for loop

package com.pack;

import java.io.FileInputStream;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcelFile {
	
	public void readExcel() throws BiffException, IOException {
		String FilePath = "D:\\sampledoc.xls";
		FileInputStream fs = new FileInputStream(FilePath);
		Workbook wb = Workbook.getWorkbook(fs);

		// TO get the access to the sheet
		Sheet sh = wb.getSheet("Sheet1");

		// To get the number of rows present in sheet
		int totalNoOfRows = sh.getRows();

		// To get the number of columns present in sheet
		int totalNoOfCols = sh.getColumns();

		for (int row = 0; row < totalNoOfRows; row++) {

			for (int col = 0; col < totalNoOfCols; col++) {
				System.out.print(sh.getCell(col, row).getContents() + "\t");
			}
			System.out.println();
		}
	}

	public static void main(String args[]) throws BiffException, IOException {
		ReadExcelFile DT = new ReadExcelFile();
		DT.readExcel();
	}
}

The output of the below program is:

Username password
testuser1 testpassword1
testuser2 testpassword2
testuser3 testpassword3
testuser4 testpassword4

Here the most significant difference between JXL and Apache POI is, JXL does not support the ".xlsx" format (Excel 2007 and +) , it just supports the old ".xls" format. Where as Apache POI supports both XLS and XLSX formats.
And JXL API was last updated in 2009 BUT Apache POI is actively maintained.

Hope the above code works. Please let me know if you face any problems. Thank you.

Excel API: 

Comments

Thanks for soo much help.Code worked fine.

This code to read data from xl is quite simple to understand and easy to use., keep up the good job., keep posting more useful articles like these..

code to read data from excel is easy and simple to use., keep up the good job

Nice Explanation of Read Excel File , but where is the main Class ,then what is the link between two class's

Main is contained inside class itself

The way you have explained is really good and understable..Thank you dude..it helped me a lot...keep up...!!

Pls help with the Code for reading data from different sheets of sigle excel file.

Good explanation for new users thanks a lot man!!
Regards,
Harish

Exception in thread "main" jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at jxl.Workbook.getWorkbook(Workbook.java:253)
at invoke.browserPackage.ReadExcelFile.readExcel(ReadExcelFile.java:14)
at invoke.browserPackage.ReadExcelFile.main(ReadExcelFile.java:36)

I got above exception

If your using xlsx format of spreadsheet, jxl will throw that error. Since it doesn't support the latest version xlsx, Instead change your spreadsheet to xls it will work.

Add new comment