参考
需求描述
pom.xml
公共类
测试
- 导出 Excel 到本地
- 浏览器导出 Excel
非注解方式数据格式化
非注解方式定义列宽和行高
- 修改 ExcelUtils 中 export 方法
非注解方式自定义数据格式【未完成】
注解方式自定义数据格式【未完成】

参考

● Easy Excel

● 如何用 easyExcel 动态导出全部数据？

● 使用 easyExcel write 步骤；并且设置列宽

● BuiltinFormats 速查表

● EasyExcel格式化映射注解和样式注解详解

需求描述

存在下面的数据

@Data
public class RepaymentPlanVO {
    /**
     * Last Interest Rate
     */
    private BigDecimal lastInterestRate;
    /**
     * 根据期间生成的动态列
     */
    private List<RepaymentPlanItemVO> listTrendsColumn;
}

其中 lastInterestRate 列是固定的
listTrendsColumn 是多列，根据计算得出

pom.xml

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>
</dependencies>

公共类

● 这里代码基本和如何用 easyExcel 动态导出全部数据？一致

ExcelHead-动态 excel 表头

package com.luoma.finance.vo.report;
import lombok.Data;
/**
 * Excel 导出类 - ExcelHead
 */
@Data
public class ExcelHead<T> {
    /**
     * 内容里的字段名称
     */
    private String fieldName;
    /**
     * 显示值，一般为中文的
     */
    private String title;
    /**
     * 如果为 null 的值
     */
    private T nullValue;
    public ExcelHead(String fieldName, String title) {
        this.fieldName = fieldName;
        this.title = title;
    }
    public ExcelHead(String fieldName, String title, T nullValue) {
        this.fieldName = fieldName;
        this.title = title;
        this.nullValue = nullValue;
    }
}

ContentTypeUtils- Content-type 工具类

package com.luoma.finance.util;
import cn.hutool.core.util.CharsetUtil;
import com.luoma.finance.data.ResponseCode;
import com.luoma.finance.excepiton.RemoteBaseException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
/**
 * Content-type 工具类
 */
public final class ContentTypeUtils {
    public static final String CONTENT_DISPOSITION;
    public static final String APPLICATION_EXCEL;
    static {
        CONTENT_DISPOSITION = "Content-Disposition";
        APPLICATION_EXCEL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    }
    public static String encode(String fileName) {
        try {
            return "attachment;filename=" + URLEncoder.encode(fileName, CharsetUtil.UTF_8)
                    + ".xlsx";
        } catch (UnsupportedEncodingException e) {
            throw new RemoteBaseException(ResponseCode.UNKNOWN_EXCEPTION, e.getMessage());
        }
    }
}

ExcelSheetVerticalCellStyleStrategy-excel 样式设置

package com.luoma.finance.util.excel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
import com.luoma.finance.util.ExcelUtils;
/**
 * excel 样式设置
 */
public class ExcelSheetVerticalCellStyleStrategy extends AbstractVerticalCellStyleStrategy {
    /**
     * 头部样式
     *
     * @return 样式
     */
    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        return ExcelUtils.writeCellStyle();
    }
    /**
     * 内容样式
     *
     * @return 样式
     */
    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        return ExcelUtils.writeCellStyle();
    }
}

ExcelUtils-excel 工具类

package com.luoma.finance.util;
import cn.hutool.core.util.CharsetUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.google.common.collect.Lists;
import com.luoma.finance.common.Contents;
import com.luoma.finance.data.ResponseCode;
import com.luoma.finance.excepiton.InternalApiException;
import com.luoma.finance.util.excel.ExcelSheetVerticalCellStyleStrategy;
import com.luoma.finance.vo.report.ExcelHead;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
 * excel 工具类
 */
public class ExcelUtils {
    /**
     * 单 sheet 导出公共方法
     *
     * @param list      导出数据集合
     * @param tClass    数据对象类型
     * @param fileName  文件名称
     * @param sheetName sheet页名称
     * @param response  http写入
     * @param <T>       数据对象类型
     */
    public static <T> void applyExport(List<T> list, Class<T> tClass, String fileName, String sheetName,
                                       HttpServletResponse response) {
        if (null == list || list.size() <= 0) {
            throw new InternalApiException(ResponseCode.NO_DATA, Contents.EXPORT_NULL);
        }
        ExcelSheetVerticalCellStyleStrategy cellStyleStrategy = new ExcelSheetVerticalCellStyleStrategy();
        response.setCharacterEncoding(CharsetUtil.UTF_8);
        response.setContentType(ContentTypeUtils.APPLICATION_EXCEL);
        try {
            response.setHeader(ContentTypeUtils.CONTENT_DISPOSITION, ContentTypeUtils.encode(fileName));
            EasyExcel.write(response.getOutputStream(), tClass).sheet(sheetName).registerWriteHandler(cellStyleStrategy)
                    .doWrite(list);
        } catch (IOException e) {
            throw new InternalApiException(ResponseCode.OPERATE_ERROR, e.getMessage());
        }
    }
    /**
     * 构造excel基础样式
     *
     * @return 样式
     */
    public static WriteCellStyle writeCellStyle() {
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        writeCellStyle.setWrapped(true);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        return writeCellStyle;
    }
    /**
     * 导出 excel
     *
     * @param fileName 文件名称
     * @param headList 表头数据
     * @param dataList 表数据
     * @param response HttpServletResponse
     */
    public static void export(String fileName,
                              List<ExcelHead> headList,
                              List<Map<String, Object>> dataList,
                              HttpServletResponse response) {
        try {
            response.setCharacterEncoding(CharsetUtil.UTF_8);
            response.setContentType(ContentTypeUtils.APPLICATION_EXCEL);
            response.setHeader(ContentTypeUtils.CONTENT_DISPOSITION, ContentTypeUtils.encode(fileName));
            ExcelWriterBuilder writerBuilder = EasyExcel.write();
            writerBuilder.file(response.getOutputStream());
            writerBuilder.excelType(ExcelTypeEnum.XLSX);
            writerBuilder.autoCloseStream(true);
            ExcelSheetVerticalCellStyleStrategy cellStyleStrategy = new ExcelSheetVerticalCellStyleStrategy();
            writerBuilder.registerWriteHandler(cellStyleStrategy);
            writerBuilder
                    .head(convertHead(headList))
                    .sheet("sheet1")
                    .doWrite(convertData(headList, dataList));
        } catch (IOException e) {
            throw new InternalApiException(ResponseCode.OPERATE_ERROR, e.getMessage());
        }
    }
    /**
     * 写 excel
     *
     * @param filePath 保存的路径名
     * @param headList 表头数据
     * @param dataList 表数据
     */
    public static void write(String filePath, List<ExcelHead> headList, List<Map<String, Object>> dataList) {
        ExcelWriterBuilder writerBuilder = EasyExcel.write();
        writerBuilder.file(filePath);
        writerBuilder.excelType(ExcelTypeEnum.XLSX);
        writerBuilder.autoCloseStream(true);
        writerBuilder.head(convertHead(headList))
                .sheet("sheet1")
                .doWrite(convertData(headList, dataList));
    }
    /**
     * 会先删除 excel 所有 sheet，再写入
     *
     * @param filePath  保存的路径名
     * @param sheetName sheetName
     * @param c         数据对象类型
     * @param list      导出数据集合
     * @param <T>       数据对象类型
     */
    public static <T> void writeSheet(String filePath, String sheetName, Class<T> c, List<T> list) {
        EasyExcel.write(filePath, c).sheet(sheetName).doWrite(list);
    }
    /**
     * 表头数据转换
     * https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E5%8A%A8%E6%80%81%E5%A4%B4%E5%AE%9E%E6%97%B6%E7%94%9F%E6%88%90%E5%A4%B4%E5%86%99%E5%85%A5
     *
     * @param headList 表头数据
     * @return List<List < String>>
     */
    private static List<List<String>> convertHead(List<ExcelHead> headList) {
        List<List<String>> list = new ArrayList<>();
        for (ExcelHead head : headList) {
            list.add(Lists.newArrayList(head.getTitle()));
        }
        //沒有搞清楚 head 的参数为 List<List<String>>,用 List<String> 就 OK 了
        return list;
    }
    /**
     * 表内容数据转换
     *
     * @param headList 表头数据
     * @param dataList 表内容数据转换
     * @return List<List<Object>>
     */
    private static List<List<Object>> convertData(List<ExcelHead> headList,
                                                  List<Map<String, Object>> dataList) {
        List<List<Object>> result = new ArrayList();
        //对 dataList 转为 easyExcel 的数据格式
        for (Map<String, Object> data : dataList) {
            List<Object> row = new ArrayList();
            for (ExcelHead h : headList) {
                Object o = data.get(h.getFieldName());
                //需要对null的处理，比如age的null，要转为-1
                row.add(handler(o, h.getNullValue()));
            }
            result.add(row);
        }
        return result;
    }
    /**
     * null 值处理
     *
     * @param 对象
     * @param nullValue 默认值
     * @return 默认值
     */
    private static Object handler(Object o, Object nullValue) {
        return o != null ? o : nullValue;
    }
}

测试

导出 Excel 到本地

public static void main(String[] args) {
    String filePath = "d:\\temp\\a.xlsx";
    //表头数据
    List<ExcelHead> headList = new ArrayList();
    headList.add(new ExcelHead<String>("name", "名称"));
    headList.add(new ExcelHead("age", "年龄", -1));
    //表内容数据
    List<Map<String, Object>> dataList = new ArrayList();
    Map<String, Object> one = new HashMap();
    one.put("name", "张三");
    one.put("age", 20);
    dataList.add(one);
    Map<String, Object> two = new HashMap();
    two.put("name", "李四");
    two.put("age", 18);
    dataList.add(two);
    Map<String, Object> there = new HashMap();
    there.put("name", "不知道年龄");
    there.put("age", null);
    dataList.add(there);
    //保存 excel 到指定路径
    ExcelUtils.write(filePath, headList, dataList);
}

运行后，打开 a.xlsx

浏览器导出 Excel

package com.luoma.finance.controller;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * 控制器 - 报表 -  还款计划报表
 */
@Slf4j
@RestController
@RequestMapping("/api/repaymentPlanReport")
public class RepaymentPlanReportController {
    @GetMapping("/testExport")
    public void testExport(HttpServletResponse response) {
        //表头数据
        List<ExcelHead> headList = new ArrayList();
        headList.add(new ExcelHead<String>("name", "名称"));
        headList.add(new ExcelHead("age", "年龄", -1));
        //表内容数据
        List<Map<String, Object>> dataList = new ArrayList();
        Map<String, Object> one = new HashMap();
        one.put("name", "张三");
        one.put("age", 20);
        dataList.add(one);
        Map<String, Object> two = new HashMap();
        two.put("name", "李四");
        two.put("age", 18);
        dataList.add(two);
        Map<String, Object> there = new HashMap();
        there.put("name", "不知道年龄");
        there.put("age", null);
        dataList.add(there);
        //导出 excel
        ExcelUtils.export("testExcel", headList, dataList, response);
    }
}

访问 http://localhost:18810/financing/api/repaymentPlanReport/testExport
下载文件 testExcel.xlsx

非注解方式数据格式化

修改 ExcelHead-动态 excel 表头

package com.luoma.finance.vo.report;
import lombok.Data;
/**
 * Excel 导出表头类 - ExcelHead
 * v_hwhao - 2023年06月03日19:26:06
 */
@Data
public class ExcelHead<T> {
    /**
     * 内容里的字段名称
     */
    private String fieldName;
    /**
     * 显示值，一般为中文的
     */
    private String title;
    /**
     * 列类型
     * 从 BuiltinFormats 的 BUILTIN_FORMATS_CN[] 获取
     * 序号参考：https://blog.csdn.net/ruantiao3440/article/details/126704369
     * 快速根据索引获取字符串、或根据字符串获取索引
     * int builtinFormat = BuiltinFormats.getBuiltinFormat("h:mm:ss AM/PM");
     * System.out.println(builtinFormat);
     * String builtinFormat1 = BuiltinFormats.getBuiltinFormat(49);
     * System.out.println(builtinFormat1);
     */
    private String dataFormat;
    /**
     * 字体颜色
     */
    private String fontColor;
    /**
     * 对齐方式，right:右对齐
     */
    private String alignment;
    /**
     * 如果为 null 的值
     */
    private T nullValue;
    public ExcelHead(String fieldName, String title, String dataFormat) {
        this.fieldName = fieldName;
        this.title = title;
        this.dataFormat = dataFormat;
    }
    public ExcelHead(String fieldName, String title, String dataFormat, T nullValue) {
        this.fieldName = fieldName;
        this.title = title;
        this.dataFormat = dataFormat;
        this.nullValue = nullValue;
    }
    public ExcelHead(){}
}

修改表头定义

@GetMapping("/testExport")
public void testExport(HttpServletResponse response) {
    //表头数据
    List<ExcelHead> headList = new ArrayList();
    headList.add(new ExcelHead<String>("name", "名称",null));
    ExcelHead<BigDecimal> ageHead = new ExcelHead<BigDecimal>("age", "年龄","4");
    ageHead.setFontColor("red");
    headList.add(ageHead);
    //表内容数据
    List<Map<String, Object>> dataList = new ArrayList();
    Map<String, Object> one = new HashMap();
    one.put("name", "张三");
    one.put("age", new BigDecimal("11111.23456789"));
    dataList.add(one);
    Map<String, Object> two = new HashMap();
    two.put("name", "李四");
    two.put("age", new BigDecimal("11122.23456"));
    dataList.add(two);
    Map<String, Object> there = new HashMap();
    there.put("name", "不知道年龄");
    there.put("age", null);
    dataList.add(there);
    //浏览器下载导出 excel
    ExcelUtils.export("testExcel", headList, dataList, response);
}

新增样式设置类-RepaymentPlanExcelStyle

package com.luoma.finance.vo.report;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
import com.luoma.finance.util.CommonUtil;
import com.luoma.finance.util.ExcelUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import java.util.List;
/**
 * Excel 样式设置 - 还款计划表
 */
public class RepaymentPlanExcelStyle extends AbstractVerticalCellStyleStrategy {
    /**
     * 表头数据
     */
    private List<ExcelHead> headList;
    public RepaymentPlanExcelStyle(List<ExcelHead> headList) {
        this.headList = headList;
    }
    /**
     * 头部样式
     *
     * @return 样式
     */
    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//水平居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //writeCellStyle.setWrapped(true);//自动换行
        writeCellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        writeCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        writeCellStyle.setBorderRight(BorderStyle.THIN);//右边框
        writeCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        writeCellStyle.setShrinkToFit(true);//设置文本收缩至合适
        return writeCellStyle;
    }
    /**
     * 内容样式
     *
     * @return 样式
     */
    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        //默认样式
        WriteCellStyle writeCellStyle =  ExcelUtils.writeCellStyle();
        int columnIndex = 0;
        for (ExcelHead itemColumn : headList) {
            //数据格式化代码
            String dataFormat = itemColumn.getDataFormat();
            if(CommonUtil.isNotNull(dataFormat)){
                if(columnIndex == head.getColumnIndex()) {
                    writeCellStyle.setDataFormat(Short.valueOf(dataFormat));
                }
            }
            //字体颜色
            String fontRed = itemColumn.getFontColor();
            if(CommonUtil.isNotNull(fontRed) && "red".equals(fontRed)){
                if(columnIndex == head.getColumnIndex()) {
                    WriteFont font = new WriteFont();
                    font.setColor(IndexedColors.RED.getIndex()); //红色
                    writeCellStyle.setWriteFont(font);
                }
            }
            //对齐方式
            String alignment = itemColumn.getAlignment();
            if(CommonUtil.isNotNull(alignment) && Contents.alignmentRight.equals(alignment)){
                if(columnIndex == head.getColumnIndex()) {
                    writeCellStyle.setHorizontalAlignment(HorizontalAlignment.RIGHT);
                }
            }
            columnIndex++;
        }
        //if(head.getColumnIndex() == 1) {
        //    //------------------------字体设置
        //    WriteFont font = new WriteFont();
        //    font.setColor(IndexedColors.RED.getIndex()); //红色
        //    writeCellStyle.setWriteFont(font);
        //
        //    //------------------------千分位设置
        //    //序号：4，从 BuiltinFormats 的 BUILTIN_FORMATS_CN[] 获取
        //    //https://blog.csdn.net/ruantiao3440/article/details/126704369
        //    //BuiltinFormats.getBuiltinFormat(4,"#,##0.00", Locale.SIMPLIFIED_CHINESE);
        //    //writeCellStyle.setDataFormat((short)4);
        //}
        return writeCellStyle;
    }
}

修改 ExcelUtils 中 export 方法

/**
 * excel工具类
 */
public class ExcelUtils {
    //其它代码省略
    /**
     * 导出 excel
     *
     * @param fileName 文件名称
     * @param headList 表头数据
     * @param dataList 表数据
     * @param response HttpServletResponse
     */
    public static void export(String fileName,
                              List<ExcelHead> headList,
                              List<Map<String, Object>> dataList,
                              HttpServletResponse response) {
        try {
            response.setCharacterEncoding(CharsetUtil.UTF_8);
            response.setContentType(ContentTypeUtils.APPLICATION_EXCEL);
            response.setHeader(ContentTypeUtils.CONTENT_DISPOSITION, ContentTypeUtils.encode(fileName));
            ExcelWriterBuilder writerBuilder = EasyExcel.write();
            writerBuilder.file(response.getOutputStream());
            writerBuilder.excelType(ExcelTypeEnum.XLSX);
            writerBuilder.autoCloseStream(true);
            RepaymentPlanExcelStyle cellStyleStrategy = new RepaymentPlanExcelStyle(headList);
            writerBuilder.registerWriteHandler(cellStyleStrategy);
            writerBuilder
                    .head(convertHead(headList))
                    .sheet("sheet1")
                    .doWrite(convertData(headList, dataList));
        } catch (IOException e) {
            throw new InternalApiException(ResponseCode.OPERATE_ERROR, e.getMessage());
        }
    }
}

测试

访问 http://localhost:18810/financing/api/repaymentPlanReport/testExport
下载文件 testExcel.xlsx

非注解方式定义列宽和行高

参考：使用 easyExcel write 步骤；并且设置列宽

修改 ExcelUtils 中 export 方法

public class ExcelUtils {
    //其它代码省略
    /**
     * 导出 excel
     *
     * @param fileName 文件名称
     * @param headList 表头数据
     * @param dataList 表数据
     * @param response HttpServletResponse
     */
    public static void export(String fileName,
                              List<ExcelHead> headList,
                              List<Map<String, Object>> dataList,
                              HttpServletResponse response) {
        try {
            response.setCharacterEncoding(CharsetUtil.UTF_8);
            response.setContentType(ContentTypeUtils.APPLICATION_EXCEL);
            response.setHeader(ContentTypeUtils.CONTENT_DISPOSITION, ContentTypeUtils.encode(fileName));
            ExcelWriterBuilder writerBuilder = EasyExcel.write();
            writerBuilder.file(response.getOutputStream());
            writerBuilder.excelType(ExcelTypeEnum.XLSX);
            writerBuilder.autoCloseStream(true);
            RepaymentPlanExcelStyle cellStyleStrategy = new RepaymentPlanExcelStyle(headList);
            writerBuilder.registerWriteHandler(cellStyleStrategy);
            writerBuilder
                    //表头
                    .head(convertHead(headList))
                    //sheet
                    .sheet("sheet1")
                    //注册策略——简单的列宽策略，列宽 25
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
                    //注册策略——简单的行高策略，头行高 30，内容行高 20
                    //.registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30,(short)20))
                    //写数据
                    .doWrite(convertData(headList, dataList));
        } catch (IOException e) {
            throw new InternalApiException(ResponseCode.OPERATE_ERROR, e.getMessage());
        }
    }
}

非注解方式自定义数据格式【未完成】

比如我先设置百分比的位数为 5 位，通过 BuiltinFormats 中查询，得到最多就是保留 2 为小数

// 68
"t0.00%",

假如我想要这种格式呢？"t0.0000%"，没有。
想解决这个问题，我找了百度和谷歌几乎所有答案，看官方文档，都没有
最后的结论是，不行。
即使 >@ContentStyle注解的dataFormat，除了内建的BuiltinFormats格式之外，是否支持自定义格式呢中提到的，可以通过在 CellWriteHandler中覆盖 dataFormat。最终也是不行的。也还是只能支持 BuiltinFormats 中的格式。
折中的解决方案是，把这一列转换为文本，把百分比数据 0.000123 直接转换成字符串 0.0123%，直接写入到单元格中，同时把这个一列设置为文本

ExcelHead<BigDecimal> ageHead = new ExcelHead<BigDecimal>("age", "年龄","49");
ageHead.setFontColor("red");
headList.add(ageHead);
//表内容数据
List<Map<String, Object>> dataList = new ArrayList();
Map<String, Object> one = new HashMap();
one.put("name", "张三111111111111111111111111111111111");
one.put("age", "0.110123%");
dataList.add(one);

49 对应的 BuiltinFormats 是

// 49
"@",

这样的效果效果并不是想要的

注解方式自定义数据格式【未完成】

EasyExcel注解方式导出数据过程解析

ExcelTestVO

package com.luoma.finance.vo.report;
import com.alibaba.excel.annotation.ExcelIgnore;
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 com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.math.BigDecimal;
/**
 * Excel 导出类 - 测试类
 */
@Data
@ColumnWidth(25)
@ContentRowHeight(25)
@HeadRowHeight(45)
@ExcelIgnoreUnannotated
public class ExcelTestVO {
    @ExcelProperty(value = "姓名")
    private String name;
    @NumberFormat(value = "#,##0.00")//千分位，保留 2 位小数
    @ExcelProperty(value = "金额")
    private BigDecimal amount;
    /**
     * 这个数值格式化注解时内置转换器使用，当没有配置自定义转换器converter时
     * 会根据field.getType类型+ String匹配内置的转换器。如 DoubleStringConverter
     */
    @ExcelProperty(value = "汇率")
    @NumberFormat(value = "#.#####%")//自定义格式，转换为百分比，保留 5 位小数
    private BigDecimal rate;
    @ExcelIgnore//不包含到导出列中
    private String remark;
}

导出接口

@GetMapping("/testExportModel")
public void testExportModel(HttpServletResponse response) {
    List<ExcelTestVO> list = new ArrayList<>();
    ExcelTestVO vo = new ExcelTestVO();
    vo.setName("张三");
    vo.setAmount(new BigDecimal("6666.6666"));
    vo.setRate(new BigDecimal("0.0001234"));
    vo.setRemark("test1");
    list.add(vo);
    vo = new ExcelTestVO();
    vo.setName("李四");
    vo.setAmount(new BigDecimal("7777.7777"));
    vo.setRate(new BigDecimal("0.0005678"));
    vo.setRemark("test2");
    list.add(vo);
    ExcelUtils.applyExport(list, ExcelTestVO.class,
            "testExportModel", "testExportModel", response);
}

导出效果

也不是我想要的效果

Java-使用 Easyexcel 导出 Excel 数据(动态列)

参考

需求描述

pom.xml

公共类

ExcelHead-动态 excel 表头

ContentTypeUtils- Content-type 工具类

ExcelSheetVerticalCellStyleStrategy-excel 样式设置

ExcelUtils-excel 工具类

测试

导出 Excel 到本地

浏览器导出 Excel

非注解方式数据格式化

修改 ExcelHead-动态 excel 表头

修改表头定义

新增样式设置类-RepaymentPlanExcelStyle

修改 ExcelUtils 中 export 方法

测试

非注解方式定义列宽和行高

修改 ExcelUtils 中 export 方法

非注解方式自定义数据格式【未完成】

注解方式自定义数据格式【未完成】

ExcelTestVO

导出接口

导出效果