cfx_ExcelQuery: specify sheet?

[Guest]

I installed the cfx_ExcelQuery custom tag which is part of the OpenXCF project and with the help of this forum have been able to successfully read data from an Excel file.

Does anyone know if there is a way to specify a specific worksheet to read by name? By trial and error I discovered that a specific sheet can be accessed by passing an index number (sheet="0") but haven't been able to get it to take a sheet name.

If not - no big deal, it's just that the program I'm working on originally used ODBC and required the user to specify the name of the sheet with the pertinent data.

Thanks in advance,
Ken

[cf_dev2 · Accepted solution]

The tag only accepts a number. You could use POI to get the sheet number.

<cfscript>
fis = createObject("java", "java.io.FileInputStream").init("c:\yourFile.xls");
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fis);
sheetNumber = wb.getSheetIndex("Sheet1");
fis.close();
</cfscript>
<cfx_ExcelQuery action="read" file="c:\yourFile.xls" sheet="#sheetNumber#" variable="myQuery" />

[Guest]

That works great, thanks so much. It even returns a trappable value (-1) if an invalid name is specified, allowing me to cleanly alert the user.

I really appreciate the help, before I got this straightened out I was about to give up and use COM which I was trying to avoid.

Ken