Getting Exception org apache poi openxml4j exception - no content type M1 13 when reading xlsx file using Apache POI

Question

i m using Apache POI XSSF API  for reading xlsx file when i tried to read file i got the following error   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 lt 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 lt 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  lt  getSheetsIndex   length  amp  amp  index  gt  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 lt 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

User · Answer

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   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  lt  numberOfSheet  i                   Sheet sheet   wb getSheetAt i                       Customize your code here                 To get sheet name  try - gt  sheet getSheetName                  catch

User · Answer

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

User · Answer

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

User · Answer

I get the same exception for  xls file  but after I open the file and save it as xlsx file   the below code works    try InputStream is  file getInputStream           XSSFWorkbook workbook   new XSSFWorkbook is

User · Answer

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

User · Answer

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

User · Answer

Cleaned up the code  commented out the logger mostly  to make it run in my Eclipse environment   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  lt  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  lt  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  lt  getSheetsIndex   length  amp  amp  index  gt   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 lt Row gt  rowIter   sheet rowIterator                XSSFRow firstRow    XSSFRow  rowIter next                Iterator lt Cell gt  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 lt Row gt  rowIter   sheet rowIterator                XSSFRow Row    XSSFRow  rowIter next                Iterator lt Cell gt  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 lt Row gt  rowIter   sheet rowIterator                while  rowIter hasNext                      row    XSSFRow  rowIter next                    Iterator lt Cell gt  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 lt Row gt  rowIter   sheet rowIterator                while  i  lt  noOfRows                     row    XSSFRow  rowIter next                    Iterator lt Cell gt  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   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   Cheers  Wim

User · Answer

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   To get round this I parsed the source file into 2 different InputStreams  one to validate and one to process

User · Answer

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    This post helped me solved my problem

[java] Getting Exception(org.apache.poi.openxml4j.exception - no content type [M1.13]) when reading xlsx file using Apache POI?

Examples related to java

Examples related to apache-poi