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();
}
}
}