通过easyexcel实现excel文件下载功能，并通过converter实现字段内容映射。

一、核心依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.10</version>
</dependency>

二、代码实现

1. bean常用注解示例

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.example.demo.entity.converter.QualificationsConverter;
import lombok.Data;
import lombok.experimental.Accessors;

import java.util.Date;

@Data
@Accessors(chain = true)
public class WorkerDTO {
    @ExcelProperty(value = "姓名", index = 0)
    private String name = "nobody";
    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;
    @ExcelProperty(value = "生日", index = 2)
    @DateTimeFormat(value = "yyyy-MM-dd")
    private Date birthday;
    /**
     * 1-大专，2-本科，3-硕士，4-博士
     */
    @ExcelProperty(value = "学历", index = 3, converter = QualificationsConverter.class)
    private Integer qualifications = 1;
    @ExcelIgnore
    private String other;
}

• @ExcelProperty
  • value：表头名称
  • index：列顺序
  • converter：强制该字段使用该converter，可以用于数据转化；本示例中qualifications（学历字段）用数字表示，但在excel文件中需要用文字展示故使用自定义converter
• @ExcelIgnore
  • 导出excel时忽略该字段
• @DateTimeFormat
  • 用于日期格式化

2. 自定义实现converter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class QualificationsConverter implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        int value = -1;
        switch (cellData.getStringValue()) {
            case "大专":
                value = 1;
                break;
            case "本科":
                value = 2;
                break;
            case "硕士":
                value = 3;
                break;
            case "博士":
                value = 4;
                break;
            default:
        }
        return value;
    }

    /**
     * 文件下载主要需要实现该方法
     */
    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String s = "";
        switch (value) {
            case 1:
                s = "大专";
                break;
            case 2:
                s = "本科";
                break;
            case 3:
                s = "硕士";
                break;
            case 4:
                s = "博士";
                break;
            default:
        }
        return new CellData(s);
    }
}

3. Controller实现

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.DateUtils;
import com.example.demo.entity.WorkerDTO;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
public class TestController {

    @GetMapping("/excel")
    public void excel(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(String.format("%s测试.", DateUtils.format(new Date(), "yyyy-MM-dd")), "UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ExcelTypeEnum.XLSX);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            EasyExcel.write(response.getOutputStream(), WorkerDTO.class).sheet("测试").doWrite(data());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化数据
     */
    private List<WorkerDTO> data() {
        List<WorkerDTO> list = new ArrayList<>();
        list.add(new WorkerDTO().setQualifications(1).setAge(27).setName("test1").setBirthday(new Date()).setOther("other"));
        list.add(new WorkerDTO().setQualifications(2).setAge(27).setName("test2").setOther("other"));
        list.add(new WorkerDTO().setQualifications(2).setAge(27).setName("test3"));
        list.add(new WorkerDTO().setQualifications(3).setAge(27).setName("test4"));
        list.add(new WorkerDTO().setQualifications(null).setAge(27).setName("test5"));
        list.add(new WorkerDTO().setQualifications(4).setAge(27).setName("test6"));
        return list;
    }
}

4. 测试

easyexcel-1.jpg

三、备注

1. easyexcel官方文档

https://www.yuque.com/easyexcel/doc/easyexcel