Reading excel data in selenium with testNG

Question

I am new to Selenium and still learning. I am trying to write a program in TestNG with two tests to read data from excel and to write, using Apache POI. I have one XLS and one XLSX file in the same folder and trying to use both the files. I am looking to configure path and name of the excels in testng.xml. When I executed my first test to read data, the sheet name is being read as xml. Below is my testng.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Suite" parallel="tests" thread-count="2">
<parameter name="filePath" value="C:\\Technologies\\Selenium\\WebDriver\\DataFiles\\ExcelFiles"></parameter>
  <test name="Xlsx">
  <parameter name="fileName" value="12142015_sx.xlsx"></parameter>
  <parameter name="sheetName" value="xlsx_1"></parameter>
    <classes>
      <class name="handlefiles.handleExcel"/>
    </classes>
  </test>
  <test name="Xls">
  <parameter name="fileName" value="12142015_s.xls"></parameter>
  <parameter name="sheetName" value="xls_1"></parameter>
    <classes>
      <class name="handlefiles.handleExcel"/>
    </classes>
  </test> <!-- Test -->
</suite> <!-- Suite -->

Below is java class code.

package handlefiles;

import java.io.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.testng.annotations.*;

public class handleExcel {

    @BeforeTest
    @Parameters({"filePath", "fileName", "sheetName"})
    public void readExcel(String filePath, String fileName, String sheetName) throws IOException{
        // set the file path
        File eFile = new File(filePath+"\\"+fileName);
        //print the file path
        System.out.println("File is at: "+eFile);

        FileInputStream inputstream = new FileInputStream(eFile);
        Workbook wb = null;

        // Read and publish extension
        String extensionName = fileName.substring(fileName.indexOf("."));
        System.out.println("File type is: "+extensionName);

        //Read the file
        if(extensionName.equalsIgnoreCase(".xlsx")){
            wb = new XSSFWorkbook(inputstream);
        }else if(extensionName.equalsIgnoreCase(".xls")){
            wb = new HSSFWorkbook(inputstream);
        }
        //Read the sheet name
        Sheet wbSheet = wb.getSheet(sheetName);
        System.out.println("Sheet Name is: "+wbSheet);

    }

  @Test(priority=1)
  public void readData(Sheet wbSheet) {

    // Get the row count
            int rowCount = wbSheet.getLastRowNum() - wbSheet.getFirstRowNum();

            // print data from excel
            for (int i=0; i<rowCount-1; i++){
                Row row = wbSheet.getRow(i);
                for (int j=0; j<row.getLastCellNum(); j++){
                    System.out.println(row.getCell(j).getStringCellValue()+"||");
                }
            }
  }
}

When I run this in eclipse, I see below exception-

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:128)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:112)
    at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:300)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:400)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:381)
    at handlefiles.handleExcel.readExcel(handleExcel.java:36)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:86)
    at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:514)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:215)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:142)
    at org.testng.TestRunner.beforeRun(TestRunner.java:656)
    at org.testng.TestRunner.run(TestRunner.java:624)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:366)
    at org.testng.SuiteRunner.access$000(SuiteRunner.java:39)
    at org.testng.SuiteRunner$SuiteWorker.run(SuiteRunner.java:400)
    at org.testng.internal.thread.ThreadUtil$2.call(ThreadUtil.java:64)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Answer 1

According to the stack trace POI thinks you are attempting to open an XLSX file using POI's XLS code (not XLSX code). Perhaps the file extension is wrong for that file is ".xls" but should be ".xlsx" or the file is corrupted, etc. (or maybe you've found a bug).

I suggest using POI's WorkbookFactory instead:

Factory for creating the appropriate kind of Workbook (be it HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.

Answer 2

Remove following IF Condition and

if(extensionName.equalsIgnoreCase(".xlsx")){
            wb = new XSSFWorkbook(inputstream);
        }else if(extensionName.equalsIgnoreCase(".xls")){
            wb = new HSSFWorkbook(inputstream);
        }

Add this piece of code

 wb = new XSSFWorkbook(inputstream);

I used in past and I never told which extension I am using.