current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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!!!

share|improve this question
    
(Edit) Not sure I follow. Why do you need options that were not selected? Or is the problem that the spreadsheet functions are not returning all of the selected options? AFAIK, there are no CF functions that can do this. This thread suggests there nothing built into POI either. At least for xls sheets. There are different types of lists in Excel, so we really need to see a sample. Can you upload a sanitized file or sample? We could play around with it and maybe someone could come up with a solution. –  Leigh Sep 17 '13 at 17:44
    
I edited my question with a small screenshot of the drop down style (It's not related to the data in the sheet I'm using, but it's the exact same style). Basically each cell I'm traversing has a "Response" so I'm importing each possible response with a unique ID so they can be re-used. –  Sterling Archer Sep 17 '13 at 18:13
    
If I am not mistaken the data values that are used for the list in Excel have to come from other Excel fields. These can be contained somewhere else in the same spreadsheet or another spreadsheet altogether. Where is the data stored for your list(s)? Can you access those cells via SpreadsheetGetCellValue() instead? –  Miguel-F Sep 17 '13 at 18:58
    
@Miguel-F - I think you are right, but the question is how to access the location info with CF. RUJordan - Maybe I am being obtuse, but you are already ensuring unique ID's with the IF NOT EXISTS... part. So I am still not clear about the answer to my earlier question: Why do you need options that were not selected? Or is the problem that the spreadsheet functions are not returning all of the selected options? –  Leigh Sep 17 '13 at 19:03
2  
@RUJordan - Haha, unfortunately there are a bunch of things you can only do with POI ;-) .. but at least its accessible. –  Leigh Sep 17 '13 at 19:33

1 Answer 1

up vote 3 down vote accepted
+50

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>
share|improve this answer
    
Inside of a <cfscript> could I set those variables using <cfset>? I just tested this as well, and it's perfect. Thank you so much, a well deserved bounty! (in 2 hours) –  Sterling Archer Sep 20 '13 at 14:02
    
Not inside it, no. But you could re-write it in cfml, if you prefer. When translating java, I tend to stick with cfscript because the syntax is nearly identical. But that is just personal preference. –  Leigh Sep 20 '13 at 14:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.