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)

Are you sure your .xls file contains pre 2007 excel content? — Sebastien
– Sebastien, Commented Dec 14, 2015 at 21:26
Here is the file type from properties - Microsoft Excel 97-2003 Worksheet (.xls) — Naresh
– Naresh, Commented Dec 14, 2015 at 21:34

mfulton26 · Accepted Answer · 2015-12-14 21:30:12Z

2

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.

answered Dec 14, 2015 at 21:30

mfulton26

31.4k7 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

N.. · Accepted Answer · 2015-12-14 21:27:11Z

0

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.

answered Dec 14, 2015 at 21:27

N..

9561 gold badge7 silver badges24 bronze badges

3 Comments

Naresh Over a year ago

I tried this. The exception has changed but the sheet name is still being read as XML. Below is the testNG trace- org.testng.TestNGException: Method readData requires 1 parameters but 0 were supplied in the @Test annotation. Console shows as below. Sheet Name is: Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml Sheet Name is: Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml

mfulton26 Over a year ago

@Naresh, I see that you've accepted this answer but your comment leads me to think this did not answer your question. Did you by chance accept the wrong answer? Thanks.

Naresh Over a year ago

@mfulton26 thanks for noticing. It might have accidentally happened, not intentional. I have corrected it.

Collectives™ on Stack Overflow

Reading excel data in selenium with testNG

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related