File Handling | Reading data from Excel files (.xls or .xlsx) using JAVA.

Reading data from Excel files (.xls or .xlsx) using JAVA. Reading excel file is simple and easy if follow it step by step.

To read and write data into an excel file, we need the third party library files. We can download it from the below link:

https://poi.apache.org/download.html

Just unzip and add to your project using Build Path configuration.

Reading data from Excel File

While coding we have to follow the below steps for reading data from an excel file:

  1. Locate the physical location of the file using File object.  (File file=new File(“input\\testdata.xlsx”);)
  2. Load the file into the memory stream using FileInputStream object. (FileInputStream fis=new FileInputStream(file);)
  3. Now create an object of XSSFWorkbook bypassing input stream object. (XSSFWorkbook wb=new XSSFWorkbook(fis);)
  4. Next step to create an object of XSSFSheet in the above workbook. (XSSFSheet sheet=wb.getSheet(“inputs”);)
  5. Now the data can be read in row/column format. For this first, we have to create an object of the Iterator class and add all rows from the excel sheet to this Iterator object. (Iterator<Row> rows=sheet.rowIterator();)
  6. To iterate through all the rows in a sheet we have to use while loop. There are two key methods which help to iterate each row:
    1. rows.hasNext() = To  check if the iterator object has more rows.
    2. rows.next() = To jump to next row in the iterator.
  7. Similarly, we have to create an Iterator object for cells in each row. We have to call cellIterator() for the same. (Iterator<Cell> cells=rows.next().cellIterator();)
    1. Functionality of hasNext() and next() would remain same.
  8. To read the cell value use next(). (cells.next().toString();)

Additional information

We have separate methods to read data from and “xlsx” for “xls” files. So, while reading/writing data we have to take care of the file extension we are using.

  1. For xlsx files we have to use XSSFWorkbook and XSSFSheet
  2. For xls files we have to use HSSFWorkbook and HSSFSheet

Getting file extension

To extract the file extension we have to use FilenameUtils Class from common library files. Follow the below steps for the same:

  1. Download commons-io library files from link https://commons.apache.org/proper/commons-io/download_io.cgi
  2. Add library files to the project using a build path feature.
  3. Use getExtension method (FilenameUtils.getExtension(filename);)

Code Example

package excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHandling 
{

	public static void main(String[] args) throws IOException 
	{
		// TODO Auto-generated method stub
		String filePath="input_Excel//testData.xlsx";
		loadFile(filePath);
	}
	
	public static void loadFile(String filePath) throws IOException
	{
		File file=new File(filePath);                               // Creating File Object
		String fileExtension=FilenameUtils.getExtension(filePath);  // Getting extension of file
		int totalRows=0;
		Iterator<Row> rowIterator=null;
		
		if(fileExtension.equalsIgnoreCase("xlsx"))
		{
			FileInputStream fis=new FileInputStream(file); 
			XSSFWorkbook doc =new XSSFWorkbook(fis); 
			XSSFSheet sheet=doc.getSheet("Sheet1");
		    totalRows=sheet.getLastRowNum();
		    rowIterator=sheet.rowIterator();
		}
		else if(fileExtension.equalsIgnoreCase("xls"))
		{
			FileInputStream fis =new FileInputStream(file);
			HSSFWorkbook doc=new HSSFWorkbook(fis);
			HSSFSheet sheet=doc.getSheet("Sheet1");
			totalRows=sheet.getLastRowNum();
			rowIterator=sheet.rowIterator();
		}
		 
			System.out.println("Total Row Count : "+totalRows);
			System.out.println("\nContent from document =>");
			while(rowIterator.hasNext())
			{
				Iterator<Cell> cellIterator=rowIterator.next().cellIterator();
				while(cellIterator.hasNext())
				{
					String cellData=cellIterator.next().toString();
					System.out.print(cellData+"\t");
				}
				System.out.println();
			}
	}
	

}


Also, read How to read word document file using JAVA?

1 thought on “File Handling | Reading data from Excel files (.xls or .xlsx) using JAVA.”

  1. Thank you a lot for sharing this with all of us you really know what you’re speaking approximately! Bookmarked. Kindly also discuss with my web site =). We can have a hyperlink alternate contract among us!

Leave a Comment

Your email address will not be published. Required fields are marked *

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.