EasyExcel工具类

起步依赖

代码语言：javascript复制

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

EasyExcelUtil

代码语言：javascript复制

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

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

/**
 * Excel工具类
 */
public class EasyExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);

    private EasyExcelUtil() {
        throw new IllegalStateException("工具类禁止实例化");
    }

    /**
     * 导出
     *
     * @param clazz    clazz
     * @param dataList dataList
     * @param fileName fileName
     * @param <T>      <T>
     */
    public static <T> void export(Class<T> clazz, List<T> dataList, String fileName) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) Objects.
                    requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
            if (response != null && !CollectionUtils.isEmpty(dataList)) {
                LOGGER.info("当前导出文件为:{}, size:{}", fileName, dataList.size());
                // 设置头的样式
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short) 11);
                headWriteCellStyle.setWriteFont(headWriteFont);
                // 设置内容的样式
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                response.setContentType("application/x-download");
                response.addHeader("Content-Disposition",
                        "attachment;filename="   URLEncoder.encode(fileName, "UTF-8")   ".xlsx");
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(new EasyExcelAutoWidthStrategy())
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .sheet("Sheet")
                        .doWrite(dataList);
            } else {
                LOGGER.warn("获取不到response，dataList:{}", dataList);
            }
        } catch (Exception e) {
            throw ExceptionFactory.bizException("导出异常", e);
        }
    }
}

样例实体

代码语言：javascript复制

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 导出DTO
 */
@ExcelIgnoreUnannotated
@Data
public class ExportDTO implements Serializable {

    private static final long serialVersionUID = 3470002146742757218L;

    /**
     * 第一列
     */
    @ExcelProperty(value = "第一列")
    private String test;

    /**
     * 第二列
     */
    @ExcelProperty(value = "第二列")
    private Long test2;

    /**
     * 第三列，指定列宽20
     */
    @ExcelProperty(value = "第三列")
    @ColumnWidth(20)
    private String test3;

    /**
     * 性别 [man=男，woman=女]
     */
    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    private String gender;

    /**
     * 金额，指定保留2位小数
     */
    @ExcelProperty(value = "金额")
    @NumberFormat("0.00")
    private BigDecimal price;
}

自定义Converter

代码语言：javascript复制

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * 转换性别字段
 */
public class GenderConverter implements Converter<String> {

    /**
     * 支持的Java类型，用于导出
     *
     * @return Class<?>
     */
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    /**
     * 支持的Excel内容类型，用于导入
     *
     * @return CellDataTypeEnum
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 转化数据到Excel数据的策略
     *
     * @param context context
     * @return WriteCellData<?>
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
        return new WriteCellData<>(GenderEnum.getEnumsByCode(context.getValue()).getDesc());
    }
}

样例性别枚举类

代码语言：javascript复制

/**
 * 性别枚举类
 */
public enum GenderEnum {
    /**
     * 男
     */
    MAN("man", "男"),
    /**
     * 女
     */
    WOMAN("woman", "女");

    GenderEnum(String code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    private static final Map<String, GenderEnum> CODE_MAP = new ConcurrentHashMap<>();

    static {
        for (GenderEnum genderEnum : EnumSet.allOf(GenderEnum.class)) {
            CODE_MAP.put(genderEnum.getCode(), genderEnum);
        }
    }

    public static GenderEnum getEnumsByCode(String code) {
        return CODE_MAP.get(code);
    }

    /**
     * code
     */
    private String code;

    /**
     * message
     */
    private String desc;

    public String getCode() {
        return code;
    }

    public String getDesc() {
        return desc;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }
}

com import string 工具类 数据

0 人点赞