使用EasyPoi实现多Sheet页导出的代码

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

<!-- 集成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>

1

2

3

4

5

6

7

8

SXSSFWorkbook workbook = new SXSSFWorkbook();

try {

                workbook = this.getSheetsList(notQualifiedSumDeptCountVos, locale);

                return workbook;

            } catch (Exception e) {

                log.error("错误",e);

                return null;

            }

1

2

3

4

5

6

7

8

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;

    }

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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);

        }

    }

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

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);

    }

  }

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

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());

    }

  }

}