springboot 集成 EasyExcel进行导出/填充操作

目录

一、EasyExcel介绍:

常用注解

二、写Excel

1、效果图

2、导入pom依赖

3、创建自定义注解

4、编写自定义单元格合并策略

5、创建导出实体类

6、编写业务逻辑

三、填充Excel

1、编写excel模版信息

2、根据模版信息创建对应的实体类

3、在业务层给模版参数赋值

---

一、EasyExcel介绍:

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

官方文档地址:EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

常用注解

注解	涵义
@ContentRowHeight	设置行高 -1为自动
@HeadRowHeight	设置标题高度
@ExcelProperty	注解中有三个参数value,index,converter分别代表列明，列序号，数据转换方式
@ColumnWidth	设置列宽度的注解,注解中只有一个参数value，value的单位是字符长度，最大可以设置255个字符
@ContentFontStyle	用于设置单元格内容字体格式的注解

二、写Excel

1、效果图

2、导入pom依赖

        <!--EasyExcel相关依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

3、创建自定义注解

package com.qz.biz.modular.materials.annotation;

import java.lang.annotation.*;

/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 * @author lihao
 * @date 2023/5/20 16:50
 **/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;

    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
}

4、编写自定义单元格合并策略

package com.qz.biz.modular.materials.config;

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.qz.biz.modular.materials.annotation.CustomMerge;
import org.apache.poi.ss.usermodel.Cell;
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;

/**
 * @author lihao
 * @create 2023-05-20 16:51
 * @desc 自定义单元格合并策略
 **/
public class CustomMergeStrategy implements RowWriteHandler {

    /**
     * 主键下标
     */
    private Integer pkIndex;

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

    /**
     * DTO数据类型
     */
    private Class<?> elementType;

    public CustomMergeStrategy(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(1);

        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
            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(1);
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

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

        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

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

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                if (null == theCell) {
                    continue;
                }
                // 将字段和excel的表头匹配上
                if (easyExcelAnno.value()[1].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }

                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }

        // 没有指定主键，则异常
        if (null == this.pkIndex) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }

    }


}

5、创建导出实体类

package com.qz.biz.modular.materials.result;

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 com.qz.biz.modular.materials.annotation.CustomMerge;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.math.BigDecimal;
import java.net.URL;

/**
 * @author lihao
 * @create 2023-05-20 16:45
 * @desc 采购单导出
 **/
@ContentRowHeight(30)
@HeadRowHeight(30)
@Data
@EqualsAndHashCode
public class PurchaseDetailExcelResult {

    /**
     * 采购单据编号
     */
    @ExcelProperty(value = {"采购单据编号"})
    @ColumnWidth(15)
    @CustomMerge(needMerge = true, isPk = true)
    private String receiptNum;

    /**
     * 发起人
     */
    @ExcelProperty(value = {"发起人"})
    @ColumnWidth(12)
    @CustomMerge(needMerge = true)
    private String initiator;

    @ExcelProperty(value = {"操作时间"})
    @ColumnWidth(20)
    @CustomMerge(needMerge = true)
    private String createTime;

    /**
     * 采购人
     */
    @ExcelProperty(value = {"采购人"})
    @ColumnWidth(12)
    @CustomMerge(needMerge = true)
    private String purchaseName;


    @ExcelProperty(value = {"采购明细信息", "模型号"})
    @ColumnWidth(20)
    private String modelNo;

    @ExcelProperty(value = {"采购明细信息", "模型缩略图"})
    @ColumnWidth(10)
    private URL cover;

    @ExcelProperty(value = {"采购明细信息", "物料号"})
    @ColumnWidth(10)
    private String materialsNo;

    @ExcelProperty(value = {"采购明细信息", "材料名称"})
    @ColumnWidth(20)
    private String materialsName;

    @ExcelProperty(value = {"采购明细信息", "材料规格"})
    @ColumnWidth(20)
    private String materialsSpe;

    @ExcelProperty(value = {"采购明细信息", "采购类型"})
    @ColumnWidth(13)
    private String materialType;

    @ExcelProperty(value = {"采购明细信息", "采购数量"})
    @ColumnWidth(15)
    private Double purchaseAmount;

    @ExcelProperty(value = {"采购明细信息", "采购单价"})
    @ColumnWidth(15)
    private BigDecimal price;

    @ExcelProperty(value = {"采购明细信息", "采购状态"})
    @ColumnWidth(15)
    private String purchaseState;

    @ExcelProperty(value = {"采购明细信息", "预计入库时间"})
    @ColumnWidth(20)
    private String purchaseEndtime;

    @ExcelProperty(value = {"采购明细信息", "已入库数量"})
    @ColumnWidth(15)
    private Double storageAmount;

    @ExcelProperty(value = {"采购明细信息", "原下料信息"})
    @ColumnWidth(20)
    private String materialsInfo;

    @ExcelProperty(value = {"采购明细信息", "未入库数量"})
    @ColumnWidth(15)
    private Double notStorageAmount;

    @ExcelProperty(value = {"采购明细信息", "备注"})
    @ColumnWidth(20)
    private String note;



}

6、编写业务逻辑

public void exportExcel(PurchaseNoteExcelParam param, HttpServletResponse response) throws IOException {
        //文件名称
        String rawFileName = "";
        //获取需要导出的列表
        List<PurchaseNoteListReslut> detailList = this.queryByList(param);
        List<PurchaseDetailExcelResult> orderDataList = convert(detailList);
        setExcelRespProp(response, rawFileName);
        EasyExcel.write(response.getOutputStream())
                .head(PurchaseDetailExcelResult.class)
                .registerWriteHandler(new CustomMergeStrategy(PurchaseDetailExcelResult.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(rawFileName)
                .doWrite(orderDataList);
    }

    /**
     * 设置excel下载响应头属性
     */
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(rawFileName + ".xlsx", "UTF-8");
            // 设置Content-Disposition响应头
            response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\";filename*=utf-8''" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Access-Control-Allow-Headers", "Content-Disposition");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }


    private List<PurchaseDetailExcelResult> convert(List<PurchaseNoteListReslut> detailList) {
        List<PurchaseDetailExcelResult> result = null;
        try {
            result = new ArrayList<>();
            for (PurchaseNoteListReslut detail : detailList) {
                PurchaseDetailExcelResult result1 = new PurchaseDetailExcelResult();
                //填充表头数据
                result1.setReceiptNum(detail.getReceiptNum());
                result1.setInitiator(detail.getInitiator());
                result1.setCreateTime(detail.getCreateTime());
                result1.setReceiptNum(detail.getReceiptNum());
                result1.setPurchaseName(detail.getPurchaseName());
                //填充明细
                for (PurchaseNoteDetail noteDetail : detail.getDetailList()) {
                    result1.setModelNo(noteDetail.getModelNo());
                    result1.setCover(ObjectUtil.isEmpty(noteDetail.getCover()) ? null : new URL(noteDetail.getCover()));
                    result1.setMaterialsName(ObjectUtil.isEmpty(noteDetail.getMaterialsNamePur()) ? noteDetail.getMaterialsName() : noteDetail.getMaterialsNamePur());
                    result1.setMaterialsNo(noteDetail.getMaterialsNo());
                    result1.setMaterialsSpe(ObjectUtil.isEmpty(noteDetail.getMaterialsSpePur()) ? noteDetail.getMaterialsSpe() : noteDetail.getMaterialsSpePur());
                    result1.setMaterialType(noteDetail.getMaterialType() == 1 ? "原材料" : noteDetail.getMaterialType() == 2 ? "消耗品材料" : "刀具");
                    result1.setPurchaseAmount(ObjectUtil.isEmpty(noteDetail.getPurchaseAmountPur()) ? noteDetail.getPurchaseAmount() : noteDetail.getPurchaseAmount());
                    result1.setPrice(noteDetail.getPrice());
                    result1.setPurchaseState(noteDetail.getPurchaseState());
                    result1.setPurchaseEndtime(noteDetail.getPurchaseEndtime());
                    result1.setStorageAmount(noteDetail.getStorageAmount());
                    result1.setMaterialsInfo(noteDetail.getMaterialsName() + " " + noteDetail.getMaterialsSpe());
                    result1.setNotStorageAmount(result1.getPurchaseAmount() - result1.getStorageAmount());
                    result1.setNote(noteDetail.getNote());
                    result.add(result1);
                }
            }
        } catch (MalformedURLException e) {
            e.printStackTrace();
        }
        return result;
    }

三、填充Excel

1、编写excel模版信息

模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替

{} 代表普通变量 {.} 代表是list的变量

参考模版信息:

2、根据模版信息创建对应的实体类

package com.qz.biz.modular.order.TDO;

import lombok.Data;

/**
 * @author lihao
 * @create 2023-05-31 10:48
 * @desc 销售合同购物车列表
 **/
@Data
public class ShopCarTDO {

    /**
     * 名称
     **/
    private String name;

    /**
     * 模型号
     **/
    private String modelNo;

    /**
     * 规格
     **/
    private String modelSpe;

    /**
     * 单位
     **/
    private String unit;

    /**
     * 数量
     **/
    private Integer num;


    /**
     * 含税单价
     **/
    private String price;

    /**
     * 含税金额
     **/
    private String totalPrice;


    /**
     * 备注
     **/
    private String note;



}

package com.qz.biz.modular.order.TDO;

import lombok.Data;

import java.math.BigDecimal;

/**
 * @author lihao
 * @create 2023-05-31 10:55
 * @desc 销售合同其他参数
 **/
@Data
public class OtherInfoTDO {

    /**
     * 合同号
     **/
    private String number;

    /**
     * 需方
     **/
    private String demandName;



    /**
     * 电话
     **/
    private String phone;


    /**
     * 统一社会信用代码
     **/
    private String SCCode;


    /**
     * 开户银行
     **/
    private String bank;


    /**
     * 账号
     **/
    private String account;


    /**
     * 签约日期
     **/
    private String date;


    /**
     * 地址
     **/
    private String address;

    /**
     * 总金额
     **/
    private BigDecimal total;

    /**
     * 总金额大写
     **/
    private String total1;


}

3、在业务层给模版参数赋值

我这边采用的是通过url生成一个文件流进行操作,官网提供的是通过读取本地文件进行填充操作。

public void getContract(String orderNo, HttpServletResponse response) {
        //查询订单信息
        TotalOrder totalOrder = orderService.getOne(new LambdaQueryWrapper<TotalOrder>().eq(TotalOrder::getOrderNo,orderNo));
        if(ObjectUtil.isEmpty(totalOrder)){
            throw new CommonException("找不到订单信息!");
        }
        //总金额
        BigDecimal total = BigDecimal.ZERO;
        //查询购物车信息
        List<ShopCarTDO> tdoList = new ArrayList<>();
        JSONObject jsonObject = JSONObject.parseObject(totalOrder.getShopCarId());
        Iterator<String> iterator = jsonObject.keySet().iterator();
        while (iterator.hasNext()){
            String index = iterator.next();
            String id = jsonObject.getString(index);
            ShopCar3D shopCar3D = shopCar3DService.getById(id);
            if(ObjectUtil.isEmpty(shopCar3D)){
                log.error("============>未找到购物车id:{} 信息!",id);
                throw new CommonException("未找到订单模型信息!");
            }
            ShopCarTDO carTDO = new ShopCarTDO();
            carTDO.setName(shopCar3D.getFileName());
            carTDO.setModelNo(shopCar3D.getShopModeNo());
            carTDO.setModelSpe(shopCar3D.getMinBox());
            carTDO.setUnit("件");
            carTDO.setNum(shopCar3D.getNum());
            carTDO.setPrice(shopCar3D.getPrice());
            carTDO.setTotalPrice(shopCar3D.getTotalPrice());
            tdoList.add(carTDO);
            total = total.add(new BigDecimal(carTDO.getTotalPrice()));
        }


        //查询开票信息
        Bill bill = JSON.toJavaObject(billApi.getBillByIdForJson(totalOrder.getBillId()),Bill.class);
        OtherInfoTDO otherInfo = new OtherInfoTDO();
        otherInfo.setNumber(RandomUtil.randomNumbers(6));
        otherInfo.setDemandName(bill.getBillName());
        otherInfo.setPhone(bill.getCompanyPhone());
        otherInfo.setSCCode(bill.getDutyParagraph());
        otherInfo.setBank(bill.getOpeningBank());
        otherInfo.setAccount(bill.getBillAccount());
        otherInfo.setAddress(bill.getCompanyAddress());
        otherInfo.setTotal(total);
        otherInfo.setTotal1(this.upperCase(total.doubleValue()));


        //获取response
        this.getHttpServletResponse(response,"销售合同");

        try (InputStream inputStream = this.getInputStreamByUrl("https://********/20235311663755022951505922.xlsx");
             ServletOutputStream outputStream = response.getOutputStream()) {
            //设置输出流和模板信息
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //开启自动换行,自动换行表示每次写入一条list数据是都会重新生成一行空行,此选项默认是关闭的,需要提前设置为true
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(tdoList, fillConfig, writeSheet);
            excelWriter.fill(otherInfo, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }



    }

    /**
     * 根据地址获得数据的输入流
     * @param strUrl 网络连接地址
     * @return url的输入流
     */
    public static InputStream getInputStreamByUrl(String strUrl){
        HttpURLConnection conn = null;
        try {
            URL url = new URL(strUrl);
            conn = (HttpURLConnection)url.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(20 * 1000);
            final ByteArrayOutputStream output = new ByteArrayOutputStream();
            IOUtils.copy(conn.getInputStream(),output);
            return  new ByteArrayInputStream(output.toByteArray());
        } catch (Exception e) {
            log.error(e+"");
        }finally {
            try{
                if (conn != null) {
                    conn.disconnect();
                }
            }catch (Exception e){
                log.error(e+"");
            }
        }
        return null;
    }


    private void getHttpServletResponse(HttpServletResponse response, String fileName) {
        response.setContentType("application/octet-stream");
        response.setHeader("content-type", "application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Access-Control-Allow-Headers","Content-Disposition");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            fileName = URLEncoder.encode("销售合同", "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
    }

    /**
     * 金额转大写
     * @author lihao
     * @date 2023/5/31 11:36
     * @param money 金额
     * @return 大写金额
     **/
    private String upperCase(double money){
        String[] upNum = {"零","壹","贰","叁","肆","伍","陆","柒","捌","玖"};
        String[] danwei = {"圆","拾","佰","仟","万","拾","佰","仟","亿","拾","佰","仟"};
        //取消科学记数法
        NumberFormat numFormat = NumberFormat.getInstance();
        numFormat.setMaximumFractionDigits(2);//设置小数位个数
        numFormat.setGroupingUsed(false);//取消科学技术发
        String formatNum = numFormat.format(money);
        String strmoney = formatNum + "";//浮点型转为字符型
        String lastUpNum = "null"; //用于存放上个参数的值
        String result = "";//返回的结果
        String[] split = strmoney.split("\\.");
        String strMoney = split[0];
        String point = "";
        //小数部分取值处理。
        if(split.length>1){
            point = split[1];
            if(point.length()==1){
                point = point.concat("0");
            }
        }else {
            point = "0";
        }
        //大于12位就直接返回。
        int moneyLen = strMoney.length();
        if(money==0){
            return "零圆整";
        }
        if(moneyLen>12){
            return "金额："+money+"元，超出大写转换范围。最大金额：999999999999.99元";
        }
        //整数(integer)部分处理。
        if(!"0".equals(strMoney)){
            for (int i = 0; i < moneyLen; i++) {
                String strNum = strMoney.charAt(i)+"";
                int singleNum = Integer.parseInt(strNum);
                String upSingleNum = upNum[singleNum];
                //上一为不等于0的情况
                if(!"零".equals(lastUpNum)){
                    if(!"零".equals(upSingleNum)){
                        result = result.concat(upSingleNum).concat(danwei[moneyLen-i-1]);
                    }else
                        //为零但是在万、亿位上要加单位 (moneyLen-i)==9 指的是单位：亿。  (moneyLen-i)==5指的是单位：万
                        if( (moneyLen-i)==5 || (moneyLen-i)==9 ){
                            lastUpNum="";
                        }else {
                            result=result.concat(upSingleNum);
                        }
                }
                //上一位为0的情况
                if("零".equals(lastUpNum) && !"零".equals(upSingleNum)){
                    result = result.concat(upSingleNum).concat(danwei[moneyLen-i-1]);
                }
                //捕捉上一位数（lastUpNum）为零的情况做优化。
                if((moneyLen-i)==5 || (moneyLen-i)==9 ){
                    //排除加单位时前面为"零"的情况。如：两百零万
                    if("零".equals(lastUpNum)||"null".equals(lastUpNum)){
                        result = result.substring(0,result.length()-1);
                    }
                    if(!result.endsWith("亿")){
                        result = result.concat(danwei[moneyLen-i-1]);
                    }
                    lastUpNum="";
                }else {
                    //把当前大写数字复制给 lastUpNum 用于下次判断
                    lastUpNum = upSingleNum;
                }
            }
            //对几万元整和几亿元整(result:五万零或者五亿零零)做优化。
            result=result.replaceAll("零零","零");
            if(result.endsWith("零")){
                String substring = result.substring(0,result.length() - 1);
                result = substring;
            }
            result = result.concat("圆");
            result = result.replaceAll("圆圆","圆");
            result = result.replaceAll("万万","万");

        }

        //小数(point)部分处理
        if("0".equals(point)){
            result =  result+"整";
        }else {
            //去 整
//            if(result.endsWith("整")){
//                result = result.substring(0,result.length()-1);
//            }
            if((point.charAt(0)+"").equals("0")){
                result = result.concat(upNum[Integer.parseInt(point.charAt(1)+"")]+"分");
            }else if((point.charAt(1)+"").equals("0")){
                result = result.concat(upNum[Integer.parseInt(point.charAt(0)+"")]+"角");
            }else {
                result = result.concat(upNum[Integer.parseInt(point.charAt(0)+"")]+"角").concat(upNum[Integer.parseInt(point.charAt(1)+"")]+"分");
            }
        }
        return result;
    }