diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 2a163a9..9e398e2 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -241,7 +241,7 @@ var dtTest = (DateTime)Convert.ChangeType(value, typeof (DateTime)); Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; - _cellValue = dtTest.ToOADate().ToString(); + _cellValue = dtTest.ToOADate().ToInvariantString(); } else if ( value is sbyte @@ -266,7 +266,7 @@ else { _dataType = XLCellValues.Number; - _cellValue = value.ToString(); + _cellValue = ((Double)Convert.ChangeType(value, typeof (Double))).ToInvariantString(); } } else if (value is Boolean) @@ -343,7 +343,7 @@ if (_dataType == XLCellValues.DateTime || IsDateFormat()) { double dTest; - if (Double.TryParse(cValue, out dTest)) + if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) { var format = GetFormat(); return DateTime.FromOADate(dTest).ToString(format); @@ -355,7 +355,7 @@ if (_dataType == XLCellValues.Number) { double dTest; - if (Double.TryParse(cValue, out dTest)) + if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) { var format = GetFormat(); return dTest.ToString(format); @@ -418,10 +418,10 @@ return cellValue != "0"; if (_dataType == XLCellValues.DateTime) - return DateTime.FromOADate(Double.Parse(cellValue)); + return DateTime.FromOADate(Double.Parse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture)); if (_dataType == XLCellValues.Number) - return Double.Parse(cellValue); + return Double.Parse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture); if (_dataType == XLCellValues.TimeSpan) { @@ -813,9 +813,9 @@ DateTime dtTest; double dblTest; if (DateTime.TryParse(_cellValue, out dtTest)) - _cellValue = dtTest.ToOADate().ToString(); - else if (Double.TryParse(_cellValue, out dblTest)) - _cellValue = dblTest.ToString(); + _cellValue = dtTest.ToOADate().ToInvariantString(); + else if (Double.TryParse(_cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dblTest)) + _cellValue = dblTest.ToInvariantString(); else { throw new ArgumentException( @@ -841,7 +841,7 @@ { try { - _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue)) - BaseDate).ToString(); + _cellValue = (DateTime.FromOADate(Double.Parse(_cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture)) - BaseDate).ToString(); } catch { @@ -855,8 +855,8 @@ else if (value == XLCellValues.Number) { double dTest; - if (Double.TryParse(_cellValue, out dTest)) - _cellValue = Double.Parse(_cellValue).ToString(); + if (Double.TryParse(_cellValue, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out dTest)) + _cellValue = dTest.ToString(CultureInfo.InvariantCulture); else { throw new ArgumentException( @@ -1620,6 +1620,8 @@ val = string.Empty; else if (value is DateTime) val = ((DateTime)value).ToString("o"); + else if (value is double) + val = ((double)value).ToInvariantString(); else val = value.ToString(); _richText = null; @@ -1645,7 +1647,7 @@ if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } - else if (value is TimeSpan || (!Double.TryParse(val, out dTest) && TimeSpan.TryParse(val, out tsTest))) + else if (value is TimeSpan || (!Double.TryParse(val, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest) && TimeSpan.TryParse(val, out tsTest))) { if (!(value is TimeSpan) && TimeSpan.TryParse(val, out tsTest)) val = tsTest.ToString(); @@ -1654,7 +1656,7 @@ if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } - else if (val.Trim() != "NaN" && Double.TryParse(val, out dTest)) + else if (val.Trim() != "NaN" && Double.TryParse(val, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) _dataType = XLCellValues.Number; else if (DateTime.TryParse(val, out dtTest) && dtTest >= BaseDate) { @@ -1667,11 +1669,11 @@ DateTime forMillis; if (value is DateTime && (forMillis = (DateTime)value).Millisecond > 0) { - val = forMillis.ToOADate().ToString(); + val = forMillis.ToOADate().ToInvariantString(); } else { - val = dtTest.ToOADate().ToString(); + val = dtTest.ToOADate().ToInvariantString(); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index 802342f..df4e9da 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -256,7 +256,7 @@ else if (thisCell.DataType == XLCellValues.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); else - comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); + comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); } else if (e.MatchCase) comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index bed27ac..6aa64ab 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -890,7 +890,7 @@ { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) { - var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text)]; + var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; var runs = sharedString.Elements(); var phoneticRuns = sharedString.Elements(); @@ -950,7 +950,7 @@ else if (cell.DataType == CellValues.Date) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); xlCell._dataType = XLCellValues.DateTime; } else if (cell.DataType == CellValues.Boolean) @@ -961,7 +961,7 @@ else if (cell.DataType == CellValues.Number) { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); if (s == null) { xlCell._dataType = XLCellValues.Number; @@ -987,7 +987,7 @@ { var numberFormatId = ((CellFormat) (s.CellFormats).ElementAt(styleIndex)).NumberFormatId; if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) - xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId)) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0ad3737..c6084d6 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -3916,7 +3916,7 @@ if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) { var cellValue = new CellValue(); - cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); + cellValue.Text = Double.Parse(opCell.InnerText, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture); cell.CellValue = cellValue; } } diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index d5f704d..b2bafb8 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -25,6 +25,8 @@ internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; internal static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(200, 200)); internal static readonly Double DpiX = Graphic.DpiX; + internal static readonly NumberStyles NumberStyle = NumberStyles.AllowDecimalPoint | NumberStyles.AllowLeadingSign | NumberStyles.AllowLeadingWhite | NumberStyles.AllowTrailingWhite; + internal static readonly CultureInfo ParseCulture = CultureInfo.InvariantCulture; internal static readonly Regex A1SimpleRegex = new Regex( @"\A" diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9cd5941..2d3008d 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -72,6 +72,7 @@ + diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 956a13d..2b94f60 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -27,6 +27,7 @@ new InsertColumns().Create(Path.Combine(path, "InsertColumns.xlsx")); new ColumnCollection().Create(Path.Combine(path, "ColumnCollection.xlsx")); new DataTypes().Create(Path.Combine(path, "DataTypes.xlsx")); + new DataTypesUnderDifferentCulture().Create(Path.Combine(path, "DataTypesUnderDifferentCulture.xlsx")); new MultipleSheets().Create(Path.Combine(path, "MultipleSheets.xlsx")); new RowCollection().Create(Path.Combine(path, "RowCollection.xlsx")); new DefiningRanges().Create(Path.Combine(path, "DefiningRanges.xlsx")); diff --git a/ClosedXML_Examples/Misc/DataTypesUnderDifferentCulture.cs b/ClosedXML_Examples/Misc/DataTypesUnderDifferentCulture.cs new file mode 100644 index 0000000..4b77144 --- /dev/null +++ b/ClosedXML_Examples/Misc/DataTypesUnderDifferentCulture.cs @@ -0,0 +1,35 @@ +using System; +using ClosedXML.Excel; +using System.Threading; +using System.Globalization; +using System.IO; + +namespace ClosedXML_Examples.Misc +{ + public class DataTypesUnderDifferentCulture : IXLExample + { + public void Create(string filePath) + { + var backupCulture = Thread.CurrentThread.CurrentCulture; + + // Set thread culture to French, which should format numbers using decimal COMMA + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("fr-FR"); + + string tempFile = ExampleHelper.GetTempFilePath(filePath); + try + { + new DataTypes().Create(tempFile); + var workbook = new XLWorkbook(tempFile); + workbook.SaveAs(filePath); + } + finally + { + Thread.CurrentThread.CurrentCulture = backupCulture; + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } + } + } + } +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 0d280d1..6b9f310 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -143,6 +143,7 @@ + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index 2433aab..38f7e8e 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -74,6 +74,12 @@ } [Test] + public void DataTypesUnderDifferentCulture() + { + TestHelper.RunTestExample(@"Misc\DataTypesUnderDifferentCulture.xlsx"); + } + + [Test] public void DataValidation() { TestHelper.RunTestExample(@"Misc\DataValidation.xlsx"); diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx new file mode 100644 index 0000000..ca68eee --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx Binary files differ