使用EasyPoi导入导出

程序员文章站 2024-03-20 23:02:10

...

1.EasyPOI简介

1.1 easypoi是什么
easypoi 就是对poi进行封装，主打简单，do less do more ，就算不懂底层的poi的操作
，也可以操作导入导出，而且提供的功能在项目里够用

1.2 easypoi的特点
1.设计精巧,使用简单
2.接口丰富,扩展简单
3.默认值多,write less do more
4.spring mvc支持,web导出可以简单明了

2.EasyPoi简单使用

2.1导入jar包

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-base</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-web</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-annotation</artifactId>
  <version>3.2.0</version>
</dependency>

2.2 测试
1.准备一个类,加上注解@Excel
处理boolean 可以处理图片类型可以日期类型可以处理关联对象

@ExcelTarget("emp")
public class EasyEmployee extends BaseDomain{
    @Excel(name = "员工姓名")
    private String name;
    @Excel(name="邮件",width = 20)
    private String email;
    //性别
    @Excel(name="性别",replace={"男_true","女_false"})
    private boolean sex;
    @Excel(name="年纪")
    private Integer age;
    //出生日期
    @Excel(name="生日",format = "yyyy-MM-dd")
    private Date birthday;
    @ExcelEntity
    private EasyDept dept;
    @Excel(name="头像",type = 2)
    private String headImage;
}

2.调用方法，导入导出

//导入
List<EasyEmployee> easyEmployees = ExcelImportUtil.importExcel(file, EasyEmployee.class, importParams);
//导出方法
 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), EasyDept.class, list);

3.EasyPoi项目导出

3.1 导入jar包

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

3.2 页面上显示导出功能

 <button  class="easyui-linkbutton" iconCls="icon-redo">导出</button>

3.2 注解扫描

    <!-- easypoi的一些view：视图-->
    <context:component-scan base-package="cn.afterturn.easypoi.view"/>
    <context:component-scan base-package="cn.itsource.aisell.common"/>
    <!-- bean的视图解析器,p:order = "0"表示在最前面 -->
    <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver"
          p:order="0"/>

3.3 Controller层的处理


    //下载download
    @RequestMapping("/download")
    public String download(EmployeeQuery employeeQuery, ModelMap map, HttpServletRequest request){
        //根据查询，查询数据
        List<Employee> employees = employeeService.findByQuery(employeeQuery);
        //E:\JavaEEIdeacode\aisell\src\main\web/images/head/avatar1.jpg
        String realPath = request.getServletContext().getRealPath("");
        employees.forEach(e->{
            e.setHeadImage(realPath+e.getHeadImage());
        });
        //导出参数设置
        ExportParams params = new ExportParams("员工数据", "员工表格", ExcelType.XSSF);

        //固定两列的意思
        params.setFreezeCol(2);
        map.put(NormalExcelConstants.DATA_LIST, employees); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "xxx");//文件名称
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称

    }

4.EasyPoi的导入

4.1 准备一个导入页面.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <%@include file="/WEB-INF/head.jsp" %>
</head>
<body>
<!-- 上传必需是:post，enctype="multipart/form-data"-->
<form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
    <input class="easyui-filebox" name="empFile" style="width:80%"
           data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
    <button class="easyui-linkbutton">导入</button>
</form>
</body>
</html>

4.2 点击导入提交到后台进行导入处理
a)设置导入参数 --开启验证功能开启自定义验证

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;


    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
        //检查用户名是否重复的方法
        boolean flag = employeeService.checkUserName(employee.getUsername());
        //检查是否重复
        if (!flag){
            result.setSuccess(false);
            result.setMsg("用户名有重复");
        }

        return result;
    }
}

4.3 导入的验证
JSR 303(J2EE) 规范的导包

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-validator</artifactId>
  <version>5.2.4.Final</version>
</dependency>

在employee上加上相应的注解

  @Excel(name = "用户名")
  @NotNull(message = "用户名不为空")
  private String username;

   @Excel(name = "年纪")
   @Max(value = 80,message = "年龄不能超过80")
   private Integer age;

	@ExcelEntity
 	private Department department;

department上也要加注解

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

对于成功的结果存入数据库
对于失败的结果导出到前台展示

package cn.itsource.aisell.web.controller;


import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.itsource.aisell.common.EmployeeExcelVerifyHandler;
import cn.itsource.aisell.domain.Department;
import cn.itsource.aisell.domain.Employee;
import cn.itsource.aisell.service.IDepartmentService;
import cn.itsource.aisell.service.IEmployeeService;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.awt.image.MultiPixelPackedSampleModel;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@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 importParams = new ImportParams();
        importParams.setTitleRows(1);
        //开启验证--是否是null，最大值
        importParams.setNeedVerfiy(true);
        //加入自定义验证方法
        importParams.setVerifyHandler(employeeExcelVerifyHandler);

        //导入并且验证功能
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(empFile.getInputStream(),
                Employee.class, importParams);

        result.getFailList().forEach(e-> {
            System.out.println("失败数据" + e);
        });

        //导入数据保存到数据库
        result.getList().forEach(e->{
            System.out.println(e);
            //初始密码
            e.setPassword("123456");
            //根据部门名称，查部门的id
            Department byName = departmentService.findByName(e.getDepartment().getName());
            e.setDepartment(byName);
            //存入数据库
            employeeService.save(e);
        });

        //把验证失败两个数据 导出到前台 error.xlsx
        if(result.isVerfiyFail()){
            //如果验证失败，代码到这里面来
            Workbook workbook = result.getFailWorkbook();
            //把这个文件导出
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
            response.setHeader("Content-disposition", "attachment;filename=error.xlsx");
            response.setHeader("Pragma", "No-cache");//设置不要缓存
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }

        return "import";
    }


}

4.4 自定义验证

@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {

    @Autowired
    private IEmployeeService employeeService;


    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
        //检查用户名是否重复的方法
        boolean flag = employeeService.checkUserName(employee.getUsername());
        //检查是否重复
        if (!flag){
            result.setSuccess(false);
            result.setMsg("用户名有重复");
        }

        return result;
    }
}

上一篇： spring cloud+docker 部署

下一篇： Apache POI组件操作Excel，制作报表（二）

使用EasyPoi导入导出

1.EasyPOI简介

2.EasyPoi简单使用

3.EasyPoi项目导出

4.EasyPoi的导入

使用EasyPoi导入导出

Excel导入导出工具类

使用java在后台将数据导出为excel文件

js使用blob导出excel,兼容IE

Julia导入和导出数据（CSV、TXT、JLD）

使用js将表格数据导出成excel文件（xlsx，xlsx-style）

MySQL导入导出txt(csv)

利用EasyExcel将数据导出或导入成Excel格式文件

php 代码实现导入导出 mysql数据库

thinkPHP5.0如何实现excel导入导出？（代码示例）