From a8f5761841bcf481eb2f2a7c535dbac145d2a468 Mon Sep 17 00:00:00 2001
From: fuliqi <fuliqi@qq.com>
Date: 星期一, 29 七月 2024 10:14:54 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java | 258 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 258 insertions(+), 0 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
new file mode 100644
index 0000000..41b9066
--- /dev/null
+++ b/ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java
@@ -0,0 +1,258 @@
+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();
+ // 杩欓噷鍙互瀵筩ell杩涜浠讳綍鎿嶄綔
+ // 杩欓噷灏辫鑰冭檻 浣犺閽堝鍝竴鍒楄繘琛屼釜鎬у寲澶勭悊 涓�瀹氳璁板緱鍔犲垽鏂� 鍥犱负姣忎釜 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;
+ }
+
+}
\ No newline at end of file
--
Gitblit v1.8.0