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));
