将Excel电子表格导入SQL数据库(Coldfusion 9)

Question

我正在编写一个脚本，将一个大型excel电子表格集导入Server 2008数据库。除了一个小细节外，一切都很好：

如果工作表中的单元格有多个选项(如<select>下拉菜单)，则只有选定的选项才会被抓取。我想获取单元格所拥有的所有可能选项，而不仅仅是正在使用的选项(请参阅我的SQL查询以了解原因)。

我已经搜索了谷歌和S/O的答案，但我没有遇到解决这个特殊情况的解决方案。这里有一个链接到我正在使用的电子表格函数。

我不能向您展示excel工作表，但是可以安全地假定工作表的遍历是正确的(我已经测试过它)。

这是我的代码：

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

编辑:我不能显示我正在使用的excel工作表，但是我重新创建了列表，这样您就知道我说的是什么样的下拉单元格。注意:我使用的工作表，选项列表不是用单元格构建的，创建者在验证子句中使用了静态值！

​

​

Answer 1

我终于有机会对此进行测试，而POI列表中的线程也很在行。只需读取文件，提取基础POI工作表，然后从sheet对象获取验证器列表。一旦您有了验证器列表，就循环遍历它，并提取每个验证器的选项。

每个验证器都包含一个允许选项的“列表”，以及使用该规则的任何单元格的范围。注意:单元格范围表示为对象，因此您必须进行一些解析才能将它们转换成可用的格式。

此示例返回一个结构数组。每个元素表示一个“列表”验证器，并包含以下键：

• 列表-数组包含允许的选项：["dog", "cat", ...]
• 细胞-细胞范围阵列(即{ startCell = A1, endCell = A10 } )

代码：

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