Introduction
Apache POI(Poor Obfuscation Implementation) is used to access Microsoft format files and the Horrible Spreadsheet Format(HSSF) is the pure Java implementation of the Excel ‘97(-2007) file format of POI project. HSSF provides a complete Java API for creating, modifying, reading and writing an XLS file. In this article let us see how to read a Microsoft excel file using java code.
Things You’ll Need
- Apache POI jar(Download).
- JDK 1.5 and above.
- Your Favourite IDE.
Excel file to be read is given in the image below,
sampleexcel.xls
The below java program is used to read the excel file and the classes used in our sample program are POIFSFileSystem, HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell.
POIFSFileSystem – This class is used to manages the entire life cycle of the filesystem.
HSSFWorkbook – This is the first object constructed used to write/read an excel file.
HSSFSheet - This class is used to create new spreadsheet which is called by HSSFWorkbook .
HSSFRow - This class represents the rows of a spreadsheet.
HSSFCell - This class represents the cell in a row of a spreadsheet.
ExcelSheetReader.java
package com.sample.excel;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
/**
* @author giftsam
*/
public class ExcelSheetReader
{
/**
* This method is used to read the data's from an excel file.
* @param fileName - Name of the excel file.
*/
private void readExcelFile(String fileName)
{
/**
* Create a new instance for cellDataList
*/
List cellDataList = new ArrayList();
try
{
/**
* Create a new instance for FileInputStream class
*/
FileInputStream fileInputStream = new FileInputStream(fileName);
/**
* Create a new instance for POIFSFileSystem class
*/
POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
/*
* Create a new instance for HSSFWorkBook Class
*/
HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
HSSFSheet hssfSheet = workBook.getSheetAt(0);
/**
* Iterate the rows and cells of the spreadsheet
* to get all the datas.
*/
Iterator rowIterator = hssfSheet.rowIterator();
while (rowIterator.hasNext())
{
HSSFRow hssfRow = (HSSFRow) rowIterator.next();
Iterator iterator = hssfRow.cellIterator();
List cellTempList = new ArrayList();
while (iterator.hasNext())
{
HSSFCell hssfCell = (HSSFCell) iterator.next();
cellTempList.add(hssfCell);
}
cellDataList.add(cellTempList);
}
}
catch (Exception e)
{
e.printStackTrace();
}
/**
* Call the printToConsole method to print the cell data in the
* console.
*/
printToConsole(cellDataList);
}
/**
* This method is used to print the cell data to the console.
* @param cellDataList - List of the data's in the spreadsheet.
*/
private void printToConsole(List cellDataList)
{
for (int i = 0; i < cellDataList.size(); i++)
{
List cellTempList = (List) cellDataList.get(i);
for (int j = 0; j < cellTempList.size(); j++)
{
HSSFCell hssfCell = (HSSFCell) cellTempList.get(j);
String stringCellValue = hssfCell.toString();
System.out.print(stringCellValue + "\t");
}
System.out.println();
}
}
public static void main(String[] args)
{
String fileName = "C:" + File.separator + "Users" +
File.separator + "Giftsam" + File.separator + "Desktop" +
File.separator + "sampleexcel.xls";
new ExcelSheetReader().readExcelFile(fileName);
}
}
Now let us execute the preceding java program and the results will look like the below in the console,
ID Name Qualification Designation Country
1 Gift Sam MCA Software Engineer India
2 Laxman MCA Software Engineer India
3 Shunmuga MCA SeniorSoftware Engineer India
Thatsall folks. I hope from this article clearly explains how to read data from an excel file using java code and for writing to an excel file, refer the article Java Code to write to an Excel file using POI. If you find this article is useful to you, Dont forget to give your valuable comments.
very useful
Thanks!
Well-loved. Like or Dislike:
92 
16
[Reply]
@gak
You are welcome!!
Well-loved. Like or Dislike:
30 
8
[Reply]
thanks
Well-loved. Like or Dislike:
11 
3
[Reply]
Very use full if u don’t mind please give the code for how to update the same result to Database.
Well-loved. Like or Dislike:
15 
4
[Reply]
Hidden due to low comment rating. Click here to see.
Poorly-rated. Like or Dislike:
5 
9
[Reply]
this code is very good tanks
Hot debate. What do you think?
6 
3
[Reply]
wat if there are multiple tabs in the excel sheet?
How can i jump to another tab depending on the value found in first tab?
Hot debate. What do you think?
8 
9
[Reply]
Thanks Sam. This really cut short my code dev time
Hot debate. What do you think?
5 
7
[Reply]
Its really helpful. Thanks for providing and explaining the use of Poi jar file to read excels.
Thanks
Hot debate. What do you think?
5 
3
[Reply]
Very use full if u don’t mind please give the code for how to update the same result to Database.
[Reply]
Thanks dear for this code.
Well-loved. Like or Dislike:
5 
1
[Reply]
awesome..thanks a lot..worked
[Reply]
Thanks dude….but this code works only with excel old formats…any alternatives for excel 2007?
Well-loved. Like or Dislike:
7 
2
[Reply]
Thanks…
[Reply]
Hi can u help me. My requirement is to print excel file on printer as and when we execute java class.
I searhed a lot but no use. No open source library to
print excel file.
So i thought to convert my excel to pdf first and then let i print my pdf.
But when am reading excel file like u gave in ur above code. how i can get the cells so that i can get it in pdf..
please help me
[Reply]
Good code .need treet
[Reply]
nice
[Reply]
very very helpful…
[Reply]
Hi,
This was very useful. What if i want to skip an excel column.Say if i want to skip the Qualifications column.
How will you do that?
Thanks,
Jayanth
Well-loved. Like or Dislike:
5 
0
[Reply]
Do u know how we can read the excel file from an url
[Reply]
Hi,
Getting eror
non-static variable this cannot be referenced from a static context
Getting error on the read file
Well thanks for the code its very useful help me out with the problem thanks again.
[Reply]
Nice code snippet. Thanks
[Reply]
do you have any sample for webapp like spring + poi.
the idea, a button on the jsp to download the excel template file which will call a controller. Controller needs to populate some data first before throwing the excel file to the user. problem is that the generated file should not be save in the server but thrown directly to the user.
[Reply]
If you need to read MS Excel files based on .xslx format then visit the link http://www.luv2code.com/?p=264
[Reply]
Thank u very much….
[Reply]
I want to retrive data from database in to excel sheet.
Actually the data is very large. So i want to store the data into different worksheet.
For example:
************
In my table i have 100 rows. So 50 rows in first work sheet and next 50 rows in second work sheet in same Excel Sheet.
Please replay.
Thanks & Regards
SatyamReddy K.
[Reply]
Hello….
My name is Kinjan Ajudiya.
I have implemented this and working fine . Now my requirement is some what complex.In that once i read file i took text from file now i will change some text in excel file and with out manually saving file i want updated text ..
Is that possible ?
If yes please let me know.
Thanks and Regards
Kinjan Ajudiya
[Reply]
hai,
ihave downloaded the apache poi jar from the link specified here,but dont know to include it in my java application.please help me to overcome package org.apache.poi.hssf.usermodel does not exists error.please give me instrustions to include the particular package and run the code.
[Reply]
Hi All,
It’s working fine (read xls file from java). I want to insert into database using struts2. in my case bean class variables are private Long id;
private String registrationNo;
private Date registrationDate;
private String name;
private String address;
private int age;
private String education;
private String occupation;
private String jointOrNuclearFamily;
private String typesOfProblems;
private int noOfSession;
private String assistance;
private String closedOrPending;
private String closedOutcome;
how can i store the data in database. pls help me if anybody know.
[Reply]
Your Program is grate! It is very useful to me. I use it to read xls files I save from Open office Calc. It works nicely. But now I use Libre Office Calc to save same xls files in same format. When I use same program, Output of that is not the cell content but like ‘org.apache.poi.hssf.usermodel.HSSFCell@13d422d’ What is my error?
[Reply]
Thanks All,
It is really helpful.
[Reply]
Very use full if u don’t mind please give the code for how to update the same result to Database.
[Reply]
can you show me how to do this in C++
thank you
[Reply]
Nice blog….. nice help… thanks a lot Giftsam
[Reply]
to display the data in horizontal alignment
[Reply]
Awesome! just like i needed..
[Reply]
Ranjith g Reply:
February 14th, 2013 at 6:43 pm
Hi,
have u tried inserting integer values
[Reply]
Thanks for Vry Gud explaination….
[Reply]
hi,
I wanna to print only first 5 fields from the table ,how it is possible
[Reply]
Hi All,
Where to install the POI files?.its not even in a .exe format, its simply a zip file.
where to extract those files? im getting error at the import line where im trying to import the POI files system and the HSSF row, cell etc.
anyone please help.
thanks in advance.
[Reply]
Pls help me out how to copy data from excel to db
[Reply]
Hi very nice and helpful articel
[Reply]
Hi
i have one query
sno sname phone
1 xxxxx xxxxx
2 xxxxx xxxxx
3 xxxxx xxxxx
i wish to search for a column like ( sname)
i want to get total data under sname column
How i can do this……..
Please help me
[Reply]
Thanks a lot for this…
[Reply]
working good.. Thanks
[Reply]
sir is there any code in java by which we can password protect the excel file that we do normally by giving the password in security settings….
[Reply]
Hi, i download the POI.jar from:
http://www.jarfinder.com/index.php/jars/versionInfo/1395
and just add it to my project.
After run it i have the following exception:
Invalid header signature; read 1688935826934608, expected -2226271756974174256………………..
Can somebody help me? thx
[Reply]
This code fails when I run it on server . In local it is working fine
[Reply]
Hi,
It is reading only String values, it is not loading int values
[Reply]
the code is very valuable,can u please help how to save excel data to database using hibernate.how to set object object properties from cell valus
Thanks
Rita Saluja
[Reply]
The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
IS THE ERROR WHICH I AM GETTING
CAN U PLEASE HELP ME OUT ?
THANKS.
[Reply]