Reading Excel Sheet Documents in Java
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | 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:
- JSON in JAVA
- Log4J Logging Inside Eclipse Console
- Modifying / Editing XML Document in JAVA
- Reading New Emails from Java Applications
- Date Manipulation in JAVA
- HTTP POST File Content in JAVA
- Sending Emails using Java
- JSP – Create Custom Tags
- Ajax Programming with JSP and Servlets
- MySql Batch Insert/Update in Java



































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?
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.
Hi ,
I liked your tutorial.
Manish
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
Hi Avijit,
Can you paste the source code you are using.
Hitesh agarwal
Hi Hitesh,
Very nice tutorial man. Keep it up
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
Great tutorial , thx a lottt …
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.
Very nice xls read code..thanx a lot.
very nice tutorial fro excel reading. Thanks
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.
hi ,if excel file have 18bm , how to read?
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?
How can we insert a new row to the sheet?
How can we copy the existing row format to a new row?
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 hitesh,
hi Hitesh,
Hi hitesh,
The tutorial was of g8 help
Thanks
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
Hi Ganesh,
Haven’t encountered this error. Will have to check this out.
Thanks,
Hitesh Agarwal
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.
@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?
@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!
thanks for your tutorial ^_^
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!
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 .
Been very useful…great work
@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
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……..
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………………
where do i get jar file?
Hi Hitesh,
Do u have any idea about writing to .xlsx files. If yes can u pls share some code samples.
Thanks
Hi Meneka,
Latest Apache POI supports xslx file.
Thanks,
Hitesh Agarwal
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
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
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?
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)
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();
}
}
}
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();
}
}
}
Hey can any one tell me from where should i download jxl package and where should I place it to compile my source code.