easyExcel导出excel文件之表头操作实现:多级表头,多级表头之表头单独操作,没有表头的动态值,table表头,动态表头

最新推荐文章于 2024-09-04 11:38:27 发布

菜菜小菠菠

最新推荐文章于 2024-09-04 11:38:27 发布

阅读量8k

点赞数 27

文章标签： excel java 前端

本文链接：https://blog.csdn.net/lol19950605/article/details/135860770

版权

本文介绍了如何使用阿里巴巴EasyExcel库在Java中实现多级表头，包括合并单元格、动态值插入以及设置样式，展示了如何配置实体类以控制Excel导出的复杂结构。

摘要由CSDN通过智能技术生成

一.多级表头

实体类

 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
 
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersExcel {
 
    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","五行", "金"})
    //合并单元格
    //@ContentLoopMerge(eachRow = 2)
    private String jin;
    /**
     * 木
     */
    @ExcelProperty(value = {"七灵图","五行", "木"})
    private String mu;
    /**
     * 水
     */
    @ExcelProperty(value = {"七灵图","五行", "水"})
    private String shui;
    /**
     * 火
     */
    @ExcelProperty(value = {"七灵图","五行", "火"})
    private String huo;
    /**
     * 土
     */
    @ExcelProperty(value = {"七灵图","五行", "土"})
    private String tu;
    /**
     * 阴
     */
    @ExcelProperty(value = {"七灵图","两仪", "阴"})
    private String yin;
    /**
     * 阳
     */
    @ExcelProperty(value = {"七灵图","两仪", "阳"})
    private String yang;
}

代码

    public void exportHeaders(String path) {
 
        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
 
        EasyExcel.write(path, ExportHeadersExcel.class)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果图

可以看到,代码和正常导出一样,关键在于实体类的配置.在真正的表头金木水火土等字段上加了

{"七灵图","五行",金},阴阳上加了{"七灵图","两仪",阴},最终的效果就是"七灵图"成了标题,金木水火土在五行标题下,阴阳在两仪标题下

二. 导出多级表头和标题+没有表头的动态值

有时候,我们需要在导出的时候添加一个动态的值,比如导出的时候把时间作为一行展示.同时表头和标题都需要存在.

实体类

 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
 
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersValueExcel {
//cellWritervalue对应ValueCellWriteHandler里的cellWritervalue
    /**
     * 金
     */
    @ExcelProperty(value = {"七灵图","总纲","五行", "金"})
    private String jin;
    /**
     * 木
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "木"})
    private String mu;
    /**
     * 水
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "水"})
    private String shui;
    /**
     * 火
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "火"})
    private String huo;
    /**
     * 土
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "土"})
    private String tu;
    /**
     * 阴
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阴"})
    private String yin;
    /**
     * 阳
     */
    @ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阳"})
    private String yang;
}

代码

    public void exportHeadersValue(String path) {
 
        String sheetName = "sheetName";
        List<ExportHeadersValueExcel> excels = new ArrayList<>();
        ExportHeadersValueExcel exportHeadersValueExcel = new ExportHeadersValueExcel();
        exportHeadersValueExcel.setShui("一元重水");
        excels.add(exportHeadersValueExcel);
        String value = "阴阳包括五行,五行含有阴阳";
        EasyExcel.write(path, ExportHeadersValueExcel.class)
                .registerWriteHandler(new ValueCellWriteHandler(value))
                .sheet(sheetName).doWrite(excels);
    }

处理器

 
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;
 
import java.util.List;
import java.util.Properties;
 
/**
 * @author 冥河教主
 * @Date 2021/02/08 16:00
 */
public class ValueCellWriteHandler implements CellWriteHandler {
    private String cellWritervalue;
    PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${","}");
 
    public ValueCellWriteHandler(String cellvalue) {
        this.cellWritervalue = cellvalue;
    }
 
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head != null) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                properties.setProperty("cellWriterValue", cellWritervalue);
                for (int i = 0; i < headNameList.size(); i++) {
                    headNameList.set(i, propertyPlaceholderHelper.replacePlaceholders(headNameList.get(i), properties));
                }
            }
        }
    }
 
}

效果

实体类中的"${cellWriterValue}",取的是处理器中的cellWriterValue,在写入流的时候, .registerWriteHandler(new ValueCellWriteHandler(value)),注册处理器就行了

三.使用table,达成两层表头效果

代码

    public void exportHeadersTable(String path) {
 
        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
        exportHeadersExcel1.setShui("玄冥真水");
        excels.add(exportHeadersExcel1);
        List<ExportHeadersExcel> excels1 = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
        exportHeadersExcel2.setShui("天一真水");
        excels1.add(exportHeadersExcel2);
        ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
        exportHeadersExcel3.setShui("无形真水");
        excels1.add(exportHeadersExcel3);
 
        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
        // 第一次写入会创建头
        ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
        excelWriter.write(excels, writeSheet, writeTable0);
        // 第二次写入也会创建头，然后在第一次的后面写入数据
        excelWriter.write(excels1, writeSheet, writeTable1);
        excelWriter.finish();
    }

效果

四. 动态表头

表头信息

private List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("地" );
        List<String> head1 = new ArrayList<String>();
        head1.add("水");
        List<String> head2 = new ArrayList<String>();
        head2.add("火");
        List<String> head3 = new ArrayList<String>();
        head3.add("风");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        return list;
    }

代码

    public void exportDynamicHeaders(String path) {
        //因为水在ExportHeadersExcel类中是第三个表头,对应head()方法中地水火风的火,所以"一元重水"在表头火下
        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        List<List<String>> head = this.head();
        EasyExcel.write(path)
                .head(head)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果

exportHeadersExcel.setShui("一元重水");shui这个字段在exportHeadersExcel对象中是第三个字段,所以导出的excel中,表头火下是一元重水.也可以在exportHeadersExcel对象中用@ExcelProperty(index = 0)来指定列.还可以直接用list<string>等形式

四. 多级表头之表头单独操作

代码

    public void exportExcelCenter(String path) {

        String sheetName = "sheetName";
        List<ExportHeadersExcel> excels = new ArrayList<>();
        ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
        exportHeadersExcel.setShui("一元重水");
        excels.add(exportHeadersExcel);
        //设置表头样式
        List<CellStyleModel> cellStyleList = new ArrayList<>();
        //设置第一行表头居中
        //HorizontalAlignment.LEFT,单元格中字体靠左或靠右 VerticalAlignment.TOP 单元格中字体靠上或靠下
        CellStyleModel alignmentCellStyleModel = CellStyleModel.createAlignmentCellStyleModel(sheetName, 0, 0, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
        cellStyleList.add(alignmentCellStyleModel);
        CustomCellStyleHandler customCellStyleHandler = new CustomCellStyleHandler(cellStyleList);
        EasyExcel.write(path, ExportHeadersExcel.class)
                .registerWriteHandler(customCellStyleHandler)
                .sheet(sheetName)
                .doWrite(excels);
    }

效果