easyexcel复杂格式导出、自定义合并

最新推荐文章于 2024-06-06 17:37:13 发布

眉宇下的小格调

最新推荐文章于 2024-06-06 17:37:13 发布

阅读量1.2w

点赞数 7

分类专栏： Tools

本文链接：https://blog.csdn.net/u010425898/article/details/106692567

版权

Tools 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

说明
- 思路
代码

说明

记录一下使用easyexcel导出复杂格式，以及遇到的问题
使用版本说明：

easyexcel：2.1.7

截止目前最新版为2.2.4，据说是修复了无法使用内容样式bug，但是我在maven中央仓库下载不了jar包，也没验证。
先看最终需要完成的成品图：
在这里插入图片描述

表头包含所有班级，还有根据班级、姓名合并单元格，以及每个人后面还增加了一行合计分数。此处有个前提，每个班级的姓名都是一模一样的，可以理解为这个表的数据按照班级–>姓名的二级分组

思路

由于原始数据从数据库拿出来的，是没有总分以及合计这些数据，第一个想法就是在写excel的时候动态的插入一行，以及取当前行的前几列的值求和，事实证明这个想法行不通，easyexcel使用 SXSSFSheet 来读写excel，要实现动态插入一行就要用到 shiftRows 这个方法，该方法被@NotImplemented了。于是只有在原始数据上动刀，组装数据时直接计算好总分和合计，写excel的时候只管合并就行了。将原始数据组装成以下这个样子：

班级	姓名	学科	选择题	阅读题	应用题	总分
高一三班	小明	语文	10	20	30	60
高一三班	小明	数学	10	20	30	60
高一三班	小明	合计				120

代码

package com.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.util.concurrent.AtomicDouble;
import org.apache.poi.ss.usermodel.*;

import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.stream.Collectors;

public class Excel {

    public static void excel() {
        String targetFile = "blog.xlsx";
        // 模拟从数据库拿基础数据
        List<Demo> db = fromDb();
        // 简单地按班级排个序，再分组，方便组装数据
        Map<String, List<Demo>> clazzMap = db.stream().sorted(Comparator.comparing(Demo::getClasses)).collect(Collectors.groupingBy(Demo::getClasses, TreeMap::new, Collectors.toList()));
        // 第一列，即班级
        List<String> clazzList = db.stream().map(Demo::getClasses).distinct().collect(Collectors.toList());
        // 第二列，即姓名，每个班级的人都一致
        List<String> nameList = db.stream().map(Demo::getName).distinct().collect(Collectors.toList());
        // 第三列，即学科，每个班级的每个人的学科个数都一致
        List<String> subjectList = db.stream().map(Demo::getSubject).distinct().collect(Collectors.toList());
        // 将基础数据整合成excel要写的数据，包含总分、合计这些数据
        List<Demo> demoList = Lists.newArrayList();
        clazzMap.forEach((clazz, clazzes) -> {
            Map<String, List<Demo>> byName = clazzes.stream().collect(Collectors.groupingBy(Demo::getName));
            byName.forEach((name, subjects) -> {
                AtomicDouble temp = new AtomicDouble(0);
                subjects.forEach(d -> {
                    Demo demo = new Demo();
                    demo.setClasses(clazz);
                    demo.setName(name);
                    demo.setSubject(d.getSubject());
                    demo.setSelect(d.getSelect());
                    demo.setRead(d.getRead());
                    demo.setPractical(d.getPractical());
                    double total = d.getSelect() + d.getRead() + d.getPractical();
                    demo.setTotal(total);
                    temp.addAndGet(total);
                    demoList.add(demo);
                });
                Demo total = new Demo();
                total.setSubject("合计：");
                total.setTotal(temp.get());
                demoList.add(total);
            });
        });

        // easyexcel的bug，如果表头是List<List<Object>>，那么内容也必须是这个格式，否则内容的样式无法加载，截止目前2.2.4的版本说明上已修复，但下载不了jar包，也就没试过
        List<List<Object>> data = Lists.newArrayList();
        demoList.forEach(d -> data.add(Lists.newArrayList(d.getClasses(), d.getName(), d.getSubject(), d.getSelect(), d.getRead(), d.getPractical(), d.getTotal())));
        // 自定义合并策略，第一个参数是学科的数量+1，+1是为了把合计这一行也算上，
        DemoMergeStrategy mergeStrategy = new DemoMergeStrategy(subjectList.size() + 1, nameList.size());
        ExcelWriter excelWriter = EasyExcel.write(targetFile)
            // 设置统一的表头、表内容样式
            .registerWriteHandler(style())
            // 设置表头，表内容的行高
            .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 50, (short) 20))
            .build();
        WriteSheet sheet1 = EasyExcel.writerSheet(0, "班级分数表")
            // 指定这个sheet的每个列的宽度
            .registerWriteHandler(new DemoColumnWidthStrategy())
            .registerWriteHandler(mergeStrategy)
            // 自定义多级复杂表头
            .head(scoreHead(clazzList))
            .build();

        excelWriter.write(data, sheet1);
        excelWriter.finish();

    }

    public static List<Demo> fromDb() {
        List<Demo> list = Lists.newArrayList();
        list.add(new Demo("高一三班", "小明", "语文", 10, 20, 30));
        list.add(new Demo("高一三班", "小明", "数学", 10, 20, 30));
        list.add(new Demo("高一三班", "小爱", "语文", 10, 20, 30));
        list.add(new Demo("高一三班", "小爱", "数学", 10, 20, 30));
        list.add(new Demo("高一三班", "小金", "语文", 10, 20, 30));
        list.add(new Demo("高一三班", "小金", "数学", 10, 20, 30));

        list.add(new Demo("高一五班", "小明", "语文", 20, 20, 40));
        list.add(new Demo("高一五班", "小明", "数学", 20, 20, 40));
        list.add(new Demo("高一五班", "小爱", "语文", 20, 20, 40));
        list.add(new Demo("高一五班", "小爱", "数学", 20, 20, 40));
        list.add(new Demo("高一五班", "小金", "语文", 20, 20, 40));
        list.add(new Demo("高一五班", "小金", "数学", 20, 20, 40));
        return list;
    }


    private static List<List<String>> scoreHead(List<String> clazz) {
        List<List<String>> list = Lists.newArrayList();
        List<String> head0 = Lists.newArrayList();
        List<String> head1 = Lists.newArrayList();
        List<String> head2 = Lists.newArrayList();
        List<String> head3 = Lists.newArrayList();
        List<String> head4 = Lists.newArrayList();
        List<String> head5 = Lists.newArrayList();
        List<String> head6 = Lists.newArrayList();
        String title = "班级分数表";
        String clazzInfo = "包含（" + Joiner.on(",").join(clazz) + "）";
        head0.add(title);
        head0.add(clazzInfo);
        head0.add("班级");
        head1.add(title);
        head1.add(clazzInfo);
        head1.add("姓名");
        head2.add(title);
        head2.add(clazzInfo);
        head2.add("学科");
        head3.add(title);
        head3.add(clazzInfo);
        head3.add("选择题");
        head4.add(title);
        head4.add(clazzInfo);
        head4.add("阅读题");
        head5.add(title);
        head5.add(clazzInfo);
        head5.add("应用题");
        head6.add(title);
        head6.add(clazzInfo);
        head6.add("总分");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        list.add(head6);
        return list;
    }

    public static HorizontalCellStyleStrategy style() {
        // 头的样式
        WriteCellStyle headStyle = new WriteCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headFont = new WriteFont();
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short) 16);
        headStyle.setWriteFont(headFont);
        headStyle.setWrapped(true);

        // 内容的样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        contentStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentStyle.setWriteFont(contentWriteFont);
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setWrapped(true);
        return new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }

}

下面是自定义列宽类：

package com.demo;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;

public class DemoColumnWidthStrategy extends AbstractHeadColumnWidthStyleStrategy {

    @Override
    protected Integer columnWidth(Head head, Integer columnIndex) {
        switch (columnIndex) {
            case 0:
                // 班级
                return 20;
            case 1:
                // 姓名
                return 10;
            case 2:
                // 学科
                return 10;
            case 3:
                // 选择题
                return 10;
            case 4:
                // 阅读题
                return 10;
            case 5:
                // 应用题
                return 10;
            case 6:
                // 总分
                return 15;
            default:
                return 10;
        }
    }
}

下面是自定义合并类：

package com.demo;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;


@Slf4j
public class DemoMergeStrategy extends AbstractMergeStrategy {

    private final int subjectLength;
    private final int nameLength;
    private int nextMerge1 = 0;
    private int nextMerge2 = 0;

    public DemoMergeStrategy(int subjectLength, int nameLength) {
        this.subjectLength = subjectLength;
        this.nameLength = nameLength;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        // 由于每个单元格单元格都会调用该方法，为了避免重复合并异常，只在应合并的行、列执行即可
        // relativeRowIndex == 0，即表内容的第一行（除去表头）
        if (cell.getColumnIndex() == 0) {
            if (relativeRowIndex == 0 || relativeRowIndex == nextMerge1) {
                int lastRow = subjectLength * nameLength;
                nextMerge1 = relativeRowIndex + lastRow;
                CellRangeAddress cellRangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + lastRow - 1, 0, 0);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        } else if (cell.getColumnIndex() == 1) {
            if (relativeRowIndex == 0 || relativeRowIndex == nextMerge2) {
                int lastRow = subjectLength;
                nextMerge2 = relativeRowIndex + lastRow;
                CellRangeAddress cellRangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + lastRow - 1, 1, 1);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        } else if (cell.getColumnIndex() == 2) {
            int first = subjectLength - 1;
            if (relativeRowIndex == first || (relativeRowIndex - first) % subjectLength == 0) {
                sheet.addMergedRegionUnsafe(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex() + 3));
            }
        }
    }

}