Checkout our demo site to practice selenium https://magento.softwaretestingboard.com/

0 like 0 dislike
6.1k views
by Contributing Tester (78 points)
retagged by
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();
}

1 Answer

0 like 0 dislike
by

You are using XSSFWorkbook to read an excel file and your input file is with .xls extension. XSSFWorkbook  is to read .xlsx format and to read .xls format you should use HSSFWorkbook.

by Contributing Tester (78 points)
I am using .xlsx file format. Thats why i am using XSSFWorkbook.
by Expert (572 points)
If I go through your code,

public void ExcelRead() throws IOException {
File src = new File("D:\\BALA\\nnew2.xls");
FileInputStream finput = new FileInputStream(src);
workbook = new XSSFWorkbook(finput);
}

It says, you are using D:\\BALA\\nnew2.xls file to read the data!!!
by Contributing Tester (78 points)
Sorry.You are correct Sir. For testing purpose i changed to xls. It shows the poixml exception for xlsx format.  Thats why i changed to .xls format to try. I am using .xlsx format only.
After getting exception, when i opened the excel file.it shows the message
"Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

But this code is working fine previously. Last two days its showing the exception
by Expert (572 points)
edited by
You can not simply change file format by changing the extension from .xls to .xlsx format.

I suspect that you are simply renaming it. It does not work that way.

The right way is to, change your file's extension back to .xls format > open the file > select 'File' option from menu > select 'Save As..' and save your file with .xlsx extension.

This will create a new file for you. Now, open the file and make sure it opens fine. Once, your excel file works fine, you can use it in your program.


This site is for software testing professionals, where you can ask all your questions and get answers from 1300+ masters of the profession. Click here to submit yours now!

1.4k questions

1.6k answers

866 comments

1.9k users

...