EasyExcel自定义策略导出-合并单元格-设置表头和内容格式

最新推荐文章于 2025-03-29 11:01:04 发布

Gnemeng

最新推荐文章于 2025-03-29 11:01:04 发布

阅读量3.7k

点赞数 16

本文链接：https://blog.youkuaiyun.com/wmengnemw/article/details/141426333

版权

1. 导出效果图

数据为主从表一对多，实现主表内容合并，从表内容不变

2. easyexcel 引入

官方文档：https://easyexcel.opensource.alibaba.com/

2.1 引入easyexcel 依赖包

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
		</dependency>

2.2 创建ExcelEntity 实体类

package com.cn.entity.export;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

@EqualsAndHashCode
@HeadRowHeight(35)// 标题行高度
@ContentRowHeight(25)// 内容行高度
@ColumnWidth(30)// 默认列宽
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelEntity {
	
	@ExcelProperty(value = "单据编号")
	private String djbh;
    @ColumnWidth(15)// 设置列宽
	@ExcelProperty(value = "合计金额")
	private String hjje;
   
   	// 下方为从表数据
    @ExcelProperty(value = {"明细", "商品名称"})
    private String spmc;
    @ColumnWidth(15)
    @ExcelProperty(value = {"明细", "数量"})
    private String sl;
    @ColumnWidth(15)
    @ExcelProperty(value = {"明细", "单价（不含税）"})
    private String dj;
    @ColumnWidth(15)
    @ExcelProperty(value = {"明细", "金额（不含税）"})
    private String je;
}

2.3 EasyExcelUtils 工具类

Merge：生成合并区（分组导出使用，因为最终使用的分组到处所以写在了EasyExcelUtils具类里）
CellStyle ：设置标题格式、内容格式

package com.cn.entity.export;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;

public class EasyExcelUtils {
    
	public static class Merge {
		/**
		 * 生成合并区
		 * @param <T>
		 * 
		 * @param detailList 数据列表
		 * @param mergeStartline
		 * @param mergeStartColumn 合并开始列的索引
		 * @param mergeEndColumn 合并结束列的索引
		 * @return 合并区
		 */
		public static <T> List<CellRangeAddress> createCellRange(LinkedHashMap<Object, List<T>> classList, int mergeStartline, int mergeStartColumn, int mergeEndColumn) {

		    List<CellRangeAddress> rangeCellList = new ArrayList<>();
		    
	        int currentRow = mergeStartline;
	        
	        for (Map.Entry<Object, List<T>> classEntry : classList.entrySet()) {
	            List<T> classStudents = classEntry.getValue();
	            int classSize = classStudents.size();

	            if (classSize > 1) {
	            	for (int i=mergeStartColumn; i < mergeEndColumn; i++) {
	    		        rangeCellList.add(new CellRangeAddress(currentRow, currentRow + classSize - 1, i, i));
	    		     }
	            }
	            currentRow += classSize;
	        }
		    return rangeCellList;
		  }
	}
	
    public static class CellStyle {
    	/**
         * 设置头样式
         * @return
         */
        public static WriteCellStyle getHeadStyle() {
            // 创建一个新的 WriteCellStyle 对象
            WriteCellStyle style = new WriteCellStyle();

            // 设置水平居中对齐
            style.setHorizontalAlignment(HorizontalAlignment.CENTER);

            // 设置垂直居中对齐
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            // 设置背景色
            style.setFillBackgroundColor(IndexedColors.ROYAL_BLUE.getIndex());
            
            // 设置字体
            WriteFont font = new WriteFont();
            font.setFontHeightInPoints((short) 10);
            font.setColor(IndexedColors.WHITE.getIndex());
            style.setWriteFont(font);
            return style;
        }

        /**
         * 设置内容样式
         * @return
         */
        public static WriteCellStyle getContentStyle() {
            // 创建一个新的 WriteCellStyle 对象
            WriteCellStyle style = new WriteCellStyle();

            // 设置水平居中对齐
            style.setHorizontalAlignment(HorizontalAlignment.CENTER);

            // 设置垂直居中对齐
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            // 设置边框
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            
            // 设置字体
            WriteFont font = new WriteFont();
            font.setFontHeightInPoints((short) 10);
            style.setWriteFont(font);
            
            return style;
        }
    }
}

参考：https://blog.youkuaiyun.com/baidu_35536997/article/details/135850846

3. EXCEL合并导出

3.1 分组导出（效率高，推荐！）

查出数据后按照合并自动进行分组，每组数量就是合并区域大小，合并区域位置可以通过行数累加来定位。因此，写出Excel前就可以预知那些合并区域。如果在创建sheet页时就将这些区域一并创建，写出时就不用关注单元格合并了。

参考：https://blog.youkuaiyun.com/weiwosuoai/article/details/141338421

3.1.1 预创建合并区：实现SheetWriteHandler接口，重写afterSheetCreate()，将合并区域加入到sheet中。

package com.cn.entity.export;

import java.util.Collections;
import java.util.List;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

/**
 * 添加合并区Handler
 */
public class AddCellRangeWriteHandler implements SheetWriteHandler {

	private final List<CellRangeAddress> rangeCellList;

	public AddCellRangeWriteHandler(List<CellRangeAddress> rangeCellList) {
		this.rangeCellList = (rangeCellList == null) ? Collections.emptyList() : rangeCellList;
	}

	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		Sheet sheet = writeSheetHolder.getSheet();
		for (CellRangeAddress cellRangeAddress : this.rangeCellList) {
			sheet.addMergedRegionUnsafe(cellRangeAddress);
		}
	}
}

3.1.2 exportExcel 导出方法（Service层）

public int exportExcel() throws IOException {
	
	// 获取数据
	List<ExcelEntity> list = queryExcelEntityList(); // 获取查询数据（省略模拟数据查询）
	Assert.notNull(list, "导出数据不能为空");
	
	log.info("分组导出开始："+new Date(System.currentTimeMillis()).toString());
	// 创建文件
	FileOutputStream outputStream22 = new FileOutputStream(new File("D:/invoice分组导出.xlsx"));
    // 按照单据编号进行分组
    LinkedHashMap<Object, List<InvoiceExcel>> classList = list.stream().collect(Collectors.groupingBy(item -> item.getDjbh(),
		LinkedHashMap::new, Collectors.toList()));
    // 获取合并区（1-第0行为标题行，从第一行开始合并；0-从第0列开始合并；1-第一列后结束合并）
    List<CellRangeAddress> rangeCellList = EasyExcelUtils.Merge.createCellRange(classList,1, 0, 1);
    // 导出EXCEL
    EasyExcel.write(outputStream22, InvoiceExcel.class).sheet("Sheet1")
        .registerWriteHandler(new HorizontalCellStyleStrategy(EasyExcelUtils.CellStyle.getHeadStyle(), EasyExcelUtils.CellStyle.getContentStyle()))
        .registerWriteHandler(new AddCellRangeWriteHandler(rangeCellList))
        .doWrite(list);
	
	log.info("分组导出结束："+new Date(System.currentTimeMillis()).toString());
}

3.2 遍历单元格合并导出（效率低，一行数据需要多次读取）

3.2.1 ExcelMergeCustomerCellHandler 合并策略

package com.cn.entity.export;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMergeCustomerCellHandler implements CellWriteHandler {
    /**
     * 一级合并的列，从0开始算
     */
    private int[] mergeColIndex;

    /**
     * 从指定的行开始合并，从0开始算
     */
    private int mergeRowIndex;

    /**
     * 在单元格上的所有操作完成后调用，遍历每一个单元格，判断是否需要向上合并
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 获取当前单元格行下标
        int currRowIndex = cell.getRowIndex();
        // 获取当前单元格列下标
        int currColIndex = cell.getColumnIndex();
        // 判断是否大于指定行下标，如果大于则判断列是否也在指定的需要的合并单元列集合中
        if (currRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColIndex.length; i++) {
                if (currColIndex == mergeColIndex[i]) {
                    if(currColIndex <= 18){
                        // 一级合并唯一标识
                        Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
                        Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
                        // 判断两条数据的是否是同一集合，只有同一集合的数据才能合并单元格
                        if(preLevelOneCode.equals(currLevelOneCode)){
                            // 如果都符合条件，则向上合并单元格
                            mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
                            break;
                        }
                    }else{
                        // 一级合并唯一标识
                        Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
                        Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
                        // 二级合并唯一标识
                        Object currLevelTwoCode = cell.getRow().getCell(19).getStringCellValue();
                        Object preLevelTwoCode = cell.getSheet().getRow(currRowIndex - 1).getCell(19).getStringCellValue();
                        if(preLevelOneCode.equals(currLevelOneCode)&&preLevelTwoCode.equals(currLevelTwoCode)){
                            // 如果都符合条件，则向上合并单元格
                            mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
                            break;
                        }
                    }
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder 表格处理句柄
     * @param cell             当前单元格
     * @param currRowIndex     当前行
     * @param currColIndex     当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int currRowIndex, int currColIndex) {
        // 获取当前单元格数值
        Object currData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 获取当前单元格正上方的单元格对象
        Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
        // 获取当前单元格正上方的单元格的数值
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数值与其正上方单元格的数值比较
        if (preData.equals(currData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            // 当前单元格的正上方单元格是否是已合并单元格
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress address = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (address.isInRange(currRowIndex - 1, currColIndex)) {
                    sheet.removeMergedRegion(i);
                    address.setLastRow(currRowIndex);
                    sheet.addMergedRegion(address);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

3.2.2 exportExcel 导出方法（Service层）

public int exportExcel() throws IOException {
	
	// 获取数据
	List<ExcelEntity> list = queryExcelEntityList(); // 获取查询数据（省略模拟数据查询）
	Assert.notNull(list, "导出数据不能为空");
	
	log.info("合并导出开始："+new Date(System.currentTimeMillis()).toString());
	// 创建文件
	FileOutputStream outputStream22 = new FileOutputStream(new File("D:/invoice合并导出.xlsx"));
    // 按照单据编号进行分组
    LinkedHashMap<Object, List<InvoiceExcel>> classList = list.stream().collect(Collectors.groupingBy(item -> item.getDjbh(),
		LinkedHashMap::new, Collectors.toList()));
		
    // 导出EXCEL
    // ExcelMergeCustomerCellHandler策略（[0，1]-数组表示合并的列；1-从第一行开始合并；）
	EasyExcel.write(outputStream2, InvoiceExcel.class).sheet(0)
        .registerWriteHandler(new HorizontalCellStyleStrategy(EasyExcelUtils.CellStyle.getHeadStyle(), EasyExcelUtils.CellStyle.getContentStyle()))
        .registerWriteHandler(new ExcelMergeCustomerCellHandler(
        		new int[]{0, 1}, 1))
        .doWrite(list);
	
	log.info("合并导出结束："+new Date(System.currentTimeMillis()).toString());
}