9 Replies Latest reply on Dec 5, 2006 12:00 AM by MikerRoo

    cfx_ExcelQuery

    gr8white Level 1
      Does anyone have any experience using the cfx_ExcelQuery custom tag which is part of the OpenXCF project?

      I installed the tag and wrote a simple program to test the functionality.

      If I call the custom tag w/ no arguments <cfx_ExcelQuery> CF throws an error indicating missing attributes, well & good.
      But when I pass the attributes - I get a JRun Servlet Error

      500 org/apache/poi/poifs/filesystem/POIFSFileSystem
      org/apache/poi/poifs/filesystem/POIFSFileSystem

      The error text appears to be coming from within the tag itself as I am running on a Windows server and what appears to be a directory path isn't related to anything on that server.

      I originally tried passing the entire directory path to the file I'm trying to read, then moved that file to the same directory containing the test program, but same results either way.

      Thanks in advance for any insights,
      Ken
        • 1. Re: cfx_ExcelQuery
          MikerRoo Level 1
          Download and install the Apache POI windows zip file.
          Extract the "final" jar and place it in the CF lib folder.

          You do not declare a CFX for POI but you do need to restart CF.

          Finally, You will probably need to specify the full path to the excel file.
          EG. "C:\temp\38018_NOLAN.xls".
          • 2. Re: cfx_ExcelQuery
            cf_dev2 Level 1
            quote:

            Originally posted by: MikerRoo
            Download and install the Apache POI windows zip file.
            Extract the "final" jar and place it in the CF lib folder.

            You do not declare a CFX for POI but you do need to restart CF.



            Yes cfx_excelQuery uses POI so the POI jar must also be installed somewhere in CF's classpath. We've been using poi-2.5.1-final-20040804.jar. I think its the latest stable version.

            http://jakarta.apache.org/poi/
            • 3. Re: cfx_ExcelQuery
              gr8white Level 1
              Thanks to both of you for the info. Interesting that none of the references I found to cfx_ExcelQuery mentioned the POI dependency.

              As for passing the full file path, as I mentioned I had originally done that and when it didn't work I tried putting the file in the same directory as the template to see if that would solve the problem. I will change it back to using the full file path.

              Ken
              • 4. cfx_ExcelQuery
                gr8white Level 1
                I installed the POI and specified the full file path and now I'm able to read the file. Thanks again.

                Ken
                • 5. Re: cfx_ExcelQuery
                  cf_dev2 Level 1
                  By default it reads the first sheet, but you can use the "sheet" attribute to specify a different sheet number.

                  <!--- warning - "sheet" number is zero-based --->
                  <cfx_ExcelQuery action="read" file="c:\yourFile.xls" sheet="0" variable="myQuery" />
                  <cfx_ExcelQuery action="read" file="c:\yourFile.xls" sheet="1" variable="myQuery" />
                  • 6. Re: cfx_ExcelQuery
                    gr8white Level 1
                    Thanks for the response - I did discover the sheet attribute but it seems to work only with numbers - is there any way to specify a name, or any way to determine the sheet name after passing the index number?

                    (I did start a new thread on this before I saw your response thinking that was better than continuing on the one already marked as answered.)

                    Ken
                    • 7. Re: cfx_ExcelQuery
                      cf_dev2 Level 1
                      See my response on the other thread.
                      • 8. Re: cfx_ExcelQuery
                        baskark
                        hello every one,

                        I got this error when iam trying to convert excel to data

                        Error1 :
                        The CFX custom tag "CFX_ExcelQuery" was not found in the custom tag database. Please be sure to add custom tags to the database before using them. If you have added your tag to the database then you should check the spelling of the tag within your template to insure that it matches the database entry.

                        Error2 :
                        Error processing CFX custom tag "CFX_ExcelQuery".
                        • 9. Re: cfx_ExcelQuery
                          MikerRoo Level 1
                          quote:

                          Originally posted by: baskark
                          hello every one,

                          I got this error when iam trying to convert excel to data

                          Error1 :
                          The CFX custom tag "CFX_ExcelQuery" was not found in the custom tag database. Please be sure to add custom tags to the database before using them. If you have added your tag to the database then you should check the spelling of the tag within your template to insure that it matches the database entry.

                          Error2 :
                          Error processing CFX custom tag "CFX_ExcelQuery".



                          Be sure you have installed it correctly.
                          I've consolidated the instructions to your original post.
                          See http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=1219365 .