将Excel电子表格导入SQL数据库（Coldfusion 9）

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:

我正在编写一个脚本来将大型excel spreadhseet导入SQL Server 2008数据库。一切都很好，除了一个小细节：

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)

如果工作表中的单元格有多个选项（如下拉菜单），则只会抓取所选选项。我想抓住单元格所有可能的选项，而不仅仅是正在使用的选项（请参阅我的SQL查询，了解原因）

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.

我搜索了谷歌和S / O的答案，但我没有遇到这种特殊情况的解决方案。这是我正在使用的电子表格功能的链接。

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

我无法向您展示excel表，但可以安全地假设表格的遍历是正确的（我已对其进行了测试）。

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

编辑：我无法显示我正在使用的Excel工作表，但我重新创建了列表，因此您知道我正在谈论的是什么类型的下拉单元格。注意：我正在使用的工作表，选项列表不是使用单元格构建的，创建者在验证子句中使用了静态值！

http://oi41.tinypic.com/30svhuw.jpg

http://oi41.tinypic.com/30svhuw.jpg

1 个解决方案

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

我终于有机会测试了这个，POI列表中的线程就是现货。只需读入文件，提取基础POI表，然后从工作表对象中获取验证器列表。获得验证器列表后，循环验证并为每个验证器提取选项。

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”验证器，并包含键：

• list - Array containing the allowed options: ["dog", "cat", ...]
• list - 包含允许选项的数组：[“dog”，“cat”，...]
• cells - Array of cell ranges ie { startCell = A1, endCell = A10 }
  
  
• cells - 单元格范围的数组，即{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>