Java使用FastExcel实现模板写入导出(多级表头)

依赖配置 (Maven pom.xml)

<dependencies>
    <!-- FastExcel 核心库 -->
    <dependency>
        <groupId>cn.idev.excel</groupId>
        <artifactId>fastexcel</artifactId>
        <version>1.0.0</version>
    </dependency>
    
    <!-- Apache POI 依赖 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

模版文件路径以及表头

导出映射实体类 DishAppManagementOriginalVo

package com.fantaibao.module.vo.appDish;

import cn.idev.excel.annotation.ExcelIgnoreUnannotated;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.ColumnWidth;
import cn.idev.excel.annotation.write.style.HeadFontStyle;
import cn.idev.excel.annotation.write.style.HeadStyle;
import cn.idev.excel.enums.BooleanEnum;
import cn.idev.excel.enums.poi.FillPatternTypeEnum;
import com.fantaibao.module.po.DishAppManagementOriginal;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

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

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 22)
@HeadFontStyle(fontName = "Microsoft YaHei", fontHeightInPoints = 11, bold = BooleanEnum.TRUE)
public class DishAppManagementOriginalVo {
    /**
     * 标准菜品名称
     */
    @ColumnWidth(30)
    @ExcelProperty({"关联标准菜品数据","标准菜品名称"})
    private String menuName;
    /**
     * 标准菜品名称
     */
    @ColumnWidth(20)
    @ExcelProperty({"关联标准菜品数据","标准单位"})
    private String menuUnit;
    /**
     * 菜品名称
     */
    @ColumnWidth(30)
    @ExcelProperty({"收银平台原始数据","菜品名称"})
    private String originalName;
    /**
     * 菜品编码
     */
    @ColumnWidth(30)
    @ExcelProperty({"收银平台原始数据","菜品编码"})
    private String originalCode;
    /**
     * 菜品类型(1-单点  2-套餐)
     */
    @ColumnWidth(20)
    @ExcelProperty({"收银平台原始数据","菜品类型"})
    private String dishTypeStr;
    /**
     * 菜品分类
     */
    @ColumnWidth(20)
    @ExcelProperty({"收银平台原始数据","菜品分类"})
    private String originalType;

    /**
     * 菜品平均定价(元保留两位小数)
     */
    @ColumnWidth(15)
    @ExcelProperty({"收银平台原始数据","定价"})
    private BigDecimal avgPrice;

    /**
     * 菜品单位(十个字符)
     */
    @ColumnWidth(20)
    @ExcelProperty({"收银平台原始数据","单位"})
    private String unit;

}

文件处理工具类CustomFileUtil

@Component
public class CustomFileUtil {

    public File getFile(String filePath) {

        try {
            String tmpPath = System.getProperty("java.io.tmpdir") + filePath.substring(1);
            File file = new File(tmpPath);
            InputStream inputStream = this.getClass().getResourceAsStream(filePath);
            if (null == inputStream) {
                throw new Exception("文件读取失败");
            }
            FileUtils.copyInputStreamToFile(inputStream, file);
            inputStream.close();
            return file;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }
}

业务层调用

 @Resource
    private CustomFileUtil customFileUtil;

 @Override
    public void export(Integer type, HttpServletResponse response) throws IOException {
        //设置导出文件格式
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        //设置导出文件名称
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("收银平台原始菜品数据.xlsx", StandardCharsets.UTF_8));
        //获取模板静态资源
        try (ExcelWriter excelWriter = FastExcel.write(response.getOutputStream()).withTemplate(customFileUtil.getFile("/template/original-dish-data.xlsx")).build()) {
            //获取模板的sheet页
            WriteSheet sheet1 = EasyExcel.writerSheet(1, "原始菜品信息").build();
            //获取导出数据源列表
            List<DishAppManagementOriginalVo> dishAppManagementOriginalVos = pageList(new Page(1, 10000), DishAppManagementOriginalListDto.builder().status(type).build()).getRecords();
            //使用 EasyExcel 进行数据填充
            excelWriter.write(dishAppManagementOriginalVos, sheet1);
            excelWriter.finish();
        }
    }