diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 8e756ab..085172a 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1,7 +1,9 @@ +using FastMember; using System; using System.Collections; using System.Collections.Generic; using System.Data; +using System.Diagnostics; using System.Globalization; using System.Linq; using System.Reflection; @@ -12,7 +14,6 @@ { using Attributes; using ClosedXML.Extensions; - using FastMember; internal class XLCell : IXLCell, IXLStylized { diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 40b0e2e..e946369 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -429,16 +429,16 @@ public void Save() { #if DEBUG - Save(true); + Save(true, false); #else - Save(false); + Save(false, false); #endif } /// /// Saves the current workbook and optionally performs validation /// - public void Save(bool validate) + public void Save(Boolean validate, Boolean evaluateFormulae = false) { checkForWorksheetsPresent(); if (_loadSource == XLLoadSource.New) @@ -446,10 +446,10 @@ if (_loadSource == XLLoadSource.Stream) { - CreatePackage(_originalStream, false, _spreadsheetDocumentType, validate); + CreatePackage(_originalStream, false, _spreadsheetDocumentType, validate, evaluateFormulae); } else - CreatePackage(_originalFile, _spreadsheetDocumentType, validate); + CreatePackage(_originalFile, _spreadsheetDocumentType, validate, evaluateFormulae); } /// @@ -458,16 +458,16 @@ public void SaveAs(String file) { #if DEBUG - SaveAs(file, true); + SaveAs(file, true, false); #else - SaveAs(file, false); + SaveAs(file, false, false); #endif } /// /// Saves the current workbook to a file and optionally validates it. /// - public void SaveAs(String file, Boolean validate) + public void SaveAs(String file, Boolean validate, Boolean evaluateFormulae = false) { checkForWorksheetsPresent(); PathHelper.CreateDirectory(Path.GetDirectoryName(file)); @@ -476,14 +476,14 @@ if (File.Exists(file)) File.Delete(file); - CreatePackage(file, GetSpreadsheetDocumentType(file), validate); + CreatePackage(file, GetSpreadsheetDocumentType(file), validate, evaluateFormulae); } else if (_loadSource == XLLoadSource.File) { if (String.Compare(_originalFile.Trim(), file.Trim(), true) != 0) File.Copy(_originalFile, file, true); - CreatePackage(file, GetSpreadsheetDocumentType(file), validate); + CreatePackage(file, GetSpreadsheetDocumentType(file), validate, evaluateFormulae); } else if (_loadSource == XLLoadSource.Stream) { @@ -493,7 +493,7 @@ { CopyStream(_originalStream, fileStream); //fileStream.Position = 0; - CreatePackage(fileStream, false, _spreadsheetDocumentType, validate); + CreatePackage(fileStream, false, _spreadsheetDocumentType, validate, evaluateFormulae); fileStream.Close(); } } @@ -531,16 +531,16 @@ public void SaveAs(Stream stream) { #if DEBUG - SaveAs(stream, true); + SaveAs(stream, true, false); #else - SaveAs(stream, false); + SaveAs(stream, false, false); #endif } /// /// Saves the current workbook to a stream and optionally validates it. /// - public void SaveAs(Stream stream, Boolean validate) + public void SaveAs(Stream stream, Boolean validate, Boolean evaluateFormulae = false) { checkForWorksheetsPresent(); if (_loadSource == XLLoadSource.New) @@ -552,13 +552,13 @@ if (stream.CanRead && stream.CanSeek && stream.CanWrite) { // all is fine the package can be created in a direct way - CreatePackage(stream, true, _spreadsheetDocumentType, validate); + CreatePackage(stream, true, _spreadsheetDocumentType, validate, evaluateFormulae); } else { // the harder way MemoryStream ms = new MemoryStream(); - CreatePackage(ms, true, _spreadsheetDocumentType, validate); + CreatePackage(ms, true, _spreadsheetDocumentType, validate, evaluateFormulae); // not really nessesary, because I changed CopyStream too. // but for better understanding and if somebody in the future // provide an changed version of CopyStream @@ -573,7 +573,7 @@ CopyStream(fileStream, stream); fileStream.Close(); } - CreatePackage(stream, false, _spreadsheetDocumentType, validate); + CreatePackage(stream, false, _spreadsheetDocumentType, validate, evaluateFormulae); } else if (_loadSource == XLLoadSource.Stream) { @@ -581,7 +581,7 @@ if (_originalStream != stream) CopyStream(_originalStream, stream); - CreatePackage(stream, false, _spreadsheetDocumentType, validate); + CreatePackage(stream, false, _spreadsheetDocumentType, validate, evaluateFormulae); } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 1fde9bd..20a9088 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -62,7 +62,7 @@ private static readonly EnumValue CvDate = new EnumValue(CellValues.Date); private static readonly EnumValue CvBoolean = new EnumValue(CellValues.Boolean); - private static EnumValue GetCellValue(XLCell xlCell) + private static EnumValue GetCellValueType(XLCell xlCell) { switch (xlCell.DataType) { @@ -111,7 +111,7 @@ return true; } - private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, bool validate) + private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, bool validate, bool evaluateFormulae) { PathHelper.CreateDirectory(Path.GetDirectoryName(filePath)); var package = File.Exists(filePath) @@ -120,12 +120,12 @@ using (package) { - CreateParts(package); + CreateParts(package, evaluateFormulae); if (validate) Validate(package); } } - private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType, bool validate) + private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType, bool validate, bool evaluateFormulae) { var package = newStream ? SpreadsheetDocument.Create(stream, spreadsheetDocumentType) @@ -133,7 +133,7 @@ using (package) { - CreateParts(package); + CreateParts(package, evaluateFormulae); if (validate) Validate(package); } } @@ -218,7 +218,7 @@ } // Adds child parts and generates content of the specified part. - private void CreateParts(SpreadsheetDocument document) + private void CreateParts(SpreadsheetDocument document, bool evaluateFormulae) { var context = new SaveContext(); @@ -314,7 +314,7 @@ GenerateVmlDrawingPartContent(vmlDrawingPart, worksheet, context); } - GenerateWorksheetPartContent(worksheetPart, worksheet, context); + GenerateWorksheetPartContent(worksheetPart, worksheet, evaluateFormulae, context); if (worksheet.PivotTables.Any()) { @@ -3695,8 +3695,8 @@ #region GenerateWorksheetPartContent - private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, - SaveContext context) + private static void GenerateWorksheetPartContent( + WorksheetPart worksheetPart, XLWorksheet xlWorksheet, bool evaluateFormulae, SaveContext context) { #region Worksheet @@ -4181,16 +4181,13 @@ lastCell = 0; var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null ? (XLHelper.GetColumnLetterFromNumber(++lastCell) + distinctRow) : c.CellReference.Value), c => c); - foreach (var opCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values + foreach (var xlCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values .OrderBy(c => c.Address.ColumnNumber) .Select(c => c)) { - var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId; - - var dataType = opCell.DataType; - var cellReference = (opCell.Address).GetTrimmedAddress(); - - var isEmpty = opCell.IsEmpty(true); + var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; + var cellReference = (xlCell.Address).GetTrimmedAddress(); + var isEmpty = xlCell.IsEmpty(true); Cell cell = null; if (cellsByReference.ContainsKey(cellReference)) @@ -4230,20 +4227,20 @@ } cell.StyleIndex = styleId; - var formula = opCell.FormulaA1; - if (opCell.HasFormula) + var formula = xlCell.FormulaA1; + if (xlCell.HasFormula) { if (formula.StartsWith("{")) { formula = formula.Substring(1, formula.Length - 2); var f = new CellFormula { FormulaType = CellFormulaValues.Array }; - if (opCell.FormulaReference == null) - opCell.FormulaReference = opCell.AsRange().RangeAddress; - if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address)) + if (xlCell.FormulaReference == null) + xlCell.FormulaReference = xlCell.AsRange().RangeAddress; + if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address)) { f.Text = formula; - f.Reference = opCell.FormulaReference.ToStringRelative(); + f.Reference = xlCell.FormulaReference.ToStringRelative(); } cell.CellFormula = f; @@ -4259,56 +4256,12 @@ else { cell.CellFormula = null; - - cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); - - if (dataType == XLCellValues.Text) - { - if (opCell.InnerText.Length == 0) - cell.CellValue = null; - else - { - if (opCell.ShareString) - { - var cellValue = new CellValue(); - cellValue.Text = opCell.SharedStringId.ToString(); - cell.CellValue = cellValue; - } - else - { - var text = opCell.GetString(); - var t = new Text(text); - if (text.PreserveSpaces()) - t.Space = SpaceProcessingModeValues.Preserve; - - cell.InlineString = new InlineString { Text = t }; - } - } - } - else if (dataType == XLCellValues.TimeSpan) - { - var timeSpan = opCell.GetTimeSpan(); - var cellValue = new CellValue(); - cellValue.Text = - XLCell.BaseDate.Add(timeSpan).ToOADate().ToInvariantString(); - cell.CellValue = cellValue; - } - else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) - { - if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) - { - var cellValue = new CellValue(); - cellValue.Text = Double.Parse(opCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); - cell.CellValue = cellValue; - } - } - else - { - var cellValue = new CellValue(); - cellValue.Text = opCell.InnerText; - cell.CellValue = cellValue; - } + cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); } + + if (!xlCell.HasFormula || evaluateFormulae) + SetCellValue(xlCell, cell); + } } xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); @@ -4824,6 +4777,74 @@ #endregion LegacyDrawingHeaderFooter } + private static void SetCellValue(XLCell xlCell, Cell openXmlCell) + { + if (xlCell.HasFormula) + { + var cellValue = new CellValue(); + try + { + cellValue.Text = xlCell.Value.ToString(); + openXmlCell.DataType = new EnumValue(CellValues.String); + } + catch + { + cellValue = null; + } + + openXmlCell.CellValue = cellValue; + return; + } + + var dataType = xlCell.DataType; + if (dataType == XLCellValues.Text) + { + if (xlCell.InnerText.Length == 0) + openXmlCell.CellValue = null; + else + { + if (xlCell.ShareString) + { + var cellValue = new CellValue(); + cellValue.Text = xlCell.SharedStringId.ToString(); + openXmlCell.CellValue = cellValue; + } + else + { + var text = xlCell.GetString(); + var t = new Text(text); + if (text.PreserveSpaces()) + t.Space = SpaceProcessingModeValues.Preserve; + + openXmlCell.InlineString = new InlineString { Text = t }; + } + } + } + else if (dataType == XLCellValues.TimeSpan) + { + var timeSpan = xlCell.GetTimeSpan(); + var cellValue = new CellValue(); + cellValue.Text = + XLCell.BaseDate.Add(timeSpan).ToOADate().ToInvariantString(); + openXmlCell.CellValue = cellValue; + } + else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + { + if (!XLHelper.IsNullOrWhiteSpace(xlCell.InnerText)) + { + var cellValue = new CellValue(); + cellValue.Text = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); + openXmlCell.CellValue = cellValue; + } + } + else + { + var cellValue = new CellValue(); + cellValue.Text = xlCell.InnerText; + openXmlCell.CellValue = cellValue; + } + } + private static void PopulateAutoFilter(XLAutoFilter xlAutoFilter, AutoFilter autoFilter) { var filterRange = xlAutoFilter.Range; diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 93f9566..aee3bcf 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -1,202 +1,203 @@ - - - - Debug - x86 - 8.0.30703 - 2.0 - {03A518D0-1CB7-488E-861C-C4E782B27A46} - Exe - Properties - ClosedXML_Examples - ClosedXML_Examples - v4.5.2 - - - 512 - ..\ - true - - - true - bin\Debug\ - DEBUG;TRACE - full - AnyCPU - prompt - false - - - bin\Release\ - TRACE - true - pdbonly - AnyCPU - prompt - false - - - ClosedXML_Examples.Program - - - true - - - ClosedXML.snk - - - true - - - - ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll - True - - - - - - - - - - - - - - - Properties\AssemblyVersionInfo.cs - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - .editorconfig - - - - - - - - {BD5E6BFE-E837-4A35-BCA9-39667D873A20} - ClosedXML - - - - - - - - - - - - - + + + + Debug + x86 + 8.0.30703 + 2.0 + {03A518D0-1CB7-488E-861C-C4E782B27A46} + Exe + Properties + ClosedXML_Examples + ClosedXML_Examples + v4.5.2 + + + 512 + ..\ + true + + + true + bin\Debug\ + DEBUG;TRACE + full + AnyCPU + prompt + false + + + bin\Release\ + TRACE + true + pdbonly + AnyCPU + prompt + false + + + ClosedXML_Examples.Program + + + true + + + ClosedXML.snk + + + true + + + + ..\packages\DocumentFormat.OpenXml.2.7.2\lib\net40\DocumentFormat.OpenXml.dll + True + + + + + + + + + + + + + + + Properties\AssemblyVersionInfo.cs + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .editorconfig + + + + + + + + {BD5E6BFE-E837-4A35-BCA9-39667D873A20} + ClosedXML + + + + + + + + + + + + + \ No newline at end of file diff --git a/ClosedXML_Examples/Misc/Formulas.cs b/ClosedXML_Examples/Misc/Formulas.cs index 56f914c..729cb9e 100644 --- a/ClosedXML_Examples/Misc/Formulas.cs +++ b/ClosedXML_Examples/Misc/Formulas.cs @@ -1,46 +1,11 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples.Misc { public class Formulas : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - - // Public - public void Create(String filePath) + public virtual void Create(String filePath) { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Formulas"); @@ -83,7 +48,7 @@ // Setting the formula of a range var rngData = ws.Range(2, 1, 4, 7); - rngData.LastColumn().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")"; + rngData.LastColumn().FormulaR1C1 = "=IF(RC[-4]=RC[-1],\"Yes\", \"No\")"; // Using an array formula: // Just put the formula between curly braces @@ -109,12 +74,5 @@ wb.SaveAs(filePath); } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/FormulasWithEvaluation.cs b/ClosedXML_Examples/Misc/FormulasWithEvaluation.cs new file mode 100644 index 0000000..dca890e --- /dev/null +++ b/ClosedXML_Examples/Misc/FormulasWithEvaluation.cs @@ -0,0 +1,16 @@ +using ClosedXML.Excel; + +namespace ClosedXML_Examples.Misc +{ + public class FormulasWithEvaluation : Formulas + { + public override void Create(string filePath) + { + base.Create(filePath); + using (var wb = new XLWorkbook(filePath)) + { + wb.Save(true, true); + } + } + } +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 1542b4d..3f12c66 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -86,6 +86,7 @@ + @@ -162,6 +163,7 @@ + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index a78a7fa..c1f97bf 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -98,6 +98,12 @@ } [Test] + public void FormulasWithEvaluation() + { + TestHelper.RunTestExample(@"Misc\FormulasWithEvaluation.xlsx", true); + } + + [Test] public void FreezePanes() { TestHelper.RunTestExample(@"Misc\FreezePanes.xlsx"); diff --git a/ClosedXML_Tests/OleDb/OleDbTests.cs b/ClosedXML_Tests/OleDb/OleDbTests.cs new file mode 100644 index 0000000..7d75a65 --- /dev/null +++ b/ClosedXML_Tests/OleDb/OleDbTests.cs @@ -0,0 +1,146 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.OleDb; +using System.IO; +using System.Linq; + +namespace ClosedXML_Tests.OleDb +{ + [TestFixture] + public class OleDbTests + { +#if !APPVEYOR + [Test] + public void TestOleDbValues() + { + using (var tf = new TestFile(CreateTestFile(), true)) + { + Console.Write("Using temporary file\t{0}", tf.Path); + var connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';", tf.Path); + using (var connection = new OleDbConnection(connectionString)) + { + // Install driver from https://www.microsoft.com/en-za/download/details.aspx?id=13255 if required + // Also check that test runner is running under correct architecture: + connection.Open(); + using (var command = new OleDbCommand("select * from [Sheet1$]", connection)) + using (var dataAdapter = new OleDbDataAdapter()) + { + dataAdapter.SelectCommand = command; + var dt = new DataTable(); + dataAdapter.Fill(dt); + + Assert.AreEqual("Base", dt.Columns[0].ColumnName); + Assert.AreEqual("Ref", dt.Columns[1].ColumnName); + + Assert.AreEqual(2, dt.Rows.Count); + + Assert.AreEqual(42, dt.Rows.Cast().First()[0]); + Assert.AreEqual("42", dt.Rows.Cast().First()[1]); + + Assert.AreEqual(41, dt.Rows.Cast().Last()[0]); + Assert.AreEqual("41", dt.Rows.Cast().Last()[1]); + } + + using (var command = new OleDbCommand("select * from [Sheet2$]", connection)) + using (var dataAdapter = new OleDbDataAdapter()) + { + dataAdapter.SelectCommand = command; + var dt = new DataTable(); + dataAdapter.Fill(dt); + + Assert.AreEqual("Ref1", dt.Columns[0].ColumnName); + Assert.AreEqual("Ref2", dt.Columns[1].ColumnName); + Assert.AreEqual("Sum", dt.Columns[2].ColumnName); + Assert.AreEqual("SumRef", dt.Columns[3].ColumnName); + + var expected = new Dictionary() + { + {"Ref1", "42" }, + {"Ref2", "41" }, + {"Sum", "83" }, + {"SumRef", "83" }, + }; + + foreach (var col in dt.Columns.Cast()) + foreach (var row in dt.Rows.Cast()) + { + Assert.AreEqual(expected[col.ColumnName], row[col]); + } + + Assert.AreEqual(2, dt.Rows.Count); + } + + connection.Close(); + } + } + } +#endif + + private string CreateTestFile() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Value = "Base"; + ws.Cell("B1").Value = "Ref"; + + ws.Cell("A2").Value = 42; + ws.Cell("A3").Value = 41; + + ws.Cell("B2").FormulaA1 = "=A2"; + ws.Cell("B3").FormulaA1 = "=A3"; + + ws = wb.AddWorksheet("Sheet2"); + ws.Cell("A1").Value = "Ref1"; + ws.Cell("B1").Value = "Ref2"; + ws.Cell("C1").Value = "Sum"; + ws.Cell("D1").Value = "SumRef"; + + ws.Cell("A2").FormulaA1 = "=Sheet1!A2"; + ws.Cell("B2").FormulaA1 = "=Sheet1!A3"; + ws.Cell("C2").FormulaA1 = "=SUM(A2:B2)"; + ws.Cell("D2").FormulaA1 = "=SUM(Sheet1!A2:Sheet1!A3)"; + + ws.Cell("A3").FormulaA1 = "=Sheet1!B2"; + ws.Cell("B3").FormulaA1 = "=Sheet1!B3"; + ws.Cell("C3").FormulaA1 = "=SUM(A3:B3)"; + ws.Cell("D3").FormulaA1 = "=SUM(Sheet1!B2:Sheet1!B3)"; + + var path = Path.ChangeExtension(Path.GetTempFileName(), "xlsx"); + wb.SaveAs(path, true, true); + + return path; + } + } + + internal class TestFile : IDisposable + { + internal TestFile(string path) + : this(path, false) + { } + + internal TestFile(string path, bool preserve) + { + this.Path = path; + this.Preserve = preserve; + } + + public string Path { get; private set; } + public bool Preserve { get; private set; } + + public void Dispose() + { + if (!Preserve) + File.Delete(Path); + } + + public override string ToString() + { + return this.Path; + } + } + } +} diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx index c2ad2c1..dea7693 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx new file mode 100644 index 0000000..7a93ddb --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx Binary files differ diff --git a/ClosedXML_Tests/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index 431d6ef..bf85115 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -1,12 +1,11 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.IO; -using System.Threading; using ClosedXML.Excel; using ClosedXML_Examples; -using DocumentFormat.OpenXml.Drawing; using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; +using System.Threading; using Path = System.IO.Path; namespace ClosedXML_Tests @@ -25,7 +24,6 @@ { return Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); } - } public const string ActualTestResultPostFix = ""; @@ -53,20 +51,20 @@ } } - public static void RunTestExample(string filePartName) + public static void RunTestExample(string filePartName, bool evaluateFormulae = false) where T : IXLExample, new() { // Make sure tests run on a deterministic culture Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); var example = new T(); - string[] pathParts = filePartName.Split(new char[] {'\\'}); + string[] pathParts = filePartName.Split(new char[] { '\\' }); string filePath1 = Path.Combine(new List() { TestsExampleOutputDirectory }.Concat(pathParts).ToArray()); var extension = Path.GetExtension(filePath1); var directory = Path.GetDirectoryName(filePath1); - var fileName= Path.GetFileNameWithoutExtension(filePath1); + var fileName = Path.GetFileNameWithoutExtension(filePath1); fileName += ActualTestResultPostFix; fileName = Path.ChangeExtension(fileName, extension); @@ -75,7 +73,7 @@ //Run test example.Create(filePath1); using (var wb = new XLWorkbook(filePath1)) - wb.SaveAs(filePath2); + wb.SaveAs(filePath2, true, evaluateFormulae); bool success = true; #pragma warning disable 162 @@ -84,7 +82,7 @@ //Compare // ReSharper disable ConditionIsAlwaysTrueOrFalse if (CompareWithResources) - // ReSharper restore ConditionIsAlwaysTrueOrFalse + // ReSharper restore ConditionIsAlwaysTrueOrFalse { string resourcePath = filePartName.Replace('\\', '.').TrimStart('.');