springboot使用EasyExcel导入导出填充,解决导出乱码问题(web)

springboot使用EasyExcel导入导出填充,解决导出乱码问题(web)

官网文档地址：​​​​​​Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 首页

maven最新版本地址:https://mvnrepository.com/artifact/com.alibaba/easyexcel

一、添加依赖

    com.alibaba
    easyexcel
    3.0.5

二、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 {
    
    private static final int BATCH_COUNT = 5;
    List 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();
        }
    }

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


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

    
    @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;
    
    private String name;
    
    private String detail;
    
    private String deptTime;
    
    private String centerTime;
    
    private Integer status;
    
    private String rejectReason;
    
    private String urgentType;
    
    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");