通過(guò)easyexcel實(shí)現(xiàn)excel文件下載功能店乐，并通過(guò)converter實(shí)現(xiàn)字段內(nèi)容映射魏割。

一廊勃、核心依賴

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

二辉饱、代碼實(shí)現(xiàn)

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 = "學(xué)歷", index = 3, converter = QualificationsConverter.class)
    private Integer qualifications = 1;
    @ExcelIgnore
    private String other;
}

• @ExcelProperty
  • value：表頭名稱
  • index：列順序
  • converter：強(qiáng)制該字段使用該converter缔逛，可以用于數(shù)據(jù)轉(zhuǎn)化；本示例中qualifications（學(xué)歷字段）用數(shù)字表示姓惑，但在excel文件中需要用文字展示故使用自定義converter
• @ExcelIgnore
  • 導(dǎo)出excel時(shí)忽略該字段
• @DateTimeFormat
  • 用于日期格式化

2. 自定義實(shí)現(xiàn)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;
    }

    /**
     * 文件下載主要需要實(shí)現(xiàn)該方法
     */
    @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實(shí)現(xiàn)

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測(cè)試.", 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("測(cè)試").doWrite(data());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化數(shù)據(jù)
     */
    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. 測(cè)試

easyexcel-1.jpg

三褐奴、備注

1. easyexcel官方文檔

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