修复 Excel 无法表示 1900 年之前日期的问题 增加对 `DateTime` 类型值的处理逻辑,当日期值小于 `1900-01-01` 时,将其写入为空字符串以避免错误。新增了日期范围判断条件,并保留了原有的日期序列值计算逻辑。此改动解决了 Excel 无法表示 `1900-01-01` 之前日期的问题,提升了代码的健壮性。石头 authored at 2025-10-03 17:22:44
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;
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);
+ }
}