Skip to content

Instantly share code, notes, and snippets.

@cflove
Created March 11, 2015 03:37
Show Gist options
  • Save cflove/c3ed90b17eb37da988d7 to your computer and use it in GitHub Desktop.
Save cflove/c3ed90b17eb37da988d7 to your computer and use it in GitHub Desktop.
Custom Tag to Read Excel file with Railo and return a query. This read character data and formula, not binary objects.
<cfparam name="attributes.src" default="0">
<cfparam name="attributes.query" default="q">
<cfswitch expression="#thisTag.ExecutionMode#">
<cfcase value="start">
<cfset inputStream = CreateObject("java", "java.io.FileInputStream").init(JavaCast("string", attributes.src )) />
<cfset XSSFWorkbook = CreateObject("java", "org.apache.poi.xssf.usermodel.XSSFWorkbook").init(inputStream) />
<cfset DataFormatter = CreateObject("java", "org.apache.poi.ss.usermodel.DataFormatter") />
<cfset Evaluator = CreateObject("java", "org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator").init(XSSFWorkbook) />
<cfset sheet = XSSFWorkbook.getSheetAt(0)>
<!--- ************************************************************************ --->
<!--- Loop over rows --->
<!--- ************************************************************************ --->
<cfset q = queryNew('COL_1')>
<cfset rowIterator = sheet.rowIterator() />
<cfloop from = "0" to= "#sheet.getLastRowNum()#" index = "i">
<cfif not IsNull(sheet.getRow(i))>
<cfset queryAddRow(q)>
<cfset thisRow = sheet.getRow(i) />
<cfset RowData = {}>
<!--- ************************************************************************ --->
<!--- Loop over Columns --->
<!--- ************************************************************************ --->
<cfloop from = "#thisRow.getFirstCellNum()#" to= "#thisRow.getLastCellNum()#" index = "c">
<cfif not isNull(thisRow.getCell(c))>
<cfset cell = thisRow.getCell(c) />
<cfset ThisCell = ''>
<cfswitch expression="#cell.getCellType()#">
<cfcase value="2">
<!--- formula --->
<cfset ThisCell = DataFormatter.formatCellValue( Evaluator.evaluateInCell(cell) )>
</cfcase>
<cfdefaultcase>
<!--- data --->
<cfset ThisCell = DataFormatter.formatCellValue(cell)>
</cfdefaultcase>
</cfswitch>
<cfif len(ThisCell)>
<cfset colName = "COL_#c+1#">
<cfif not structKeyExists(q,colName)>
<cfset queryAddColumn(q,colName)>
</cfif>
<cfset querySetCell(q, colName, ThisCell)>
</cfif>
</cfif>
</cfloop>
</cfif>
</cfloop>
<cfset inputStream.close() />
<cfset caller[attributes.query] = q>
</cfcase>
</cfswitch>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment