14 Replies Latest reply on Mar 30, 2013 9:06 PM by jareeq

    Upload of XLSX File using XLS2COLLECTION

    K Cannell
      When uploading XLS files, I am finding that my .XLS files upload, but my .XLSX files (Office 2010) do not.

      Is there some .XLSX setting or option(s) that need(s) to be on/off for the XLS upload to work?

      I have tried with and without macros enabled.
      Compatibility mode is on.

      I am finding that .XLS files upload fine, macros or not.
      Formulas come over as the formula - OK, I pretty much expected that.

      My issue is with XLSX uploads. MS Office 2010.
      For these I get the message below. The message suggests an XML mode that could be switched on of off - or, is the Office 2010 format that much different that this will not work for Office 2012 XLSX documents?

      Is there a particular Office 2010 Excel setting or version that works or does not?
      I have yet to get an XLSX file to upload.

      Any thoughts or information will be helpful.

      Thank you - Karen

      SEVERE: service exception:
      org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of the POI that deals with OLE Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
      ... <exception location trail> ...
        • 1. Re: Upload of XLSX File using XLS2COLLECTION
          Hello Karen,

          this issue has been reported before: {thread:id=2275835}
          It seems that it's actually not possible to import XLSX with the current APEX Listener release. This can be due to a missing case distinction for the mime type in APEX Listener itself, or an error/missing function in the library used. I tried using the most recent version of the library instead of the one included in APEX Listener originally, but it didn't make any difference - XLS still works (so I'm sure I didn't corrupt anything with the exchange), XLSX still doesn't, though I know the library is capable of handling XLSX correctly. The error message also indicates that APEX Listener calls the wrong method to handle the input, which makes me prefer my case distinction theory.

          Despite the Kris' blog posts about that feature ([url http://krisrice.blogspot.com/2010/02/another-apex-listener-ea-more-knobs-to.html]last, [url http://krisrice.blogspot.com/2010/02/yet-another-apex-listener-ea.html]first), which you probably read, I didn't see any documentation on that feature. The default configuration for the current releases doesn't have the "apex.excel2collection" parameters.

          So I came to the conclusion that this "undocumented" feature was initially on the roadmap in EA state, but didn't make it into the production release (yet), though the library used for it is still there and obviously can be used for XLS.
          Perhaps it will be part of a future release, including an updated library and working XLSX support.

          1 person found this helpful
          • 2. Re: Upload of XLSX File using XLS2COLLECTION
            K Cannell
            Thank you for the sanity check, Udo.

            I had seen that post, was just hoping for a configuration setting, something to be able to upload .XLSX files.

            As many know, the reason to want pure XLS upload is to bypass the need for the Save As CSV step.
            With no XLSX upload, even though we can Save As XLS, we lose that big advantage.

            The feature is documented briefly in Export Oracle Application Express, the Apex Listener chapter, and in Kris' blog postings. Aside form that, I see very little on this feature that my users would find very helpful. Except for the XLSX exception.

            Thank you for confirming my experience -
            I hope that Kris gets a windows to work on this one!

            • 3. Re: Upload of XLSX File using XLS2COLLECTION
              Hello Karen,
              The feature is documented briefly in Export Oracle Application Express, the Apex Listener chapter.
              Right, I forgot that parts of the APEX Listener usage are referenced in the APEX docs. Unfortunately, these references seem to be written into the documents based on early roadmaps and were published without reviewing them against the actual production release. The APEX 4.0 Install Guide for instance has a section on [url http://docs.oracle.com/cd/E17556_01/doc/install.40/e15513/pre_require.htm#HTMIG381]HTTP Server Requirements that refers to the APEX Listener as follows
              Oracle Application Express Listener is a Java-based Web server. It features file system caching, support for FOP transformations to PDF, offers improved file upload capability, and is certified against Web Logic, Tomcat, and OC4J with Oracle WebLogic Server, OC4J, and Oracle Glassfish Server.
              The thing is, though Kris developed on Tomcat, Oracle decided to drop official support for non-Oracle products and you won't find anything about Tomcat in any document on APEX Listener any more. This was different in the EA stage of the development. Also, the APEX Listener didn't get the FOP integration that was announced. The XLS import is another bullet on that list.
              The requirements section in the APEX 4.1 document has been corrected accordingly and now reads
              Oracle Application Express Listener is a Java-based Web server. It features file system caching, offers improved file upload capability, and is certified with Oracle WebLogic Server, Oracle Glassfish Server, and OC4J.
              Probably the APEX 4.0 documents had been finished long before the decisions on the feature cut in APEX Listener had been made, because the APEX release was to be released earlier. My guess is that APEX Listener only got minor priority on finishing within the initial schedule and finally failed to do so.
              I also guess "Expert Oracle Application Express" was based on the EA release as well, where it still seemed XLS would make it into production. It's no official documentation released by Oracle, though. ;)
              I hope that Kris gets a windows to work on this one!
              It seems as if that feature had almost been completed, but I guess other projects (most likely SQL Developer) got the focus. With a bit of luck, APEX Listener will be next in line and then finally gets the features that had been planned for the initial release...

              • 4. Re: Upload of XLSX File using XLS2COLLECTION
                Hi K Cannell
                In your entry you have said that, .xls file upload seems to work fine for you. Can u pls explain the procedure for uploading .xls files into the database? Currently i am able to upload excel files only after converting them to .csv format.. So, I need a procedure for uploading them directly in the .xls format..

                Thanks in advance...
                • 5. Re: Upload of XLSX File using XLS2COLLECTION
                  • 6. Re: Upload of XLSX File using XLS2COLLECTION
                    K Cannell
                    Dimitri's post is almost there ... there are some directives to add. I'll respond in full as soon as I have time -
                    and, note that in the NEXT release of the APEX Listener, the XLS2COLLECTION upload will word.
                    I met up with Kris at RMOUG 2012 and confirmed this.
                    1. Use the APEX Listner
                    2. Add directives to apex_config.xml:
                    <entry key="apex.excel2collection">true</entry>
                    <entry key="apex.excel2collection.onecollection">true</entry>
                    <entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry>
                    <entry key="apex.excel2collection.useSheetName">true</entry>

                    3. Create page, create File Browse item that uses WWV_FLOW_FILES
                    4. Create Button, name it XLS2COLLECTION (this has to be the request)
                    5. Create report
                    SELECT * FROM APEX_COLLECTIONS
                    WHERE collection_name = < name of your File Browse item>
                    That's it!
                    This loads the XLS/XLSX worksheet data into a collection.
                    From there, you need to parse the contnets of that collection to you tables.

                    I will post a more complete answer as soon as possible.
                    Good news is, with XLSX working, this is now a vialbe means of loading XLS / XLS data into APEX apps, considering ease of use for end users.
                    Of course one still needs to ware of the possible complexity of the uploaded spreadsheets ... the more one can dictate the XLS file format, the less code to write in parsing/loading data from the collection to your tables.

                    Have fun - KC
                    • 7. Re: Upload of XLSX File using XLS2COLLECTION
                      Hello Karen,
                      note that in the NEXT release of the APEX Listener, the XLS2COLLECTION upload will word.
                      I met up with Kris at RMOUG 2012 and confirmed this.
                      That's great news! Personal contacts are really priceless. ;)
                      Did Kris also announce some rough estimation on when this next APEX Listener release will be available?

                      Since you've started the how to here, I'll add my example.
                      A short sidenote first: It seems the parameter apex.excelcollection.name has no effect, because - as you've already mentioned, you'll always get the name of the file browse item for your collection name if you use just one collection. In my example, this will be the prefix for all collections.
                      h3. Preliminary
                      Take an Excel file with three sheets. The intention is to use the excel upload feature in order to get one collection per sheet, so there should be three different collections after uploading this sheet.
                      h5. Sheet1
                      Col_A     Col_B     C
                      1     2     C2
                      2     2     C3
                      3     2     C4
                      h5. Sheet2
                      Column_A     Column_B     Column_C
                      3.9          2.1          3.2
                      1          2          2.1
                      3.1          2          2.5
                      h5. Sheet3
                      h3. APEX Listener Configuration
                      1. apex_config.xml
                      <entry key="apex.excel2collection">true</entry>
                      <entry key="apex.excel2collection.onecollection">false</entry>
                      h3. APEX Page items
                      Note: Just the name of the file upload item has effect on your upload. The button request value has to have that fixed value, whereas its name as all other names can be chosen freely.
                      1. New blank Page named Excel Upload : *10*
                      2. Simple HTML-Region titled Upload
                      3. File Browse item in region Upload that uses storage type WWV_FLOW_FILES : P10_FILE_BROWSE
                      4. Button in region Upload (next to P10_FILE_BROWSE ): P10_UPLOAD (Button Request: xls2collection )
                      h3. Usage
                      1. Run page
                      2. Choose the Excel file
                      3. View Debug => Collections
                      You should find three collections named using the following pattern +<NAME OF FILE BROWSE ITEM>:<NAME OF SHEET>+ . (Note that all names are upcase). Within these collections, you'll find the values as follows:
                      - C001: Actual sheet name (case sensitive)
                      - C002 onwards: sheet values
                      So, for the example above the result would be:
                      Sequence     Collection Name          C001     C002          C003          C004          C005
                      1          P10_FILE_BROWSE:SHEET1     Sheet1     Col_A          Col_B          C          -
                      2          P10_FILE_BROWSE:SHEET1     Sheet1     1          2          C2          -
                      3          P10_FILE_BROWSE:SHEET1     Sheet1     2          2          C3          -
                      4          P10_FILE_BROWSE:SHEET1     Sheet1     3          2          C4          -
                      1          P10_FILE_BROWSE:SHEET2     Sheet2     Column_A     Column_B     Column_C     -
                      2          P10_FILE_BROWSE:SHEET2     Sheet2     3.9          2.1          3.2          -
                      3          P10_FILE_BROWSE:SHEET2     Sheet2     1          2          2.1          -
                      4          P10_FILE_BROWSE:SHEET2     Sheet2     3.1          2          2.5          -
                      1          P10_FILE_BROWSE:SHEET3     Sheet3     Something     -          -          -
                      2          P10_FILE_BROWSE:SHEET3     Sheet3     different     -          -          -
                      h3. Conclusion
                      APEX Listener's Excel Upload works for Excel versions before 2007 if configured properly.
                      It maps the sheet columns to the collection's character columns, so you can always be sure where your data will be stored. On the other hand, you'll get problems with Excel sheets that use more columns than APEX Collections offer for text data.

                      I hope this helps other people to setup that scenario in their APEX applications.

                      To keep this thread readable for others, I recommend all readers of the examples above to open a new thread for questions or remarks on how to configure or use that feature.

                      @Karen: If you don't mind, I'd open a new (non-question-) thread including your example to have a kind of reference here. I'll delete the corresponding of this post afterwards. Of course, you could do the same if you like using my example.


                      • 8. Re: Upload of XLSX File using XLS2COLLECTION
                        K Cannell
                        Thank you for the example, Udo. It will be awhile before I can get my complete example posted. When I do, I'll post - new post, as you suggest - and on my blog, with appropriate tags so curious persons can find it better.

                        Re "when", as you know, Oracle employees carefully avoid any mention of "when" regarding new releases. My understanding is that we should watch for an Early Adopter of APEX Listener 2 - the XLSX upload capability is included in that.

                        Thank you!
                        • 9. Re: Upload of XLSX File using XLS2COLLECTION
                          Well, I think your presentation of that feature at Kscope12 should be a good motivation to at least get an official (documented) EA that actually includes it and also supports 2007+ formats. Let's hope it'll be there before that date. ;)

                          Either way, good luck for that session!

                          • 10. Re: Upload of XLSX File using XLS2COLLECTION

                            Is this feature available on the standalonde APEX Listener configuration? I tryied with version 1.1.3, modifying the apex-config.xml as you describe and restaring the listener, but the collection does not create inside APEX. Is there any way to check whether the listener is working fine?


                            • 11. Re: Upload of XLSX File using XLS2COLLECTION
                              Hi Josep,

                              the example works with APEX Listener in Standalone Mode.
                              Please don't resurrect this thread for your issue as it has already been marked answered by the original poster.
                              If you want to continue trouble shooting, please create a new thread and provide some additional informtion on your scenario there. Especially, please include a detailed description how you configured and started your APEX Listener, what console output you receive up to the point where you expect the collection to be created, and of course please also describe the steps how you created the upload page exactly, what your XLS file looks like and how you determine the state of the collections.


                              • 12. Re: Upload of XLSX File using XLS2COLLECTION

                                Thanks, I finally got it working... In fact it was already working fine... the problem I had was for two reasons:

                                1) The collection name is not simply the APEX ITEM but it concatenates the excel sheet name. I solved that whit a like condition: collection_name like 'P1_FILE%'

                                2) I was trying to upload an excel with more than 50 columns

                                Thanks and sorry for re-opening the issue!

                                Edited by: jcoves on 23-abr-2012 2:04
                                • 13. Re: Upload of XLSX File using XLS2COLLECTION

                                  Do we know if this has now been fixed by the production release of the Apex Listener?
                                  • 14. Re: Upload of XLSX File using XLS2COLLECTION
                                    checked with 4.2 and listener 2.0.1 --> xls works, xlsx still not