Java - Reading Excel Sheet Documents in Java

Home > Java > Reading Excel Sheet Documents in Java

Reading Excel Sheet Documents in Java

December 19th, 2008 Hitesh Agrawal Leave a comment Go to comments

For some reason i found Apache POI difficult to use for reading excel sheet, in terms of naming conventions and the code so i was searching for another Library for reading Excel Sheet in Java and i stopped at JXL.

Java Excel API namely JXL is an Java based API that allows us to read/write/edit the Excel Sheet. The main think i liked about it was its simplicity over Apache POI Package. This article will show you how we can read Excel Sheet using JXL.

Now here i am going to read an excel sheet having content:

EmpId	Name	Designation
1	ABC	Software Engineer
2	DFG	Sr Software Engineer
3	LOI	Team Leader
4	LKJ	Project Manager
5	QWE	Software Architech

I have uploaded the same excel sheet on the server you can download it from here: Download Excel Sheet

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Locale;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.DateCell;
 
public class ReadXLSheet {
 
	public void init(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			contentReading(fs);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
 
	//Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}
 
	public void contentReading(InputStream fileInputStream) {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[] = null;
		int rowCount = '0';
		int columnCount = '0';
		DateCell dc = null;
		int totalSheet = 0;
 
		try {
			ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);
 
			totalSheet = workbook.getNumberOfSheets();
			if(totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				for(int j=0;j<totalsheet ;j++) {
					System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
				}
			}
 
			//Getting Default Sheet i.e. 0
			s = workbook.getSheet(0);
 
			//Reading Individual Cell
			getHeadingFromXlsFile(s);
 
			//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
			System.out.println("Total Rows inside Sheet:" + s.getRows());
			rowCount = s.getRows();
 
			//Total Total No Of Columns in Sheet
			System.out.println("Total Column inside Sheet:" + s.getColumns());
			columnCount = s.getColumns();
 
			//Reading Individual Row Content
			for (int i = 0; i < rowCount; i++) {
				//Get Individual Row
				rowData = s.getRow(i);
				if (rowData[0].getContents().length() != 0) { // the first date column must not null
					for (int j = 0; j < columnCount; j++) {
						switch (j) {
						case 0:
							System.out.println("Employee Id:" + rowData[j].getContents());
						case 1:
							System.out.println("Employee Name:" + rowData[j].getContents());
						case 2:
							System.out.println("Employee Designation:" + rowData[j].getContents());
						default:
							break;
						}
					}
				}
			}
			workbook.close();			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}
 
		public static void main(String[] args) {
		try {
			ReadXLSheet xlReader = new ReadXLSheet();
			xlReader.init("/home/hitesh/Desktop/test.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Total Sheet Found:3
----------------------------------------------
Sheet Name:Sheet1
Sheet Name:Sheet2
Sheet Name:Sheet3
----------------------------------------------
Column Heading:EmpId
Column Heading:Name
Column Heading:Designation
----------------------------------------------
Total Rows inside Sheet:6
----------------------------------------------
Total Column inside Sheet:3
----------------------------------------------
Employee Id:EmpId
Employee Name:Name
Employee Designation:Designation
----------------------------------------------
Employee Id:1
Employee Name:ABC
Employee Designation:Software Engineer
----------------------------------------------
Employee Id:2
Employee Name:DFG
Employee Designation:Sr Software Engineer
----------------------------------------------
Employee Id:3
Employee Name:LOI
Employee Designation:Team Leader
----------------------------------------------
Employee Id:4
Employee Name:LKJ
Employee Designation:Project Manager
----------------------------------------------
Employee Id:5
Employee Name:QWE
Employee Designation:Software Architect

With respect to the following code written i am able to perform the following operation:

Calculate total no of Sheets inside the Excel Sheet
List all the Sheet Name used inside Excel Sheet
Read Individual Cell for specifying the cell position – in code have read the column heading i.e. 1st row
Read Total Modified Rows in the Excel Sheet
Read Total Modified Columns in the Excel Sheet
Read Individual Row Content

Custom Search

Popular Articles:

Share and Enjoy:

Categories: Java Tags: Java

Comments (41) Trackbacks (0) Leave a comment Trackback

Surya Suravarapu

December 26th, 2008 at 10:05 | #1

Reply | Quote

If I have to write this code using POI, to accomplish the tasks that you have performed, the code would have been pretty similar with very similar API calls. I see the difficulty level as more or less the same when compared with POI. My question is how is this different from POI?
Hitesh Agrawal

December 28th, 2008 at 22:36 | #2

Reply | Quote

Hi Surya,
Difference comes with the package naming convention used. JXL is purely used for Excel in Java. Where as in Apache POI there are different projects made for different Microsoft Office Files, E.g HWPF, HSSF, HPSF etc. Now as a developer you have to check which one if for Excel Parsing, download the package and then implement it.
Manish Kumar Chauthani

January 2nd, 2009 at 11:58 | #3

Reply | Quote

Hi ,
I liked your tutorial.
Manish
Avijit

January 6th, 2009 at 04:10 | #4

Reply | Quote

there is a problem while having multiple sheets in xls file. i want to accsess multiple sheet values at once, but it returns a ovelap value of two sheets.

but the tutorial is too good.
plz solve the problem
Hitesh Agrawal

January 6th, 2009 at 04:44 | #5

Reply | Quote

Hi Avijit,
Can you paste the source code you are using.

Hitesh agarwal
Vivek Mittal

January 11th, 2009 at 14:08 | #6

Reply | Quote

Hi Hitesh,
Very nice tutorial man. Keep it up
Abhishek

February 18th, 2009 at 07:55 | #7

Reply | Quote

Hi Hitesh,

Nice work man. I had a question. Is it possible to write the macros of Excel in Java and execute those macros while working on the spreadsheet.

Thanks
Abhi
seeraz

May 29th, 2009 at 02:41 | #8

Reply | Quote

Great tutorial , thx a lottt …
Moha

June 2nd, 2009 at 07:38 | #9

Reply | Quote

Hi,

First of all, thank you for the tutorial.
I’ve a problem. I’ve downloaded the api but I don’t know where to save it in eclipse. The reason is that I putted the unzipped files of the api into plugin folder but the program you gave as an example still asks for jxl.
Could you please help?
Thanks.
rohan

September 1st, 2009 at 04:40 | #10

Reply | Quote

Very nice xls read code..thanx a lot.
barakha

September 2nd, 2009 at 01:30 | #11

Reply | Quote

very nice tutorial fro excel reading. Thanks
Uma

September 10th, 2009 at 21:19 | #12

Reply | Quote

how to read data from excel file using column name.
Say for eg,
Place Country
Chennai India
Mumbai India
Need to read using the column Place, Country.
Please help me out.
qing

October 13th, 2009 at 18:06 | #13

Reply | Quote

hi ，if excel file have 18bm , how to read?
id

October 15th, 2009 at 13:17 | #14

Reply | Quote

hey..
I am trying to run yr program but it keeps on giving me wrong java version 50.0 should be 49.0.

Dosent JXL work with Java 1.5?
Let me know if there is anythign i m missing?
James Zhao

October 23rd, 2009 at 14:36 | #15

Reply | Quote

How can we insert a new row to the sheet?
How can we copy the existing row format to a new row?
Swamy

October 28th, 2009 at 06:03 | #16

Reply | Quote

Hi Hitesh,

Nice library, keep it up, useful for people.
How can we insert a new row to the sheet?
How can we copy the existing row format to a new row?

Rgds
Swamy.

suresh

October 30th, 2009 at 22:31 | #17

Reply | Quote

Suresh hitesh,

Nice Example. Tutorial is very good.
I have one Problem with reading data from Excel Sheet, if the Excel Sheet data is not starting from first column. if Excel sheet data is not in a tablar form
then how to read data from Excel Sheet.

suresh

October 30th, 2009 at 22:47 | #18

Reply | Quote

hi Hitesh,

I havae one problem with reading
data from Excel Sheet.If the Excel Sheet
Data is not in a tabler form then how to
read data. I run the same Example but it
rises ArrayIndexOutOfBounds Exception.
Plz Tell The Solution for this problem.....

divs

November 10th, 2009 at 05:31 | #19

Reply | Quote

Hi hitesh,
The tutorial was of g8 help

Thanks
Ganesh

November 10th, 2009 at 23:23 | #20

Reply | Quote

Hi Hitesh,

I got the following exception when i tried to run the ReadXLSheet.java.

jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.(CompoundFile.java:116)
at jxl.read.biff.File.(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at com.sample.ReadXLSheet.contentReading(ReadXLSheet.java:65)
at com.sample.ReadXLSheet.init(ReadXLSheet.java:30)
at com.sample.ReadXLSheet.main(ReadXLSheet.java:20)

What could be the reason for this.

Initially i got the following Exception when i tried run the ReadXLSheet.java.

java.lang.UnsupportedClassVersionError: Bad version number in .class file

Later i complied the jexcelapi_2_6_12 source with my JVM and used the generated jar. Now I am not getting this error but i am getting
jxl.read.biff.BiffException. can u please help me to resolve this issue.

thanks,
ganesh
- Hitesh Agrawal
  
  November 14th, 2009 at 10:04 | #21
  
  Reply | Quote
  
  Hi Ganesh,
  Haven’t encountered this error. Will have to check this out.
  
  Thanks,
  Hitesh Agarwal
Sandeep

November 30th, 2009 at 23:03 | #22

Reply | Quote

Hi Hitesh,
Thanku for ur comments and tutorial.
I have a problem regarding excel sheet.
i have two excel sheet, Both are at the same system.
if i do change in one excel sheet, then it affect the other excel sheet.
i am able to ping other system and can send the message the other system by using socket programming.
but i have no idea how to update the excel sheet which is on the other system as the same i m doing when the excel sheet at the same system.

Please reply ASAP

Thanks in advance.
Deepak Sharma

December 30th, 2009 at 00:16 | #23

Reply | Quote

@Ganesh

Check if u r using .xlsx instead of .xls
I get this exception (Unable to recognize OLE stream) if I use new xlsx file.
So using jexcelapi_2_6_12 will solve xlsx format problem?
Swagata

January 6th, 2010 at 21:56 | #24

Reply | Quote

@Ganesh

Check whether the file test.xls is blocked.. Right click and go to properties. If it is blocked, unblock it.. Some security feature in Windows that blocks some downloaded files until you unblock them.. Try if this helps!
umar faisol

January 7th, 2010 at 00:36 | #25

Reply | Quote

thanks for your tutorial ^_^
CAG

January 22nd, 2010 at 12:18 | #26

Reply | Quote

thank you so much, i was working on a problem where i needed to strop xls file ( 400+) – only one time. You saved me day with this JAR!
Roshan Khandelwal

January 31st, 2010 at 23:57 | #27

Reply | Quote

Hi everyone ,
The code works pretty fine with one change.

After every case statement add a “break ”
so the revised code would be :

switch (j) {
case 0:
System.out.println(“Employee Id:” + rowData[j].getContents());
break;
case 1:
System.out.println(“Employee Name:” + rowData[j].getContents());
break;
case 2:
System.out.println(“Employee Designation:” + rowData[j].getContents());
break;
default:
break;
}

With the code as given , it loops through all three condition and gives an erroneous output.
Thanks for the code .
Paramjit

February 18th, 2010 at 03:26 | #28

Reply | Quote

Been very useful…great work
Manjunath

March 15th, 2010 at 11:41 | #29

Reply | Quote

@Ganesh

I am getting the same error as Ganesh got. My file is saved as .xls and is unblocked,but still getting the same BiffException.Please help
Prema GV

March 25th, 2010 at 04:41 | #30

Reply | Quote

Hi thanks for the code………
Code worked fine when i run first time got result too….

But when i run same code after three times of exection…… its giving file not found and null point exception……….. tHE path name specified is also correct….plzzzz get me back with solution as soon as possible……..
Prema GV

March 25th, 2010 at 04:49 | #31

Reply | Quote

Hi thanks for the code………
Code worked fine when i run first time got result too….

But when i run same code after three times of exection…… its giving file not found and null point exception……….. plzzzz get me back with solution as soon as possible……..

Thanks in advance………………
sadanala

April 13th, 2010 at 00:50 | #32

Reply | Quote

where do i get jar file?
Menaka

April 19th, 2010 at 22:53 | #33

Reply | Quote

Hi Hitesh,
Do u have any idea about writing to .xlsx files. If yes can u pls share some code samples.
Thanks
- Hitesh Agrawal
  
  April 21st, 2010 at 11:46 | #34
  
  Reply | Quote
  
  Hi Meneka,
  Latest Apache POI supports xslx file.
  
  Thanks,
  Hitesh Agarwal
Gaurav Arora

May 6th, 2010 at 17:22 | #35

Reply | Quote

I have downloaded your code and tryied to run it on ubuntu platform with jdk6
but at run time its showed this
Exception in thread \main\ java.lang.NoClassDefFoundError: ReadXLSheet
at java.lang.Class.initializeClass(libgcj.so.10)
Caused by: java.lang.ClassNotFoundException: jxl.Cell not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
at java.net.URLClassLoader.findClass(libgcj.so.10)
at java.lang.ClassLoader.loadClass(libgcj.so.10)
at java.lang.ClassLoader.loadClass(libgcj.so.10)
at java.lang.Class.initializeClass(libgcj.so.10)

instead of output

CAN you please tell me whats the problem with my code i am not able to get and its urget

seeking towards help…
Thanks in advance
- Hitesh Agrawal
  
  May 8th, 2010 at 02:09 | #36
  
  Reply | Quote
  
  Hi Gaurav,
  You will have to add JExcel API in your class path while compiling the java file, you can download the JExcelAPI from http://jexcelapi.sourceforge.net/
  
  Thanks,
  Hitesh Agarwal
Thomas

May 24th, 2010 at 07:23 | #37

Reply | Quote

Hi,

I’ve written the following function:

public void read() throws IOException {
File inputWorkbook = new File(“c:/temp/test.xls”);
Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
…
} catch (BiffException e) {
e.printStackTrace();
}
}

When I run this function within a normal Java Application together with JFace everything works fine. But when I run this function inside a RCP project I get the following error:

java.lang.ClassNotFoundException: jxl.read.biff.BiffException
at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:494)
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:410)
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:398)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:105)
at java.lang.ClassLoader.loadClass(Unknown Source)
at model.CollectionManager.newOilFor(CollectionManager.java:120)
at model.CollectionManager.loadCollection(CollectionManager.java:107)
at model.CollectionManager.getCollection(CollectionManager.java:56)
at hellooil.CollectionViewContentProvider.getElements(CollectionViewContentProvider.java:39)
…

Does someone know what could be wrong?
Shan

June 29th, 2010 at 01:24 | #38

Reply | Quote

I am getting this error when i run the code posted above. I dont really understand the problem. Thanks in advance.

jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.(CompoundFile.java:116)
at jxl.read.biff.File.(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at ReadXLSheet.contentReading(ReadXLSheet.java:55)
at ReadXLSheet.init(ReadXLSheet.java:20)
at ReadXLSheet.main(ReadXLSheet.java:109)
Neeraj

July 2nd, 2010 at 08:08 | #39

Reply | Quote

Can anybody help me in writing the content of an excel file into an XML file in java.

Actually i m able to read the excel file but m trying to output the data into the xml file through an object of FileWriter but its not working…
please provide me with some better code.
_________________________________________________
This is my code which m trying to execute:

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

public static void main(String[] args) throws IOException {
String inputFile = “c:/data.xls”;

File inputWorkbook = new File(inputFile);

File fileout = new File(“C:/data.xml”);
FileWriter fw = new FileWriter(fileout);

fw.write(“”);

Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines

for (int i = 0; i < sheet.getColumns(); i++) {
for (int j = 0; j < sheet.getRows(); j++) {
Cell cell = sheet.getCell(j, i);
//CellType type = cell.getType();
if (cell.getType() == CellType.LABEL) {
fw.write(" “+ cell.getContents()+”");
}

if (cell.getType() == CellType.NUMBER) {
fw.write(” “+ cell.getContents()+”");
}

}
}
fw.write(“”);

} catch (BiffException e) {
e.printStackTrace();
}
}
}
Neeraj

July 2nd, 2010 at 08:11 | #40

Reply | Quote

Can anybody help me in writing the content of an excel file into an XML file in java.

Actually i m able to read the excel file but m trying to output the data into the xml file through an object of FileWriter but its not working…
please provide me with some better code.
_________________________________________________
This is my code which m trying to execute:
**NOTE:I wasn’t able to write tags on this site so i used curly braces for tags instead of angular braces

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

public static void main(String[] args) throws IOException {
String inputFile = “c:/data.xls”;

File inputWorkbook = new File(inputFile);

File fileout = new File(“C:/data.xml”);
FileWriter fw = new FileWriter(fileout);

fw.write(“{root}”);

Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines

for (int i = 0; i < sheet.getColumns(); i++) {
for (int j = 0; j < sheet.getRows(); j++) {
Cell cell = sheet.getCell(j, i);
//CellType type = cell.getType();
if (cell.getType() == CellType.LABEL) {
fw.write("{tag} "+ cell.getContents()+"{/tag}");
}

if (cell.getType() == CellType.NUMBER) {
fw.write("{tag} "+ cell.getContents()+"{/tag}");
}

}
}
fw.write("{/root}");

} catch (BiffException e) {
e.printStackTrace();
}
}
}
Imran

July 26th, 2010 at 01:57 | #41

Reply | Quote

Hey can any one tell me from where should i download jxl package and where should I place it to compile my source code.

No trackbacks yet.

Reading POP3 Mails Using Java Checking Session TimeOut during Ajax Call

Techie Zone

Reading Excel Sheet Documents in Java

Popular Articles:

Java Tutorial

Translator