Skip to main content
A
Anonymous
November 27, 2006
Answered

cfx_ExcelQuery: specify sheet?

  • November 27, 2006
  • 1 reply
  • 419 views
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
This topic has been closed for replies.
Correct answer cf_dev2
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" />


1 reply

C
cf_dev2Correct answer
Inspiring
November 27, 2006
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" />


A
Anonymous
November 27, 2006
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices