Importing Excel Spreadsheet Into SQL Database (Coldfusion 9)

Question

I'm writing a script to import a large excel spreadhseet into an SQL Server 2008 database. Everything is working fine except for one minor detail:

If a cell in the sheet has multiple options (like a <select> dropdown menu), only the selected option gets grabbed. I want to grab every possible option the cell has, not just the one being used (See my SQL query for why)

I have searched google and S/O for answers but I have not encountered a solution for this particular situation. Here is a link to the Spreadsheet Functions I am using.

I cannot show you the excel sheet, but it is safe to assume the traversing of the sheet is correct (I have tested it).

Here is my code:

<cfspreadsheet action="read" src="spreadsheet.xlsx" name="sheet">
<cfoutput>
    #sheet.rowcount-3#
<cfloop from="2" to="#sheet.rowcount-3#" index="row">
    <cfquery datasource="Questions" result="rState">
        INSERT INTO States
        (
            State,
            StateAbbr
        )
        VALUES 
        (
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#SpreadsheetGetCellValue(sheet,row,1)#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#SpreadsheetGetCellValue(sheet,row,2)#">
        )
    </cfquery>
    #SpreadsheetGetCellValue(sheet,row,1)#<br />
    #SpreadsheetGetCellValue(sheet,row,2)#<br />
    <cfloop from="3" to="15" index="col"> <!--- multi row selection (edit based on excel sheet col relationship) --->
        <cfif SpreadsheetGetCellValue(sheet,row,col) EQ "">
            <cfset SpreadsheetSetCellValue(sheet,"N/A",row,col) />
        </cfif>
        <cfquery datasource="Questions" result="rResponse">
            IF NOT EXISTS 
            (
                SELECT Response
                FROM Responses
                WHERE Response=<cfqueryparam cfsqltype="cf_sql_nvarchar" value="#SpreadsheetGetCellValue(sheet,row,col)#">
            )
            INSERT INTO Responses
            (
                Response
            )
            VALUES
            (
                <cfqueryparam cfsqltype="cf_sql_nvarchar" value="#SpreadsheetGetCellValue(sheet,row,col)#">
            )
        </cfquery>
        #row#X#col#<br />
        #SpreadsheetGetCellValue(sheet,row,col)#<br />
    </cfloop>
</cfloop>
</cfoutput>

Edit: I cannot show the excel sheet I'm using, but I recreated the list so you know what sort of drop down cell I am talking about. NOTE: The sheet I'm using, the options list was NOT built with cells, the creator used static values in the validation clause!!!

Answer 1

I finally got a chance to test this, and the thread from the POI list was spot on. Just read in the file, extract the underlying POI sheet, then grab the list of validators from the sheet object. Once you have the list of validators, loop through it and extract the options for each one.

Each validator contains a "list" of allowed options, as well as the range(s) of any cells using that rule. Note: The cell ranges are represented as objects, so you must do a little parsing to get them into a usable format.

This example returns an array of structures. Each element represents a "LIST" validator, and contains the keys:

• list - Array containing the allowed options: ["dog", "cat", ...]
• cells - Array of cell ranges ie { startCell = A1, endCell = A10 }
  
  

Code:

 <cfscript>
    // read in file and grab POI sheet
    path     = "c:/path/to/file.xlsx";
    workbook = spreadSheetRead( path ).getWorkBook();
    poiSheet = workbook.getSheet("Sheet1");

    // extract all validators and types
    results = [];
    allRules = poiSheet.getDataValidations();
    ruleTypes = createObject("java", "org.apache.poi.ss.usermodel.DataValidationConstraint$ValidationType");

    // search all validators for "LIST" type    
    for (rule in allRules ) {

        // determine the rule type
        constraint = rule.getValidationConstraint();
        type = constraint.getValidationType();

        // if "LIST" type, grab the values and cell locations
        if (type == ruleTypes.LIST) {

            // convert address objects into strings
            ranges    = [];
            addresses = rule.getRegions().getCellRangeAddresses();

            for (i = 1; i < arrayLen(addresses); i++) {

                // extract start/end cell
                addrString = addresses[ i ];
                startCell   = listFirst( addrString, ":");  
                endCell     = listLast( addrString, ":");

                // store results
                arrayAppend( ranges, { startCell=startCell, endCell=endCell } );
            }

            // grab list values         
            values    = constraint.getExplicitListValues();

            // store results
            arrayAppend( results, { list=values, cells=ranges } );
        }
   }

   // display results   
   writeDump(results);
</cfscript>