How to create a new sheet in an excel file using apache poi in selenium webdriver with java

Question

I am working with selenium webdriver - Java.
I need to write the results of test cases in to an excel file, I used Apache POI.I was successful in creating an excel file and writing the results of testcase "Registration" in to it. Now i want to write the result of testcase "Login" to another sheet in same excel file. How can i add a new Sheet to my excel file and write in it? I will explain more clearly. I have 3 classes : QZO.java, QZO_Registration.java and QZO-Login.java. - QZO_Registration.java contains different test cases of Registration. - QZO_Login contains different test cases of Login. - QZO.java contains some functions that are common for QZO_Registration and QZO_Login. - QZO.java contains the code for creating 2 sheets in the class
sheet1 = workbook.createSheet("TestResult_Registration");sheet2 = workbook.createSheet("TestResult_Registration"); When I run the class QZO_Registration an excel file is created with 2 sheets TestResult_Registration and TestResult_Login. The result of the test cases of Registration is written in the sheet TestResult_Registration. But when i run the class QZO_Login, the values in the sheet TestResult_Registration is getting cleared

QZO_Registration.java

package User;
import java.io.IOException;
import junit.framework.Assert;
import jxl.read.biff.BiffException;
import org.openqa.selenium.WebElement;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

@SuppressWarnings("deprecation")
public class QZO_Registration extends QZO {

    @BeforeTest
    public void beforeTest() throws BiffException, IOException,InterruptedException {
        openBrowser(1);
        input(properties.getProperty("VAR_REGISTRATIONDETAILS"));
    }
    /* Registration without entering email */
    @Test(priority = 0)
    public void registrationWithoutEmail() throws InterruptedException {
        try {
            int emptyEmailRowNumber = 1;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyEmailRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyEmailRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyEmailRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
            confirmPassword.sendKeys(getCellContent(3, emptyEmailRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Please enter email", message);
            testresultdata.put("2", new Object[] { 1d, "Registration","Registration without entering email","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("2", new Object[] { 1d, "Registration","Registration without entering email","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Registration without entering username */
    @Test(priority = 1)
    public void registrationWithoutUsername() throws InterruptedException { 
        try {
            int emptyUsernameRowNumber = 2;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyUsernameRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyUsernameRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyUsernameRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
            confirmPassword.sendKeys(getCellContent(3, emptyUsernameRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Enter username", message);
            testresultdata.put("3", new Object[] { 2d, "Registration","Registration without entering username","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("3", new Object[] { 2d, "Registration","Registration without entering username","Should show an error message","Error message is not shown", "Fail" });
        }}
    /* Registration without entering password */
    @Test(priority = 2)
    public void registrationWithoutPassword() throws InterruptedException { 
        try {
            int emptyPasswordRowNumber = 3;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyPasswordRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyPasswordRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyPasswordRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
            confirmPassword.sendKeys(getCellContent(3, emptyPasswordRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Enter password", message);
            testresultdata.put("4", new Object[] { 3d, "Registration","Registration without entering password","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("4", new Object[] { 3d, "Registration","Registration without entering password","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Registration without entering confirmpassword */
    @Test(priority = 3)
    public void registrationWithoutConfirmPassword()throws InterruptedException {       
        try {
            int emptyConfirmPasswordRowNumber = 4;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyConfirmPasswordRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyConfirmPasswordRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyConfirmPasswordRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();        confirmPassword.sendKeys(getCellContent(3,emptyConfirmPasswordRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Enter confirm password", message);
            testresultdata.put("5", new Object[] { 4d, "Registration","Registration without entering confirm password","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("5", new Object[] { 4d, "Registration","Registration without entering confirm password","Should show an error message","Error message is not shown", "Fail" });
        }}
    /* Registration with existing email */
    @Test(priority = 4)
    public void registrationWithExistingEmail() throws InterruptedException {   
        try {
            int emptyExistingEmailRowNumber = 5;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyExistingEmailRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyExistingEmailRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyExistingEmailRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
    confirmPassword.sendKeys(getCellContent(3,emptyExistingEmailRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Email already exists", message);
            testresultdata.put("6", new Object[] { 5d, "Registration","Registration with existing email","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("6", new Object[] { 5d, "Registration","Registration with existing email","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Registration with existing username */
    @Test(priority = 5)
    public void registrationWithExistingUsername() throws InterruptedException {    
        try {
            int emptyExistingUsernameRowNumber = 6;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, emptyExistingUsernameRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, emptyExistingUsernameRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, emptyExistingUsernameRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
confirmPassword.sendKeys(getCellContent(3,emptyExistingUsernameRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Username already exists", message);
            testresultdata.put("7", new Object[] { 6d, "Registration","Registration with existing username","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("7", new Object[] { 6d, "Registration","Registration with existing username","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Registration with password and confirm password are different */
    @Test(priority = 6)
    public void registrationWithPasswordMismatch() throws InterruptedException {    
        try {
            int passwordMismatchRowNumber = 7;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, passwordMismatchRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1, passwordMismatchRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(2, passwordMismatchRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();
    confirmPassword.sendKeys(getCellContent(3,passwordMismatchRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Password and confirm password must match!", message);
            testresultdata.put("8",new Object[] {7d,"Registration","Registration with different password and confirm password","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("8",new Object[] {7d,"Registration","Registration with different password and confirm password","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Successfull Registration */
    @Test(priority = 7)
    public void registrationSuccess() throws InterruptedException { 
        try {
            int successfullRegistrationRowNumber = 8;
            WebElement registration = webElement("VAR_REGISTRATION");
            registration.click();
            Thread.sleep(1000);
            WebElement email = webElement("VAR_REGISTRATION_EMAIL");
            email.clear();
            email.sendKeys(getCellContent(0, successfullRegistrationRowNumber));
            Thread.sleep(1000);
            WebElement username = webElement("VAR_REGISTRATION_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(1,successfullRegistrationRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_REGISTRATION_PASSWORD");
            password.clear();
    password.sendKeys(getCellContent(2,successfullRegistrationRowNumber));
            Thread.sleep(1000);
            WebElement confirmPassword = webElement("VAR_REGISTRATION_CONFIRMPASSWORD");
            confirmPassword.clear();        confirmPassword.sendKeys(getCellContent(3,successfullRegistrationRowNumber));
            Thread.sleep(1000);
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(1000);
            WebElement successMessage = webElement("VAR_SUCCESSMESSAGE");
            String message = successMessage.getText();
            Assert.assertEquals("Registration successful", message);
            Thread.sleep(1000);
            WebElement okbutton = webElement("VAR_REGISTRATION_OKBUTTON");
            okbutton.click();
            testresultdata.put("9", new Object[] { 8d, "Registration","Successfull registration","User should be successfully registered","User is successfully registered", "Pass" });
        } catch (Exception e) {
            testresultdata.put("9", new Object[] { 8d, "Registration","Successfull registration", "Should show an error message","Error message is not shown", "Fail" });
        }
    }
    @AfterTest
    public void afterTest() throws InterruptedException, BiffException, IOException {
        closeBrowser();
    }   }

QZO_Login.java

package User;
import java.io.IOException;
import junit.framework.Assert;
import jxl.read.biff.BiffException;
import org.openqa.selenium.WebElement;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

@SuppressWarnings("deprecation")
public class QZO_login extends QZO {
    @BeforeTest
    public void beforeTest() throws BiffException, IOException,InterruptedException {   
        openBrowser(2);
        input(properties.getProperty("VAR_LOGINDETAILS"));
    }
    /* Login without entering username */
    @Test(priority = 0)
    public void loginWithoutUsername() throws InterruptedException {    
        try {
            int emptyUsernameRowNumber = 1;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, emptyUsernameRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.sendKeys(getCellContent(1, emptyUsernameRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Please enter email!", message);
            testresultdata.put("10", new Object[] { 9d, "Login","Login without username", "Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("10", new Object[] { 9d, "Login","Login without username", "Should show an error message","Error message is not shown", "Fail" });
        }}
    /* Login without entering password */
    @Test(priority = 1)
    public void loginWithoutPassword() throws InterruptedException {
        try {
            int emptyPasswordRowNumber = 2;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, emptyPasswordRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(1, emptyPasswordRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Error! Please enter password!", message);
            testresultdata.put("11", new Object[] { 10d, "Login","Login without password", "Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("11", new Object[] { 10d, "Login","Login without password", "Should show an error message","Error message is not shown", "Fail" });
        }}
    /* Login with wrong password */
    @Test(priority = 2)
    public void loginWithWrongPassword() throws InterruptedException {
        try {
            int wrongPasswordRowNumber = 3;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, wrongPasswordRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(1, wrongPasswordRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Sorry.. Please check your email or password.!", message);
            testresultdata.put("12", new Object[] { 11d, "Login","Login with wrong username","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("12", new Object[] { 11d, "Login","Login with wrong username","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Login with wrong username */
    @Test(priority = 3)
    public void loginWithWrongUsername() throws InterruptedException {
        try {
            int wrongPasswordRowNumber = 4;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, wrongPasswordRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(1, wrongPasswordRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            WebElement errorText = webElement("VAR_ERRORTEXT");
            String message = errorText.getText();
            Assert.assertEquals("Sorry.. Please check your email or password.!", message);
            testresultdata.put("13", new Object[] { 12d, "Login","Login with wrong password","Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("13", new Object[] { 12d, "Login","Login with wrong password","Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Login without clicking on the activation link */
    @Test(priority = 4)
    public void loginWithoutActivation() throws InterruptedException {
        try {
            int wrongUsernameRowNumber = 5;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, wrongUsernameRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(1, wrongUsernameRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            WebElement errorText = webElement("VAR_ERRORTEXT1");
            String message = errorText.getText();
            Assert.assertEquals("Registration not completed !", message);
            WebElement okButton = webElement("VAR_OKBUTTON");
            okButton.click();
            testresultdata.put("14", new Object[] { 13d, "Login","Login without activation", "Should show an error message","An error message is shown", "Pass" });
        } catch (Exception e) {
            testresultdata.put("14", new Object[] { 13d, "Login","Login without activation", "Should show an error message","Error message is not shown", "Fail" });
        }   }
    /* Successful Login */
    @Test(priority = 5)
    public void loginSuccess() throws InterruptedException {
        try {
            int successRowNumber = 6;
            WebElement login = webElement("VAR_LOGIN");
            login.click();
            Thread.sleep(1000);
            WebElement username = webElement("VAR_USERNAME");
            username.clear();
            username.sendKeys(getCellContent(0, successRowNumber));
            Thread.sleep(1000);
            WebElement password = webElement("VAR_PASSWORD");
            password.clear();
            password.sendKeys(getCellContent(1, successRowNumber));
            WebElement continueButton = webElement("VAR_CONTINUE");
            continueButton.click();
            Thread.sleep(2000);
            String currentURL = driver.getCurrentUrl();
Assert.assertEquals(currentURL,"http://mqdemostaging.azurewebsites.net/Dashboard");
            testresultdata.put("15",new Object[] {14d,"Login","Successfull Login","User should be successfully logged in to the application","User is successfully logged in to the application","Pass" });
        } catch (Exception e) {
            testresultdata.put("15", new Object[] { 14d, "Login","Successfull Login", "Should show an error message","Error message is shown", "Fail" });
        }   }
    @AfterTest
    public void afterTest() throws InterruptedException, BiffException, IOException {
        closeBrowser();
    }}

QZO.java

package User;

import java.awt.AWTException;
import java.awt.Robot;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.Point;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.interactions.Action;
import org.openqa.selenium.interactions.Actions;
import org.openqa.selenium.support.ui.Select;

public class QZO {
    WebDriver driver;
    WebDriver tempDriver;
    protected Properties properties;
    private final String PROPERTY_FILE_NAME = "constant.properties";
    private Sheet excelSheet;
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    Map<String, Object[]> testresultdata;
    int rowNumber=1;
    public QZO() {
        properties = new Properties();
        try {
            InputStream inputStream = getClass().getClassLoader()
                    .getResourceAsStream(PROPERTY_FILE_NAME);
            if (null != inputStream) {
                properties.load(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public int openBrowser(int value) throws BiffException, IOException,InterruptedException {

        input(properties.getProperty("VAR_SHEETS"));
        System.setProperty("webdriver.chrome.driver","D:\\MyProjects\\SeleniumTrials\\chromedriver_win32\\chromedriver.exe");
        driver = new ChromeDriver();
        driver.get(properties.getProperty("VAR_BASEURL"));
        driver.manage().window().maximize();
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet("TestResult_Registration");
        //sheet = workbook.createSheet("TestResult_Login");
        testresultdata = new LinkedHashMap<String, Object[]>();
        testresultdata.put("1", new Object[] { "Test Case Id", "Functionality","Action", "Expected Result", "Actual Result", "Status" });
        return 0;
    }

    public void input(String fileName) throws BiffException, IOException,
            InterruptedException {
        FileInputStream fi = new FileInputStream(fileName);
        Workbook w = Workbook.getWorkbook(fi);
        excelSheet = w.getSheet(0);
        Thread.sleep(1000);
    }

    public String getCellContent(int columnNumber, int rowNumber) {
        return excelSheet.getCell(columnNumber, rowNumber).getContents();
    }

    public WebElement webElement(String element) {
        return driver.findElement(By.xpath(properties.getProperty(element)));
    }
public void closeBrowser() throws InterruptedException, BiffException, IOException {


        Thread.sleep(1000);
        Set<String> keyset = testresultdata.keySet();
        int rownum = 0;
        for (String key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = testresultdata.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(cellnum++);
                if (obj instanceof Date)
                    cell.setCellValue((Date) obj);
                else if (obj instanceof Boolean)
                    cell.setCellValue((Boolean) obj);
                else if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Double)
                    cell.setCellValue((Double) obj);
            }
        }
        try {
            FileOutputStream out = new FileOutputStream("D:\\MyProjects\\Selenium Trials\\QZO\\src\\Resources\\Data\\TestResult.xls");
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully..");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver.quit();
    }
}

This didn't work for me. I will explain more clearly. I have 3 classes : QZO.java, QZO_Registration.java and QZO-Login.java. In QZO_Registration.java i have written different test cases of Registration. Similarly, in QZO_Login i have written different test cases of Login. The class QZO.java has some functions that is common for QZO_Registration and QZO_Login. Now i have written the code for creating 2 sheets in the class QZO.java //sheet1 = workbook.createSheet("TestResult_Registration"); sheet2 = workbook.createSheet("TestResult_Registration"); — Amrutha
– Amrutha, Commented Dec 23, 2016 at 10:16
When i run the class QZO_Registration an excel file is created with 2 sheets TestResult_Registration and TestResult_Login. And the result of the test cases of Registration is written in the sheet TestResult_Registration. But when i run the class QZO_Login, the values in the sheet TestResult_Registration is getting cleared. — Amrutha
– Amrutha, Commented Dec 23, 2016 at 10:18

NarendraR · Accepted Answer · 2016-12-23 10:16:18Z

1

You can create like following -

 XSSFSheet sheet= workbook.createSheet("Sheet_No_1");

 XSSFSheet sheet= workbook.createSheet("Sheet_No_2");

and so on .

answered Dec 23, 2016 at 10:16

NarendraR

7,71811 gold badges49 silver badges89 bronze badges

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

3 Comments

Amrutha Over a year ago

Now i'm using HSSFWorkbook and HSSFSheet. Whether i need to import any header files to change this to XSSFWorkbook and XSSFSheet..?

NarendraR Over a year ago

if you are using .xls file then you have to use HSSFSheet & HSSFWorkbook and for .xlsx you need to use XSSFSheet & XSSFWorkbook

Amrutha Over a year ago

Ok. I'm using .xls file.

Maninder · Accepted Answer · 2016-12-23 10:51:41Z

Create a new class in you project with name Xsl_Reader and copy paste the code below.

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;


import java.io.*;
import java.util.Calendar;


public class Xls_Reader {

public  String path;
public  FileInputStream fis = null;
public  FileOutputStream fileOut =null;
private XSSFWorkbook workbook = null;
private XSSFSheet sheet = null;
private XSSFRow row   =null;
private XSSFCell cell = null;

public Xls_Reader(String path) {

    this.path=path;
    try {
        fis = new FileInputStream(path);
        workbook = new XSSFWorkbook(fis);
        sheet = workbook.getSheetAt(0);
        fis.close();
    } catch (Exception e) {

        e.printStackTrace();
    } 

}


// returns the row count in a sheet
public int getRowCount(String sheetName){
    int index = workbook.getSheetIndex(sheetName);
    if(index==-1)
        return 0;
    else{
    sheet = workbook.getSheetAt(index);
    int number=sheet.getLastRowNum()+1;
    return number;
    }

}



// returns the data from a cell
public String getCellData(String sheetName,String colName,int rowNum){
    try{
        if(rowNum <=0)
            return "";

    int index = workbook.getSheetIndex(sheetName);
    int col_Num=-1;
    if(index==-1)
        return "";

    sheet = workbook.getSheetAt(index);
    row=sheet.getRow(0);
    for(int i=0;i<row.getLastCellNum();i++){
        //System.out.println(row.getCell(i).getStringCellValue().trim());
        if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
            col_Num=i;
    }
    if(col_Num==-1)
        return "";

    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum-1);
    if(row==null)
        return "";
    cell = row.getCell(col_Num);

    if(cell==null)
        return "";

    if(cell.getCellType()==Cell.CELL_TYPE_STRING)
          return cell.getStringCellValue();
    else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

          String cellText  = String.valueOf(cell.getNumericCellValue());
          if (HSSFDateUtil.isCellDateFormatted(cell)) {

              double d = cell.getNumericCellValue();

              Calendar cal =Calendar.getInstance();
              cal.setTime(HSSFDateUtil.getJavaDate(d));
                cellText =
                 (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
               cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +
                          cal.get(Calendar.MONTH)+1 + "/" + 
                          cellText;



             }



          return cellText;
      }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
          return ""; 
      else 
          return String.valueOf(cell.getBooleanCellValue());

    }
    catch(Exception e){

        e.printStackTrace();
        return "row "+rowNum+" or column "+colName +" does not exist in xls";
    }
}



// returns the data from a cell
public String getCellData(String sheetName,int colNum,int rowNum){
    try{
        if(rowNum <=0)
            return "";

    int index = workbook.getSheetIndex(sheetName);

    if(index==-1)
        return "";


    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum-1);
    if(row==null)
        return "";
    cell = row.getCell(colNum);
    if(cell==null)
        return "";

  if(cell.getCellType()==Cell.CELL_TYPE_STRING)
      return cell.getStringCellValue();
  else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

      String cellText  = String.valueOf(cell.getNumericCellValue());
      if (HSSFDateUtil.isCellDateFormatted(cell)) {
           // format in form of M/D/YY
          double d = cell.getNumericCellValue();

          Calendar cal =Calendar.getInstance();
          cal.setTime(HSSFDateUtil.getJavaDate(d));
            cellText =
             (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
           cellText = cal.get(Calendar.MONTH)+1 + "/" +
                      cal.get(Calendar.DAY_OF_MONTH) + "/" +
                      cellText;



         }



      return cellText;
  }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
      return "";
  else 
      return String.valueOf(cell.getBooleanCellValue());
    }
    catch(Exception e){

        e.printStackTrace();
        return "row "+rowNum+" or column "+colNum +" does not exist  in xls";
    }
}




// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String colName,int rowNum, String data){
    try{
    fis = new FileInputStream(path); 
    workbook = new XSSFWorkbook(fis);

    if(rowNum<=0)
        return false;

    int index = workbook.getSheetIndex(sheetName);
    int colNum=-1;
    if(index==-1)
        return false;


    sheet = workbook.getSheetAt(index);


    row=sheet.getRow(0);
    for(int i=0;i<row.getLastCellNum();i++){
        //System.out.println(row.getCell(i).getStringCellValue().trim());
        if(row.getCell(i).getStringCellValue().trim().equals(colName))
            colNum=i;
    }
    if(colNum==-1)
        return false;

    sheet.autoSizeColumn(colNum); 
    row = sheet.getRow(rowNum-1);
    if (row == null)
        row = sheet.createRow(rowNum-1);

    cell = row.getCell(colNum); 
    if (cell == null)
        cell = row.createCell(colNum);


    cell.setCellValue(data);

    fileOut = new FileOutputStream(path);

    workbook.write(fileOut);

    fileOut.close();    

    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
    return true;
}



// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){

    try{
    fis = new FileInputStream(path); 
    workbook = new XSSFWorkbook(fis);

    if(rowNum<=0)
        return false;

    int index = workbook.getSheetIndex(sheetName);
    int colNum=-1;
    if(index==-1)
        return false;


    sheet = workbook.getSheetAt(index);

    row=sheet.getRow(0);
    for(int i=0;i<row.getLastCellNum();i++){

        if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))
            colNum=i;
    }

    if(colNum==-1)
        return false;
    sheet.autoSizeColumn(colNum); 
    row = sheet.getRow(rowNum-1);
    if (row == null)
        row = sheet.createRow(rowNum-1);

    cell = row.getCell(colNum); 
    if (cell == null)
        cell = row.createCell(colNum);

    cell.setCellValue(data);
    XSSFCreationHelper createHelper = workbook.getCreationHelper();

    //cell style for hyperlinks

    CellStyle hlink_style = workbook.createCellStyle();
    XSSFFont hlink_font = workbook.createFont();
    hlink_font.setUnderline(XSSFFont.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    //hlink_style.setWrapText(true);

    XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
    link.setAddress(url);
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    fileOut = new FileOutputStream(path);
    workbook.write(fileOut);

    fileOut.close();    

    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
    return true;
}



// returns true if sheet is created successfully else false
public boolean addSheet(String  sheetname){     

    FileOutputStream fileOut;
    try {
         workbook.createSheet(sheetname);   
         fileOut = new FileOutputStream(path);
         workbook.write(fileOut);
         fileOut.close();           
    } catch (Exception e) {         
        e.printStackTrace();
        return false;
    }
    return true;
}

// returns true if sheet is removed successfully else false if sheet does not exist
public boolean removeSheet(String sheetName){       
    int index = workbook.getSheetIndex(sheetName);
    if(index==-1)
        return false;

    FileOutputStream fileOut;
    try {
        workbook.removeSheetAt(index);
        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();            
    } catch (Exception e) {         
        e.printStackTrace();
        return false;
    }
    return true;
}
// returns true if column is created successfully
public boolean addColumn(String sheetName,String colName){


    try{                
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);
        int index = workbook.getSheetIndex(sheetName);
        if(index==-1)
            return false;

    XSSFCellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    sheet=workbook.getSheetAt(index);

    row = sheet.getRow(0);
    if (row == null)
        row = sheet.createRow(0);


    if(row.getLastCellNum() == -1)
        cell = row.createCell(0);
    else
        cell = row.createCell(row.getLastCellNum());

        cell.setCellValue(colName);
        cell.setCellStyle(style);

        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();            

    }catch(Exception e){
        e.printStackTrace();
        return false;
    }

    return true;


}



// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
    try{
    if(!isSheetExist(sheetName))
        return false;
    fis = new FileInputStream(path); 
    workbook = new XSSFWorkbook(fis);
    sheet=workbook.getSheet(sheetName);
    XSSFCellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
    XSSFCreationHelper createHelper = workbook.getCreationHelper();
    style.setFillPattern(HSSFCellStyle.NO_FILL);



    for(int i =0;i<getRowCount(sheetName);i++){
        row=sheet.getRow(i);    
        if(row!=null){
            cell=row.getCell(colNum);
            if(cell!=null){
                cell.setCellStyle(style);
                row.removeCell(cell);
            }
        }
    }
    fileOut = new FileOutputStream(path);
    workbook.write(fileOut);
    fileOut.close();
    }
    catch(Exception e){
        e.printStackTrace();
        return false;
    }
    return true;

}


public boolean isSheetExist(String sheetName){
    int index = workbook.getSheetIndex(sheetName);
    if(index==-1){
        index=workbook.getSheetIndex(sheetName.toUpperCase());
            if(index==-1)
                return false;
            else
                return true;
    }
    else
        return true;
}


// returns number of columns in a sheet 
public int getColumnCount(String sheetName){
    // check if sheet exists
    if(!isSheetExist(sheetName))
     return -1;

    sheet = workbook.getSheet(sheetName);
    row = sheet.getRow(0);

    if(row==null)
        return -1;

    return row.getLastCellNum();



}


//String sheetName, String testCaseName,String keyword ,String URL,String message
public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){


    url=url.replace('\\', '/');
    if(!isSheetExist(sheetName))
         return false;

    sheet = workbook.getSheet(sheetName);

    for(int i=2;i<=getRowCount(sheetName);i++){
        if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){

            setCellData(sheetName, screenShotColName, i+index, message,url);
            break;
        }
    }


    return true; 
}
public int getCellRowNum(String sheetName,String colName,String cellValue){

    for(int i=2;i<=getRowCount(sheetName);i++){
        if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){
            return i;
        }
    }
    return -1;

}


// to run this on stand alone
public static void main(String arg[]) throws IOException{


    Xls_Reader datatable = null;


         datatable = new Xls_Reader("C:\\CM3.0\\app\\test\\Framework\\AutomationBvt\\src\\config\\xlfiles\\Controller.xlsx");
            for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){
                System.out.println(datatable.getCellData("TC5", col, 1));
            }
}}

//End of Xls_Reader class

Now You can Create a new object of this class in you paret class and perform all the function on excel sheet for example refer to your answer.

Xls_Reader xr = new Xls_Reader("C:\\Users\\new28062016\\Desktop\\New Microsoft Office Excel Worksheet.xlsx");
    xr.addSheet("Maninder");
    //Xls_Reader is a class you can add this class into your project and that will help you to perform excel function
    //Now a new sheet with name of must be added. If you wanted to write on this new sheet then you have to simply create columns and results

    xr.addColumn("Maninder", "TC ID");
    xr.addColumn("Maninder", "Description");
    xr.addColumn("Maninder", "Description");

Collectives™ on Stack Overflow

How to create a new sheet in an excel file using apache poi in selenium webdriver with java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related