EasyPOI实现导入导出详解

最新推荐文章于 2024-05-01 19:17:00 发布

Cohen 奕博

最新推荐文章于 2024-05-01 19:17:00 发布

阅读量4k

点赞数 5

分类专栏： springboot 文章标签： java

版权归Cohen所有

本文链接：https://blog.csdn.net/shallum/article/details/111061219

版权

springboot 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

EasyPOI导入导出Excel

基本操作

依赖

easypoi-base、easypoi-web、easypoi-annotation

相关注解

EasyPOI起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model-row,filed-col,这样利用注解很容易完成导入导出。

@ExcelTarget 用在类上,表示要导出为excel的目标对象,添加唯一标识,作用于最外层对象
@Excel 作用在属性上,name表示导出到哪个标题行,replace,format,orderNum,width,type=2是图片
@ExcelIgnore 作用在属性,表示忽略该字段导出
@ExcelEntity 作用对象类型的属性,表示继续深入导出的实体,
@ExcelCollection 作用在集合含对象类型的属性,表示一对多

导出代码实现

//1. 从数据库获取要导出的对象集合
List<User> userList = getUser();
//2. 导出excel
	//参数:1.exportParams 导出配置对象 参数2:导出类型 参数3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("生成文件名","SheetName"),User.class,userList);
//3. 将excel写入指定位置
FileOuteputStream outputStream = new FileOutputStream("F://url");
workbook.write(outputStream);
outputStream.close();
workbook.close;

导入代码实现

//参数1：导入excel文件流 参数2：导入映射对象类型 参数3：导入配置对象
ImportParams param = new ImportParams();
param.setTitleRows(1);  //标题占几行
param.setHeadRows(2);   //文件头占几行
//param.setStartSheetIndex(1); //从第二个工作表中读取
//param.setSheetNum(3); //读取三个 sheet
param.setImportFields(new String[]{"姓名","年龄"});//验证是否是我们需要得excel
List<Employee> emps = ExcelImportUtil.importExcel(new FileInputStream("F:\\Development Code\\eclipse_workspace\\Kevin_maven\\员工import.xls"), Employee.class, param);
emps.forEach(System.out::println);

整合Springboot

项目环境搭建

创建maven项目

导入相关依赖

<!--引入mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>
<!--引入mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>
<!--引入 druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.3</version>
</dependency>
<!--引入 thymeleaf-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!--引入easypoi依赖-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.3</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

配置端口及数据源和mapper位置

server:
  port: 8001
spring:
  application:
    name: test-kevin
  profiles:
    active: dev2

#dev2 中配置
#thymeleaf 缓存关闭
spring:
  thymeleaf:
    cache: false

#配置数据源
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.174.132:3306/test_2020?characterEncoding=utf-8
    username: root
    password: 123456
  #指定文件位置
  ##resources:
    #static-locations: classpath:/static/,file:${upload.dri}
#指定mapper位置
mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
  # 实体扫描，多个package用逗号或者分号分隔
  type-aliases-package: com.yc.poi.entity
  configuration:
    # 驼峰下划线转换
    map-underscore-to-camel-case: true
logging:
  level:
    root: info
    com.yc.poi.dao: debug

#下载图片地址
upload.dir: F:/Development Code/eclipse_workspace/Kevin_maven/src/main/resources/static/image

创建数据库(目标数据)和对应实体

建表sql

CREATE TABLE `t_employee` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `age` int(5) DEFAULT NULL COMMENT '年龄',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `cardNo` varchar(18) DEFAULT NULL COMMENT '身份证号码',
  `address` varchar(50) DEFAULT NULL COMMENT '籍贯',
  `status` varchar(4) DEFAULT NULL COMMENT '状态',
  `photo` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '头像',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

实体

@Data
@ExcelTarget("employees")
public class Employee {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "头像" ,type = 2,width = 8,height =2,savePath = "F:\\Development Code\\eclipse_workspace\\Kevin_maven\\src\\main\\resources\\static\\image")
    private String photo;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日",format = "yyyy-MM-dd",width = 15)
    private Date birthday;
    @Excel(name = "身份证号码",width = 18)
    private String cardNo;
    @Excel(name = "籍贯",width = 15)
    private String address;
    @Excel(name = "状态",replace = {"激活_0","未激活_1"})
    private String status;
}

Excel导入实现

编辑导入导出界面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>导入Excel主页面</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css"
    integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg320mUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
</head>
<body>
<div class="container-fluid">
    <div class="row">
        <div class="col-md-12">
            <h1>选择Excel文件导入到数据库中</h1>
            <form th:action="@{/empl/import}" method="post" enctype="multipart/form-data" class="form-inline">
                <div class="form-group">
                    <input class="form-control" type="file" name="excelFile"/>
                    <input type="submit" class="btn btn-danger" value="导入数据"/>
                </div>
            </form>
        </div>
        <div class="col-md-12">
            <h1>显示导入数据列表</h1>
            <table class="table table-bordered" >
                <tr>
                    <th>编号</th>
                    <th>头像</th>
                    <th>姓名</th>
                    <th>年龄</th>
                    <th>生日</th>
                    <th>身份证号码</th>
                    <th>籍贯</th>
                    <th>状态</th>
                </tr>
                <tr th:each="empl : ${empls}">
                    <td th:text="${empl.id}"></td>
                    <td><img th:src="${'/image/'+empl.photo}" style="height: 40px" alt=""></td>
                    <td th:text="${empl.name}"></td>
                    <td th:text="${empl.age}"></td>
                    <td th:text="${#dates.format(empl.birthday,'yyyy-MM-dd')}"></td>
                    <td th:text="${empl.cardNo}"></td>
                    <td th:text="${empl.address}"></td>
                    <td th:text="${empl.status}"></td>
                </tr>
            </table>
            <hr>
            <a th:href="@{/empl/export}"  class="btn btn-info">导出Excel文件</a>
        </div>
    </div>
</div>
</body>
</html>

查询数据

@Controller
@RequestMapping("/empl")
@Slf4j
public class EmployeeController {

    @Autowired
    private EmployeeService employeeService;

    @Value("${upload.dir}")
    private String realPath;
    /**
     * 查询所有员工信息
     * @param model
     * @return
     */
    @RequestMapping("findAll")
    public String findAll(Model model) {
        List<Employee> empls = employeeService.findAll();
        model.addAttribute("empls", empls);
        return "index";
    }
}

导入代码

/**
 * 导入Excel文件
 */
@RequestMapping("import")
public String importExcel(MultipartFile excelFile){
    log.info("文件名:[{}]",excelFile.getOriginalFilename());
    try {
        ImportParams param = new ImportParams();
        param.setTitleRows(1);
        param.setHeadRows(2);
        List<Employee> empls = ExcelImportUtil.importExcel(excelFile.getInputStream(), Employee.class, param);
        int i = employeeService.addEmpls(empls);
    } catch (Exception e) {
        e.printStackTrace();
        log.error("导入失败");
    }
    return  "redirect:/empl/findAll";//上传完成重定向到查询所有路径
}

Excel导出实现

/**
 * 导出Excel文件
 */
@RequestMapping("/export")
public void ExportExcel(HttpServletResponse response){
    List<Employee> empls = employeeService.findAll();
    //设置图片下载路径
    empls.forEach(empl ->{
        empl.setPhoto(realPath+'/'+empl.getPhoto());
    });
    log.info("导出当前excel数据为：[{}]",empls.size());
    try {
        //将数据对象导出为工作簿
        Workbook sheets = ExcelExportUtil.exportExcel(new ExportParams("导出测试员工目录", "测试工作薄"), Employee.class, empls);
        //设置请求头
        response.setHeader("content-disposition","attachment;fileName="+ URLEncoder.encode("员工表.xls","UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        sheets.write(outputStream);
        outputStream.close();
        sheets.close();
    } catch (IOException e) {
        e.printStackTrace();
        log.error("导出失败");
    }
}

帮助到您是我的荣幸，不喜勿喷
创作不易，希望尊重

Cohen 奕博

关注

5
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
EasyPOI实现导入导出详解

EasyPOI导入导出Excel基本操作依赖easypoi-base、easypoi-web、easypoi-annotation相关注解EasyPOI起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model-row,filed-col,这样利用注解很容易完成导入导出。@ExcelTarget 用在类上,表示要导出为excel的目标对象,添加唯一标识,作用于最外层对象@Excel 作用在属性上,name表示导出到哪个标题行,replace,format,order
复制链接

扫一扫