How to Read Excel File Apache POI in Java Selenium Example Program?

Read Excel File Cell By Cell

package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile 
{
	public static void main(String[] args) throws IOException 
	{
		File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
		FileInputStream fi=new FileInputStream(file);
		XSSFWorkbook wb=new XSSFWorkbook(fi);
		XSSFSheet sheet=wb.getSheet("login");
		
		//Number Of Rows
		XSSFRow row=sheet.getRow(1);
		Cell cell=row.getCell(1); 
		System.out.println(sheet.getRow(0).getCell(0)+" "+sheet.getRow(0).getCell(1));
		System.out.println(sheet.getRow(1).getCell(0)+" "+sheet.getRow(1).getCell(1));
		System.out.println(sheet.getRow(2).getCell(0)+" "+sheet.getRow(2).getCell(1));
	}
}

Read All Cell Values

package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadAllCellValue 
{
	public static void main(String[] args) throws IOException 
	{
		File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
		FileInputStream fi=new FileInputStream(file);
		@SuppressWarnings("resource")
		XSSFWorkbook wb=new XSSFWorkbook(fi);
		XSSFSheet sheet=wb.getSheet("login");
		
		//Top Read The Row Values
		for(int i=sheet.getFirstRowNum();i<=sheet.getLastRowNum();i++)
		{
			//To Read The Cell Values Of Each Row
			for(int j=sheet.getRow(i).getFirstCellNum();j<sheet.getRow(i).getLastCellNum();j++)
			{
				System.out.print(sheet.getRow(i).getCell(j)+"\t");
			}
			System.out.println();
		}
		
	}
}

Read Excel File without using XSSFWorkbook & XSSFSheet

package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile 
{
	public static void main(String[] args) throws IOException 
	{
		File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\ReadExcelFile.xlsx");
		FileInputStream fi=new FileInputStream(file);
		XSSFWorkbook wb=new XSSFWorkbook(fi);
		XSSFSheet sheet=wb.getSheet("login");
		
		//Number Of Rows
		XSSFRow row=sheet.getRow(1);
		Cell cell=row.getCell(1); 
		//System.out.println(sheet.getRow(0).getCell(0)+" "+sheet.getRow(0).getCell(1));
		System.out.println(sheet.getRow(1).getCell(0)+" "+sheet.getRow(1).getCell(1));
		System.out.println(sheet.getRow(2).getCell(0)+" "+sheet.getRow(2).getCell(1));
	}
}

Write In Excel File

package com.softwaretestingo.file;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelFile 
{
	public static void main(String[] args) throws IOException 
	{
		File file=new File("D:\\Workspace\\Automation\\SeleniumPractice\\Resources\\WriteExcelFile.xlsx");
		FileInputStream fis=new FileInputStream(file);
		@SuppressWarnings("resource")
		XSSFWorkbook wb=new XSSFWorkbook(fis);
		XSSFSheet sheet=wb.getSheet("TestData");
		
		int lastRowNo=sheet.getLastRowNum();
		System.out.println(lastRowNo);
			
		//Go to The Last Cell Number
		for(int i=0;i<=lastRowNo;i++)
		{
			//Create a New Row & Write the Valuev
			sheet.createRow(lastRowNo+1).createCell(0).setCellValue("TestData");
		}
		fis.close();
		FileOutputStream fos=new FileOutputStream(file);
		wb.write(fos);
		fos.close();
	}
}

Avatar for Softwaretestingo Editorial Board

I love open-source technologies and am very passionate about software development. I like to share my knowledge with others, especially on technology that's why I have given all the examples as simple as possible to understand for beginners. All the code posted on my blog is developed, compiled, and tested in my development environment. If you find any mistakes or bugs, Please drop an email to softwaretestingo.com@gmail.com, or You can join me on Linkedin.

Leave a Comment