*Poi For Xlsx Read In Selenium For Mac Osx
*Poi For Xlsx Read In Selenium For Mac Osx
*Poi For Xlsx Read In Selenium For Mac Catalina
*Apache POI library v3.8 or above (download)
Handling excel files Using Apache POI in Selenium WebDriver. As we all know Selenium supports only Web browser automation. We need to get the help of third party API like Apache POI to handle (read and write) excel files using Selenium WebDriver. This video will talk about How to read excel file in Selenium using Apache POI. In Automation, we have to take data from an external source like excel file.
To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements
import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;
// Will get the workbook instance for XLS and takes excel file to read
XSSFWorkbook workbook = new XSSFWorkbook(XlsxFileToRead);
// We will pass the name / index of the sheet which starts from ‘0’.
XSSFSheet sheet = workbook.getSheet(“Sheet1”);orXSSFSheet sheet=wb.getSheetAt(0);//We will iterate all the rows in the sheet
Iterator rows = sheet.rowIterator();//We will be iterating all the cells of the current row
Iterator cells = row.cellIterator();
package com.read;
import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadXlsx {
public void readXLSXFile(String fileName) {InputStream XlsxFileToRead = null;XSSFWorkbook workbook = null;try {XlsxFileToRead = new FileInputStream(fileName);
//Getting the workbook instance for xlsx fileworkbook = new XSSFWorkbook(XlsxFileToRead);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}
//getting the first sheet from the workbook using sheet name.// We can also pass the index of the sheet which starts from ‘0’.XSSFSheet sheet = workbook.getSheet(“Sheet1″);XSSFRow row;XSSFCell cell;
//Iterating all the rows in the sheetIterator rows = sheet.rowIterator();
while (rows.hasNext()) {row = (XSSFRow) rows.next();
//Iterating all the cells of the current rowIterator cells = row.cellIterator();
while (cells.hasNext()) {cell = (XSSFCell) cells.next();
if (cell.getCellType() XSSFCell.CELL_TYPE_STRING) {System.out.print(cell.getStringCellValue() + ” “);} else if (cell.getCellType() XSSFCell.CELL_TYPE_NUMERIC) {System.out.print(cell.getNumericCellValue() + ” “);} else if (cell.getCellType() XSSFCell.CELL_TYPE_BOOLEAN) {System.out.print(cell.getBooleanCellValue() + ” “);
} else { // //Here if require, we can also add below methods to// read the cell content// XSSFCell.CELL_TYPE_BLANK// XSSFCell.CELL_TYPE_FORMULA// XSSFCell.CELL_TYPE_ERROR}}System.out.println();try {XlsxFileToRead.close();} catch (IOException e) {e.printStackTrace();}}}
public static void main(String[] args) {ReadXlsx readXlsx = new ReadXlsx();readXlsx.readXLSXFile(“C:/testXlsxRead.xlsx”);}
}
Make sure to add all the required jars to the class path. Below are the exception you generally observe if we don’t add jars.
You may see below exception if you don’t add ‘xmlbeans-2.x.x’Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
And you may also come across the below exception if you don’t add jar ‘poi-ooxml-schemas-3.x-xxx’Exception in thread “main” java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheetPoi For Xlsx Read In Selenium For Mac Osx
The below is the other exception that you get, if we don’t add jar ‘dom4j-1.x.x ‘Exception in thread “main” java.lang.NoClassDefFoundError: org/dom4j/DocumentExceptionPoi For Xlsx Read In Selenium For Mac Osx
After adding all the jars the program will get executed successfully.
Output of the above program should look like below:Poi For Xlsx Read In Selenium For Mac Catalina
Username Passwordtest1 1234.0test2 test123test3 test456test4 test789test5 test012
