|
Answer» To read and write an excel FILE, the Apache POI library is capable to read and write both xls and xlsx file format of excel. Below are the steps to write to excel sheet - - Create an xlsx file and save it at the desired location( d:/test/testdata.xlsx). Add some DATA to read by selenium and rename sheet (ex. mysheet).
- By default excel cell format is general, make it text.
- Close that excel file before the execution of the script.
- You NEED to download POI jars and unzip it. POI jars allow to create, modify, and display MS Office files using Java programs. Go to the official website of Apache POI download section http://poi.apache.org/download.html
- In your editor Select Project
- Right-CLICK on the project
- Go to build path
- Go to 'Configure build path;
- Click on the 'lib' tab and,
- add POI external jar files.
- Below is the code to read excel file
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
public class ReadExcel {
public static void main(String []args){
try {
// To specify the path of file
File src=new File("filepath/excelsheetname.xlsx");
// To load file
FileInputStream fis = new FileInputStream(src);
// To load workbook
XSSFWorkbook wb=new XSSFWorkbook(fis);
// To load sheet- Here loading the first sheet
XSSFSheet sh1= wb.getSheetAt(0);
// getRow() to specify which row we want to read.
// and getCell() to specify which column to read.
// getStringCellValue() to specify that we are reading String data.
System.out.println(sh1.getRow(0).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(0).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(1).getStringCellValue());
} catch (Exception E) {
System.out.println(e.getMessage());
}
}
}
|
