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 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(), 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();
|
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<>();
|
CalculateRule calculateRule1 = new CalculateRule();
|
calculateRule1.setId(1L);
|
calculateRule1.setRuleName("视频平均在线率");
|
calculateRule1.setRuleCondition("≥98%");
|
calculateRule1.setMax(98D);
|
calculateRule1.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
|
calculateRule1.setCalcFraction(0.00);
|
list.add(calculateRule1);
|
CalculateRule calculateRule2 = new CalculateRule();
|
calculateRule2.setRuleName("视频平均在线率");
|
calculateRule2.setRuleCondition("95%≤视频平均在线率＜98%");
|
calculateRule2.setMax(97D);
|
calculateRule2.setMin(95D);
|
calculateRule2.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
|
calculateRule2.setCalcFraction(5.00);
|
list.add(calculateRule2);
|
CalculateRule calculateRule3 = new CalculateRule();
|
calculateRule3.setRuleName("视频平均在线率");
|
calculateRule3.setRuleCondition("90%≤视频平均在线率＜95%");
|
calculateRule3.setMax(94D);
|
calculateRule3.setMin(90D);
|
calculateRule3.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
|
calculateRule3.setCalcFraction(10.00);
|
list.add(calculateRule3);
|
CalculateRule calculateRule33 = new CalculateRule();
|
calculateRule33.setRuleName("视频平均在线率");
|
calculateRule33.setRuleCondition("＜90%");
|
calculateRule33.setMin(89D);
|
calculateRule33.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);
|
calculateRule33.setCalcFraction(10.00);
|
list.add(calculateRule33);
|
CalculateRule calculateRule4 = new CalculateRule();
|
calculateRule4.setId(2L);
|
calculateRule4.setRuleName("前端感知源治理工作");
|
calculateRule4.setRuleCondition("时钟同步（超过±3秒为不合格）");
|
calculateRule4.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY);
|
calculateRule4.setCalcFraction(0.1);
|
list.add(calculateRule4);
|
CalculateRule calculateRule7 = new CalculateRule();
|
calculateRule7.setRuleName("前端感知源治理工作");
|
calculateRule7.setRuleCondition("OSD标识");
|
list.add(calculateRule7);
|
CalculateRule calculateRule6 = new CalculateRule();
|
calculateRule6.setRuleName("前端感知源治理工作");
|
calculateRule6.setRuleCondition("一机一档");
|
list.add(calculateRule6);
|
CalculateRule calculateRule5 = new CalculateRule();
|
calculateRule5.setId(3L);
|
calculateRule5.setRuleName("后台系统的保障");
|
calculateRule5.setRuleCondition("单次故障时长若超出72小时不足144小时的，每超出12小时（不足12小时按12小时计）");
|
calculateRule5.setMax(144D);
|
calculateRule5.setMin(72D);
|
calculateRule5.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_AFTER_DIVIDING_QUANTITY);
|
calculateRule5.setCalcFraction(2.00);
|
calculateRule5.setCalcUnit(12);
|
list.add(calculateRule5);
|
return list;
|
}
|
|
}
|
