使用 Apache POI 读取 xlsx 文件时出现异常（org.apache.poi.openxml4j.exception - 无内容类型 \[M1.13\]）？

Question

我正在使用 Apache POI(XSSF API) 读取 xlsx 文件。当我尝试读取文件时。我收到以下错误：

 org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]

代码：

 public class ReadXLSX
{
private String filepath;
private XSSFWorkbook workbook;
private static Logger logger=null;
private  InputStream resourceAsStream;

public ReadXLSX(String FilePath)
{
    logger=LoggerFactory.getLogger("ReadXLSX");
    this.filepath=FilePath;
    resourceAsStream = ClassLoader.getSystemResourceAsStream(filepath);
}

public ReadXLSX(InputStream fileStream)
{
    logger=LoggerFactory.getLogger("ReadXLSX");
    this.resourceAsStream=fileStream;
}
private void loadFile() throws FileNotFoundException, NullObjectFoundException
{
    if(resourceAsStream==null)
        throw new FileNotFoundException("Unable to locate give file..");
    else
    {
        try
        {
           workbook = new XSSFWorkbook(resourceAsStream);
        }
        catch(IOException ex)
        {
        }
    }
}// end loadxlsFile

public String[] getSheetsName()
{
   int totalsheet=0;int i=0;
   String[] sheetName=null;

    try {
        loadFile();
        totalsheet=workbook.getNumberOfSheets();
        sheetName=new String[totalsheet];
        while(i<totalsheet)
        {
           sheetName[i]=workbook.getSheetName(i);
           i++;
        }

    } catch (FileNotFoundException ex) {
       logger.error(ex);
    } catch (NullObjectFoundException ex) {
          logger.error(ex);
    }

   return sheetName;
}

public int[] getSheetsIndex()
{
   int totalsheet=0;int i=0;
   int[] sheetIndex=null;
    String[] sheetname=getSheetsName();
    try {
        loadFile();
        totalsheet=workbook.getNumberOfSheets();
        sheetIndex=new int[totalsheet];
        while(i<totalsheet)
        {
           sheetIndex[i]=workbook.getSheetIndex(sheetname[i]);
           i++;
        }

    } catch (FileNotFoundException ex) {
       logger.error(ex);
    } catch (NullObjectFoundException ex) {
          logger.error(ex);
    }

   return  sheetIndex;
}

private boolean validateIndex(int index)
{
    if(index < getSheetsIndex().length && index >=0)
         return true;
    else
         return false;
}

public int getNumberOfSheet()
{
    int totalsheet=0;
    try {
        loadFile();
        totalsheet=workbook.getNumberOfSheets();

    } catch (FileNotFoundException ex) {
         logger.error(ex.getMessage());
    } catch (NullObjectFoundException ex) {
         logger.error(ex.getMessage());
    }

    return totalsheet;
    }

public int getNumberOfColumns(int SheetIndex)
{
    int NO_OF_Column=0;XSSFCell cell = null;
    XSSFSheet sheet=null;
            try {
                loadFile();  //load give Excel
                if(validateIndex(SheetIndex))
                {
                    sheet  = workbook.getSheetAt(SheetIndex);
                    Iterator rowIter = sheet.rowIterator();
                    XSSFRow firstRow = (XSSFRow) rowIter.next();
                    Iterator cellIter = firstRow.cellIterator();
                    while(cellIter.hasNext())
                    {
                          cell = (XSSFCell) cellIter.next();
                          NO_OF_Column++;
                    }
                }
                else
                    throw new InvalidSheetIndexException("Invalid sheet index.");
            } catch (Exception ex) {
                logger.error(ex.getMessage());

            }

    return NO_OF_Column;
}

public int getNumberOfRows(int SheetIndex)
{
        int NO_OF_ROW=0; XSSFSheet sheet=null;

        try {
                loadFile();  //load give Excel
               if(validateIndex(SheetIndex))
               {
                 sheet  = workbook.getSheetAt(SheetIndex);
                 NO_OF_ROW = sheet.getLastRowNum();
               }
               else
                    throw new InvalidSheetIndexException("Invalid sheet index.");
            } catch (Exception ex) {
                logger.error(ex);}

    return NO_OF_ROW;
}

 public String[] getSheetHeader(int SheetIndex)
{
            int noOfColumns = 0;XSSFCell cell = null; int i =0;
            String columns[] = null; XSSFSheet sheet=null;

            try {
                    loadFile();  //load give Excel
                    if(validateIndex(SheetIndex))
                    {
                     sheet  = workbook.getSheetAt(SheetIndex);
                     noOfColumns = getNumberOfColumns(SheetIndex);
                     columns = new String[noOfColumns];
                     Iterator rowIter = sheet.rowIterator();
                     XSSFRow Row = (XSSFRow) rowIter.next();
                     Iterator cellIter = Row.cellIterator();

                     while(cellIter.hasNext())
                     {
                        cell  = (XSSFCell) cellIter.next();
                        columns[i] = cell.getStringCellValue();
                        i++;
                     }
                  }
                    else
                         throw new InvalidSheetIndexException("Invalid sheet index.");
                }

                 catch (Exception ex) {
                    logger.error(ex);}

            return columns;
}//end of method

 public String[][] getSheetData(int SheetIndex)
 {
    int noOfColumns = 0;XSSFRow row = null;
    XSSFCell cell = null;
    int i=0;int noOfRows=0;
    int j=0;
    String[][] data=null; XSSFSheet sheet=null;

    try {
                    loadFile();  //load give Excel
                    if(validateIndex(SheetIndex))
                    {
                            sheet  = workbook.getSheetAt(SheetIndex);
                            noOfColumns = getNumberOfColumns(SheetIndex);
                            noOfRows =getNumberOfRows(SheetIndex)+1;
                            data = new String[noOfRows][noOfColumns];
                            Iterator rowIter = sheet.rowIterator();
                            while(rowIter.hasNext())
                            {
                                row = (XSSFRow) rowIter.next();
                                Iterator cellIter = row.cellIterator();
                                j=0;
                                while(cellIter.hasNext())
                                {
                                    cell  = (XSSFCell) cellIter.next();
                                    if(cell.getCellType() == cell.CELL_TYPE_STRING)
                                    {
                                        data[i][j] = cell.getStringCellValue();
                                    }
                                    else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)
                                    {
                                        if (HSSFDateUtil.isCellDateFormatted(cell))
                                        {
                                         String formatCellValue = new DataFormatter().formatCellValue(cell);
                                         data[i][j] =formatCellValue;
                                        }
                                        else
                                        {
                                          data[i][j] = Double.toString(cell.getNumericCellValue());
                                        }

                                    }
                                    else if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN)
                                    {
                                         data[i][j] = Boolean.toString(cell.getBooleanCellValue());
                                    }

                                    else if(cell.getCellType() == cell.CELL_TYPE_FORMULA)
                                    {
                                         data[i][j] = cell.getCellFormula().toString();
                                    }

                                    j++;
                                }

                                i++;
                            }   // outer while

                    }
                    else throw new InvalidSheetIndexException("Invalid sheet index.");

                } catch (Exception ex) {
                    logger.error(ex);}
        return data;
 }

 public String[][] getSheetData(int SheetIndex,int noOfRows)
 {
    int noOfColumns = 0;
    XSSFRow row = null;
    XSSFCell cell = null;
    int i=0;
    int j=0;
    String[][] data=null;
    XSSFSheet sheet=null;

    try {
                    loadFile();  //load give Excel

                  if(validateIndex(SheetIndex))
                  {
                            sheet  = workbook.getSheetAt(SheetIndex);
                             noOfColumns = getNumberOfColumns(SheetIndex);
                             data = new String[noOfRows][noOfColumns];
                            Iterator rowIter = sheet.rowIterator();
                            while(i<noOfRows)
                            {

                                row = (XSSFRow) rowIter.next();
                                Iterator cellIter = row.cellIterator();
                                j=0;
                                while(cellIter.hasNext())
                                {
                                    cell  = (XSSFCell) cellIter.next();
                                    if(cell.getCellType() == cell.CELL_TYPE_STRING)
                                    {
                                        data[i][j] = cell.getStringCellValue();
                                    }
                                    else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)
                                    {
                                         if (HSSFDateUtil.isCellDateFormatted(cell))
                                        {
                                         String formatCellValue = new DataFormatter().formatCellValue(cell);
                                         data[i][j] =formatCellValue;
                                        }
                                        else
                                        {
                                          data[i][j] = Double.toString(cell.getNumericCellValue());
                                        }
                                    }

                                    j++;
                                }

                                i++;
                            }   // outer while
              }else  throw new InvalidSheetIndexException("Invalid sheet index.");
    } catch (Exception ex) {
       logger.error(ex);
    }

    return data;
 }

请帮我解决这个问题。

谢谢

原文由 Sameek Mishra 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

该错误告诉您 POI 找不到 OOXML 文件的核心部分，在本例中是内容类型部分。您的文件不是有效的 OOXML 文件，更不用说有效的 .xlsx 文件了。虽然它是一个有效的 zip 文件，否则你会遇到更早的错误

Excel 真的可以加载这个文件吗？我希望它不能，因为异常通常是通过给 POI 一个常规的 .zip 文件来触发的！我怀疑您的文件无效，因此出现异常

.

更新： 在 Apache POI 3.15（从 beta 1 开始）中，对于这个问题的更常见原因，有一组更有用的异常消息。在这种情况下，您现在将获得更多描述性异常，例如 ODFNotOfficeXmlFileException 和 OLE2NotOfficeXmlFileException 。只有当 POI 确实不知道您提供了什么但知道它已损坏或无效时，才会显示此原始表单。

原文由 Gagravarr 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

当 Excel 文件受密码保护或文件本身已损坏时，可以肯定会抛出此异常。如果您只想阅读 .xlsx 文件，请尝试下面的代码。它更短，更容易阅读。

 import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
//.....

static final String excelLoc = "C:/Documents and Settings/Users/Desktop/testing.xlsx";

public static void ReadExcel() {
InputStream inputStream = null;
   try {
        inputStream = new FileInputStream(new File(excelLoc));
        Workbook wb = WorkbookFactory.create(inputStream);
        int numberOfSheet = wb.getNumberOfSheets();

        for (int i = 0; i < numberOfSheet; i++) {
             Sheet sheet = wb.getSheetAt(i);
             //.... Customize your code here
             // To get sheet name, try -> sheet.getSheetName()
        }
   } catch {}
}

原文由 Wilts C 发布，翻译遵循 CC BY-SA 3.0 许可协议