【easypoi 模板导出嵌套 list 问题】

厕所博士

已于 2024-09-29 00:17:17 修改

阅读量9.7k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： java java基础 javaWeb 文章标签： easypoi list

于 2022-12-09 12:50:37 首次发布

本文链接：https://blog.csdn.net/x123453316/article/details/128248716

java基础同时被 3 个专栏收录

13 篇文章

订阅专栏

java

9 篇文章

订阅专栏

javaWeb

3 篇文章

订阅专栏

本文解决使用EasyPoi导出包含多个列表数据的Excel文件时遇到的问题，特别是共享单车数据未正确显示的情况，并提供了处理合并单元格边框及确保数据格式正确的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

easypoi 模板导出多 list 问题

1背景

1背景

没钱的小明去上海旅游，交通方式选择共享单车或者地铁，要统计每天小明两种交通方式费用数据如图：

1.1 espoi 模板定义

在这里插入图片描述

1.2 导出结果

在这里插入图片描述

1.3发现共享单车的数据没有显示，手动操作取消共享单车的单元格合并

在这里插入图片描述

1.4手动取消单元格合并后，数据正常了，再把框线画好

在这里插入图片描述

1.5 代码操作：用 esaypoi 处理到1.2，用基础 poi 代码处理 1.3和 1.4

		// (1)处理共享单车详细数据不显示；
		// (2)处理合并单元格边框实线
		//(3)处理共享单车和地铁没有边框实线

private void myRest(Workbook book) {
		Sheet firstSheet = book.getSheetAt(0);
		List<CellRangeAddress> mergedRegions = firstSheet.getMergedRegions();
		List<Integer> removeMergedRegionIndexList = new ArrayList<>();

		// 处理共享单车详细数据不显示；
		for (int i = 0; i < mergedRegions.size(); i++) {
			CellRangeAddress mergedRegion = mergedRegions.get(i);
			int firstColumn = mergedRegion.getFirstColumn();
			int lastColumn = mergedRegion.getLastColumn();
			int firstRow = mergedRegion.getFirstRow();
			if(firstRow >= 2 && firstColumn >= 3 && lastColumn <= 5){
				removeMergedRegionIndexList.add(i);
			}

		}
		firstSheet.removeMergedRegions(removeMergedRegionIndexList);
		// (2)处理合并单元格边框实线
		for (CellRangeAddress mergedRegion : mergedRegions) {
			RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, firstSheet);
			RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, firstSheet);
			RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, firstSheet);
			RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, firstSheet);
		}
		// (3)处理共享单车和地铁没有边框实线；略，自行处理
	}

在这里插入图片描述

1.6还需要注意一点共享单车和地铁 list 不能一个有数据一个没数据，否则会导致格式错误显示

在这里插入图片描述

1.7 完整代码

在这里插入图片描述

package com.example.myeasypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.junit.Before;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author xuanjinnan
 * @date 2022/12/9 10:32
 */
public class MyComplexTemplateTest {
    List<Traffic> traffics = new ArrayList<>();

    @Test
    public void myNestedLoopTest() throws IOException {
        TemplateExportParams params = new TemplateExportParams(
                "doc/mynestedloop.xlsx");
//		params.setColForEach(true);
        Map<String,Object> value = new HashMap<>();
        value.put("list",traffics);

        Workbook book = ExcelExportUtil.exportExcel(params, value);
        // (1)处理付款明细数据不显示；
        // (2)处理合并单元格边框实线
        // (3)处理共享单车和地铁没有边框实线
        myRest(book);
        FileOutputStream fos = new FileOutputStream("D:/study/code_source/my-easypoi/src/main/resources/ExcelExportTemplateMyNestedLoop.xlsx");
        book.write(fos);
        fos.close();
    }

    private void myRest(Workbook book) {
        Sheet firstSheet = book.getSheetAt(0);
        List<CellRangeAddress> mergedRegions = firstSheet.getMergedRegions();
        List<Integer> removeMergedRegionIndexList = new ArrayList<>();

        // (1)处理付款明细数据不显示；
        for (int i = 0; i < mergedRegions.size(); i++) {
            CellRangeAddress mergedRegion = mergedRegions.get(i);
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int firstRow = mergedRegion.getFirstRow();
            if(firstRow >= 2 && firstColumn >= 3 && lastColumn <= 5){
                removeMergedRegionIndexList.add(i);
            }

        }
        firstSheet.removeMergedRegions(removeMergedRegionIndexList);
        // (2)处理合并单元格边框实线
        for (CellRangeAddress mergedRegion : mergedRegions) {
            RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, firstSheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, firstSheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, firstSheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, firstSheet);
        }
        // (3)处理共享单车和地铁没有边框实线；略，自行处理
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Traffic{
        public Traffic(String name, Date date, List<TrafficDetail> shareBikes, List<TrafficDetail> subways) {
            this.name = name;
            this.date = date;
            this.shareBikes = shareBikes;
            this.subways = subways;
        }

        private Integer id;
        private String name;
        private Date date;
        private List<TrafficDetail> shareBikes;
        private List<TrafficDetail> subways;
    }
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class TrafficDetail{
        private Integer number;
        private Integer cost;

    }
    @Before
    public void testBefore() {
        Traffic traffic1 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(4).atZone(ZoneId.systemDefault()).toInstant())
                , new ArrayList<>(Arrays.asList(new TrafficDetail(1,3),new TrafficDetail(2,4),new TrafficDetail(3,4)))
                , new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 5))));
        Traffic traffic2 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(3).atZone(ZoneId.systemDefault()).toInstant())
                , new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 3)))
                , new ArrayList<>(Arrays.asList(new TrafficDetail(1,5),new TrafficDetail(2,6),new TrafficDetail(3,7))));
        Traffic traffic3 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(2).atZone(ZoneId.systemDefault()).toInstant())
                , new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 3)))
                , new ArrayList<>(Collections.singletonList(new TrafficDetail())));
        Traffic traffic4 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(1).atZone(ZoneId.systemDefault()).toInstant())
                , new ArrayList<>(Collections.singletonList(new TrafficDetail()))
                , new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 5))));
        traffics.add(traffic1);
        traffics.add(traffic2);
        traffics.add(traffic3);
        traffics.add(traffic4);
        //设置 id
        AtomicInteger id = new AtomicInteger(1);
        traffics.forEach(traffic -> traffic.setId(id.getAndIncrement()));
    }

}

1.8 easypoi 版本

考虑有些版本不一样，把 maven 依赖也粘贴下

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>
        <!-- 建议只用start -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>