diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index e1b6e51..00d8a32 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -51,7 +51,9 @@ return cs.Equals(value); // if criteria is an expression (e.g. ">20"), use calc engine - if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') + if ((cs[0] == '=' && cs.IndexOfAny(new[] { '*', '?' }) < 0) + || cs[0] == '<' + || cs[0] == '>') { // build expression var expression = string.Format("{0}{1}", value, cs); @@ -79,6 +81,8 @@ // if criteria is a regular expression, use regex if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { + if (cs[0] == '=') cs = cs.Substring(1); + var pattern = Regex.Replace( cs, "(" + String.Join( @@ -99,5 +103,27 @@ Debug.Assert(false, "failed to evaluate criteria in SumIf"); return false; } + + internal static bool ValueIsBlank(object value) + { + return + value == null || + value is string && ((string)value).Length == 0; + } + + /// + /// Get total count of cells in the specified range without initalizing them all + /// (which might cause serious performance issues on column-wide calculations). + /// + /// Expression referring to the cell range. + /// Total number of cells in the range. + internal static long GetTotalCellsCount(XObjectExpression rangeExpression) + { + var range = ((rangeExpression)?.Value as CellRangeReference)?.Range; + if (range == null) + return 0; + return (long)(range.LastColumn().ColumnNumber() - range.FirstColumn().ColumnNumber() + 1) * + (long)(range.LastRow().RowNumber() - range.FirstRow().RowNumber() + 1); + } } } diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index ff1288c..a65b7ad 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -486,6 +486,9 @@ public IEnumerator GetEnumerator() { + if (_value is string) + return new [] {(string) _value}.GetEnumerator(); + return (_value as IEnumerable).GetEnumerator(); } diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index dfd9d25..4eb642e 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -340,20 +340,22 @@ { rangeValues.Add(value); } - var sumRangeValues = new List(); - foreach (var cell in ((CellRangeReference)sumRange.Value).Range.Cells()) + var sumRangeValues = new List(); + foreach (var value in sumRange) { - sumRangeValues.Add(cell); + sumRangeValues.Add(value); } // compute total var ce = new CalcEngine(); var tally = new Tally(); - for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++) + for (var i = 0; i < Math.Max(rangeValues.Count, sumRangeValues.Count); i++) { - if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) + var targetValue = i < rangeValues.Count ? rangeValues[i] : string.Empty; + if (CalcEngineHelpers.ValueSatisfiesCriteria(targetValue, criteria, ce)) { - tally.AddValue(sumRangeValues[i].Value); + var value = i < sumRangeValues.Count ? sumRangeValues[i] : 0d; + tally.AddValue(value); } } @@ -378,18 +380,18 @@ int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want. // prepare criteria-parameters: - var criteriaRanges = new Tuple>[numberOfCriteria]; - for(int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + var criteriaRanges = new Tuple>[numberOfCriteria]; + for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) { - var criterion = p[criteriaPair * 2 + 1].Evaluate(); - var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; - var criteriaRangeValues = new List(); - foreach (var value in criteriaRange) - { - criteriaRangeValues.Add(value); - } + var criteriaRange = p[criteriaPair * 2 + 1] as IEnumerable; - criteriaRanges[criteriaPair] = new Tuple>( + if (criteriaRange == null) + throw new CellReferenceException($"Expected parameter {criteriaPair * 2 + 2} to be a range"); + + var criterion = p[criteriaPair * 2 + 2].Evaluate(); + var criteriaRangeValues = criteriaRange.Cast().ToList(); + + criteriaRanges[criteriaPair] = new Tuple>( criterion, criteriaRangeValues); } @@ -398,10 +400,10 @@ { bool shouldUseValue = true; - foreach(var criteriaPair in criteriaRanges) + foreach (var criteriaPair in criteriaRanges) { if (!CalcEngineHelpers.ValueSatisfiesCriteria( - criteriaPair.Item2[i], + i < criteriaPair.Item2.Count ? criteriaPair.Item2[i] : string.Empty, criteriaPair.Item1, ce)) { @@ -630,7 +632,6 @@ else throw new NumberException(); - n = (int)p[0]; k = (int)p[1]; @@ -670,11 +671,9 @@ if (!(input is long || input is int || input is byte || input is double || input is float)) throw new CellValueException(); - var num = Math.Floor((double)input); double fact = 1.0; - if (num < 0) throw new NumberException(); diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 58baace..06f720a 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -1,7 +1,7 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; -using System.Net; -using System.Collections; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel.CalcEngine { @@ -96,68 +96,65 @@ //ZTEST Returns the one-tailed probability-value of a z-test } - - - static object Average(List p) + private static object Average(List p) { return GetTally(p, true).Average(); } - static object AverageA(List p) + + private static object AverageA(List p) { return GetTally(p, false).Average(); } - static object Count(List p) + + private static object Count(List p) { return GetTally(p, true).Count(); } - static object CountA(List p) + + private static object CountA(List p) { return GetTally(p, false).Count(); } - static object CountBlank(List p) + + private static object CountBlank(List p) { - var cnt = 0.0; - foreach (Expression e in p) + if ((p[0] as XObjectExpression)?.Value as CellRangeReference == null) + throw new NoValueAvailableException("COUNTBLANK should have a single argument which is a range reference"); + + var e = p[0] as XObjectExpression; + long totalCount = CalcEngineHelpers.GetTotalCellsCount(e); + long nonBlankCount = 0; + foreach (var value in e) { - var ienum = e as IEnumerable; - if (ienum != null) - { - foreach (var value in ienum) - { - if (IsBlank(value)) - cnt++; - } - } - else - { - if (IsBlank(e.Evaluate())) - cnt++; - } + if (!CalcEngineHelpers.ValueIsBlank(value)) + nonBlankCount++; } - return cnt; + + return 0d + totalCount - nonBlankCount; } - internal static bool IsBlank(object value) - { - return - value == null || - value is string && ((string)value).Length == 0; - } - - static object CountIf(List p) + private static object CountIf(List p) { CalcEngine ce = new CalcEngine(); var cnt = 0.0; - var ienum = p[0] as IEnumerable; + long processedCount = 0; + var ienum = p[0] as XObjectExpression; if (ienum != null) { + long totalCount = CalcEngineHelpers.GetTotalCellsCount(ienum); var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) cnt++; + processedCount++; } + + // Add count of empty cells outside the used range if they match criteria + if (CalcEngineHelpers.ValueSatisfiesCriteria(string.Empty, criteria, ce)) + cnt += (totalCount - processedCount); } + return cnt; } @@ -165,15 +162,16 @@ { // get parameters var ce = new CalcEngine(); - int count = 0; + long count = 0; int numberOfCriteria = p.Count / 2; + long totalCount = 0; // prepare criteria-parameters: var criteriaRanges = new Tuple>[numberOfCriteria]; for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) { - var criteriaRange = p[criteriaPair * 2] as IEnumerable; + var criteriaRange = p[criteriaPair * 2] as XObjectExpression; var criterion = p[(criteriaPair * 2) + 1].Evaluate(); var criteriaRangeValues = new List(); foreach (var value in criteriaRange) @@ -184,85 +182,94 @@ criteriaRanges[criteriaPair] = new Tuple>( criterion, criteriaRangeValues); + + if (totalCount == 0) + totalCount = CalcEngineHelpers.GetTotalCellsCount(criteriaRange); } + long processedCount = 0; for (var i = 0; i < criteriaRanges[0].Item2.Count; i++) { - bool shouldUseValue = true; - - foreach (var criteriaPair in criteriaRanges) - { - if (!CalcEngineHelpers.ValueSatisfiesCriteria( - criteriaPair.Item2[i], - criteriaPair.Item1, - ce)) - { - shouldUseValue = false; - break; // we're done with the inner loop as we can't ever get true again. - } - } - - if (shouldUseValue) + if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria( + criteriaPair.Item2[i], criteriaPair.Item1, ce))) count++; + + processedCount++; + } + + // Add count of empty cells outside the used range if they match criteria + if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria( + string.Empty, criteriaPair.Item1, ce))) + { + count += (totalCount - processedCount); } // done return count; } - static object Max(List p) + private static object Max(List p) { return GetTally(p, true).Max(); } - static object MaxA(List p) + private static object MaxA(List p) { return GetTally(p, false).Max(); } - static object Min(List p) + private static object Min(List p) { return GetTally(p, true).Min(); } - static object MinA(List p) + + private static object MinA(List p) { return GetTally(p, false).Min(); } - static object StDev(List p) + + private static object StDev(List p) { return GetTally(p, true).Std(); } - static object StDevA(List p) + + private static object StDevA(List p) { return GetTally(p, false).Std(); } - static object StDevP(List p) + + private static object StDevP(List p) { return GetTally(p, true).StdP(); } - static object StDevPA(List p) + + private static object StDevPA(List p) { return GetTally(p, false).StdP(); } - static object Var(List p) + + private static object Var(List p) { return GetTally(p, true).Var(); } - static object VarA(List p) + + private static object VarA(List p) { return GetTally(p, false).Var(); } - static object VarP(List p) + + private static object VarP(List p) { return GetTally(p, true).VarP(); } - static object VarPA(List p) + + private static object VarPA(List p) { return GetTally(p, false).VarP(); } // utility for tallying statistics - static Tally GetTally(List p, bool numbersOnly) + private static Tally GetTally(List p, bool numbersOnly) { return new Tally(p, numbersOnly); } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index 5cf212e..93abf67 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -13,7 +13,6 @@ private double[] _numericValues; - public Tally() : this(false) { } @@ -81,10 +80,10 @@ if (numbersOnly) return NumericValuesInternal().Length; else - return _list.Count(o => !Statistical.IsBlank(o)); + return _list.Count(o => !CalcEngineHelpers.ValueIsBlank(o)); } - IEnumerable NumericValuesEnumerable() + private IEnumerable NumericValuesEnumerable() { foreach (var value in _list) { @@ -92,14 +91,14 @@ var vEnumerable = value as IEnumerable; if (vEnumerable == null) { - if (double.TryParse(value.ToString(), out tmp)) + if (TryParseToDouble(value, out tmp)) yield return tmp; } else { foreach (var v in vEnumerable) { - if (double.TryParse(v.ToString(), out tmp)) + if (TryParseToDouble(v, out tmp)) yield return tmp; break; } @@ -107,7 +106,25 @@ } } - double[] NumericValuesInternal() + private bool TryParseToDouble(object value, out double d) + { + if (value.IsNumber()) + { + d = Convert.ToDouble(value); + return true; + } + else if (value is DateTime) + { + d = Convert.ToDouble(((DateTime)value).ToOADate()); + return true; + } + else + { + return double.TryParse(value.ToString(), out d); + } + } + + private double[] NumericValuesInternal() => LazyInitializer.EnsureInitialized(ref _numericValues, () => NumericValuesEnumerable().ToArray()); public IEnumerable NumericValues() @@ -144,7 +161,7 @@ public double Range() => Max() - Min(); - static double Sum2(IEnumerable nums) + private static double Sum2(IEnumerable nums) { return nums.Sum(d => d * d); } diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index 96a8f62..86155cb 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -82,7 +82,10 @@ // ** IEnumerable public IEnumerator GetEnumerator() { - return _range.Cells().Select(GetValue).GetEnumerator(); + var maxRow = Math.Min(_range.RangeAddress.LastAddress.RowNumber, _range.Worksheet.LastCellUsed().Address.RowNumber); + var maxCol = Math.Min(_range.RangeAddress.LastAddress.ColumnNumber, _range.Worksheet.LastCellUsed().Address.ColumnNumber); + using (var trimmedRange = (XLRangeBase)_range.Worksheet.Range(_range.FirstCell().Address, new XLAddress(maxRow, maxCol, false, false))) + return trimmedRange.CellValues().GetEnumerator(); } private Boolean _evaluating; @@ -90,9 +93,9 @@ // ** implementation private object GetValue(IXLCell cell) { - if (_evaluating) + if (_evaluating || (cell as XLCell).IsEvaluating) { - throw new InvalidOperationException("Circular Reference"); + throw new InvalidOperationException($"Circular Reference occured during evaluation. Cell: {cell.Address.ToString(XLReferenceStyle.Default, true)}"); } try { diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 28309e3..274a50f 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -9,13 +9,6 @@ public enum XLTableCellType { None, Header, Data, Total } - public enum XLClearOptions - { - ContentsAndFormats, - Contents, - Formats - } - public interface IXLCell { /// @@ -131,7 +124,7 @@ /// Clears the contents of this cell. /// /// Specify what you want to clear. - IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.All); /// /// Deletes the current cell and shifts the surrounding cells according to the shiftDeleteCells parameter. @@ -326,6 +319,8 @@ Boolean IsMerged(); + IXLRange MergedRange(); + Boolean IsEmpty(); Boolean IsEmpty(Boolean includeFormats); diff --git a/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/Excel/Cells/IXLCells.cs index e42a5ae..bc1d953 100644 --- a/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/Excel/Cells/IXLCells.cs @@ -34,7 +34,7 @@ /// Clears the contents of these cells. /// /// Specify what you want to clear. - IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.All); /// /// Delete the comments of these cells. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 71e0a30..1debabd 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -356,6 +356,11 @@ return cValue; } + /// + /// Flag showing that the cell is in formula evaluation state. + /// + internal bool IsEvaluating { get; private set; } + public object Value { get @@ -363,6 +368,9 @@ var fA1 = FormulaA1; if (!XLHelper.IsNullOrWhiteSpace(fA1)) { + if (IsEvaluating) + throw new InvalidOperationException("Circular Reference"); + if (fA1[0] == '{') fA1 = fA1.Substring(1, fA1.Length - 2); @@ -382,19 +390,31 @@ cAddress = fA1; } - if (_worksheet.Workbook.WorksheetsInternal.Any( - w => String.Compare(w.Name, sName, true) == 0) - && XLHelper.IsValidA1Address(cAddress) - ) + object retVal; + try { - var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); - if (referenceCell.IsEmpty(false)) - return 0; - else - return referenceCell.Value; + IsEvaluating = true; + + if (_worksheet + .Workbook + .WorksheetsInternal + .Any(w => String.Compare(w.Name, sName, true) == 0) + && XLHelper.IsValidA1Address(cAddress)) + { + var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); + if (referenceCell.IsEmpty(false)) + return 0; + else + return referenceCell.Value; + } + + retVal = Worksheet.Evaluate(fA1); + } + finally + { + IsEvaluating = false; } - var retVal = Worksheet.Evaluate(fA1); var retValEnumerable = retVal as IEnumerable; if (retValEnumerable != null && !(retVal is String)) @@ -1001,7 +1021,7 @@ } } - public IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.All) { return Clear(clearOptions, false); } @@ -1021,24 +1041,33 @@ } else { - if (clearOptions == XLClearOptions.Contents || clearOptions == XLClearOptions.ContentsAndFormats) + if (clearOptions.HasFlag(XLClearOptions.Contents)) { Hyperlink = null; _richText = null; - //_comment = null; _cellValue = String.Empty; FormulaA1 = String.Empty; } - if (clearOptions == XLClearOptions.Formats || clearOptions == XLClearOptions.ContentsAndFormats) - { - if (HasDataValidation) - { - var validation = NewDataValidation; - Worksheet.DataValidations.Delete(validation); - } + if (clearOptions.HasFlag(XLClearOptions.DataType)) + _dataType = XLDataType.Text; + if (clearOptions.HasFlag(XLClearOptions.NormalFormats)) SetStyle(Worksheet.Style); + + if (clearOptions.HasFlag(XLClearOptions.ConditionalFormats)) + { + using (var r = this.AsRange()) + r.RemoveConditionalFormatting(); + } + + if (clearOptions.HasFlag(XLClearOptions.Comments)) + _comment = null; + + if (clearOptions.HasFlag(XLClearOptions.DataValidation) && HasDataValidation) + { + var validation = NewDataValidation; + Worksheet.DataValidations.Delete(validation); } } @@ -1212,6 +1241,11 @@ return Worksheet.Internals.MergedRanges.Contains(this); } + public IXLRange MergedRange() + { + return Worksheet.Internals.MergedRanges.FirstOrDefault(r => r.Contains(this)); + } + public Boolean IsEmpty() { return IsEmpty(false); @@ -1722,7 +1756,7 @@ public void DeleteComment() { - _comment = null; + Clear(XLClearOptions.Comments); } private bool IsDateFormat() diff --git a/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/Excel/Cells/XLCells.cs index bde0c7f..1fbc0be 100644 --- a/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/Excel/Cells/XLCells.cs @@ -1,7 +1,6 @@ using System; using System.Collections; using System.Collections.Generic; -using System.Linq; namespace ClosedXML.Excel { @@ -10,6 +9,7 @@ internal class XLCells : IXLCells, IXLStylized, IEnumerable { public Boolean StyleChanged { get; set; } + #region Fields private readonly bool _includeFormats; @@ -17,7 +17,8 @@ private readonly bool _usedCellsOnly; private IXLStyle _style; private readonly Func _predicate; - #endregion + + #endregion Fields #region Constructor @@ -29,7 +30,7 @@ _predicate = predicate; } - #endregion + #endregion Constructor #region IEnumerable Members @@ -65,7 +66,7 @@ && (_predicate == null || _predicate(c)) ); - foreach(var cell in cellRange) + foreach (var cell in cellRange) { yield return cell; } @@ -89,12 +90,12 @@ else { var mm = new MinMax - { - MinRow = range.FirstAddress.RowNumber, - MaxRow = range.LastAddress.RowNumber, - MinColumn = range.FirstAddress.ColumnNumber, - MaxColumn = range.LastAddress.ColumnNumber - }; + { + MinRow = range.FirstAddress.RowNumber, + MaxRow = range.LastAddress.RowNumber, + MinColumn = range.FirstAddress.ColumnNumber, + MaxColumn = range.LastAddress.ColumnNumber + }; if (mm.MaxRow > 0 && mm.MaxColumn > 0) { for (Int32 ro = mm.MinRow; ro <= mm.MaxRow; ro++) @@ -154,7 +155,7 @@ } } - #endregion + #endregion IEnumerable Members #region IXLCells Members @@ -195,14 +196,14 @@ set { this.ForEach(c => c.DataType = value); } } - - public IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.All) { this.ForEach(c => c.Clear(clearOptions)); return this; } - public void DeleteComments() { + public void DeleteComments() + { this.ForEach(c => c.DeleteComment()); } @@ -216,7 +217,7 @@ set { this.ForEach(c => c.FormulaR1C1 = value); } } - #endregion + #endregion IXLCells Members #region IXLStylized Members @@ -250,7 +251,7 @@ } } - #endregion + #endregion IXLStylized Members public void Add(XLRangeAddress rangeAddress) { @@ -274,7 +275,7 @@ public Int32 MinRow; } - #endregion + #endregion Nested type: MinMax public void Select() { diff --git a/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/Excel/Columns/IXLColumn.cs index 1021190..a3eb08b 100644 --- a/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/Excel/Columns/IXLColumn.cs @@ -4,7 +4,6 @@ { public interface IXLColumn : IXLRangeBase { - /// /// Gets or sets the width of this column. /// @@ -66,11 +65,13 @@ /// Adjusts the width of the column based on its contents. /// IXLColumn AdjustToContents(); + /// /// Adjusts the width of the column based on its contents, starting from the startRow. /// /// The row to start calculating the column width. IXLColumn AdjustToContents(Int32 startRow); + /// /// Adjusts the width of the column based on its contents, starting from the startRow and ending at endRow. /// @@ -79,7 +80,9 @@ IXLColumn AdjustToContents(Int32 startRow, Int32 endRow); IXLColumn AdjustToContents(Double minWidth, Double maxWidth); + IXLColumn AdjustToContents(Int32 startRow, Double minWidth, Double maxWidth); + IXLColumn AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth); /// @@ -152,13 +155,17 @@ Int32 CellCount(); IXLRangeColumn CopyTo(IXLCell cell); + IXLRangeColumn CopyTo(IXLRangeBase range); + IXLColumn CopyTo(IXLColumn column); IXLColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeColumn Column(Int32 start, Int32 end); + IXLRangeColumn Column(IXLCell start, IXLCell end); + IXLRangeColumns Columns(String columns); /// @@ -169,17 +176,19 @@ IXLColumn SetDataType(XLDataType dataType); IXLColumn ColumnLeft(); + IXLColumn ColumnLeft(Int32 step); + IXLColumn ColumnRight(); + IXLColumn ColumnRight(Int32 step); /// /// Clears the contents of this column. /// /// Specify what you want to clear. - new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLRangeColumn ColumnUsed(Boolean includeFormats = false); - } } diff --git a/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/Excel/Columns/IXLColumns.cs index 9be1cf5..ac7cf30 100644 --- a/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/Excel/Columns/IXLColumns.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLColumns: IEnumerable, IDisposable + public interface IXLColumns : IEnumerable, IDisposable { /// /// Sets the width of all columns. @@ -22,11 +22,13 @@ /// Adjusts the width of all columns based on its contents. /// IXLColumns AdjustToContents(); + /// /// Adjusts the width of all columns based on its contents, starting from the startRow. /// /// The row to start calculating the column width. IXLColumns AdjustToContents(Int32 startRow); + /// /// Adjusts the width of all columns based on its contents, starting from the startRow and ending at endRow. /// @@ -35,7 +37,9 @@ IXLColumns AdjustToContents(Int32 startRow, Int32 endRow); IXLColumns AdjustToContents(Double minWidth, Double maxWidth); + IXLColumns AdjustToContents(Int32 startRow, Double minWidth, Double maxWidth); + IXLColumns AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth); /// @@ -93,7 +97,7 @@ /// Returns the collection of cells. /// IXLCells Cells(); - + /// /// Returns the collection of cells that have a value. /// @@ -118,7 +122,7 @@ /// Clears the contents of these columns. /// /// Specify what you want to clear. - IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index 3f51208..9a94c32 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -138,7 +138,7 @@ } } - public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; @@ -664,16 +664,19 @@ private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.RangeAddress.FirstAddress.ColumnNumber <= ColumnNumber()) + if (range.RangeAddress.IsValid && + RangeAddress.IsValid && + range.RangeAddress.FirstAddress.ColumnNumber <= ColumnNumber()) SetColumnNumber(ColumnNumber() + columnsShifted); } private void SetColumnNumber(int column) { if (column <= 0) - RangeAddress.IsInvalid = false; + RangeAddress.IsValid = false; else { + RangeAddress.IsValid = true; RangeAddress.FirstAddress = new XLAddress(Worksheet, 1, column, diff --git a/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/Excel/Columns/XLColumns.cs index 9f5b571..05f7a7d 100644 --- a/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/Excel/Columns/XLColumns.cs @@ -213,7 +213,7 @@ return this; } - #endregion + #endregion IXLColumns Members #region IXLStylized Members @@ -254,7 +254,7 @@ } } - #endregion + #endregion IXLStylized Members public void Add(XLColumn column) { @@ -266,9 +266,9 @@ _columns.ForEach(c => c.Collapsed = true); } - public IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.All) { - _columns.ForEach(c=>c.Clear(clearOptions)); + _columns.ForEach(c => c.Clear(clearOptions)); return this; } @@ -284,4 +284,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormats.cs index 8ccfbdd..cae5f8c 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormats.cs @@ -1,14 +1,14 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - public interface IXLConditionalFormats: IEnumerable + public interface IXLConditionalFormats : IEnumerable { void Add(IXLConditionalFormat conditionalFormat); + void RemoveAll(); + void Remove(Predicate predicate); } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs index a27df4d..bddd195 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs @@ -24,7 +24,22 @@ for (Int32 i = 1; i <= cf.Colors.Count; i++) { - Color color = new Color { Rgb = cf.Colors[i].Color.ToHex() }; + var xlColor = cf.Colors[i]; + var color = new Color(); + switch (xlColor.ColorType) + { + case XLColorType.Color: + color.Rgb = xlColor.Color.ToHex(); + break; + case XLColorType.Theme: + color.Theme = System.Convert.ToUInt32(xlColor.ThemeColor); + break; + + case XLColorType.Indexed: + color.Indexed = System.Convert.ToUInt32(xlColor.Indexed); + break; + } + colorScale.Append(color); } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index e55ec35..7c47c57 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -142,6 +142,7 @@ } public Guid Id { get; internal set; } + internal Int32 OriginalPriority { get; set; } public Boolean CopyDefaultModify { get; set; } private IXLStyle _style; private Int32 _styleCacheId; diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index 940bd22..f986db8 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -110,5 +110,15 @@ _conditionalFormats.ForEach(cf => cf.Range.Dispose()); _conditionalFormats.Clear(); } + + /// + /// Reorders the according to original priority. Done during load process + /// + public void ReorderAccordingToOriginalPriority() + { + var reorderedFormats = _conditionalFormats.OrderBy(cf => (cf as XLConditionalFormat).OriginalPriority).ToList(); + _conditionalFormats.Clear(); + _conditionalFormats.AddRange(reorderedFormats); + } } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index dbdfac9..1f8ac67 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { [DebuggerDisplay("{SourceName}")] - public class XLPivotField : IXLPivotField + internal class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { diff --git a/ClosedXML/Excel/Ranges/IXLBaseCollection.cs b/ClosedXML/Excel/Ranges/IXLBaseCollection.cs index ef6b851..d4d6c83 100644 --- a/ClosedXML/Excel/Ranges/IXLBaseCollection.cs +++ b/ClosedXML/Excel/Ranges/IXLBaseCollection.cs @@ -3,8 +3,8 @@ namespace ClosedXML.Excel { - public interface IXLBaseCollection: IEnumerable - { + public interface IXLBaseCollection : IEnumerable + { Int32 Count { get; } IXLStyle Style { get; set; } @@ -12,7 +12,7 @@ IXLDataValidation SetDataValidation(); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// The default scope for the named range is Workbook. /// @@ -20,7 +20,7 @@ TMultiple AddToNamed(String rangeName); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// Name of the range. /// The scope for the named range. @@ -28,7 +28,7 @@ TMultiple AddToNamed(String rangeName, XLScope scope); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// Name of the range. /// The scope for the named range. @@ -72,6 +72,6 @@ /// Clears the contents of these ranges. /// /// Specify what you want to clear. - TMultiple Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + TMultiple Clear(XLClearOptions clearOptions = XLClearOptions.All); } } diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index 291e290..367b83d 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -280,7 +280,7 @@ /// Clears the contents of this range. /// /// Specify what you want to clear. - new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLRangeRows RowsUsed(Boolean includeFormats, Func predicate = null); diff --git a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 86ed2b1..751f1a4 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -13,12 +13,12 @@ IXLAddress FirstAddress { get; set; } /// - /// Gets or sets a value indicating whether this range is invalid. + /// Gets or sets a value indicating whether this range is valid. /// /// - /// true if this instance is invalid; otherwise, false. + /// true if this instance is valid; otherwise, false. /// - Boolean IsInvalid { get; set; } + Boolean IsValid { get; } /// /// Gets or sets the last address in the range. diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 3c5d25e..0932cbf 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -1,4 +1,5 @@ using System; +using System.Collections; using System.Globalization; namespace ClosedXML.Excel @@ -230,7 +231,7 @@ /// Clears the contents of this range. /// /// Specify what you want to clear. - IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.All); /// /// Deletes the cell comments from this range. diff --git a/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 3e701bf..576b230 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -1,6 +1,5 @@ using System; - namespace ClosedXML.Excel { public interface IXLRangeColumn : IXLRangeBase @@ -17,6 +16,7 @@ /// /// The column cells to return. new IXLCells Cells(String cellsInColumn); + /// /// Returns the specified group of cells. /// @@ -30,33 +30,41 @@ /// /// Number of columns to insert. IXLRangeColumns InsertColumnsAfter(int numberOfColumns); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. IXLRangeColumns InsertColumnsBefore(int numberOfColumns); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of cells on top of this column. /// This column and all cells below it will be shifted X number of rows. /// /// Number of cells to insert. IXLCells InsertCellsAbove(int numberOfRows); + IXLCells InsertCellsAbove(int numberOfRows, Boolean expandRange); + /// /// Inserts X number of cells below this range. /// All cells below this column will be shifted X number of rows. /// /// Number of cells to insert. IXLCells InsertCellsBelow(int numberOfRows); + IXLCells InsertCellsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells at the right. /// void Delete(); + /// /// Deletes this range and shifts the surrounding cells accordingly. /// @@ -76,35 +84,43 @@ Int32 CellCount(); IXLRangeColumn CopyTo(IXLCell target); + IXLRangeColumn CopyTo(IXLRangeBase target); IXLRangeColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); - + IXLRangeColumn Column(Int32 start, Int32 end); + IXLRangeColumn Column(IXLCell start, IXLCell end); + IXLRangeColumns Columns(String columns); IXLRangeColumn SetDataType(XLDataType dataType); IXLRangeColumn ColumnLeft(); + IXLRangeColumn ColumnLeft(Int32 step); + IXLRangeColumn ColumnRight(); + IXLRangeColumn ColumnRight(Int32 step); IXLColumn WorksheetColumn(); IXLTable AsTable(); + IXLTable AsTable(String name); + IXLTable CreateTable(); + IXLTable CreateTable(String name); /// /// Clears the contents of this column. /// /// Specify what you want to clear. - new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLRangeColumn ColumnUsed(Boolean includeFormats = false); } } - diff --git a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index 89c820e..01395ab 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -3,9 +3,8 @@ namespace ClosedXML.Excel { - public interface IXLRangeColumns: IEnumerable, IDisposable + public interface IXLRangeColumns : IEnumerable, IDisposable { - /// /// Adds a column range to this group. /// @@ -16,7 +15,7 @@ /// Returns the collection of cells. /// IXLCells Cells(); - + /// /// Returns the collection of cells that have a value. /// @@ -41,7 +40,7 @@ /// Clears the contents of these columns. /// /// Specify what you want to clear. - IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/Excel/Ranges/IXLRangeRow.cs index 7c34ad3..499b680 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -1,9 +1,8 @@ using System; - namespace ClosedXML.Excel { - public interface IXLRangeRow: IXLRangeBase + public interface IXLRangeRow : IXLRangeBase { /// /// Gets the cell in the specified column. @@ -23,12 +22,14 @@ /// /// The row's cells to return. new IXLCells Cells(String cellsInRow); + /// /// Returns the specified group of cells. /// /// The first column in the group of cells to return. /// The last column in the group of cells to return. IXLCells Cells(Int32 firstColumn, Int32 lastColumn); + /// /// Returns the specified group of cells. /// @@ -42,33 +43,41 @@ /// /// Number of cells to insert. IXLCells InsertCellsAfter(int numberOfColumns); + IXLCells InsertCellsAfter(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of cells to the left of this row. /// This row and all cells to the right of it will be shifted X number of columns. /// /// Number of cells to insert. IXLCells InsertCellsBefore(int numberOfColumns); + IXLCells InsertCellsBefore(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of rows on top of this row. /// This row and all cells below it will be shifted X number of rows. /// /// Number of rows to insert. IXLRangeRows InsertRowsAbove(int numberOfRows); + IXLRangeRows InsertRowsAbove(int numberOfRows, Boolean expandRange); + /// /// Inserts X number of rows below this row. /// All cells below this row will be shifted X number of rows. /// /// Number of rows to insert. IXLRangeRows InsertRowsBelow(int numberOfRows); + IXLRangeRows InsertRowsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells below. /// void Delete(); + /// /// Deletes this range and shifts the surrounding cells accordingly. /// @@ -83,20 +92,27 @@ Int32 CellCount(); IXLRangeRow CopyTo(IXLCell target); + IXLRangeRow CopyTo(IXLRangeBase target); IXLRangeRow Sort(); + IXLRangeRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeRow Row(Int32 start, Int32 end); + IXLRangeRow Row(IXLCell start, IXLCell end); + IXLRangeRows Rows(String rows); IXLRangeRow SetDataType(XLDataType dataType); IXLRangeRow RowAbove(); + IXLRangeRow RowAbove(Int32 step); + IXLRangeRow RowBelow(); + IXLRangeRow RowBelow(Int32 step); IXLRow WorksheetRow(); @@ -105,9 +121,8 @@ /// Clears the contents of this row. /// /// Specify what you want to clear. - new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLRangeRow RowUsed(Boolean includeFormats = false); } } - diff --git a/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/Excel/Ranges/IXLRangeRows.cs index cc3e3df..3934c12 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -15,7 +15,7 @@ /// Returns the collection of cells. /// IXLCells Cells(); - + /// /// Returns the collection of cells that have a value. /// @@ -40,7 +40,7 @@ /// Clears the contents of these rows. /// /// Specify what you want to clear. - IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/Excel/Ranges/IXLRanges.cs index f767b18..cc6cdab 100644 --- a/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRanges: IEnumerable, IDisposable + public interface IXLRanges : IEnumerable, IDisposable { /// /// Adds the specified range to this group. @@ -28,7 +28,7 @@ IXLDataValidation SetDataValidation(); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// The default scope for the named range is Workbook. /// @@ -36,7 +36,7 @@ IXLRanges AddToNamed(String rangeName); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// Name of the range. /// The scope for the named range. @@ -44,7 +44,7 @@ IXLRanges AddToNamed(String rangeName, XLScope scope); /// - /// Creates a named range out of these ranges. + /// Creates a named range out of these ranges. /// If the named range exists, it will add these ranges to that named range. /// Name of the range. /// The scope for the named range. @@ -88,7 +88,7 @@ /// Clears the contents of these ranges. /// /// Specify what you want to clear. - IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 4a19fcc..a133105 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -815,7 +815,7 @@ ^ Worksheet.GetHashCode(); } - public new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 177677e..d900347 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -84,7 +84,7 @@ { get { - if (IsInvalid) + if (!IsValid) throw new InvalidOperationException("Range is invalid."); return _firstAddress; @@ -96,7 +96,7 @@ { get { - if (IsInvalid) + if (!IsValid) throw new InvalidOperationException("Range is an invalid state."); return _lastAddress; @@ -123,7 +123,7 @@ set { LastAddress = value as XLAddress; } } - public bool IsInvalid { get; set; } + public bool IsValid { get; set; } = true; #endregion Public properties diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index a3f0a70..84948c3 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1,6 +1,7 @@ using ClosedXML.Excel.Misc; using ClosedXML.Extensions; using System; +using System.Collections; using System.Collections.Generic; using System.Globalization; using System.Linq; @@ -285,6 +286,20 @@ return Cells(true); } + /// + /// Return the collection of cell values not initializing empty cells. + /// + public IEnumerable CellValues() + { + for (int ro = RangeAddress.FirstAddress.RowNumber; ro <= RangeAddress.LastAddress.RowNumber; ro++) + { + for (int co = RangeAddress.FirstAddress.ColumnNumber; co <= RangeAddress.LastAddress.ColumnNumber; co++) + { + yield return Worksheet.GetCellValue(ro, co); + } + } + } + public IXLRange Merge() { return Merge(true); @@ -322,13 +337,15 @@ return asRange; } - public IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.All) { - var includeFormats = clearOptions == XLClearOptions.Formats || - clearOptions == XLClearOptions.ContentsAndFormats; + var includeFormats = clearOptions.HasFlag(XLClearOptions.NormalFormats) || + clearOptions.HasFlag(XLClearOptions.ConditionalFormats); + foreach (var cell in CellsUsed(includeFormats)) { - (cell as XLCell).Clear(clearOptions, true); + // We'll clear the conditional formatting later down. + (cell as XLCell).Clear(clearOptions & ~XLClearOptions.ConditionalFormats, true); } if (includeFormats) @@ -336,18 +353,71 @@ ClearMerged(); } - if (clearOptions == XLClearOptions.ContentsAndFormats) + if (clearOptions.HasFlag(XLClearOptions.ConditionalFormats)) + RemoveConditionalFormatting(); + + if (clearOptions == XLClearOptions.All) { Worksheet.Internals.CellsCollection.RemoveAll( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber - ); + ); } return this; } + internal void RemoveConditionalFormatting() + { + var mf = RangeAddress.FirstAddress; + var ml = RangeAddress.LastAddress; + foreach (var format in Worksheet.ConditionalFormats.Where(x => x.Range.Intersects(this)).ToList()) + { + var f = format.Range.RangeAddress.FirstAddress; + var l = format.Range.RangeAddress.LastAddress; + bool byWidth = false, byHeight = false; + XLRange rng1 = null, rng2 = null; + if (mf.ColumnNumber <= f.ColumnNumber && ml.ColumnNumber >= l.ColumnNumber) + { + if (mf.RowNumber.Between(f.RowNumber, l.RowNumber) || ml.RowNumber.Between(f.RowNumber, l.RowNumber)) + { + if (mf.RowNumber > f.RowNumber) + rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, mf.RowNumber - 1, l.ColumnNumber); + if (ml.RowNumber < l.RowNumber) + rng2 = Worksheet.Range(ml.RowNumber + 1, f.ColumnNumber, l.RowNumber, l.ColumnNumber); + } + byWidth = true; + } + + if (mf.RowNumber <= f.RowNumber && ml.RowNumber >= l.RowNumber) + { + if (mf.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber) || ml.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber)) + { + if (mf.ColumnNumber > f.ColumnNumber) + rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, l.RowNumber, mf.ColumnNumber - 1); + if (ml.ColumnNumber < l.ColumnNumber) + rng2 = Worksheet.Range(f.RowNumber, ml.ColumnNumber + 1, l.RowNumber, l.ColumnNumber); + } + byHeight = true; + } + + if (rng1 != null) + { + format.Range = rng1; + } + if (rng2 != null) + { + //TODO: reflect the formula for a new range + if (rng1 == null) + format.Range = rng2; + else + ((XLConditionalFormat)rng2.AddConditionalFormat()).CopyFrom(format); + } + if (byWidth && byHeight) Worksheet.ConditionalFormats.Remove(x => x == format); + } + } + public void DeleteComments() { Cells().DeleteComments(); @@ -388,7 +458,7 @@ public bool Intersects(IXLRangeBase range) { - if (range.RangeAddress.IsInvalid || RangeAddress.IsInvalid) + if (!range.RangeAddress.IsValid || !RangeAddress.IsValid) return false; var ma = range.RangeAddress; var ra = RangeAddress; @@ -1509,7 +1579,7 @@ protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) { - if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; + if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return; bool allRowsAreCovered = thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber; @@ -1539,7 +1609,7 @@ if (destroyedByShift) { - thisRangeAddress.IsInvalid = true; + (thisRangeAddress as XLRangeAddress).IsValid = false; return; } @@ -1560,7 +1630,7 @@ protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { - if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; + if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return; bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; @@ -1590,7 +1660,7 @@ if (destroyedByShift) { - thisRangeAddress.IsInvalid = true; + (thisRangeAddress as XLRangeAddress).IsValid = false; return; } diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index ae85bf2..08103f0 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -371,7 +371,7 @@ return asRange.CreateTable(name); } - public new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; diff --git a/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/Excel/Ranges/XLRangeColumns.cs index f2481ed..5e5fffb 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -19,7 +19,7 @@ #region IXLRangeColumns Members - public IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.All) { _ranges.ForEach(c => c.Clear(clearOptions)); return this; @@ -89,7 +89,7 @@ return this; } - #endregion + #endregion IXLRangeColumns Members #region IXLStylized Members @@ -131,7 +131,7 @@ } } - #endregion + #endregion IXLStylized Members public void Dispose() { @@ -145,4 +145,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index aeb54e1..6b1b83e 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -350,7 +350,7 @@ #endregion XLRangeRow Below - public new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; diff --git a/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/Excel/Ranges/XLRangeRows.cs index bbd51f8..6fb9fcc 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -19,7 +19,7 @@ #region IXLRangeRows Members - public IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.All) { _ranges.ForEach(c => c.Clear(clearOptions)); return this; @@ -39,7 +39,7 @@ public IEnumerator GetEnumerator() { return _ranges.Cast() - .OrderBy(r=>r.Worksheet.Position) + .OrderBy(r => r.Worksheet.Position) .ThenBy(r => r.RowNumber()) .GetEnumerator(); } @@ -89,7 +89,7 @@ return this; } - #endregion + #endregion IXLRangeRows Members #region IXLStylized Members @@ -131,7 +131,7 @@ } } - #endregion + #endregion IXLStylized Members public void Dispose() { @@ -144,6 +144,5 @@ foreach (var range in this) range.Select(); } - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index 501a50c..fcca3f6 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -18,7 +18,7 @@ #region IXLRanges Members - public IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.All) { _ranges.ForEach(c => c.Clear(clearOptions)); return this; @@ -74,12 +74,12 @@ public Boolean Contains(IXLCell cell) { - return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(cell)); + return _ranges.Any(r => r.RangeAddress.IsValid && r.Contains(cell)); } public Boolean Contains(IXLRange range) { - return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range)); + return _ranges.Any(r => r.RangeAddress.IsValid && r.Contains(range)); } public IEnumerable DataValidation @@ -149,7 +149,7 @@ _ranges.ForEach(r => r.Dispose()); } - #endregion + #endregion IXLRanges Members #region IXLStylized Members @@ -188,7 +188,7 @@ get { return this; } } - #endregion + #endregion IXLStylized Members public override string ToString() { diff --git a/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/Excel/Rows/IXLRow.cs index a89ddda..e3fae6c 100644 --- a/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/Excel/Rows/IXLRow.cs @@ -4,8 +4,6 @@ { public interface IXLRow : IXLRangeBase { - - /// /// Gets or sets the height of this row. /// @@ -50,6 +48,7 @@ /// /// The column to start calculating the row height. IXLRow AdjustToContents(Int32 startColumn); + /// /// Adjusts the height of the row based on its contents, starting from the startColumn and ending at endColumn. /// @@ -57,9 +56,10 @@ /// The column to end calculating the row height. IXLRow AdjustToContents(Int32 startColumn, Int32 endColumn); - IXLRow AdjustToContents(Double minHeight, Double maxHeight); + IXLRow AdjustToContents(Int32 startColumn, Double minHeight, Double maxHeight); + IXLRow AdjustToContents(Int32 startColumn, Int32 endColumn, Double minHeight, Double maxHeight); /// Hides this row. @@ -113,7 +113,6 @@ /// IXLRow Ungroup(); - /// /// Adds this row to the previous outline level (decrements the outline level for this row by 1). /// @@ -143,12 +142,14 @@ /// /// The row's cells to return. new IXLCells Cells(String cellsInRow); + /// /// Returns the specified group of cells. /// /// The first column in the group of cells to return. /// The last column in the group of cells to return. IXLCells Cells(Int32 firstColumn, Int32 lastColumn); + /// /// Returns the specified group of cells. /// @@ -162,14 +163,19 @@ Int32 CellCount(); IXLRangeRow CopyTo(IXLCell cell); + IXLRangeRow CopyTo(IXLRangeBase range); + IXLRow CopyTo(IXLRow row); IXLRow Sort(); + IXLRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeRow Row(Int32 start, Int32 end); + IXLRangeRow Row(IXLCell start, IXLCell end); + IXLRangeRows Rows(String columns); /// @@ -180,15 +186,18 @@ IXLRow SetDataType(XLDataType dataType); IXLRow RowAbove(); + IXLRow RowAbove(Int32 step); + IXLRow RowBelow(); + IXLRow RowBelow(Int32 step); /// /// Clears the contents of this row. /// /// Specify what you want to clear. - new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLRangeRow RowUsed(Boolean includeFormats = false); } diff --git a/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/Excel/Rows/IXLRows.cs index 5f4c6bc..376a373 100644 --- a/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/Excel/Rows/IXLRows.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRows: IEnumerable, IDisposable + public interface IXLRows : IEnumerable, IDisposable { /// /// Sets the height of all rows. @@ -22,11 +22,13 @@ /// Adjusts the height of all rows based on its contents. /// IXLRows AdjustToContents(); + /// /// Adjusts the height of all rows based on its contents, starting from the startColumn. /// /// The column to start calculating the row height. IXLRows AdjustToContents(Int32 startColumn); + /// /// Adjusts the height of all rows based on its contents, starting from the startColumn and ending at endColumn. /// @@ -35,7 +37,9 @@ IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn); IXLRows AdjustToContents(Double minHeight, Double maxHeight); + IXLRows AdjustToContents(Int32 startColumn, Double minHeight, Double maxHeight); + IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn, Double minHeight, Double maxHeight); /// @@ -93,7 +97,7 @@ /// Returns the collection of cells. /// IXLCells Cells(); - + /// /// Returns the collection of cells that have a value. /// @@ -118,7 +122,7 @@ /// Clears the contents of these rows. /// /// Specify what you want to clear. - IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index 145e37e..697801e 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -213,7 +213,7 @@ return Worksheet.Rows(rowNum, rowNum + numberOfRows - 1); } - public new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; @@ -598,16 +598,19 @@ private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - if (range.RangeAddress.FirstAddress.RowNumber <= RowNumber()) + if (range.RangeAddress.IsValid && + RangeAddress.IsValid && + range.RangeAddress.FirstAddress.RowNumber <= RowNumber()) SetRowNumber(RowNumber() + rowsShifted); } private void SetRowNumber(Int32 row) { if (row <= 0) - RangeAddress.IsInvalid = false; + RangeAddress.IsValid = false; else { + RangeAddress.IsValid = true; RangeAddress.FirstAddress = new XLAddress(Worksheet, row, 1, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); RangeAddress.LastAddress = new XLAddress(Worksheet, diff --git a/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/Excel/Rows/XLRows.cs index 27583d1..1853bd5 100644 --- a/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/Excel/Rows/XLRows.cs @@ -23,7 +23,7 @@ public IEnumerator GetEnumerator() { - return _rows.Cast().OrderBy(r=>r.RowNumber()).GetEnumerator(); + return _rows.Cast().OrderBy(r => r.RowNumber()).GetEnumerator(); } IEnumerator IEnumerable.GetEnumerator() @@ -121,7 +121,6 @@ return this; } - public void Hide() { _rows.ForEach(r => r.Hide()); @@ -209,7 +208,7 @@ return this; } - #endregion + #endregion IXLRows Members #region IXLStylized Members @@ -250,14 +249,14 @@ } } - #endregion + #endregion IXLStylized Members public void Add(XLRow row) { _rows.Add(row); } - public IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.All) { _rows.ForEach(c => c.Clear(clearOptions)); return this; @@ -275,4 +274,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 9a6fa97..1553973 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -23,7 +23,7 @@ /// Clears the contents of this table. /// /// Specify what you want to clear. - new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.All); IXLTableField Field(string fieldName); diff --git a/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/Excel/Tables/IXLTableRow.cs index 9f467c5..5722624 100644 --- a/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -2,26 +2,32 @@ namespace ClosedXML.Excel { - public interface IXLTableRow: IXLRangeRow + public interface IXLTableRow : IXLRangeRow { IXLCell Field(Int32 index); + IXLCell Field(String name); new IXLTableRow Sort(); + new IXLTableRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); new IXLTableRow RowAbove(); + new IXLTableRow RowAbove(Int32 step); + new IXLTableRow RowBelow(); + new IXLTableRow RowBelow(Int32 step); /// /// Clears the contents of this row. /// /// Specify what you want to clear. - new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.All); new IXLTableRows InsertRowsAbove(int numberOfRows); + new IXLTableRows InsertRowsBelow(int numberOfRows); } } diff --git a/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/Excel/Tables/IXLTableRows.cs index fef29b6..5e10d8a 100644 --- a/ClosedXML/Excel/Tables/IXLTableRows.cs +++ b/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLTableRows: IEnumerable + public interface IXLTableRows : IEnumerable { /// /// Adds a table row to this group. @@ -33,7 +33,7 @@ /// Clears the contents of these rows. /// /// Specify what you want to clear. - IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.All); void Select(); } diff --git a/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/Excel/Tables/IXLTables.cs index 299ec51..cf864d3 100644 --- a/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/Excel/Tables/IXLTables.cs @@ -3,19 +3,22 @@ namespace ClosedXML.Excel { - public interface IXLTables: IEnumerable + public interface IXLTables : IEnumerable { void Add(IXLTable table); + IXLTable Table(Int32 index); + IXLTable Table(String name); /// /// Clears the contents of these tables. /// /// Specify what you want to clear. - IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.All); void Remove(Int32 index); + void Remove(String name); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index cd8afe7..d42ddf1 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -518,7 +518,7 @@ return DataRange.Sort(toSortBy.ToString(), sortOrder, matchCase, ignoreBlanks); } - public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; diff --git a/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/Excel/Tables/XLTableRow.cs index 2420477..5d96e00 100644 --- a/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/Excel/Tables/XLTableRow.cs @@ -36,7 +36,7 @@ return this; } - #endregion + #endregion IXLTableRow Members private XLTableRow RowShift(Int32 rowsToShift) { @@ -65,7 +65,7 @@ return RowShift(step * -1); } - #endregion + #endregion XLTableRow Above #region XLTableRow Below @@ -89,9 +89,9 @@ return RowShift(step); } - #endregion + #endregion XLTableRow Below - public new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; @@ -101,6 +101,7 @@ { return XLHelper.InsertRowsWithoutEvents(base.InsertRowsAbove, _tableRange, numberOfRows, !_tableRange.Table.ShowTotalsRow); } + public new IXLTableRows InsertRowsBelow(int numberOfRows) { return XLHelper.InsertRowsWithoutEvents(base.InsertRowsBelow, _tableRange, numberOfRows, !_tableRange.Table.ShowTotalsRow); @@ -112,4 +113,4 @@ _tableRange.Table.ExpandTableRows(-1); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/Excel/Tables/XLTableRows.cs index 71cc081..bb17b88 100644 --- a/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/Excel/Tables/XLTableRows.cs @@ -10,7 +10,6 @@ public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); private IXLStyle _style; - public XLTableRows(IXLStyle defaultStyle) { @@ -57,11 +56,11 @@ } } - #endregion + #endregion IXLStylized Members #region IXLTableRows Members - public IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.All) { _ranges.ForEach(r => r.Clear(clearOptions)); return this; @@ -118,7 +117,7 @@ return cells; } - #endregion + #endregion IXLTableRows Members public void Select() { @@ -126,4 +125,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs index 76e9185..492b7dd 100644 --- a/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/Excel/Tables/XLTables.cs @@ -46,7 +46,7 @@ #endregion IXLTables Members - public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.All) { _tables.Values.ForEach(t => t.Clear(clearOptions)); return this; diff --git a/ClosedXML/Excel/XLClearOptions.cs b/ClosedXML/Excel/XLClearOptions.cs new file mode 100644 index 0000000..0de7700 --- /dev/null +++ b/ClosedXML/Excel/XLClearOptions.cs @@ -0,0 +1,18 @@ +using System; + +namespace ClosedXML.Excel +{ + [Flags] + public enum XLClearOptions + { + Contents = 1 << 0, + DataType = 1 << 1, + NormalFormats = 1 << 2, + ConditionalFormats = 1 << 3, + Comments = 1 << 4, + DataValidation = 1 << 5, + + AllFormats = NormalFormats | ConditionalFormats, + All = Contents | DataType | NormalFormats | ConditionalFormats | Comments | DataValidation + } +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 096e459..a322ce2 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -268,6 +268,8 @@ reader.Close(); } + (ws.ConditionalFormats as XLConditionalFormats).ReorderAccordingToOriginalPriority(); + #region LoadTables foreach (var tableDefinitionPart in worksheetPart.TableDefinitionParts) @@ -2137,6 +2139,7 @@ // The conditional formatting type is compulsory. If it doesn't exist, skip the entire rule. if (fr.Type == null) continue; conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); + conditionalFormat.OriginalPriority = fr.Priority?.Value ?? Int32.MaxValue; if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index d3e1134..d39a886 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -3396,6 +3396,11 @@ AddDifferentialFormats(workbookStylesPart, context); } + /// + /// Populates the differential formats that are currently in the file to the SaveContext + /// + /// The workbook styles part. + /// The context. private void AddDifferentialFormats(WorkbookStylesPart workbookStylesPart, SaveContext context) { if (workbookStylesPart.Stylesheet.DifferentialFormats == null) @@ -3463,7 +3468,11 @@ SaveContext context) { var differentialFormat = new DifferentialFormat(); - differentialFormat.Append(GetNewFont(new FontInfo { Font = cf.Style.Font as XLFont }, false)); + + var diffFont = GetNewFont(new FontInfo { Font = cf.Style.Font as XLFont }, false); + if (diffFont?.HasChildren ?? false) + differentialFormat.Append(diffFont); + if (!XLHelper.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) { var numberFormat = new NumberingFormat @@ -3473,8 +3482,14 @@ }; differentialFormat.Append(numberFormat); } - differentialFormat.Append(GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false)); - differentialFormat.Append(GetNewBorder(new BorderInfo { Border = cf.Style.Border as XLBorder }, false)); + + var diffFill = GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false); + if (diffFill?.HasChildren ?? false) + differentialFormat.Append(diffFill); + + var diffBorder = GetNewBorder(new BorderInfo { Border = cf.Style.Border as XLBorder }, false); + if (diffBorder?.HasChildren ?? false) + differentialFormat.Append(diffBorder); differentialFormats.Append(differentialFormat); @@ -3485,7 +3500,11 @@ SaveContext context) { var differentialFormat = new DifferentialFormat(); - differentialFormat.Append(GetNewFont(new FontInfo { Font = style.Font as XLFont }, false)); + + var diffFont = GetNewFont(new FontInfo { Font = style.Font as XLFont }, false); + if (diffFont?.HasChildren ?? false) + differentialFormat.Append(diffFont); + if (!XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format) || style.NumberFormat.NumberFormatId != 0) { var numberFormat = new NumberingFormat(); @@ -3509,8 +3528,14 @@ differentialFormat.Append(numberFormat); } - differentialFormat.Append(GetNewFill(new FillInfo { Fill = style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false)); - differentialFormat.Append(GetNewBorder(new BorderInfo { Border = style.Border as XLBorder }, false)); + + var diffFill = GetNewFill(new FillInfo { Fill = style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false); + if (diffFill?.HasChildren ?? false) + differentialFormat.Append(diffFill); + + var diffBorder = GetNewBorder(new BorderInfo { Border = style.Border as XLBorder }, false); + if (diffBorder?.HasChildren ?? false) + differentialFormat.Append(diffBorder); differentialFormats.Append(differentialFormat); @@ -3915,7 +3940,9 @@ break; case XLColorType.Indexed: - backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; + // 64 is 'transparent' and should be ignored for differential formats + if (fillInfo.Fill.BackgroundColor.Indexed != 64) + backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; break; case XLColorType.Theme: @@ -4992,8 +5019,9 @@ worksheetPart.Worksheet.RemoveAllChildren(); var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); - var priority = 1; // priority is 1 origin in Microsoft Excel - foreach (var cfGroup in xlWorksheet.ConditionalFormats + var conditionalFormats = xlWorksheet.ConditionalFormats.ToList(); // Required for IndexOf method + + foreach (var cfGroup in conditionalFormats .GroupBy( c => c.Range.RangeAddress.ToStringRelative(false), c => c, @@ -5008,8 +5036,8 @@ }; foreach (var cf in cfGroup.CfList) { + var priority = conditionalFormats.IndexOf(cf) + 1; conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context)); - priority++; } worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 08aecef..9d2ba32 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1231,7 +1231,7 @@ cfAddress.LastAddress.RowNumber, cfAddress.LastAddress.ColumnNumber + columnsShifted); } - if (cf.Range.RangeAddress.IsInvalid || + if (!cf.Range.RangeAddress.IsValid || cf.Range.RangeAddress.FirstAddress.ColumnNumber > cf.Range.RangeAddress.LastAddress.ColumnNumber) ConditionalFormats.Remove(f => f == cf); } @@ -1305,7 +1305,7 @@ cfAddress.LastAddress.RowNumber + rowsShifted, cfAddress.LastAddress.ColumnNumber); } - if (cf.Range.RangeAddress.IsInvalid || + if (!cf.Range.RangeAddress.IsValid || cf.Range.RangeAddress.FirstAddress.RowNumber > cf.Range.RangeAddress.LastAddress.RowNumber) ConditionalFormats.Remove(f => f == cf); } @@ -1567,5 +1567,25 @@ else this.Cell(ro, co).SetValue(value); } + + /// + /// Get a cell value not initializing it if it has not been initialized yet. + /// + /// Row number + /// Column number + /// Current value of the specified cell. Empty string for non-initialized cells. + internal object GetCellValue(int ro, int co) + { + if (Internals.CellsCollection.MaxRowUsed < ro || + Internals.CellsCollection.MaxColumnUsed < co || + !Internals.CellsCollection.Contains(ro, co)) + return string.Empty; + + var cell = Worksheet.Internals.CellsCollection.GetCell(ro, co); + if (cell.IsEvaluating) + return string.Empty; + + return cell.Value; + } } } diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index d485d78..6f25f72 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -175,7 +175,7 @@ public static Boolean IsValidRangeAddress(IXLRangeAddress rangeAddress) { - return !rangeAddress.IsInvalid + return rangeAddress.IsValid && rangeAddress.FirstAddress.RowNumber >= 1 && rangeAddress.LastAddress.RowNumber <= MaxRowNumber && rangeAddress.FirstAddress.ColumnNumber >= 1 && rangeAddress.LastAddress.ColumnNumber <= MaxColumnNumber && rangeAddress.FirstAddress.RowNumber <= rangeAddress.LastAddress.RowNumber diff --git a/ClosedXML_Examples/Ranges/AddingRowToTables.cs b/ClosedXML_Examples/Ranges/AddingRowToTables.cs index 4cfba08..b0a7b65 100644 --- a/ClosedXML_Examples/Ranges/AddingRowToTables.cs +++ b/ClosedXML_Examples/Ranges/AddingRowToTables.cs @@ -1,9 +1,8 @@ +using ClosedXML.Excel; using System; using System.IO; -using ClosedXML.Excel; using System.Linq; - namespace ClosedXML_Examples.Ranges { public class AddingRowToTables : IXLExample @@ -26,7 +25,7 @@ range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) // We want to use a theme for table, not the hard coded format of the BasicTable - range.Clear(XLClearOptions.Formats); + range.Clear(XLClearOptions.AllFormats); // Put back the date and number formats range.Column(4).Style.NumberFormat.NumberFormatId = 15; range.Column(5).Style.NumberFormat.Format = "$ #,##0"; @@ -52,7 +51,6 @@ // Override - - #endregion + #endregion Methods } } diff --git a/ClosedXML_Examples/Tables/UsingTables.cs b/ClosedXML_Examples/Tables/UsingTables.cs index 24f4903..3dd012d 100644 --- a/ClosedXML_Examples/Tables/UsingTables.cs +++ b/ClosedXML_Examples/Tables/UsingTables.cs @@ -25,7 +25,7 @@ range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) // We want to use a theme for table, not the hard coded format of the BasicTable - range.Clear(XLClearOptions.Formats); + range.Clear(XLClearOptions.AllFormats); // Put back the date and number formats range.Column(4).Style.NumberFormat.NumberFormatId = 15; range.Column(5).Style.NumberFormat.Format = "$ #,##0"; diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 6b68014..20d17f0 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -125,7 +125,7 @@ } } } - + [Test] [TestCase("A1:A4")] [TestCase("A1:B4")] @@ -152,7 +152,7 @@ range.InsertColumnsBefore(1); //Assert - Assert.IsFalse(ws.AutoFilter.Range.RangeAddress.IsInvalid); + Assert.IsTrue(ws.AutoFilter.Range.RangeAddress.IsValid); } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index ae8c4cb..781a945 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -379,7 +379,7 @@ actual = XLWorkbook.EvaluateExpr("Round(-50.55, -2)"); Assert.AreEqual(-100.0, actual); - + actual = XLWorkbook.EvaluateExpr("ROUND(59 * 0.535, 2)"); // (59 * 0.535) = 31.565 Assert.AreEqual(31.57, actual); @@ -574,6 +574,22 @@ } [Test] + public void SumDateTimeAndNumber() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Value = 1; + ws.Cell("A2").Value = new DateTime(2018, 1, 1); + Assert.AreEqual(43102, ws.Evaluate("SUM(A1:A2)")); + + ws.Cell("A1").Value = 2; + ws.Cell("A2").FormulaA1 = "DATE(2018,1,1)"; + Assert.AreEqual(43103, ws.Evaluate("SUM(A1:A2)")); + } + } + + [Test] public void SumSq() { Object actual; diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 2bdb25a..b611dec 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -702,7 +702,6 @@ Assert.Throws(() => XLWorkbook.EvaluateExpr(@"CSC(0)")); } - [TestCase("FF", 16, 255)] [TestCase("111", 2, 7)] [TestCase("zap", 36, 45745)] @@ -1152,7 +1151,6 @@ [Test] public void SumIf_ReturnsCorrectValues_WhenFormulaBelongToSameRange() { - using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Data"); @@ -1163,11 +1161,10 @@ new { Id = "C", Value = 2}, new { Id = "A", Value = 1}, new { Id = "B", Value = 4}, - }; ws.Cell("A1").InsertTable(data); ws.Cell("A7").SetValue("Sum A"); - // SUMIF formula + // SUMIF formula var formula = "=SUMIF(A:A,\"=A\",B:B)"; ws.Cell("B7").SetFormulaA1(formula); var value = ws.Cell("B7").Value; @@ -1175,17 +1172,16 @@ } } - /// /// refers to Example 1 to SumIf from the Excel documentation. - /// As SumIfs should behave the same if called with three parameters, we can take that example here again. + /// As SumIfs should behave the same if called with three parameters, but in a different order /// /// /// /// - [TestCase(63000, "SUMIFS(B1:B4, \">160000\", A1:A4)")] - [TestCase(21000, "SUMIFS(B1:B4, 300000, A1:A4)")] - [TestCase(28000, "SUMIFS(B1:B4, \">\" &C1, A1:A4)")] + [TestCase(63000, "SUMIFS(B1:B4, A1:A4, \">160000\")")] + [TestCase(21000, "SUMIFS(B1:B4, A1:A4, 300000)")] + [TestCase(28000, "SUMIFS(B1:B4, A1:A4, \">\" &C1)")] public void SumIfs_ReturnsCorrectValues_ReferenceExampleForSumIf1FromMicrosoft(int expectedOutcome, string formula) { using (var wb = new XLWorkbook()) @@ -1213,13 +1209,13 @@ /// As SumIfs should behave the same if called with three parameters, we can take that example here again. /// /// - /// + /// /// - [TestCase(2000, "SUMIFS(C2:C7, \"Fruits\", A2:A7)")] - [TestCase(12000, "SUMIFS(C2:C7, \"Vegetables\", A2:A7)")] - [TestCase(4300, "SUMIFS(C2:C7, \"*es\", B2:B7)")] - [TestCase(400, "SUMIFS(C2:C7, \"\", A2:A7)")] - public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + [TestCase(2000, "SUMIFS(C2:C7, A2:A7, \"Fruits\")")] + [TestCase(12000, "SUMIFS(C2:C7, A2:A7, \"Vegetables\")")] + [TestCase(4300, "SUMIFS(C2:C7, B2:B7, \"*es\")")] + [TestCase(400, "SUMIFS(C2:C7, A2:A7, \"\")")] + public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedResult, string formula) { using (var wb = new XLWorkbook()) { @@ -1249,7 +1245,8 @@ ws.Cell(1, 3).Value = 300000; - Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + var actualResult = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, actualResult); } } @@ -1260,7 +1257,7 @@ [TestCase(20, "=SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")")] [TestCase(30, "=SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")")] public void SumIfs_ReturnsCorrectValues_ReferenceExampleFromMicrosoft( - int result, + int expectedResult, string formula) { using (var wb = new XLWorkbook()) @@ -1268,41 +1265,53 @@ wb.ReferenceStyle = XLReferenceStyle.A1; var ws = wb.AddWorksheet("Sheet1"); - ws.Cell(1, 1).Value = 5; - ws.Cell(1, 2).Value = "Apples"; - ws.Cell(1, 3).Value = "Tom"; + var row = 2; - ws.Cell(2, 1).Value = 4; - ws.Cell(2, 2).Value = "Apples"; - ws.Cell(2, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 5; + ws.Cell(row, 2).Value = "Apples"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(3, 1).Value = 15; - ws.Cell(3, 2).Value = "Artichokes"; - ws.Cell(3, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 4; + ws.Cell(row, 2).Value = "Apples"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(4, 1).Value = 3; - ws.Cell(4, 2).Value = "Artichokes"; - ws.Cell(4, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 15; + ws.Cell(row, 2).Value = "Artichokes"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(5, 1).Value = 22; - ws.Cell(5, 2).Value = "Bananas"; - ws.Cell(5, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 3; + ws.Cell(row, 2).Value = "Artichokes"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(6, 1).Value = 12; - ws.Cell(6, 2).Value = "Bananas"; - ws.Cell(6, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 22; + ws.Cell(row, 2).Value = "Bananas"; + ws.Cell(row, 3).Value = "Tom"; + row++; - ws.Cell(7, 1).Value = 10; - ws.Cell(7, 2).Value = "Carrots"; - ws.Cell(7, 3).Value = "Tom"; + ws.Cell(row, 1).Value = 12; + ws.Cell(row, 2).Value = "Bananas"; + ws.Cell(row, 3).Value = "Sarah"; + row++; - ws.Cell(8, 1).Value = 33; - ws.Cell(8, 2).Value = "Carrots"; - ws.Cell(8, 3).Value = "Sarah"; + ws.Cell(row, 1).Value = 10; + ws.Cell(row, 2).Value = "Carrots"; + ws.Cell(row, 3).Value = "Tom"; + row++; + + ws.Cell(row, 1).Value = 33; + ws.Cell(row, 2).Value = "Carrots"; + ws.Cell(row, 3).Value = "Sarah"; + + var actualResult = ws.Evaluate(formula).CastTo(); + + Assert.AreEqual(expectedResult, actualResult, tolerance); } } - [Test] public void SumProduct() { @@ -1356,7 +1365,6 @@ { var actual = XLWorkbook.EvaluateExpr(string.Format(@"INT({0})", input.ToString(CultureInfo.InvariantCulture))); Assert.AreEqual(expected, actual); - } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs index 5fa2850..34516fe 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -2,6 +2,8 @@ using NUnit.Framework; using System; using System.Linq; +using ClosedXML.Excel.CalcEngine; +using ClosedXML.Excel.CalcEngine.Exceptions; namespace ClosedXML_Tests.Excel.CalcEngine { @@ -72,8 +74,15 @@ value = ws.Evaluate(@"=COUNTBLANK(D43:D49)").CastTo(); Assert.AreEqual(4, value); - value = workbook.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo(); + value = ws.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo(); Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=COUNTBLANK(A1)").CastTo(); + Assert.AreEqual(1, value); + + Assert.Throws(() => workbook.Evaluate(@"=COUNTBLANK(E3:E45)")); + Assert.Throws(() => ws.Evaluate(@"=COUNTBLANK()")); + Assert.Throws(() => ws.Evaluate(@"=COUNTBLANK(A3:A45,E3:E45)")); } [Test] @@ -167,7 +176,7 @@ ws.Cell(4, 3).Value = "Yes"; ws.Cell(4, 4).Value = "Yes"; - Assert.AreEqual(expectedOutcome, (int)ws.Evaluate(formula)); + Assert.AreEqual(expectedOutcome, ws.Evaluate(formula)); } } @@ -320,6 +329,36 @@ Assert.AreEqual(2189.430863, value, tolerance); } + [Test] + [TestCase("COUNT(G:I,G:G,H:I)", 258d, Description = "COUNT overlapping columns")] + [TestCase("COUNT(6:8,6:6,7:8)", 30d, Description = "COUNT overlapping rows")] + [TestCase("COUNTBLANK(H:J)", 3145640d, Description = "COUNTBLANK columns")] + [TestCase("COUNTBLANK(7:9)", 49128d, Description = "COUNTBLANK rows")] + [TestCase("COUNT(1:1048576)", 216d, Description = "COUNT worksheet")] + [TestCase("COUNTBLANK(1:1048576)", 17179868831d, Description = "COUNTBLANK worksheet")] + [TestCase("SUM(H:J)", 20501.15d, Description = "SUM columns")] + [TestCase("SUM(4:5)", 85366.12d, Description = "SUM rows")] + [TestCase("SUMIF(G:G,50,H:H)", 24.98d, Description = "SUMIF columns")] + [TestCase("SUMIF(G23:G52,\"\",H3:H32)", 53.24d, Description = "SUMIF ranges")] + [TestCase("SUMIFS(H:H,G:G,50,I:I,\">900\")", 19.99d, Description = "SUMIFS columns")] + public void TallySkipsEmptyCells(string formulaA1, double expectedResult) + { + using (var wb = SetupWorkbook()) + { + var ws = wb.Worksheets.First(); + //Let's pre-initialize cells we need so they didn't affect the result + ws.Range("A1:J45").Style.Fill.BackgroundColor = XLColor.Amber; + ws.Cell("ZZ1000").Value = 1; + int initialCount = (ws as XLWorksheet).Internals.CellsCollection.Count; + + var actualResult = (double)ws.Evaluate(formulaA1); + int cellsCount = (ws as XLWorksheet).Internals.CellsCollection.Count; + + Assert.AreEqual(expectedResult, actualResult, tolerance); + Assert.AreEqual(initialCount, cellsCount); + } + } + private XLWorkbook SetupWorkbook() { var wb = new XLWorkbook(); diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 754d158..2173c3e 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -427,6 +427,43 @@ } [Test] + public void CanClearDateTimeCellValue() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var c = ws.FirstCell(); + c.SetValue(new DateTime(2017, 10, 08)); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 08), c.Value); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + var c = ws.FirstCell(); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 08), c.Value); + + c.Clear(); + wb.Save(); + } + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + var c = ws.FirstCell(); + Assert.AreEqual(XLDataType.Text, c.DataType); + Assert.True(c.IsEmpty()); + } + } + } + + [Test] public void CurrentRegion() { // Partially based on sample in https://github.com/ClosedXML/ClosedXML/issues/120 @@ -487,6 +524,7 @@ { Assert.AreEqual("B1:E3", c.CurrentRegion.RangeAddress.ToString()); } + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); //// SECOND REGION @@ -494,6 +532,7 @@ { Assert.AreEqual("F1:H4", c.CurrentRegion.RangeAddress.ToString()); } + Assert.AreEqual("F1:H5", ws.Cell("F5").CurrentRegion.RangeAddress.ToString()); //// DIAGONAL @@ -576,5 +615,37 @@ Assert.AreEqual("B2", cell.FormulaA1); } } + + [Test] + public void FormulaWithCircularReferenceFails() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var A1 = ws.Cell("A1"); + var A2 = ws.Cell("A2"); + A1.FormulaA1 = "A2 + 1"; + A2.FormulaA1 = "A1 + 1"; + + Assert.Throws(() => + { + var _ = A1.Value; + }); + Assert.Throws(() => + { + var _ = A2.Value; + }); + } + } + + public void FormulaWithCircularReferenceFails2() + { + var cell = new XLWorkbook().Worksheets.Add("Sheet1").FirstCell(); + cell.FormulaA1 = "A1"; + Assert.Throws(() => + { + var _ = cell.Value; + }); + } } } diff --git a/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs b/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs new file mode 100644 index 0000000..99e8bb2 --- /dev/null +++ b/ClosedXML_Tests/Excel/Clearing/ClearingTests.cs @@ -0,0 +1,265 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests +{ + [TestFixture] + public class ClearingTests + { + private static XLColor backgroundColor = XLColor.LightBlue; + private static XLColor foregroundColor = XLColor.DarkBrown; + + private IXLWorkbook SetupWorkbook() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); + + var c = ws.FirstCell() + .SetValue("Hello world!"); + + c.Comment.AddText("Some comment"); + + c.Style.Fill.BackgroundColor = backgroundColor; + c.Style.Font.FontColor = foregroundColor; + c.SetDataValidation().Custom("B1"); + + //// + + c = ws.FirstCell() + .CellBelow() + .SetFormulaA1("=LEFT(A1,5)"); + + c.Comment.AddText("Another comment"); + + c.Style.Fill.BackgroundColor = backgroundColor; + c.Style.Font.FontColor = foregroundColor; + + //// + + c = ws.FirstCell() + .CellBelow(2) + .SetValue(new DateTime(2018, 1, 15)); + + c.Comment.AddText("A date"); + + c.Style.Fill.BackgroundColor = backgroundColor; + c.Style.Font.FontColor = foregroundColor; + + ws.Column(1) + .AddConditionalFormat().WhenStartsWith("Hell") + .Fill.SetBackgroundColor(XLColor.Red) + .Border.SetOutsideBorder(XLBorderStyleValues.Thick) + .Border.SetOutsideBorderColor(XLColor.Blue) + .Font.SetBold(); + + 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(false, ws.Cell("A1").HasFormula); + Assert.AreEqual(true, ws.Cell("A2").HasFormula); + Assert.AreEqual(false, ws.Cell("A1").HasFormula); + + foreach (var cell in ws.Range("A1:A3").Cells()) + { + Assert.AreEqual(backgroundColor, cell.Style.Fill.BackgroundColor); + Assert.AreEqual(foregroundColor, cell.Style.Font.FontColor); + Assert.IsTrue(ws.ConditionalFormats.Any()); + Assert.IsTrue(cell.HasComment); + } + + Assert.AreEqual("B1", ws.Cell("A1").DataValidation.Value); + + return wb; + } + + [Test] + public void WorksheetClearAll() + { + using (var wb = SetupWorkbook()) + { + var ws = wb.Worksheets.First(); + + ws.Clear(XLClearOptions.All); + + foreach (var c in ws.Range("A1:A10").Cells()) + { + Assert.IsTrue(c.IsEmpty()); + Assert.AreEqual(XLDataType.Text, c.DataType); + Assert.AreEqual(ws.Style.Fill.BackgroundColor, c.Style.Fill.BackgroundColor); + Assert.AreEqual(ws.Style.Font.FontColor, c.Style.Font.FontColor); + Assert.IsFalse(ws.ConditionalFormats.Any()); + Assert.IsFalse(c.HasComment); + Assert.AreEqual(String.Empty, c.DataValidation.Value); + } + } + } + + [Test] + public void WorksheetClearContents() + { + { + using (var wb = SetupWorkbook()) + { + 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); + } + } + } + + [Test] + public void WorksheetClearDataType() + { + { + using (var wb = SetupWorkbook()) + { + 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); + } + } + } + + [Test] + public void WorksheetClearNormalFormats() + { + { + using (var wb = SetupWorkbook()) + { + 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); + } + } + } + + [Test] + public void WorksheetClearConditionalFormats() + { + { + using (var wb = SetupWorkbook()) + { + 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); + } + } + } + + [Test] + public void WorksheetClearComments() + { + { + using (var wb = SetupWorkbook()) + { + 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); + } + } + } + + [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); + } + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs index 847d91a..1a34b9e 100644 --- a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs +++ b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs @@ -236,5 +236,14 @@ Assert.AreEqual(2, lastCoUsed); } + [Test] + public void NegativeColumnNumberIsInvalid() + { + var ws = new XLWorkbook().AddWorksheet("Sheet1") as XLWorksheet; + + var column = new XLColumn(-1, new XLColumnParameters(ws, 0, false)); + + Assert.IsFalse(column.RangeAddress.IsValid); + } } } diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatTests.cs new file mode 100644 index 0000000..983a5d6 --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatTests.cs @@ -0,0 +1,31 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatTests + { + [Test] + public void MaintainConditionalFormattingOrder() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"StyleReferenceFiles\ConditionalFormattingOrder\inputfile.xlsx"))) + using (var ms = new MemoryStream()) + { + TestHelper.CreateAndCompare(() => + { + var wb = new XLWorkbook(stream); + wb.SaveAs(ms); + return wb; + }, @"StyleReferenceFiles\ConditionalFormattingOrder\ConditionalFormattingOrder.xlsx"); + } + } + + } +} diff --git a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs index 2a966d7..d753cf1 100644 --- a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs +++ b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs @@ -1,12 +1,9 @@ -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System.Linq; namespace ClosedXML_Tests.Excel.DataValidations { - /// - /// Summary description for UnitTest1 - /// [TestFixture] public class DataValidationTests { @@ -23,7 +20,6 @@ cell.SetValue("Value 3"); cell = cell.CellBelow(); cell.SetValue("Value 4"); - cell = cell.CellBelow(); ws.Cell("A1").SetValue("Cell below has Validation Only."); cell = ws.Cell("A2"); @@ -34,19 +30,18 @@ cell.DataValidation.List(ws.Range("$E$1:$E$4")); cell.DataValidation.InputTitle = "Title for B2"; - Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List); - Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4"); - Assert.AreEqual(cell.DataValidation.InputTitle, "Title for B2"); - + Assert.AreEqual(XLAllowedValues.List, cell.DataValidation.AllowedValues); + Assert.AreEqual("'Data Validation Issue'!$E$1:$E$4", cell.DataValidation.Value); + Assert.AreEqual("Title for B2", cell.DataValidation.InputTitle); ws.Cell("C1").SetValue("Cell below has Validation with a message."); cell = ws.Cell("C2"); cell.DataValidation.List(ws.Range("$E$1:$E$4")); cell.DataValidation.InputMessage = "Message for C2"; - Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List); - Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4"); - Assert.AreEqual(cell.DataValidation.InputMessage, "Message for C2"); + Assert.AreEqual(XLAllowedValues.List, cell.DataValidation.AllowedValues); + Assert.AreEqual("'Data Validation Issue'!$E$1:$E$4", cell.DataValidation.Value); + Assert.AreEqual("Message for C2", cell.DataValidation.InputMessage); ws.Cell("D1").SetValue("Cell below has Validation with title and message."); cell = ws.Cell("D2"); @@ -54,10 +49,10 @@ cell.DataValidation.InputTitle = "Title for D2"; cell.DataValidation.InputMessage = "Message for D2"; - Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List); - Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4"); - Assert.AreEqual(cell.DataValidation.InputTitle, "Title for D2"); - Assert.AreEqual(cell.DataValidation.InputMessage, "Message for D2"); + Assert.AreEqual(XLAllowedValues.List, cell.DataValidation.AllowedValues); + Assert.AreEqual("'Data Validation Issue'!$E$1:$E$4", cell.DataValidation.Value); + Assert.AreEqual("Title for D2", cell.DataValidation.InputTitle); + Assert.AreEqual("Message for D2", cell.DataValidation.InputMessage); } [Test] @@ -218,7 +213,7 @@ validation.WholeNumber.Between(0, 100); //Act - ws.Cell("B2").Clear(XLClearOptions.ContentsAndFormats); + ws.Cell("B2").Clear(XLClearOptions.DataValidation); //Assert Assert.IsFalse(ws.Cell("B2").HasDataValidation); diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index 87e90ed..57092e6 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -139,7 +139,7 @@ thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); - Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.IsTrue(thisRange.RangeAddress.IsValid); Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); } } @@ -157,7 +157,7 @@ thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); - Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + Assert.IsFalse(thisRange.RangeAddress.IsValid); } } @@ -192,7 +192,7 @@ thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); - Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.IsTrue(thisRange.RangeAddress.IsValid); Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); } } @@ -209,7 +209,7 @@ thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); - Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + Assert.IsFalse(thisRange.RangeAddress.IsValid); } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 28a7b6f..eff6f11 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -298,5 +298,94 @@ Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); } } + + [Test] + public void ClearConditionalFormattingsWhenRangeAbove1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("B2:E3").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C4:D7", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeAbove2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C3:D3").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C4:D7", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeBelow1() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("B7:E8").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:D6", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeBelow2() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C7:D7").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:D6", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeRowInMiddle() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:D7").AddConditionalFormat(); + ws.Range("C5:E5").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4")); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C6:D7")); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeColumnInMiddle() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("E2:E4").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4")); + Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "F3:G4")); + } + + [Test] + public void ClearConditionalFormattingsWhenRangeContainsFormatWhole() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("B2:G4").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(0, ws.ConditionalFormats.Count()); + } + + [Test] + public void NoClearConditionalFormattingsWhenRangePartiallySuperimposed() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.Range("C3:G4").AddConditionalFormat(); + ws.Range("C2:D3").Clear(XLClearOptions.ConditionalFormats); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + Assert.AreEqual("C3:G4", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative()); + } } } diff --git a/ClosedXML_Tests/Excel/Rows/RowTests.cs b/ClosedXML_Tests/Excel/Rows/RowTests.cs index bb74d4f..6453623 100644 --- a/ClosedXML_Tests/Excel/Rows/RowTests.cs +++ b/ClosedXML_Tests/Excel/Rows/RowTests.cs @@ -253,5 +253,15 @@ ws.Rows(1, 2).Group(); ws.Rows(1, 2).Ungroup(true); } + + [Test] + public void NegativeRowNumberIsInvalid() + { + var ws = new XLWorkbook().AddWorksheet("Sheet1") as XLWorksheet; + + var row = new XLRow(-1, new XLRowParameters(ws, 0, false)); + + Assert.IsFalse(row.RangeAddress.IsValid); + } } } diff --git a/ClosedXML_Tests/Excel/Styles/XLFillTests.cs b/ClosedXML_Tests/Excel/Styles/XLFillTests.cs index 72abc22..87fbb2f 100644 --- a/ClosedXML_Tests/Excel/Styles/XLFillTests.cs +++ b/ClosedXML_Tests/Excel/Styles/XLFillTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using NUnit.Framework; +using System.IO; namespace ClosedXML_Tests.Excel { @@ -12,30 +13,30 @@ [Test] public void BackgroundColorSetsPattern() { - var fill = new XLFill {BackgroundColor = XLColor.Blue}; + var fill = new XLFill { BackgroundColor = XLColor.Blue }; Assert.AreEqual(XLFillPatternValues.Solid, fill.PatternType); } [Test] public void BackgroundNoColorSetsPatternNone() { - var fill = new XLFill {BackgroundColor = XLColor.NoColor}; + var fill = new XLFill { BackgroundColor = XLColor.NoColor }; Assert.AreEqual(XLFillPatternValues.None, fill.PatternType); } [Test] public void BackgroundPatternEqualCheck() { - var fill1 = new XLFill {BackgroundColor = XLColor.Blue}; - var fill2 = new XLFill {BackgroundColor = XLColor.Blue}; + var fill1 = new XLFill { BackgroundColor = XLColor.Blue }; + var fill2 = new XLFill { BackgroundColor = XLColor.Blue }; Assert.IsTrue(fill1.Equals(fill2)); } [Test] public void BackgroundPatternNotEqualCheck() { - var fill1 = new XLFill {BackgroundColor = XLColor.Blue}; - var fill2 = new XLFill {BackgroundColor = XLColor.Red}; + var fill1 = new XLFill { BackgroundColor = XLColor.Blue }; + var fill2 = new XLFill { BackgroundColor = XLColor.Red }; Assert.IsFalse(fill1.Equals(fill2)); } @@ -61,5 +62,20 @@ Assert.AreEqual(style.Border.LeftBorderColor, XLColor.Blue); Assert.AreEqual(style.Border.RightBorderColor, XLColor.Blue); } + + [Test] + public void LoadAndSaveTransparentBackgroundFill() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"StyleReferenceFiles\TransparentBackgroundFill\inputfile.xlsx"))) + using (var ms = new MemoryStream()) + { + TestHelper.CreateAndCompare(() => + { + var wb = new XLWorkbook(stream); + wb.SaveAs(ms); + return wb; + }, @"StyleReferenceFiles\TransparentBackgroundFill\TransparentBackgroundFill.xlsx"); + } + } } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 3a41da8..f87f008 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -110,6 +110,11 @@ Assert.AreEqual(2, ws.MergedRanges.Count); Assert.AreEqual("A1:B2", ws.MergedRanges.First().RangeAddress.ToStringRelative()); Assert.AreEqual("D2:E2", ws.MergedRanges.Last().RangeAddress.ToStringRelative()); + + Assert.AreEqual("A1:B2", ws.Cell("A2").MergedRange().RangeAddress.ToStringRelative()); + Assert.AreEqual("D2:E2", ws.Cell("D2").MergedRange().RangeAddress.ToStringRelative()); + + Assert.AreEqual(null, ws.Cell("Z10").MergedRange()); } [Test] diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx index a0c3791..8e4adf1 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx index 6ec8da4..94f5caa 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx index bfb06cd..80d08b7 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx index bbfa6d4..13f828d 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx index 4b6a481..8a25637 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx index 84f5931..e6fa5ec 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx index 0677026..ad1c538 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx index d396c20..565af0c 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx index f2a8f5b..8861be4 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx index 22026b6..8656701 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx index 4eabc7c..6e23b6a 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx index 72e5631..d39adb0 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx index c808164..1ca0131 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/ConditionalFormattingOrder.xlsx b/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/ConditionalFormattingOrder.xlsx new file mode 100644 index 0000000..ed07359 --- /dev/null +++ b/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/ConditionalFormattingOrder.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/inputfile.xlsx b/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/inputfile.xlsx new file mode 100644 index 0000000..28a7fcd --- /dev/null +++ b/ClosedXML_Tests/Resource/StyleReferenceFiles/ConditionalFormattingOrder/inputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx b/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx new file mode 100644 index 0000000..bccd5b2 --- /dev/null +++ b/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/inputfile.xlsx b/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/inputfile.xlsx new file mode 100644 index 0000000..6d6f1a8 --- /dev/null +++ b/ClosedXML_Tests/Resource/StyleReferenceFiles/TransparentBackgroundFill/inputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index 027106d..220a7b7 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -23,16 +23,17 @@ { get { - return Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); + return Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "Generated"); } } public const string ActualTestResultPostFix = ""; - public static readonly string TestsExampleOutputDirectory = Path.Combine(TestsOutputDirectory, "Examples"); + public static readonly string ExampleTestsOutputDirectory = Path.Combine(TestsOutputDirectory, "Examples"); + public static readonly string OtherTestsOutputDirectory = Path.Combine(TestsOutputDirectory, "Other"); private const bool CompareWithResources = true; - private static readonly ResourceFileExtractor _extractor = new ResourceFileExtractor(null, ".Resource.Examples."); + private static readonly ResourceFileExtractor _extractor = new ResourceFileExtractor(null, ".Resource."); public static void SaveWorkbook(XLWorkbook workbook, params string[] fileNameParts) { @@ -47,10 +48,14 @@ { get { +#if _NETFRAMEWORK_ int p = (int)Environment.OSVersion.Platform; return ((p == 4) || (p == 6) || (p == 128)); - } +#else + return true; +#endif } + } public static void RunTestExample(string filePartName, bool evaluateFormulae = false) where T : IXLExample, new() @@ -60,7 +65,7 @@ var example = new T(); string[] pathParts = filePartName.Split(new char[] { '\\' }); - string filePath1 = Path.Combine(new List() { TestsExampleOutputDirectory }.Concat(pathParts).ToArray()); + string filePath1 = Path.Combine(new List() { ExampleTestsOutputDirectory }.Concat(pathParts).ToArray()); var extension = Path.GetExtension(filePath1); var directory = Path.GetDirectoryName(filePath1); @@ -76,28 +81,60 @@ using (var wb = new XLWorkbook(filePath1)) wb.SaveAs(filePath2, true, evaluateFormulae); - if (CompareWithResources) - + if (CompareWithResources) + { + string resourcePath = "Examples." + filePartName.Replace('\\', '.').TrimStart('.'); + using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) + using (var streamActual = File.OpenRead(filePath2)) { - string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); - using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) - using (var streamActual = File.OpenRead(filePath2)) - { - string message; -#if _NETFRAMEWORK_ - var success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); -#else - var success = ExcelDocsComparer.Compare(streamActual, streamExpected, true, out message); -#endif - var formattedMessage = - String.Format( - "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", - filePath2, resourcePath, message); + string message; + var success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); + var formattedMessage = + String.Format( + "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", + filePath2, resourcePath, message); - Assert.IsTrue(success, formattedMessage); - } + Assert.IsTrue(success, formattedMessage); } } + } + + public static void CreateAndCompare(Func workbookGenerator, string referenceResource, bool evaluateFormulae = false) + { + Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); + + string[] pathParts = referenceResource.Split(new char[] { '\\' }); + string filePath1 = Path.Combine(new List() { OtherTestsOutputDirectory }.Concat(pathParts).ToArray()); + + var extension = Path.GetExtension(filePath1); + var directory = Path.GetDirectoryName(filePath1); + + var fileName = Path.GetFileNameWithoutExtension(filePath1); + fileName += ActualTestResultPostFix; + fileName = Path.ChangeExtension(fileName, extension); + + var filePath2 = Path.Combine(directory, fileName); + + using (var wb = workbookGenerator.Invoke()) + wb.SaveAs(filePath2, true, evaluateFormulae); + + if (CompareWithResources) + { + string resourcePath = referenceResource.Replace('\\', '.').TrimStart('.'); + using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) + using (var streamActual = File.OpenRead(filePath2)) + { + string message; + var success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); + var formattedMessage = + String.Format( + "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", + filePath2, resourcePath, message); + + Assert.IsTrue(success, formattedMessage); + } + } + } public static string GetResourcePath(string filePartName) { diff --git a/ClosedXML_Tests/Utils/PackageHelper.cs b/ClosedXML_Tests/Utils/PackageHelper.cs index 3d20488..df9a851 100644 --- a/ClosedXML_Tests/Utils/PackageHelper.cs +++ b/ClosedXML_Tests/Utils/PackageHelper.cs @@ -90,7 +90,7 @@ throw new ArgumentNullException("dest"); } - #endregion + #endregion Check if (dest.PartExists(uri)) { @@ -131,7 +131,7 @@ throw new ArgumentNullException("serializeAction"); } - #endregion + #endregion Check if (package.PartExists(descriptor.Uri)) { @@ -161,7 +161,7 @@ throw new ArgumentNullException("serializeAction"); } - #endregion + #endregion Check if (package.PartExists(descriptor.Uri)) { @@ -191,7 +191,7 @@ throw new ArgumentNullException("deserializeFunc"); } - #endregion + #endregion Check if (!package.PartExists(uri)) { @@ -221,7 +221,7 @@ throw new ArgumentNullException("deserializeAction"); } - #endregion + #endregion Check if (!package.PartExists(uri)) { @@ -251,7 +251,7 @@ throw new ArgumentNullException("deserializeAction"); } - #endregion + #endregion Check if (!package.PartExists(uri)) { @@ -302,7 +302,7 @@ throw new ArgumentNullException("right"); } - #endregion + #endregion Check excludeMethod = excludeMethod ?? (uri => false); PackagePartCollection leftParts = left.GetParts(); @@ -362,7 +362,10 @@ leftPart.ContentType == @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" && rightPart.ContentType == @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"; - if (!StreamHelper.Compare(leftMemoryStream, rightMemoryStream, stripColumnWidthsFromSheet)) + var tuple1 = new Tuple(pair.Uri, leftMemoryStream); + var tuple2 = new Tuple(pair.Uri, rightMemoryStream); + + if (!StreamHelper.Compare(tuple1, tuple2, stripColumnWidthsFromSheet)) { pair.Status = CompareStatus.NonEqual; if (compareToFirstDifference) @@ -396,11 +399,16 @@ { #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly CompressionOption _compressOption; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly string _contentType; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly Uri _uri; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly CompressionOption _compressOption; - #endregion + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly string _contentType; + + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly Uri _uri; + + #endregion Private fields #region Constructor @@ -423,33 +431,36 @@ throw new ArgumentNullException("contentType"); } - #endregion + #endregion Check _uri = uri; _contentType = contentType; _compressOption = compressOption; } - #endregion + #endregion Constructor #region Public properties public Uri Uri { - [DebuggerStepThrough] get { return _uri; } + [DebuggerStepThrough] + get { return _uri; } } public string ContentType { - [DebuggerStepThrough] get { return _contentType; } + [DebuggerStepThrough] + get { return _contentType; } } public CompressionOption CompressOption { - [DebuggerStepThrough] get { return _compressOption; } + [DebuggerStepThrough] + get { return _compressOption; } } - #endregion + #endregion Public properties #region Public methods @@ -458,10 +469,10 @@ return string.Format("Uri:{0} ContentType: {1}, Compression: {2}", _uri, _contentType, _compressOption); } - #endregion + #endregion Public methods } - #endregion + #endregion Nested type: PackagePartDescriptor #region Nested type: CompareStatus @@ -473,7 +484,7 @@ NonEqual } - #endregion + #endregion Nested type: CompareStatus #region Nested type: PartPair @@ -481,10 +492,13 @@ { #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly Uri _uri; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private CompareStatus _status; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly Uri _uri; - #endregion + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private CompareStatus _status; + + #endregion Private fields #region Constructor @@ -494,25 +508,28 @@ _status = status; } - #endregion + #endregion Constructor #region Public properties public Uri Uri { - [DebuggerStepThrough] get { return _uri; } + [DebuggerStepThrough] + get { return _uri; } } public CompareStatus Status { - [DebuggerStepThrough] get { return _status; } - [DebuggerStepThrough] set { _status = value; } + [DebuggerStepThrough] + get { return _status; } + [DebuggerStepThrough] + set { _status = value; } } - #endregion + #endregion Public properties } - #endregion + #endregion Nested type: PartPair //-- } diff --git a/ClosedXML_Tests/Utils/StreamHelper.cs b/ClosedXML_Tests/Utils/StreamHelper.cs index 8ecb4f9..bf89911 100644 --- a/ClosedXML_Tests/Utils/StreamHelper.cs +++ b/ClosedXML_Tests/Utils/StreamHelper.cs @@ -114,36 +114,39 @@ /// /// /// /// - public static bool Compare(Stream one, Stream other, bool stripColumnWidths) + public static bool Compare(Tuple tuple1, Tuple tuple2, bool stripColumnWidths) { #region Check - if (one == null) + if (tuple1 == null || tuple1.Item1 == null || tuple1.Item2 == null) { throw new ArgumentNullException("one"); } - if (other == null) + if (tuple2 == null || tuple2.Item1 == null || tuple2.Item2 == null) { throw new ArgumentNullException("other"); } - if (one.Position != 0) + if (tuple1.Item2.Position != 0) { throw new ArgumentException("Must be in position 0", "one"); } - if (other.Position != 0) + if (tuple1.Item2.Position != 0) { throw new ArgumentException("Must be in position 0", "other"); } #endregion Check - var stringOne = new StreamReader(one).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); - var stringOther = new StreamReader(other).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); + var stringOne = new StreamReader(tuple1.Item2).ReadToEnd().RemoveIgnoredParts(tuple1.Item1, stripColumnWidths, ignoreGuids: true); + var stringOther = new StreamReader(tuple2.Item2).ReadToEnd().RemoveIgnoredParts(tuple2.Item1, stripColumnWidths, ignoreGuids: true); return stringOne == stringOther; } - private static string RemoveIgnoredParts(this string s, Boolean ignoreColumnWidths, Boolean ignoreGuids) + private static string RemoveIgnoredParts(this string s, Uri uri, Boolean ignoreColumnWidths, Boolean ignoreGuids) { + foreach (var pair in uriSpecificIgnores.Where(p => p.Key.Equals(uri.OriginalString))) + s = pair.Value.Replace(s, ""); + if (ignoreColumnWidths) s = RemoveColumnWidths(s); @@ -153,6 +156,12 @@ return s; } + private static IEnumerable> uriSpecificIgnores = new List>() + { + // Remove dcterms elements + new KeyValuePair("/docProps/core.xml", new Regex(@"", RegexOptions.Compiled)) + }; + private static Regex columnRegex = new Regex("", RegexOptions.Compiled); private static Regex widthRegex = new Regex("width=\"\\d+(\\.\\d+)?\"\\s+", RegexOptions.Compiled);