From a4a904acc5f4de80d30eafe19dd97f3cc8f56a0b Mon Sep 17 00:00:00 2001
From: zxl <763096477@qq.com>
Date: 星期一, 29 九月 2025 17:17:34 +0800
Subject: [PATCH] 新问题 25-9-28
---
ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java | 183 ++++++++++++++++++++-------------------------
1 files changed, 81 insertions(+), 102 deletions(-)
diff --git a/ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java b/ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java
index acb9559..9a50226 100644
--- a/ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java
+++ b/ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java
@@ -14,6 +14,7 @@
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;
@@ -29,18 +30,17 @@
* 鎵�浠ヨ兘鍋氱殑鑷畾涔夋搷浣滃緢涓板瘜
*/
// static class CustomCellWriteHandler {
-
- @Override
- public void afterCellDispose(CellWriteHandlerContext context) {
- // 褰撳墠鐨� cell
- Cell cell = context.getCell();
- // 杩欓噷鍙互瀵筩ell杩涜浠讳綍鎿嶄綔
- // 杩欓噷灏辫鑰冭檻 浣犺閽堝鍝竴鍒楄繘琛屼釜鎬у寲澶勭悊 涓�瀹氳璁板緱鍔犲垽鏂� 鍥犱负姣忎釜 cell 閮戒細鎵ц鍒拌繖閲� 鎵�浠ヨ璁板緱鍖哄垎
- if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {
- // 1 琛ㄧず 鐪侀偅涓�鍒� 瑕佸鐪佸競鍖鸿繘琛岃仈鍔ㄤ笅鎷夊鐞�
- ExcelAreaUtil.writeAreaInfo(context, 0);
- }
+ @Override
+ public void afterCellDispose(CellWriteHandlerContext context) {
+ // 褰撳墠鐨� cell
+ Cell cell = context.getCell();
+ // 杩欓噷鍙互瀵筩ell杩涜浠讳綍鎿嶄綔
+ // 杩欓噷灏辫鑰冭檻 浣犺閽堝鍝竴鍒楄繘琛屼釜鎬у寲澶勭悊 涓�瀹氳璁板緱鍔犲垽鏂� 鍥犱负姣忎釜 cell 閮戒細鎵ц鍒拌繖閲� 鎵�浠ヨ璁板緱鍖哄垎
+ if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {
+ // 1 琛ㄧず 鐪侀偅涓�鍒� 瑕佸鐪佸競鍖鸿繘琛岃仈鍔ㄤ笅鎷夊鐞�
+ ExcelAreaUtil.writeAreaInfo(context, 0);
}
+ }
// }
public static class ExcelAreaUtil {
@@ -76,7 +76,7 @@
// 鍑嗗鐐规暟鎹�
List<AreaInfo> provinceList = new ArrayList<>();
for (ContractRule contractRule : ContractRule.getParent()) {
- provinceList.add(new AreaInfo(contractRule.getName(), ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));
+ provinceList.add(new AreaInfo(contractRule.getName(), CollectionUtils.isEmpty(ContractRule.getChildren(contractRule)) ? null : ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));
}
// 鑾峰彇鍒板綋鍓嶇殑 excel 鍥犱负瑕佸垱寤洪殣钘忕殑 sheet 涔熷氨鏄渷甯傚尯鐨勫疄闄呭唴瀹归兘鏉ヨ嚜浜庨殣钘忕殑 sheet
@@ -96,23 +96,24 @@
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);
+ 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);
}
-
- // 娣诲姞鍚嶇О绠＄悊鍣�
- 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);
@@ -134,7 +135,7 @@
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
// 楠岃瘉
- provinceDataValidation.createErrorBox("error", "璇烽�夋嫨姝ｇ‘鐨勭渷浠�");
+ provinceDataValidation.createErrorBox("error", "璇烽�夋嫨姝ｇ‘鐨勮鍒�");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheetPro.addValidationData(provinceDataValidation);
@@ -181,78 +182,56 @@
}
- 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());
- }
+// 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);
+// }
- 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;
- }
-
-}
\ No newline at end of file
+}
--
Gitblit v1.8.0