feat: 导出新增数据标红+数据批注

yanghb

2025-05-07 398e6d10892c428f2ff0e3f6485472d5509088e6

feat: 导出新增数据标红+数据批注

 cz-bussiness/src/main/java/com/ruoyi/bussiness/object/response/placementApply/ProblemExportResponse.java

@@ -1,5 +1,6 @@
package com.ruoyi.bussiness.object.response.placementApply;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.ruoyi.common.easyExcel.BigDecimalConverter;
import com.ruoyi.common.easyExcel.DateConverter;
@@ -40,6 +41,14 @@
    @ExcelProperty(value = "身份证号", index = 5, converter = StringConverter.class)
    private String idCard;

    @ApiModelProperty(value = "身份证不在安置库警告(0不警告 1警告")
    @ExcelIgnore
    private Integer idCardExistsWarn;

    @ExcelIgnore
    @ApiModelProperty(value = "身份证重复提醒警告(0不警告 1警告")
    private Integer idCardNoWarn;

    @ApiModelProperty(value = "联系电话")
    @ExcelProperty(value = "联系电话", index = 6, converter = StringConverter.class)
    private String mobile;
@@ -60,9 +69,21 @@
    @ExcelProperty(value = "待安置家庭成员姓名", index = 10, converter = StringConverter.class)
    private String waitFamilyNames;

    @ExcelIgnore
    @ApiModelProperty(value = "待安置家庭成员(警告)")
    private Integer waitFamilyNamesWarn;

    @ExcelIgnore
    @ApiModelProperty(value = "待安置家庭成员不在安置库(警告)")
    private Integer waitFamilyNamesNoWarn;

    @ApiModelProperty(value = "待安置人员应安置面积合计(m²)")
    @ExcelProperty(value = "待安置人员应安置面积合计(m²)", index = 11, converter = BigDecimalConverter.class)
    private BigDecimal waitFamilyArea;

    @ExcelIgnore
    @ApiModelProperty(value = "应补偿面积数据异常警告")
    private Integer waitFamilyAreaWarn;

    @ApiModelProperty(value = "补偿金额(万元)-新建商品住房、商业用房、停车位")
    @ExcelProperty(value = {"补偿金额(万元)","新建商品住房、商业用房、停车位"}, index = 12, converter = BigDecimalConverter.class)
@@ -72,22 +93,42 @@
    @ExcelProperty(value = {"补偿金额(万元)","二手住房"}, index = 13, converter = BigDecimalConverter.class)
    private BigDecimal compensationOldAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "多个购房活动警告(0正常 1异常)")
    private Integer compensationAmountWarn;

    @ApiModelProperty(value = "补偿金额（合计）")
    @ExcelProperty(value = {"补偿金额(万元)","合计"}, index = 14, converter = BigDecimalConverter.class)
    private BigDecimal compensationSum;

    @ExcelIgnore
    @ApiModelProperty(value = "补偿金额（合计）警告")
    private Integer compensationSumWarn;

    @ApiModelProperty(value = "25%首付款（万元）")
    @ExcelProperty(value = "25%首付款（万元）", index = 15, converter = BigDecimalConverter.class)
    private BigDecimal downPaymentAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "首付款警告")
    private Integer downPaymentAmountWarn;

    @ApiModelProperty(value = "每季度需支付款项（万元）")
    @ExcelProperty(value = "每季度需支付款项（万元）", index = 16, converter = BigDecimalConverter.class)
    private BigDecimal quarterPayAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "每季度需支付款项（警告）")
    private Integer quarterPayAmountWarn;

    @ApiModelProperty(value = "过渡补贴(万元)")
    @ExcelProperty(value = "过渡补贴(万元)", index = 17, converter = BigDecimalConverter.class)
    private BigDecimal subsidyAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "过渡补贴警告")
    private Integer subsidyAmountWarn;

    @ApiModelProperty(value = "备注")
    @ExcelProperty(value = "备注", index = 18, converter = StringConverter.class)
    private String remark;

 cz-bussiness/src/main/java/com/ruoyi/bussiness/object/response/placementBatch/AssetExportResponse.java

@@ -1,5 +1,6 @@
package com.ruoyi.bussiness.object.response.placementBatch;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.ruoyi.common.easyExcel.BigDecimalConverter;
import com.ruoyi.common.easyExcel.DateConverter;
@@ -30,9 +31,17 @@
    @ApiModelProperty(value = "户主姓名")
    private String householdHead;

    @ExcelIgnore
    @ApiModelProperty(value = "户主未通过安置申请提示(1未通过)")
    private Integer householdHeadWarn;

    @ExcelProperty(value = "身份证号", index = 4, converter = StringConverter.class)
    @ApiModelProperty(value = "身份证号")
    private String idCard;

    @ExcelIgnore
    @ApiModelProperty(value = "身份证不存在安置库")
    private Integer idCardWarn;

    @ExcelProperty(value = "应安置人数(人)", index = 5, converter = NumberConverter.class)
    @ApiModelProperty(value = "应安置人数(人)")
@@ -50,20 +59,43 @@
    @ApiModelProperty(value = "补偿单价标准-二手住房")
    private String priceOldAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "多个购房情况警告（新建房和二手住房都录入了）")
    private Integer twoPriceWarn;

    @ExcelIgnore
    @ApiModelProperty(value = "补偿标准数据异常（不等于补偿协议里面写的）")
    private Integer priceAmountWarn;

    @ExcelProperty(value = "补偿总价(万元)", index = 9, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "补偿总价(万元)")
    private BigDecimal compensationAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "补偿总价异常")
    private Integer compensationSumWarn;

    @ExcelProperty(value = "25%首付款(万元)", index = 10, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "25%首付款(万元)")
    private BigDecimal downPaymentAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "首付款警告")
    private Integer downPaymentAmountWarn;

    @ExcelProperty(value = "每季度需支付款项(万元)", index = 11, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "每季度需支付款项(万元)")
    private BigDecimal quarterPayAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "每季度需支付款警告")
    private Integer quarterPayAmountWarn;

    @ExcelProperty(value = "过渡补贴(万元)", index = 12, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "过渡补贴(万元)")
    private BigDecimal subsidyAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "过渡补贴警告")
    private Integer subsidyAmountWarn;
}

 cz-bussiness/src/main/java/com/ruoyi/bussiness/object/response/placementBatch/HouseholdExportResponse.java

@@ -1,5 +1,6 @@
package com.ruoyi.bussiness.object.response.placementBatch;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.easyExcel.BigDecimalConverter;
@@ -38,9 +39,17 @@
    @ApiModelProperty(value = "户主姓名")
    private String householdHead;

    @ExcelIgnore
    @ApiModelProperty(value = "未通过安置申请警告")
    private Integer householdHeadWarn;

    @ExcelProperty(value = "身份证号", index = 5, converter = StringConverter.class)
    @ApiModelProperty(value = "身份证号")
    private String idCard;

    @ExcelIgnore
    @ApiModelProperty(value = "身份证不存在安置库警告 0: 不警告, 1: 警告")
    private Integer idCardWarn;

    @ExcelProperty(value = "联系电话", index = 6, converter = StringConverter.class)
    @ApiModelProperty(value = "联系电话")
@@ -62,9 +71,21 @@
    @ApiModelProperty(value = "待安置家庭成员姓名")
    private String waitFamilyNames;

    @ExcelIgnore
    @ApiModelProperty(value = "家庭成员(警告)")
    private Integer waitFamilyNamesWarn;

    @ExcelIgnore
    @ApiModelProperty(value = "待安置家庭成员不在安置库(警告)")
    private Integer waitFamilyNamesNoWarn;

    @ExcelProperty(value = "待安置人员应安置面积合计(m²)", index = 11, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "待安置人员应安置面积合计(m²)")
    private BigDecimal waitFamilyArea;

    @ExcelIgnore
    @ApiModelProperty(value = "待安置人员应安置面积警告")
    private Integer waitFamilyAreaWarn;

    @ExcelProperty(value = {"补偿金额(万元)", "新建商品住房、商业用房、停车位"}, index = 12, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "补偿金额 - 新建商品住房、商业用房、停车位")
@@ -74,22 +95,42 @@
    @ApiModelProperty(value = "补偿金额 - 二手住房")
    private BigDecimal compensationOldAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "多个购房情况警告")
    private Integer compensationAmountWarn;

    @ExcelProperty(value = {"补偿金额(万元)", "合计"}, index = 14, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "补偿金额（合计）")
    private BigDecimal compensationSum;

    @ExcelIgnore
    @ApiModelProperty(value = "补偿金额（合计）警告")
    private Integer compensationSumWarn;

    @ExcelProperty(value = "25%首付款（万元）", index = 15, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "25%首付款（万元）")
    private BigDecimal downPaymentAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "首付款警告")
    private Integer downPaymentAmountWarn;

    @ExcelProperty(value = "每季度需支付款项（万元）", index = 16, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "每季度需支付款项（万元）")
    private BigDecimal quarterPayAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "季度款警告")
    private Integer quarterPayAmountWarn;

    @ExcelProperty(value = "过渡补贴", index = 17, converter = BigDecimalConverter.class)
    @ApiModelProperty(value = "过渡补贴")
    private BigDecimal subsidyAmount;

    @ExcelIgnore
    @ApiModelProperty(value = "过渡补贴警告")
    private Integer subsidyAmountWarn;

    @ExcelProperty(value = "备注", index = 18, converter = StringConverter.class)
    @ApiModelProperty(value = "备注")
    private String remark;

 cz-bussiness/src/main/java/com/ruoyi/bussiness/service/impl/PlacementApplyServiceImpl.java

@@ -19,6 +19,7 @@
import com.ruoyi.bussiness.object.response.placementApply.*;
import com.ruoyi.bussiness.service.*;
import com.ruoyi.bussiness.utils.BatchNumberUtils;
import com.ruoyi.common.easyExcel.CommentWriteHandler;
import com.ruoyi.common.exception.GlobalException;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.file.FileUtils;
@@ -530,8 +531,35 @@
            List<PlacementApplyRecord> applyRecords = placementApplyRecordService.problemList(request);
            List<ProblemExportResponse> data = BeanUtil.copyToList(applyRecords, ProblemExportResponse.class);

            Map<String, String> warnFieldToTarget = new HashMap<>();
            warnFieldToTarget.put("idCardExistsWarn","idCard");
            warnFieldToTarget.put("idCardNoWarn","idCard");
            warnFieldToTarget.put("waitFamilyNamesWarn","waitFamilyNames");
            warnFieldToTarget.put("waitFamilyNamesNoWarn","waitFamilyNames");
            warnFieldToTarget.put("waitFamilyAreaWarn","waitFamilyArea");
            warnFieldToTarget.put("compensationAmountWarn","compensationNewAmount");
            warnFieldToTarget.put("compensationSumWarn","compensationSum");
            warnFieldToTarget.put("quarterPayAmountWarn","quarterPayAmount");
            warnFieldToTarget.put("subsidyAmountWarn","subsidyAmount");



            Map<String, String> warnMessages = new HashMap<>();
            warnMessages.put("idCardExistsWarn","身份证不在安置库警告");
            warnMessages.put("idCardNoWarn","身份证重复提醒警告");
            warnMessages.put("waitFamilyNamesWarn","待安置家庭成员重复");
            warnMessages.put("waitFamilyNamesNoWarn","待安置家庭成员不在安置库");
            warnMessages.put("waitFamilyAreaWarn","应补偿面积数据异常警告");
            warnMessages.put("compensationAmountWarn","多个购房活动警告");
            warnMessages.put("compensationSumWarn","补偿金额异常");
            warnMessages.put("downPaymentAmountWarn","首付款警告");
            warnMessages.put("quarterPayAmountWarn","每季度需支付款项");
            warnMessages.put("subsidyAmountWarn","过渡补贴异常");

            FileUtils.setExcelResponseHeader(response, "问题数据.xlsx");
            ExcelWriterBuilder write = EasyExcelFactory.write(response.getOutputStream(), ProblemExportResponse.class);
            ExcelWriterBuilder write = EasyExcelFactory.write(response.getOutputStream(),
                    ProblemExportResponse.class)
                    .registerWriteHandler(new CommentWriteHandler<>(data,warnFieldToTarget,warnMessages));
            write.sheet("sheet").doWrite(data);
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        } catch (Exception e) {

 cz-bussiness/src/main/java/com/ruoyi/bussiness/service/impl/PlacementBatchServiceImpl.java

@@ -22,6 +22,7 @@
import com.ruoyi.bussiness.service.*;
import com.ruoyi.bussiness.utils.BatchNumberUtils;
import com.ruoyi.bussiness.utils.PaymentCycleHelper;
import com.ruoyi.common.easyExcel.CommentWriteHandler;
import com.ruoyi.common.exception.GlobalException;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.file.FileUtils;
@@ -737,16 +738,66 @@
            FileUtils.setExcelResponseHeader(response, "问题数据.xlsx");
            //资金表
            if (request.getType() == 1) {
                ExcelWriterBuilder write = EasyExcelFactory.write(response.getOutputStream(), AssetExportResponse.class);
                Map<String, String> warnFieldToTarget = new HashMap<>();
                warnFieldToTarget.put("householdHeadWarn","householdHead");
                warnFieldToTarget.put("idCardWarn","idCard");
                warnFieldToTarget.put("twoPriceWarn","priceNewAmount");
                warnFieldToTarget.put("priceAmountWarn","priceNewAmount");
                warnFieldToTarget.put("compensationSumWarn","compensationAmount");
                warnFieldToTarget.put("downPaymentAmountWarn","downPaymentAmount");
                warnFieldToTarget.put("quarterPayAmountWarn","quarterPayAmount");
                warnFieldToTarget.put("subsidyAmountWarn","subsidyAmount");

                Map<String, String> warnMessages = new HashMap<>();
                warnMessages.put("householdHeadWarn","户主未通过安置申请提示");
                warnMessages.put("idCardWarn","身份证不存在安置库");
                warnMessages.put("twoPriceWarn","多个购房情况警告");
                warnMessages.put("priceAmountWarn","补偿标准数据异常");
                warnMessages.put("compensationSumWarn","补偿总价异常");
                warnMessages.put("downPaymentAmountWarn","首付款警告");
                warnMessages.put("quarterPayAmountWarn","每季度需支付款项警告");
                warnMessages.put("subsidyAmountWarn","过渡补贴异常");

                List<PlacementBatchAsset> assets = placementBatchAssetService.problemList(request);
                List<AssetExportResponse> data = BeanUtil.copyToList(assets, AssetExportResponse.class);

                ExcelWriterBuilder write = EasyExcelFactory.
                        write(response.getOutputStream(), AssetExportResponse.class)
                        .registerWriteHandler(new CommentWriteHandler<>(data,warnFieldToTarget,warnMessages));
                write.sheet("sheet").doWrite(data);
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            } else {
                ExcelWriterBuilder write = EasyExcelFactory.write(response.getOutputStream(), HouseholdExportResponse.class);
                Map<String, String> warnFieldToTarget = new HashMap<>();
                warnFieldToTarget.put("householdHeadWarn","householdHead");
                warnFieldToTarget.put("idCardWarn","idCard");
                warnFieldToTarget.put("waitFamilyNamesWarn","waitFamilyNames");
                warnFieldToTarget.put("waitFamilyNamesNoWarn","waitFamilyNames");
                warnFieldToTarget.put("waitFamilyAreaWarn","waitFamilyArea");
                warnFieldToTarget.put("compensationAmountWarn","compensationNewAmount");
                warnFieldToTarget.put("compensationSumWarn","compensationSum");
                warnFieldToTarget.put("downPaymentAmountWarn","downPaymentAmount");
                warnFieldToTarget.put("quarterPayAmountWarn","quarterPayAmount");
                warnFieldToTarget.put("subsidyAmountWarn","subsidyAmount");

                Map<String, String> warnMessages = new HashMap<>();
                warnMessages.put("householdHeadWarn","未通过安置申请警告");
                warnMessages.put("idCardWarn","身份证不存在安置库警告");
                warnMessages.put("waitFamilyNamesWarn","家庭成员重复");
                warnMessages.put("waitFamilyNamesNoWarn","待安置家庭成员不在安置库");
                warnMessages.put("waitFamilyAreaWarn","待安置人员应安置面积警告");
                warnMessages.put("compensationAmountWarn","多个购房情况警告");
                warnMessages.put("compensationSumWarn","补偿金额异常");
                warnMessages.put("downPaymentAmountWarn","首付款警告");
                warnMessages.put("quarterPayAmountWarn","每季度需支付款项");
                warnMessages.put("subsidyAmountWarn","过渡补贴异常");

                //购房表
                List<PlacementBatchHousehold> households = placementBatchHouseholdService.problemList(request);
                List<HouseholdExportResponse> data = BeanUtil.copyToList(households, HouseholdExportResponse.class);

                ExcelWriterBuilder write = EasyExcelFactory
                        .write(response.getOutputStream(), HouseholdExportResponse.class)
                        .registerWriteHandler(new CommentWriteHandler<>(data,warnFieldToTarget,warnMessages));
                write.sheet("sheet").doWrite(data);
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            }

 cz-common/src/main/java/com/ruoyi/common/easyExcel/CommentWriteHandler.java

New file
@@ -0,0 +1,98 @@
package com.ruoyi.common.easyExcel;

import cn.hutool.core.util.NumberUtil;
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 com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

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

public class CommentWriteHandler<T> implements CellWriteHandler {

    private final List<T> dataList;
    private final Map<String, String> warnToTargetFieldMap;
    private final Map<String, String> warnMessages;

    public CommentWriteHandler(List<T> dataList,
                               Map<String, String> warnToTargetFieldMap,
                               Map<String, String> warnMessages) {
        this.dataList = dataList;
        this.warnToTargetFieldMap = warnToTargetFieldMap;
        this.warnMessages = warnMessages;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder,
                                 WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList,
                                 Cell cell,
                                 Head head,
                                 Integer relativeRowIndex,
                                 Boolean isHead) {
        if (isHead) return;

        String fieldName = head.getFieldName();
        T rowData = dataList.get(relativeRowIndex);

        for (Map.Entry<String, String> entry : warnToTargetFieldMap.entrySet()) {
            String warnField = entry.getKey();
            String targetField = entry.getValue();

            if (!fieldName.equals(targetField)) continue;

            try {
                Field warn = rowData.getClass().getDeclaredField(warnField);
                warn.setAccessible(true);
                Object warnValue = warn.get(rowData);
                if (NumberUtil.parseInt(warnValue.toString()) == 1) {

                    // ✅ 设置背景色通过 WriteCellData 实现
                    if (cellDataList != null && !cellDataList.isEmpty()) {
                        WriteCellStyle style = new WriteCellStyle();
                        style.setFillForegroundColor(IndexedColors.RED.getIndex());
                        style.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                        cellDataList.get(0).setWriteCellStyle(style);
                    }

                    // 获取现有的批注
                    Comment existingComment = cell.getCellComment();
                    String existingText = "";
                    if (existingComment != null) {
                        // 获取已有批注的内容
                        existingText = existingComment.getString().getString();
                    }

                    // 追加新的批注内容
                    String newCommentText = warnMessages.getOrDefault(warnField, "数据异常，请核查");
                    String updatedCommentText = existingText + "\n" + newCommentText;

                    // 如果已有批注，则更新批注内容；如果没有，则创建一个新的批注
                    if (existingComment != null) {
                        existingComment.setString(new XSSFRichTextString(updatedCommentText));
                    } else {
                        // 创建新的批注并设置内容
                        Drawing<?> drawing = cell.getSheet().createDrawingPatriarch();
                        Comment comment = drawing.createCellComment(new XSSFClientAnchor(
                                0, 0, 0, 0,
                                cell.getColumnIndex(), cell.getRowIndex(),
                                cell.getColumnIndex() + 3, cell.getRowIndex() + 2
                        ));
                        comment.setString(new XSSFRichTextString(updatedCommentText));
                        comment.setAuthor("系统提示");
                        cell.setCellComment(comment);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}