10j. Advanced WebDriver – Reading data from excel using POI

Hiya friends! Today, let us delve into excel sheets and understand how to read data from them. As part of automation and building an automation framework, we tend to store data in excel sheets in a predefined format (usually following a template). The data we store is mostly test data, different test URLs, release specific parameters etc. In any such case, knowing how to deal with an excel sheet in our code becomes a lot significant.

This is going to be another pure java article. So, it is time that you grab a cup of coffee (Java)!! We will be using POI jar to achieve this.

Step 1:

As always, our first step is to download the required POI JAR. Head over to, Apache POI and download the binary distribution of the latest stable version at that point (3.17 is the latest stable release at the time of writing this article). Clicking on the binary version for the zip file redirected to the actual download page.

Step 2:

Add these JARs to our project build path. Make sure to select all the JARs under “poi-x.xx”, “ooxml-lib” and “lib” folders. I have placed these in our GitHub repo as well, along with all other code files dealt as part of this post.

We have seen this adding JARs to build path procedure numerous times before and hence I am not re-iterating it (Refer to Step 3 of this article for detailed explanation).

Step 3:

Create a new class, “ExcelOperationsUsingPOI.java”. In this class, let us have a method to read a particular sheet of the excel file from a specific location.

• Create an object of the File class by passing the complete file path of the excel file you wish to open –  File file = new File(filePath+"\\"+fileName);
• Next step is to create a FileInputStream object to obtain input bytes of the excel file –  FileInputStream inputStream = new FileInputStream(file);
• Create a Workbook object –  Workbook myWorkbook = null;
• Excel files can have two extensions most of the time. Either “.xls” or “.xlsx”. Find the extension by splitting the file name using substring method and create Workbook object accordingly.

<span class="token comment">//indexOf gives the index of . in the file name</span>
<span class="token comment">//substring method splits the string starting from index of . to the end</span>
<span class="token class-name">String</span> fileExtensionName <span class="token operator">=</span> fileName<span class="token punctuation">.</span><span class="token function">substring</span><span class="token punctuation">(</span>fileName<span class="token punctuation">.</span><span class="token function">indexOf</span><span class="token punctuation">(</span><span class="token string">"."</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
       
<span class="token comment">//Check condition if the file is xlsx file</span>
<span class="token keyword">if</span><span class="token punctuation">(</span>fileExtensionName<span class="token punctuation">.</span><span class="token function">equals</span><span class="token punctuation">(</span><span class="token string">".xlsx"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token comment">//If it is xlsx file then create object of XSSFWorkbook class</span>
myWorkbook <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">XSSFWorkbook</span><span class="token punctuation">(</span>inputStream<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">//Check condition if the file is xls file</span>
<span class="token keyword">else</span> <span class="token keyword">if</span><span class="token punctuation">(</span>fileExtensionName<span class="token punctuation">.</span><span class="token function">equals</span><span class="token punctuation">(</span><span class="token string">".xls"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token comment">//If it is xls file then create object of HSSFWorkbook class</span>
  myWorkbook <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">HSSFWorkbook</span><span class="token punctuation">(</span>inputStream<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

• With the passed in exact sheet name, that particular sheet can be read –  Sheet mySheet = myWorkbook.getSheet(sheetName);

Now it is very easy to work with rows and columns whose intersection will give us the cell content we wish to read.

Let us now see the code implementing the whole functionality discussed so far using java application with public static void main.

ExcelOperationsUsingPOI.java

<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>io</span><span class="token punctuation">.</span><span class="token class-name">File</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>io</span><span class="token punctuation">.</span><span class="token class-name">FileInputStream</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>io</span><span class="token punctuation">.</span><span class="token class-name">IOException</span><span class="token punctuation">;</span>

<span class="token keyword">import</span> <span class="token namespace">org<span class="token punctuation">.</span>apache<span class="token punctuation">.</span>poi<span class="token punctuation">.</span>hssf<span class="token punctuation">.</span>usermodel</span><span class="token punctuation">.</span><span class="token class-name">HSSFWorkbook</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">org<span class="token punctuation">.</span>apache<span class="token punctuation">.</span>poi<span class="token punctuation">.</span>ss<span class="token punctuation">.</span>usermodel</span><span class="token punctuation">.</span><span class="token class-name">Row</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">org<span class="token punctuation">.</span>apache<span class="token punctuation">.</span>poi<span class="token punctuation">.</span>ss<span class="token punctuation">.</span>usermodel</span><span class="token punctuation">.</span><span class="token class-name">Sheet</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">org<span class="token punctuation">.</span>apache<span class="token punctuation">.</span>poi<span class="token punctuation">.</span>ss<span class="token punctuation">.</span>usermodel</span><span class="token punctuation">.</span><span class="token class-name">Workbook</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">org<span class="token punctuation">.</span>apache<span class="token punctuation">.</span>poi<span class="token punctuation">.</span>xssf<span class="token punctuation">.</span>usermodel</span><span class="token punctuation">.</span><span class="token class-name">XSSFWorkbook</span><span class="token punctuation">;</span>

<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">ExcelOperationsUsingPOI</span> <span class="token punctuation">{</span>
   <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">readExcel</span><span class="token punctuation">(</span><span class="token class-name">String</span> filePath<span class="token punctuation">,</span><span class="token class-name">String</span> fileName<span class="token punctuation">,</span><span class="token class-name">String</span> sheetName<span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">IOException</span><span class="token punctuation">{</span>

        <span class="token comment">//Create a object of File class to open xlsx file</span>
        <span class="token class-name">File</span> file <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">File</span><span class="token punctuation">(</span>filePath<span class="token operator">+</span><span class="token string">"\\"</span><span class="token operator">+</span>fileName<span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">//Create an object of FileInputStream class to read excel file</span>
        <span class="token class-name">FileInputStream</span> inputStream <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">FileInputStream</span><span class="token punctuation">(</span>file<span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token class-name">Workbook</span> myWorkbook <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>

        <span class="token comment">//Find the file extension by spliting file name in substring and getting only extension name</span>
        <span class="token comment">//indexOf gives the index of . in the file name</span>
        <span class="token comment">//substring method splits the string starting from index of . to the end</span>
        <span class="token class-name">String</span> fileExtensionName <span class="token operator">=</span> fileName<span class="token punctuation">.</span><span class="token function">substring</span><span class="token punctuation">(</span>fileName<span class="token punctuation">.</span><span class="token function">indexOf</span><span class="token punctuation">(</span><span class="token string">"."</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
       
        <span class="token comment">//Check condition if the file is xlsx file</span>
        <span class="token keyword">if</span><span class="token punctuation">(</span>fileExtensionName<span class="token punctuation">.</span><span class="token function">equals</span><span class="token punctuation">(</span><span class="token string">".xlsx"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
        <span class="token comment">//If it is xlsx file then create object of XSSFWorkbook class</span>
        	myWorkbook <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">XSSFWorkbook</span><span class="token punctuation">(</span>inputStream<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">//Check condition if the file is xls file</span>
        <span class="token keyword">else</span> <span class="token keyword">if</span><span class="token punctuation">(</span>fileExtensionName<span class="token punctuation">.</span><span class="token function">equals</span><span class="token punctuation">(</span><span class="token string">".xls"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
            <span class="token comment">//If it is xls file then create object of HSSFWorkbook class</span>
        	myWorkbook <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">HSSFWorkbook</span><span class="token punctuation">(</span>inputStream<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">//Read sheet inside the workbook by its name</span>
        <span class="token class-name">Sheet</span> mySheet <span class="token operator">=</span> myWorkbook<span class="token punctuation">.</span><span class="token function">getSheet</span><span class="token punctuation">(</span>sheetName<span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">//Find number of rows in excel file</span>
        <span class="token keyword">int</span> rowCount <span class="token operator">=</span> mySheet<span class="token punctuation">.</span><span class="token function">getLastRowNum</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span> mySheet<span class="token punctuation">.</span><span class="token function">getFirstRowNum</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        
        <span class="token comment">//Create a loop over all the rows of excel file to read it</span>
        <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">int</span> i <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span> i <span class="token operator"><</span> rowCount<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">;</span> i<span class="token operator">++</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token class-name">Row</span> row <span class="token operator">=</span> mySheet<span class="token punctuation">.</span><span class="token function">getRow</span><span class="token punctuation">(</span>i<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token comment">//Create a loop to print cell values in a row</span>
            <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">int</span> j <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span> j <span class="token operator"><</span> row<span class="token punctuation">.</span><span class="token function">getLastCellNum</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> j<span class="token operator">++</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token comment">//Print excel data in console</span>
                <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">print</span><span class="token punctuation">(</span>row<span class="token punctuation">.</span><span class="token function">getCell</span><span class="token punctuation">(</span>j<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">getStringCellValue</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token string">"|| "</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
            <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>		    
   <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

ReadExcelData.java

Used to call the readExcel method and pass required arguments.

<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>io</span><span class="token punctuation">.</span><span class="token class-name">IOException</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">com<span class="token punctuation">.</span>blog<span class="token punctuation">.</span>utility</span><span class="token punctuation">.</span><span class="token class-name">ExcelOperationsUsingPOI</span><span class="token punctuation">;</span>

<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">ReadExcelData</span> <span class="token punctuation">{</span>

  <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> args<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
      <span class="token class-name">ExcelOperationsUsingPOI</span><span class="token punctuation">.</span><span class="token function">readExcel</span><span class="token punctuation">(</span><span class="token string">"E:\\Selenium"</span><span class="token punctuation">,</span> <span class="token string">"ReadUsingPOI.xlsx"</span><span class="token punctuation">,</span> <span class="token string">"Demographics"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">IOException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> 
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

Comments make the code self-explanatory. The excel sheet under consideration has data as shown below,

Accessing this information with our code prints all the values separated by pipes to console as expected.

Give me a shout-out in the comments section if you would like code snippets to retrieve,

1. Zero-based row and column indices for a given entry
2. Value using given zero-based row and column indices
3. All the row values in a list, based on a given entry
4. All the column values in a list, based on a given entry

Experiment with these features and let me know if you have a bumpy ride.

Have a nice day!