前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SpringBoot+EasyPOI操作Excel

SpringBoot+EasyPOI操作Excel

作者头像
默存
发布2022-06-17 17:32:06
5830
发布2022-06-17 17:32:06
举报
文章被收录于专栏:默存默存

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。

添加依赖

代码语言:javascript
复制
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-spring-boot-starter</artifactId>
  <version>3.3.0</version>
</dependency>

一、导出Excel

1、映射实体注解

代码语言:javascript
复制
/**
 * 路径:com.example.demo.entity
 * 类名:
 * 功能:使用easypoi导出excel
 * 备注:
 * 创建人:typ
 * 创建时间:2019/5/19 20:54
 * 修改人:
 * 修改备注:
 * 修改时间:
 */
@Data
public class BrandInfo implements Serializable{
 
    @Excel(name = "brandGuid", width = 25,orderNum = "0")
    private String brandGuid;
 
    @Excel(name = "brandName", width = 25,orderNum = "0")
    private String brandName;
 
    @Excel(name = "ytFullcode", width = 25,orderNum = "0")
    private String ytFullcode;
 
    @Excel(name = "formatGuid", width = 25,orderNum = "0")
    private String formatGuid;
 
    @Excel(name = "flag", width = 25,orderNum = "0")
    private String flag;
 
    @Excel(name = "customerid", width = 25,orderNum = "0")
    private String customerid;
 
    @Excel(name = "createDatetime",width = 20,exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "1")
    private String createDatetime;
 
    @Excel(name = "updateDatetime",width = 20,exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "1")
    private String updateDatetime;
 
    @Excel(name = "source", width = 25,orderNum = "0")
    private Integer source;
 
}

2、查询数据

2.1、service接口

代码语言:javascript
复制
public interface ExcelService {
    List<BrandInfo> list();
}

2.2、service实现类

代码语言:javascript
复制
@Service
public class ExcelServiceImlp implements ExcelService {
 
    @Autowired
    private ExcelMapper excelMapper;
 
    @Override
    public List<BrandInfo> list() {
        return excelMapper.list();
    }
}

2.3、mapper接口

代码语言:javascript
复制
@Mapper
public interface ExcelMapper {
 
    List<BrandInfo> list();
}

2.4、mapper对应的xml

代码语言:javascript
复制
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
 
<mapper namespace="com.example.demo.mapper.ExcelMapper">
    
    <select id="list" resultType="com.example.demo.entity.BrandInfo">
        select brand_guid,brand_name,yt_fullcode,format_guid,flag,customerid,create_datetime,update_datetime,source from brand_info
    </select>
 
</mapper>

3、导出Controller

代码语言:javascript
复制
package com.example.demo.controller;
 
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.example.demo.entity.BrandInfo;
import com.example.demo.service.ExcelService;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
 
/**
 * 路径:com.example.demo.controller
 * 类名:EasyPoiExcelController
 * 功能:使用easypoi注解进行导入导出
 * 备注:
 * 创建人:typ
 * 创建时间:2019/5/19 20:00
 * 修改人:
 * 修改备注:
 * 修改时间:
 */
@RestController
@RequestMapping("/easypoi")
public class EasyPoiExcelController {
 
    private static final Logger log = LoggerFactory.getLogger(EasyPoiExcelController.class);
 
    @Autowired
    public ExcelService excelService;
 
    /**
     * 方法名:exportExcel
     * 功能:导出
     * 描述:
     * 创建人:typ
     * 创建时间:2019/5/19 20:03
     * 修改人:
     * 修改描述:
     * 修改时间:
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response){
        log.info("请求 exportExcel start ......");
 
        // 获取用户信息
        List<BrandInfo> list = excelService.list();
 
        try {
            // 设置响应输出的头类型及下载文件的默认名称
            String fileName = new String("demo信息表.xls".getBytes("utf-8"), "ISO-8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
 
            //导出
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), BrandInfo.class, list);
            workbook.write(response.getOutputStream());
            log.info("请求 exportExcel end ......");
        } catch (IOException e) {
            log.info("请求 exportExcel 异常:{}", e.getMessage());
        }
    }
}

5、浏览器请求 http://localhost:8081/easypoi/exportExcel,导出结果如下

二、导入Excel

这里只是简单的测试了一下,没有把数据往数据库存储

代码语言:javascript
复制
public static void main(String[] args) {
        try{
            // 没有使用实体类注解的形式,这里用的Map
            List<Map<String,Object>> list = ExcelImportUtil.importExcel(
                    new File(PoiPublicUtil.getWebRootPath("check.xls")),
                    Map.class,
                    new ImportParams()
            );
            // 数据打印
            for (Map<String, Object> map : list) {
                System.out.println(JSON.toJSON(map));
            }
        } catch (Exception e){
            log.info(" Excel 导入异常:{}", e.getMessage());
        }
}

Excel原数据如图

导入结果

代码语言:javascript
复制
DEBUG 2019-05-31 14:54:06,466 cn.afterturn.easypoi.excel.imports.ExcelImportServer: Excel import start ,class is interface java.util.Map
DEBUG 2019-05-31 14:54:06,811 cn.afterturn.easypoi.excel.imports.ExcelImportServer:  start to read excel by is ,startTime is 1559285646811
DEBUG 2019-05-31 14:54:06,812 cn.afterturn.easypoi.excel.imports.ExcelImportServer:  end to read excel by is ,endTime is 1559285646811
DEBUG 2019-05-31 14:54:06,837 cn.afterturn.easypoi.excel.imports.ExcelImportServer:  end to read excel list by pos ,endTime is 1559285646837
{"name":"zhangsan","password":123,"id":1,"sex":"男"}
{"name":"lisi","password":123456,"id":2,"sex":"男"}
{"name":"wangwu","password":10002,"id":3,"sex":"女"}
{"name":"zhaoliu","password":1587,"id":4,"sex":"男"}
{"name":"maqi","password":45987,"id":5,"sex":"女"}
{"name":"houjiu","password":23143,"id":6,"sex":"男"}
{"name":"jishi","password":4543645,"id":7,"sex":"男"}

三、解决不同浏览器导出excel中文名称乱码问题

在Windows上以上的导出都是正常,而在Max上导出时,文件名称包含中文时会乱码,只需添加一下代码就可以完美的解决名称乱码问题。

代码语言:javascript
复制
// 各浏览器基本都支持ISO编码
String userAgent = request.getHeader("User-Agent").toUpperCase();
if(userAgent.contains("TRIDENT") || userAgent.contains("EDGE")){
   fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
} else if(userAgent.contains("MSIE")) {
   fileName = new String(fileName.getBytes(), "ISO-8859-1");
} else {
   fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.addHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", fileName));
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-04-27,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 全栈客 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、导出Excel
  • 二、导入Excel
  • 三、解决不同浏览器导出excel中文名称乱码问题
相关产品与服务
云数据库 Redis
腾讯云数据库 Redis(TencentDB for Redis)是腾讯云打造的兼容 Redis 协议的缓存和存储服务。丰富的数据结构能帮助您完成不同类型的业务场景开发。支持主从热备,提供自动容灾切换、数据备份、故障迁移、实例监控、在线扩容、数据回档等全套的数据库服务。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档