导出 ▼

更多动作 ▼

欢迎进入Wiki » FAQ » Excel 数据导入示例?

Excel 数据导入示例?

在2014-11-06 11:31上被李小翔修改

评论 (0) · 附件 (4) · 记录 · 信息

Excel 数据导入常见需求

1、在 list 界面中，增加一个导入按钮，点击后可以下载 excel 模板，或者上传 excel 文件

2、文件上传后，进行数据校验，并显示预览界面；如果数据校验有错误，则在预览表单中显示错误消息，并不允许执行导入操作

3、如果数据校验通过，则预览界面中显示“导入”操作，点击后逐条或批量导入到系统中

下面以导入部门（Department）为例，导入模板见附件导入模板示例.xls。

上传文件界面：
1-上传文件.png

导入预览界面：
2-导入预览.png

导入结果界面：
3-导入结果.png

列表界面：list.gsp

导入按钮：

导入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"> </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 代码所示：

除了逐条导入外，还可以采用批量导入的方式（具体采用哪种，根据业务需求来定），如下面的 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> 对象。

标签： BroFramework

在2014-11-06 11:12上被李小翔创建