| New file |
| | |
|---|
| | | 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(1); |
|---|
| | | 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(2); |
|---|
| | | 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(3); |
|---|
| | | 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; |
|---|
| | | } |
|---|
| | | |
|---|
| | | } |
|---|
