Excel Interaction Using ApachePOI

Introduction: 

ApachePOI is an API built by Apache foundation and this API is used to perform read/write operations in Microsoft files (e.g.: MS-Excel, Word, and Power-Point). In this article, we are going to see how to interact with Microsoft Excel file and how to use that as a data source for the Selenium-JAVA POM framework.  

Installation: 

1.Visit https://poi.apache.org/ 

2.Click the ‘Download’ link in the left navigation menu and click on the ‘The latest stable release…’ link available under the ‘Available Downloads’ section  

3.Click the poi-bin zip file link   

4.Click the below mirror site link and download the Zip. Then, extract the zip and store in your local file system  

5.Import the downloaded jar file into your project using below steps: 

• I.Right click the project 
• II.Click on ‘Build  Path’ > ‘Configure Build Path’  

• III.Click on ‘Libraries’ > ‘Add External Jars’  

• IV.Select the below Jar files stored in your local file system and click on ‘Apply and Close’. 

If you are using a Maven project, we can add the dependency as below: 

1. <!– https://mvnrepository.com/artifact/org.apache.poi/poi –> 
2. <dependency> 
3.     <groupId>org.apache.poi</groupId> 
4.     <artifactId>poi</artifactId> 
5.     <version>3.9</version> 
6. </dependency> 
8.  <!– https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml –> 
9. <dependency> 
10.     <groupId>org.apache.poi</groupId> 
11.     <artifactId>poi–ooxml</artifactId> 
12.     <version>3.9</version> 
13. </dependency> 
14.  
15. <!– https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas –> 
16. <dependency> 
17.     <groupId>org.apache.poi</groupId> 
18.     <artifactId>poi–ooxml-schemas</artifactId> 
19.     <version>3.9</version> 
20. </dependency> 

Majorly used Excel Utility classes available in the poi-jar: 

XSSFWorkbook: 

It is abbreviated as an XMLSpreadSheet format. It is used to create the .xlsx type excel files. It is a pure Java implementation from Excel: 2007 format. 

With the help of XSSFWorkbook, you can create the sheet, row, cell in XSSF format. 

XSSFSheet: 

It is used to perform the Sheet operations in XSSFWorkbook. 

XSSFRow: 

 It is used to perform the Row operations in XSSFWorkbook. 

XSSFCell: 

It used to perform the Cell operations in XSSFWorkbook.  

HSSFWorkbook: 

It is abbreviated as HorribleSpreadSheet Format. It is used to create the .xls type excel files. It is a pure Java implementation of Excel: 97-2007 format. 

With the help of HSSFWorkbook, you can create the sheet, row, cell in HSSF format. 

HSSFSheet: 

It is used to perform the Sheet operations in HSSFWorkbook. 

HSSFRow: 

 It is used to perform the Row operations in HSSFWorkbook. 

HSSFCell: 

It used to perform the Cell operations in HSSFWorkbook.   

File Handling: 

In Java, to perform the read and write operations in documents, the file handling is necessary.  

FileInputStream and FileOutputStream are the two things to perform the read and write operations in Java. 

FileOutputStream: 

It is used to write the data in the file. Based on the type of workbook, the user can create the file using this FileOutputStream. 

Syntax: 

FileOutputStream out = new FileOutputStream(“file”); 

FileInputStream: 

It is used to read the data in the file. Based on the type of workbook, the user can read the file or open the file using this FileInputStream. 

FileInputStream in = new FileInputStream( “file”); 

Write Operation in excel: 

The code snippet performs the below actions: 

1. Opens a excel file mentioned in the ‘filePath’ variable in write mode. 
2. Create a new sheet as ‘Testsheet’ 
3. Create a new row and a cell and set the cell value as ‘MST QA Automation’ 
4. Closes the output stream. 

1. public?static?void?createWorkbook()?throws?IOException{?? 
2. ????String?filePath?=?“C:/Testxssf.xlsx”;?? 
3. ????File?file?=?new?File(filePath);?? 
4. ????FileOutputStream?out?=?new?FileOutputStream(file);?? ?????? 
5. ????if(file.isFile()?&&?file.exists())?{?? ?????????? 
6. ????????XSSFWorkbook?xbook?=?new?XSSFWorkbook();?? ?????????? 
7. ????????XSSFSheet?xsheet?=?xbook.createSheet(“Testsheet”);?? ?????????? 
8. ????????XSSFRow?xrow?=?xsheet.createRow(0);?? ?????????? 
9. ????????XSSFCell?xcell?=?xrow.createCell(0);?? ?????????? 
10. ????????xcell.setCellValue(“MST?QA?Automation”);?? ?????????? 
11. ????????xbook.write(out);?? 
12. ????}?? 
13. ????else?{?? 
14. ????????System.out.println(“Valid?File?does?not?exist”);?? 
15. ????}?? 
16. ????out.close();?? 
17. }?? 

Output:  

Read operation in excel: 

This code snippet performs the below actions: 

1.Opens a excel file mentioned in the filePathxlsx variable in read mode. 

2.Read the sheet with name as ‘Testsheet’ 

3.Read the first two row’s 4 cell value (as below table) and print it. 

 4.Closes the input stream. 

1. public?static?void?readData()?throws?IOException{?? 
2. ????????String?filePath?=?“C:/Testxssf.xlsx”;?? 
3. ????????File?file?=?new?File(filePath);?? 
4. ????????FileInputStream?in?=?new?FileInputStream(file);???????????? 
5. ????????if(file.isFile()?&&?file.exists())?{?? ?????????????? 
6. ????????????XSSFWorkbook?xbook?=?new?XSSFWorkbook(in);?? ?????????????? 
7. ????????????XSSFSheet?xsheet?=?xbook.getSheet(“Testsheet”);?? ?????????????? 
8. ????????????for(int?row=0;?row<2;row++)?{?? 
9. ????????????????XSSFRow?xrow?=?xsheet.getRow(row);?? ?????????????????? 
10. ????????????????for(int?col=0;?col<2;col++)?{?? 
11. ????????????????????XSSFCell?xcell?=?xrow.getCell(col);?? 
12. ????????????????????String?output?=?xcell.getStringCellValue();?? 
13. ????????????????????System.out.println(output);?? 
14. ????????????????}?? 
15. ????????????}????????????? 
16. ????????}?? 
17. ????????else?{?? 
18. ????????????System.out.println(“Valid?File?does?not?exist”);?? 
19. ????????}?? 
20. ????????in.close();?? 
21. ????}?? 

Since the cell can contain different types of datd, we need to write the code to handle all types of data as below: 

1. cell = sheet.getRow(k).getCell(l); 
2. switch (cell.getCellType()) { 
3. case?Cell.CELL_TYPE_NUMERIC://?numeric?value?in?excel?? 
4. ????if?(DateUtil.isCellDateFormatted(cell))?{?? 
5. ?????Date?myDate?=?cell.getDateCellValue();?? 
6. ?????SimpleDateFormat?formatter?=?new?SimpleDateFormat(“MM/dd/yyyy”,?Locale.US);?? 
7. ?????result?=?formatter.format(myDate);?? 
8. ????}?? 
9. ????else?{?? 
10. ?????result?=?new?BigDecimal(cell.getNumericCellValue()).toPlainString();?? 
11. ????}?? 
12. ????break;?? ?? 
13. case?Cell.CELL_TYPE_STRING:?//?string?value?in?excel?? 
14. ????result?=?cell.getStringCellValue();?? 
15. ????break;?? ?? 
16. case?Cell.CELL_TYPE_BOOLEAN:?//?boolean?value?in?excel?? 
17. ????result?=?cell.getBooleanCellValue();?? 
18. ????break;?? ?? 
19. case?Cell.CELL_TYPE_BLANK:?//?blank?value?in?excel?? 
20. ????result?=?cell.getStringCellValue();?? 
21. ????break;?? ?? 
22. case?Cell.CELL_TYPE_ERROR:?//?Error?value?in?excel?? 
23. ????result?=?cell.getErrorCellValue()?+?“”;?? 
24. ????break;?? ?? 
25. default:?? 
26. ????throw?new?Exception(“The?cell?data?type?is?invalid”);?? 
27. } 

Sample code to do the data driven approach: 

1. public?class?ExcelUtility?{?? ?? 
2. ????//declaration?of?the?needed?objects?? 
3. ????private?static?File?file;?? 
4. ????private?static?FileInputStream?inputStream;?? 
5. ????protected?static?Workbook?workbook;?? 
6. ????protected?static?CellStyle?style;?? 
7. ????protected?static?Font?font;?? 
8. ????protected?static?Sheet?sheet;?? 
9. ????protected?static?Row?row;?? 
10. ????protected?static?Cell?cell;?? 
11. ????protected?static?int?totalRows?=?0;?? 
12. ????protected?static?int?totalCols?=?0;?? ?????????? 
13. ????public?static?void?openStream()?throws?IOException?{?? 
14. ????????//initiate?the?file?with?the?file?stored?in?the?given?path?? 
15. ????????file?=?new?File(Constant.datafilePath);?? 
16. ????????//open?the?file?in?read?mode?? 
17. ????????inputStream?=?new?FileInputStream(file);?? 
18. ????????String?fileExtensionName?=?Constant.datafilePath.substring(Constant.datafilePath.indexOf(“.”));?? 
19. ????????//To?initiate?the?proper?Workbook?type?based?on?the?identified?substring?? 
20. ????????if?(fileExtensionName.equals(“.xlsx”))?{?? 
21. ????????????workbook?=?new?XSSFWorkbook(inputStream);?? 
22. ????????}?else?if?(fileExtensionName.equals(“.xls”))?{?? 
23. ????????????workbook?=?new?HSSFWorkbook(inputStream);?? 
24. ????????}?? 
25. ????}?? ?? 
26. ????//To?read?the?cell?by?using?it’s?relevant?sheet?name,?test?case?number,and?column?header?? 
27. ????public?static?String?readData(String?sheetName,?String?tcID,?String?header)?{?? 
28. ????????Object?result?=?new?Object();?? 
29. ????????try?{?? 
30. ????????????sheet?=?workbook.getSheet(sheetName);?? 
31. ????????????totalRows?=?sheet.getLastRowNum();?? 
32. ????????????row?=?sheet.getRow(0);?? 
33. ????????????totalCols?=?row.getLastCellNum();?? ?? 
34. ????????????//Iterating?to?identify?the?respective?row?? 
35. ????????????for?(int?k?=?1;?k?<=?totalRows;?k++)?{?? 
36. ????????????????String?testCaseID?=?sheet.getRow(k).getCell(0).getStringCellValue();?? ?? 
37. ????????????????if?(testCaseID.equalsIgnoreCase(tcID))?{?? 
38. ????????????????????//Iterating?to?identify?the?respective?column?? 
39. ?????????????????for?(int?l?=?1;?l?<?totalCols;?l++)?{?? ?? 
40. ????????????????????String?testData_FieldName?=?sheet.getRow(0).getCell(l).getStringCellValue();?? ?? 
41. ?????????????????????if?(testData_FieldName.equalsIgnoreCase(header))?{?? 
42. ????????????????????????//To?identify?the?cell?using?the?identified?row,?column?number?? 
43. ????????????????????????cell?=?sheet.getRow(k).getCell(l);?? 
44. ????????????????????????if?(cell?!=?null)?{?? 
45. ????????????????????????????switch?(cell.getCellType())?{?? 
46. ????????????????????????????case?Cell.CELL_TYPE_NUMERIC://?numeric?value?in?excel?? 
47. ????????????????????????????????if?(DateUtil.isCellDateFormatted(cell))?{?? 
48. ?????????????????????????????????Date?myDate?=?cell.getDateCellValue();?? 
49. ?????????????????????????????????SimpleDateFormat?formatter?=?new?SimpleDateFormat(“MM/dd/yyyy”, 
50.                                                                    ?Locale.US);?? 
51. ?????????????????????????????????result?=?formatter.format(myDate);?? 
52. ????????????????????????????????}?? 
53. ????????????????????????????????else?{?? 
54. ?????????????????????????????????result?=?new?BigDecimal(cell.getNumericCellValue()).toPlainString();?? 
55. ????????????????????????????????}?? 
56. ????????????????????????????????break;?? ?? 
57. ????????????????????????????case?Cell.CELL_TYPE_STRING:?//?string?value?in?excel?? 
58. ????????????????????????????????result?=?cell.getStringCellValue();?? 
59. ????????????????????????????????break;?? ?? 
60. ????????????????????????????case?Cell.CELL_TYPE_BOOLEAN:?//?boolean?value?in?excel?? 
61. ????????????????????????????????result?=?cell.getBooleanCellValue();?? 
62. ????????????????????????????????break;?? ?? 
63. ????????????????????????????case?Cell.CELL_TYPE_BLANK:?//?blank?value?in?excel?? 
64. ????????????????????????????????result?=?cell.getStringCellValue();?? 
65. ????????????????????????????????break;?? ?? 
66. ????????????????????????????case?Cell.CELL_TYPE_ERROR:?//?Error?value?in?excel?? 
67. ????????????????????????????????result?=?cell.getErrorCellValue()?+?“”;?? 
68. ????????????????????????????????break;?? ?? 
69. ????????????????????????????default:?? 
70. ????????????????????????????????throw?new?Exception(“The?cell?data?type?is?invalid”);?? 
71. ????????????????????????????????}?? 
72. ????????????????????????????}?? 
73. ????????????????????????}?? 
74. ????????????????????}?? 
75. ????????????????????k?=?totalRows?+?1;?? 
76. ????????????????}?? 
77. ????????????}?? 
78. ????????}?catch?(Exception?ex)?{?? 
79. ????????????throw?ex;?? 
80. ????????}?? 
81. ????????return?result.toString();?? 
82. ???   ?}? ? 
83. ????//?To?close?the?input?stream?after?fetched?data?? 
84. ????????public?static?void?closeStream()?throws?IOException?{?? 
85. ????????????inputStream.close();?? 
86. ????????}?? 
87. }??????????? 

Sample test data sheet:  

The below code will call the ‘readData’ method which in turn returns the TC_002 username and password and then uses that value inside the ‘sendKeys’ method. 

1. /*To?enter?the?credentials?in?the?Login?page*/?? 
2. ?public?HomePage?login()?throws?IOException{?? 
3. ?????txtbxUserName.sendKeys(ExcelUtility.readData(“testSheet”,?“TC_002”,?“Username”));?? 
4. ?????txtbxPassword.sendKeys(ExcelUtility.readData(“testSheet”,?“TC_002”,?“Password”));?? 
5. ?????lnkLogin.click();?? 
6. ?????return?new?HomePage(driver);?? 
7. ?}??