使用EasyExcel实现模板导出Excel数据并合并单元格_Java

需求

数据库里的主表+明细表，联查出数据并导出excel，合并主表数据的单元格。

代码

controller

    @postmapping("export")
    @apioperation(value = "导出数据")
    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 = "明细_" + 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("明细_" + 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条数据，不然后面数据太多了

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("总箱数")
    @excelproperty(index = 3)
    @customrowmerge(needmerge = true)
    private integer totalamount;

    @apimodelproperty("标")
    @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("箱数")
    @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 {
    /**
     * 主键下标集合
     */
    private list<integer> pkcolumnindex = new arraylist<>();

    /**
     * 需要合并的列的下标集合
     */
    private list<integer> needmergecolumnindex = new arraylist<>();

    /**
     * dto数据类型
     */
    private class<?> elementtype;

    public customrowmergestrategy(class<?> elementtype) {
        this.elementtype = elementtype;
    }

    @override
    public void afterrowdispose(writesheetholder writesheetholder, writetableholder writetableholder, row row, integer relativerowindex, boolean ishead) {
        // 如果是标题,则直接返回
        if (ishead) {
            return;
        }

        // 获取当前sheet
        sheet sheet = writesheetholder.getsheet();

        // 获取标题行
        row titlerow = sheet.getrow(0);

        if (pkcolumnindex.isempty()) {
            this.lazyinit(writesheetholder);
        }

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getrownum() <= 1) {
            return;
        }
        // 获取上一行数据
        row lastrow = sheet.getrow(row.getrownum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        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);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyinit(writesheetholder writesheetholder) {

        // 获取当前sheet
        sheet sheet = writesheetholder.getsheet();

        // 获取标题行
        row titlerow = sheet.getrow(0);
        // 获取dto的类型
        class<?> eletype = this.elementtype;

        // 获取dto所有的属性
        field[] fields = eletype.getdeclaredfields();

        int i = 0;
        // 遍历所有的字段，因为是基于dto的字段来构建excel，所以字段数 >= excel的列数
        for (field thefield : fields) {
            // 获取@excelproperty注解，用于获取该字段对应在excel中的列的下标
            excelproperty easyexcelanno = thefield.getannotation(excelproperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyexcelanno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            customrowmerge custommerge = thefield.getannotation(customrowmerge.class);

            // 没有@custommerge注解的默认不合并
            if (null == custommerge) {
                continue;
            }

            // 判断是否有主键标识
            if (custommerge.ispk()) {
                pkcolumnindex.add(i);
            }

            // 判断是否需要合并
            if (custommerge.needmerge()) {
                needmergecolumnindex.add(i);
            }
            i++;
        }

        // 没有指定主键，则异常
        if (pkcolumnindex.isempty()) {
            throw new illegalstateexception("使用@custommerge注解必须指定主键");
        }

    }
}

效果图

拓展

可以增加居中策略

可以通过 easyexcel 的 writehandler 或 abstractcellstylestrategy 来设置 excel 单元格内容的 水平居中 和 垂直居中

使用 writehandler 自定义单元格样式

你可以创建一个继承自 abstractcellstylestrategy 或 abstractcellwritehandler 的类，设置单元格样式。

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) {
        // 如果你也希望表头居中，可以在这里设置
        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();

        // 设置水平居中
        cellstyle.setalignment(horizontalalignment.center);

        // 设置垂直居中
        cellstyle.setverticalalignment(verticalalignment.center);

        // 可选：自动换行
        cellstyle.setwraptext(true);

        cell.setcellstyle(cellstyle);
    }
}

注册样式策略到导出逻辑中

excelwriter excelwriter = easyexcel.write(bos)
    .registerwritehandler(new centercellstylestrategy()) // 设置居中样式
    .registerwritehandler(new customrowmergestrategy(arrays.aslist(
        "containerno", "orderno", "totalamount", "sortnote", "consigneename"
    )))
    .withtemplate(inputstream)
    .build();

如果你只想对某些列设置居中（可选）

你可以修改 setcellstyle 方法，根据 cell.getcolumnindex() 判断是否对某些列应用居中

private void setcellstyle(cell cell) {
    workbook workbook = cell.getsheet().getworkbook();
    cellstyle cellstyle = workbook.createcellstyle();

    // 只对第 0 列（柜号）和第 2 列（登记总箱数）设置居中
    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 模板，并希望保留模板样式

你可以这样设置：

// 从模板中读取样式，避免覆盖原有样式
cellstyle originalstyle = cell.getcellstyle();

cellstyle newstyle = workbook.createcellstyle();
newstyle.clonestylefrom(originalstyle); // 复制原样式
newstyle.setalignment(horizontalalignment.center);
newstyle.setverticalalignment(verticalalignment.center);
newstyle.setwraptext(true);

cell.setcellstyle(newstyle);

到此这篇关于使用easyexcel实现模板导出excel数据并合并单元格的文章就介绍到这了,更多相关easyexcel模板导出excel数据内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！