Unable to open an .xlsx file with script in Selenium WebDriver - getting InvalidFormatException

Question

I have a script that requires an excel spreadsheet to be downloaded from a website and opened, ending in an assertion that a certain value exists or not on the spreadsheet.

Getting the following error:

java.lang.AssertionError: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13] at Assert.fail(Assert.java:95)

Any ideas?

Answer 1

Use file extension to handle WorkSheet Type

String inputFilename = new File(path).getName();

switch (inputFilename.substring(inputFilename.lastIndexOf(".") + 1,
   inputFilename.length())) {
      case "xls":
      return readXLS(path);

      case "xlsx":
      return readXLSX(path);
      default:
      Log.e(TAG, "No XLS file chosen");
      return "Please select valid \"Excel\" File\"";
}

For XLSX file: use XSSFWorkbook & XSSFSheet

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(path)));

XSSFSheet sheet = workbook.getSheetAt(0);

For XLS file: use HSSFWorkbook & HSSFSheet

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(path)));

HSSFSheet sheet = workbook.getSheetAt(0);

Refer the link for a more detail

Answer 2

This may happen when your create your XLS/XLSX file through LibreOffice. Apparently something is lost in the conversion and the file is not the same as a spreadsheet made in Microsoft Office. I had the same error and the solution for me was copying all the work I have done in LibreOffice Calc to a MS Excel spreadsheet and then save a new file.