官网文档地址：Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 首页
maven最新版本地址:https://mvnrepository.com/artifact/com.alibaba/easyexcel
一、添加依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency>
二、web导入
1、文件上传
样例数据
可以忽略mapPointInfoMapper，正常的批插入mapper
@PostMapping("/writexxx") @ApiOperation("xxx") public AjaxResult writeMapInfo(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), MapPointInfo.class, new ExcelListener(mapPointInfoMapper)).sheet().doRead(); return toAjax(1); }
2、接收数据的model
package xxxx; import com.alibaba.excel.annotation.ExcelIgnore; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Getter; import lombok.Setter; import lombok.ToString; import java.io.Serializable; import java.math.BigDecimal; @Getter @Setter @ToString public class MapPointInfo implements Serializable { @ApiModelProperty("主键id") @ExcelIgnore private Long id; @ApiModelProperty("名称") private String name; @ApiModelProperty("经度") private BigDecimal longitude; @ApiModelProperty("纬度") private BigDecimal latitude; @ApiModelProperty("地址") private String address; @ApiModelProperty("电话") private String phone; @ApiModelProperty("分类") private String type; }
3、处理数据的监听器
package xxxx.listener; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.util.ListUtils; import com.alibaba.fastjson.JSON; import com.lets.psccs.mapper.MapPointInfoMapper; import com.lets.psccs.model.MapPointInfo; import lombok.extern.slf4j.Slf4j; import java.util.List; @Slf4j public class ExcelListener extends AnalysisEventListener<MapPointInfo> { /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list */ private static final int BATCH_COUNT = 5; List<MapPointInfo> list = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); private MapPointInfoMapper mapPointInfoMapper; public ExcelListener() { } public ExcelListener(MapPointInfoMapper mapPointInfoMapper) { this.mapPointInfoMapper = mapPointInfoMapper; } /** * 这个每一条数据解析都会来调用 */ @Override public void invoke(MapPointInfo data, AnalysisContext context) { log.info("解析到一条数据:{}", JSON.toJSONString(data)); //mapPointInfoMapper.insertSelective(data); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); log.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { log.info("{}条数据，开始存储数据库！", list.size()); mapPointInfoMapper.insertList(list); log.info("存储数据库成功！"); } }
三、web导出
1、导出接口
操作日志的导出demo,operationLogs 自己加模拟数据就可以
@ApiOperation("导出操作日志") @GetMapping("/export") @ApiImplicitParams({ @ApiImplicitParam(name = "date1", value = "开始日期", defaultValue = "2020-12-01",required = true), @ApiImplicitParam(name = "date2", value = "开始日期", defaultValue = "2021-12-01",required = true), }) public void downchin(HttpServletResponse response, String date1, String date2) throws IOException { SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm"); String date = sdf.format(new Date()); List<OperationLog> operationLogs = operationLogMapper.selectOperationLog(date1,date2); //定义输出文件名称 String fileName = URLEncoder.encode("操作日志"+date + ".xlsx","UTF-8") ; //设置响应字符集 response.setCharacterEncoding("UTF-8"); //设置响应媒体类型 response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename="+fileName); EasyExcel.write(response.getOutputStream(),OperationLog.class).sheet("操作日志").doWrite(operationLogs); }
2、导出的model
import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.baomidou.mybatisplus.annotation.FieldFill; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.fasterxml.jackson.annotation.JsonFormat; import com.fasterxml.jackson.annotation.JsonIgnore; import lombok.Data; import java.io.Serializable; import java.util.Date; /** * operation_log * @author */ @Data public class OperationLog implements Serializable { /** * 自增主键 */ @TableId(type = IdType.AUTO) @ExcelIgnore private Integer id; /** * 操作防区 */ @ExcelProperty(value = {"操作日志"}) private String defence; /** * 动作 */ @ExcelProperty(value = {"操作日志","动作"}) @ColumnWidth(16) private String action; /** * 操作员姓名 */ @ExcelProperty(value = {"操作日志","操作人"}) private String nickname; /** * 操作人员id */ @ExcelIgnore private Integer userId; /** * 操作时间 */ @TableField(fill = FieldFill.INSERT) @ExcelProperty(value = {"操作日志","处理时间"}) @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8") @ColumnWidth(23) private Date createTime; /** * 操作命令 */ @ExcelIgnore @JsonIgnore private String command; /** * 执行状态 */ @ExcelProperty(value = {"操作日志","执行状态"}) @ColumnWidth(23) private String state; /** * 备注 */ @ExcelIgnore @JsonIgnore private String remark; private static final long serialVersionUID = 1L; }
四、Excel数据填充
1、准备一个excel模板
2、创建一个对应填充的类
import lombok.Getter; import lombok.Setter; @Getter @Setter public class xxxxPrint { /** *所属部门 */ private String deptName; /** * 申请人 */ private String userName; /** *申请时间 */ private String createTime; /** *xx标题 */ private String name; /** *申请内容 */ private String detail; /** *部门时间 */ private String deptTime; /** *中心时间 */ private String centerTime; /** *xx状态 */ private Integer status; /** *驳回原因 */ private String rejectReason; /** *xxx */ private String urgentType; /** *xxxx */ private String eventType; }
3、编写填充代码
@GetMapping("/getExcel") @ApiOperation("获取Excel") public void writeMapInfo(HttpServletResponse res,Long id) throws IOException { //获取xx打印 表格 xxPrint xxPrint = xxService.getxxPrint(id); //定义输出文件名称 String fileName = URLEncoder.encode(teamCasePrint.getName() + ".xlsx","UTF-8") ; //设置响应字符集 res.setCharacterEncoding("UTF-8"); //设置响应媒体类型 res.setContentType("application/vnd.ms-excel"); //设置响应的格式说明 res.setHeader("Content-Disposition", "attachment;filename="+fileName); //读取响应文件的模板 File file= ResourceUtils.getFile("classpath:templete/申请表打印.xls"); //替换模板的数据 EasyExcel.write(res.getOutputStream()).withTemplate(file).sheet().doFill(teamCasePrint); }
4、执行结果
乱码的主要原因是字符编码和媒体类型
//定义输出文件名称 String fileName = URLEncoder.encode(teamCasePrint.getName() + ".xlsx","UTF-8") ; //设置响应字符集 res.setCharacterEncoding("UTF-8"); //设置响应媒体类型 res.setContentType("application/vnd.ms-excel");
