From 2e85e2dda945ca269d87b7f1f6147246be9accfa Mon Sep 17 00:00:00 2001
From: maven <2163098428@qq.com>
Date: 星期一, 18 八月 2025 13:21:05 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/pim_yys'
---
src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java | 1039 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 1,039 insertions(+), 0 deletions(-)
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
--
Gitblit v1.9.3