1
votes

mvn test doesnt work with Apache Poi.

  1. If i exclude the code associated to Apache POI within my Framework: mvn test works.

  2. However my code needs to access specific data from an Excel sheet and therefore i need to use 'mvn test' and Apache POI together.

  3. Seem to be getting the following exception when using mvn test: enter image description here

  4. For example if i comment the following code 'examples' mvn test successfully executed my test cases: enter image description here

enter image description here

<project xmlns="http://maven.apache.org/POM/4.0.0"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>uk.co.uat.pizzahut</groupId>
<artifactId>PhFramework</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>PhFramework</name>
<url>http://maven.apache.org</url>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<build>
    <pluginManagement>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.3</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.19.1</version>
                <configuration>
                    <suiteXmlFiles>
                        <!-- TestNG suite XML files -->
                        <suiteXmlFile>testng.xml</suiteXmlFile>
                    </suiteXmlFiles>
                </configuration>
            </plugin>

        </plugins>
    </pluginManagement>
</build>


<dependencies>
    <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>2.53.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.4</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.15-beta2</version>
    </dependency>


    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-collections4</artifactId>
        <version>4.0</version>
    </dependency>

    <dependency>
        <groupId>commons-collections</groupId>
        <artifactId>commons-collections</artifactId>
        <version>3.2.2</version>
    </dependency>

    <dependency>
        <groupId>com.danielflower.apprunner</groupId>
        <artifactId>app-runner</artifactId>
        <version>0.1.8</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>6.8</version>
    </dependency>

    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-server</artifactId>
        <version>3.0.1</version>
    </dependency>

    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>20.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>

</dependencies>

<!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-server -->

Excel reader code:

private XSSFSheet sheet = null;
private XSSFRow row = null;
private XSSFCell cell = null;
String path = null;


public ExcelReader() throws IOException {
    path = System.getProperty("user.dir") + "\\src\\main\\java\\PhFramework\\testData\\pricebandPrices.xlsx";
    fis = new FileInputStream(path);
    workbook = new XSSFWorkbook(fis);


    sheet = workbook.getSheetAt(0);
}


public int getSheetRows() throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    return (sheet.getLastRowNum() + 1);
}


public int getSheetColumns(String sheetName) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(0);
    return (row.getLastCellNum());
}


public String getCellDataString(int colNum, int rowNum) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum);
    cell = row.getCell(colNum);
    return (cell.getStringCellValue());
}

public double getCellDataDouble(int colNum, int rowNum) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum);
    cell = row.getCell(colNum);
    return (cell.getNumericCellValue());
}

public String getCellDataText(String colName, int rowNum) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int colNum = -1;
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    for (int i = 0; i < getSheetColumns(p.getProperty("priceband")); i++) {
        row = sheet.getRow(0);
        cell = row.getCell(i);
        if (cell.getStringCellValue().equals(colName)) {
            colNum = cell.getColumnIndex();
            break;
        }
    }
    row = sheet.getRow(rowNum);
    cell = row.getCell(colNum);
    return (cell.getStringCellValue());
}

public double getCellDataNumber(String colName, int rowNum) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int colNum = -1;
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    for (int i = 0; i < getSheetColumns(p.getProperty("priceband")); i++) {
        row = sheet.getRow(0);
        cell = row.getCell(i);
        if (cell.getStringCellValue().equals(colName)) {
            colNum = cell.getColumnIndex();
            break;
        }
    }
    row = sheet.getRow(rowNum);
    cell = row.getCell(colNum);
    return (cell.getNumericCellValue());
}

public void setCellData(int colNum, int rowNum, String str) throws IOException {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    int index = workbook.getSheetIndex(p.getProperty("priceband"));
    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(str);
    try {
        fileOut = new FileOutputStream(path);

        try {
            workbook.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
 }

}

enter image description here

1
how is apache poi dependency defined in pom.xml?Vladimir
I think you have missed entry of Apache-poi jar in your pom.xmlAmitB10
What is the output of mvn dependency:tree?Florian Albrecht
@Vladimir please find the POM tree code attatched to the original questions, please note I have also manually added Apache POI 3.15 via EclipseGbru
you have two versions of apache-poi - 3.15 and 3.15-beta2Vladimir

1 Answers

2
votes

org.apache.poi.xssf... is provided by poi-ooxml artifact, you need to add it to your pom.xml:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

you also might want to remove 3.15-beta2 version of poi artifact