EasyExcel动态生成多级表头以及内容填充

默认效果图

 设置背景色

1.导入依赖

​
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.0</version>
        </dependency>

​

2.Excel/Load代码

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
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.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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


@RestController
@RequestMapping("excel")
public class MultistageHeaderExcelController {

    @GetMapping("load")
    public void downLoad(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("渗流压力特征值统计", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        ExcelWriter writer = EasyExcelFactory.write(response.getOutputStream())
                // 核心代码：表头和正文的样式在此
                .registerWriteHandler(setConfigure()).build();

        // 动态添加表头，适用一些表头动态变化的场景
        WriteSheet sheet1 = new WriteSheet();
        sheet1.setSheetName("sheet001");
        sheet1.setSheetNo(0);

        // 创建一个表格，用于 Sheet 中使用
        WriteTable table = new WriteTable();
        table.setTableNo(1);
        // 核心代码：设置表头
        table.setHead(head());
        // 写数据
        writer.write(contentData(), sheet1, table);
        writer.finish();
    }


    private static List<List<String>> head() {
        List<List<String>> headTitles = ListUtils.newArrayList();
        String empty = " ";
        //表头可以根据实际情况进行修改
        //第一列，1/2/3行
        headTitles.add(ListUtils.newArrayList( "测点编号"));
        //第二列，1/2/3行
        ArrayList<String> foodList = ListUtils.newArrayList("渗压水位 (m)", "日期", "库水位 (m)");
        ArrayList<String> mealList = ListUtils.newArrayList("历史最高渗压水位", "历史最低渗压水位");

        // 根据实际需要，决定要渲染多少列
            mealList.forEach(meal -> {
                foodList.forEach(food -> {
                    headTitles.add(ListUtils.newArrayList(meal, food));
                });
            });
        headTitles.add(ListUtils.newArrayList("变化量 (m)"));
        return headTitles;
    }

    private static List<List<Object>> contentData() {
        List<List<Object>> contentList = ListUtils.newArrayList();
        //这里一个List<Object>才代表一行数据，需要映射成每行数据填充，横向填充（把实体数据的字段设置成一个List<Object>）
        //数据根据实际获取进行填充
        contentList.add(ListUtils.newArrayList("0001", "190.76", "2023-05-10", "null", "6.78", "2023-02-15","null","183.97"));
        contentList.add(ListUtils.newArrayList("0001", "190.76", "2023-05-10", "null", "6.78", "2023-02-15","null","183.97"));
        return contentList;
    }

    //配置字体，表头背景等
    private static HorizontalCellStyleStrategy setConfigure() {
        // 头的策略

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex()); // 黄色背景
        WriteFont headWriteFont = new WriteFont();
        // 加粗
        headWriteFont.setBold(true);
//        headWriteFont.setFontHeightInPoints((short) 14); // 设置行高，不重要
        headWriteCellStyle.setWriteFont(headWriteFont);


        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
//        contentWriteFont.setFontHeightInPoints((short) 14);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //边框
        //导出数据垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //导出数据水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
        contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
        contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}