CFLib.org – Common Function Library Project

parseExcel(excelFile[, returnType])

Last updated May 19, 2011

author

anthony petruzzi

Version: 2 | Requires: CF8 | Library: UtilityLib

Description:
Converts an excel file to a structure of arrays

Return Values:
Returns a struct of arrays.

Example:

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

Parameters:

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 (tpetruzzi@gmail.com) 
 @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 loc.io = CreateObject("java","java.io.FileInputStream").init(excelFile)>
       <!--- read the excel file --->
       <cfset loc.workbook = CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(loc.io)>
       <!--- 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>
       </cfif>

       <!--- array to store data --->
       <cfset loc.data = []>
       <!--- 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>
                       </cfif>
                       <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()>
                                       <cfelse>
                                               <cfset loc.value = loc.cellType.getStringCellValue()>
                                       </cfif>
                               </cfif>
                               <cfset arrayappend(loc.values, loc.value)>
                       </cfloop>

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

       <cfif !arraylen(loc.data)>
               <cfreturn loc.ret>
       </cfif>

       <!--- create a struct of arrays to return --->
       <cfset loc.numDown = arraylen(loc.data)>
       <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.data[loc.down][loc.across]>
                       <cfset loc.ret[loc.key] = []>
               <cfelse>
                       <cfset arrayappend(loc.ret[loc.key], loc.data[loc.down][loc.across])>
               </cfif>
               </cfloop>
       </cfloop>

       <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])>
               </cfloop>
               <cfset loc.ret = loc.q>
       </cfif>

       <cfreturn loc.ret>
</cffunction>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson