使用easyExcel 导出带有合计行的excel

项目背景：

产品提了个需求，让把系统中的已存在的几个excel导出加一行合计

实现：

当时想了两种思路；

1. 在业务层把需要合计的手动累加，然后写到最后一行实现合计

2.实现一个工具类，指定需要合计的表头，然后在工具类中对这些列进行合计，基本不改变原有的业务代码，只需要传入需要合计的表头

因为是大量地方使用，所以采用工具类的方式，基本不需要了解之前的业务逻辑

 效果图：

思路：

1.采用了alibaba的easyExcel，使用填充的方式，需要一个空模板，将数据填充进去，具体请点击查看 easyexcel语雀官方文档

2. 识别出需要合计的列，将这一列除表头外的所有数据进行累加，累加结果写入到新的数据行；

3. 数据格式：

    表头：List<List<String>> headList，

    填充数据：List<List<String>> dataList，

    需要合计的表头：HashSet<String> totalHeadSet 合计列不能重复

表头格式：表头只支持一行，里面的泛型List<String>是每一个表头的名称，外面的List<List<String>>是每一行的所有表头，一定不要把内层的List<String>理解为List<表头>；

示例：

        // 表头格式
        List<List<String>> rowHead = new ArrayList<>();
        List<String> head = new ArrayList<>();
        head.add("姓名");
        rowHead.add(head);
        List<String> head1 = new ArrayList<>();
        head1.add("身份证");
        rowHead.add(head);
        List<String> head2 = new ArrayList<>();
        head2.add("金额");
        rowHead.add(head);

        // 数据格式
        List<List<String>> dataList = new ArrayList<>();
        List<String> data = new ArrayList<>();
        data.add("小明");
        data.add("111111");
        data.add("100");
        dataList.add(data);
        List<String> data1 = new ArrayList<>();
        data1.add("大明");
        data1.add("222222");
        data1.add("3600");
        dataList.add(data1);

        // 合计列表头
        HashSet<String> totalHeadSet = new HashSet<>();
        totalHeadSet.add("金额");

问题：

1. 为什么不在第一列写“合计”两个字，便于查看？

    因为不确定第一列是否需要进行合计，如果业务明确不会出现，可以手动修改工具类

package excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.Data;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import utils.DateUtils;
import utils.TotalRowHandler;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 导出带有合计行的excel
 *
 * @author wcl
 */
@Data
public class ExportExcelUtils {

    /**
     * 合计行数据
     */
    @Data
    static class TotalHeadData {
        /**
         * 表头
         */
        private String head;

        /**
         * 下标
         */
        private Integer index;

        /**
         * 数据
         */
        private String value;
    }

    /**
     * 导出带有合计的动态表头（合计行会添加样式）
     *
     * @param enName       导出excel模板名称
     * @param name         excel文件名称
     * @param dataList     数据
     * @param headList     表头
     * @param totalHeadSet 合计列表头
     * @return excel地址
     */
    public static String exportExcelDynamicAndTotal(String enName, String name, List<List<String>> dataList, List<List<String>> headList,
                                                    HashSet<String> totalHeadSet) throws Exception {
        // 模板文件
        InputStream is = ExportExcelUtils.class.getClassLoader().getResourceAsStream("excel/" + enName + "ExportTemplate.xlsx");
        if (is == null) {
            throw new Exception(name + "模板没有找到");
        }
        // 创建需要导出的文件
        String tempFilePath = FileUtils.getSystemTempPath();
        boolean flg = new File(tempFilePath).mkdir();
        String fileName = name + DateUtils.getCurrentDay("yyyyMMddHHmmss") + ".xlsx";
        tempFilePath += fileName;
        File excelFile = new File(tempFilePath);
        try {
            if (!excelFile.exists()) {
                flg = new File(tempFilePath).createNewFile();
            }
        } catch (IOException e) {
            log.error("创建excel文件失败：", e);
            throw new Exception("创建excel文件失败：" + e.getMessage());
        }
        if (CollectionUtils.isEmpty(dataList)) {
            throw new Exception("没有需要导出的数据，请调整检索条件");
        }
        ExcelWriter excelWriter = EasyExcel.write(tempFilePath).head(headList).withTemplate(is).build();
        // 写入合计行样式；默认表头只有一行
        TotalRowHandler totalRowHandler = new TotalRowHandler(dataList.size());
        WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(totalRowHandler).build();
        addTotalRow(headList, dataList, totalHeadSet);
        // 用填充的方式写入数据
        excelWriter.write(dataList, writeSheet);

        // 关闭流
        excelWriter.finish();
        if (!excelFile.exists()) {
            throw new Exception("导出失败，没有生成导出文件");
        }
        return tempFilePath;
    }

    /**
     * 写入合计行,直接写入导出数据里，数据为空不会写入
     * 先找出所有需要进行合计的列，遍历这一列的数据，进行累加操作，最后将计算出的合计写入到最后一行
     *
     * @param headList     表头
     * @param dataList     数据
     * @param totalHeadSet 需要合计的列表头名称
     */
    private static void addTotalRow(List<List<String>> headList, List<List<String>> dataList, HashSet<String> totalHeadSet) {
        if (!CollectionUtils.isEmpty(totalHeadSet)) {
            // 需要合计的列，表头和列下标
            List<TotalHeadData> headIndexData = new ArrayList<>();
            for (int i = 0; i < headList.size(); i++) {
                // i是表头的列下标
                List<String> stringList = headList.get(i);
                String head = stringList.get(0);
                // 需要合计的表头
                if (totalHeadSet.contains(head)) {
                    TotalHeadData headData = new TotalHeadData();
                    headData.setHead(head);
                    headData.setIndex(i);
                    headIndexData.add(headData);
                }
            }
            // 下标-数据对象
            Map<Integer, TotalHeadData> indexTotalHeadData = headIndexData
                    .parallelStream()
                    .collect(Collectors.toMap(TotalHeadData::getIndex, v -> v));
            // 遍历行
            for (List<String> rowList : dataList) {
                // 遍历列
                for (int i = 0; i < rowList.size(); i++) {
                    if (indexTotalHeadData.containsKey(i)) {
                        TotalHeadData totalHeadData = indexTotalHeadData.get(i);
                        // 累计数据
                        String valueTotal = totalHeadData.getValue();
                        BigDecimal total = BigDecimal.ZERO;
                        if (StringUtils.isNotEmpty(valueTotal)) {
                            try {
                                total = new BigDecimal(valueTotal);
                            } catch (Exception e) {
                                log.error("String转换BigDecimal失败，累计值：{}，错误信息：{}", valueTotal, e.getMessage());
                            }
                        }
                        // 本次数据
                        String valueNow = rowList.get(i);
                        BigDecimal decimalNow = BigDecimal.ZERO;
                        if (StringUtils.isNotEmpty(valueNow)) {
                            try {
                                decimalNow = new BigDecimal(valueNow);
                            } catch (Exception e) {
                                log.error("String转换BigDecimal失败，单元格内容：{}，错误信息：{}", valueNow, e.getMessage());
                            }
                        }
                        total = total.add(decimalNow);
                        totalHeadData.setValue(String.valueOf(total));
                    }
                }
            }
            // 写入合计行
            List<String> totalRow = new ArrayList<>();
            // 写入行数据；如果业务明确，可以将此处改为fori，在第一列写入“合计”二字，便于查看
            for (List<String> ignored : headList) {
                String value = "";
                totalRow.add(value);
            }
            // 修改行数据
            for (int i = 0; i < totalRow.size(); i++) {
                if (indexTotalHeadData.containsKey(i)) {
                    TotalHeadData totalHeadData = indexTotalHeadData.get(i);
                    totalRow.set(i, totalHeadData.getValue());
                }
            }
            dataList.add(totalRow);
        }
    }


}

package utils;

import org.apache.commons.lang.StringUtils;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;

/**
 * 日期处理工具类
 *
 * @author wcl
 */
public class DateUtils {
    /**
     * 获取当前日期
     *
     * @param pattern 格式，默认格式yyyyMMdd
     * @return 20190101
     */
    public static String getCurrentDay(String pattern) {
        LocalDateTime localDateTime = LocalDateTime.now();
        if (StringUtils.isEmpty(pattern)) {
            pattern = "yyyyMMdd";
        }
        return format(localDateTime2Date(localDateTime), pattern);
    }

    /**
     * 格式化日期为字符串
     *
     * @param date    date
     * @param pattern 格式
     * @return 日期字符串
     */
    public static String format(Date date, String pattern) {
        if (date == null) {
            return null;
        }
        Instant instant = date.toInstant();
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
        return localDateTime.format(DateTimeFormatter.ofPattern(pattern));
    }

    /**
     * LocalDateTime类型转为Date
     *
     * @param localDateTime LocalDateTime object
     * @return Date object
     */
    public static Date localDateTime2Date(LocalDateTime localDateTime) {
        return Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
    }
}

 设置合计行的样式

package utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

/**
 * 合计行设置样式
 *
 * @author wcl
 */
@Slf4j
public class TotalRowHandler implements CellWriteHandler {

    /**
     * 开始添加样式的行下标
     */
    private Integer startRow;

    public TotalRowHandler(Integer startRow) {
        this.startRow = startRow;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
                                 Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
                                Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData,
                                       Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList,
                                 Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 非表头项
        if (!isHead) {
            if (cell != null) {
                // 从数据开始，表头不会读取
                if (relativeRowIndex >= startRow) {
                    if (head != null) {
                        log.debug("表头====》" + head.getHeadNameList().get(0));
                    }
                    if (CellType.STRING.name().equals(cell.getCellTypeEnum().name())) {
                        log.debug("单元格数据===》" + cell.getStringCellValue());
                    } else if (CellType.NUMERIC.name().equals(cell.getCellTypeEnum().name())) {
                        log.debug("单元格数据===》" + cell.getNumericCellValue());
                    }
                    log.info("");
                    // 设置样式
                    Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                    // 设置字体样式
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    WriteFont headWriteFont = new WriteFont();
                    headWriteFont.setFontName("宋体");
                    headWriteFont.setFontHeightInPoints((short) 14);
                    headWriteFont.setBold(true);
                    writeCellStyle.setWriteFont(headWriteFont);
                    // 设置背景色
                    writeCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

                    // 样式写入单元格
                    CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, writeCellStyle);
                    cell.setCellStyle(cellStyle);
                }
            }
        }

    }
}