Java org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException:

org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException:

提问by Learner

I'm using the following jar files:

我正在使用以下 jar 文件：

dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar

Code:

代码：

package ExcelTest;

import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test
{
    public static void main(String[] args)
    {
        try
        {
            FileInputStream file = new FileInputStream(new File("G:\Selenium Jar Files\TestData\Data.xls"));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType())
                    {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

When I compile, I get the following error:

当我编译时，我收到以下错误：

org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:41)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:218)
    at ExcelTest.Test.main(Test.java:21)
Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
    at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:199)
    at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:665)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:274)
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
    ... 2 more

I have gone through the link but was unable to solve the issue. Getting Exception(org.apache.poi.openxml4j.exception - no content type [M1.13]) when reading xlsx file using Apache POI?

我已经浏览了链接，但无法解决问题。 使用 Apache POI 读取 xlsx 文件时出现异常（org.apache.poi.openxml4j.exception - 无内容类型 [M1.13]）？

Can anyone please help me?

谁能帮帮我吗？

采纳答案by PopoFibo

You are trying to read xlswith explicit implementation poi classes for xlsx.

您正在尝试xls使用xlsx.

G:\Selenium Jar Files\TestData\Data.xls

G:\Selenium Jar Files\TestData\Data。xls

Either use HSSFWorkbookand HSSFSheetclasses or make your implementation more generic by using shared interfaces, like;

使用HSSFWorkbook和HSSFSheet类或通过使用共享接口使您的实现更通用，例如；

Change:

改变：

XSSFWorkbook workbook = new XSSFWorkbook(file);

To:

到：

 org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);

And Change:

并改变：

XSSFSheet sheet = workbook.getSheetAt(0);

To:

到：

org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);

回答by Abhishek Shah

You are trying to access an XLS file. However, you are using XSSFWorkbook and XSSFSheet class objects. These classes are mainly used for XLSX files.

您正在尝试访问 XLS 文件。但是，您使用的是 XSSFWorkbook 和 XSSFSheet 类对象。这些类主要用于 XLSX 文件。

For XLS file: HSSFWorkbook& HSSFSheet
For XLSX file: XSSFSheet& XSSFSheet

对于 XLS 文件：HSSFWorkbook&HSSFSheet
对于 XLSX 文件：XSSFSheet&XSSFSheet

So in place of XSSFWorkbookuse HSSFWorkbookand in place of XSSFSheetuse HSSFSheet.

所以代替XSSFWorkbook使用HSSFWorkbook和代替XSSFSheet使用HSSFSheet。

So your code should look like this after the changes are made:

因此，在进行更改后，您的代码应如下所示：

HSSFWorkbook workbook = new HSSFWorkbook(file);

HSSFSheet sheet = workbook.getSheetAt(0);

回答by Gre?anu Emanuel - Vasile

Try this:

尝试这个：

package my_default;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

    public static void main(String[] args) {
        try {
        // Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Get first/desired sheet from the workbook
        XSSFSheet sheet = createSheet(workbook, "Sheet 1", false);

        // XSSFSheet sheet = workbook.getSheetAt(1);//Don't use this line
        // because you get Sheet index (1) is out of range (no sheets)

        //Write some information in the cells or do what you want
        XSSFRow row1 = sheet.createRow(0);
        XSSFCell r1c2 = row1.createCell(0);
        r1c2.setCellValue("NAME");
        XSSFCell r1c3 = row1.createCell(1);
        r1c3.setCellValue("AGE");


        //Save excel to HDD Drive
        File pathToFile = new File("D:\test.xlsx");
        if (!pathToFile.exists()) {
            pathToFile.createNewFile();
        }
        FileOutputStream fos = new FileOutputStream(pathToFile);
        workbook.write(fos);
        fos.close();
        System.out.println("Done");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static XSSFSheet createSheet(XSSFWorkbook wb, String prefix, boolean isHidden) {
    XSSFSheet sheet = null;
    int count = 0;

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        String sName = wb.getSheetName(i);
        if (sName.startsWith(prefix))
            count++;
    }

    if (count > 0) {
        sheet = wb.createSheet(prefix + count);
    } else
        sheet = wb.createSheet(prefix);

    if (isHidden)
        wb.setSheetHidden(wb.getNumberOfSheets() - 1, XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);

        return sheet;
    }

}