(org.apache.poi.openxml4j变得异常。当使用Apache POI读取xlsx文件时，没有内容类型[M1.13] ?

i'm using Apache POI(XSSF API) for reading xlsx file.when i tried to read file.i got the following error:

我正在使用Apache POI(XSSF API)来读取xlsx文件。当我试图读取文件时。我有以下错误:

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

Code:

代码:

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;
 }

please help me to sort out this problem.

请帮我解决这个问题。

Thanks

谢谢

9 个解决方案

#1

The error is telling you that POI couldn't find a core part of the OOXML file, in this case the content types part. Your file isn't a valid OOXML file, let alone a valid .xlsx file. It is a valid zip file though, otherwise you'd have got an earlier error

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

Can Excel really load this file? I'd expect it wouldn't be able to, as the exception is most commonly triggered by giving POI a regular .zip file! I suspect your file isn't valid, hence the exception

Excel真的能加载这个文件吗?我希望它不能，因为异常通常是通过给POI一个常规的.zip文件触发的。我怀疑您的文件无效，因此有例外。

.

。

Update: In Apache POI 3.15 (from beta 1 onwards), there's a more helpful set of Exception messages for the more common causes of this problem. You'll now get more descriptive exceptions in this case, eg ODFNotOfficeXmlFileException and OLE2NotOfficeXmlFileException. This raw form should only ever show up if POI really has no clue what you've given it but knows it's broken or invalid.

更新:在Apache POI 3.15(从beta 1开始)中，有一组更有用的异常消息，用于更常见的原因。在本例中，您将获得更多的描述性异常，如ODFNotOfficeXmlFileException和OLE2NotOfficeXmlFileException。如果POI真的不知道你给了它什么，但是知道它是坏的或者无效的，这个原始的表单就应该出现了。

#2

Pretty sure that this exception is thrown when the Excel file is either password protected or the file itself is corrupted. If you just want to read a .xlsx file, try my code below. It's a lot more shorter and easier to read.

很肯定的是，当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 {}
}

#3

You get this exact error should you pass an old school .xls file into this API. Save the .xls as a .xlsx and then it will work.

如果将一个旧的school .xls文件传递到这个API中，就会得到这个错误。将.xls保存为.xlsx，然后就可以工作了。

#4

I was using XSSFWorkbook to read .xls, which resulted in InvalidFormatException. I have to use a more generic Workbook and Sheet to make it work.

我使用XSSFWorkbook来读取.xls，结果是InvalidFormatException。我必须使用更通用的工作簿和表格来让它工作。

This post helped me solved my problem.

这篇文章帮助我解决了我的问题。

#5

Cleaned up the code (commented out the logger mostly) to make it run in my Eclipse environment.

清理代码(主要是注释掉日志记录器)使它在我的Eclipse环境中运行。

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;

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;
    @SuppressWarnings("unused")
    XSSFCell cell = null;
    XSSFSheet sheet = null;
    try {
        loadFile(); // load give Excel
        if (validateIndex(SheetIndex)) {
            sheet = workbook.getSheetAt(SheetIndex);
            Iterator<Row> rowIter = sheet.rowIterator();
            XSSFRow firstRow = (XSSFRow) rowIter.next();
            Iterator<Cell> 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<Row> rowIter = sheet.rowIterator();
            XSSFRow Row = (XSSFRow) rowIter.next();
            Iterator<Cell> 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<Row> rowIter = sheet.rowIterator();
            while (rowIter.hasNext()) {
                row = (XSSFRow) rowIter.next();
                Iterator<Cell> 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<Row> rowIter = sheet.rowIterator();
            while (i < noOfRows) {

                row = (XSSFRow) rowIter.next();
                Iterator<Cell> 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;
}
}

Created this little testcode:

创造了这个小testcode:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;


public class ReadXLSXTest {

/**
 * @param args
 * @throws FileNotFoundException 
 */
public static void main(String[] args) throws FileNotFoundException {
    // TODO Auto-generated method stub


    ReadXLSX test = new ReadXLSX(new FileInputStream(new File("./sample.xlsx")));

    System.out.println(test.getSheetsName());
    System.out.println(test.getNumberOfSheet());


}

}

All this ran like a charm, so my guess is you have an XLSX file that is 'corrupt' in one way or another. Try testing with other data.

所有这些都像一个符咒一样运行，所以我猜你有一个XLSX文件在某种程度上是“腐败”的。尝试使用其他数据进行测试。

Cheers, Wim

欢呼,Wim

#6

I get the same exception for .xls file, but after I open the file and save it as xlsx file , the below code works:

我在.xls文件中得到了相同的异常，但是在打开文件并将其保存为xlsx文件之后，下面的代码工作如下:

 try(InputStream is =file.getInputStream()){
      XSSFWorkbook workbook = new XSSFWorkbook(is);
      ...
 }

#7

If the excel file is password protected, then this error comes up.

如果excel文件是受密码保护的，那么这个错误就会出现。

#8

You might also see this error if you attempt to parse the same file twice from the same source.

如果您试图从相同的源解析同一个文件，您可能也会看到这个错误。

I was parsing the file once to validate and again (from the same InputStream) to process - this produced the above error.

我对文件进行了一次解析，以验证并再次(从相同的InputStream)进行处理——这产生了上述错误。

To get round this I parsed the source file into 2 different InputStreams, one to validate and one to process.

为了解决这个问题，我将源文件解析为两个不同的InputStreams，一个用于验证，一个用于处理。

#9

Try saving the file as Excel Workbook ONLY. NOT any other format. It worked for me. I was getting the same error.

尝试将文件保存为Excel工作簿。没有任何其他格式。它为我工作。我也犯了同样的错误。