diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index f9312ee..2a163a9 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1615,7 +1615,13 @@ private void SetValue(object value) { FormulaA1 = String.Empty; - var val = value == null ? String.Empty : value.ToString(); + string val; + if (value == null) + val = string.Empty; + else if (value is DateTime) + val = ((DateTime)value).ToString("o"); + else + val = value.ToString(); _richText = null; if (val.Length == 0) _dataType = XLCellValues.Text; diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 408d16e..1415703 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -1,6 +1,8 @@ using System; using System.Collections.Generic; +using System.Globalization; using System.Linq; +using System.Threading; using ClosedXML.Excel; using NUnit.Framework; @@ -327,5 +329,21 @@ string expected = String.Empty; Assert.AreEqual(expected, actual); } + + [Test] + public void ValueSetDateWithShortUserDateFormat() + { + // For this test to make sense, user's local date format should be dd/MM/yy (note without the 2 century digits) + // What happened previously was that the century digits got lost in .ToString() conversion and wrong century was sometimes returned. + var ci = new CultureInfo(CultureInfo.InvariantCulture.LCID); + ci.DateTimeFormat.ShortDatePattern = "dd/MM/yy"; + Thread.CurrentThread.CurrentCulture = ci; + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); + IXLCell cell = ws.Cell(1, 1); + var expected = DateTime.Today.AddYears(20); + cell.Value = expected; + var actual = (DateTime)cell.Value; + Assert.AreEqual(expected, actual); + } } } \ No newline at end of file