package com.ycl.handler;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.util.BooleanUtils;
|
import com.alibaba.excel.write.handler.CellWriteHandler;
|
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
|
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
|
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
|
import com.ycl.platform.domain.entity.CalculateRule;
|
import enumeration.ContractRule;
|
import enumeration.general.RuleDeductCategoryEnum;
|
import lombok.Data;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
import org.apache.poi.ss.util.CellReference;
|
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
|
import org.springframework.util.CollectionUtils;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.List;
|
|
public class CustomCellWriteHandler implements CellWriteHandler {
|
|
/**
|
* 这个处理器其实可以做很多事情
|
* 他能拿到整个 Excel 的 WorkHook
|
* 也能拿到当前的 sheet
|
* 也能拿到当前的 cell
|
* 所以能做的自定义操作很丰富
|
*/
|
// static class CustomCellWriteHandler {
|
@Override
|
public void afterCellDispose(CellWriteHandlerContext context) {
|
// 当前的 cell
|
Cell cell = context.getCell();
|
// 这里可以对cell进行任何操作
|
// 这里就要考虑 你要针对哪一列进行个性化处理 一定要记得加判断 因为每个 cell 都会执行到这里 所以要记得区分
|
if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {
|
// 1 表示 省那一列 要对省市区进行联动下拉处理
|
ExcelAreaUtil.writeAreaInfo(context, 0);
|
}
|
}
|
// }
|
|
public static class ExcelAreaUtil {
|
|
@Data
|
static class AreaInfo {
|
|
private String name;
|
|
private List<String> areaList;
|
|
|
public AreaInfo(String name, List<String> areaList) {
|
this.name = name;
|
this.areaList = areaList;
|
}
|
}
|
|
|
/**
|
* @param context handler 的 上下文
|
* @param startIndex 省的列号 从0开始的
|
*/
|
public static void writeAreaInfo(CellWriteHandlerContext context, int startIndex) {
|
// 获取到当前的 sheet 后续要给省市区列加下拉
|
WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
|
Sheet sheet = writeSheetHolder.getSheet();
|
|
// 省市区挨着的列号
|
int cIndex = startIndex + 1;
|
int aIndex = cIndex + 1;
|
|
// 准备点数据
|
List<AreaInfo> provinceList = new ArrayList<>();
|
for (ContractRule contractRule : ContractRule.getParent()) {
|
provinceList.add(new AreaInfo(contractRule.getName(), CollectionUtils.isEmpty(ContractRule.getChildren(contractRule)) ? null : ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));
|
}
|
|
// 获取到当前的 excel 因为要创建隐藏的 sheet 也就是省市区的实际内容都来自于隐藏的 sheet
|
Workbook workbook = context.getWriteSheetHolder().getParentWriteWorkbookHolder().getWorkbook();
|
Sheet hideSheet = workbook.createSheet("area");
|
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
|
// 写隐藏 sheet 的省市区信息
|
int rowId = 0;
|
// 设置第一行，存省的信息
|
Row provinceRow = hideSheet.createRow(rowId++);
|
provinceRow.createCell(0).setCellValue("省列表");
|
for (int i = 0; i < provinceList.size(); i++) {
|
Cell provinceCell = provinceRow.createCell(i + 1);
|
provinceCell.setCellValue(provinceList.get(i).getName());
|
}
|
|
for (AreaInfo pro : provinceList) {
|
String pName = pro.getName();
|
List<String> cList = pro.getAreaList();
|
if (!CollectionUtils.isEmpty(cList)) {
|
Row cRow = hideSheet.createRow(rowId++);
|
cRow.createCell(0).setCellValue(pName);
|
for (int j = 0; j < cList.size(); j++) {
|
String cInfo = cList.get(j);
|
Cell cCell = cRow.createCell(j + 1);
|
cCell.setCellValue(cInfo);
|
}
|
|
// 添加名称管理器
|
String range = getRange(1, rowId, cList.size());
|
Name name = workbook.createName();
|
// key不可重复
|
name.setNameName(pName);
|
String formula = "area!" + range;
|
name.setRefersToFormula(formula);
|
}
|
}
|
// 给省添加下拉
|
int lastRow = 100;
|
setDataValidation(sheet, 1, lastRow, startIndex, startIndex);
|
|
// 给市 区 添加下拉
|
// 设置第二列和第三列的有效性
|
for (int i = 2; i < lastRow; i++) {
|
setDataValidation(CellReference.convertNumToColString(startIndex), sheet, i - 1, i - 1, cIndex, cIndex);
|
setDataValidation(CellReference.convertNumToColString(cIndex), sheet, i - 1, i - 1, aIndex, aIndex);
|
}
|
}
|
|
public static void setDataValidation(Sheet sheetPro, int firstRow, int lastRow, int firstCol, int lastCol) {
|
DataValidationHelper dvHelper = sheetPro.getDataValidationHelper();
|
String formula = getFormula(1, 1, 1, 34);
|
// 省规则
|
DataValidationConstraint provConstraint = dvHelper.createFormulaListConstraint(formula);
|
// 四个参数分别是：起始行、终止行、起始列、终止列
|
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
|
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
|
// 验证
|
provinceDataValidation.createErrorBox("error", "请选择正确的规则");
|
provinceDataValidation.setShowErrorBox(true);
|
provinceDataValidation.setSuppressDropDownArrow(true);
|
sheetPro.addValidationData(provinceDataValidation);
|
}
|
|
public static void setDataValidation(String offset, Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
|
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
|
String formulaString = String.format("INDIRECT($%s%s)", offset, firstRow + 1);
|
DataValidation dataValidation = getDataValidationByFormula(formulaString, dvHelper, firstRow, lastRow, firstCol, lastCol);
|
sheet.addValidationData(dataValidation);
|
}
|
|
private static DataValidation getDataValidationByFormula(String formulaString, DataValidationHelper dvHelper,
|
int firstRow, int lastRow, int firstCol, int lastCol) {
|
// 加载下拉列表内容
|
// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
|
// 如果A2是江苏省，那么此处就是江苏省下的市信息。
|
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
|
// 设置数据有效性加载在哪个单元格上。
|
// 四个参数分别是：起始行、终止行、起始列、终止列
|
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
|
// 绑定 数据有效性对象
|
XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
|
dataValidation.setEmptyCellAllowed(true);
|
dataValidation.setSuppressDropDownArrow(true);
|
dataValidation.setShowErrorBox(true);
|
// 设置输入错误提示信息
|
dataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
|
return dataValidation;
|
}
|
|
private static String getFormula(int firstRow, int lastRow, int firstCol, int lastCol) {
|
String ss = String.format("$%s$%s:$%s$%s", CellReference.convertNumToColString(firstCol), firstRow,
|
CellReference.convertNumToColString(lastCol), lastRow);
|
return String.format("=%s!%s", "area", ss);
|
}
|
|
private static String getRange(int offset, int rowId, int colCount) {
|
String columnLetter1 = CellReference.convertNumToColString(offset);
|
String columnLetter2 = CellReference.convertNumToColString(offset + colCount - 1);
|
return String.format("$%s$%s:$%s$%s", columnLetter1, rowId, columnLetter2, rowId);
|
}
|
|
}
|
|
|
public static void main(String[] args) {
|
EasyExcel.write("test.xlsx", CalculateRule.class)
|
// 自适应列宽
|
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
|
// 下拉框
|
.registerWriteHandler(new CustomSheetWriteHandler(Arrays.asList("扣指定分数", "分数乘以数量", "除以数量后乘以分数")))
|
.registerWriteHandler(new CustomCellWriteHandler())
|
// 标注
|
.registerWriteHandler(new CommentWriteHandler())
|
.sheet("合同导入模板")
|
.doWrite(getExcelData());
|
}
|
|
private static List<CalculateRule> getExcelData() {
|
ArrayList<CalculateRule> list = new ArrayList<>();
|
|
setTemplateRule(list,1,"设备平均在线率","≥98%",null,98D,RuleDeductCategoryEnum.DEDUCT_POINTS,0.00);
|
setTemplateRule(list,null,"设备平均在线率","95%≤设备平均在线率＜98%",97D,95D,RuleDeductCategoryEnum.DEDUCT_POINTS,5.00);
|
setTemplateRule(list,null,"设备平均在线率","90%≤设备平均在线率＜95%",94D,90D,RuleDeductCategoryEnum.DEDUCT_POINTS,10.00);
|
setTemplateRule(list,null,"设备平均在线率","＜90%",89D,null,RuleDeductCategoryEnum.DEDUCT_POINTS,20.00);
|
|
setTemplateRule(list,2,"前端感知源治理工作","时钟同步（超过±3秒为不合格）",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);
|
setTemplateRule(list,null,"前端感知源治理工作","OSD标识",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);
|
setTemplateRule(list,null,"前端感知源治理工作","一机一档",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);
|
|
setTemplateRule(list,3,"对于前端点位异常情况的处理","镜头故障或污染或树枝遮挡或枪机视角偏移正常角度或补光灯应亮未亮,24小时后未修复的",null,24D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.5);
|
setTemplateRule(list,null,"对于前端点位异常情况的处理","镜头故障或污染或树枝遮挡或枪机视角偏移正常角度或补光灯应亮未亮,48小时后未修复的",null,48D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.0);
|
|
setTemplateRule(list,4,"确保录像完整不定期对所有点位录像完整性抽查","每路视频累计丢失10分钟以内",10D,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.2);
|
setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失10-60 分钟",10D,60D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.5);
|
setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失1 小时-4 小时（含）",60D,240D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.0);
|
setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失4 小时-12 小时（含）",240D,720D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.5);
|
setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失12 小时以上",null,720D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,2.0);
|
|
setTemplateRule(list,5,"确保图片完整不定期对所有人脸车辆以及智能前端抓拍的图片完整性抽查","发现后台存储不能调取前端设备图片",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,2.0);
|
|
|
return list;
|
}
|
|
private static void setTemplateRule(ArrayList<CalculateRule> list,Integer id,String ruleName,String condition,Double max,Double min,RuleDeductCategoryEnum deductCategoryEnum,Double calcFraction) {
|
CalculateRule calculateRule = new CalculateRule();
|
calculateRule.setId(id);
|
calculateRule.setRuleName(ruleName);
|
calculateRule.setRuleCondition(condition);
|
calculateRule.setMax(max);
|
calculateRule.setMin(min);
|
calculateRule.setDeductCategory(deductCategoryEnum);
|
calculateRule.setCalcFraction(calcFraction);
|
list.add(calculateRule);
|
}
|
|
}
|
