• 手摸手系列之EasyPoi导出Excel横向遍历实战

    前言

    技术栈：
    SpringBoot 2.3.1.RELEASE
    Vue 2.x
    EasyPoi 4.4.0

    最近做前后端分离项目，遇到一个导出对账单的需求，还都是些不规则的Excel，其中一个境外飞机进口的需求如下：

    请添加图片描述

    每一列是一票台账，每行展示的是左侧表头的所列的具体的费用金额。而且每个sheet最多展示7个台账，超过的就多加一个sheet。因此我们知道，把每一列作为数据集合的一个元素，然后直接用EasyPOI的横向遍历+sheet clone功能就可轻易实现。官方文档👉[点我直达]

    一、定义导出数据实体对象ExportExcelEntityForStatement，因为跟别的导出功能共用，所以会有一些无用的字段

    import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.math.BigDecimal;

/**
 * 
    
 * 对账单表自定义导出用
 * 
    
 *
 * @author ZHANGCHAO
 * @since 2022-07-18
 */    
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class ExportExcelEntityForStatement implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 台账号
     */
    private String accountNo;
    /**
     * 提单号
     */
    private String awbNo;
    /**
     * 件数 舱单信息
     */
    private Integer packs;
    /**
     * 计费重量
     */
    private BigDecimal chargedWeight;
    /**
     * 件数/重量Pcs/Weight
     */
    private String packsChargedWeight;
    /**
     * 费用描述
     */
    private String moneyName;
    /**
     * 总价
     */
    private BigDecimal amount;
    /**
     * 税率
     */
    private BigDecimal tax;
    /**
     * 开票税率
     */
    private BigDecimal fpTax;
    /**
     * 发票税
     */
    private BigDecimal invoiceTax;
    /**
     * 合计
     */
    private BigDecimal totalAmount;
    /**
     * 备注说明
     */
    private String remarkNote;

    /**
     * 出库手续费
     */
    private BigDecimal outgoingBondedWarehouseAmount;
    /**
     * 航空运费
     */
    private BigDecimal airfreightAmount;
    /**
     * 目的港派送
     */
    private BigDecimal dDuChargeAmount;
    /**
     * 操作费
     */
    private BigDecimal handlingChargeAmount;
    /**
     * 空空中转费
     */
    private BigDecimal transferDeclarationFeeAmount;
    /**
     * 单证服务费
     */
    private BigDecimal ducomentationAmount;
    /**
     * 机场杂费
     */
    private BigDecimal terminalChargeAmount;
    /**
     * 外库费用
     */
    private BigDecimal outsideTheLibraryFeeAmount;
    /**
     * 监管仓库操作服务费
     */
    private BigDecimal bondedHandingChargeAmount;
    /**
     * 舱单信息处理费
     */
    private BigDecimal manifestFeeAmount;
    /**
     * 单证费
     */
    private BigDecimal documentFeeAmount;
    /**
     * 危品操作费
     */
    private BigDecimal handlingChargeForDgAmount;
    /**
     * 危品包装费
     */
    private BigDecimal uNPackingChargeAmount;
    /**
     * 作业单平台使用费
     */
    private BigDecimal handlingPlatformChargeAmount;
    /**
     * 制单费
     */
    private BigDecimal documentsMakingAmount;
    /**
     * 报关报检代理服务费
     */
    private BigDecimal customsClearanceFeeAmount;
    /**
     * 录单劳务费
     */
    private BigDecimal dataPutInChargeAmount;
    /**
     * 地面处理费
     */
    private BigDecimal terminalChargeInTnaAmount;
    /**
     * 国外港杂费
     */
    private BigDecimal oversesaHarbourChargeAmount;
    /**
     * 国外提货费
     */
    private BigDecimal overseasPickupChargeAmount;
    /**
     * 仓储费
     */
    private BigDecimal storageChargeAmount;
    /**
     * 装卸费
     */
    private BigDecimal loadingAndUnloadingFeeAmount;
    /**
     * 国内普货运输
     */
    private BigDecimal inlandFreightAmount;
    /**
     * 特殊货物处理费
     */
    private BigDecimal handlingChargeForSpecialGoodsAmount;
    /**
     * 叉车服务费
     */
    private BigDecimal forkliftHandlingChargesAmount;
    /**
     * 检验检测鉴定服务费
     */
    private BigDecimal dGmAmount;
    /**
     * 危品申报费
     */
    private BigDecimal declaredForDgAmount;
    /**
     * 入库手续费
     */
    private BigDecimal incomingBondedWarehouseAmount;
    /**
     * 铲车服务费
     */
    private BigDecimal forkliftServiceChargeAmount;
    /**
     * 货物查验费
     */
    private BigDecimal cargoCheckingFeeAmount;
    /**
     * 分舱单费
     */
    private BigDecimal cargoManifestChangeAmount;
    /**
     * AOG费
     */
    private BigDecimal overtimeWorkChargeAmount;
    /**
     * 代垫费
     */
    private BigDecimal reimbursedExpensesAmount;
    /**
     * 消毒、熏蒸代理费
     */
    private BigDecimal disinfectionChargeAmount;

    private String feiyongType;
    private String curr;
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211

    二、根据对账单的ID集合查询数据的xml

    <select id="exportImportOfOverseasAircraft"
            resultType="com.yorma.finance.entity.dto.ExportExcelEntityForStatement">
        SELECT
            a.ACCOUNT_NO,
            CASE

                WHEN ( c.M_BILL_NO IS NOT NULL AND c.M_BILL_NO != '' ) THEN
                    c.M_BILL_NO ELSE c.HBL
                END awbNo,
            c.PACKS,
            c.CHARGED_WEIGHT,
            CASE

                WHEN ( b.MEMO IS NOT NULL AND b.MEMO != '' ) THEN
                    b.MEMO ELSE b.`DESC`
                END moneyName,
            b.AMOUNT,
            a.MEMO remarkNote,
            b.TAX,
            b.FP_TAX,
            b.CURR,
            b.TYPE feiyongType
        FROM
            `ACCOUNT` a
                LEFT JOIN ACCOUNT_EXPENSE b ON a.ACCOUNT_NO = b.ACCOUNT_NO
                LEFT JOIN APPLY c ON c.ACCOUNT_NO = a.ACCOUNT_NO
                LEFT JOIN ACCOUNT_PUT_REL d ON d.EXP_ID = b.ID
                LEFT JOIN ACCOUNT_STATEMENT f ON f.ID = d.CLA_ID
        WHERE
            f.ID IN
        <foreach collection="idList" item="id" index="index" open="(" close=")" separator=",">
            #{id}
        foreach>
        AND b.CURR = 'CNY'
    select>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    三、主要的导出实现方法

     /**
     * 导出对账单
     * 11-境外飞机进口
     * 12-日本飞机进口
     * 13-太古进口新
     * 14-太古出口新
     *
     * @param ids        对账单id拼接串
     * @param exportType 对账单类型
     * @return com.yorma.entity.YmMsg
     * @apiNote 
         *   导出对账单
     * 
    
     * @author ZHANGCHAO 2022/8/4 13:28
     * @version 1.0
     */    
    @Override
    public YmMsg<String> exportImportOfOverseasAircraft(String ids, String exportType) {
        if (isBlank(ids)) {
            return YmMsg.error("参数[对账单ID]不能为空！");
        }
        if (isBlank(exportType)) {
            return YmMsg.error("参数[导出类型]不能为空！");
        }
        String templateUrl = "";
        if ("11".equals(exportType)) {
            templateUrl = templatesPath + "境外飞机进口.xls";
        } else if ("12".equals(exportType)) {
            templateUrl = templatesPath + "日本飞机进口.xls";
        } else if ("13".equals(exportType)) {
            templateUrl = templatesPath + "太古进口新.xls";
        } else if ("14".equals(exportType)) {
            templateUrl = templatesPath + "太古出口新.xls";
        }
        TemplateExportParams params = new TemplateExportParams(templateUrl);
        params.setColForEach(true);
        params.setSheetName(new String[]{"1"});
        List<ExportExcelEntityForStatement> exportExcelEntityForStatementList = baseMapper.exportImportOfOverseasAircraft(Arrays.asList(ids.split(",")));
        if (isEmpty(exportExcelEntityForStatementList)) {
            return YmMsg.error("未获取到数据，无法导出！");
        }
        // 类型 1应收 2应支
        String type = exportExcelEntityForStatementList.get(0).getFeiyongType();
        List<Map<String, Object>> numOneList = new ArrayList<>();
        // 处理数据！！
        List<Map<String, Object>> colList = getColList(exportExcelEntityForStatementList, exportType);
        // 每7个一组，再次分组
        List<List<Map<String, Object>>> subColList = CollUtil.split(colList, 7);
        int i = 0;
        // 此循环是分sheet的关键，subColList有几个subList就分几个sheet
        for (List<Map<String, Object>> subList : subColList) {
            i++;
            Map<String, Object> value = new HashMap<>();
            value.put("subList", subList);
            value.put("title", "太古飞机中英文进口货物对帐单Incoming goods Debit Note");
            value.put(SHEET_NAME, i); // 每个sheet的sheet名称
            numOneList.add(value);
        }
        // 反转
        Collections.reverse(numOneList); // 因为EasyPOI生成Excel后会按集合的顺序反着生成，所以咱给集合反转一下顺序。
        Map<Integer, List<Map<String, Object>>> realMap = new HashMap<>();
        realMap.put(0, numOneList);
        Workbook workbook = ExcelExportUtil.exportExcelClone(realMap, params);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        byte[] data = bos.toByteArray();
        String prefix = "";
        if ("11".equals(exportType)) {
            if ("1".equals(type)) {
                prefix = "境外飞机进口-应收对账单-";
            } else {
                prefix = "境外飞机进口-应支对账单-";
            }
        } else if ("12".equals(exportType)) {
            if ("1".equals(type)) {
                prefix = "日本飞机进口-应收对账单-";
            } else {
                prefix = "日本飞机进口-应支对账单-";
            }
        } else if ("13".equals(exportType)) {
            if ("1".equals(type)) {
                prefix = "太古进口新-应收对账单-";
            } else {
                prefix = "太古进口新-应支对账单-";
            }
        } else if ("14".equals(exportType)) {
            if ("1".equals(type)) {
                prefix = "太古出口新-应收对账单-";
            } else {
                prefix = "太古出口新-应支对账单-";
            }
        }
        String fileName = prefix + DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN) + RandomUtil.randomString(6) + ".xls";
        String xmlPath = download + File.separator + fileName;
        File file = new File(xmlPath);
        FileOutputStream fos;
        try {
            fos = new FileOutputStream(file);
            fos.write(data, 0, data.length);
            fos.flush();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        String path = xmlPath.substring(xmlPath.lastIndexOf(File.separator) + 1);
        String filePath = viewPdfPath + path;
        log.info("[exportImportOfOverseasAircraft]生成文件成功：==> " + filePath);
        return YmMsg.ok(filePath);
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119

    📌境外飞机进口、日本飞机进口、太古进口新、太古出口新：模板差不多，只是费用有所不同，其他取值和逻辑都一样。此处只是以境外飞机进口为例。

    最后是生成文件的下载地址返回给前端，前端通过nginx下载。

    代码注释很完整，可以参考注释来理解。

    四、主要的数据处理方法

    /**
 * 获取Excel数据
 *
 * @param exportExcelEntityForStatementList
 * @return java.util.List>
 * @author ZHANGCHAO
 * @date 2022/8/8 9:27
 */
private List<Map<String, Object>> getColList(List<ExportExcelEntityForStatement> exportExcelEntityForStatementList, String exportType) {
    List<Map<String, Object>> colList = new ArrayList<>();
    Map<String, List<ExportExcelEntityForStatement>> excelEntityMap = exportExcelEntityForStatementList.stream()
            .collect(Collectors.groupingBy(ExportExcelEntityForStatement::getAccountNo));
    excelEntityMap = sortMapByKey(excelEntityMap);
    if (isNotEmpty(excelEntityMap)) {
        excelEntityMap.forEach((k, v) -> {
            Map<String, Object> map = new HashMap<>();
            map.put("accountNo", k); // 台账号
            map.put("awbNo", isNotEmpty(v) ? v.get(0).getAwbNo() : ""); // 提单号
            map.put("packsChargedWeight", "0/0.00 KG"); // 件数重量
            if (isNotEmpty(v)) {
                List<String> packsChargedWeightList = new ArrayList<>(16);
                packsChargedWeightList.add(isNotEmpty(v.get(0).getPacks()) ? String.valueOf(v.get(0).getPacks()) : "0");
                packsChargedWeightList.add(isNotEmpty(v.get(0).getChargedWeight()) ? String.valueOf(v.get(0).getChargedWeight().setScale(2, RoundingMode.HALF_UP)) : "0.00");
                String packsChargedWeight = CollUtil.join(packsChargedWeightList, "/") + " KG"; // 件数重量
                map.put("packsChargedWeight", packsChargedWeight); // 件数重量
                // 处理导出对账单的各个费用数据
                dealEveryAmounts(map, v, exportType);
                // 备注说明
                map.put("remarkNote", v.get(0).getRemarkNote());
            }
            colList.add(map);
        });
    }
    return colList;
}

 /**
* 使用 Map按key进行排序
*
* @param map
* @return
*/
private static Map<String, List<ExportExcelEntityForStatement>> sortMapByKey(Map<String, List<ExportExcelEntityForStatement>> map) {
    if (map == null || map.isEmpty()) {
        return null;
    }
    Map<String, List<ExportExcelEntityForStatement>> sortMap = new TreeMap<>(
            new MapKeyComparator());
    sortMap.putAll(map);
    return sortMap;
}
  
 /**
 * 设置费用金额
 *
 * @param map
 * @param v
 * @param exportType
 * @return void
 * @author ZHANGCHAO
 * @date 2022/8/9 8:13
 */
public static void dealEveryAmounts(Map<String, Object> map, List<ExportExcelEntityForStatement> v, String exportType) {
    BigDecimal bijiaoyong = new BigDecimal("0.00000");
    // 类型 1应收 2应支
    String type = v.get(0).getFeiyongType();
    // 境外飞机进口
    if ("11".equals(exportType)) {
        // 航空运费
        BigDecimal airfreightAmount = v.stream().filter(i -> "航空运费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("airfreightAmount", bijiaoyong.compareTo(airfreightAmount) != 0 ? airfreightAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 操作费
        BigDecimal handlingChargeAmount = v.stream().filter(i -> "操作费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("handlingChargeAmount", bijiaoyong.compareTo(handlingChargeAmount) != 0 ? handlingChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 单证服务费
        BigDecimal ducomentationAmount = v.stream().filter(i -> "单证服务费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("ducomentationAmount", bijiaoyong.compareTo(ducomentationAmount) != 0 ? ducomentationAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 机场杂费
        BigDecimal terminalChargeAmount = v.stream().filter(i -> "机场杂费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("terminalChargeAmount", bijiaoyong.compareTo(terminalChargeAmount) != 0 ? terminalChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 监管仓库操作服务费
        BigDecimal bondedHandingChargeAmount = v.stream().filter(i -> "监管仓库操作服务费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("bondedHandingChargeAmount", bijiaoyong.compareTo(bondedHandingChargeAmount) != 0 ? bondedHandingChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 舱单信息处理费
        BigDecimal manifestFeeAmount = v.stream().filter(i -> "舱单信息处理费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("manifestFeeAmount", bijiaoyong.compareTo(manifestFeeAmount) != 0 ? manifestFeeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 单证费
        BigDecimal documentFeeAmount = v.stream().filter(i -> "单证费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("documentFeeAmount", bijiaoyong.compareTo(documentFeeAmount) != 0 ? documentFeeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 危品操作费
        BigDecimal handlingChargeForDgAmount = v.stream().filter(i -> "危品操作费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("handlingChargeForDgAmount", bijiaoyong.compareTo(handlingChargeForDgAmount) != 0 ? handlingChargeForDgAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 作业单平台使用费
        BigDecimal handlingPlatformChargeAmount = v.stream().filter(i -> "作业单平台使用费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("handlingPlatformChargeAmount", bijiaoyong.compareTo(handlingPlatformChargeAmount) != 0 ? handlingPlatformChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 报关报检代理服务费
        BigDecimal customsClearanceFeeAmount = v.stream().filter(i -> "报关报检代理服务费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("customsClearanceFeeAmount", bijiaoyong.compareTo(customsClearanceFeeAmount) != 0 ? customsClearanceFeeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 国外港杂费
        BigDecimal oversesaHarbourChargeAmount = v.stream().filter(i -> "国外港杂费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("oversesaHarbourChargeAmount", bijiaoyong.compareTo(oversesaHarbourChargeAmount) != 0 ? oversesaHarbourChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 国外提货费
        BigDecimal overseasPickupChargeAmount = v.stream().filter(i -> "国外提货费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("overseasPickupChargeAmount", bijiaoyong.compareTo(overseasPickupChargeAmount) != 0 ? overseasPickupChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 仓储费
        BigDecimal storageChargeAmount = v.stream().filter(i -> "仓储费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("storageChargeAmount", bijiaoyong.compareTo(storageChargeAmount) != 0 ? storageChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 装卸费
        BigDecimal loadingAndUnloadingFeeAmount = v.stream().filter(i -> "装卸费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("loadingAndUnloadingFeeAmount", bijiaoyong.compareTo(loadingAndUnloadingFeeAmount) != 0 ? loadingAndUnloadingFeeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 国内普货运输
        BigDecimal inlandFreightAmount = v.stream().filter(i -> "国内普货运输".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("inlandFreightAmount", bijiaoyong.compareTo(inlandFreightAmount) != 0 ? inlandFreightAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 特殊货物处理费
        BigDecimal handlingChargeForSpecialGoodsAmount = v.stream().filter(i -> "特殊货物处理费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("handlingChargeForSpecialGoodsAmount", bijiaoyong.compareTo(handlingChargeForSpecialGoodsAmount) != 0 ? handlingChargeForSpecialGoodsAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 入库手续费
        BigDecimal incomingBondedWarehouseAmount = v.stream().filter(i -> "入库手续费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("incomingBondedWarehouseAmount", bijiaoyong.compareTo(incomingBondedWarehouseAmount) != 0 ? incomingBondedWarehouseAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 铲车服务费
        BigDecimal forkliftServiceChargeAmount = v.stream().filter(i -> "铲车服务费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("forkliftServiceChargeAmount", bijiaoyong.compareTo(forkliftServiceChargeAmount) != 0 ? forkliftServiceChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 货物查验费
        BigDecimal cargoCheckingFeeAmount = v.stream().filter(i -> "货物查验费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("cargoCheckingFeeAmount", bijiaoyong.compareTo(cargoCheckingFeeAmount) != 0 ? cargoCheckingFeeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 分舱单费
        BigDecimal cargoManifestChangeAmount = v.stream().filter(i -> "分舱单费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("cargoManifestChangeAmount", bijiaoyong.compareTo(cargoManifestChangeAmount) != 0 ? cargoManifestChangeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // AOG费
        BigDecimal overtimeWorkChargeAmount = v.stream().filter(i -> "AOG费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("overtimeWorkChargeAmount", bijiaoyong.compareTo(overtimeWorkChargeAmount) != 0 ? overtimeWorkChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 代垫费
        BigDecimal reimbursedExpensesAmount = v.stream().filter(i -> "代垫费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("reimbursedExpensesAmount", bijiaoyong.compareTo(reimbursedExpensesAmount) != 0 ? reimbursedExpensesAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 消毒、熏蒸代理费
        BigDecimal disinfectionChargeAmount = v.stream().filter(i -> "消毒、熏蒸代理费".equals(i.getMoneyName()) && isNotEmpty(i.getAmount()))
                .map(ExportExcelEntityForStatement::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);
        map.put("disinfectionChargeAmount", bijiaoyong.compareTo(disinfectionChargeAmount) != 0 ? disinfectionChargeAmount.setScale(2, RoundingMode.HALF_UP) : "");
        // 发票税
        map.put("invoiceTax", "");
        if (isNotEmpty(v)) {
            BigDecimal invoiceTax = BigDecimal.ZERO;
            for (ExportExcelEntityForStatement entityForStatement : v) {
                if (isNotBlank(entityForStatement.getMoneyName()) && moneyName_11_List.contains(entityForStatement.getMoneyName())) {
                    if ("1".equals(type)) {
                        if (isNotEmpty(entityForStatement.getTax()) && entityForStatement.getTax().compareTo(new BigDecimal("6.00")) == 0) {
                            BigDecimal everyInvoiceTax = (isNotEmpty(entityForStatement.getAmount()) ? entityForStatement.getAmount() : BigDecimal.ZERO)
                                    .multiply(entityForStatement.getTax()).divide(new BigDecimal("100"), 2, RoundingMode.HALF_UP).setScale(2, RoundingMode.HALF_UP);
                            invoiceTax = invoiceTax.add(everyInvoiceTax);
                        }
                    } else if ("2".equals(type)) {
                        if (isNotEmpty(entityForStatement.getFpTax()) && entityForStatement.getFpTax().compareTo(new BigDecimal("6.00")) == 0) {
                            BigDecimal everyInvoiceTax = (isNotEmpty(entityForStatement.getAmount()) ? entityForStatement.getAmount() : BigDecimal.ZERO)
                                    .multiply(entityForStatement.getFpTax()).divide(new BigDecimal("100"), 2, RoundingMode.HALF_UP).setScale(2, RoundingMode.HALF_UP);
                            invoiceTax = invoiceTax.add(everyInvoiceTax);
                        }
                    }
                }
            }
            map.put("invoiceTax", new BigDecimal("0.00").compareTo(invoiceTax) != 0 ? invoiceTax : "");
        }
        BigDecimal totalAmount = airfreightAmount.add(handlingChargeAmount).add(ducomentationAmount).add(terminalChargeAmount)
                .add(bondedHandingChargeAmount).add(manifestFeeAmount).add(documentFeeAmount).add(handlingChargeForDgAmount)
                .add(handlingPlatformChargeAmount).add(customsClearanceFeeAmount).add(oversesaHarbourChargeAmount)
                .add(overseasPickupChargeAmount).add(storageChargeAmount).add(loadingAndUnloadingFeeAmount).add(inlandFreightAmount)
                .add(handlingChargeForSpecialGoodsAmount).add(incomingBondedWarehouseAmount).add(forkliftServiceChargeAmount).add(cargoCheckingFeeAmount)
                .add(cargoManifestChangeAmount).add(overtimeWorkChargeAmount).add(reimbursedExpensesAmount).add(disinfectionChargeAmount);
        // 合计
        map.put("totalAmount", totalAmount);
    }
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195

    五、设置Excel模版

    请添加图片描述

    标题是合并单元格，长度是集合subList的长度+2，#fe表示横向遍历集合subList。

    六、最终导出Excel

    请添加图片描述

  • 相关阅读:
    day33 List接口
    【Docker】docker常用命令
    vue ref和$refs获取组件实例
    （※）力扣刷题-字符串-实现 strStr()（KMP算法）
    JVM常用参数
    蓝桥杯嵌入式史上最全最详细教程教你快速入门
    深入理解关键字 一（auto,register,static,sizeof）
    View绘制流程
    Flutter自动路由插件auto_route详解
    JavaScript基础: 异步
  • 原文地址：https://blog.csdn.net/qq_26030541/article/details/126285769