CFLib.org – parseExcel

parseExcel(excelFile)

Last updated July 14, 2009

Download UDF

author

anthony petruzzi
Send Email

Version: 0 | Requires: ColdFusion 8 | Library: UtilityLib

Rated 2 time(s). Average Rating: 5.0

Description:
Converts an excel file to a structure of arrays

Return Values:
Returns a struct of arrays.

Example:

Parameters:

Name	Description	Required
excelFile	Excel file to parse.	Yes

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)
 @return Returns a struct of arrays. 
 @author anthony petruzzi (tpetruzzi@gmail.com) 
 @version 0, July 14, 2009 
--->
<cffunction name="parseExcel" access="public" returntype="struct" output="false">
    <cfargument name="excelFile" type="string" required="true">
    <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.cellType = loc.row.getCell(javacast("int", loc.col))>
                <cfset loc.value = "">
                <cfif loc.cellType.getCellType() eq 0>
                    <cfset loc.value = loc.cellType.getNumericCellValue()>
                <cfelse>
                    <cfset loc.value = loc.cellType.getStringCellValue()>
                </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>
    <cfreturn loc.ret>
</cffunction>