Excel 数据导入示例?
李小翔
2014-11-06 11:31

内容列表
Excel 数据导入常见需求 	 # 
列表界面：list.gsp 	 # 
导入设置界面：importSetup.gsp 	 # 
控制器Action 	 # 
导入校验：parseExcel 	 # 
导入预览界面：importPreview.gsp 	 # 

Excel 数据导入常见需求
1、在 list 界面中，增加一个导入按钮，点击后可以下载 excel 模板，或者上传 excel 文件
2、文件上传后，进行数据校验，并显示预览界面；如果数据校验有错误，则在预览表单中显示错误消息，并不允许执行导入操作
3、如果数据校验通过，则预览界面中显示“导入”操作，点击后逐条或批量导入到系统中
下面以导入部门（Department）为例，导入模板见附件导入模板示例.xls。
上传文件界面：
导入预览界面：
导入结果界面：

列表界面：list.gsp
导入按钮：
<span class=”menuButton”><a class=”excel_add” href=”javascript: importDepartments();void(0)”>导入</a></span>
导入javascript：
function importDepartments() { 
    $j.formDialog({ 
        modal:true, title:”导入”, width:420, resizable:false,
        id: ”dlgImportDepartments”, url: ”${ createLink(action:’importDepartments’)} ”,
      ”submit.close”: true, ”submit.reload”: false,
      ’buttons.OK’: ”预览”, ’submit.ajax’: false
    } )
} 
这里设置了一个 url，加载导入的 dialog 页面。

导入设置界面：importSetup.gsp
<%@ page contentType=”text/html;charset=UTF-8” %>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8” />
<meta name=”layout” content=”blank” />
<div class=”dialog”>
   <g:form method=”post” action=”importDepartments” target=”_blank”>
       <g:hiddenField name=”command” value=”preview” />
       <table>
           <tr class=”prop”>
               <td class=”name”>选择文件:</td>
               <td class=”value”><input type=”file” name=”file” required=”” /></td>
           </tr>
           
           <tr class=”prop”>
               <td class=”name”>导入模板:</td>
               <td class=”value”>
                   <span class=”menuButton”><g:link class=”excel” action=”importDepartments”
                       params=”[’command’:’template’, ‘_’:System.currentTimeMillis()]” >导入模版下载</g:link></span>
               </td>
           </tr>
           <tr class=”prop”>
               <td class=”name”>导入说明:</td>
               <td class=”value”>
                   <div class=”help”>
                        必须先创建公司信息，再导入部门。
                   </div>
               </td>
           </tr>
       </table>
   </g:form>
</div>
你可能会发现这里下载导入模板的 action 也是 importDepartments，和本导入dialog页面是一个，区别在于多了一个 command 参数；并且 g:form 标签的 target 为 _blank，也就是点击预览按钮时，将会打开一个新窗口。
此外，设置界面中还可以加入一些其他和导入数据相关的参数，比如常见的导入属主、时间等。

控制器Action
/**
 * 导入预览与确认
 */
def importDepartments() { 
   // 逐条导入数据
    if ( params.command == ”import” ) { 
        Map data = JSON.parse(params.data)
        renderAjaxMessage( [error: importDept(data)] )
   } 
   // 导入预览
    else if ( params.command == ”preview” ) { 
        List<Map> result = parseExcel(request)
       if ( result.first()?.error ) { 
            renderError( result.error )
       }  else { 
            render( view:”${ VIEW_PATH} importPreview”, model: [result: result] )
       } 
   } 
   // 下载导入模板
    else if ( params.command == ”template” ) { 
        String path = ServletUtils.getServerURL() + ”templates/erp/base/Department/import.xls”
        path += ”?_=” + System.currentTimeMillis()
        response.sendRedirect( path )
   } 
   // 显示导入表单
    else { 
        render( view:”${ VIEW_PATH} importSetup” )
   } 
} 
能看出来，显示导入表单、下载导入模板、导入预览、导入数据等全部在一个 action 里，通过参数 command 区分。
这里需要注意导入模板的下载地址：示例中将导入模板放在 web-app/templates/.... 下，一般情况下（尤其是下载模板的同时还需要对模板进行修改的时候）放在工程的 resources/template 下，并且按照 Domain 类全名建立文件结构，这样的话，下载模板的代码示例为
// 打开Excel模板文件，并写入数据...
String filename = settingService.get(”bropen.framework.resource.template.basedir”) + VIEW_PATH + ”import.xls”
Workbook wb = MSExcelUtils.openWorkbook( filename )
......
// 写入 response
attachmentService.writeResponse(request, response, { 
    wb.write( response.getOutputStream() )
} , ”导入模板.xls”, ”application/vnd.ms-excel”)
// 或者直接下载模板
String filename = ......
attachmentService.writeResponse(request, response,
   new FileInputStream(new File(filename)), ”导入模板.xls”, ”application/vnd.ms-excel”)

导入校验：parseExcel
上面的action中，导入预览时会先执行一个 parseExcel 的方法，该私有方法中将解析 excel，并进行数据校验，代码示例如下。
/**
 * 解析导入的Excel：
 * 如果解析错误，则返回的列表的第一个Map元素会包含一个 error 值
 */
private List<Map> parseExcel( request ) { 
   def result = MSExcelUtils.readAll( request )
   if ( result.error ) return [result]    // 解析错误
    List<List> rows = result.values().first()
   
   // 解析 Excel
    result = []
    List<String> EXCEL_HEADERS = [’company’, ’fullName’, ’costType’, ’sequence’]
   for ( int i=1; i<rows.size(); i++ ) { 
        List cells = rows[i]
        Map ent = [i: i, errors:[:]]
       for ( int j=0; j<EXCEL_HEADERS.size(); j++ ) { 
            ent.put( EXCEL_HEADERS[j], (cells[j] instanceof String ? cells[j].trim() : cells[j]) )
       } 
       if ( ent.’fullName’ ) { 
            result << ent
       } 
   } 
    result = CollectionUtils.sort(result, ’asc’, ’fullName’)
   
   // 数据校验
    List<String> fullNames = []
   for ( Map ent in result ) { 
       if ( !ent.’company’ ) { 
            ent.errors.put(’company’, ’所属公司不能为空！’)
       }  else if ( !Company.findActiveByName(ent.’company’) ) { 
            ent.errors.put(’company’, ’所属公司不存在或已被禁用！’)
       } 
       if ( ent.’company’ ) { 
            String fullName = ent.’company’ + ”/” + ent.’fullName’
            fullNames << fullName
           if ( Department.findByFullName(fullName) ) { 
                ent.errors.put(’fullName’, ’部门已存在！’)
           }  else { 
                String parentFullName = fullName.replaceAll(/(.+)\ /[^\ /]+$/, ”\ $1”)
               if ( !fullNames.contains(parentFullName) && !Department.findByFullName(parentFullName) ) { 
                    ent.errors.put(’fullName’, ’上级部门不存在！’)
               } 
           } 
       } 
       if ( !ent.”costType” ) { 
            ent.errors.put(’costTyp’, ’费用类型不能为空！’)
       }  else if ( !Department.constraints.costType.inList.contains(ent.”costType”) ) { 
            ent.errors.put(’costTyp’, ’费用类型不正确！’)
       } 
   } 
   return result
} 
上面的示例中，调用的 MSExcelUtils 的 API 来解析 excel 文件，并返回一个 Map<String, List>，其中 key 为每个 Sheet 的名称，value 为一个代表行列（单元格）的值的二维列表。
随后根据 excel 中每列所代表的属性，将其重建为一个 List<Map> 对象，每个 Map 的格式为 [i: 行号, errors:[:], 属性1:xx, 属性2:xx]，其中 errors 为保存校验错误信息的 Map；此外，这里硬编码每列的属性为 EXCEL_HEADERS，其实更为灵活的做法是在 Excel 导入模板中添加一个隐藏的行，在该行的每个单元格中写上对应的属性名称，这样如果仅仅是模板发生小的变化，不需要修改控制器中的代码。
数据重构完成后，进行数据校验，并且将错误信息保存在列表每个Map对象的 errors 元素中。

导入预览界面：importPreview.gsp
数据校验完成后，会渲染导入预览的 gsp 页面。
<%@ page contentType=”text/html;charset=UTF-8” %>
<% boolean hasErrors = false; %>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8”/>
<meta name=”layout” content=”${ setting(code:’view.layout.form’)} ” />
<title>导入预览</title>
<style>
    td.error {  color: red } 
    td {  white-space: nowrap } 
</style>
</head>
<body><div class=”body” style=”width:90%”>
   <h1>导入预览</h1>
   <div class=”list”>
       <table>
           <tr>
               <th width=”20”>&nbsp;</th>
               <th>行号</th>
               <th><g:message code=”bropen.erp.base.Department.company” default=”Company” /></th>
               <th><g:message code=”bropen.erp.base.Department.name” default=”Full Name” /></th>
               <th><g:message code=”bropen.erp.base.Department.costType” default=”Cost Type” /></th>
               <th><g:message code=”bropen.erp.base.Department.sequence” default=”Sequence” /></th>
           </tr>
           <g:each in=”${ CollectionUtils.sort(result,’asc’,’i’)} ” var=”ent” status=”i”>
           <% if ( ent.errors ) hasErrors = true %>
           <tr class=”${ (i % 2) == 0 ? ‘odd’ : ‘even’}  entity_${ ent.i} ”>
               <td>
                   <img src=”${ fam.icon(name:’accept’)} ” style=”display:none” class=”success” />
                   <img src=”${ fam.icon(name:’exclamation’)} ” style=”display:none”  class=”failed” />
               </td>
               <td>${ ent.i + 1} </td>
               <td title=”${ ent.errors.company ?: ‘’} ” class=”${ ent.errors.company ? ‘error’ : ‘’} ”>${ ent.company?:”*”} </td>
               <td title=”${ ent.errors.fullName ?: ‘’} ” class=”${ ent.errors.fullName ? ‘error’ : ‘’} ”>${ ent.fullName?:”*”} </td>
               <td title=”${ ent.errors.costType ?: ‘’} ” class=”${ ent.errors.costType ? ‘error’ : ‘’} ”>${ ent.costType?:”*”} </td>
               <td title=”${ ent.errors.sequence ?: ‘’} ” class=”${ ent.errors.sequence ? ‘error’ : ‘’} ”>${ ent.sequence} </td>
           </tr>
           </g:each>
       </table>
   </div>
   <g:if test=”${ !hasErrors} ”>
       <div class=”buttons”>
           <span class=”button”><input type=”button” value=”导入” class=”save” onclick=”importDepartments()” /></span>
       </div>
   </g:if>
</div></body>
</html>
导入预览界面中，仍然使用 form 模板，但是 body 部分和 list 模板类似。
预览列表中，第一列预留为两个图标，其他列会根据数据校验情况设置 title 和 class 属性。
如果没有校验错误的话，列表最下面会显示出一个导入按钮，点击后执行导入方法：
/**
 * 逐条导入
 */
function importDepartments() { 
    $j(”div.buttons”).hide();
    showProcessingDlg(function(){ 
       var entities = ${ CollectionUtils.sort(result, ’asc’, ’i’).encodeAsJSON()} ;
       for ( var i=0; i<entities.length; i++ ) { 
           var entity = entities[i];
           delete entity.errors;
           var data = JSON.stringify( entity );
            $j.ajaxSetup( { async:false, alertMsg:false, alertErr:false}  );
            $j.post( ”${ createLink(action:’importDepartments’)} ”, { data:data, command:’import’} , function(result) { 
               if ( result.error ) { 
                    $j(”tr.entity_” + entity.i + ” img.failed”).show().attr(”title”, result.error);
                }  else { 
                    $j(”tr.entity_” + entity.i + ” img.success”).show().attr(”title”, ”导入成功!”);
                } 
            } )
        } 
    } );
   return false;
} 
上面的 js 方法中，采用的是逐条导入的方式，每导入一条就将对应行的第一个单元格中的图标显示出来，表示导入成功或失败。
导入的 ajax url 仍然调用同一个 action，并将 command 设为 import，回看前面的 action，当 command 为导入时，会调用一个 importDept 的私有方法，代码如下所示，无非就是 new 一个对象并设置属性，如果保存失败则返回错误消息：
/**
 * 导入一条数据
 * @return 如有错误，则返回消息；否则返回 null
 */
@grails.transaction.Transactional
private String importDept( Map ent ) { 
    Department dept = new Department()
    dept.company = Company.findActiveByName(ent.’company’)
    dept.parent = Department.findByFullName(ent.’company’ + ”/” + ent.’fullName’.replaceAll(/(.+)\ /[^\ /]+$/, ”\ $1”))
    dept.name = ent.’fullName’.replaceAll(/.+\ /([^\ /]+)$/, ”\ $1”)
    dept.costType = ent.’costType’
    dept.sequence = ent.sequence
   if ( !dept.sequence ) dept.sequence = 10
    dept.save(flush: true)
   if ( dept.hasErrors() ) { 
       return GrailsUtils.errorMessages(dept).join(”\ n”)
   }  else { 
       return null
   } 
} 
除此以外，也可以直接调用控制器脚手架中的 save、update 方法来逐条导入，前提是在导入校验时，先行计算老对象的id，如下面的 js 代码所示：
function importPayslips() { 
    $j(”div.buttons”).hide();
    showProcessingDlg(function(){ 
       var urlSave = ”${ createLink(action:’save’)} ”,
            urlUpdate = ”${ createLink(action:’update’)} ”,
            entities = ${ data.encodeAsJSON()} ;
       for ( var i=0; i<entities.length; i++ ) { 
           var entity = entities[i];
            $j.ajaxSetup( { async:false, alertMsg:false, alertErr:false}  );
            $j.post( (entity.id ? urlUpdate : urlSave), $j.param(entity, true), function(result) { 
               if ( result.error ) { 
                    $j(”tr.entity_” + entity.i + ” img.failed”).show().attr(”title”, result.error);
                }  else { 
                    $j(”tr.entity_” + entity.i + ” img.success”).show().attr(”title”, ”导入成功!”);
                } 
            } )
        } 
    } );
} 
除了逐条导入外，还可以采用批量导入的方式（具体采用哪种，根据业务需求来定），如下面的 js 代码所示：
/**
 * 一次性批量导入
 */
function importPayrevises() { 
    $j(”div.buttons”).hide();
    showProcessingDlg(function(){ 
       var entities = ’${ CollectionUtils.sort(result, ‘asc’, ‘i’).encodeAsJSON().toString().replace(”’”, “\ \ ’”)} ’;
        $j.ajaxSetup( { async:false, alertMsg:false, alertErr:false}  );
        $j.post( ”${ createLink(action:’importPayrevises’)} ”,
            { entities: entities, command: ’import’} ,
           function(result) { 
               if ( result.errors && result.errors.length ) { 
                   for ( var i=0; i<result.errors.length; i++ ) { 
                       var err = result.errors[i];
                        $j(”tr.entity_” + err.i + ” img.failed”).show().attr(”title”, err.error);
                    } 
                }  else { 
                    $j(”img.success”).show().attr(”title”, ”导入成功!”);
                } 
            } 
        )
    } );
} 
而批量导入的 action 中，将返回 json 格式，形如 [[i: 行号, error: 错误消息], ...] 的 List<Map> 对象。