SpringMVC-Excel文件的导入

1.在页面中点击事件弹出模态框

/*文件的导入*/                                
$(".btn-import").click(function () {
     $("#xlsModal").modal("show");
 });

2.弹出模态框后点击保存进行提交

$("#editForm").ajaxForm(function (ret) {
if(ret.flag==true){
    $.messager.alert("温馨提示:",ret.msg);
    //window.location.reload();
}else{
    let fallEmpList=[];
    $(ret.data).each(function (i,e) {
        fallEmpList.push(e.name);
    });
    $.messager.alert("温馨提示:",ret.msg+"<br>"+fallEmpList);
}
});

3.模态框

<div class="modal fade" id="xlsModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="myModalLabel">导入</h4>
            </div>
            <div class="modal-body">
                <form class="form-horizontal" action="/employee/importXls.do" enctype="multipart/form-data" method="post" id="editForm">
                    <input type="hidden" name="id">
                    <div class="form-group" style="margin-top: 10px;">
                        <label for="name" class="col-sm-3 control-label"></label>
                        <div class="col-sm-6">
                            <input type="file" name="file" accept="application/vnd.ms-excel">
                        </div>
                    </div>
                    <div class="form-group" style="margin-top: 10px;">
                        <div class="col-sm-3"></div>
                        <div class="col-sm-6">
                            <a href="/xls/employeeTemplate.xls" class="btn btn-success" >
                                <span class="glyphicon glyphicon-download"></span> 下载模板
                            </a>
                        </div>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">取消</button>
                <button type="submit" form="editForm" class="btn btn-primary btn-submit">保存</button>
            </div>
        </div>
    </div>
</div>

4.controller

//文件的导入
    @RequestMapping("/importXls")
    @ResponseBody
    public ResultBean importXls(MultipartFile file) throws IOException {
        //1.读取文件
            //创建集合,分贝存储成功和失败的数据
        List<Employee> successList = new ArrayList<>();
        List<Employee> failList = new ArrayList<>();
            //读取excel文件到流中
        HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
        //2分析出来每一行数据
            //获取页
        HSSFSheet sheet = wb.getSheetAt(0);
            //获取总行数
        int rows = sheet.getPhysicalNumberOfRows();
            //遍历每一行,获取相关列的数据,并将对应的列的信息,
            // 添加到employee对象中,然后将employee对象添加到数据库中
        for (int i = 2; i < rows; i++) {
            //获取行
            HSSFRow row = sheet.getRow(i);
            Employee employee = new Employee();
            try {
            	//设置默认密码
                employee.setPassword("1");
                //设置姓名
                HSSFCell cell2 = row.getCell(2);
                employee.setName(cell2.getStringCellValue());
                //设置邮箱
                HSSFCell cell3 = row.getCell(3);
                employee.setEmail(cell3.getStringCellValue());
                //设置年龄
                HSSFCell cell4 = row.getCell(4);
                employee.setAge((int)cell4.getNumericCellValue());
                //设置超级管理员  true/false
                HSSFCell cell5 = row.getCell(5);
                employee.setAdmin("是".equals(cell5.getStringCellValue()));
                //设置部门名称
                HSSFCell cell6 = row.getCell(6);
                String departName = cell6.getStringCellValue();
                //设置新添加数据的部门
                Department department = departmentService.selectByName(departName);
                employee.setDept(department);
				//添加
                employeeService.save(employee);
                //3.封装到employee表格中,用于统计成功与失败的个数
                successList.add(employee);
            }catch (Exception e){
                e.printStackTrace();
                failList.add(employee);
            }
        }
        //关闭
        wb.close();
        //4.收集成功的个数
        if(failList.size()>0){
            return ResultBean.fail("导入失败,成功数量:"+successList.size()+",失败数量:"+failList.size(),failList);
        }else {
            return ResultBean.success("导入成功,导入数量"+successList.size());
        }
    }