cfx_ExcelQuery

[Guest]

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

[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.

Finally, You will probably need to specify the full path to the excel file.
EG. "C:\temp\38018_NOLAN.xls".

[cf_dev2 · Accepted solution]

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/

[Guest]

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

[Guest]

I installed the POI and specified the full file path and now I'm able to read the file. Thanks again.

Ken

[cf_dev2]

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" />

[Guest]

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

[cf_dev2]

See my response on the other thread.

[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".

[MikerRoo]

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 .