使用EasyExcel實現(xiàn)模板導(dǎo)出Excel數(shù)據(jù)并合并單元格

更新時間：2026年03月23日 09:43:57 作者：Java小王子呀

這篇文章主要為大家詳細(xì)介紹了如何使用EasyExcel實現(xiàn)模板導(dǎo)出Excel數(shù)據(jù)并合并單元格,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以了解下

需求

數(shù)據(jù)庫里的主表+明細(xì)表，聯(lián)查出數(shù)據(jù)并導(dǎo)出Excel，合并主表數(shù)據(jù)的單元格。

代碼

controller

    @PostMapping("export")
    @ApiOperation(value = "導(dǎo)出數(shù)據(jù)")
    protected void export(@ApiParam @Valid @RequestBody NewWmsExceptionCaseSearchCondition request, HttpServletResponse response) throws IOException {
        getService().export(request, response);
    }

service

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ctsfreight.oseb.common.strategy.CustomRowMergeStrategy;
import com.ctsfreight.oseb.common.utils.TokenUtil;
import com.ctsfreight.oseb.common.vo.*;
import com.ctsfreight.oseb.common.vo.excel.ExceptionExcelVo;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.core.io.ResourceLoader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.MessageFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;


    @Resource
    private ResourceLoader resourceLoader;
    private final String TEMPLATE_EXCEPTION_EXCEL_XLSX = "classpath:template/exception_excel.xlsx";


 @Override
    public void export(NewWmsExceptionCaseSearchCondition request, HttpServletResponse response) throws IOException {
        String fileName = "明細(xì)_" + LocalDateTime.now();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));

        String template = TEMPLATE_EXCEPTION_EXCEL_XLSX;
        InputStream inputStream = resourceLoader.getResource(template).getInputStream();
        File xlsx = null;
        try {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();

            List<ExceptionExcelVo> crossdockSeaFinanceVoList = baseMapper.listExceptionExcelVo(request);
            if (CollectionUtils.isNotEmpty(crossdockSeaFinanceVoList)) {
                AtomicInteger index = new AtomicInteger(0);
                AtomicReference<String> lastId = new AtomicReference<>("");
                crossdockSeaFinanceVoList.forEach(item -> {
                    String currentId = item.getId();
                    if (!lastId.get().equals(currentId)) {
                        index.set(index.get() + 1);
                        lastId.set(currentId);
                    }
                    item.setId(String.valueOf(index.get()));
                });

                ExcelWriter excelWriter = EasyExcel.write(bos).registerWriteHandler(new CustomRowMergeStrategy(ExceptionExcelVo.class))
                        .withTemplate(inputStream).build();

                WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
                excelWriter.write(crossdockSeaFinanceVoList, writeSheet);
                excelWriter.finish();
            }

            InputStreamSource inputStreamSource = new ByteArrayResource(bos.toByteArray());
            xlsx = File.createTempFile("明細(xì)_" + UUID.randomUUID(), ".xlsx");
            FileUtils.copyInputStreamToFile(inputStreamSource.getInputStream(), xlsx);
            IOUtils.copy(inputStreamSource.getInputStream(), response.getOutputStream());
        } catch (Exception e) {
            log.error("export error", e);
            throw new ApiException(ResultCode.FAULT);
        } finally {
            if (xlsx != null) {
                xlsx.delete();
            }
            inputStream.close();
        }
    }

這里的template 是放在了src/main/resources/template/delivery_export_en.xlsx

xml

    <select id="listExceptionExcelVo" resultType="com.ctsfreight.oseb.common.vo.excel.ExceptionExcelVo">
        SELECT ecs.id AS id,
               ecs.order_no       AS orderNo,
               ecs.container_no   AS containerNo,
               ecs.total_amount   AS totalAmount,
               ecsit.sort_note    AS sortNote,
               ecsit.consignee_name AS consigneeName,
               ecsit.fba_id       AS fbaId,
               ecsit.fba_number   AS fbaNumber,
               ecsit.package_num  AS packageNum
        FROM (
                 SELECT id, order_no, container_no, total_amount, create_time
                 FROM exception_case_summary
                 WHERE delete_flag = 0
                    <if test="request.summaryIdList != null and !request.summaryIdList.isEmpty()">
                        AND id IN
                            <foreach item="id" collection="request.summaryIdList" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                    </if>
                 ORDER BY create_time DESC
                     LIMIT 100
             ) ecs
                 LEFT JOIN exception_case_sorting_item ecsit
                           ON ecs.id = ecsit.exception_case_summary_id
                 WHERE ecsit.delete_flag = 0
                 ORDER BY ecs.create_time DESC;
    </select>

LIMIT 100，是為了查詢最新的100條數(shù)據(jù)，不然后面數(shù)據(jù)太多了

vo：

package com.ctsfreight.oseb.common.vo.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.ctsfreight.oseb.common.strategy.annotations.CustomRowMerge;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * <p>
 *  信息VO
 * </p>
 *
 *
 */
@Data
@Accessors(chain = true)
@ApiModel(value = "信息VO")
public class ExceptionExcelVo {

    @ApiModelProperty("主表id")
    @ExcelProperty(index = 0)
    @CustomRowMerge(needMerge = true, isPk = true)
    private String id;

    @ApiModelProperty("號")
    @ExcelProperty(index = 1)
    @CustomRowMerge(needMerge = true)
    private String containerNo;

    @ApiModelProperty("單號")
    @ExcelProperty(index = 2)
    @CustomRowMerge(needMerge = true)
    private String orderNo;

    @ApiModelProperty("總箱數(shù)")
    @ExcelProperty(index = 3)
    @CustomRowMerge(needMerge = true)
    private Integer totalAmount;

    @ApiModelProperty("標(biāo)")
    @ExcelProperty(index = 4)
    private String sortNote;

    @ApiModelProperty("")
    @ExcelProperty(index = 5)
    private String consigneeName;

    @ApiModelProperty("")
    @ExcelProperty(index = 6)
    private String fbaId;

    @ApiModelProperty("")
    @ExcelProperty(index = 7)
    private String fbaNumber;

    @ApiModelProperty("箱數(shù)")
    @ExcelProperty(index = 8)
    private Integer packageNum;

}

自定義單元格合并策略

package com.ctsfreight.oseb.common.strategy;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.ctsfreight.oseb.common.strategy.annotations.CustomRowMerge;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * 自定義單元格合并策略
 */
public class CustomRowMergeStrategy implements RowWriteHandler {
    /**
     * 主鍵下標(biāo)集合
     */
    private List<Integer> pkColumnIndex = new ArrayList<>();

    /**
     * 需要合并的列的下標(biāo)集合
     */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * DTO數(shù)據(jù)類型
     */
    private Class<?> elementType;

    public CustomRowMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是標(biāo)題,則直接返回
        if (isHead) {
            return;
        }

        // 獲取當(dāng)前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 獲取標(biāo)題行
        Row titleRow = sheet.getRow(0);

        if (pkColumnIndex.isEmpty()) {
            this.lazyInit(writeSheetHolder);
        }

        // 判斷是否需要和上一行進行合并
        // 不能和標(biāo)題合并，只能數(shù)據(jù)行之間合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 獲取上一行數(shù)據(jù)
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 將本行和上一行是同一類型的數(shù)據(jù)(通過主鍵字段進行判斷)，則需要合并
        boolean margeBol = true;
        for (Integer pkIndex : pkColumnIndex) {
            String lastKey = lastRow.getCell(pkIndex).getCellType() == CellType.STRING ? lastRow.getCell(pkIndex).getStringCellValue() : String.valueOf(lastRow.getCell(pkIndex).getNumericCellValue());
            String currentKey = row.getCell(pkIndex).getCellType() == CellType.STRING ? row.getCell(pkIndex).getStringCellValue() : String.valueOf(row.getCell(pkIndex).getNumericCellValue());
            if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) {
                margeBol = false;
                break;
            }
        }
        if (margeBol) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                        needMerIndex, needMerIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主鍵下標(biāo)和需要合并字段的下標(biāo)
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 獲取當(dāng)前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 獲取標(biāo)題行
        Row titleRow = sheet.getRow(0);
        // 獲取DTO的類型
        Class<?> eleType = this.elementType;

        // 獲取DTO所有的屬性
        Field[] fields = eleType.getDeclaredFields();

        int i = 0;
        // 遍歷所有的字段，因為是基于DTO的字段來構(gòu)建excel，所以字段數(shù) >= excel的列數(shù)
        for (Field theField : fields) {
            // 獲取@ExcelProperty注解，用于獲取該字段對應(yīng)在excel中的列的下標(biāo)
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 為空,則表示該字段不需要導(dǎo)入到excel,直接處理下一個字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 獲取自定義的注解，用于合并單元格
            CustomRowMerge customMerge = theField.getAnnotation(CustomRowMerge.class);

            // 沒有@CustomMerge注解的默認(rèn)不合并
            if (null == customMerge) {
                continue;
            }

            // 判斷是否有主鍵標(biāo)識
            if (customMerge.isPk()) {
                pkColumnIndex.add(i);
            }

            // 判斷是否需要合并
            if (customMerge.needMerge()) {
                needMergeColumnIndex.add(i);
            }
            i++;
        }

        // 沒有指定主鍵，則異常
        if (pkColumnIndex.isEmpty()) {
            throw new IllegalStateException("使用@CustomMerge注解必須指定主鍵");
        }

    }
}

效果圖

拓展

可以增加居中策略

可以通過 EasyExcel 的 WriteHandler 或 AbstractCellStyleStrategy 來設(shè)置 Excel 單元格內(nèi)容的 水平居中 和 垂直居中

使用 WriteHandler 自定義單元格樣式

你可以創(chuàng)建一個繼承自 AbstractCellStyleStrategy 或 AbstractCellWriteHandler 的類，設(shè)置單元格樣式。

import com.alibaba.excel.write.handler.AbstractCellStyleStrategy;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CenterCellStyleStrategy extends AbstractCellStyleStrategy {

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        // 如果你也希望表頭居中，可以在這里設(shè)置
        setCellStyle(cell);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        setCellStyle(cell);
    }

    private void setCellStyle(Cell cell) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();

        // 設(shè)置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 設(shè)置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 可選：自動換行
        cellStyle.setWrapText(true);

        cell.setCellStyle(cellStyle);
    }
}

注冊樣式策略到導(dǎo)出邏輯中

ExcelWriter excelWriter = EasyExcel.write(bos)
    .registerWriteHandler(new CenterCellStyleStrategy()) // 設(shè)置居中樣式
    .registerWriteHandler(new CustomRowMergeStrategy(Arrays.asList(
        "containerNo", "orderNo", "totalAmount", "sortNote", "consigneeName"
    )))
    .withTemplate(inputStream)
    .build();

如果你只想對某些列設(shè)置居中（可選）

你可以修改 setCellStyle 方法，根據(jù) cell.getColumnIndex() 判斷是否對某些列應(yīng)用居中

private void setCellStyle(Cell cell) {
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle cellStyle = workbook.createCellStyle();

    // 只對第 0 列（柜號）和第 2 列（登記總箱數(shù)）設(shè)置居中
    if (cell.getColumnIndex() == 0 || cell.getColumnIndex() == 2) {
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
    } else {
        // 其他列左對齊
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    }

    cell.setCellStyle(cellStyle);
}

如果你使用的是 .xlsx 模板，并希望保留模板樣式

你可以這樣設(shè)置：

// 從模板中讀取樣式，避免覆蓋原有樣式
CellStyle originalStyle = cell.getCellStyle();

CellStyle newStyle = workbook.createCellStyle();
newStyle.cloneStyleFrom(originalStyle); // 復(fù)制原樣式
newStyle.setAlignment(HorizontalAlignment.CENTER);
newStyle.setVerticalAlignment(VerticalAlignment.CENTER);
newStyle.setWrapText(true);

cell.setCellStyle(newStyle);

到此這篇關(guān)于使用EasyExcel實現(xiàn)模板導(dǎo)出Excel數(shù)據(jù)并合并單元格的文章就介紹到這了,更多相關(guān)EasyExcel模板導(dǎo)出Excel數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: