MrBool
You must be logged in to give feedback. Click here to login
[Close]

You must be logged to download.

Click here to login

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Reading Excel file with Java

In this article you will learn how create a program to read an excel file and show the excel data on a frame.

[close]

You didn't like the quality of this content?

Would you like to comment what you didn't like?

Suppose you have an excel sheet on which you want to perform some processing. Now it would be much easy if you drag the data from excel and apply the logic on the data obtained to obtain results.

Program Explanation:

First we will include all the import classes.

Note: You need to download the apache poi library for availing the excel functionality. The site for the same is poi.apache.org

Listing 1: Importing classes

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

BufferedInputStream is used for obtaining the buffered input, IOException is for reporting of input output exceptions, Iterator is used to iterate over the values of the result obtained from the sheet, POIFSFileSystem is the main class of the POIFS system; it manages the entire life cycle of the filesystem.

HSSFCell is representation of a cell in a row of a spreadsheet, HSSFSheet is representation of a worksheet, HSSFWorkbook is representation of a workbook and HSSFRow is representation of a row of a spreadsheet.

Now we will define the class along with main method:

Listing 2: Class name and and main method

    public static void main( String [] args ) {
        try {
   
        	 InputStream input = new BufferedInputStream(
        		        new FileInputStream("sample.xls"));
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);

In the code above we defined the main method and make an object of InputStream which points to the excel file which we want to read.

We define an object of POIFSFileSystem which takes the input as the InputStream object.Now we define a new workbook. This workbook will contain the sheets from the excel file.

Now we make the HSSFSheet object called sheet and pass the first sheet from the excel file and we will iterate through each row and column of the sheet and then display the result.

Listing 3: Itearting and displaying the results from sheet

Iterator rows = sheet.rowIterator(); 
            while( rows.hasNext() ) {   
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println("\n");
                Iterator cells = row.cellIterator();
                while( cells.hasNext() ) {
                	
                    HSSFCell cell = (HSSFCell) cells.next();
                    if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType())
                    System.out.print( cell.getNumericCellValue()+"     " );
                    else
                    if(HSSFCell.CELL_TYPE_STRING==cell.getCellType())
                        System.out.print( cell.getStringCellValue()+"     " );
                    else
                    	if(HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType())
                    	System.out.print( cell.getBooleanCellValue()+"     " );
                    	else
                    		if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType())
                            	System.out.print( "BLANK     " );
                            	else
                    		System.out.print("Unknown cell type");
                  
                }
                
                
            }
           
            
        } catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
    
}

We iterate through each row of the sheet and store that information in variable called rows.

Now we define a while function to read all the rows obtained and use the rows.next() function to obtain the next row and iterate through each column of the row and obtain the column using the next method.

Now we obtain the type of data in that particular column like HSSFCell.CELL_TYPE_NUMERIC is for the numeric data in that particular column. HSSFCell.CELL_TYPE_STRING is for the string data in that particular column.

HSSFCell.CELL_TYPE_Boolean is for the boolean value in that particular column. HSSFCell.CELL_TYPE_BLANK is for the blank data in that particular column.

We put the whole code inside a try catch block to handle the exceptions.

Output :

Reading Excel File

Full Source code:

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class ReadExcel {

    public static void main( String [] args ) {
        try {
   
        	 InputStream input = new BufferedInputStream(
        		        new FileInputStream("sample.xls"));
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            
            
            Iterator rows = sheet.rowIterator(); 
            while( rows.hasNext() ) {   
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println("\n");
                Iterator cells = row.cellIterator();
                while( cells.hasNext() ) {
                	
                    HSSFCell cell = (HSSFCell) cells.next();
                    if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType())
                    System.out.print( cell.getNumericCellValue()+"     " );
                    else
                    if(HSSFCell.CELL_TYPE_STRING==cell.getCellType())
                        System.out.print( cell.getStringCellValue()+"     " );
                    else
                    	if(HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType())
                    	System.out.print( cell.getBooleanCellValue()+"     " );
                    	else
                    		if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType())
                            	System.out.print( "BLANK     " );
                            	else
                    		System.out.print("Unknown cell type");
                  
                }
                
                
            }
           
            
        } catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
    
}

Hope you like the article. See you next time.



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?

Did you like the post?

Help us to keep publishing good contents like this.

SUPPORT US

funded

remaining

[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually � in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits � in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login