合同导入模板

龚焕茏

2024-04-23 7ce7861b02a6a4c4fe53d6292d3d216a65551371

合同导入模板

 pom.xml

@@ -246,6 +246,12 @@
            <artifactId>jaxb-api</artifactId>
            <version>${jaxb-api.version}</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easyexcel.version}</version>
        </dependency>
    </dependencies>

    <build>

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/CalculateRule.java

@@ -1,9 +1,13 @@
package com.ycl.platform.domain.entity;

import annotation.Excel;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.ycl.system.domain.TreeEntity;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import lombok.Data;

/**
 * 分数核算规则对象 t_default_rule
@@ -11,112 +15,65 @@
 * @author ruoyi
 * @date 2024-04-01
 */
public class CalculateRule extends TreeEntity
{
@Data
@ExcelIgnoreUnannotated
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class CalculateRule extends TreeEntity {
    private static final long serialVersionUID = 1L;

    /** 主键 */
    /**
     * 主键
     */
    @ExcelProperty("序号")
    private Long id;

    /** 规则名称 */
    @Excel(name = "规则名称")
    /**
     * 单位id
     */
    private Integer unitId;

    /**
     * 规则名称
     */
    @ExcelProperty("考核内容")
    private String ruleName;

    /** 扣减方式 */
    @Excel(name = "扣减方式")
    private Long deductCategory;
    /**
     * 关联上级id
     */
    private Long parentId;

    /** 人工打分/系统打分 */
    private Long deductWay;

    /** 规则描述 */
    @Excel(name = "规则描述")
    /**
     * 规则描述
     */
    @ExcelProperty("评判标准")
    private String ruleDesc;

    /** 规则状态 */
    @Excel(name = "规则状态")
    private String ruleStatus;
    /**
     * 规则条件
     */
    @ExcelProperty("考核要求")
    private String ruleCondition;

    /** 逻辑删除 */
    /**
     * 扣减方式 1/2/3 扣指定分数/分数乘以数量/除以数量后乘以分数
     */
    @ExcelProperty("扣分方式")
    private String deductCategory;

    /**
     * 扣除分数
     */
    @ExcelProperty("扣分数值")
    private Double calcFraction;

    /**
     * 当方式为除以时，除以当前字段数量
     */
    @ExcelProperty("除以数量")
    private Integer calcUnit;

    @TableLogic
    private String deleted;

    public void setId(Long id)
    {
        this.id = id;
    }

    public Long getId()
    {
        return id;
    }
    public void setRuleName(String ruleName)
    {
        this.ruleName = ruleName;
    }

    public String getRuleName()
    {
        return ruleName;
    }
    public void setDeductCategory(Long deductCategory)
    {
        this.deductCategory = deductCategory;
    }

    public Long getDeductCategory()
    {
        return deductCategory;
    }
    public void setDeductWay(Long deductWay)
    {
        this.deductWay = deductWay;
    }

    public Long getDeductWay()
    {
        return deductWay;
    }
    public void setRuleDesc(String ruleDesc)
    {
        this.ruleDesc = ruleDesc;
    }

    public String getRuleDesc()
    {
        return ruleDesc;
    }
    public void setRuleStatus(String ruleStatus)
    {
        this.ruleStatus = ruleStatus;
    }

    public String getRuleStatus()
    {
        return ruleStatus;
    }
    public void setDeleted(String deleted)
    {
        this.deleted = deleted;
    }

    public String getDeleted()
    {
        return deleted;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this, ToStringStyle.MULTI_LINE_STYLE)
            .append("id", getId())
            .append("ruleName", getRuleName())
            .append("parentId", getParentId())
            .append("deductCategory", getDeductCategory())
            .append("deductWay", getDeductWay())
            .append("ruleDesc", getRuleDesc())
            .append("ruleStatus", getRuleStatus())
            .append("createTime", getCreateTime())
            .append("updateTime", getUpdateTime())
            .append("deleted", getDeleted())
            .toString();
    }
}

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/TContract.java

@@ -1,135 +1,58 @@
package com.ycl.platform.domain.entity;

import annotation.Excel;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ycl.system.entity.BaseEntity;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import lombok.Data;

import java.util.Date;

/**
 * 【请填写功能名称】对象 t_contract
 * 
 * @author ruoyi
 * @date 2024-03-12
 * 运维合同对象 t_contract
 *
 * @author gonghl
 * @date 2024-04-22
 */
public class TContract extends BaseEntity
{
@Data
@ExcelIgnoreUnannotated
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class TContract extends BaseEntity {
    private static final long serialVersionUID = 1L;

    /** 主键 */
    /**
     * 主键
     */
    @ExcelProperty("序号")
    private Long id;

    /** 公司id */
    @Excel(name = "公司id")
    private Long companyId;
    /**
     * 单位id
     */
    private Integer unitId;

    /** 公司名 */
    @Excel(name = "公司名")
    private String companyName;
    /**
     * 合同名称
     */
    private String name;

    /** 对接公安部门id */
    @Excel(name = "对接公安部门id")
    private Long deptId;

    /** 部门名称 */
    @Excel(name = "部门名称")
    private String deptName;

    /** 合同明细json */
    @Excel(name = "合同明细json")
    private String detail;

    /** 开始时间 */
    @Excel(name = "开始时间")
    /**
     * 开始时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date startTime;

    /** 截止时间 */
    @Excel(name = "截止时间")
    /**
     * 结束时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date endTime;

    public Date getStartTime() {
        return startTime;
    }
    @TableLogic
    private String deleted;

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    public Long getId() 
    {
        return id;
    }
    public void setCompanyId(Long companyId) 
    {
        this.companyId = companyId;
    }

    public Long getCompanyId() 
    {
        return companyId;
    }
    public void setCompanyName(String companyName) 
    {
        this.companyName = companyName;
    }

    public String getCompanyName() 
    {
        return companyName;
    }
    public void setDeptId(Long deptId) 
    {
        this.deptId = deptId;
    }

    public Long getDeptId() 
    {
        return deptId;
    }
    public void setDeptName(String deptName) 
    {
        this.deptName = deptName;
    }

    public String getDeptName() 
    {
        return deptName;
    }
    public void setDetail(String detail) 
    {
        this.detail = detail;
    }

    public String getDetail() 
    {
        return detail;
    }

    @Override
    public String toString() {
        return "TContract{" +
                "id=" + id +
                ", companyId=" + companyId +
                ", companyName='" + companyName + '\'' +
                ", deptId=" + deptId +
                ", deptName='" + deptName + '\'' +
                ", detail='" + detail + '\'' +
                ", startTime=" + startTime +
                ", endTime=" + endTime +
                '}';
    }
}

 ycl-server/src/main/java/com/ycl/handler/CommentWriteHandler.java

New file
@@ -0,0 +1,37 @@
package com.ycl.handler;

import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

/**
 * 自定义拦截器.新增注释,第一行头加批注
 *
 * @author gonghl
 */
@Slf4j
public class CommentWriteHandler implements RowWriteHandler {

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        if (BooleanUtils.isTrue(context.getHead())) {
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();

            Comment comment1 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,  5, 4,  4, 5));
            comment1.setString(new XSSFRichTextString("扣除相关镜头数量*0.1分"));
            sheet.getRow(0).getCell(1).setCellComment(comment1);

            Comment comment2 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 5, 7,  7, 8));
            comment2.setString(new XSSFRichTextString("扣2分/12小时"));
            sheet.getRow(0).getCell(1).setCellComment(comment2);
        }
    }

}

 ycl-server/src/main/java/com/ycl/handler/CustomSheetWriteHandler.java

New file
@@ -0,0 +1,84 @@
package com.ycl.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 合同模板下拉框
 * @author gonghl
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {

    private List<String> selectDataList;

    public CustomSheetWriteHandler(List<String> selectDataList) {
        this.selectDataList = selectDataList;
    }

    /**
     * 想实现Excel引用其他sheet页数据作为单元格下拉选项值，
     * 需要重写该方法
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        // 构造下拉选项单元格列的位置，以及下拉选项可选参数值的map集合
        // key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
        // value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100
        Map<Integer, List<String>> selectParamMap = new HashMap<>();
        selectParamMap.put(4, selectDataList);

        // 获取第一个sheet页
        Sheet sheet = writeSheetHolder.getCachedSheet();
        // 获取sheet页的数据校验对象
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页
        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // 迭代索引，用于存放下拉数据的字典sheet数据页命名
        int index = 1;
        for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {

            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            String dictSheetName = "dict_hide_sheet" + index;
            Sheet dictSheet = workbook.createSheet(dictSheetName);
            // 隐藏字典sheet页
            workbook.setSheetHidden(index++, true);

            // 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的
            // 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实
            // 际业务设置真正的数据开始行，如果要设置到最后一行，那么一定注意，
            // 最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表
            // 失效，出不来
            CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            for (int i = 0; i < rowLen; i++) {
                // 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定
                // 义从第几行还是写，写的时候注意一下行索引是从0开始的即可
                dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));
            }

            // 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的
            String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().size();
            Name name = workbook.createName();
            name.setNameName(dictSheetName);
            // 将关联公式和sheet页做关联
            name.setRefersToFormula(refers);

            // 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
            DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
            DataValidation dataValidation = helper.createValidation(constraint, infoList);
            sheet.addValidationData(dataValidation);
        }
    }
}

 ycl-server/src/main/java/com/ycl/platform/controller/TContractController.java

@@ -5,16 +5,13 @@
import com.ycl.platform.service.ITContractService;
import com.ycl.system.AjaxResult;
import com.ycl.system.controller.BaseController;
import com.ycl.system.page.TableDataInfo;
import com.ycl.utils.poi.ExcelUtil;
import enumeration.BusinessType;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import lombok.AllArgsConstructor;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;


import java.util.List;
import org.springframework.web.multipart.MultipartFile;

/**
 * 【请填写功能名称】Controller
@@ -24,76 +21,75 @@
 */
@RestController
@RequestMapping("/system/contract")
public class TContractController extends BaseController
{
    @Autowired
    private ITContractService tContractService;
@AllArgsConstructor
public class TContractController extends BaseController {

    private final ITContractService tContractService;


    /**
     * 查询【请填写功能名称】列表
     * 合同导入模板
     */
   @PreAuthorize("@ss.hasPermi('system:contract:list')")
    @GetMapping("/list")
    public TableDataInfo list(TContract tContract)
    {
        startPage();
        List<TContract> list = tContractService.selectTContractList(tContract);
        return getDataTable(list);
    @PreAuthorize("@ss.hasPermi('system:contract:importTemplate')")
    @Log(title = "导入模板", businessType = BusinessType.IMPORT)
    @PostMapping("/importTemplate")
    public void importTemplate(HttpServletResponse response) {
        tContractService.importTemplate(response);
    }

    /**
     * 导出【请填写功能名称】列表
     * 合同导入
     *
     * @param file      导入文件
     * @param unitId    运维单位
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return 导入结果
     */
   @PreAuthorize("@ss.hasPermi('system:contract:export')")
    @Log(title = "【请填写功能名称】", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, TContract tContract)
    {
        List<TContract> list = tContractService.selectTContractList(tContract);
        ExcelUtil<TContract> util = new ExcelUtil<TContract>(TContract.class);
        util.exportExcel(response, list, "【请填写功能名称】数据");
    @Log(title = "合同导入", businessType = BusinessType.IMPORT)
    @PreAuthorize("@ss.hasPermi('system:user:import')")
    @PostMapping("/importData")
    public AjaxResult importData(MultipartFile file, String unitId, String startTime, String endTime) {
        ExcelUtil<TContract> util = new ExcelUtil<>(TContract.class);
        return success();
    }

    /**
     * 获取【请填写功能名称】详细信息
     */
   @PreAuthorize("@ss.hasPermi('system:contract:query')")
    @PreAuthorize("@ss.hasPermi('system:contract:query')")
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id)
    {
        return success(tContractService.selectTContractById(id));
    public AjaxResult getInfo(@PathVariable("id") Long id) {
        return success(tContractService.getById(id));
    }

    /**
     * 新增【请填写功能名称】
     */
   @PreAuthorize("@ss.hasPermi('system:contract:add')")
    @PreAuthorize("@ss.hasPermi('system:contract:add')")
    @Log(title = "【请填写功能名称】", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody TContract tContract)
    {
        return toAjax(tContractService.insertTContract(tContract));
    public AjaxResult add(@RequestBody TContract tContract) {
        return toAjax(tContractService.save(tContract));
    }

    /**
     * 修改【请填写功能名称】
     */
   @PreAuthorize("@ss.hasPermi('system:contract:edit')")
    @PreAuthorize("@ss.hasPermi('system:contract:edit')")
    @Log(title = "【请填写功能名称】", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody TContract tContract)
    {
        return toAjax(tContractService.updateTContract(tContract));
    public AjaxResult edit(@RequestBody TContract tContract) {
        return toAjax(tContractService.updateById(tContract));
    }

    /**
     * 删除【请填写功能名称】
     */
   @PreAuthorize("@ss.hasPermi('system:contract:remove')")
    @PreAuthorize("@ss.hasPermi('system:contract:remove')")
    @Log(title = "【请填写功能名称】", businessType = BusinessType.DELETE)
    @DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids)
    {
        return toAjax(tContractService.deleteTContractByIds(ids));
    @DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids) {
        return toAjax(tContractService.removeById(ids));
    }
}

 ycl-server/src/main/java/com/ycl/platform/mapper/TContractMapper.java

@@ -3,61 +3,11 @@
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ycl.platform.domain.entity.TContract;

import java.util.List;

/**
 * 【请填写功能名称】Mapper接口
 * 
 *
 * @author ruoyi
 * @date 2024-03-12
 */
public interface TContractMapper extends BaseMapper<TContract>
{
    /**
     * 查询【请填写功能名称】
     * 
     * @param id 【请填写功能名称】主键
     * @return 【请填写功能名称】
     */
    public TContract selectTContractById(Long id);

    /**
     * 查询【请填写功能名称】列表
     * 
     * @param tContract 【请填写功能名称】
     * @return 【请填写功能名称】集合
     */
    public List<TContract> selectTContractList(TContract tContract);

    /**
     * 新增【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    public int insertTContract(TContract tContract);

    /**
     * 修改【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    public int updateTContract(TContract tContract);

    /**
     * 删除【请填写功能名称】
     * 
     * @param id 【请填写功能名称】主键
     * @return 结果
     */
    public int deleteTContractById(Long id);

    /**
     * 批量删除【请填写功能名称】
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteTContractByIds(Long[] ids);
public interface TContractMapper extends BaseMapper<TContract> {
}

 ycl-server/src/main/java/com/ycl/platform/service/ITContractService.java

@@ -1,62 +1,20 @@
package com.ycl.platform.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.ycl.platform.domain.entity.TContract;

import java.util.List;
import jakarta.servlet.http.HttpServletResponse;

/**
 * 【请填写功能名称】Service接口
 * 
 *
 * @author ruoyi
 * @date 2024-03-12
 */
public interface ITContractService 
{
    /**
     * 查询【请填写功能名称】
     * 
     * @param id 【请填写功能名称】主键
     * @return 【请填写功能名称】
     */
    public TContract selectTContractById(Long id);
public interface ITContractService extends IService<TContract> {

    /**
     * 查询【请填写功能名称】列表
     * 
     * @param tContract 【请填写功能名称】
     * @return 【请填写功能名称】集合
     * 导入合同模板
     * @param response 结果
     */
    public List<TContract> selectTContractList(TContract tContract);

    /**
     * 新增【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    public int insertTContract(TContract tContract);

    /**
     * 修改【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    public int updateTContract(TContract tContract);

    /**
     * 批量删除【请填写功能名称】
     * 
     * @param ids 需要删除的【请填写功能名称】主键集合
     * @return 结果
     */
    public int deleteTContractByIds(Long[] ids);

    /**
     * 删除【请填写功能名称】信息
     * 
     * @param id 【请填写功能名称】主键
     * @return 结果
     */
    public int deleteTContractById(Long id);
    void importTemplate(HttpServletResponse response);
}

 ycl-server/src/main/java/com/ycl/platform/service/impl/TContractServiceImpl.java

@@ -1,95 +1,110 @@
package com.ycl.platform.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ycl.handler.CommentWriteHandler;
import com.ycl.handler.CustomSheetWriteHandler;
import com.ycl.platform.domain.entity.CalculateRule;
import com.ycl.platform.domain.entity.TContract;
import com.ycl.platform.mapper.TContractMapper;
import com.ycl.platform.service.ITContractService;
import org.springframework.beans.factory.annotation.Autowired;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Service;

import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * 【请填写功能名称】Service业务层处理
 * 
 *
 * @author ruoyi
 * @date 2024-03-12
 */
@Service
public class TContractServiceImpl extends ServiceImpl<TContractMapper, TContract> implements ITContractService
{
    @Autowired
    private TContractMapper tContractMapper;

    /**
     * 查询【请填写功能名称】
     * 
     * @param id 【请填写功能名称】主键
     * @return 【请填写功能名称】
     */
public class TContractServiceImpl extends ServiceImpl<TContractMapper, TContract> implements ITContractService {
    @Override
    public TContract selectTContractById(Long id)
    {
        return tContractMapper.selectTContractById(id);
    public void importTemplate(HttpServletResponse response) {
        try {
            List<CalculateRule> list = getExcelData();
            ArrayList<String> arrayList = new ArrayList<>();
            arrayList.add("扣指定分数");
            arrayList.add("分数乘以数量");
            arrayList.add("除以数量后乘以分数");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("导入合同模板", StandardCharsets.UTF_8).replace("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 第一列、第二列上下合并3行
            LoopMergeStrategy loopMergeStrategy1 = new LoopMergeStrategy(3, 0);
            LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(3, 1);
            EasyExcel.write(response.getOutputStream(), CalculateRule.class)
                    .registerWriteHandler(loopMergeStrategy1)
                    .registerWriteHandler(loopMergeStrategy2)
                    // 自适应列宽
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 下拉框
                    .registerWriteHandler(new CustomSheetWriteHandler(arrayList))
                    // 标注
                    .registerWriteHandler(new CommentWriteHandler())
                    .sheet("合同导入模板").doWrite(list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询【请填写功能名称】列表
     * 
     * @param tContract 【请填写功能名称】
     * @return 【请填写功能名称】
     */
    @Override
    public List<TContract> selectTContractList(TContract tContract)
    {
        return tContractMapper.selectTContractList(tContract);
    }

    /**
     * 新增【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    @Override
    public int insertTContract(TContract tContract)
    {
        return tContractMapper.insertTContract(tContract);
    }

    /**
     * 修改【请填写功能名称】
     * 
     * @param tContract 【请填写功能名称】
     * @return 结果
     */
    @Override
    public int updateTContract(TContract tContract)
    {
        return tContractMapper.updateTContract(tContract);
    }

    /**
     * 批量删除【请填写功能名称】
     * 
     * @param ids 需要删除的【请填写功能名称】主键
     * @return 结果
     */
    @Override
    public int deleteTContractByIds(Long[] ids)
    {
        return tContractMapper.deleteTContractByIds(ids);
    }

    /**
     * 删除【请填写功能名称】信息
     * 
     * @param id 【请填写功能名称】主键
     * @return 结果
     */
    @Override
    public int deleteTContractById(Long id)
    {
        return tContractMapper.deleteTContractById(id);
    private List<CalculateRule> getExcelData() {
        ArrayList<CalculateRule> list = new ArrayList<>();
        CalculateRule calculateRule1 = new CalculateRule();
        calculateRule1.setId(1L);
        calculateRule1.setRuleName("视频平均在线率");
        calculateRule1.setRuleDesc("（1）视频在线率=视频在线数量÷前端服务配置数（应扣除不可抗力或与中标人服务无关造成的掉线除外。）");
        calculateRule1.setRuleCondition("≥98%");
        calculateRule1.setDeductCategory("扣指定分数");
        calculateRule1.setCalcFraction(0.00);
        list.add(calculateRule1);
        CalculateRule calculateRule2 = new CalculateRule();
        calculateRule2.setRuleDesc("（2）视频平均在线率：当月每日视频在线率的平均值。");
        calculateRule2.setRuleCondition("95%≤视频平均在线率＜98%");
        calculateRule2.setDeductCategory("扣指定分数");
        calculateRule2.setCalcFraction(5.00);
        list.add(calculateRule2);
        CalculateRule calculateRule3 = new CalculateRule();
        calculateRule3.setRuleDesc("（3）视频在线率保留整数并且向下取整。");
        calculateRule3.setRuleCondition("90%≤视频平均在线率＜95%");
        calculateRule3.setDeductCategory("扣指定分数");
        calculateRule3.setCalcFraction(10.00);
        list.add(calculateRule3);
        CalculateRule calculateRule4 = new CalculateRule();
        calculateRule4.setId(2L);
        calculateRule4.setRuleName("前端感知源治理工作");
        calculateRule4.setRuleDesc("时钟同步（超过±3秒为不合格）");
        calculateRule4.setRuleCondition("视频监控摄像机时钟不同步，24小时内未修复的");
        calculateRule4.setDeductCategory("分数乘以数量");
        calculateRule4.setCalcFraction(0.1);
        list.add(calculateRule4);
        CalculateRule calculateRule7 = new CalculateRule();
        calculateRule7.setRuleName("…………");
        calculateRule7.setRuleDesc("…………");
        calculateRule7.setRuleCondition("…………");
        list.add(calculateRule7);
        CalculateRule calculateRule6 = new CalculateRule();
        calculateRule6.setRuleName("…………");
        calculateRule6.setRuleDesc("…………");
        calculateRule6.setRuleCondition("…………");
        list.add(calculateRule6);
        CalculateRule calculateRule5 = new CalculateRule();
        calculateRule5.setId(3L);
        calculateRule5.setRuleName("后台系统的保障");
        calculateRule5.setRuleDesc("在个别设备故障但不影响该功能模块整体应用情况下，须在72小时以内排除故障。");
        calculateRule5.setRuleCondition("单次故障时长若超出144小时以后，每超出12小时（不足12小时按12小时计）");
        calculateRule5.setDeductCategory("除以数量后乘以分数");
        calculateRule5.setCalcFraction(2.00);
        calculateRule5.setCalcUnit(12);
        list.add(calculateRule5);
        return list;
    }
}

 ycl-server/src/main/resources/mapper/zgyw/TContractMapper.xml

@@ -1,86 +1,7 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ycl.platform.mapper.TContractMapper">
    
    <resultMap type="com.ycl.platform.domain.entity.TContract" id="TContractResult">
        <result property="id"    column="id"    />
        <result property="companyId"    column="company_id"    />
        <result property="companyName"    column="company_name"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="detail"    column="detail"    />
        <result property="startTime"    column="start_time"    />
        <result property="endTime"    column="end_time"    />
    </resultMap>

    <sql id="selectTContractVo">
        select id, company_id, company_name, dept_id, dept_name, detail ,start_time,end_time from t_contract
    </sql>

    <select id="selectTContractList" parameterType="com.ycl.platform.domain.entity.TContract" resultMap="TContractResult">
        <include refid="selectTContractVo"/>
        <where>  
            <if test="companyId != null "> and company_id = #{companyId}</if>
            <if test="companyName != null  and companyName != ''"> and company_name like concat('%', #{companyName}, '%')</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="detail != null  and detail != ''"> and detail = #{detail}</if>
            <if test="startTime != null"> and start_time = #{startTime}</if>
            <if test="endTime != null"> and end_time = #{endTime}</if>
        </where>
    </select>
    
    <select id="selectTContractById" parameterType="Long" resultMap="TContractResult">
        <include refid="selectTContractVo"/>
        where id = #{id}
    </select>
        
    <insert id="insertTContract" parameterType="com.ycl.platform.domain.entity.TContract" useGeneratedKeys="true" keyProperty="id">
        insert into t_contract
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="companyId != null">company_id,</if>
            <if test="companyName != null">company_name,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="deptName != null">dept_name,</if>
            <if test="detail != null">detail,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="companyId != null">#{companyId},</if>
            <if test="companyName != null">#{companyName},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="deptName != null">#{deptName},</if>
            <if test="detail != null">#{detail},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
         </trim>
    </insert>

    <update id="updateTContract" parameterType="com.ycl.platform.domain.entity.TContract">
        update t_contract
        <trim prefix="SET" suffixOverrides=",">
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="companyName != null">company_name = #{companyName},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="deptName != null">dept_name = #{deptName},</if>
            <if test="detail != null">detail = #{detail},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteTContractById" parameterType="Long">
        delete from t_contract where id = #{id}
    </delete>

    <delete id="deleteTContractByIds" parameterType="String">
        delete from t_contract where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>