From cf7478fa2339196ffd88d24f1149c192c3f0f0e3 Mon Sep 17 00:00:00 2001
From: maven <2163098428@qq.com>
Date: 星期一, 18 八月 2025 10:05:30 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/pim_yys'
---
src/main/java/com/ruoyi/common/utils/excel/ExcelImport.java | 31 +
src/main/java/com/ruoyi/approve/utils/StartAndEndDateDto.java | 13
src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java | 1039 +++++++++++++++++++++++++++++++++++++++++
src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java | 5
src/main/resources/mapper/lavorissue/LavorIssueMapper.xml | 6
src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java | 196 +++++++
src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java | 29 +
src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java | 11
src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java | 43 +
src/main/java/com/ruoyi/common/utils/excel/ExcelExport.java | 28 +
pom.xml | 6
src/main/java/com/ruoyi/common/utils/excel/ExcelClassField.java | 77 +++
12 files changed, 1,481 insertions(+), 3 deletions(-)
diff --git a/pom.xml b/pom.xml
index c802315..17973d1 100644
--- a/pom.xml
+++ b/pom.xml
@@ -289,6 +289,12 @@
<version>1.12.2</version>
</dependency>
+ <dependency>
+ <groupId>com.alibaba</groupId>
+ <artifactId>easyexcel</artifactId>
+ <version>4.0.3</version>
+ </dependency>
+
</dependencies>
<build>
diff --git a/src/main/java/com/ruoyi/approve/utils/StartAndEndDateDto.java b/src/main/java/com/ruoyi/approve/utils/StartAndEndDateDto.java
index fdf4c38..7913ffd 100644
--- a/src/main/java/com/ruoyi/approve/utils/StartAndEndDateDto.java
+++ b/src/main/java/com/ruoyi/approve/utils/StartAndEndDateDto.java
@@ -21,4 +21,17 @@
@TableField(exist = false)
private String endDate;
+ @ApiModelProperty("寮�濮嬫湀浠�")
+ @TableField(exist = false)
+ private Integer startMonth;
+
+ @ApiModelProperty("缁撴潫鏈堜唤")
+ @TableField(exist = false)
+ private Integer endMonth;
+
+ @ApiModelProperty("骞翠唤")
+ @TableField(exist = false)
+ private Integer year;
+
+
}
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java b/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java
new file mode 100644
index 0000000..6d59d27
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java
@@ -0,0 +1,29 @@
+package com.ruoyi.common.utils.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.write.merge.AbstractMergeStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.util.List;
+
+public class ConfigurableMergeStrategy extends AbstractMergeStrategy {
+ private final List<CellRangeAddress> mergeRegions;
+
+ public ConfigurableMergeStrategy(List<CellRangeAddress> mergeRegions) {
+ this.mergeRegions = mergeRegions;
+ }
+
+ @Override
+ protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
+ // 妫�鏌ュ綋鍓嶅崟鍏冩牸鏄惁鍦ㄤ换浣曚竴涓悎骞跺尯鍩熷唴
+ for (CellRangeAddress region : mergeRegions) {
+ if (cell.getRowIndex() == region.getFirstRow() &&
+ cell.getColumnIndex() == region.getFirstColumn()) {
+ sheet.addMergedRegion(region); // 鎵ц鍚堝苟
+ break;
+ }
+ }
+ }
+}
diff --git a/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java b/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java
new file mode 100644
index 0000000..7c095e7
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java
@@ -0,0 +1,43 @@
+package com.ruoyi.common.utils.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.write.handler.CellWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
+import org.apache.poi.ss.usermodel.*;
+
+import java.util.List;
+
+public class CustomCellStyleHandler implements CellWriteHandler {
+
+ @Override
+ public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+
+ }
+
+
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+ int row = cell.getRowIndex();
+ int col = cell.getColumnIndex();
+
+ // 闇�瑕佹帓闄ょ殑鍗曞厓鏍�
+ if ((row == 2 && col == 0) || (row == 10 && col == 0)) {
+ return;
+ }
+
+ // 涓哄叾浠栧崟鍏冩牸璁剧疆鏍峰紡锛堝眳涓�+鑷姩鎹㈣锛�
+ Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setWrapText(true);
+ cell.setCellStyle(style);
+ }
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ExcelClassField.java b/src/main/java/com/ruoyi/common/utils/excel/ExcelClassField.java
new file mode 100644
index 0000000..de42dd9
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ExcelClassField.java
@@ -0,0 +1,77 @@
+package com.ruoyi.common.utils.excel;
+
+import java.util.LinkedHashMap;
+
+/**
+ * @author sunnyzyq
+ * @date 2021/12/17
+ */
+public class ExcelClassField {
+
+ /** 瀛楁鍚嶇О */
+ private String fieldName;
+
+ /** 琛ㄥご鍚嶇О */
+ private String name;
+
+ /** 鏄犲皠鍏崇郴 */
+ private LinkedHashMap<String, String> kvMap;
+
+ /** 绀轰緥鍊� */
+ private Object example;
+
+ /** 鎺掑簭 */
+ private int sort;
+
+ /** 鏄惁涓烘敞瑙ｅ瓧娈碉細0-鍚︼紝1-鏄� */
+ private int hasAnnotation;
+
+ public String getFieldName() {
+ return fieldName;
+ }
+
+ public void setFieldName(String fieldName) {
+ this.fieldName = fieldName;
+ }
+
+ public String getName() {
+ return name;
+ }
+
+ public void setName(String name) {
+ this.name = name;
+ }
+
+ public LinkedHashMap<String, String> getKvMap() {
+ return kvMap;
+ }
+
+ public void setKvMap(LinkedHashMap<String, String> kvMap) {
+ this.kvMap = kvMap;
+ }
+
+ public Object getExample() {
+ return example;
+ }
+
+ public void setExample(Object example) {
+ this.example = example;
+ }
+
+ public int getSort() {
+ return sort;
+ }
+
+ public void setSort(int sort) {
+ this.sort = sort;
+ }
+
+ public int getHasAnnotation() {
+ return hasAnnotation;
+ }
+
+ public void setHasAnnotation(int hasAnnotation) {
+ this.hasAnnotation = hasAnnotation;
+ }
+
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ExcelExport.java b/src/main/java/com/ruoyi/common/utils/excel/ExcelExport.java
new file mode 100644
index 0000000..06ffc83
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ExcelExport.java
@@ -0,0 +1,28 @@
+package com.ruoyi.common.utils.excel;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ * @author sunnyzyq
+ * @date 2021/12/17
+ */
+@Target(ElementType.FIELD)
+@Retention(RetentionPolicy.RUNTIME)
+public @interface ExcelExport {
+
+ /** 瀛楁鍚嶇О */
+ String value();
+
+ /** 瀵煎嚭鎺掑簭鍏堝悗: 鏁板瓧瓒婂皬瓒婇潬鍓嶏紙榛樿鎸塉ava绫诲瓧娈甸『搴忓鍑猴級 */
+ int sort() default 0;
+
+ /** 瀵煎嚭鏄犲皠锛屾牸寮忓锛�0-鏈煡;1-鐢�;2-濂� */
+ String kv() default "";
+
+ /** 瀵煎嚭妯℃澘绀轰緥鍊硷紙鏈夊�肩殑璇濓紝鐩存帴鍙栬鍊硷紝涓嶅仛鏄犲皠锛� */
+ String example() default "";
+
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ExcelImport.java b/src/main/java/com/ruoyi/common/utils/excel/ExcelImport.java
new file mode 100644
index 0000000..cbfcf7f
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ExcelImport.java
@@ -0,0 +1,31 @@
+package com.ruoyi.common.utils.excel;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ * @author sunnyzyq
+ * @date 2021/12/17
+ */
+@Target(ElementType.FIELD)
+@Retention(RetentionPolicy.RUNTIME)
+public @interface ExcelImport {
+
+ /** 瀛楁鍚嶇О */
+ String value();
+
+ /** 瀵煎嚭鏄犲皠锛屾牸寮忓锛�0-鏈煡;1-鐢�;2-濂� */
+ String kv() default "";
+
+ /** 鏄惁涓哄繀濉瓧娈碉紙榛樿涓洪潪蹇呭～锛� */
+ boolean required() default false;
+
+ /** 鏈�澶ч暱搴︼紙榛樿255锛� */
+ int maxLength() default 255;
+
+ /** 瀵煎叆鍞竴鎬ч獙璇侊紙澶氫釜瀛楁鍒欏彇鑱斿悎楠岃瘉锛� */
+ boolean unique() default false;
+
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java b/src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java
new file mode 100644
index 0000000..230bb77
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java
@@ -0,0 +1,1039 @@
+package com.ruoyi.common.utils.excel;
+
+import com.alibaba.fastjson2.JSONArray;
+import com.alibaba.fastjson2.JSONObject;
+import com.ruoyi.common.utils.StringUtils;
+import org.apache.poi.hssf.usermodel.HSSFDataValidation;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.poifs.filesystem.POIFSFileSystem;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.servlet.ServletOutputStream;
+import javax.servlet.http.HttpServletResponse;
+import java.io.*;
+import java.lang.reflect.Field;
+import java.math.BigDecimal;
+import java.math.RoundingMode;
+import java.net.URL;
+import java.text.NumberFormat;
+import java.text.SimpleDateFormat;
+import java.util.*;
+import java.util.Map.Entry;
+import java.util.regex.Pattern;
+
+/**
+ * Excel瀵煎叆瀵煎嚭宸ュ叿绫�
+ * 鍘熸枃閾炬帴锛堜笉瀹氭椂澧炲姞鏂板姛鑳斤級: https://zyqok.blog.csdn.net/article/details/121994504
+ *
+ * @author sunnyzyq
+ * @date 2021/12/17
+ */
+@SuppressWarnings("unused")
+public class ExcelUtils {
+
+ private static final String XLSX = ".xlsx";
+ private static final String XLS = ".xls";
+ public static final String ROW_MERGE = "row_merge";
+ public static final String COLUMN_MERGE = "column_merge";
+ private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
+ private static final String ROW_NUM = "rowNum";
+ private static final String ROW_DATA = "rowData";
+ private static final String ROW_TIPS = "rowTips";
+ private static final int CELL_OTHER = 0;
+ private static final int CELL_ROW_MERGE = 1;
+ private static final int CELL_COLUMN_MERGE = 2;
+ private static final int IMG_HEIGHT = 30;
+ private static final int IMG_WIDTH = 30;
+ private static final char LEAN_LINE = '/';
+ private static final int BYTES_DEFAULT_LENGTH = 10240;
+ private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
+
+
+ public static <T> List<T> readFile(File file, Class<T> clazz) throws Exception {
+ JSONArray array = readFile(file);
+ return getBeanList(array, clazz);
+ }
+
+ public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz) throws Exception {
+ JSONArray array = readMultipartFile(mFile);
+ return getBeanList(array, clazz);
+ }
+
+ public static JSONArray readFile(File file) throws Exception {
+ return readExcel(null, file);
+ }
+
+ public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
+ return readExcel(mFile, null);
+ }
+
+ public static Map<String, JSONArray> readFileManySheet(File file) throws Exception {
+ return readExcelManySheet(null, file);
+ }
+
+ public static Map<String, JSONArray> readFileManySheet(MultipartFile file) throws Exception {
+ return readExcelManySheet(file, null);
+ }
+
+ private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) throws Exception {
+ List<T> list = new ArrayList<>();
+ Map<Integer, String> uniqueMap = new HashMap<>(16);
+ for (int i = 0; i < array.size(); i++) {
+ list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
+ }
+ return list;
+ }
+
+ /**
+ * 鑾峰彇姣忎釜瀵硅薄鐨勬暟鎹�
+ */
+ private static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
+ T t = c.newInstance();
+ Field[] fields = c.getDeclaredFields();
+ List<String> errMsgList = new ArrayList<>();
+ boolean hasRowTipsField = false;
+ StringBuilder uniqueBuilder = new StringBuilder();
+ int rowNum = 0;
+ for (Field field : fields) {
+ // 琛屽彿
+ if (field.getName().equals(ROW_NUM)) {
+ rowNum = obj.getInteger(ROW_NUM);
+ field.setAccessible(true);
+ field.set(t, rowNum);
+ continue;
+ }
+ // 鏄惁闇�瑕佽缃紓甯镐俊鎭�
+ if (field.getName().equals(ROW_TIPS)) {
+ hasRowTipsField = true;
+ continue;
+ }
+ // 鍘熷鏁版嵁
+ if (field.getName().equals(ROW_DATA)) {
+ field.setAccessible(true);
+ field.set(t, obj.toString());
+ continue;
+ }
+ // 璁剧疆瀵瑰簲灞炴�у��
+ setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
+ }
+ // 鏁版嵁鍞竴鎬ф牎楠�
+ if (uniqueBuilder.length() > 0) {
+ if (uniqueMap.containsValue(uniqueBuilder.toString())) {
+ Set<Integer> rowNumKeys = uniqueMap.keySet();
+ for (Integer num : rowNumKeys) {
+ if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
+ errMsgList.add(String.format("鏁版嵁鍞竴鎬ф牎楠屽け璐�,(%s)涓庣%s琛岄噸澶�)", uniqueBuilder, num));
+ }
+ }
+ } else {
+ uniqueMap.put(rowNum, uniqueBuilder.toString());
+ }
+ }
+ // 澶辫触澶勭悊
+ if (errMsgList.isEmpty() && !hasRowTipsField) {
+ return t;
+ }
+ StringBuilder sb = new StringBuilder();
+ int size = errMsgList.size();
+ for (int i = 0; i < size; i++) {
+ if (i == size - 1) {
+ sb.append(errMsgList.get(i));
+ } else {
+ sb.append(errMsgList.get(i)).append(";");
+ }
+ }
+ // 璁剧疆閿欒淇℃伅
+ for (Field field : fields) {
+ if (field.getName().equals(ROW_TIPS)) {
+ field.setAccessible(true);
+ field.set(t, sb.toString());
+ }
+ }
+ return t;
+ }
+
+ private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
+ // 鑾峰彇 ExcelImport 娉ㄨВ灞炴��
+ ExcelImport annotation = field.getAnnotation(ExcelImport.class);
+ if (annotation == null) {
+ return;
+ }
+ String cname = annotation.value();
+ if (cname.trim().length() == 0) {
+ return;
+ }
+ // 鑾峰彇鍏蜂綋鍊�
+ String val = null;
+ if (obj.containsKey(cname)) {
+ val = getString(obj.getString(cname));
+ }
+ if (val == null) {
+ return;
+ }
+ field.setAccessible(true);
+ // 鍒ゆ柇鏄惁蹇呭～
+ boolean require = annotation.required();
+ if (require && val.isEmpty()) {
+ errMsgList.add(String.format("[%s]涓嶈兘涓虹┖", cname));
+ return;
+ }
+ // 鏁版嵁鍞竴鎬ц幏鍙�
+ boolean unique = annotation.unique();
+ if (unique) {
+ if (uniqueBuilder.length() > 0) {
+ uniqueBuilder.append("--").append(val);
+ } else {
+ uniqueBuilder.append(val);
+ }
+ }
+ // 鍒ゆ柇鏄惁瓒呰繃鏈�澶ч暱搴�
+ int maxLength = annotation.maxLength();
+ if (maxLength > 0 && val.length() > maxLength) {
+ errMsgList.add(String.format("[%s]闀垮害涓嶈兘瓒呰繃%s涓瓧绗�(褰撳墠%s涓瓧绗�)", cname, maxLength, val.length()));
+ }
+ // 鍒ゆ柇褰撳墠灞炴�ф槸鍚︽湁鏄犲皠鍏崇郴
+ LinkedHashMap<String, String> kvMap = getKvMap(annotation.kv());
+ if (!kvMap.isEmpty()) {
+ boolean isMatch = false;
+ for (String key : kvMap.keySet()) {
+ if (kvMap.get(key).equals(val)) {
+ val = key;
+ isMatch = true;
+ break;
+ }
+ }
+ if (!isMatch) {
+ errMsgList.add(String.format("[%s]鐨勫�间笉姝ｇ‘(褰撳墠鍊间负%s)", cname, val));
+ return;
+ }
+ }
+ // 鍏朵綑鎯呭喌鏍规嵁绫诲瀷璧嬪��
+ String fieldClassName = field.getType().getSimpleName();
+ try {
+ if ("String".equalsIgnoreCase(fieldClassName)) {
+ field.set(t, val);
+ } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
+ field.set(t, Boolean.valueOf(val));
+ } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
+ try {
+ field.set(t, Integer.valueOf(val));
+ } catch (NumberFormatException e) {
+ errMsgList.add(String.format("[%s]鐨勫�兼牸寮忎笉姝ｇ‘(褰撳墠鍊间负%s)", cname, val));
+ }
+ } else if ("double".equalsIgnoreCase(fieldClassName)) {
+ field.set(t, Double.valueOf(val));
+ } else if ("long".equalsIgnoreCase(fieldClassName)) {
+ field.set(t, Long.valueOf(val));
+ } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
+ field.set(t, new BigDecimal(val));
+ } else if ("Date".equalsIgnoreCase(fieldClassName)) {
+ try {
+ field.set(t, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val));
+ } catch (Exception e) {
+ field.set(t, new SimpleDateFormat("yyyy-MM-dd").parse(val));
+ }
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ private static Map<String, JSONArray> readExcelManySheet(MultipartFile mFile, File file) throws IOException {
+ Workbook book = getWorkbook(mFile, file);
+ if (book == null) {
+ return Collections.emptyMap();
+ }
+ Map<String, JSONArray> map = new LinkedHashMap<>();
+ for (int i = 0; i < book.getNumberOfSheets(); i++) {
+ Sheet sheet = book.getSheetAt(i);
+ JSONArray arr = readSheet(sheet);
+ map.put(sheet.getSheetName(), arr);
+ }
+ book.close();
+ return map;
+ }
+
+ private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
+ Workbook book = getWorkbook(mFile, file);
+ if (book == null) {
+ return new JSONArray();
+ }
+ JSONArray array = readSheet(book.getSheetAt(0));
+ book.close();
+ return array;
+ }
+
+ private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
+ boolean fileNotExist = (file == null || !file.exists());
+ if (mFile == null && fileNotExist) {
+ return null;
+ }
+ // 瑙ｆ瀽琛ㄦ牸鏁版嵁
+ InputStream in;
+ String fileName;
+ if (mFile != null) {
+ // 涓婁紶鏂囦欢瑙ｆ瀽
+ in = mFile.getInputStream();
+ fileName = getString(mFile.getOriginalFilename()).toLowerCase();
+ } else {
+ // 鏈湴鏂囦欢瑙ｆ瀽
+ in = new FileInputStream(file);
+ fileName = file.getName().toLowerCase();
+ }
+ Workbook book;
+ if (fileName.endsWith(XLSX)) {
+ book = new XSSFWorkbook(in);
+ } else if (fileName.endsWith(XLS)) {
+ POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
+ book = new HSSFWorkbook(poifsFileSystem);
+ } else {
+ return null;
+ }
+ in.close();
+ return book;
+ }
+
+ private static JSONArray readSheet(Sheet sheet) {
+ // 棣栬涓嬫爣
+ int rowStart = sheet.getFirstRowNum();
+ // 灏捐涓嬫爣
+ int rowEnd = sheet.getLastRowNum();
+ // 鑾峰彇琛ㄥご琛�
+ Row headRow = sheet.getRow(rowStart);
+ if (headRow == null) {
+ return new JSONArray();
+ }
+ int cellStart = headRow.getFirstCellNum();
+ int cellEnd = headRow.getLastCellNum();
+ Map<Integer, String> keyMap = new HashMap<>();
+ for (int j = cellStart; j < cellEnd; j++) {
+ // 鑾峰彇琛ㄥご鏁版嵁
+ String val = getCellValue(headRow.getCell(j));
+ if (val != null && val.trim().length() != 0) {
+ keyMap.put(j, val);
+ }
+ }
+ // 濡傛灉琛ㄥご娌℃湁鏁版嵁鍒欎笉杩涜瑙ｆ瀽
+ if (keyMap.isEmpty()) {
+ return (JSONArray) Collections.emptyList();
+ }
+ // 鑾峰彇姣忚JSON瀵硅薄鐨勫��
+ JSONArray array = new JSONArray();
+ // 濡傛灉棣栬涓庡熬琛岀浉鍚岋紝琛ㄦ槑鍙湁涓�琛岋紝杩斿洖琛ㄥご鏁版嵁
+ if (rowStart == rowEnd) {
+ JSONObject obj = new JSONObject();
+ // 娣诲姞琛屽彿
+ obj.put(ROW_NUM, 1);
+ for (int i : keyMap.keySet()) {
+ obj.put(keyMap.get(i), "");
+ }
+ array.add(obj);
+ return array;
+ }
+ for (int i = rowStart + 1; i <= rowEnd; i++) {
+ Row eachRow = sheet.getRow(i);
+ JSONObject obj = new JSONObject();
+ // 娣诲姞琛屽彿
+ obj.put(ROW_NUM, i + 1);
+ StringBuilder sb = new StringBuilder();
+ for (int k = cellStart; k < cellEnd; k++) {
+ if (eachRow != null) {
+ String val = getCellValue(eachRow.getCell(k));
+ // 鎵�鏈夋暟鎹坊鍔犲埌閲岄潰锛岀敤浜庡垽鏂琛屾槸鍚︿负绌�
+ sb.append(val);
+ obj.put(keyMap.get(k), val);
+ }
+ }
+ if (sb.length() > 0) {
+ array.add(obj);
+ }
+ }
+ return array;
+ }
+
+ private static String getCellValue(Cell cell) {
+ // 绌虹櫧鎴栫┖
+ if (cell == null || cell.getCellType() == CellType.BLANK) {
+ return "";
+ }
+ // String绫诲瀷
+ if (cell.getCellType() == CellType.STRING) {
+ String val = cell.getStringCellValue();
+ if (val == null || val.trim().length() == 0) {
+ return "";
+ }
+ return val.trim();
+ }
+ // 鏁板瓧绫诲瀷
+ if (cell.getCellType() == CellType.NUMERIC) {
+ String s = cell.getNumericCellValue() + "";
+ // 鍘绘帀灏惧反涓婄殑灏忔暟鐐�0
+ if (Pattern.matches(".*\\.0*", s)) {
+ return s.split("\\.")[0];
+ } else {
+ return s;
+ }
+ }
+ // 甯冨皵鍊肩被鍨�
+ if (cell.getCellType() == CellType.BOOLEAN) {
+ return cell.getBooleanCellValue() + "";
+ }
+ // 閿欒绫诲瀷
+ return cell.getCellFormula();
+ }
+
+ public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz) {
+ exportTemplate(response, fileName, fileName, clazz, false);
+ }
+
+ public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
+ Class<T> clazz) {
+ exportTemplate(response, fileName, sheetName, clazz, false);
+ }
+
+ public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz,
+ boolean isContainExample) {
+ exportTemplate(response, fileName, fileName, clazz, isContainExample);
+ }
+
+ public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
+ Class<T> clazz, boolean isContainExample) {
+ // 鑾峰彇琛ㄥご瀛楁
+ List<ExcelClassField> headFieldList = getExcelClassFieldList(clazz);
+ // 鑾峰彇琛ㄥご鏁版嵁鍜岀ず渚嬫暟鎹�
+ List<List<Object>> sheetDataList = new ArrayList<>();
+ List<Object> headList = new ArrayList<>();
+ List<Object> exampleList = new ArrayList<>();
+ Map<Integer, List<String>> selectMap = new LinkedHashMap<>();
+ for (int i = 0; i < headFieldList.size(); i++) {
+ ExcelClassField each = headFieldList.get(i);
+ headList.add(each.getName());
+ exampleList.add(each.getExample());
+ LinkedHashMap<String, String> kvMap = each.getKvMap();
+ if (kvMap != null && kvMap.size() > 0) {
+ selectMap.put(i, new ArrayList<>(kvMap.values()));
+ }
+ }
+ sheetDataList.add(headList);
+ if (isContainExample) {
+ sheetDataList.add(exampleList);
+ }
+ // 瀵煎嚭鏁版嵁
+ export(response, fileName, sheetName, sheetDataList, selectMap);
+ }
+
+ private static <T> List<ExcelClassField> getExcelClassFieldList(Class<T> clazz) {
+ // 瑙ｆ瀽鎵�鏈夊瓧娈�
+ Field[] fields = clazz.getDeclaredFields();
+ boolean hasExportAnnotation = false;
+ Map<Integer, List<ExcelClassField>> map = new LinkedHashMap<>();
+ List<Integer> sortList = new ArrayList<>();
+ for (Field field : fields) {
+ ExcelClassField cf = getExcelClassField(field);
+ if (cf.getHasAnnotation() == 1) {
+ hasExportAnnotation = true;
+ }
+ int sort = cf.getSort();
+ if (map.containsKey(sort)) {
+ map.get(sort).add(cf);
+ } else {
+ List<ExcelClassField> list = new ArrayList<>();
+ list.add(cf);
+ sortList.add(sort);
+ map.put(sort, list);
+ }
+ }
+ Collections.sort(sortList);
+ // 鑾峰彇琛ㄥご
+ List<ExcelClassField> headFieldList = new ArrayList<>();
+ if (hasExportAnnotation) {
+ for (Integer sort : sortList) {
+ for (ExcelClassField cf : map.get(sort)) {
+ if (cf.getHasAnnotation() == 1) {
+ headFieldList.add(cf);
+ }
+ }
+ }
+ } else {
+ headFieldList.addAll(map.get(0));
+ }
+ return headFieldList;
+ }
+
+ private static ExcelClassField getExcelClassField(Field field) {
+ ExcelClassField cf = new ExcelClassField();
+ String fieldName = field.getName();
+ cf.setFieldName(fieldName);
+ ExcelExport annotation = field.getAnnotation(ExcelExport.class);
+ // 鏃� ExcelExport 娉ㄨВ鎯呭喌
+ if (annotation == null) {
+ cf.setHasAnnotation(0);
+ cf.setName(fieldName);
+ cf.setSort(0);
+ return cf;
+ }
+ // 鏈� ExcelExport 娉ㄨВ鎯呭喌
+ cf.setHasAnnotation(1);
+ cf.setName(annotation.value());
+ String example = getString(annotation.example());
+ if (!example.isEmpty()) {
+ if (isNumeric(example) && example.length() < 8) {
+ cf.setExample(Double.valueOf(example));
+ } else {
+ cf.setExample(example);
+ }
+ } else {
+ cf.setExample("");
+ }
+ cf.setSort(annotation.sort());
+ // 瑙ｆ瀽鏄犲皠
+ String kv = getString(annotation.kv());
+ cf.setKvMap(getKvMap(kv));
+ return cf;
+ }
+
+ private static LinkedHashMap<String, String> getKvMap(String kv) {
+ LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
+ if (kv.isEmpty()) {
+ return kvMap;
+ }
+ String[] kvs = kv.split(";");
+ if (kvs.length == 0) {
+ return kvMap;
+ }
+ for (String each : kvs) {
+ String[] eachKv = getString(each).split("-");
+ if (eachKv.length != 2) {
+ continue;
+ }
+ String k = eachKv[0];
+ String v = eachKv[1];
+ if (k.isEmpty() || v.isEmpty()) {
+ continue;
+ }
+ kvMap.put(k, v);
+ }
+ return kvMap;
+ }
+
+ /**
+ * 瀵煎嚭琛ㄦ牸鍒版湰鍦�
+ *
+ * @param file 鏈湴鏂囦欢瀵硅薄
+ * @param sheetData 瀵煎嚭鏁版嵁
+ */
+ public static void exportFile(File file, List<List<Object>> sheetData) {
+ if (file == null) {
+ System.out.println("鏂囦欢鍒涘缓澶辫触");
+ return;
+ }
+ if (sheetData == null) {
+ sheetData = new ArrayList<>();
+ }
+ Map<String, List<List<Object>>> map = new HashMap<>();
+ map.put(file.getName(), sheetData);
+ export(null, file, file.getName(), map, null);
+ }
+
+ /**
+ * 瀵煎嚭琛ㄦ牸鍒版湰鍦�
+ *
+ * @param <T> 瀵煎嚭鏁版嵁绫讳技锛屽拰K绫诲瀷淇濇寔涓�鑷�
+ * @param filePath 鏂囦欢鐖惰矾寰勶紙濡傦細D:/doc/excel/锛�
+ * @param fileName 鏂囦欢鍚嶇О锛堜笉甯﹀熬缂�锛屽锛氬鐢熻〃锛�
+ * @param list 瀵煎嚭鏁版嵁
+ * @throws IOException IO寮傚父
+ */
+ public static <T> File exportFile(String filePath, String fileName, List<T> list) throws IOException {
+ File file = getFile(filePath, fileName);
+ List<List<Object>> sheetData = getSheetData(list);
+ exportFile(file, sheetData);
+ return file;
+ }
+
+ /**
+ * 鑾峰彇鏂囦欢
+ *
+ * @param filePath filePath 鏂囦欢鐖惰矾寰勶紙濡傦細D:/doc/excel/锛�
+ * @param fileName 鏂囦欢鍚嶇О锛堜笉甯﹀熬缂�锛屽锛氱敤鎴疯〃锛�
+ * @return 鏈湴File鏂囦欢瀵硅薄
+ */
+ private static File getFile(String filePath, String fileName) throws IOException {
+ String dirPath = getString(filePath);
+ String fileFullPath;
+ if (dirPath.isEmpty()) {
+ fileFullPath = fileName;
+ } else {
+ // 鍒ゅ畾鏂囦欢澶规槸鍚﹀瓨鍦紝濡傛灉涓嶅瓨鍦紝鍒欑骇鑱斿垱寤�
+ File dirFile = new File(dirPath);
+ if (!dirFile.exists()) {
+ boolean mkdirs = dirFile.mkdirs();
+ if (!mkdirs) {
+ return null;
+ }
+ }
+ // 鑾峰彇鏂囦欢澶瑰叏鍚�
+ if (dirPath.endsWith(String.valueOf(LEAN_LINE))) {
+ fileFullPath = dirPath + fileName + XLSX;
+ } else {
+ fileFullPath = dirPath + LEAN_LINE + fileName + XLSX;
+ }
+ }
+ System.out.println(fileFullPath);
+ File file = new File(fileFullPath);
+ if (!file.exists()) {
+ boolean result = file.createNewFile();
+ if (!result) {
+ return null;
+ }
+ }
+ return file;
+ }
+
+ private static <T> List<List<Object>> getSheetData(List<T> list) {
+ // 鑾峰彇琛ㄥご瀛楁
+ List<ExcelClassField> excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());
+ List<String> headFieldList = new ArrayList<>();
+ List<Object> headList = new ArrayList<>();
+ Map<String, ExcelClassField> headFieldMap = new HashMap<>();
+ for (ExcelClassField each : excelClassFieldList) {
+ String fieldName = each.getFieldName();
+ headFieldList.add(fieldName);
+ headFieldMap.put(fieldName, each);
+ headList.add(each.getName());
+ }
+ // 娣诲姞琛ㄥご鍚嶇О
+ List<List<Object>> sheetDataList = new ArrayList<>();
+ sheetDataList.add(headList);
+ // 鑾峰彇琛ㄦ暟鎹�
+ for (T t : list) {
+ Map<String, Object> fieldDataMap = getFieldDataMap(t);
+ Set<String> fieldDataKeys = fieldDataMap.keySet();
+ List<Object> rowList = new ArrayList<>();
+ for (String headField : headFieldList) {
+ if (!fieldDataKeys.contains(headField)) {
+ continue;
+ }
+ Object data = fieldDataMap.get(headField);
+ if (data == null) {
+ rowList.add("");
+ continue;
+ }
+ ExcelClassField cf = headFieldMap.get(headField);
+ // 鍒ゆ柇鏄惁鏈夋槧灏勫叧绯�
+ LinkedHashMap<String, String> kvMap = cf.getKvMap();
+ if (kvMap == null || kvMap.isEmpty()) {
+ rowList.add(data);
+ continue;
+ }
+ String val = kvMap.get(data.toString());
+ if (isNumeric(val)) {
+ rowList.add(Double.valueOf(val));
+ } else {
+ rowList.add(val);
+ }
+ }
+ sheetDataList.add(rowList);
+ }
+ return sheetDataList;
+ }
+
+ private static <T> Map<String, Object> getFieldDataMap(T t) {
+ Map<String, Object> map = new HashMap<>();
+ Field[] fields = t.getClass().getDeclaredFields();
+ try {
+ for (Field field : fields) {
+ String fieldName = field.getName();
+ field.setAccessible(true);
+ Object object = field.get(t);
+ map.put(fieldName, object);
+ }
+ } catch (IllegalArgumentException | IllegalAccessException e) {
+ e.printStackTrace();
+ }
+ return map;
+ }
+
+ public static void exportEmpty(HttpServletResponse response, String fileName) {
+ List<List<Object>> sheetDataList = new ArrayList<>();
+ List<Object> headList = new ArrayList<>();
+ headList.add("瀵煎嚭鏃犳暟鎹�");
+ sheetDataList.add(headList);
+ export(response, fileName, sheetDataList);
+ }
+
+ public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList) {
+ export(response, fileName, fileName, sheetDataList, null);
+ }
+
+ public static void exportManySheet(HttpServletResponse response, String fileName, Map<String, List<List<Object>>> sheetMap) {
+ export(response, null, fileName, sheetMap, null);
+ }
+
+
+ public static void export(HttpServletResponse response, String fileName, String sheetName,
+ List<List<Object>> sheetDataList) {
+ export(response, fileName, sheetName, sheetDataList, null);
+ }
+
+ public static void export(HttpServletResponse response, String fileName, String sheetName,
+ List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
+
+ Map<String, List<List<Object>>> map = new HashMap<>();
+ map.put(sheetName, sheetDataList);
+ export(response, null, fileName, map, selectMap);
+ }
+
+ public static <T, K> void export(HttpServletResponse response, String fileName, List<T> list, Class<K> template) {
+ // list 鏄惁涓虹┖
+ boolean lisIsEmpty = list == null || list.isEmpty();
+ // 濡傛灉妯℃澘鏁版嵁涓虹┖锛屼笖瀵煎叆鐨勬暟鎹负绌猴紝鍒欏鍑虹┖鏂囦欢
+ if (template == null && lisIsEmpty) {
+ exportEmpty(response, fileName);
+ return;
+ }
+ // 濡傛灉 list 鏁版嵁锛屽垯瀵煎嚭妯℃澘鏁版嵁
+ if (lisIsEmpty) {
+ exportTemplate(response, fileName, template);
+ return;
+ }
+ // 瀵煎嚭鏁版嵁
+ List<List<Object>> sheetDataList = getSheetData(list);
+ export(response, fileName, sheetDataList);
+ }
+
+ public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
+ export(response, fileName, fileName, sheetDataList, selectMap);
+ }
+
+ private static void export(HttpServletResponse response, File file, String fileName,
+ Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap) {
+ // 鏁翠釜 Excel 琛ㄦ牸 book 瀵硅薄
+ SXSSFWorkbook book = new SXSSFWorkbook();
+ // 姣忎釜 Sheet 椤�
+ Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
+ for (Entry<String, List<List<Object>>> entry : entries) {
+ List<List<Object>> sheetDataList = entry.getValue();
+ Sheet sheet = book.createSheet(entry.getKey());
+ Drawing<?> patriarch = sheet.createDrawingPatriarch();
+ // 璁剧疆琛ㄥご鑳屾櫙鑹诧紙鐏拌壊锛�
+ CellStyle headStyle = book.createCellStyle();
+ headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
+ headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ headStyle.setAlignment(HorizontalAlignment.CENTER);
+ headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
+ // 璁剧疆琛ㄨ韩鑳屾櫙鑹诧紙榛樿鑹诧級
+ CellStyle rowStyle = book.createCellStyle();
+ rowStyle.setAlignment(HorizontalAlignment.CENTER);
+ rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 璁剧疆琛ㄦ牸鍒楀搴︼紙榛樿涓�15涓瓧鑺傦級
+ sheet.setDefaultColumnWidth(15);
+ // 鍒涘缓鍚堝苟绠楁硶鏁扮粍
+ int rowLength = sheetDataList.size();
+ int columnLength = sheetDataList.get(0).size();
+ int[][] mergeArray = new int[rowLength][columnLength];
+ for (int i = 0; i < sheetDataList.size(); i++) {
+ // 姣忎釜 Sheet 椤典腑鐨勮鏁版嵁
+ Row row = sheet.createRow(i);
+ List<Object> rowList = sheetDataList.get(i);
+ for (int j = 0; j < rowList.size(); j++) {
+ // 姣忎釜琛屾暟鎹腑鐨勫崟鍏冩牸鏁版嵁
+ Object o = rowList.get(j);
+ int v = 0;
+ if (o instanceof URL) {
+ // 濡傛灉瑕佸鍑哄浘鐗囩殑璇�, 閾炬帴闇�瑕佷紶閫� URL 瀵硅薄
+ setCellPicture(book, row, patriarch, i, j, (URL) o);
+ } else {
+ Cell cell = row.createCell(j);
+ if (i == 0) {
+ // 绗竴琛屼负琛ㄥご琛岋紝閲囩敤鐏拌壊搴曡儗鏅�
+ v = setCellValue(cell, o, headStyle);
+ } else {
+ // 鍏朵粬琛屼负鏁版嵁琛岋紝榛樿鐧藉簳鑹�
+ v = setCellValue(cell, o, rowStyle);
+ }
+ }
+ mergeArray[i][j] = v;
+ }
+ }
+ // 鍚堝苟鍗曞厓鏍�
+ mergeCells(sheet, mergeArray);
+ // 璁剧疆涓嬫媺鍒楄〃
+ setSelect(sheet, selectMap);
+ }
+ // 鍐欐暟鎹�
+ if (response != null) {
+ // 鍓嶇瀵煎嚭
+ try {
+ write(response, book, fileName);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ } else {
+ // 鏈湴瀵煎嚭
+ FileOutputStream fos;
+ try {
+ fos = new FileOutputStream(file);
+ ByteArrayOutputStream ops = new ByteArrayOutputStream();
+ book.write(ops);
+ fos.write(ops.toByteArray());
+ fos.close();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ /**
+ * 鍚堝苟褰撳墠Sheet椤电殑鍗曞厓鏍�
+ *
+ * @param sheet 褰撳墠 sheet 椤�
+ * @param mergeArray 鍚堝苟鍗曞厓鏍肩畻娉�
+ */
+ private static void mergeCells(Sheet sheet, int[][] mergeArray) {
+ // 妯悜鍚堝苟
+ for (int x = 0; x < mergeArray.length; x++) {
+ int[] arr = mergeArray[x];
+ boolean merge = false;
+ int y1 = 0;
+ int y2 = 0;
+ for (int y = 0; y < arr.length; y++) {
+ int value = arr[y];
+ if (value == CELL_COLUMN_MERGE) {
+ if (!merge) {
+ y1 = y;
+ }
+ y2 = y;
+ merge = true;
+ } else {
+ merge = false;
+ if (y1 > 0) {
+ sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
+ }
+ y1 = 0;
+ y2 = 0;
+ }
+ }
+ if (y1 > 0) {
+ sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
+ }
+ }
+ // 绾靛悜鍚堝苟
+ int xLen = mergeArray.length;
+ int yLen = mergeArray[0].length;
+ for (int y = 0; y < yLen; y++) {
+ boolean merge = false;
+ int x1 = 0;
+ int x2 = 0;
+ for (int x = 0; x < xLen; x++) {
+ int value = mergeArray[x][y];
+ if (value == CELL_ROW_MERGE) {
+ if (!merge) {
+ x1 = x;
+ }
+ x2 = x;
+ merge = true;
+ } else {
+ merge = false;
+ if (x1 > 0) {
+ // 妫�鏌ユ槸鍚︿笌鐜版湁鍚堝苟鍖哄煙閲嶅彔锛屽鏋滄湁鍒欏垹闄ゆ棫鍖哄煙锛屽啀鍚堝苟鏂板尯鍩�
+ CellRangeAddress newRegion = new CellRangeAddress((x1 - 1), x2, y, y);
+ handleOverlappingRegions(sheet, newRegion);
+ }
+ x1 = 0;
+ x2 = 0;
+ }
+ }
+ if (x1 > 0) {
+ // 妫�鏌ユ槸鍚︿笌鐜版湁鍚堝苟鍖哄煙閲嶅彔锛屽鏋滄湁鍒欏垹闄ゆ棫鍖哄煙锛屽啀鍚堝苟鏂板尯鍩�
+ CellRangeAddress newRegion = new CellRangeAddress((x1 - 1), x2, y, y);
+ handleOverlappingRegions(sheet, newRegion);
+ }
+ }
+ }
+
+ /**
+ * 妫�鏌ユ槸鍚︿笌鐜版湁鍚堝苟鍖哄煙閲嶅彔锛屽鏋滄湁鍒欏垹闄ゆ棫鍖哄煙锛屽啀鍚堝苟鏂板尯鍩�
+ * @param sheet Excel宸ヤ綔琛�
+ * @param newRegion 鏂扮殑鍚堝苟鍖哄煙
+ */
+ private static void handleOverlappingRegions(Sheet sheet, CellRangeAddress newRegion) {
+ // 绉婚櫎閲嶅彔鐨勭幇鏈夊尯鍩�
+ List<CellRangeAddress> overlappingRegions = new ArrayList<>();
+ for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
+ CellRangeAddress existingRegion = sheet.getMergedRegion(i);
+ if (newRegion.intersects(existingRegion)) {
+ overlappingRegions.add(existingRegion);
+ sheet.removeMergedRegion(i);
+ }
+ }
+ // 鍚堝苟鍒版柊鍖哄煙锛堣繖閲岀畝鍗曞湴灏嗗畠浠坊鍔犲埌鏂扮殑鍚堝苟鍖哄煙鍒楄〃涓紝瀹為檯搴旂敤涓彲鑳介渶瑕佹洿澶嶆潅鐨勯�昏緫鏉ョ湡姝ｂ�滃悎骞垛�濆尯鍩燂級
+ // 娉ㄦ剰锛歅OI鐨凜ellRangeAddress娌℃湁鎻愪緵鐩存帴鍚堝苟涓や釜鍖哄煙鐨勬柟娉曪紝鎵�浠ヨ繖閲屽彧鏄ず渚�
+ // 濡傛灉闇�瑕佺湡姝ｇ殑鍚堝苟锛屽彲鑳介渶瑕佽嚜瀹氫箟閫昏緫鏉ヨ绠楁柊鐨勮竟鐣�
+ for (CellRangeAddress overlappingRegion : overlappingRegions) {
+ sheet.addMergedRegion(overlappingRegion);
+ }
+ sheet.addMergedRegion(newRegion);
+ }
+
+
+ private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX;
+ response.addHeader("Content-Disposition", "attachment;filename=" + name);
+ ServletOutputStream out = response.getOutputStream();
+ book.write(out);
+ out.flush();
+ out.close();
+ }
+
+ private static int setCellValue(Cell cell, Object o, CellStyle style) {
+ // 璁剧疆鏍峰紡
+ cell.setCellStyle(style);
+ // 鏁版嵁涓虹┖鏃�
+ if (o == null) {
+ cell.setCellType(CellType.STRING);
+ cell.setCellValue("");
+ return CELL_OTHER;
+ }
+ // 鏄惁涓哄瓧绗︿覆
+ if (o instanceof String) {
+ String s = o.toString();
+ // 褰撴暟瀛楃被鍨嬮暱搴﹁秴杩�8浣嶆椂锛屾敼涓哄瓧绗︿覆绫诲瀷鏄剧ず锛圗xcel鏁板瓧瓒呰繃涓�瀹氶暱搴︿細鏄剧ず涓虹瀛﹁鏁版硶锛�
+ if (isNumeric(s) && s.length() < 8) {
+ cell.setCellType(CellType.NUMERIC);
+ cell.setCellValue(Double.parseDouble(s));
+ return CELL_OTHER;
+ } else {
+ cell.setCellType(CellType.STRING);
+ cell.setCellValue(s);
+ }
+ if (s.equals(ROW_MERGE)) {
+ return CELL_ROW_MERGE;
+ } else if (s.equals(COLUMN_MERGE)) {
+ return CELL_COLUMN_MERGE;
+ } else {
+ return CELL_OTHER;
+ }
+ }
+ // 鏄惁涓哄瓧绗︿覆
+ if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
+ cell.setCellType(CellType.NUMERIC);
+ cell.setCellValue(Double.parseDouble(o.toString()));
+ return CELL_OTHER;
+ }
+ // 鏄惁涓築oolean
+ if (o instanceof Boolean) {
+ cell.setCellType(CellType.BOOLEAN);
+ cell.setCellValue((Boolean) o);
+ return CELL_OTHER;
+ }
+ // 濡傛灉鏄疊igDecimal锛屽垯榛樿3浣嶅皬鏁�
+ if (o instanceof BigDecimal) {
+ cell.setCellType(CellType.NUMERIC);
+ cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue());
+ return CELL_OTHER;
+ }
+ // 濡傛灉鏄疍ate鏁版嵁锛屽垯鏄剧ず鏍煎紡鍖栨暟鎹�
+ if (o instanceof Date) {
+ cell.setCellType(CellType.STRING);
+ cell.setCellValue(formatDate((Date) o));
+ return CELL_OTHER;
+ }
+ // 濡傛灉鏄叾浠栵紝鍒欓粯璁ゅ瓧绗︿覆绫诲瀷
+ cell.setCellType(CellType.STRING);
+ cell.setCellValue(o.toString());
+ return CELL_OTHER;
+ }
+
+ private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) {
+ // 璁剧疆鍥剧墖瀹介珮
+ sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
+ // 锛坖dk1.7鐗堟湰try涓畾涔夋祦鍙嚜鍔ㄥ叧闂級
+ try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
+ byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
+ int rc;
+ while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
+ outputStream.write(buff, 0, rc);
+ }
+ // 璁剧疆鍥剧墖浣嶇疆
+ XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
+ // 璁剧疆杩欎釜锛屽浘鐗囦細鑷姩濉弧鍗曞厓鏍肩殑闀垮
+ anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
+ patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ private static String formatDate(Date date) {
+ if (date == null) {
+ return "";
+ }
+ SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
+ return format.format(date);
+ }
+
+ private static void setSelect(Sheet sheet, Map<Integer, List<String>> selectMap) {
+ if (selectMap == null || selectMap.isEmpty()) {
+ return;
+ }
+ Set<Entry<Integer, List<String>>> entrySet = selectMap.entrySet();
+ for (Entry<Integer, List<String>> entry : entrySet) {
+ int y = entry.getKey();
+ List<String> list = entry.getValue();
+ if (list == null || list.isEmpty()) {
+ continue;
+ }
+ String[] arr = new String[list.size()];
+ for (int i = 0; i < list.size(); i++) {
+ arr[i] = list.get(i);
+ }
+ DataValidationHelper helper = sheet.getDataValidationHelper();
+ CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
+ DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
+ DataValidation dv = helper.createValidation(dvc, addressList);
+ if (dv instanceof HSSFDataValidation) {
+ dv.setSuppressDropDownArrow(false);
+ } else {
+ dv.setSuppressDropDownArrow(true);
+ dv.setShowErrorBox(true);
+ }
+ sheet.addValidationData(dv);
+ }
+ }
+
+ private static boolean isNumeric(String str) {
+ if(StringUtils.isEmpty(str)){
+ return false;
+ }
+ if (Objects.nonNull(str) && "0.0".equals(str)) {
+ return true;
+ }
+ for (int i = str.length(); --i >= 0; ) {
+ if (!Character.isDigit(str.charAt(i))) {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private static String getString(String s) {
+ if (s == null) {
+ return "";
+ }
+ if (s.isEmpty()) {
+ return s;
+ }
+ return s.trim();
+ }
+
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java b/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
index ccff5e6..b3890d8 100644
--- a/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
+++ b/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
@@ -26,6 +26,7 @@
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
+import java.io.UnsupportedEncodingException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;
@@ -125,4 +126,14 @@
util.exportExcel(response, list , "鍔充繚鍙拌处");
}
+ /**
+ * 鍔充繚鍙戞斁-瀵煎嚭
+ */
+ @Log(title = "鍔充繚鍙戞斁-瀵煎嚭", businessType = BusinessType.EXPORT)
+ @PostMapping("/exportCopy")
+ @ApiOperation("鍔充繚鍙戞斁-瀵煎嚭")
+ public void exportCopy(HttpServletResponse response,LaborIssue laborIssue) throws UnsupportedEncodingException {
+ laborIssueService.exportCopy(response,laborIssue);
+ }
+
}
diff --git a/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java b/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
index 5a6f765..dc97453 100644
--- a/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
+++ b/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
@@ -6,6 +6,9 @@
import com.ruoyi.lavorissue.dto.StatisticsLaborIssue;
import com.ruoyi.lavorissue.pojo.LaborIssue;
+import javax.servlet.http.HttpServletResponse;
+import java.io.UnsupportedEncodingException;
+
/**
* @author :yys
* @date : 2025/8/13 11:18
@@ -22,4 +25,6 @@
IPage<LaborIssue> listPage(Page page, LaborIssue laborIssue);
StatisticsLaborIssue statistics(StatisticsLaborIssue req) throws Exception;
+
+ void exportCopy(HttpServletResponse response, LaborIssue laborIssue) throws UnsupportedEncodingException;
}
diff --git a/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java b/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
index b6a5e27..a3da8f9 100644
--- a/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
+++ b/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
@@ -1,23 +1,38 @@
package com.ruoyi.lavorissue.service.impl;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.ExcelWriter;
+import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.approve.utils.StartAndEndDateDto;
+import com.ruoyi.common.utils.SecurityUtils;
+import com.ruoyi.common.utils.excel.ConfigurableMergeStrategy;
+import com.ruoyi.common.utils.excel.CustomCellStyleHandler;
import com.ruoyi.lavorissue.dto.StatisticsLaborIssue;
import com.ruoyi.lavorissue.mapper.LavorIssueMapper;
import com.ruoyi.lavorissue.pojo.LaborIssue;
import com.ruoyi.lavorissue.service.LavorIssueService;
+import com.ruoyi.project.system.domain.SysDept;
+import com.ruoyi.project.system.domain.SysDictData;
+import com.ruoyi.project.system.mapper.SysDeptMapper;
+import com.ruoyi.project.system.mapper.SysDictDataMapper;
import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.UnsupportedEncodingException;
+import java.net.URLEncoder;
import java.text.SimpleDateFormat;
-import java.util.Calendar;
-import java.util.Date;
-import java.util.List;
+import java.util.*;
+import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
@@ -46,22 +61,31 @@
if(season != null){
Calendar calendar = Calendar.getInstance();
int currentYear = calendar.get(Calendar.YEAR);
+ startAndEndDateDto.setYear(currentYear);
switch (season){
case 1:
startAndEndDateDto.setStartDate(currentYear + "-01-01");
startAndEndDateDto.setEndDate(currentYear + "-03-31");
+ startAndEndDateDto.setStartMonth(1);
+ startAndEndDateDto.setEndMonth(3);
break;
case 2:
startAndEndDateDto.setStartDate(currentYear + "-04-01");
startAndEndDateDto.setEndDate(currentYear + "-06-30");
+ startAndEndDateDto.setStartMonth(4);
+ startAndEndDateDto.setEndMonth(6);
break;
case 3:
startAndEndDateDto.setStartDate(currentYear + "-07-01");
startAndEndDateDto.setEndDate(currentYear + "-09-30");
+ startAndEndDateDto.setStartMonth(7);
+ startAndEndDateDto.setEndMonth(9);
break;
case 4:
startAndEndDateDto.setStartDate(currentYear + "-10-01");
startAndEndDateDto.setEndDate(currentYear + "-12-31");
+ startAndEndDateDto.setStartMonth(10);
+ startAndEndDateDto.setEndMonth(12);
break;
}
}
@@ -112,6 +136,172 @@
return statisticsLaborIssue;
}
+ @Autowired
+ private SysDictDataMapper sysDictDataMapper;
+
+ @Override
+ public void exportCopy(HttpServletResponse response, LaborIssue laborIssue) throws UnsupportedEncodingException {
+ List<SysDictData> sys_lavor_issue = sysDictDataMapper.selectDictDataByType("sys_lavor_issue");
+ if(CollectionUtils.isEmpty(sys_lavor_issue)){
+ throw new RuntimeException("瀛楀吀鏁版嵁涓虹┖");
+ }
+ StartAndEndDateDto startAndEndDateDto = getStartAndEndDateDto(laborIssue.getSeason(), laborIssue.getIssueDate());
+ BeanUtils.copyProperties(startAndEndDateDto, laborIssue);
+ List<LaborIssue> laborIssues = lavorIssueMapper.list(laborIssue);
+ if (CollectionUtils.isEmpty(laborIssues)) {
+ throw new RuntimeException("鏁版嵁涓虹┖");
+ }
+ Map<String, List<LaborIssue>> laborIssueMap = laborIssues.stream()
+ .collect(Collectors.groupingBy(LaborIssue::getStaffNo));
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding("UTF-8");
+ // 杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+ String fileName = URLEncoder.encode("澶栭儴瑁呯鍗�", "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+
+ try {
+ //鏂板缓ExcelWriter
+ ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
+ .registerWriteHandler(new CustomCellStyleHandler())
+ .registerWriteHandler(new ConfigurableMergeStrategy(exportExternalPackingListGetMerge(4,sys_lavor_issue.size(),laborIssueMap.size())))
+ .build();
+
+ List<List<List<String>>> data = completeExternalPackingList(laborIssueMap, sys_lavor_issue,laborIssue);
+
+ for (int i = 0; i < data.size(); i++) {
+ List<List<String>> list = data.get(i);
+
+ //鑾峰彇sheet0瀵硅薄
+ WriteSheet mainSheet = EasyExcel.writerSheet(i, "澶栭儴瑁呯鍗�" + i).build();
+ //鍚憇heet0鍐欏叆鏁版嵁 浼犲叆绌簂ist杩欐牱鍙鍑鸿〃澶�
+ excelWriter.write(list, mainSheet);
+ }
+
+ //鍏抽棴娴�
+ excelWriter.finish();
+ } catch (IOException e) {
+ throw new RuntimeException("瀵煎嚭澶辫触");
+ }
+ }
+
+ @Autowired
+ private SysDeptMapper sysDeptMapper;
+
+
+// 澶勭悊澶栭儴瑁呯鍗曟暟鎹�
+ private List<List<List<String>>> completeExternalPackingList(Map<String, List<LaborIssue>> listMap,List<SysDictData> sys_lavor_issue,LaborIssue obj) {
+ List<List<List<String>>> data = new ArrayList<>();
+ int num = sys_lavor_issue.size();
+
+ List<List<String>> item = new ArrayList<>();
+ List<String> list = new ArrayList<>();
+ list.add("閮ㄩ棬");
+ list.add("");
+ list.add("浼佷笟绠＄悊绉�");
+ list.add("");
+ list.add("浼佷笟绠＄悊绉�" + obj.getYear() + "骞�" + obj.getStartMonth() + "鏈�-" + obj.getYear() + "骞�" + obj.getEndMonth() +"鏈堝姵淇濆彂鏀捐鍒掕〃");
+ for (int i = 1; i <= num; i++){
+ list.add("");
+ }
+ item.add(list);
+
+ List<String> list1 = new ArrayList<>();
+ list1.add("寮�濮嬪勾/鏈�");
+ list1.add("");
+ list1.add("缁撴潫骞�/鏈�");
+ list1.add("");
+ for (int i = 0; i <= num; i++){
+ list1.add("");
+ }
+ item.add(list1);
+
+ List<String> list2 = new ArrayList<>();
+ list2.add(obj.getYear().toString());
+ list2.add(obj.getStartMonth().toString());
+ list2.add(obj.getYear().toString());
+ list2.add(obj.getEndMonth().toString());
+ for (int i = 0; i <= num; i++){
+ list.add("");
+ }
+ item.add(list2);
+
+ List<String> list3 = new ArrayList<>();
+ list3.add("閮ㄩ棬鍚嶇О");
+ list3.add("");
+ list3.add("濮撳悕");
+ list3.add("宸ュ彿");
+ for (SysDictData sysDictData : sys_lavor_issue) {
+ list3.add(sysDictData.getDictLabel());
+ }
+ list3.add("绛惧悕");
+ item.add(list3);
+
+ // 濉��
+ SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getTenantId());
+ List<String> sumList = new ArrayList<>();
+ AtomicInteger i = new AtomicInteger();
+ listMap.forEach((key, value) -> {
+ List<String> list4 = new ArrayList<>();
+ list4.add(sysDept.getDeptName());
+ list4.add("");
+ list4.add(value.get(0).getStaffName());
+ list4.add(value.get(0).getStaffNo());
+ int j = 0;
+ for (SysDictData sysDictData : sys_lavor_issue) {
+ list4.add(value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum()
+ + "");
+ if(i.get() == 0){
+ sumList.add(value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum()
+ + "");
+ }else {
+ sumList.set(j,(Long.parseLong(sumList.get(j)) + value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum())
+ + "");
+ }
+ j++;
+ }
+ i.getAndIncrement();
+ item.add(list4);
+ });
+ List<String> list5 = new ArrayList<>();
+ list5.add("鍚堣");
+ list5.add("");
+ list5.add("");
+ list5.add("");
+ for (int h = 0; h < sumList.size(); h++){
+ list5.add(sumList.get(h));
+ }
+ item.add(list5);
+ data.add(item);
+ return data;
+ }
+
+ /**
+ * 澶栭儴瑁呯鍗曞悎骞跺崟鍏冩牸
+ *
+ * @return
+ */
+ private List<CellRangeAddress> exportExternalPackingListGetMerge(Integer rowNum,Integer num,Integer listSize) {
+ List<CellRangeAddress> mergeRegions = new ArrayList<>();
+ mergeRegions.add(new CellRangeAddress(0, 0, 0, 1)); // 鍚堝苟A1:Q2
+ mergeRegions.add(new CellRangeAddress(0, 0, 2, 3)); // 鍚堝苟A4:Q4
+
+ mergeRegions.add(new CellRangeAddress(0, 2, rowNum, rowNum + num)); // 鍚堝苟A5:B6
+ mergeRegions.add(new CellRangeAddress(1, 1, 0, 1)); // 鍚堝苟C5:E6
+ mergeRegions.add(new CellRangeAddress(1, 1, 2, 3)); // 鍚堝苟F5:G6
+ mergeRegions.add(new CellRangeAddress(3, 3, 0, 1)); // 鍚堝苟H5:I6
+ for (int i = 1; i <= listSize; i++) {
+ mergeRegions.add(new CellRangeAddress(3 + i, 3 + i, 0, 1));
+ }
+ mergeRegions.add(new CellRangeAddress(rowNum + listSize, rowNum + listSize, 0, 3));
+ return mergeRegions;
+ }
+
public Date getLastDayOfMonth(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date); // 璁剧疆浼犲叆鐨凞ate
diff --git a/src/main/resources/mapper/lavorissue/LavorIssueMapper.xml b/src/main/resources/mapper/lavorissue/LavorIssueMapper.xml
index fabf558..dcde507 100644
--- a/src/main/resources/mapper/lavorissue/LavorIssueMapper.xml
+++ b/src/main/resources/mapper/lavorissue/LavorIssueMapper.xml
@@ -55,6 +55,12 @@
<if test="req.issueDate != null">
and t1.issue_date < #{req.issueDate}
</if>
+ <if test="req.staffId != null">
+ and t1.staff_id = #{req.staffId}
+ </if>
+ <if test="req.season != null and req.season != ''">
+ and (t1.issue_date >= #{req.startDate} and t1.issue_date <= #{req.endDate})
+ </if>
</where>
</select>
</mapper>
\ No newline at end of file
--
Gitblit v1.9.3