SpringBoot+EasyPOI操作Excel

默存

发布于 2022-06-17 17:32:06

5860

发布于 2022-06-17 17:32:06

文章被收录于专栏：默存默存

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

添加依赖

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-spring-boot-starter</artifactId>
  <version>3.3.0</version>
</dependency>

一、导出Excel

1、映射实体注解

/**
 * 路径：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接口

public interface ExcelService {
    List<BrandInfo> list();
}

2.2、service实现类

@Service
public class ExcelServiceImlp implements ExcelService {
 
    @Autowired
    private ExcelMapper excelMapper;
 
    @Override
    public List<BrandInfo> list() {
        return excelMapper.list();
    }
}

2.3、mapper接口

@Mapper
public interface ExcelMapper {
 
    List<BrandInfo> list();
}

2.4、mapper对应的xml

<?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

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

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

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原数据如图

导入结果

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上导出时，文件名称包含中文时会乱码，只需添加一下代码就可以完美的解决名称乱码问题。

// 各浏览器基本都支持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 删除

excel