使用EasyPoi實現(xiàn)多Sheet頁導出的示例代碼

更新時間：2025年03月08日 15:55:42 作者：chenHelloWorld

在項目開發(fā)中,我們常常會遇到導出多Sheet頁的需求,本文降維打擊介紹一下如何使用EasyPoi實現(xiàn)這一功能,文中的示例代碼簡潔易懂,有需要的可以參考下

前言

因多次遇到導出多Sheet頁的需求，故記錄下來，以備后續(xù)參考使用

一、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<>();
        // 創(chuàng)建數(shù)據(jù)概覽1-不合格次數(shù)的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)) {
            // 創(chuàng)建數(shù)據(jù)概覽1-不合格次數(shù)的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 創(chuàng)建多個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);
      }
    }
    //數(shù)據(jù)體
    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);
      //獲取所有參數(shù)后,后面的邏輯判斷就一致了
      createSheetForMap(workbook, entity, excelParams, dataSet);
    } catch (Exception e) {
      LOGGER.error(e.getMessage(), e);
      throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
  }
}

參考：

Easypoi官網(wǎng)

EasyPOI實現(xiàn)多sheet和列數(shù)的動態(tài)生成

到此這篇關于使用EasyPoi實現(xiàn)多Sheet頁導出的示例代碼的文章就介紹到這了,更多相關EasyPoi多Sheet頁導出內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: