Introduction: In Java, the use of POI to read and parse Excel is a common practice; there are 2007,2003 two different formats in Office Excel, often differentiated by different suffixes of xls/xlsx, but users may not know the difference, Therefore, there will be an incompatible format exception
1. POI
Poi originates from the Apache Foundation, is its top-level domain project, and POI provides APIs to Java programs to read and write to Microsoft Office format archives.
2. Differences between Excel 2003 and 2007
Microsoft has used the Ooxml format to describe content information for Excel in its implementation after version 2007, and the previous 2003 version of Excel has been implemented differently, so a new class is available in the POI that is compatible with the 2007 version of Excel's read and create process.
HSSF-provides the ability to read and write files in Microsoft Excel format. XSSF-provides the ability to read and write Microsoft Excel ooxml format files.
3. Description of the problem
In the upload Excel file, parse the Excel file, and the following error message appears in parsing:
APR 1:29:08 PM org.apache.catalina.core.StandardWrapperValve invokeSEVERE:Servlet.service () for Servlet [ Appservlet] In the context with the path [/bsettle] threw exception [Request processing failed; nested exception is Org.apache.poi . POIXMLException:org.apache.poi.openxml4j.exceptions.InvalidFormatException:Package should contain a content type Part [M1.13]] with root causeorg.apache.poi.openxml4j.exceptions.InvalidFormatException:Package should contain a Content type Part [M1.13]at Org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl (zippackage.java:203) at Org.apache.poi.openxml4j.opc.OPCPackage.getParts (opcpackage.java:673) at Org.apache.poi.openxml4j.opc.OPCPackage.open (opcpackage.java:274) at Org.apache.poi.util.PackageHelper.open ( packagehelper.java:37) at org.apache.poi.xssf.usermodel.xssfworkbook.<init> (XSSFWorkbook.java:258) at Com.creditease.bsettle.basic.controller.BasicDataController.uploadBusinessDept (basicdatacontroller.java:350) at Sun.reflect.NativeMethodAccessorIMPL.INVOKE0 (Native Method) at Sun.reflect.NativeMethodAccessorImpl.invoke (nativemethodaccessorimpl.java:39) at Sun.reflect.DelegatingMethodAccessorImpl.invoke (DELEGATINGMETHODACCESSORIMPL.JAVA:25) at Java.lang.reflect.Method.invoke (method.java:597) at Org.springframework.web.method.support.InvocableHandlerMethod.invoke (invocablehandlermethod.java:215) at Org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest (invocablehandlermethod.java:132 ) at Org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle ( SERVLETINVOCABLEHANDLERMETHOD.JAVA:104) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod ( requestmappinghandleradapter.java:745) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal ( requestmappinghandleradapter.java:685) at Org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle (Abstracthandlermethodadapter.jaVA:80) at Org.springframework.web.servlet.DispatcherServlet.doDispatch (dispatcherservlet.java:919) at Org.springframework.web.servlet.DispatcherServlet.doService (dispatcherservlet.java:851) at Org.springframework.web.servlet.FrameworkServlet.processRequest (frameworkservlet.java:953) at Org.springframework.web.servlet.FrameworkServlet.doPost (frameworkservlet.java:855) at Javax.servlet.http.HttpServlet.service (httpservlet.java:647) at Org.springframework.web.servlet.FrameworkServlet.service (frameworkservlet.java:829) at Javax.servlet.http.HttpServlet.service (httpservlet.java:728) at Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (applicationfilterchain.java:305) at Org.apache.catalina.core.ApplicationFilterChain.doFilter (applicationfilterchain.java:210) at Org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal (hiddenhttpmethodfilter.java:77) at Org.springframework.web.filter.OncePerRequestFilter.doFilter (onceperrequestfilter.java:106) at Org.apache.catalina.core.ApplIcationfilterchain.internaldofilter (applicationfilterchain.java:243) at Org.apache.catalina.core.ApplicationFilterChain.doFilter (applicationfilterchain.java:210) at Org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal ( openentitymanagerinviewfilter.java:180) at Org.springframework.web.filter.OncePerRequestFilter.doFilter ( onceperrequestfilter.java:106) at Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter ( applicationfilterchain.java:243) at Org.apache.catalina.core.ApplicationFilterChain.doFilter ( applicationfilterchain.java:210) at Org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal ( characterencodingfilter.java:88) at Org.springframework.web.filter.OncePerRequestFilter.doFilter ( onceperrequestfilter.java:106) at Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter ( applicationfilterchain.java:243) at Org.apache.catalina.core.ApplicationFilterChain.doFilter ( applicationfilterchain.java:210) at Org.apache.catalina.core.StandardWrappervalve.invoke (standardwrappervalve.java:222) at Org.apache.catalina.core.StandardContextValve.invoke ( standardcontextvalve.java:123) at Org.apache.catalina.authenticator.AuthenticatorBase.invoke ( authenticatorbase.java:502) at Org.apache.catalina.core.StandardHostValve.invoke (standardhostvalve.java:171) at Org.apache.catalina.valves.ErrorReportValve.invoke (errorreportvalve.java:100) at Org.apache.catalina.valves.AccessLogValve.invoke (accesslogvalve.java:953) at Org.apache.catalina.core.StandardEngineValve.invoke (standardenginevalve.java:118) at Org.apache.catalina.connector.CoyoteAdapter.service (coyoteadapter.java:408) at Org.apache.coyote.http11.AbstractHttp11Processor.process (abstracthttp11processor.java:1041) at Org.apache.coyote.abstractprotocol$abstractconnectionhandler.process (abstractprotocol.java:603) at Org.apache.tomcat.util.net.jioendpoint$socketprocessor.run (jioendpoint.java:312) at Java.util.concurrent.threadpoolexecutor$worker.runtask (threadpoolexecutor.java:886) at Java.util.conCurrent. Threadpoolexecutor$worker.run (threadpoolexecutor.java:908) at Java.lang.Thread.run (thread.java:662)
In the process of user operation, after uploading Excel, the above error occurred in the background, for the normal situation of Excel is not a problem, after analysis of the upload file found, should be 2007 and 2003 file format caused by different problems.
In the original code, the contents of the parsing Excel are as follows:
Xssfworkbook Hssfworkbook = new Xssfworkbook (File.getinputstream ());
It can be concluded that the current code only supports format resolution for Excel 2007 and cannot support 2003 format. Therefore, abnormal information is generated.
4. Solution
To resolve multiple formats for Excel 2003 and Excel 2007, use the following code to provide good compatibility:
Workbook Workbook = Workbookfactory.create (File.getinputstream ()); Sheet hssfsheet = workbook.getsheetat (0); Schematic access to sheet
Here we use an abstract class workbook to access all the formats of Excel, although some features, such as 2007, may not read properly, but the main content is readable.
5. Summary
The reading of Excel mainly involves the processing of workbooks, workbooks, rows of data, cells, etc., and the POI handles 97-2003 and 2007+ two versions with different classes, as shown in:
which
A) Workbook, Sheet, Row, cell and so on as the interface;
b) Hssfworkbook, Hssfsheet, Hssfrow, Hssfcell for the 97-2003 version of the corresponding processing implementation class;
c) Xssfworkbook, Xssfsheet, Xssfrow, Xssfcell for the 2007+ version of the corresponding processing implementation class;
Resources:
1. http://cgs1999.iteye.com/blog/1525665 Read Excel Files (compatible with 97-2003 and 2007+ two formats) via POI
2. http://stackoverflow.com/questions/21992071/ Org-apache-poi-poixmlexception-org-apache-poi-openxml4j-exceptions-invalidforma
Java POI read Excel package should contain a content type part [M1.13]] with root cause exception problem resolution