Excel files provide a very convenient way to store data in a structured way and are widely used by both developers and automation testers to perform various operations on excel spreadsheets.
In this article, I’ll show you how to read and write data in excel files in Java using the most preferred and widely used library called Apache POI. It provides an API for manipulating Microsoft Excel documents like .xls and .xlsx.
If you are working with Selenium or LeanFT ( UFT Developer) you can utilize information written in this article to read input test data at runtime from Excel Sheet to parametrize input values in a test script and write required information back to an Excel Sheet.
Let’s get started!
Apache POI Dependencies
If you are not using either Maven or Gradle you can simply download the latest version of POI jars from http://poi.apache.org/download.html. Once you navigate to this page click on the link highlighted in the following image. Currently, the latest version of POI jars is 5.0.0. By the time you are reading this article, the latest version might have changed. Click on the link highlighted in the following image to download the jar files.
After downloading the zip file, extract the zip file and add all the jar files (including jar file inside lib and ooxml-lib folders) to the classpath of your project.
Maven: If you are using Maven in your Java project, you will have to add the following dependencies to your pom.xml file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency> |
Gradle: If you are using the Gradle project, you can add the following dependencies to your build.gradle file.
|
1 2 3 4 5 |
// https://mvnrepository.com/artifact/org.apache.poi/poi implementation group: 'org.apache.poi', name: 'poi', version: '5.0.0' // https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.0.0' |
We are using two different types of POI libraries. The first one is used to work with the old Microsoft’s binary file format for the excel having .xls extension.
The second one poi-ooxml is used to work with the newer XML-based file format having a .xlsx extension.
Sample Excel File to Read
We will read the following sample Excel file having different types of data that we come across frequently. Also, we will write code in such a way that will work with both types of Excel file formats – .xls and .xlsx.
Apache POI Interfaces
Apache POI excel library has the following four key Interfaces:
- Workbook: A workbook is the representation of a Spreadsheet. HSSFWorkbook and XSSFWorkbook classes implement the Workbook interface.
- Sheet: A workbook may contain one or more than one sheet. The above sample Excel File has two sheets – Customer and Statement. XSSFSheet and HSSFSheet classes implement the Sheet interface
- Row: It represents a row or a record in the spreadsheet. XSSFRow and HSSFRow classes implement the Row interface.
- Cell: A cell represents a cell in a row of the spreadsheet. XSSFCell and HSSFCell classes implement the Cell interface.
How to Read an Excel Sheet Using Java Apache POI in Selenium
The following program would be able to read an excel file for both types of file formats – .xls and .xlsx. Also, the method written in this program can read excel sheets having the following types of values in a cell or CellTypes. When using the Apache POI library we have to be very specific about what kind of cell value we want to read. The following code will evaluate the cell type at runtime and read the cell with the appropriate method.
- Text Value
- Numeric value
- Calander/Date value
- Empty Cell value
- Boolean (TRUE,False) value
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
package excelutility; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; 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; public class ReadExcelSheet { /* * This method reads data for all row from an Excel Sheet * @parameter: * @filePath: Complete path of the Excel File along with file name * @SheetName: Sheet Name to read * @return:Excel Sheet Cell values */ public void readEntireExcelSheet(String filePath,String sheetName) throws IOException { //Create an object of File class to open Excel file File file = new File(filePath); //Create an object of FileInputStream class to read an Excel file FileInputStream inputStream = new FileInputStream(file); Workbook workbook = null; //Find the file extension by splitting file name in substring and get only extension name String fileExtensionName = filePath.substring(filePath.indexOf(".")); //If the file is xlsx file if(fileExtensionName.equals(".xlsx")) { //For xlsx file create object of XSSFWorkbook class workbook = new XSSFWorkbook(inputStream); } //if the file is xls file else if(fileExtensionName.equals(".xls")) { //For xls file create object of HSSFWorkbook class workbook = new HSSFWorkbook(inputStream); } //Read sheet inside the workbook by its name Sheet worksheet = workbook.getSheet(sheetName); //Get total number of rows in the particular Excel Sheet int rowCount = worksheet.getLastRowNum(); String cellValue=null; //Creating an object of FormulaEvaluator class to get the cell type information //This is quite helpful to understand which cell contains what type of data like String,Integer,double etc FormulaEvaluator formulaEvaluator=workbook.getCreationHelper().createFormulaEvaluator(); //Create a loop to iterate through all the rows of the Excel sheet to read it for (int i = 0; i < rowCount+1; i++) { Row row = worksheet.getRow(i); //Create a loop to print all cell values in a row for (int j = 0; j < row.getLastCellNum(); j++) { //Printing Excel data for the current row in the console switch(formulaEvaluator.evaluateInCell(row.getCell(j)).getCellType()) { case NUMERIC: //If the cell has numeric values is number or Date //Check if the cell contains Date Value if (DateUtil.isCellDateFormatted(row.getCell(j))) { //formatting cell using DataFormatter class DataFormatter dataFormatter = new DataFormatter(); cellValue = dataFormatter.formatCellValue(row.getCell(j)); } else//If the cell contains integer or double value { Double doubleNumber =row.getCell(j).getNumericCellValue(); //Converting double value to String String doubleNumAsString = String.valueOf(doubleNumber); //Separating integer and decimal part if the numeric value contains decimal values int indexOfDecimal = doubleNumAsString.indexOf("."); String intPart = doubleNumAsString.substring(0, indexOfDecimal); String decimalPart=doubleNumAsString.substring(indexOfDecimal); double decimalValue = Double.parseDouble(decimalPart); if(decimalValue==0) cellValue = intPart; else cellValue=doubleNumAsString; } break; case STRING://If the cell has plain text value cellValue= row.getCell(j).getStringCellValue(); break; case BLANK: //If the cell has empty value cellValue=""; break; case _NONE: cellValue=""; break; case BOOLEAN://If the cell has boolean value cellValue= String.valueOf(row.getCell(j).getBooleanCellValue()); break; default: break; } System.out.print(cellValue+"\t\t"); } System.out.println(); } } //The main() method is calling readEntireExcelSheet method to read data from all rows of the given Excel Sheet public static void main(String args[]) throws IOException { //Create an object of ReadExcelFile class ReadExcelSheet objExcl = new ReadExcelSheet(); //Provide the complete path of the Excel file along with file name String filePath = "D:\\InputTestData.xlsx"; //Calling readEntireExcelSheet method of the class to read data objExcl.readEntireExcelSheet(filePath,"Customer"); } } |
The output of the Code:
How to Read a Particular Cell value from an Excel file?
In practical scenarios, quite often we need to retrieve the value of a particular cell from an Excel file. The following program shows how to do it. The program reads the value of the second row and first column from the sample Excel sheet mentioned earlier in the Sample Excel file section.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
package excelutility; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; 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; public class GetExcelCellValue { /* * This method to reads a particular cell value from an Excel Sheet * * @parameter: * * @filePath: Complete path of the Excel File along with file name * @SheetName: Sheet Name to read * @rowNo: Value for row number.The value for the first row starts from 0 * @colNo: Value for column number.The value for the first row starts from 0 * @return:Excel Sheet Cell Value * */ public String getExcelCellValue(String filePath,String sheetName,int rowNo,int colNo) throws IOException { //Create an object of File class to open Excel file File file = new File(filePath); //Create an object of FileInputStream class to read an Excel file FileInputStream inputStream = new FileInputStream(file); Workbook workbook = null; //Find the file extension by splitting file name in substring and get only extension name String fileExtensionName = filePath.substring(filePath.indexOf(".")); //If the file is xlsx file if(fileExtensionName.equals(".xlsx")) { //For xlsx file create object of XSSFWorkbook class workbook = new XSSFWorkbook(inputStream); } //if the file is xls file else if(fileExtensionName.equals(".xls")) { //For xls file create object of HSSFWorkbook class workbook = new HSSFWorkbook(inputStream); } //Read sheet inside the workbook by its name Sheet worksheet = workbook.getSheet(sheetName); //Get the required row in the particular Excel Sheet Row row = worksheet.getRow(rowNo); String cellValue=null; //Creating an object of FormulaEvaluator class to get the cell type information //This is quite helpful to understand which cell contains what type of data like String,Integer,double etc FormulaEvaluator formulaEvaluator=workbook.getCreationHelper().createFormulaEvaluator(); //Create a loop to iterate through all the rows of the Excel sheet to read it switch(formulaEvaluator.evaluateInCell(row.getCell(colNo)).getCellType()) { case NUMERIC: //If the cell has numeric values is number or Date //Check if the cell contains Date Value if (DateUtil.isCellDateFormatted(row.getCell(colNo))) { //formatting cell using DataFormatter class DataFormatter dataFormatter = new DataFormatter(); cellValue = dataFormatter.formatCellValue(row.getCell(colNo)); } else//If the cell contains integer or double value { Double doubleNumber =row.getCell(colNo).getNumericCellValue(); //Converting double value to String String doubleNumAsString = String.valueOf(doubleNumber); //Separating integer and decimal part if the numeric value contains decimal values int indexOfDecimal = doubleNumAsString.indexOf("."); String intPart = doubleNumAsString.substring(0, indexOfDecimal); String decimalPart=doubleNumAsString.substring(indexOfDecimal); double decimalValue = Double.parseDouble(decimalPart); if(decimalValue==0) cellValue = intPart; else cellValue=doubleNumAsString; } break; case STRING://If the cell has plain text value cellValue= row.getCell(colNo).getStringCellValue(); break; case BLANK: //If the cell has empty value cellValue=""; break; case _NONE: cellValue=""; break; case BOOLEAN://If the cell has boolean value cellValue= String.valueOf(row.getCell(colNo).getBooleanCellValue()); break; default: break; } return cellValue; } //The main() method is calling getExcelCellValue method to read a specific cell value from Excel Sheet public static void main(String args[]) throws IOException { //Create an object of GetExcelCellValue class GetExcelCellValue objExc = new GetExcelCellValue(); //Provide the complete path of the Excel file along with file name String filePath = "D:\\InputTestData.xlsx"; //Calling readEntireExcelSheet method of the class to read data String strCellValue= objExc.getExcelCellValue(filePath,"Customer",1,0); System.out.println("The Cell value for the specified row and column is: " + strCellValue); } } |
The output of the Program:
The Cell value for the specified row and column is: John
How to Write Excel File in Selenium Java?
In order to write data into an Excel file using Apache POI we have to follow the following steps:
- Create a workbook
- Add a sheet in the workbook to write the data values
- Create a row in a sheet for every new record
- Add new a cell in the sheet to store every individual value for a row
- Repeat steps 3 and 4 to add more records
Java Code Example to Write Excel File Using Apache POI
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
package excelutility; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashMap; import java.util.Map; import java.util.Set; 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 WriteExcelJava { //Method to create new Excel File and write data in it.If the file already exists.It will override the file public void writeDataInNewExcelFile(String filePath,String sheetName,Map < String,Object[] > map) { //Creating an object of blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Creating an object of blank sheet XSSFSheet sheet = workbook.createSheet(sheetName); //Creating an object of Set to get all key values of the map Set <String> keyset = map.keySet(); int rownum = 0;//Set the initial value of row number to 0 //Iterating through the map for (String key: keyset) { Row row = sheet.createRow(rownum++);//Creating a new row in the sheet Object[] objArray = map.get(key); int cellnum = 0; for (Object obj: objArray) { Cell cell = row.createCell(cellnum++);//Creating a new cell in the row of the current sheet if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Integer) { cell.setCellValue((Integer) obj); } } } try { //Write the workbook and save it in the file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook.write(out); out.close(); } catch(Exception e) { e.printStackTrace(); } } public static void main(String[] args) { //Creating a map object to store employee details.The value part of map is stored in a generic data type Object Array Map < String,Object[] > map = new HashMap < String,Object[] > (); map.put("101", new Object[] {"EmpID","Name","Department" }); map.put("102", new Object[] {1,"Ajay","CSE" }); map.put("103", new Object[] {2,"Arvind","IT" }); map.put("104", new Object[] {3,"Karan","HR" }); map.put("105", new Object[] {4,"Manoj","Admin" }); WriteExcelJava objExcl=new WriteExcelJava(); objExcl.writeDataInNewExcelFile("D:\\EmployeeDetails.xlsx", "Employee", map); System.out.println("Excel file was written successfully."); } } |
The Output of the code:
Java Code to Update an Excel Cell Value Using Apache POI?
The following java program shows how to update a value of a specific cell in java using Apache POI.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; 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 UpdateCellValueOfExcel { /* * Method to update cell a value in Excel Sheet * @parameter: * @filePath: Path of the Excel File along with file name * @SheetName: Sheet Name to update * @rowNo: Row number of the cell * @colNo: Column number of the cell * @return: * */ public void updateCellValue(String filePath,String sheetName,String newCellValue,int rowNo,int colNo) { try { FileInputStream file = new FileInputStream(filePath); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheet(sheetName); Cell cell = null; //Retrieve the row and check for null XSSFRow sheetrow = sheet.getRow(rowNo); //Check whether the given row number already exists.Create a new row if no row found against given row number if(sheetrow == null) { sheetrow = sheet.createRow(rowNo); } //Update the value of cell cell = sheetrow.getCell(colNo); //Check whether the cell already exists.Create a new cell if the particular does not exist at given row and column number if(cell == null) { cell = sheetrow.createCell(colNo); } cell.setCellValue(newCellValue); file.close(); FileOutputStream outFile =new FileOutputStream(new File(filePath)); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) { UpdateCellValueOfExcel objExcl=new UpdateCellValueOfExcel(); objExcl.updateCellValue("D:\\EmployeeDetails.xlsx", "Employee", "Arvind K.",2,1); System.out.println("Excel file was updated successfully."); } } |
The output of the code:
Conclusion
In this article, we learned how to read write and update excel file formats – .xls and .xlsx in java using the Apache POI library.Hope you will find this article helpful on Excel spreadsheet.Please don’t forget to share it and if you have any questions/queries, please do mention that in the comment box.
Recommended Posts
- How to Use CSS Selector in Selenium WebDriver | 13 Examples
- 23 Most Useful Selenium WebDriver Commands That You Must know
- Handle iFrame in Selenium WebDriver
- Page Object Model (POM) & Page Factory in Selenium WebDriver
- XPath in Selenium WebDriver with Example
- How to Use Regular Expression in LeanFT | UFT Developer
- How to Get Table Cell Value in LeanFT | UFT Developer
- Overview of Micro Focus UFT Developer | LeanFT
- Intuitive Way of MySQL Database Testing in Selenium | LeanFT
