diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj
index 238169d..a4b4662 100644
--- a/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML.csproj
@@ -51,6 +51,10 @@
..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll
True
+
+ ..\packages\ExcelNumberFormat.1.0.3\lib\net20\ExcelNumberFormat.dll
+ True
+
..\packages\FastMember.Signed.1.1.0\lib\net40\FastMember.Signed.dll
True
@@ -101,6 +105,7 @@
+
diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs
index b9a29ca..ff1288c 100644
--- a/ClosedXML/Excel/CalcEngine/Expression.cs
+++ b/ClosedXML/Excel/CalcEngine/Expression.cs
@@ -113,6 +113,11 @@
return ((DateTime)v).ToOADate();
}
+ if (v is TimeSpan)
+ {
+ return ((TimeSpan)v).TotalDays;
+ }
+
// handle nulls
if (v == null || v is string)
{
diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs
index bbf58ca..552c79d 100644
--- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs
+++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs
@@ -1,4 +1,5 @@
using ClosedXML.Excel.CalcEngine.Exceptions;
+using ExcelNumberFormat;
using System;
using System.Collections.Generic;
using System.Globalization;
@@ -267,14 +268,12 @@
var format = (string)p[1];
if (string.IsNullOrEmpty(format.Trim())) return "";
- // We'll have to guess as to whether the format represents a date and/or time.
- // Not sure whether there's a better way to detect this.
- bool isDateFormat = new string[] { "y", "m", "d", "h", "s" }.Any(part => format.ToLower().Contains(part.ToLower()));
+ var nf = new NumberFormat(format);
- if (isDateFormat)
- return DateTime.FromOADate(number).ToString(format, CultureInfo.CurrentCulture);
+ if (nf.IsDateTimeFormat)
+ return nf.Format(DateTime.FromOADate(number), CultureInfo.InvariantCulture);
else
- return number.ToString(format, CultureInfo.CurrentCulture);
+ return nf.Format(number, CultureInfo.InvariantCulture);
}
private static object Trim(List p)
diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index 692f9dd..911910b 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -320,36 +320,36 @@
cValue = _cellValue;
}
+ var format = GetFormat();
+
if (_dataType == XLDataType.Boolean)
- return (cValue != "0").ToString();
- if (_dataType == XLDataType.TimeSpan)
- return cValue;
- if (_dataType == XLDataType.DateTime || IsDateFormat())
+ return (cValue != "0").ToExcelFormat(format);
+
+ else if (_dataType == XLDataType.TimeSpan || _dataType == XLDataType.DateTime || IsDateFormat())
{
double dTest;
if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)
&& dTest.IsValidOADateNumber())
{
- var format = GetFormat();
- return DateTime.FromOADate(dTest).ToString(format);
+ return DateTime.FromOADate(dTest).ToExcelFormat(format);
}
return cValue;
}
- if (_dataType == XLDataType.Number)
+ else if (_dataType == XLDataType.Number)
{
double dTest;
if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest))
{
- var format = GetFormat();
- return dTest.ToString(format);
+ return dTest.ToExcelFormat(format);
}
return cValue;
}
- return cValue;
+ else
+ return cValue;
}
public object Value
diff --git a/ClosedXML/Extensions/FormatExtensions.cs b/ClosedXML/Extensions/FormatExtensions.cs
new file mode 100644
index 0000000..4a183bb
--- /dev/null
+++ b/ClosedXML/Extensions/FormatExtensions.cs
@@ -0,0 +1,17 @@
+using ExcelNumberFormat;
+using System.Globalization;
+
+namespace ClosedXML.Extensions
+{
+ internal static class FormatExtensions
+ {
+ public static string ToExcelFormat(this object o, string format)
+ {
+ var nf = new NumberFormat(format);
+ if (!nf.IsValid)
+ return format;
+
+ return nf.Format(o, CultureInfo.InvariantCulture);
+ }
+ }
+}
diff --git a/ClosedXML/packages.config b/ClosedXML/packages.config
index bed0dd7..344713c 100644
--- a/ClosedXML/packages.config
+++ b/ClosedXML/packages.config
@@ -1,5 +1,6 @@
+
\ No newline at end of file
diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
index 59d0a59..b4cf9b6 100644
--- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
+++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs
@@ -27,7 +27,7 @@
[Test]
public void Char_Input_Too_Large()
{
- Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)"));
+ Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char(9797)"));
}
[Test]
@@ -459,7 +459,8 @@
[Test]
public void Text_Value()
{
- Object actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")");
+ Object actual;
+ actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")");
Assert.AreEqual("2010-01-01", actual);
actual = XLWorkbook.EvaluateExpr(@"Text(1469.07, ""0,000,000.00"")");
@@ -488,6 +489,18 @@
Assert.AreEqual("211x", actual);
}
+ [TestCase(2020, 11, 1, 9, 23, 11, "m/d/yyyy h:mm:ss", "11/1/2020 9:23:11")]
+ [TestCase(2023, 7, 14, 2, 12, 3, "m/d/yyyy h:mm:ss", "7/14/2023 2:12:03")]
+ [TestCase(2025, 10, 14, 2, 48, 55, "m/d/yyyy h:mm:ss", "10/14/2025 2:48:55")]
+ [TestCase(2023, 2, 19, 22, 1, 38, "m/d/yyyy h:mm:ss", "2/19/2023 22:01:38")]
+ [TestCase(2025, 12, 19, 19, 43, 58, "m/d/yyyy h:mm:ss", "12/19/2025 19:43:58")]
+ [TestCase(2034, 11, 16, 1, 48, 9, "m/d/yyyy h:mm:ss", "11/16/2034 1:48:09")]
+ [TestCase(2018, 12, 10, 11, 22, 42, "m/d/yyyy h:mm:ss", "12/10/2018 11:22:42")]
+ public void Text_DateFormats(int year, int months, int days, int hour, int minutes, int seconds, string format, string expected)
+ {
+ Assert.AreEqual(expected, XLWorkbook.EvaluateExpr($@"TEXT(DATE({year}, {months}, {days}) + TIME({hour}, {minutes}, {seconds}), ""{format}"")"));
+ }
+
[Test]
public void Trim_EmptyInput_Striong()
{
diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
index 87ab21a..ed012b3 100644
--- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
+++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
@@ -31,5 +31,21 @@
Assert.AreEqual("yy-MM-dd", ws.Cell("E1").Style.DateFormat.Format);
}
}
+
+ [Test]
+ public void TestExcelNumberFormats()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ var c = ws.FirstCell()
+ .SetValue(41573.875)
+ .SetDataType(XLDataType.DateTime);
+
+ c.Style.NumberFormat.SetFormat("m/d/yy\\ h:mm;@");
+
+ Assert.AreEqual("10/26/13 21:00", c.GetFormattedString());
+ }
+ }
}
}