NewLife/X

修复 Excel 无法表示 1900 年之前日期的问题

增加对 `DateTime` 类型值的处理逻辑,当日期值小于 `1900-01-01` 时,将其写入为空字符串以避免错误。新增了日期范围判断条件,并保留了原有的日期序列值计算逻辑。此改动解决了 Excel 无法表示 `1900-01-01` 之前日期的问题,提升了代码的健壮性。
石头 authored at 2025-10-03 17:22:44
90fd7e4
Tree
1 Parent(s) 55950df
Summary: 2 changed files with 33 additions and 1 deletions.
Modified +8 -1
Modified +25 -0
Modified +8 -1
diff --git a/NewLife.Core/IO/ExcelWriter.cs b/NewLife.Core/IO/ExcelWriter.cs
index 39e29d6..e9352b6 100644
--- a/NewLife.Core/IO/ExcelWriter.cs
+++ b/NewLife.Core/IO/ExcelWriter.cs
@@ -191,8 +191,15 @@ public class ExcelWriter : DisposeBase
                     }
                 case DateTime dt:
                     {
-                        // Excel 序列值:1=1900/1/1(含闰年Bug),读取时减2,这里写入需补2
                         var baseDate = new DateTime(1900, 1, 1);
+                        if (dt < baseDate)
+                        {
+                            // Excel 无法表示 1900-01-01 之前(或无效)日期,这里写入空字符串
+                            tAttr = "s";
+                            inner = GetSharedStringIndex(String.Empty).ToString();
+                            break;
+                        }
+                        // Excel 序列值:1=1900/1/1(含闰年Bug),读取时减2,这里写入需补2
                         var serial = (dt - baseDate).TotalDays + 2; // 包含时间小数
                         var hasTime = dt.TimeOfDay.Ticks != 0;
                         style = hasTime ? ExcelCellStyle.DateTime : ExcelCellStyle.Date;
Modified +25 -0
diff --git a/XUnitTest.Core/IO/ExcelWriterTests.cs b/XUnitTest.Core/IO/ExcelWriterTests.cs
index 89eb310..3811c6e 100644
--- a/XUnitTest.Core/IO/ExcelWriterTests.cs
+++ b/XUnitTest.Core/IO/ExcelWriterTests.cs
@@ -247,4 +247,29 @@ public class ExcelWriterTests
         // 互不串表:确认 Users 的列数 != Logs 的列数
         Assert.NotEqual(users[0].Length, logs[0].Length);
     }
+
+    [Fact, DisplayName("小于1900-01-01的日期写入为空字符串")]
+    public void Date_Before_1900_Written_As_EmptyString()
+    {
+        using var ms = new MemoryStream();
+        var w = new ExcelWriter(ms);
+        w.WriteHeader(null!, new[] { "D1", "D2", "D3" });
+        var invalidDate = new DateTime(1899, 12, 31);
+        var boundaryDate = new DateTime(1900, 1, 1);
+        var boundaryDateTime = new DateTime(1900, 1, 1, 12, 0, 0);
+        w.WriteRows(null, new[] { new Object?[] { invalidDate, boundaryDate, boundaryDateTime } });
+        w.Save();
+
+        ms.Position = 0;
+        var r = new ExcelReader(ms, Encoding.UTF8);
+        var rows = r.ReadRows().ToList();
+        Assert.Equal(2, rows.Count); // header + 1 数据行
+        var data = rows[1];
+        // 第1列:应为空字符串(共享字符串的空值)
+        Assert.True(data[0] is String s && s.Length == 0);
+        // 第2列:应解析为日期 1900-01-01
+        Assert.True(data[1] is DateTime d1 && d1.Date == boundaryDate.Date && d1.TimeOfDay == TimeSpan.Zero);
+        // 第3列:应解析为 日期时间 1900-01-01 12:00:00
+        Assert.True(data[2] is DateTime d2 && d2 == boundaryDateTime);
+    }
 }