EasyExcel 导出多sheet，并控制sheet大小

1、EasyExcelUtils核心代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
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.style.HorizontalCellStyleStrategy;
import com.xgxz.finance.excel.config.ExcelRowProperties;
import com.xgxz.finance.excel.handler.FreezeAndFilter;
import com.xgxz.finance.excel.listener.ExcelDataListener;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * ClassName: EasyExcelUtil
 * Package: com.xgxz.finance.excel.utils
 * Description:
 *
 * @Author: 习惯向左
 * @Create: 2023/2/23 - 15:15
 * @Version: v1.0
 */
@Slf4j
public class EasyExcelUtil {

    public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        response.setCharacterEncoding("UTF-8");

        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        return response.getOutputStream();
    }

    /**
     * 获取默认表头内容的样式
     *
     * @return
     */
    public static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {

        // 头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());

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

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static <T> void importData(InputStream inputStream, Class<? extends Serializable> clazz, ExcelDataListener<T> listener) {

        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
    }

    /**
     * 写出多个sheet, 并且数据量大的时候对同一个sheet进行分页
     * @param dataList       数据
     * @param fileName       文件名称
     * @param sheetNameList  sheet名称
     * @param clazzList      每个sheet对应的字段文件
     * @param response       响应对象
     * @param <T>
     * @throws Exception
     */
    public static <T> void writeExcel(List<List<T>> dataList, String fileName, List<String> sheetNameList, List<Class<? extends Serializable>> clazzList, HttpServletResponse response) throws Exception {

        long start = System.currentTimeMillis();

        OutputStream outputStream = getOutputStream(fileName, response);

        ExcelWriter excelWriter = EasyExcelFactory.write(outputStream).autoCloseStream(Boolean.TRUE).build();

        Map<WriteSheet, List<T>> map = new LinkedHashMap<>();

        Integer sheetNo = 0;

        for (int i = 0; i < dataList.size(); i++) {

            List<List<T>> lists = SplitList.splitList(dataList.get(i), ExcelRowProperties.MAX_ROW_SIZE);
            int k = 1;

            for (int j = 0; j < lists.size(); j++) {
                String sheetName = sheetNameList.get(i);
                if (lists.size() > 1) {
                    sheetName = sheetNameList.get(i) + k;
                }

                WriteSheet sheet = EasyExcelFactory.writerSheet(sheetNo, sheetName)
                        .head(clazzList.get(i))
                        .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                        .registerWriteHandler(new FreezeAndFilter())
                        .build();

                map.put(sheet, lists.get(j));
                sheetNo++;
                if (lists.size() == k) {
                    k = 1;
                } else {
                    k++;
                }
            }
        }

        for (Map.Entry<WriteSheet, List<T>> entry : map.entrySet()) {
            excelWriter.write(entry.getValue(), entry.getKey());
        }

        outputStream.flush();
        excelWriter.finish();
        outputStream.close();

        log.info("耗时: " + (System.currentTimeMillis() - start));
    }

}

2、SplitList

import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: SplitList
 * Package: com.xgxz.finance.excel.utils
 * Description:
 *
 * @Author: 习惯向左
 * @Create: 2023/2/23 - 15:09
 * @Version: v1.0
 */
public class SplitList {

    public static <T> List<List<T>> splitList(List<T> list, int len) {

        List<List<T>> result = new ArrayList<>();

        if (list == null || list.size() == 0 || len < 1) {

            return null;
        }

        int size = list.size();

        int count = (size + len - 1) / len;

        for (int i = 0; i < count; i++) {

            List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));

            result.add(subList);
        }

        return result;
    }
}

3、额外的配置

创建冻结窗格、设置自动筛选

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * ClassName: FreezeAndFilter
 * Package: com.xgxz.finance.excel.handler
 * Description:
 *
 * @Author: 习惯向左
 * @Create: 2023/2/23 - 15:47
 * @Version: v1.0
 */
public class FreezeAndFilter implements SheetWriteHandler {

    private int colSplit = 0, rowSplit = 1, leftmostColumn = 0, topRow = 1;

    private String autoFilterRange = "1:1";

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        Sheet sheet = writeSheetHolder.getSheet();

        sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);

        sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
    }
}

4、控制sheet大小的配置

@Data
@ConfigurationProperties(prefix = "excel.max")
@Component
public class ExcelRowProperties implements InitializingBean {

    private int rowSize;

    public static int MAX_ROW_SIZE;

    @Override
    public void afterPropertiesSet() throws Exception {
        MAX_ROW_SIZE = rowSize;
    }
}

5、application.yml

excel:
  max:
    row-size: 60

6、sheet dto

@Data
public class ExcelSheet1DTO implements Serializable {

    @ExcelProperty("序号")
    private Integer id;

    @ExcelProperty("姓名")
    @ColumnWidth(10)
    private String name;

    @ExcelProperty("年龄")
    @ColumnWidth(10)
    private int age;

    @ExcelProperty("爱好")
    @ColumnWidth(20)
    private String happy;
}

7、使用创建好的工具类

   @Override
   public void download(HttpServletResponse response) {

       List<T> baseInfo = getBaseInfo();

       List<T> schoolInfo = getSchoolInfo();

       List<List<T>> dataList = Arrays.asList(baseInfo, schoolInfo);

       String fileName = "习惯向左";

       List<String> sheetNameList = Arrays.asList("基本信息", "毕业院校");

       List<Class<? extends Serializable>> clazzList = Arrays.asList(ExcelSheet1DTO.class, ExcelSheet2DTO.class);

       try {
           EasyExcelUtil.writeExcel(dataList, fileName, sheetNameList, clazzList, response);
       } catch (Exception e) {
           throw new RuntimeException(e);
       }

   }

习惯向左，感觉至上