智能销售系统导入导出

导入导出

操作办公软件的两个技术
1.JXL(只支持Excel，内存优化)
2.POI（功能强大，支持广泛）

EasyPOI的使用

导包，easyPOI的包和POI的包冲突，要删除POI的包

准备domain

@ExcelTarget("emp")
public class PoiEmployee {
    @Excel(name = "名称")
    private String name;
    @Excel(name="邮件",width = 25)
    private String email;
    @Excel(name="年龄")
    private Integer age;
    @Excel(name="性别",replace = {"男_true","女_false"})
    private Boolean sex;
    @Excel(name="出生日期",format = "yyyy-MM-dd")
    private Date bornDate = new Date();
    //type=2:代表这是一张图片
    @Excel(name = "头像",type = 2,height = 25)
    private String headImage;
    @ExcelEntity
    private PoiDepartment department;
}

@ExcelTarget("dept")
public class PoiDepartment {

    @Excel(name="部门名称_emp,名称_dept")
    private String name;

    @Excel(name="部门地址_emp,地址_dept")
    private String address;

    @Excel(name="邮件_dept")
    private String email;
}

EasyPOI创建Excel文件

/**
list的值自己准备一下
 * new ExportParams(title,sheetName):导出的属性设置
    title:表头名称 
    sheetName：sheet表的名称 
 * PoiEmployee .class：导出的实体类型
 * list:导出的数据（List<PoiEmployee>）
 */
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("部门名称","bbb"),
        PoiEmployee.class, list);
FileOutputStream fos = new FileOutputStream("poidept.xlsx");
workbook.write(fos);
fos.close();

EasyPOI读取Excel文件

ImportParams params = new ImportParams();
    //params.setTitleRows(1);
    params.setHeadRows(1);

    List<PoiEmployee> list = ExcelImportUtil.importExcel(
            new File("poiemp.xlsx"),
            PoiEmployee.class, params);

    list.forEach(e -> {
        System.out.println(e+","+e.getDepartment());
    });

EasyPOI集成SpringMVC完成导入导出

扫描view
<context:component-scan base-package="cn.afterturn.easypoi.view" />配置视图解析器
p:order=“0” 先找这个bean的解析，再找其它的

<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0" />

前台传入相应的数据

 <form id="searchForm" method="post" action="/employee/download">
    用户名: <input name="username" class="easyui-textbox" style="width:80px">
    邮件: <input name="email" class="easyui-textbox" style="width:80px">
    部门:
    <input name="departmentId"  class="easyui-combobox"
           data-options="panelHeight:'auto',valueField:'id',textField:'name',url:'/util/deptlist'" />
    <a href="javascript:;" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
    <!-- button不写type,它就是提交 -->
    <button class="easyui-linkbutton" iconCls="icon-redo">导出</button>
</form>

后台接收参数进行导出
头像路径必须是真实路径

@RequestMapping("/download")
public String download(ModelMap map, HttpServletRequest request,EmployeeQuery query){
    //拿到所有员工
    List<Employee> employeeList = employeeService.findByQuery(query);
    //拿到当前项目的路径
    String realPath = request.getServletContext().getRealPath("");
    //修改(拼接)员工头像的路径
    employeeList.forEach(e -> {
        e.setHeadImage(realPath+e.getHeadImage());
    });

    ExportParams params = new ExportParams("员工信息", "列表", ExcelType.XSSF);
    params.setFreezeCol(5);

    map.put(NormalExcelConstants.DATA_LIST, employeeList); // 数据集合
    map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
    map.put(NormalExcelConstants.PARAMS, params);//参数
    map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称
    //easypoiExcelView
    return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}

导入功能

访问页面

@RequestMapping("/import")
public class ImportController {

    @RequestMapping("/index")
    public String index(){
        return "import";
    }
}

加一个导入的按钮

<!-- 注意:上传需要加enctype -->
<form method="post" action="/import/empXlsx" enctype="multipart/form-data">
    <input class="easyui-filebox" name="empFile" data-options="prompt:'选择一个excel文件..'" style="width:80%">
    <button class="easyui-linkbutton">导入</button>
</form

导入功能的实现

@RequestMapping("/empXlsx")
public String empXlsx(MultipartFile empFile) throws Exception{
//准备一些导入的参数
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    //把上传的excel文件中的数据变成Employee
    List<Employee> list = ExcelImportUtil.importExcel(
            empFile.getInputStream(),
            Employee.class, params);
    //把员工进行保存
    list.forEach(e -> {
        //设置一个默认密码
        e.setPassword("123456");
        //根据名称到数据库中拿到部门
        Department department = e.getDepartment();
        if(department!=null){
            Department dbDept =  departmentService.findByName(department.getName());
            e.setDepartment(dbDept);
        }
        employeeService.save(e);
    });
    return "import";
}

导入功能的验证
加入验证的注解

@Excel(name = "用户名")
@NotNull(message = "用户名不能为空")
private String username;
private String password;
@Excel(name = "邮箱",width = 25)
private String email;

@Excel(name = "年龄")
@Max(value=80,message = "年龄不能超过80岁")
@Min(value = 20,message = "年龄不能小于20岁")
private Integer age;

@Excel(name="头像",type = 2,height = 25)
private String headImage; //头像

//jpa在管理的懒加载对象
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="department_id")
@ExcelEntity
private Department department;

导入功能修改

要验证，必需设置 params.setNeedVerfiy(true);
自定义验证:params.setVerifyHandler(employeeExcelVerifyHandler);
result.getList(); 拿到所有通过验证的数据
result.getFailList():拿到所有错误的数据
result.getFailWorkbook();拿到错误的文本

@Controller
@RequestMapping("/import")
public class ImportController {

   @Autowired
   private IEmployeeService employeeService;
   @Autowired
   private IDepartmentService departmentService;
   @Autowired
   private EmployeeExcelVerifyHandler employeeExcelVerifyHandler;

   @RequestMapping("/index")
   public String index(){
       return "import";
   }
   @RequestMapping("/employeeXlsx")
   public String employeeXlsx(MultipartFile empFile, HttpServletResponse response)throws Exception{
       //设置导入的参数
       ImportParams params = new ImportParams();
       params.setNeedVerfiy(true);//代表这里需要验证
       params.setHeadRows(1);
       //加入自定义验证
       params.setVerifyHandler(employeeExcelVerifyHandler);

       ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
               empFile.getInputStream(),
               Employee.class, params);
       //拿到正确的数据，然后把它们保存到数据库
       List<Employee> list = result.getList();
       list.forEach(e->{
           System.out.println("正确的数据"+e);
       });
       List<Employee> failList = result.getFailList();
       failList.forEach(e->{
           System.out.println("错误的数据"+e);
       });

       list.forEach(e ->{
           //从数据库中查询部门
           String deptName = e.getDepartment().getName();
           Department dept = departmentService.findByName(deptName);
           e.setDepartment(dept);
           //设置初始密码
           e.setPassword("123");
           employeeService.save(e);
       });
       Workbook failWorkbook = result.getFailWorkbook();
       //设置响应的文件类型
       response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
       //attachment:不要用浏览器打开(下载)
       response.setHeader("Content-disposition", "attachment;filename=empFail.xlsx");
       response.setHeader("Pragma", "No-cache");//设置不要缓存
       OutputStream ouputStream = response.getOutputStream();
       failWorkbook.write(ouputStream);
       ouputStream.flush();
       ouputStream.close();
       return "import";
   }
}