From e301d5c4e6de0aca423c54481d100e6ca2886b01 Mon Sep 17 00:00:00 2001
From: jinlin <jinlin>
Date: 星期六, 08 十一月 2025 22:57:12 +0800
Subject: [PATCH] 清洗数据库日期错误，并且导入日期通用化处理
---
src/main/java/com/example/server/utils/ImportUtil.java | 190 +++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 190 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/example/server/utils/ImportUtil.java b/src/main/java/com/example/server/utils/ImportUtil.java
index 72b0eea..b36ec60 100644
--- a/src/main/java/com/example/server/utils/ImportUtil.java
+++ b/src/main/java/com/example/server/utils/ImportUtil.java
@@ -1,14 +1,20 @@
package com.example.server.utils;
+import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.text.DateFormat;
+import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;
+
+import static org.apache.commons.lang3.ObjectUtils.isEmpty;
+
public class ImportUtil {
@@ -77,4 +83,188 @@
}
return cellStr;
}
+
+ /**
+ * 鍒ゆ柇鏃堕棿鏍煎紡鏄惁姝ｇ‘
+ *
+ * @param value
+ * @param format
+ * @return
+ */
+ public static boolean isDate(String value, String format) {
+
+ SimpleDateFormat sdf = null;
+ ParsePosition pos = new ParsePosition(0);//鎸囧畾浠庢墍浼犲瓧绗︿覆鐨勯浣嶅紑濮嬭В鏋�
+
+ if (value == null || isEmpty(format)) {
+ return false;
+ }
+ try {
+ sdf = new SimpleDateFormat(format);
+ sdf.setLenient(false);
+ Date date = sdf.parse(value, pos);
+ if (date == null) {
+ return false;
+ } else {
+ //鏇翠负涓ヨ皑鐨勬棩鏈�,濡�2011-03-024璁や负鏄笉鍚堟硶鐨�
+ if (pos.getIndex() > sdf.format(date).length()) {
+ return false;
+ }
+ return true;
+ }
+ } catch (Exception e) {
+ return false;
+ }
+ }
+ /**
+ * 瀵煎叆寮傚父澶勭悊
+ * @param errMap
+ * @param errSting
+ * @param sheetName
+ * @param row
+ */
+ public static void updateErrMap(Map<String, Object> errMap, String errSting, String sheetName, int row) {
+ if (!errMap.containsKey(errSting)) {
+ errMap.put(errSting, new ImportErrMessage(sheetName, row + "銆�"));
+ } else {
+ ImportErrMessage importErrMessage1 = (ImportErrMessage) errMap.get(errSting);
+ importErrMessage1.setLineNumber(importErrMessage1.getLineNumber() + row + "銆�");
+ }
+ }
+ /**
+ * 浠嶦xcel鐨勬棩鏈熷瓧娈靛鍏ユ暟鎹�
+ * 1. 濡傛灉Excel鍗曞厓鏍间负绌猴紝鍒欒繑鍥瀗ull
+ * 2. 鏃犺Excel鍗曞厓鏍兼牸寮忔槸浠�涔堬紝鍙鏄棩鏈熷唴瀹癸紝閮借兘瀵煎叆锛岃繑鍥濪ate瀵硅薄锛堝崟鍏冩牸涓烘枃鏈牸寮忔椂鏀寔yyyy-mm-dd銆亂yyy/mm/dd銆亂yyy.mm.dd鍙妝yyy骞碝M鏈坉d鏃ユ牸寮忥級
+ * 3. 濡傛灉Excel鍗曞厓鏍煎唴瀹逛笉鏄棩鏈燂紝鍒欐姏鍑篍xcelImportException
+ */
+ public static Date getDateFromCell(Cell cell) throws ExcelImportException {
+ if (cell == null) return null;
+
+ //瀹氫箟鏃堕棿鏍煎紡
+ List<String> DateFormatList = new ArrayList<>();
+ DateFormatList.add("yyyy骞碝M鏈坉d鏃�");
+ DateFormatList.add("yyyy/MM/dd");
+ DateFormatList.add("yyyy-MM-dd");
+ DateFormatList.add("yyyy.MM.dd");
+ List<SimpleDateFormat> formatList = new ArrayList<>();
+ formatList.add(new SimpleDateFormat("yyyy骞碝M鏈坉d鏃�"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy/MM/dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy-MM-dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy.MM.dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+
+ switch (cell.getCellType()) {
+ case STRING:
+ String strValue = cell.getStringCellValue();
+ if (StringUtils.isBlank(strValue)) return null;
+
+ strValue = strValue.trim();
+ try {
+ for (int i = 0; i < DateFormatList.size(); i++) {
+ if (ImportUtil.isDate(strValue, DateFormatList.get(i))) {
+ Date date = formatList.get(i).parse(strValue);
+ if (date != null) {
+ return date;
+ }
+ }
+ }
+ } catch (ParseException pe) {
+ throw new ExcelImportException("璇ュ崟鍏冩牸鏃ユ湡鏍煎紡涓嶆纭�");
+ }
+ throw new ExcelImportException("璇ュ崟鍏冩牸鏃ユ湡鏍煎紡涓嶆纭�");
+
+ case NUMERIC:
+ if (DateUtil.isCellDateFormatted(cell)) {
+ // 姝ｅ父鏃ユ湡鏍煎紡
+ Date date = cell.getDateCellValue();
+ return date;
+ } else {
+ // 妫�鏌ユ槸鍚︽槸鏁板瓧褰㈠紡鐨勬棩鏈�
+ double numericValue = cell.getNumericCellValue();
+ if (isLikelyExcelDate(numericValue)) {
+ // 灏咵xcel鏁板瓧鏃ユ湡杞崲涓篔ava鏃ユ湡
+ Date date = DateUtil.getJavaDate(numericValue);
+ return date;
+ } else {
+ // 纭疄鏄櫘閫氭暟瀛�
+ throw new ExcelImportException("璇ュ崟鍏冩牸鍐呭涓嶆槸鏃ユ湡");
+ }
+ }
+
+ default:
+ throw new ExcelImportException("璇ュ崟鍏冩牸鍐呭涓嶆槸鏃ユ湡");
+ }
+ }
+
+ public static String getDateStrFromCell(Cell cell) throws ExcelImportException {
+ if (cell == null) return null;
+
+ //瀹氫箟鏃堕棿鏍煎紡
+ List<String> DateFormatList = new ArrayList<>();
+ DateFormatList.add("yyyy骞碝M鏈坉d鏃�");
+ DateFormatList.add("yyyy/MM/dd");
+ DateFormatList.add("yyyy-MM-dd");
+ DateFormatList.add("yyyy.MM.dd");
+ List<SimpleDateFormat> formatList = new ArrayList<>();
+ formatList.add(new SimpleDateFormat("yyyy骞碝M鏈坉d鏃�"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy/MM/dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy-MM-dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+ formatList.add(new SimpleDateFormat("yyyy.MM.dd"));//灏忓啓鐨刴m琛ㄧず鐨勬槸鍒嗛挓
+
+ switch (cell.getCellType()) {
+ case STRING:
+ String strValue = cell.getStringCellValue();
+ if (StringUtils.isBlank(strValue)) return null;
+
+ strValue = strValue.trim();
+ try {
+ for (int i = 0; i < DateFormatList.size(); i++) {
+ if (ImportUtil.isDate(strValue, DateFormatList.get(i))) {
+ Date date = formatList.get(i).parse(strValue);
+ if (date != null) {
+ return dateToString(date);
+ }
+ }
+ }
+ } catch (ParseException pe) {
+ throw new ExcelImportException("璇ュ崟鍏冩牸鏃ユ湡鏍煎紡涓嶆纭�");
+ }
+ throw new ExcelImportException("璇ュ崟鍏冩牸鏃ユ湡鏍煎紡涓嶆纭�");
+
+ case NUMERIC:
+ if (DateUtil.isCellDateFormatted(cell)) {
+ // 姝ｅ父鏃ユ湡鏍煎紡
+ Date date = cell.getDateCellValue();
+ return dateToString(date);
+ } else {
+ // 妫�鏌ユ槸鍚︽槸鏁板瓧褰㈠紡鐨勬棩鏈�
+ double numericValue = cell.getNumericCellValue();
+ if (isLikelyExcelDate(numericValue)) {
+ // 灏咵xcel鏁板瓧鏃ユ湡杞崲涓篔ava鏃ユ湡
+ Date date = DateUtil.getJavaDate(numericValue);
+ return dateToString(date);
+ } else {
+ // 纭疄鏄櫘閫氭暟瀛�
+ throw new ExcelImportException("璇ュ崟鍏冩牸鍐呭涓嶆槸鏃ユ湡");
+ }
+ }
+
+ default:
+ throw new ExcelImportException("璇ュ崟鍏冩牸鍐呭涓嶆槸鏃ユ湡");
+ }
+ }
+ /**
+ * 鍒ゆ柇鏁板�兼槸鍚﹀彲鑳芥槸Excel鏃ユ湡
+ * Excel鏃ユ湡閫氬父鍦ㄤ竴瀹氳寖鍥村唴锛堝36526-73051瀵瑰簲2000-21000骞达級
+ */
+ private static boolean isLikelyExcelDate(double value) {
+ // Excel鏃ユ湡浠�1900-01-01寮�濮嬶紝搴忓垪鍙�1
+ // 鐜颁唬鏃ユ湡閫氬父鍦�36526-73051鑼冨洿鍐�
+ return value >= 36526 && value < 73051;
+ }
+
+ private static String dateToString (Date date) {
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ return sdf.format(date);
+ }
+
}
--
Gitblit v1.9.1