Unable to upload excel file using spring + apache-poi

Question

When I'm trying to upload excel file (.xls/.xlsx), then I'm getting following exception :

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:133)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:106)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:137)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:382)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:364)
    at com.model.Writer.saveOfficeContents(Writer.java:52)
    at com.controller.UploadControl.handleFormUpload(UploadControl.java:58)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:214)
    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:749)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:537)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1085)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
    at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1556)
The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF) null
Upload failed!org.springframework.web.multipart.MultipartException: Constraints Violated
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1513)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
org.springframework.web.multipart.MultipartException: Constraints Violated
    at com.model.Writer.saveOfficeContents(Writer.java:85)
    at com.controller.UploadControl.handleFormUpload(UploadControl.java:58)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:214)
    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:749)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:537)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1085)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
    at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1556)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1513)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

Controller

@RequestMapping(value = "/form", headers = "content-type=multipart/*",  method = RequestMethod.POST)
    public String handleFormUpload(@RequestParam("file") MultipartFile file) throws IOException {
        try {
            if (!file.isEmpty()) {
                Writer.validateOfficeData(file);
                Writer.saveOfficeContents(file);
                System.out.println("Upload successful!");
            }
        } catch (MultipartException e) {
            e.printStackTrace();
            System.out.println("Upload failed!"+e);
        }
        return "redirect:/";
    }

jsp

<form method="post" action="form.htm" enctype="multipart/form-data">
                <input type="file" name="file"/>
                <input type="submit"/>
            </form>

Inside Writer Class

public static void saveOfficeContents(MultipartFile file) throws IOException {
        try {
            Workbook offices = new HSSFWorkbook(file.getInputStream());
            System.out.println("Sheet Index :"+offices.getSheetIndex("Sheet1"));
            Sheet worksheet = offices.getSheetAt(0);
            Row entry;
            Integer noOfEntries = 1;
            //getLastRowNum and getPhysicalNumberOfRows showing false values sometimes.
            while (worksheet.getRow(noOfEntries) != null) {
                noOfEntries++;
            }
            System.out.println(noOfEntries.toString());
            for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
                entry = worksheet.getRow(rowIndex);
                //Integer externalId=((Double)entry.getCell(0).getNumericCellValue()).intValue();
                // Office parent=officeBo.getOfficeByName(entry.getCell(1).getStringCellValue());
                //Long parentId=parent.getId();
                String name = entry.getCell(0).getStringCellValue();
                Date openingDate = entry.getCell(1).getDateCellValue();
//                Date date = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH).parse(openingDate);
                System.out.println("Row Contents:" + name + " " + openingDate);
//                Office office=new Office();
//                office.setParentId((long)parentId);
//                office.setExternalId((long)externalId);
//                office.setName(name);
//                office.setOpeningDate(openingDate);
//                String parentHierarchy=parent.getHierarchy();
//                //Pre save to generate id for use in hierarchy
//                officeBo.save(office);
//                office.setHierarchy(parentHierarchy+office.getId()+".");
//                officeBo.save(office);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage() + " " + e.getCause());
            throw new MultipartException("Constraints Violated");
        }
    }

Anyone suggest me, if anything missing which is causing above exception ?

You're opening a workbook using new HSSFWorkbook, but it seems the file you're trying to open is an Office 2007+ (.xlsx) file, for which you should be using new XSSFWorkbook. That seems clear to me from the exception message, or is there something I'm missing? — Luke Woodward
– Luke Woodward, Commented Jun 7, 2015 at 9:02
You mean.. I should use new XSSFWorkbook instead new HSSFWorkbook . Am I right ?"" — Ravi
– Ravi, Commented Jun 7, 2015 at 9:07
Yes. You should use new XSSFWorkbook for files created in Excel 2007 or later,and new HSSFWorkbook for files created in Excel 2003 or earlier. — Luke Woodward
– Luke Woodward, Commented Jun 7, 2015 at 9:14
@Ravi, you saved my day. I was facing another issue, in my case i was missing the headers = "content-type=multipart/*" — Raghu
– Raghu, Commented Jul 12, 2017 at 4:52
@Raghu you need to upvote teh answer as well :) So that other will also get benefited. — Ravi
– Ravi, Commented Jul 12, 2017 at 6:26

Ravi · Accepted Answer · 2015-06-07 09:50:30Z

5

As Suggested by @Luke, I have made following change in my code to accommodate both version of excel file xls and xlsx

        Workbook offices;
        String lowerCaseFileName = file.getOriginalFilename().toLowerCase();
        if (lowerCaseFileName.endsWith(".xlsx")) {
            offices = new XSSFWorkbook(file.getInputStream());
        } else {
            offices = new HSSFWorkbook(file.getInputStream());
        }

Since, I'm validating the file type before the above code. So, there will be only two scenario either .xls or .xlsx. Also, I have included following libraries to make it work

enter image description here

Hoping this will save somebody's time.

answered Jun 7, 2015 at 9:50

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gagravarr Over a year ago

Why not use WorkbookFactory instead? It detects the type for you

Collectives™ on Stack Overflow

Unable to upload excel file using spring + apache-poi

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related