前后端分离下EasyExcel的使用

项目环境:SpringBoot+Vue

依赖导入

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.2</version>
</dependency>

tips

3.0.1版本 @ColumnWidth失效问题 用其他版本即可

实体类关联Excel

@ExcelProperty：value属性可用来设置表头名称
@ExcelPropertyvalue属性可用来设置表头名称

@TableName(value = "five_insurances")
@Data
public class FiveInsurances implements Serializable {
    /**
     *
     */
    @ExcelProperty("编号")
    @ColumnWidth(10)
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 工号
     */
    @ExcelProperty("工号")
    @ColumnWidth(15)
    @TableField(value = "number")
    private String number;

    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    @ColumnWidth(20)
    @TableField(value = "name")
    private String name;

    /**
     * 部门id
     */
    @ExcelProperty("部门")
    @ColumnWidth(20)
    @TableField(value = "dept_id")
    private Integer deptId;

    /**
     * 电话
     */
    @ExcelProperty("电话")
    @ColumnWidth(20)
    @TableField(value = "phone")
    private String phone;

    /**
     * 缴纳基数
     */
    @ExcelProperty({"社保", "缴纳基数"})
    @ColumnWidth(20)
    @TableField(value = "base_payment")
    private String basePayment;

    /**
     * 个人缴纳
     */
    @ExcelProperty({"社保", "个人", "缴纳费用"})
    @ColumnWidth(20)
    @TableField(value = "self_payment")
    private String selfPayment;

    /**
     * 工伤保险缴纳比例
     */
    @ExcelProperty({"社保", "企业", "工伤保险缴纳比例"})
    @ColumnWidth(20)
    @TableField(value = "ratio")
    private String ratio;

    /**
     * 企业缴纳
     */
    @ExcelProperty({"社保", "企业", "缴纳费用"})
    @ColumnWidth(20)
    @TableField(value = "com_payment")
    private String comPayment;

    /**
     * 备注
     */
    @ExcelProperty({"社保", "备注"})
    @ColumnWidth(30)
    @TableField(value = "remarks")
    private String remarks;

    @ExcelIgnore
    @TableField(exist = false)
    private Dept dept;

    @ExcelIgnore
    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

导出Excel

Controller

@GetMapping("/fihf")
@ApiOperation(value = "导出五险一金列表Excel")
@ApiImplicitParams(
            @ApiImplicitParam(dataType = "Interger",name = "page",value = "page==-1:查询所有;page==-2,返回空模板",required = false)
    )
public void exportList(HttpServletResponse response, @RequestParam(value = "page", defaultValue = "1") Integer page) throws Exception {
        PageBean<List<FiveInsurances>> pageBean = fiveInsurancesService.selectFiveInsurancesList(page);
        ExcelUtils.exportToWeb(response,"sheet1",FiveInsurances.class,pageBean.getData());
    }

前端Axios请求

exportFile(page = this.pageBean.current) {
    //复选框选中则设置page为-1,表示导出全部
    if (this.checked) {
        page = -1;
    }
    //关闭对话框
    this.dialogVisible = false;
    this.axios({
        method: 'get',
        url: baseURL + "fileExport/fihf",
        params: {
            page: page,
        },
        responseType: 'blob' //响应类型须设置为二进制文件流
    }).then((res) => {
        if (!res) {
            return
        }
        const link = document.createElement("a");//创建a标签
        let blob = new Blob([res.data], { type: "multipart/form-data" }); //设置文件类型
        link.style.display = "none";
        let url = URL.createObjectURL(blob);
        link.href = url; //给a标签href属性赋值
        link.setAttribute("download", decodeURI(Date.now() + '.xlsx'));
        document.body.appendChild(link);//挂载a标签
        link.click();//a标签click事件
        document.body.removeChild(link); //移除a标签
        window.URL.revokeObjectURL(url); //销毁下载链接

        console.log(res);
        this.checked = false;
        return this.$message.success("导出报表数据成功!")
    })
},

导入Excel

Controller

@PostMapping("/fihf")
@ApiOperation(value = "导入五险一金列表Excel")
public ResultVO<FiveInsurances> FiveInsurances(@RequestPart("file") MultipartFile file) throws IOException {
    if (file.getSize() < 0) {
        return ResultVO.createFail(404, "导入数据失败");
    }
    List<FiveInsurances> list = ExcelUtils.importFile(file, FiveInsurances.class);

    //持久化到数据库
    int i = fiveInsurancesService.insertBatch(list);
    if (i > 0) {
        return ResultVO.createSuccess("读取excel成功", null);
    } else {
        return ResultVO.createFail();
    }
}

前端Axios请求

handleBeforUpload(file) {
    console.log("beforeUpload", file);
    //创建文件附件
    let formData = new FormData();
    //添加到formdata
    formData.append("file", file);
    this.axios({
        method: 'post',
        url: baseURL + "fileImport/fihf",
        data: formData,
        Headers: {
            "Content-Type": "multipart/form-data",
        }
    }).then((res) => {
        console.log("读取excel", res);
        if (res.data.data == 10000) {
            this.$message.success(res.data.msg)
        }
    })
},

Excel导入导出工具类

package com.self.salarymanagement.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.util.IOUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel工具类
 */
@Slf4j
public class ExcelUtils {

    /**
     * 导出Excel到指定路径下
     *
     * @param path      路径
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     */
    public static void exportFileToLocal(String path, String excelName, String sheetName, Class clazz, List data) {
        String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 导出Excel到web
     *
     * @param response  响应
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void exportToWeb(HttpServletResponse response, String sheetName, Class clazz, List data) throws Exception {
//        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder .encode可以防止中文乱码
        String excelName = URLEncoder.encode(String.valueOf(System.currentTimeMillis()), "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(data);
    }

    /**
     * 导出Excel到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void exportToWeb(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
//        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 将指定位置指定名称的Excel导出到web
     *
     * @param response  响应
     * @param path      文件路径
     * @param excelName 文件名称
     * @throws UnsupportedEncodingException
     */
    public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
        File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
        if (!file.exists()) {
            return "文件不存在！";
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

        try (
                FileInputStream in = new FileInputStream(file);
                ServletOutputStream out = response.getOutputStream();
        ) {
            IOUtils.copy(in, out);
            return "导出成功！";
        } catch (Exception e) {
            log.error("导出文件异常：", e);
        }

        return "导出失败！";
    }

    public static <T> List<T> importFile(MultipartFile file, Class<T> clazz) throws IOException {

        return EasyExcel.read(file.getInputStream())
                .head(clazz)
                .registerReadListener(new DefaultExcelListener<T>())
                .sheet()
                .doReadSync();
    }
}

Excel导入监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Liu
 * @create 2023-03-08-11:44
 * @description:
 */
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {

    private final List<T> rows = new ArrayList<>();

    /**
     * 读取excel操作
     *
     * @param obj             数据
     * @param analysisContext 上下文
     */
    // 每读一样，会调用该invoke方法一次
    @Override
    public void invoke(T obj, AnalysisContext analysisContext) {
        //添加到list
        rows.add(obj);
        log.info("list容量" + rows.size() + obj);
        /** 数据量不是特别大，可以不需要打开
         // 实际数据量比较大时，rows里的数据可以存到一定量之后进行批量处理（比如存到数据库），
         // 然后清空列表，以防止内存占用过多造成OOM
         if(rows.size() >= 500){
         log.info("存入数据库ing");
         try {
         Thread.sleep(3000);
         } catch (InterruptedException e) {
         e.printStackTrace();
         }
         rows.clear();
         }
         */
    }

    /**
     * 读取玩excel后的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("成功读取【" + rows.size() + "】条数据");

        System.out.println("================================");
        rows.forEach(System.out::println);
        System.out.println("================================");

    }

    /**
     * 在读取excel异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
        }
    }

    /**
     * @return 返回读取的总数据
     */
    public List<T> getRows() {
        return rows;
    }
}

多级表头设置

嵌套el-table-column即可

<el-table-column label="社保" width="180">
    <el-table-column label="缴纳基数" width="120">
        <template slot-scope="scope">
            <span style="margin-left: 10px">{{ scope.row.basePayment }}</span>
        </template>
    </el-table-column>
    <el-table-column label="个人" width="120">
        <el-table-column label="缴纳费用" width="120">
            <template slot-scope="scope">
                <span style="margin-left: 10px">{{ scope.row.selfPayment }}</span>
            </template>
        </el-table-column>
    </el-table-column>
    <el-table-column label="企业" width="180">
        <el-table-column label="工伤保险缴纳比例" width="180">
            <template slot-scope="scope">
                <span style="margin-left: 10px">{{ scope.row.ratio }}</span>
            </template>
        </el-table-column>
        <el-table-column label="缴纳费用" width="180">
            <template slot-scope="scope">
                <span style="margin-left: 10px">{{ scope.row.comPayment }}</span>
            </template>
        </el-table-column>
    </el-table-column>
    <el-table-column label="备注" width="120">
        <template slot-scope="scope">
            <span style="margin-left: 10px">{{ scope.row.remarks }}</span>
        </template>
    </el-table-column>
</el-table-column>