0

I have created a test where I am reading from excel and iterating through a worksheet to process an application in a web portal. This is working as expected.

However I am now trying to write results from the web page into another sheet on the same excel. The test case I am running passes in Eclipse but no data is written to the specified sheet. (I'm also looking to iterate on the results sheet to capture multiple application records, haven't got to that part yet).

Please see below my test script and the methods I have created in an ExcelConfig util sheet. Hoping someone can advise where I'm going wrong, thanks in advance.

Steve

Test Case

package com.htb.puma.uatTests;

import org.junit.Test;
import org.openqa.selenium.WebDriver;
import com.htb.puma.pages.SMcaseHeader;
import com.htb.puma.pages.SMhome;
import com.htb.puma.pages.SMloanDetails;
import com.htb.puma.pages.SMlogin;
import com.htb.puma.pages.SetUpConfig;
import com.htb.puma.util.ExcelConfig;
import org.openqa.selenium.Keys;
import org.openqa.selenium.NoAlertPresentException;
import java.io.File;
import java.io.IOException;

public class THAM_WRITE_Test {

    WebDriver driver;

    @Test
    public void specMortHome() throws NoAlertPresentException, InterruptedException, IOException {

        // calling drivers from the SetUpConfig page

        driver = SetUpConfig.getChromeDriver();
        // driver = SetUpConfig.getFirefoxDriver();
        // driver = SetUpConfig.getIEDriver();

        String path = new File("src/test/resources/TestData.xlsx").getAbsolutePath();

        ExcelConfig excel = new ExcelConfig(path);

        int row = 1;
        while (excel.getData("PDFRollUp", row, 0) != "") {

            String loanAmReq = excel.getNumericData("PDFRollUp", row, 6);

            // LOGIN

            SMlogin specMortLogin = new SMlogin(driver);
            specMortLogin.openSMlogin();
            specMortLogin.maximiseWindow();

            specMortLogin.enterUsername("OpsAdminAuto");
            specMortLogin.enterPassword("AutoOps123!");
            specMortLogin.clickSignInBtn();

            Thread.sleep(2000);

            SMhome specMortHome = new SMhome(driver);

            specMortHome.clickTopMC();

            Thread.sleep(2000);

            SMcaseHeader specMortCaseHeader = new SMcaseHeader(driver);

            specMortCaseHeader.clickLoanDetailsTab();

            SMloanDetails specMortLoanDetails = new SMloanDetails(driver);

            Thread.sleep(2000);

            specMortLoanDetails.enterLoanAmReq(Keys.CONTROL + "a"); // PDF
            specMortLoanDetails.enterLoanAmReq(loanAmReq); // PDF

            String erc = specMortLoanDetails.getERC();
            String ltv = specMortLoanDetails.getLTV();

            excel.createFile("src/test/resources/TestData.xlsx");
            excel.writeStringData("Results", 1, 1, erc);
            excel.writeStringData("Results", 1, 2, ltv);

            specMortHome.clickUserActionsHomeLM();
            specMortHome.clickLogoutHomeLM();

            row++;

        }
        driver.quit();
    }

}

Excel Config

package com.htb.puma.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;

public class ExcelConfig {

    public XSSFWorkbook wb;
    XSSFSheet sheet1;

    public ExcelConfig(String Excelpath) {

        File src = new File(Excelpath);
        try {
            FileInputStream fis = new FileInputStream(src);
            wb = new XSSFWorkbook(fis);

        } catch (Exception e) {
            System.out.println("Excel file not loaded");
        }

    }

    // reads the string in the excel file
    public String getData(String sheetName, int row, int column) {
        sheet1 = wb.getSheet(sheetName);
        String data = "";

        try {
            data = sheet1.getRow(row).getCell(column).getStringCellValue();
        } catch (NullPointerException e) {
            data = "";
        }
        return data;
    }

    // reads the number in the excel file
    public String getNumericData(String sheetName, int row, int column) {

        sheet1 = wb.getSheet(sheetName);
        String data = "";

        try {
            // data = sheet.getRow(row).getCell(column).getRawValue();
            DataFormatter dataFormatter = new DataFormatter();
            Cell cell = sheet1.getRow(row).getCell(column);
            data = dataFormatter.formatCellValue(cell);
        } catch (NullPointerException e) {
            data = "";
        }
        return data;
    }

//write string data into excel
public void writeStringData(String sheetName, int row, int column, String data) {
    sheet1 = wb.getSheet(sheetName);

    Row valueRow = sheet1.getRow(row);
    Cell valueCell = valueRow.createCell(column);

    if (data.equals("FAILED")) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        //font.setColor(HSSFColor.RED.index);
        style.setFont(font);
        valueCell.setCellValue(data);
        valueCell.setCellStyle(style);
    }

    valueCell.setCellValue(data);

}

//creates an excel file
public void createFile(String path) {
    File src = new File(path);
    try {
        FileOutputStream outputStream = new FileOutputStream(src);
        try {
            wb.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
}

}

1 Answer 1

1

write(java.io.OutputStream stream) is used to write data to the excel file

public void writeStringData(String sheetName, int row, int column, String data) {

try {
    sheet1 = wb.getSheet(sheetName);
    Row valueRow = sheet1.getRow(row);
    Cell valueCell = valueRow.createCell(column);

    if (data.equals("FAILED")) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        //font.setColor(HSSFColor.RED.index);
        style.setFont(font);
        valueCell.setCellValue(data);
        valueCell.setCellStyle(style);
    }

    valueCell.setCellValue(data);

    FileOutputStream fout;
    fout = new FileOutputStream(new File("<path>"));
    //fout = new FileOutputStream("src/test/resources/TestData.xlsx" );
    wb.write(fout);
    // fout.flush();
    wb.close();
} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (EncryptedDocumentException e) {
    e.printStackTrace();
} catch (NullPointerException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

}

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.