diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index fd3db5e..c70febf 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -5613,6 +5613,8 @@ openXmlCell.CellValue = cellValue; return; } + else + openXmlCell.CellValue = null; var dataType = xlCell.DataType; if (dataType == XLDataType.Text) diff --git a/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs b/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs index 99e8bb2..1b2928f 100644 --- a/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs +++ b/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs @@ -1,6 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; using System; +using System.IO; using System.Linq; namespace ClosedXML_Tests @@ -100,164 +101,185 @@ [Test] public void WorksheetClearContents() { + using (var wb = SetupWorkbook()) { - using (var wb = SetupWorkbook()) + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.Contents); + + foreach (var c in ws.Range("A1:A3").Cells()) { - var ws = wb.Worksheets.First(); - - ws.Clear(XLClearOptions.Contents); - - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsTrue(c.IsEmpty()); - Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); - Assert.IsTrue(ws.ConditionalFormats.Any()); - Assert.IsTrue(c.HasComment); - } - - Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); - - Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); - Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); - Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + Assert.IsTrue(c.IsEmpty()); + Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsTrue(c.HasComment); } + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + + Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); + Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); + Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); } } [Test] public void WorksheetClearDataType() { + using (var wb = SetupWorkbook()) { - using (var wb = SetupWorkbook()) + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.DataType); + + foreach (var c in ws.Range("A1:A3").Cells()) { - var ws = wb.Worksheets.First(); - - ws.Clear(XLClearOptions.DataType); - - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsFalse(c.IsEmpty()); - Assert.AreEqual(XLDataType.Text, c.DataType); - Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); - Assert.IsTrue(ws.ConditionalFormats.Any()); - Assert.IsTrue(c.HasComment); - } - - Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + Assert.IsFalse(c.IsEmpty()); + Assert.AreEqual(XLDataType.Text, c.DataType); + Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsTrue(c.HasComment); } + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); } } [Test] public void WorksheetClearNormalFormats() { + using (var wb = SetupWorkbook()) { - using (var wb = SetupWorkbook()) + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.NormalFormats); + + foreach (var c in ws.Range("A1:A3").Cells()) { - var ws = wb.Worksheets.First(); - - ws.Clear(XLClearOptions.NormalFormats); - - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsFalse(c.IsEmpty()); - Assert.AreEqual(ws.Style.Fill.BackgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(ws.Style.Font.FontColor, c.Style.Font.FontColor); - Assert.IsTrue(ws.ConditionalFormats.Any()); - Assert.IsTrue(c.HasComment); - } - - Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); - Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); - Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); - - Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + Assert.IsFalse(c.IsEmpty()); + Assert.AreEqual(ws.Style.Fill.BackgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(ws.Style.Font.FontColor, c.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsTrue(c.HasComment); } + + Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); + Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); + Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); } } [Test] public void WorksheetClearConditionalFormats() { + using (var wb = SetupWorkbook()) { - using (var wb = SetupWorkbook()) + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.ConditionalFormats); + + foreach (var c in ws.Range("A1:A3").Cells()) { - var ws = wb.Worksheets.First(); - - ws.Clear(XLClearOptions.ConditionalFormats); - - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsFalse(c.IsEmpty()); - Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); - Assert.IsFalse(ws.ConditionalFormats.Any()); - Assert.IsTrue(c.HasComment); - } - - Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); - Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); - Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); - - Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + Assert.IsFalse(c.IsEmpty()); + Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); + Assert.IsFalse(ws.ConditionalFormats.Any()); + Assert.IsTrue(c.HasComment); } + + Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); + Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); + Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); } } [Test] public void WorksheetClearComments() { + using (var wb = SetupWorkbook()) { - using (var wb = SetupWorkbook()) + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.Comments); + + foreach (var c in ws.Range("A1:A3").Cells()) { - var ws = wb.Worksheets.First(); - - ws.Clear(XLClearOptions.Comments); - - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsFalse(c.IsEmpty()); - Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); - Assert.IsTrue(ws.ConditionalFormats.Any()); - Assert.IsFalse(c.HasComment); - } - - Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); - Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); - Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); - - Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + Assert.IsFalse(c.IsEmpty()); + Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsFalse(c.HasComment); } + + Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); + Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); + Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); } } [Test] public void WorksheetClearDataValidation() { + using (var wb = SetupWorkbook()) + { + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.DataValidation); + + foreach (var c in ws.Range("A1:A3").Cells()) + { + Assert.IsFalse(c.IsEmpty()); + Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsTrue(c.HasComment); + } + + Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); + Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); + Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + + Assert.AreEqual(string.Empty, ws.Cell("A1").DataValidation.Value); + } + } + + [Test] + public void DeleteClearedCellValue() + { + using (var ms = new MemoryStream()) { using (var wb = SetupWorkbook()) { var ws = wb.Worksheets.First(); + Assert.AreEqual("Hello world!", ws.Cell("A1").GetString()); + Assert.AreEqual(new DateTime(2018, 1, 15), ws.Cell("A3").GetDateTime()); - ws.Clear(XLClearOptions.DataValidation); + wb.SaveAs(ms); + } - foreach (var c in ws.Range("A1:A3").Cells()) - { - Assert.IsFalse(c.IsEmpty()); - Assert.AreEqual(backgroundColor, c.Style.Fill.BackgroundColor); - Assert.AreEqual(foregroundColor, c.Style.Font.FontColor); - Assert.IsTrue(ws.ConditionalFormats.Any()); - Assert.IsTrue(c.HasComment); - } + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + ws.Clear(XLClearOptions.Contents); + Assert.AreEqual("", ws.Cell("A1").GetString()); + Assert.Throws(() => ws.Cell("A3").GetDateTime()); - Assert.AreEqual(XLDataType.Text, ws.Cell("A1").DataType); - Assert.AreEqual(XLDataType.Text, ws.Cell("A2").DataType); - Assert.AreEqual(XLDataType.DateTime, ws.Cell("A3").DataType); + wb.Save(); + } - Assert.AreEqual(string.Empty, ws.Cell("A1").DataValidation.Value); + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual("", ws.Cell("A1").GetString()); + Assert.Throws(() => ws.Cell("A3").GetDateTime()); } } }