![Cover image for 🔥 How to Read Excel File in Selenium with Apache POI(with Latest Version)[Download Code] 🔥](https://media.dev.to/cdn-cgi/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fka0rfr9y559eiefmmfmd.png)
✅ Join us - https://sendfox.com/thetestingacademy
In this video, We are going to learn How to Read Excel File in Selenium with Apache POI
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML).
To read or write an Excel, Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.
🔥 To read XLS files, an HSSF implementation is provided by POI library. 🔥
To read XLSX, XSSF implementation of POI library will be the choice. Let's study these implementations in detail.
✅ Download Code - https://scrolltest.com/automation/day24
✅http://poi.apache.org
✅https://mvnrepository.com/
package com.scrolltest;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
public class ExcelDemo {
public static void main(String[] args) {
WebDriver driver = new FirefoxDriver( );
readFile( );
String[] recordToAdd = {"Aman","LeadSDET"};
writeExcel(recordToAdd);
driver.quit();
}
private static void writeExcel(String[] recordToAdd) {
File file = new File("src/main/java/com/scrolltest/TestDATA.xlsx");
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
Workbook testDataWorkBook1 = new XSSFWorkbook(inputStream);
Sheet testDataSheet1 = testDataWorkBook1.getSheet("Sheet1");
int rowCount1 = testDataSheet1.getLastRowNum()-testDataSheet1.getFirstRowNum();
Row row = testDataSheet1.getRow(0);
Row newRow = testDataSheet1.createRow(rowCount1+1);
for(int j = 0; j < row.getLastCellNum(); j++){
//Fill data in row
Cell cell = newRow.createCell(j);
cell.setCellValue(recordToAdd[j]);
}
inputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace( );
} catch (IOException e) {
e.printStackTrace( );
}
}
private static void readFile() {
try {
File file = new File("src/main/java/com/scrolltest/TestDATA.xlsx");
FileInputStream inputStream = new FileInputStream(file);
Workbook testDataWorkBook = new XSSFWorkbook(inputStream);
Sheet testDataSheet = testDataWorkBook.getSheet("Sheet1");
int rowCount = testDataSheet.getLastRowNum( ) - testDataSheet.getFirstRowNum( );
for (int i = 0; i < rowCount + 1; i++) {
Row row = testDataSheet.getRow(i);
//Create a loop to print cell values in a row
for (int j = 0; j < row.getLastCellNum( ); j++) {
//Print Excel data in console
System.out.print(row.getCell(j).getStringCellValue( ) + "|| ");
}
System.out.println( );
}
inputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace( );
} catch (IOException e) {
e.printStackTrace( );
}
}
}
--
Be sure to subscribe for more videos like this!
Top comments (0)