– Common Function Library Project

parseExcel(excelFile[, returnType])

Last updated May 19, 2011


anthony petruzzi

Version: 2 | Requires: CF8 | Library: UtilityLib

Converts an excel file to a structure of arrays

Return Values:
Returns a struct of arrays.


<cfset myVar = ParseExcel("#expandpath('example.xls')#") />
<cfdump var="#myVar#" />


Name Description Required
excelFile Excel file to parse. Yes
returnType Whether to return a query or a structure. Defaults to an empty string which means a query. No

Full UDF Source:

 Converts an excel file to a structure of arrays
 Modded by Raymond Camden to fix incorrect col count
 added minor changes from Tony
 @param excelFile      Excel file to parse. (Required)
 @param returnType      Whether to return a query or a structure. Defaults to an empty string which means a query. (Optional)
 @return Returns a struct of arrays. 
 @author anthony petruzzi ( 
 @version 2, May 19, 2011 
<cffunction name="parseExcel" access="public" returntype="any" output="false">
       <cfargument name="excelFile" type="string" required="true">
       <cfargument name="returnType" type="string" required="false" default="">
       <cfset var loc = {}>
       <!--- structure to hold data retrieved --->
       <cfset loc.ret = {}>
       <!--- did we get headers yet --->
       <cfset loc.firstRow = true>
       <!--- create io stream for the excel file --->
       <cfset = CreateObject("java","").init(excelFile)>
       <!--- read the excel file --->
       <cfset loc.workbook = CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(>
       <!--- get the first sheet of the workbook. zero indexed --->
       <cfset loc.workSheet = loc.workBook.getSheetAt(javacast("int", 0))>
       <!--- get the number of rows the sheet has. zero indexed --->
       <cfset loc.rows = loc.workSheet.getLastRowNum()>

       <cfif !loc.rows>
               <cfreturn loc.ret>

       <!--- array to store data --->
       <cfset = []>
       <!--- loop through the rows and get the values. --->
       <cfloop from="0" to="#loc.rows#" index="loc.atrow">
               <!--- get the row --->
               <cfset loc.row = loc.workSheet.getRow(javacast("int", loc.atrow))>
               <!--- first check to see if first cell 1 is blank, if not process, if so move to next --->
               <cfset loc.checkCell = loc.row.getCell(0).getStringCellValue()>
               <!--- if the first cell isn't blank, proceed --->
               <cfif len(trim(loc.checkCell))>
                       <!--- the first row will tell us the number of columns to process --->
                       <cfif loc.firstRow>
                               <cfset loc.cols = loc.row.getLastCellNum() - 1>
                               <cfset loc.firstrow = false>
                       <cfset loc.values = []>
                       <!--- loop through the columns (cells) of the row and get the values --->
                       <cfloop from="0" to="#loc.cols#" index="loc.col">
                               <cfset loc.value = "">
                               <cfset loc.cellType = loc.row.getCell(javacast("int", loc.col))>
                               <cfif structkeyexists(loc, "celltype")>
                                       <cfif loc.cellType.getCellType() eq 0>
                                               <cfset loc.value = loc.cellType.getNumericCellValue()>
                                               <cfset loc.value = loc.cellType.getStringCellValue()>
                               <cfset arrayappend(loc.values, loc.value)>

                       <!--- store the data --->
                       <cfset arrayappend(, loc.values)>

       <cfif !arraylen(>
               <cfreturn loc.ret>

       <!--- create a struct of arrays to return --->
       <cfset loc.numDown = arraylen(>
       <cfset loc.numAcross = ++loc.cols>
       <cfloop from="1" to="#loc.numAcross#" index="loc.across">
               <cfloop from="1" to="#loc.numDown#" index="loc.down">
               <!--- header --->
               <cfif loc.down eq 1>
                       <cfset loc.key =[loc.down][loc.across]>
                       <cfset loc.ret[loc.key] = []>
                       <cfset arrayappend(loc.ret[loc.key],[loc.down][loc.across])>

       <cfif arguments.returnType eq "query">
               <cfset loc.q = QueryNew("")>
               <cfloop collection="#loc.ret#" item="loc.i">
                       <cfset QueryAddColumn(loc.q, loc.i, "varchar", loc.ret[loc.i])>
               <cfset loc.ret = loc.q>

       <cfreturn loc.ret>


Latest Additions

Raymond Camden added
November 04, 2017

Leigh added
May 11, 2016

Raymond Camden added
May 10, 2016

Kevin Cotton added
May 05, 2016

Raymond Camden added
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson