EasyPoi多Sheet页导出

前言

因多次遇到导出多Sheet页的需求，故记录下来，以备后续参考使用

一、Pom依赖

<!-- 集成easypoi组件 .导出excel http://easypoi.mydoc.io/ -->
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.2.0</version>
            </dependency>

二、主方法

SXSSFWorkbook workbook = new SXSSFWorkbook();
try {
                workbook = this.getSheetsList(notQualifiedSumDeptCountVos, locale);
                return workbook;
            } catch (Exception e) {
                log.error("错误",e);
                return null;
            }

三、拼接多Sheet页

private SXSSFWorkbook getSheetsList(List<Vo> notQualifiedSumDeptCountVos, Locale locale){
        // 点检项排名导出多sheet页
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        // 创建数据概览1-不合格次数的sheet
        this.getNotQualifiedSumExportSheet(notQualifiedSumDeptCountVos, sheetsList, locale);
        SXSSFWorkbook workbook = ExcelUtils.exportExcel(sheetsList);
        return workbook;
    }

四、获取单个Sheet页

private void getNotQualifiedSumExportSheet(List<NotQualifiedSumDeptCountVo> notQualifiedSumDeptCountVos, List<Map<String, Object>> sheetsList, Locale locale){
        if (CollectionUtil.isNotEmpty(notQualifiedSumDeptCountVos)) {
            // 创建数据概览1-不合格次数的sheet使用的map
            Map<String, Object> notQualifiedSumExportMap = new HashMap<>(16);
            String notQualifiedSumTitle = messageSource.getMessage("export.check.item.rank0.not.qualified.sum.title", null, locale);
            String notQualifiedSumSheetName = messageSource.getMessage("export.check.item.rank.not.qualified.sheet.name", null, locale);
            ExportParams notQualifiedSumExportParams = new ExportParams(notQualifiedSumTitle, notQualifiedSumSheetName, ExcelType.XSSF);
            List<ExcelExportEntity> notQualifiedSumColList = new ArrayList<>();
            List<Map<String,Object>> notQualifiedSumResList = new ArrayList<>();
            try {
                ExcelUtils.getExcelExportMap(notQualifiedSumColList, notQualifiedSumResList, notQualifiedSumDeptCountVos, NotQualifiedSumDeptCountVo.class, locale);
            } catch (Exception e) {
                log.error("getNotQualifiedSumExportSheet", e);
            }
            notQualifiedSumExportMap.put("title", notQualifiedSumExportParams);
            notQualifiedSumExportMap.put("entityList", notQualifiedSumColList);
            notQualifiedSumExportMap.put("data", notQualifiedSumResList);
            sheetsList.add(notQualifiedSumExportMap);
        }
    }

五、ExcelUtils

package com.ovopark.check.util;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.util.ReflectUtil;
import com.ovopark.check.service.impl.MyExcelExportService;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.context.MessageSource;

/**
 * @author: chenheng
 * @create: 2022-05-25 09:20
 * @description:
 **/
public class ExcelUtils {
  /**
   * 用于国际化
   */
  private static MessageSource messageSource = SpringContextUtils.getBean(MessageSource.class);
  /**
   * 一个excel 创建多个sheet
   * @param list
   * @return
   */
  public static SXSSFWorkbook exportExcel(List<Map<String, Object>> list) {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    for (Map<String, Object> map : list) {
      MyExcelExportService service = new MyExcelExportService();
      service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class,
          (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data"));
    }
    return workbook;
  }

  public static void getExcelExportMap(List<ExcelExportEntity> colList, List<Map<String,Object>> resList,
      List list, Class<?> pojoClass, Locale locale) throws IllegalAccessException {
    Field[] classFields = ReflectUtil.getFields(pojoClass);
    //需要导出的属性list
    List<Field> newFields = new ArrayList<>();
    for (Field field : classFields) {
      Excel excel = field.getAnnotation(Excel.class);
      if (excel != null) {
        ExcelExportEntity entity = new ExcelExportEntity();
        entity.setName(messageSource.getMessage(excel.name(), null, locale));
        entity.setKey(field.getName());
        entity.setOrderNum(Integer.parseInt(excel.orderNum()==null?"0":excel.orderNum()));
        colList.add(entity);
        newFields.add(field);
      }
    }
    //数据体
    for (Object obj : list) {
      Map<String, Object> map = new HashMap<>();
      for (Field field : newFields) {
        // 仅在获取用private修饰属性使用
        field.setAccessible(true);
        map.put(field.getName(), field.get(obj)!=null?field.get(obj):"-");
      }
      resList.add(map);
    }
  }

}

六、MyExcelExportService

package com.ovopark.check.service.impl;

import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import java.lang.reflect.Field;
import java.util.Collection;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * @author: chenheng
 * @create: 2022-05-25 09:26
 * @description:
 **/
@Slf4j
public class MyExcelExportService extends ExcelExportService {

  public void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) {
    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Excel export start ,class is {}", pojoClass);
      LOGGER.debug("Excel version is {}",
          entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }
    if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
      throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    try {
      List<ExcelExportEntity> excelParams = entityList;
      // 得到所有字段
      Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
      ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
      String targetId = etarget == null ? null : etarget.value();
      getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,
          null, null);
      //获取所有参数后,后面的逻辑判断就一致了
      createSheetForMap(workbook, entity, excelParams, dataSet);
    } catch (Exception e) {
      LOGGER.error(e.getMessage(), e);
      throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
  }
}

七、参考链接

1、Easypoi官网
2、使用EasyPOI实现列数动态生成，多个sheet生成
3、EasyPOI多sheet，动态列导出