EasyExcel多数据导出到多Sheet工作表,以及常用配置

一、EasyExcel多数据导出到多Sheet工作表

  outputStream = response.getOutputStream();



            // 创建ExcelWriter对象
            com.alibaba.excel.ExcelWriter writer = EasyExcel
                    .write(outputStream)
                    //自定义合并单元格策略
                    .registerWriteHandler(new CustomMergeStrategy())
                    //自定义样式
                    .registerWriteHandler(ExcelExportUtil.defaultStylePolicyPolicy())
                    .build();

            List<EventTask1Template> list1 = new ArrayList<>();
            list1.add(new EventTask1Template("张三", "20", "男"));
            list1.add(new EventTask1Template("张三", "02", "女"));
            list1.add(new EventTask1Template("李四", "25", "女"));
            WriteSheet sheet1 = EasyExcel.writerSheet("sheet1").head(EventTask1Template.class).build();
            writer.write(list1,sheet1);

            List<EventTask2Template> list2 = new ArrayList<>();
            list2.add(new EventTask2Template("王五", "20", "男"));
            list2.add(new EventTask2Template("王五", "02", "女"));
            list2.add(new EventTask2Template("赵六", "25", "女"));
            WriteSheet sheet2 = EasyExcel.writerSheet("sheet2").head(EventTask2Template.class).build();
            writer.write(list2,sheet2);

            // 完成写入操作
            writer.finish();

 WriteSheet sheet1 = EasyExcel.writerSheet("sheet1").head(EventTask1Template.class).build();
 这里的head可以用List<List<String>>来自定义动态的表头

自定义合并策略(自行扩充内容)

package com.netintech.core.easyexcel.mergeStrategy;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class CustomMergeStrategy extends AbstractMergeStrategy {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public CustomMergeStrategy() {
    }

    public CustomMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int curRowIndex = cell.getRowIndex();  //获取当前行数
        int curColIndex = cell.getColumnIndex(); //获取当前列
        sheet.setColumnWidth(curColIndex,5120);

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(sheet, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 单元格向上合并
     *
     * @param sheet 工作簿
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(Sheet sheet, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前列数据
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        //获取上一行单元格
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        //获取上一行数据
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //curColIndex 可指定哪一行为对比列，也可以通过配置合并列进行对比
        // 获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
        Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && bool) {
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }

        }
    }

    private void mergeCells(Sheet sheet, int columnIndex, int startRowIndex, int endRowIndex) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex);
        sheet.addMergedRegion(cellRangeAddress);
    }
}

二、默认样式策略

 /**
     * 默认样式策略策略
     *
     * @return
     */
    public static HorizontalCellStyleStrategy defaultStylePolicyPolicy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置列宽
        // 背景设置为浅蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        //设置水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字体为微软雅黑
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字体为微软雅黑
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 这个策略是 头是头的样式 内容是内容的样式
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }