Repository: wangguanquan/eec
Pull request #410 (2024-12-18 13:35:59)

Fix#409 增加Excel转CSV时对时间处理的兼容性


Patch:
diff --git a/src/main/java/org/ttzero/excel/entity/CSVSheet.java b/src/main/java/org/ttzero/excel/entity/CSVSheet.java
index aa3b6a86..91c002a5 100644
--- a/src/main/java/org/ttzero/excel/entity/CSVSheet.java
+++ b/src/main/java/org/ttzero/excel/entity/CSVSheet.java
@@ -66,7 +66,7 @@ public class CSVSheet extends Sheet {
     /**
      * Delimiter char
      */
-    protected char delimiter = 0x0;
+    protected char delimiter = ',';
 
     /**
      * 实例化工作表，未指定工作表名称时默认以{@code 'Sheet'+id}命名
diff --git a/src/main/java/org/ttzero/excel/entity/csv/CSVCellValueAndStyle.java b/src/main/java/org/ttzero/excel/entity/csv/CSVCellValueAndStyle.java
index 565baa18..bdad0666 100644
--- a/src/main/java/org/ttzero/excel/entity/csv/CSVCellValueAndStyle.java
+++ b/src/main/java/org/ttzero/excel/entity/csv/CSVCellValueAndStyle.java
@@ -42,6 +42,8 @@ import static org.ttzero.excel.entity.IWorksheetWriter.isLong;
 import static org.ttzero.excel.entity.IWorksheetWriter.isShort;
 import static org.ttzero.excel.entity.IWorksheetWriter.isString;
 import static org.ttzero.excel.entity.IWorksheetWriter.isTime;
+import static org.ttzero.excel.util.DateUtil.toDateString;
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
 
 /**
  * @author guanquan.wang at 2019-09-25 11:46
@@ -83,9 +85,9 @@ public class CSVCellValueAndStyle implements ICellValueAndStyle {
             cell.setString(e.toString());
         } else if (isDate(clazz)) {
             // TODO hc.numFmt
-            cell.setString(DateUtil.toDateString((java.util.Date) e));
+            cell.setString(toDateString((java.util.Date) e));
         } else if (isDateTime(clazz)) {
-            cell.setString(DateUtil.toString((Timestamp) e));
+            cell.setString(toDateTimeString((Timestamp) e));
         } else if (isChar(clazz)) {
             cell.setChar((Character) e);
         } else if (isShort(clazz)) {
diff --git a/src/main/java/org/ttzero/excel/entity/csv/CSVWorksheetWriter.java b/src/main/java/org/ttzero/excel/entity/csv/CSVWorksheetWriter.java
index beea7289..a9abc049 100644
--- a/src/main/java/org/ttzero/excel/entity/csv/CSVWorksheetWriter.java
+++ b/src/main/java/org/ttzero/excel/entity/csv/CSVWorksheetWriter.java
@@ -25,7 +25,6 @@ import org.ttzero.excel.entity.Sheet;
 import org.ttzero.excel.manager.Const;
 import org.ttzero.excel.reader.Cell;
 import org.ttzero.excel.util.CSVUtil;
-import org.ttzero.excel.util.DateUtil;
 
 import java.io.IOException;
 import java.nio.charset.Charset;
@@ -44,6 +43,10 @@ import static org.ttzero.excel.reader.Cell.LONG;
 import static org.ttzero.excel.reader.Cell.NUMERIC;
 import static org.ttzero.excel.reader.Cell.SST;
 import static org.ttzero.excel.reader.Cell.TIME;
+import static org.ttzero.excel.util.DateUtil.toDate;
+import static org.ttzero.excel.util.DateUtil.toDateString;
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
+import static org.ttzero.excel.util.DateUtil.toTimeString;
 import static org.ttzero.excel.util.StringUtil.isNotEmpty;
 
 /**
@@ -63,9 +66,9 @@ public class CSVWorksheetWriter implements IWorksheetWriter {
     /**
      * Delimiter char
      */
-    protected char delimiter = 0x0;
+    protected char delimiter = ',';
 
-    public CSVWorksheetWriter() {}
+    public CSVWorksheetWriter() { }
 
     public CSVWorksheetWriter(Sheet sheet) {
         this.sheet = sheet;
@@ -269,16 +272,16 @@ public class CSVWorksheetWriter implements IWorksheetWriter {
         for (Cell cell : cells) {
             switch (cell.t) {
                 case INLINESTR:
-                case SST      : writer.write(cell.stringVal);                                      break;
-                case NUMERIC  : writer.write(cell.intVal);                                         break;
-                case LONG     : writer.write(cell.longVal);                                        break;
-                case DOUBLE   : writer.write(cell.doubleVal);                                      break;
-                case BOOL     : writer.write(cell.boolVal);                                        break;
-                case DECIMAL  : writer.write(cell.decimal.toString());                             break;
-                case CHARACTER: writer.writeChar(cell.charVal);                                    break;
-                case DATE     : writer.write(DateUtil.toDateString(DateUtil.toDate(cell.intVal))); break;
-                case DATETIME : writer.write(DateUtil.toString(DateUtil.toDate(cell.doubleVal)));  break;
-                case TIME     : writer.write(DateUtil.toDate(cell.doubleVal).toString());          break;
+                case SST      : writer.write(cell.stringVal);                           break;
+                case NUMERIC  : writer.write(cell.intVal);                              break;
+                case LONG     : writer.write(cell.longVal);                             break;
+                case DOUBLE   : writer.write(cell.doubleVal);                           break;
+                case BOOL     : writer.write(cell.boolVal);                             break;
+                case DECIMAL  : writer.write(cell.decimal.toString());                  break;
+                case CHARACTER: writer.writeChar(cell.charVal);                         break;
+                case DATE     : writer.write(toDateString(toDate(cell.intVal)));        break;
+                case DATETIME : writer.write(toDateTimeString(toDate(cell.doubleVal))); break;
+                case TIME     : writer.write(toTimeString(toDate(cell.doubleVal)));     break;
                 default       : writer.writeEmpty();
             }
         }
diff --git a/src/main/java/org/ttzero/excel/manager/Const.java b/src/main/java/org/ttzero/excel/manager/Const.java
index 6cee8c7f..bd9e6e06 100644
--- a/src/main/java/org/ttzero/excel/manager/Const.java
+++ b/src/main/java/org/ttzero/excel/manager/Const.java
@@ -167,7 +167,7 @@ public interface Const {
     }
 
     /**
-     * The extend properties key
+     * The extends properties key
      */
     interface ExtendPropertyKey {
         /**
diff --git a/src/main/java/org/ttzero/excel/reader/AppInfo.java b/src/main/java/org/ttzero/excel/reader/AppInfo.java
index e871f214..e65ca025 100644
--- a/src/main/java/org/ttzero/excel/reader/AppInfo.java
+++ b/src/main/java/org/ttzero/excel/reader/AppInfo.java
@@ -18,13 +18,14 @@ package org.ttzero.excel.reader;
 
 import org.ttzero.excel.manager.docProps.App;
 import org.ttzero.excel.manager.docProps.Core;
-import org.ttzero.excel.util.DateUtil;
 import org.ttzero.excel.util.ReflectUtil;
 
 import java.lang.reflect.Method;
 import java.util.Date;
 import java.util.StringJoiner;
 
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
+
 /**
  * Excel文件基础信息包含作者、日期等信息，在windows操作系统上使用鼠标右键-&gt;属性-&gt;详细信息查看
  *
@@ -183,7 +184,7 @@ public class AppInfo {
                 if (o != null) {
                     joiner.add(method.getReturnType() == boolean.class ? method.getName().substring(2)
                         : method.getName().substring(3) + ": "
-                        + (o instanceof Date ? DateUtil.toString((Date) o) : o.toString()));
+                        + (o instanceof Date ? toDateTimeString((Date) o) : o.toString()));
                 }
             }
         } catch (Exception e) {
diff --git a/src/main/java/org/ttzero/excel/reader/Sheet.java b/src/main/java/org/ttzero/excel/reader/Sheet.java
index ea496530..d37e66c0 100644
--- a/src/main/java/org/ttzero/excel/reader/Sheet.java
+++ b/src/main/java/org/ttzero/excel/reader/Sheet.java
@@ -25,10 +25,12 @@ import java.io.Closeable;
 import java.io.IOException;
 import java.io.OutputStream;
 import java.io.OutputStreamWriter;
+import java.math.BigDecimal;
 import java.nio.charset.Charset;
 import java.nio.charset.StandardCharsets;
 import java.nio.file.Files;
 import java.nio.file.Path;
+import java.time.LocalTime;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Spliterator;
@@ -44,7 +46,10 @@ import static org.ttzero.excel.reader.Cell.INLINESTR;
 import static org.ttzero.excel.reader.Cell.LONG;
 import static org.ttzero.excel.reader.Cell.NUMERIC;
 import static org.ttzero.excel.reader.Cell.SST;
+import static org.ttzero.excel.util.DateUtil.toTimeChars;
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
 import static org.ttzero.excel.util.DateUtil.toLocalDate;
+import static org.ttzero.excel.util.DateUtil.toLocalTime;
 import static org.ttzero.excel.util.DateUtil.toTimestamp;
 import static org.ttzero.excel.util.FileUtil.exists;
 
@@ -316,9 +321,14 @@ public interface Sheet extends Closeable {
      */
     default void saveAsCSV(BufferedWriter bw) throws IOException {
         try (CSVUtil.Writer writer = CSVUtil.newWriter(bw)) {
+            int rowNum = 1;
             for (Iterator<Row> iter = iterator(); iter.hasNext(); ) {
                 Row row = iter.next();
-                if (row.isEmpty()) {
+                // 保持与xlsx相同行号
+                if (row.getRowNum() - rowNum > 1) {
+                    for (; ++rowNum < row.getRowNum(); writer.newLine());
+                } else rowNum = row.getRowNum();
+                if (row.isBlank()) {
                     writer.newLine();
                     continue;
                 }
@@ -332,19 +342,23 @@ public interface Sheet extends Closeable {
                             writer.write(c.stringVal);
                             break;
                         case NUMERIC:
-                            if (!row.styles.fastTestDateFmt(c.xf)) writer.write(c.intVal);
-                            else writer.write(toLocalDate(c.intVal).toString());
+                            if (!row.styles.isDate(c.xf)) writer.write(c.intVal);
+                            else if (c.intVal > 0) writer.write(toLocalDate(c.intVal).toString());
+                            // 时分秒为00:00:00时读取为整数0
+                            else writer.write(toTimeChars(LocalTime.MIN));
                             break;
                         case LONG:
                             writer.write(c.longVal);
                             break;
                         case DECIMAL:
-                            if (!row.styles.fastTestDateFmt(c.xf)) writer.write(c.decimal.toString());
-                            else writer.write(toTimestamp(c.decimal.doubleValue()).toString());
+                            if (!row.styles.isDate(c.xf)) writer.write(c.decimal.toString());
+                            else if (c.decimal.compareTo(BigDecimal.ONE) >= 0) writer.write(toDateTimeString(toTimestamp(c.decimal.doubleValue())));
+                            else writer.write(toTimeChars(toLocalTime(c.decimal.doubleValue())));
                             break;
                         case DOUBLE:
-                            if (!row.styles.fastTestDateFmt(c.xf)) writer.write(c.doubleVal);
-                            else writer.write(toTimestamp(c.doubleVal).toString());
+                            if (!row.styles.isDate(c.xf)) writer.write(c.doubleVal);
+                            else if (c.doubleVal >= 1.0D) writer.write(toDateTimeString(toTimestamp(c.doubleVal)));
+                            else writer.write(toTimeChars(toLocalTime(c.doubleVal)));
                             break;
                         case BOOL:
                             writer.write(c.boolVal);
diff --git a/src/main/java/org/ttzero/excel/util/DateUtil.java b/src/main/java/org/ttzero/excel/util/DateUtil.java
index 4382684e..37e86620 100644
--- a/src/main/java/org/ttzero/excel/util/DateUtil.java
+++ b/src/main/java/org/ttzero/excel/util/DateUtil.java
@@ -88,8 +88,19 @@ public class DateUtil {
      *
      * @param date 待转换日期
      * @return {@code yyyy-MM-dd HH:mm:ss}格式字符串
+     * @deprecated 使用 {@link #toDateTimeString(Date)} 替代
      */
+    @Deprecated
     public static String toString(Date date) {
+        return toDateTimeString(date);
+    }
+    /**
+     * 将日期{@code date}格式化为{@code yyyy-MM-dd HH:mm:ss}
+     *
+     * @param date 待转换日期
+     * @return {@code yyyy-MM-dd HH:mm:ss}格式字符串
+     */
+    public static String toDateTimeString(Date date) {
         return dateTimeFormat.get().format(date);
     }
     /**
@@ -110,6 +121,16 @@ public class DateUtil {
     public static String toDateString(Date date) {
         return dateFormat.get().format(date);
     }
+    /**
+     * 将日期{@code date}格式化为{@code HH:mm:ss}
+     *
+     * @param date 待转换日期
+     * @return {@code HH:mm:ss}格式字符串
+     */
+    public static String toTimeString(Date date) {
+        LocalTime lt = new Timestamp(date.getTime()).toLocalDateTime().toLocalTime();
+        return new String(toTimeChars(lt));
+    }
     /**
      * 获取当日{@code yyyy-MM-dd}字符串
      *
@@ -370,4 +391,21 @@ public class DateUtil {
         }
         return java.sql.Timestamp.valueOf(v);
     }
+
+    /**
+     * 将给定的 LocalTime 对象转换为指定格式的字符数组
+     *
+     * <p>注意：内部使用，外部勿用</p>
+     *
+     * @param time 要转换的 LocalTime 对象
+     * @return 转换后的字符数组，固定格式化为{@code HH:mm:ss}
+     */
+    public static char[] toTimeChars(LocalTime time) {
+        int hms = time.getHour() * 10000 + time.getMinute() * 100 + time.getSecond();
+        char[] chars = { '0', '0', ':', '0', '0', ':', '0', '0' };
+        for (int i = chars.length - 1; hms > 0; chars[i--] += hms % 10, hms /= 10) {
+            if (i == 5 || i == 2) i--;
+        }
+        return chars;
+    }
 }


Test patch:
diff --git a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
index 860d01cf..1a833d35 100644
--- a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
+++ b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest.java
@@ -25,7 +25,6 @@ import org.ttzero.excel.annotation.IgnoreImport;
 import org.ttzero.excel.annotation.RowNum;
 import org.ttzero.excel.entity.ListObjectSheetTest;
 import org.ttzero.excel.util.CSVUtil;
-import org.ttzero.excel.util.DateUtil;
 import org.ttzero.excel.util.FileUtil;
 import org.ttzero.excel.util.StringUtil;
 
@@ -55,7 +54,9 @@ import static org.junit.Assert.assertThrows;
 import static org.ttzero.excel.Print.println;
 import static org.ttzero.excel.entity.WorkbookTest.getOutputTestPath;
 import static org.ttzero.excel.reader.ExcelReaderTest2.listEquals;
+import static org.ttzero.excel.util.DateUtil.toDateString;
 import static org.ttzero.excel.util.StringUtil.swap;
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
 
 /**
  * @author guanquan.wang at 2019-04-26 17:42
@@ -128,7 +129,7 @@ public class ExcelReaderTest {
                     switch (type) {
                         case INTEGER : o = row.getInt(cell).toString();                   break;
                         case BOOLEAN : o = row.getBoolean(cell).toString().toUpperCase(); break;
-                        case DATE    : o = DateUtil.toString(row.getDate(cell));          break;
+                        case DATE    : o = toDateTimeString(row.getDate(cell));           break;
                         default      : o = row.getString(start);
                     }
                     if (StringUtil.isEmpty(e)) assertTrue(StringUtil.isEmpty(o));
@@ -150,7 +151,7 @@ public class ExcelReaderTest {
                     switch (type) {
                         case INTEGER : o = row.getInt(cell).toString();                   break;
                         case BOOLEAN : o = row.getBoolean(cell).toString().toUpperCase(); break;
-                        case DATE    : o = DateUtil.toString(row.getDate(cell));          break;
+                        case DATE    : o = toDateTimeString(row.getDate(cell));           break;
                         default      : o = row.getString(start);
                     }
                     if (StringUtil.isEmpty(e)) assertTrue(StringUtil.isEmpty(o));
@@ -181,7 +182,7 @@ public class ExcelReaderTest {
                         switch (type) {
                             case INTEGER : o = row.getInt(cell).toString();                   break;
                             case BOOLEAN : o = row.getBoolean(cell).toString().toUpperCase(); break;
-                            case DATE    : o = DateUtil.toString(row.getDate(cell));          break;
+                            case DATE    : o = toDateTimeString(row.getDate(cell));           break;
                             default: o = row.getString(start);
                         }
                         if (StringUtil.isEmpty(e)) assertTrue(StringUtil.isEmpty(o));
@@ -224,7 +225,7 @@ public class ExcelReaderTest {
                 assertEquals(o[0], e.channelId.toString());
                 assertEquals(o[1], CustomAnnoReaderTest.GameConverter.names[Integer.parseInt(e.pro)]);
                 assertEquals(o[2], e.account);
-                assertEquals(o[3], DateUtil.toString(e.registered));
+                assertEquals(o[3], toDateTimeString(e.registered));
                 assertEquals(o[4], e.up30 ? "TRUE" : "FALSE");
                 assertEquals(o[5], new String(new char[] {e.c}, 0, 1));
             }
@@ -242,7 +243,7 @@ public class ExcelReaderTest {
                 assertEquals(o[0], e.channelId.toString());
                 assertEquals(o[1], e.pro);
                 assertNull(e.account);
-                assertEquals(o[3], DateUtil.toString(e.registered));
+                assertEquals(o[3], toDateTimeString(e.registered));
                 assertEquals(o[4], e.up30 ? "TRUE" : "FALSE");
                 assertEquals(o[5], new String(new char[] {e.c}, 0, 1));
             }
@@ -261,7 +262,7 @@ public class ExcelReaderTest {
                 assertEquals(o[0], row.getString("渠道ID"));
                 assertEquals(o[1], row.getString("游戏"));
                 assertEquals(o[2], row.getString("account"));
-                assertEquals(o[3], DateUtil.toString(row.getDate("注册时间")));
+                assertEquals(o[3], toDateTimeString(row.getDate("注册时间")));
                 assertEquals(o[4], row.getBoolean("是否满30级") ? "TRUE" : "FALSE");
                 assertEquals(o[5], row.getString("VIP"));
             }
@@ -514,7 +515,7 @@ public class ExcelReaderTest {
                                 case LONG    : o = row.getLong(cell).toString();                  break;
                                 case DECIMAL : o = row.getDecimal(cell).toString();               break;
                                 case BOOLEAN : o = row.getBoolean(cell).toString().toUpperCase(); break;
-                                case DATE    : o = DateUtil.toString(row.getDate(cell));          break;
+                                case DATE    : o = toDateTimeString(row.getDate(cell));           break;
                                 default      : o = row.getString(start);
                             }
                             if (StringUtil.isEmpty(e)) assertTrue(StringUtil.isEmpty(o));
@@ -936,7 +937,7 @@ public class ExcelReaderTest {
             return num + " | " + channelId + " | "
                 + pro + " | "
                 + account + " | "
-                + (registered != null ? DateUtil.toDateString(registered) : null) + " | "
+                + (registered != null ? toDateString(registered) : null) + " | "
                 + up30 + " | "
                 + c + " | "
                 + isVip()
@@ -994,7 +995,7 @@ public class ExcelReaderTest {
             return channelId + " | "
                 + pro + " | "
                 + account + " | "
-                + (registered != null ? DateUtil.toDateString(registered) : null) + " | "
+                + (registered != null ? toDateString(registered) : null) + " | "
                 + up30 + " | "
                 + c + " | "
                 + vip
@@ -1063,7 +1064,7 @@ public class ExcelReaderTest {
             return rowNum + " | " + channelId + " | "
                 + pro + " | "
                 + account + " | "
-                + (registered != null ? DateUtil.toDateString(registered) : null) + " | "
+                + (registered != null ? toDateString(registered) : null) + " | "
                 + up30 + " | "
                 + c + " | "
                 + vip
diff --git a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
index 138cdce2..4cc1308e 100644
--- a/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
+++ b/src/test/java/org/ttzero/excel/reader/ExcelReaderTest2.java
@@ -27,7 +27,6 @@ import org.ttzero.excel.entity.ListSheet;
 import org.ttzero.excel.entity.Panes;
 import org.ttzero.excel.entity.Workbook;
 import org.ttzero.excel.util.CSVUtil;
-import org.ttzero.excel.util.DateUtil;
 import org.ttzero.excel.util.StringUtil;
 
 import java.io.IOException;
@@ -49,6 +48,7 @@ import static org.ttzero.excel.entity.WorkbookTest.defaultTestPath;
 import static org.ttzero.excel.entity.WorkbookTest.getRandomString;
 import static org.ttzero.excel.entity.WorkbookTest.random;
 import static org.ttzero.excel.reader.ExcelReaderTest.testResourceRoot;
+import static org.ttzero.excel.util.DateUtil.toDateTimeString;
 
 /**
  * @author guanquan.wang at 2023-01-06 09:32
@@ -326,7 +326,7 @@ public class ExcelReaderTest2 {
                         switch (type) {
                             case INTEGER : o = row.getInt(cell).toString();                   break;
                             case BOOLEAN : o = row.getBoolean(cell).toString().toUpperCase(); break;
-                            case DATE    : o = DateUtil.toString(row.getDate(cell));          break;
+                            case DATE    : o = toDateTimeString(row.getDate(cell));          break;
                             default: o = row.getString(start);
                         }
                         if (StringUtil.isEmpty(e)) assertTrue(StringUtil.isEmpty(o));