I have read and write the data from excel using apache POI in selenium. Its working fine. I am using xlsx format(XSSFCell).
Last two days, it shows the exception now
org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
This is my complete code:
package mytestpackage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.NoSuchElementException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.Alert;
import org.openqa.selenium.By;
import org.openqa.selenium.NoAlertPresentException;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
import org.testng.asserts.SoftAssert;
public class Addcourse {
WebDriver driver;
WebDriverWait wait;
XSSFWorkbook workbook;
XSSFSheet sheet;
XSSFCell cell;
String actualTitle;
String expectedTitle;
@BeforeTest
public void TestSetup() {
// Set the path of the Firefox driver.
System.setProperty("webdriver.chrome.driver", "D:\\BALA\\chromedriver\\chromedriver.exe");
// driver = new FirefoxDriver();
driver = new ChromeDriver();
driver.manage().window().maximize();
driver.get("
http://dev.intouchondemand.com:8282");
// driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
wait = new WebDriverWait(driver, 25);
}
private XSSFCell getCellValue(int row, int column) {
XSSFCell cell = sheet.getRow(row).getCell(column, Row.CREATE_NULL_AS_BLANK);
return cell;
}
public void ExcelRead() throws IOException {
File src = new File("D:\\BALA\\nnew2.xls");
FileInputStream finput = new FileInputStream(src);
workbook = new XSSFWorkbook(finput);
}
@Test(priority = 2)
public void Invalid_Data() throws IOException, InterruptedException {
SoftAssert softAssert = new SoftAssert();
ExcelRead();
sheet = workbook.getSheetAt(1);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
try {
cell = getCellValue(i, 1);
String cellValue = getCellValueAsString(cell);
wait.until(ExpectedConditions.elementToBeClickable(By.id("LG")));
driver.findElement(By.id("LG")).clear();
driver.findElement(By.id("LG")).sendKeys(cellValue);
cell = getCellValue(i, 2);
Thread.sleep(4000);
String cellValue1 = getCellValueAsString(cell);
driver.findElement(By.id("PWD")).clear();
driver.findElement(By.id("PWD")).sendKeys(cellValue1);
driver.findElement(By.id("btnSubmit")).click();
Thread.sleep(4000);
File src = new File("D:\\BALA\\nnew2.xls");
FileOutputStream foutput = new FileOutputStream(src);
if(isElementPresent(By.id("divLoginFailed")))
{
softAssert.assertEquals(driver.findElement(By.id("divLoginFailed")).getText(),
"Your Email or Password is invalid.");
String Output = "Email or Password is invalid";
String Status = "Passed ";
sheet.getRow(i).createCell(3).setCellValue(Output);
sheet.getRow(i).createCell(4).setCellValue(Status);
workbook.write(foutput);
}
else if (isAlertPresent()) {
Alert alert = driver.switchTo().alert();
softAssert.assertEquals(closeAlertAndGetItsText(), "Enter Login.");
alert.accept();
String Output = "Email or Password is empty";
String Status = "Passed ";
sheet.getRow(i).createCell(3).setCellValue(Output);
sheet.getRow(i).createCell(4).setCellValue(Status);
workbook.write(foutput);
}
else {
String Output = "Login successful ";
String Status = "Failed ";
sheet.getRow(i).createCell(3).setCellValue(Output);
sheet.getRow(i).createCell(4).setCellValue(Status);
workbook.write(foutput);
}
} catch (Exception e) {
}
}
softAssert.assertAll();
}
public static String getCellValueAsString(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
} else {
return cell.getNumericCellValue() + "";
}
/*
* case Cell.CELL_TYPE_BLANK: return cell.getStringCellValue();
*/
case Cell.CELL_TYPE_BOOLEAN:
return ((Boolean) cell.getBooleanCellValue()).toString();
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula().toString();
}
return null;
}
private boolean isAlertPresent() {
try {
Alert alert = driver.switchTo().alert();
return true;
} catch (NoAlertPresentException e) {
return false;
}
}
private String closeAlertAndGetItsText() {
boolean acceptNextAlert = false;
try {
Alert alert = driver.switchTo().alert();
String alertText = alert.getText();
if (acceptNextAlert) {
alert.accept();
} else {
alert.dismiss();
}
return alertText;
} finally {
acceptNextAlert = true;
}
}
private boolean isElementPresent(By by) {
try {
driver.findElement(by);
return true;
} catch (NoSuchElementException e) {
return false;
}
}
@AfterTest
public void tearDown() throws Exception {
driver.close();
}