search

mjul / docjure

Read and write Office documents from Clojure
MIT License
622 stars 129 forks source link

Exceptions while reading XLSX #21

Open pepijndevos opened 10 years ago

pepijndevos commented 10 years ago

I have a confidential Excel document that I want to read data from. It is a set of 3 document, the other two work fine.

If I just read the document as I got it, it tells me it has trouble with CELL_TYPE_ERROR. There appears to be a partial patch for this, from a few years ago.

java.lang.IllegalArgumentException: No method in multimethod 'read-cell' for dispatch value: 5
              MultiFn.java:160 clojure.lang.MultiFn.getFn
              MultiFn.java:227 clojure.lang.MultiFn.invoke
             spreadglob.clj:11 excelgraph.spreadglob/row-values[fn]
             spreadglob.clj:10 excelgraph.spreadglob/row-values[fn]
               LazySeq.java:42 clojure.lang.LazySeq.sval
               LazySeq.java:60 clojure.lang.LazySeq.seq
                   RT.java:484 clojure.lang.RT.seq
                  core.clj:133 clojure.core/seq
                 core.clj:2490 clojure.core/map[fn]
               LazySeq.java:42 clojure.lang.LazySeq.sval
               LazySeq.java:60 clojure.lang.LazySeq.seq
                   RT.java:484 clojure.lang.RT.seq
                  core.clj:133 clojure.core/seq
              protocols.clj:30 clojure.core.protocols/seq-reduce
              protocols.clj:54 clojure.core.protocols/fn
              protocols.clj:13 clojure.core.protocols/fn[fn]
                 core.clj:6177 clojure.core/reduce
                 core.clj:6229 clojure.core/into
             spreadglob.clj:18 excelgraph.spreadglob/parse-sheet[fn]
               LazySeq.java:42 clojure.lang.LazySeq.sval
               LazySeq.java:67 clojure.lang.LazySeq.seq
                   RT.java:484 clojure.lang.RT.seq
                  core.clj:133 clojure.core/seq
                 core.clj:6574 clojure.core/map-indexed[fn]
               LazySeq.java:42 clojure.lang.LazySeq.sval
               LazySeq.java:60 clojure.lang.LazySeq.seq
                   RT.java:484 clojure.lang.RT.seq
                  core.clj:133 clojure.core/seq
              protocols.clj:30 clojure.core.protocols/seq-reduce
              protocols.clj:54 clojure.core.protocols/fn
              protocols.clj:13 clojure.core.protocols/fn[fn]
                 core.clj:6177 clojure.core/reduce
           relationalize.clj:7 excelgraph.relationalize/build-index
                   core.clj:23 neoviz.core/fn
                   core.clj:94 compojure.core/make-route[fn]
                   core.clj:40 compojure.core/if-route[fn]
                   core.clj:25 compojure.core/if-method[fn]
                  core.clj:107 compojure.core/routing[fn]
                 core.clj:2443 clojure.core/some
                  core.clj:107 compojure.core/routing
               RestFn.java:139 clojure.lang.RestFn.applyTo
                  core.clj:619 clojure.core/apply
                  core.clj:112 compojure.core/routes[fn]
         keyword_params.clj:32 ring.middleware.keyword-params/wrap-keyword-params[fn]
          nested_params.clj:70 ring.middleware.nested-params/wrap-nested-params[fn]
                 params.clj:58 ring.middleware.params/wrap-params[fn]
      multipart_params.clj:107 ring.middleware.multipart-params/wrap-multipart-params[fn]
                  flash.clj:31 ring.middleware.flash/wrap-flash[fn]
                session.clj:85 ring.middleware.session/wrap-session[fn]
                   json.clj:42 ring.middleware.json/wrap-json-response[fn]
                  Var.java:415 clojure.lang.Var.invoke
                 reload.clj:18 ring.middleware.reload/wrap-reload[fn]
             stacktrace.clj:17 ring.middleware.stacktrace/wrap-stacktrace-log[fn]
             stacktrace.clj:80 ring.middleware.stacktrace/wrap-stacktrace-web[fn]
                  jetty.clj:18 ring.adapter.jetty/proxy-handler[fn]
              (Unknown Source) ring.adapter.jetty.proxy$org.eclipse.jetty.server.handler.AbstractHandler$0.handle
       HandlerWrapper.java:116 org.eclipse.jetty.server.handler.HandlerWrapper.handle
               Server.java:363 org.eclipse.jetty.server.Server.handle
AbstractHttpConnection.java:483 org.eclipse.jetty.server.AbstractHttpConnection.handleRequest
AbstractHttpConnection.java:931 org.eclipse.jetty.server.AbstractHttpConnection.content
AbstractHttpConnection.java:992 org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content
           HttpParser.java:856 org.eclipse.jetty.http.HttpParser.parseNext
           HttpParser.java:240 org.eclipse.jetty.http.HttpParser.parseAvailable
   AsyncHttpConnection.java:82 org.eclipse.jetty.server.AsyncHttpConnection.handle
SelectChannelEndPoint.java:628 org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle
 SelectChannelEndPoint.java:52 org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run
     QueuedThreadPool.java:608 org.eclipse.jetty.util.thread.QueuedThreadPool.runJob
     QueuedThreadPool.java:543 org.eclipse.jetty.util.thread.QueuedThreadPool$3.run
               Thread.java:724 java.lang.Thread.run

I don't run Windows, so I opened the document in LibreOffice. To make sure that LibreOffice in itself does not change things, I just hit save.

It did change. It appears that this error is due to some unsupported type of formula in Apache POI.

java.lang.RuntimeException: Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)
          WorkbookEvaluator.java:683 org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg
          WorkbookEvaluator.java:527 org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula
          WorkbookEvaluator.java:288 org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny
          WorkbookEvaluator.java:230 org.apache.poi.ss.formula.WorkbookEvaluator.evaluate
       XSSFFormulaEvaluator.java:264 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue
       XSSFFormulaEvaluator.java:117 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate
                    (Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0
    NativeMethodAccessorImpl.java:57 sun.reflect.NativeMethodAccessorImpl.invoke
DelegatingMethodAccessorImpl.java:43 sun.reflect.DelegatingMethodAccessorImpl.invoke
                     Method.java:606 java.lang.reflect.Method.invoke
                   Reflector.java:93 clojure.lang.Reflector.invokeMatchingMethod
                   Reflector.java:28 clojure.lang.Reflector.invokeInstanceMethod
                  spreadsheet.clj:31 dk.ative.docjure.spreadsheet/eval5881[fn]
                    MultiFn.java:227 clojure.lang.MultiFn.invoke
                   spreadglob.clj:11 excelgraph.spreadglob/row-values[fn]
                   spreadglob.clj:10 excelgraph.spreadglob/row-values[fn]
                     LazySeq.java:42 clojure.lang.LazySeq.sval
                     LazySeq.java:60 clojure.lang.LazySeq.seq
                         RT.java:484 clojure.lang.RT.seq
                        core.clj:133 clojure.core/seq
                       core.clj:2490 clojure.core/map[fn]
                     LazySeq.java:42 clojure.lang.LazySeq.sval
                     LazySeq.java:60 clojure.lang.LazySeq.seq
                         RT.java:484 clojure.lang.RT.seq
                        core.clj:133 clojure.core/seq
                    protocols.clj:30 clojure.core.protocols/seq-reduce
                    protocols.clj:54 clojure.core.protocols/fn
                    protocols.clj:13 clojure.core.protocols/fn[fn]
                       core.clj:6177 clojure.core/reduce
                       core.clj:6229 clojure.core/into
                   spreadglob.clj:18 excelgraph.spreadglob/parse-sheet[fn]
                     LazySeq.java:42 clojure.lang.LazySeq.sval
                     LazySeq.java:67 clojure.lang.LazySeq.seq
                         RT.java:484 clojure.lang.RT.seq
                        core.clj:133 clojure.core/seq
                       core.clj:6574 clojure.core/map-indexed[fn]
                     LazySeq.java:42 clojure.lang.LazySeq.sval
                     LazySeq.java:60 clojure.lang.LazySeq.seq
                         RT.java:484 clojure.lang.RT.seq
                        core.clj:133 clojure.core/seq
                    protocols.clj:30 clojure.core.protocols/seq-reduce
                    protocols.clj:54 clojure.core.protocols/fn
                    protocols.clj:13 clojure.core.protocols/fn[fn]
                       core.clj:6177 clojure.core/reduce
                 relationalize.clj:7 excelgraph.relationalize/build-index
                         core.clj:23 neoviz.core/fn
                         core.clj:94 compojure.core/make-route[fn]
                         core.clj:40 compojure.core/if-route[fn]
                         core.clj:25 compojure.core/if-method[fn]
                        core.clj:107 compojure.core/routing[fn]
                       core.clj:2443 clojure.core/some
                        core.clj:107 compojure.core/routing
                     RestFn.java:139 clojure.lang.RestFn.applyTo
                        core.clj:619 clojure.core/apply
                        core.clj:112 compojure.core/routes[fn]
               keyword_params.clj:32 ring.middleware.keyword-params/wrap-keyword-params[fn]
                nested_params.clj:70 ring.middleware.nested-params/wrap-nested-params[fn]
                       params.clj:58 ring.middleware.params/wrap-params[fn]
            multipart_params.clj:107 ring.middleware.multipart-params/wrap-multipart-params[fn]
                        flash.clj:31 ring.middleware.flash/wrap-flash[fn]
                      session.clj:85 ring.middleware.session/wrap-session[fn]
                         json.clj:42 ring.middleware.json/wrap-json-response[fn]
                        Var.java:415 clojure.lang.Var.invoke
                       reload.clj:18 ring.middleware.reload/wrap-reload[fn]
                   stacktrace.clj:17 ring.middleware.stacktrace/wrap-stacktrace-log[fn]
                   stacktrace.clj:80 ring.middleware.stacktrace/wrap-stacktrace-web[fn]
                        jetty.clj:18 ring.adapter.jetty/proxy-handler[fn]
                    (Unknown Source) ring.adapter.jetty.proxy$org.eclipse.jetty.server.handler.AbstractHandler$0.handle
             HandlerWrapper.java:116 org.eclipse.jetty.server.handler.HandlerWrapper.handle
                     Server.java:363 org.eclipse.jetty.server.Server.handle
     AbstractHttpConnection.java:483 org.eclipse.jetty.server.AbstractHttpConnection.handleRequest
     AbstractHttpConnection.java:931 org.eclipse.jetty.server.AbstractHttpConnection.content
     AbstractHttpConnection.java:992 org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content
                 HttpParser.java:856 org.eclipse.jetty.http.HttpParser.parseNext
                 HttpParser.java:240 org.eclipse.jetty.http.HttpParser.parseAvailable
         AsyncHttpConnection.java:82 org.eclipse.jetty.server.AsyncHttpConnection.handle
      SelectChannelEndPoint.java:628 org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle
       SelectChannelEndPoint.java:52 org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run
           QueuedThreadPool.java:608 org.eclipse.jetty.util.thread.QueuedThreadPool.runJob
           QueuedThreadPool.java:543 org.eclipse.jetty.util.thread.QueuedThreadPool$3.run
                     Thread.java:724 java.lang.Thread.run

I don't think there are any tricky formula in this document. They are just rows of data, from the looks of it. Just to be sure, I copied the whole thing, and did paste special, and pasted only text and numbers. This resulted in

org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '' in specified formula ''. Expected cell ref or constant literal
                      FormulaParser.java:219 org.apache.poi.ss.formula.FormulaParser.expected
                     FormulaParser.java:1124 org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor
                     FormulaParser.java:1079 org.apache.poi.ss.formula.FormulaParser.percentFactor
                     FormulaParser.java:1066 org.apache.poi.ss.formula.FormulaParser.powerFactor
                     FormulaParser.java:1426 org.apache.poi.ss.formula.FormulaParser.Term
                     FormulaParser.java:1526 org.apache.poi.ss.formula.FormulaParser.additiveExpression
                     FormulaParser.java:1510 org.apache.poi.ss.formula.FormulaParser.concatExpression
                     FormulaParser.java:1467 org.apache.poi.ss.formula.FormulaParser.comparisonExpression
                     FormulaParser.java:1447 org.apache.poi.ss.formula.FormulaParser.unionExpression
                     FormulaParser.java:1568 org.apache.poi.ss.formula.FormulaParser.parse
                      FormulaParser.java:176 org.apache.poi.ss.formula.FormulaParser.parse
             XSSFEvaluationWorkbook.java:148 org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens
                  WorkbookEvaluator.java:286 org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny
                  WorkbookEvaluator.java:230 org.apache.poi.ss.formula.WorkbookEvaluator.evaluate
               XSSFFormulaEvaluator.java:264 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue
               XSSFFormulaEvaluator.java:117 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate
                            (Unknown Source) sun.reflect.NativeMethodAccessorImpl.invoke0
            NativeMethodAccessorImpl.java:57 sun.reflect.NativeMethodAccessorImpl.invoke
        DelegatingMethodAccessorImpl.java:43 sun.reflect.DelegatingMethodAccessorImpl.invoke
                             Method.java:606 java.lang.reflect.Method.invoke
                           Reflector.java:93 clojure.lang.Reflector.invokeMatchingMethod
                           Reflector.java:28 clojure.lang.Reflector.invokeInstanceMethod
                          spreadsheet.clj:31 dk.ative.docjure.spreadsheet/eval5881[fn]
                            MultiFn.java:227 clojure.lang.MultiFn.invoke
                           spreadglob.clj:11 excelgraph.spreadglob/row-values[fn]
                           spreadglob.clj:10 excelgraph.spreadglob/row-values[fn]
                             LazySeq.java:42 clojure.lang.LazySeq.sval
                             LazySeq.java:60 clojure.lang.LazySeq.seq
                                 RT.java:484 clojure.lang.RT.seq
                                core.clj:133 clojure.core/seq
                               core.clj:2490 clojure.core/map[fn]
                             LazySeq.java:42 clojure.lang.LazySeq.sval
                             LazySeq.java:60 clojure.lang.LazySeq.seq
                                 RT.java:484 clojure.lang.RT.seq
                                core.clj:133 clojure.core/seq
                            protocols.clj:30 clojure.core.protocols/seq-reduce
                            protocols.clj:54 clojure.core.protocols/fn
                            protocols.clj:13 clojure.core.protocols/fn[fn]
                               core.clj:6177 clojure.core/reduce
                               core.clj:6229 clojure.core/into
                           spreadglob.clj:18 excelgraph.spreadglob/parse-sheet[fn]
                             LazySeq.java:42 clojure.lang.LazySeq.sval
                             LazySeq.java:67 clojure.lang.LazySeq.seq
                                 RT.java:484 clojure.lang.RT.seq
                                core.clj:133 clojure.core/seq
                               core.clj:6574 clojure.core/map-indexed[fn]
                             LazySeq.java:42 clojure.lang.LazySeq.sval
                             LazySeq.java:60 clojure.lang.LazySeq.seq
                                 RT.java:484 clojure.lang.RT.seq
                                core.clj:133 clojure.core/seq
                            protocols.clj:30 clojure.core.protocols/seq-reduce
                            protocols.clj:54 clojure.core.protocols/fn
                            protocols.clj:13 clojure.core.protocols/fn[fn]
                               core.clj:6177 clojure.core/reduce
                         relationalize.clj:7 excelgraph.relationalize/build-index
                                 core.clj:23 neoviz.core/fn
                                 core.clj:94 compojure.core/make-route[fn]
                                 core.clj:40 compojure.core/if-route[fn]
                                 core.clj:25 compojure.core/if-method[fn]
                                core.clj:107 compojure.core/routing[fn]
                               core.clj:2443 clojure.core/some
                                core.clj:107 compojure.core/routing
                             RestFn.java:139 clojure.lang.RestFn.applyTo
                                core.clj:619 clojure.core/apply
                                core.clj:112 compojure.core/routes[fn]
                       keyword_params.clj:32 ring.middleware.keyword-params/wrap-keyword-params[fn]
                        nested_params.clj:70 ring.middleware.nested-params/wrap-nested-params[fn]
                               params.clj:58 ring.middleware.params/wrap-params[fn]
                    multipart_params.clj:107 ring.middleware.multipart-params/wrap-multipart-params[fn]
                                flash.clj:31 ring.middleware.flash/wrap-flash[fn]
                              session.clj:85 ring.middleware.session/wrap-session[fn]
                                 json.clj:42 ring.middleware.json/wrap-json-response[fn]
                                Var.java:415 clojure.lang.Var.invoke
                               reload.clj:18 ring.middleware.reload/wrap-reload[fn]
                           stacktrace.clj:17 ring.middleware.stacktrace/wrap-stacktrace-log[fn]
                           stacktrace.clj:80 ring.middleware.stacktrace/wrap-stacktrace-web[fn]
                                jetty.clj:18 ring.adapter.jetty/proxy-handler[fn]
                            (Unknown Source) ring.adapter.jetty.proxy$org.eclipse.jetty.server.handler.AbstractHandler$0.handle
                     HandlerWrapper.java:116 org.eclipse.jetty.server.handler.HandlerWrapper.handle
                             Server.java:363 org.eclipse.jetty.server.Server.handle
             AbstractHttpConnection.java:483 org.eclipse.jetty.server.AbstractHttpConnection.handleRequest
             AbstractHttpConnection.java:931 org.eclipse.jetty.server.AbstractHttpConnection.content
             AbstractHttpConnection.java:992 org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content
                         HttpParser.java:856 org.eclipse.jetty.http.HttpParser.parseNext
                         HttpParser.java:240 org.eclipse.jetty.http.HttpParser.parseAvailable
                 AsyncHttpConnection.java:82 org.eclipse.jetty.server.AsyncHttpConnection.handle
              SelectChannelEndPoint.java:628 org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle
               SelectChannelEndPoint.java:52 org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run
                   QueuedThreadPool.java:608 org.eclipse.jetty.util.thread.QueuedThreadPool.runJob
                   QueuedThreadPool.java:543 org.eclipse.jetty.util.thread.QueuedThreadPool$3.run
                             Thread.java:724 java.lang.Thread.run