diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index bf439af..a7bc0e1 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -131,6 +131,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index e1b6e51..c45be92 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( 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..233a7c8 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -378,18 +378,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,7 +398,7 @@ { bool shouldUseValue = true; - foreach(var criteriaPair in criteriaRanges) + foreach (var criteriaPair in criteriaRanges) { if (!CalcEngineHelpers.ValueSatisfiesCriteria( criteriaPair.Item2[i], @@ -630,7 +630,6 @@ else throw new NumberException(); - n = (int)p[0]; k = (int)p[1]; @@ -670,11 +669,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(); @@ -1031,4 +1028,4 @@ return m.Invert().mat; } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 58baace..958221e 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -1,5 +1,5 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; -using System.Net; using System.Collections; using System.Collections.Generic; @@ -96,44 +96,39 @@ //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) { + if ((p[0] as XObjectExpression)?.Value as CellRangeReference == null) + throw new NoValueAvailableException("COUNTBLANK should have a single argument which is a range reference"); + var cnt = 0.0; - foreach (Expression e in p) + var e = p[0] as XObjectExpression; + 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 (IsBlank(value)) + cnt++; } + return cnt; } @@ -144,7 +139,7 @@ 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; @@ -210,59 +205,68 @@ 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..8796b84 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) { } @@ -84,7 +83,7 @@ return _list.Count(o => !Statistical.IsBlank(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/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 28309e3..b46bf38 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. 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 09e018b..094119f 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -997,7 +997,7 @@ } } - public IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.All) { return Clear(clearOptions, false); } @@ -1017,24 +1017,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); } } @@ -1679,7 +1688,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 37a8b43..413ddfe 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 { @@ -15,7 +14,8 @@ private readonly List _rangeAddresses = new List(); private readonly bool _usedCellsOnly; private readonly Func _predicate; - #endregion + + #endregion Fields #region Constructor @@ -27,7 +27,7 @@ _predicate = predicate; } - #endregion + #endregion Constructor #region IEnumerable Members @@ -63,7 +63,7 @@ && (_predicate == null || _predicate(c)) ); - foreach(var cell in cellRange) + foreach (var cell in cellRange) { yield return cell; } @@ -87,12 +87,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++) @@ -152,7 +152,7 @@ } } - #endregion + #endregion IEnumerable Members #region IXLCells Members @@ -183,14 +183,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()); } @@ -204,7 +204,7 @@ set { this.ForEach(c => c.FormulaR1C1 = value); } } - #endregion + #endregion IXLCells Members #region IXLStylized Members public override IEnumerable Styles @@ -236,7 +236,7 @@ } } - #endregion + #endregion IXLStylized Members public void Add(XLRangeAddress rangeAddress) { @@ -260,7 +260,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 0fda531..5bb1f50 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -1,7 +1,7 @@ using System; using System.Collections.Generic; -using System.Linq; using System.Drawing; +using System.Linq; namespace ClosedXML.Excel { @@ -15,7 +15,7 @@ private Double _width; - #endregion + #endregion Private fields #region Constructor @@ -54,7 +54,7 @@ _outlineLevel = column._outlineLevel; } - #endregion + #endregion Constructor public Boolean IsReference { get; private set; } @@ -134,7 +134,7 @@ } } - public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.All) { base.Clear(clearOptions); return this; @@ -286,7 +286,7 @@ foreach (IXLRichString rt in c.RichText) { String formattedString = rt.Text; - var arr = formattedString.Split(new[] {Environment.NewLine}, StringSplitOptions.None); + var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None); Int32 arrCount = arr.Count(); for (Int32 i = 0; i < arrCount; i++) { @@ -300,7 +300,7 @@ else { String formattedString = c.GetFormattedString(); - var arr = formattedString.Split(new[] {Environment.NewLine}, StringSplitOptions.None); + var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None); Int32 arrCount = arr.Count(); for (Int32 i = 0; i < arrCount; i++) { @@ -311,7 +311,7 @@ } } - #endregion + #endregion if (c.HasRichText) #region foreach (var kp in kpList) @@ -344,7 +344,7 @@ else runningWidth += f.GetWidth(formattedString, fontCache); - #endregion + #endregion if (newLinePosition >= 0) } else { @@ -383,11 +383,11 @@ runningWidth += f.GetWidth(formattedString, fontCache); } - #endregion + #endregion if (textRotation == 255) } } - #endregion + #endregion foreach (var kp in kpList) if (runningWidth > thisWidthMax) thisWidthMax = runningWidth; @@ -407,7 +407,7 @@ thisWidthMax = (thisWidthMax * Math.Cos(r)) + (maxLineWidth * lineCount); } - #endregion + #endregion if (rotated) } else thisWidthMax = cellStyle.Font.GetWidth(c.GetFormattedString(), fontCache); @@ -415,7 +415,6 @@ if (autoFilterRows.Contains(c.Address.RowNumber)) thisWidthMax += 2.7148; // Allow room for arrow icon in autofilter - if (thisWidthMax >= maxWidth) { colMaxWidth = maxWidth; @@ -438,7 +437,6 @@ return this; } - public IXLColumn Hide() { IsHidden = true; @@ -549,19 +547,18 @@ return this; } - IXLRangeColumn IXLColumn.CopyTo(IXLCell target) { using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Column(1); + using (var copy = asRange.CopyTo(target)) + return copy.Column(1); } IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target) { using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Column(1); + using (var copy = asRange.CopyTo(target)) + return copy.Column(1); } public IXLColumn CopyTo(IXLColumn column) @@ -617,7 +614,7 @@ return Column(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } - #endregion + #endregion IXLColumn Members public override XLRange AsRange() { @@ -679,7 +676,6 @@ return Math.PI * angle / 180.0; } - private XLColumn ColumnShift(Int32 columnsToShift) { return Worksheet.Column(ColumnNumber() + columnsToShift); @@ -707,7 +703,7 @@ return ColumnShift(step * -1); } - #endregion + #endregion XLColumn Left #region XLColumn Right @@ -731,7 +727,7 @@ return ColumnShift(step); } - #endregion + #endregion XLColumn Right public override Boolean IsEmpty() { diff --git a/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/Excel/Columns/XLColumns.cs index 1325bef..8a992d2 100644 --- a/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/Excel/Columns/XLColumns.cs @@ -194,7 +194,7 @@ return this; } - #endregion + #endregion IXLColumns Members #region IXLStylized Members @@ -239,7 +239,7 @@ } } - #endregion + #endregion IXLStylized Members public void Add(XLColumn column) { @@ -251,9 +251,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; } @@ -269,4 +269,4 @@ range.Select(); } } -} \ No newline at end of file +} 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/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 3c5d25e..4707327 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -230,7 +230,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 bdb8247..e4d4c85 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -814,7 +814,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/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 0e67a68..9923a91 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -313,13 +313,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) @@ -327,18 +329,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(); @@ -1212,12 +1267,6 @@ return retVal; } - private struct DataValidationToCopy - { - public XLAddress SourceAddress; - public XLDataValidation DataValidation; - } - public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) { InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 26048d6..a642b56 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -370,7 +370,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 525b13a..ede54ff 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -16,7 +16,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; @@ -76,7 +76,7 @@ return this; } - #endregion + #endregion IXLRangeColumns Members #region IXLStylized Members @@ -117,7 +117,7 @@ } } - #endregion + #endregion IXLStylized Members public void Dispose() { @@ -131,4 +131,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index cdba803..ec245c5 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -349,7 +349,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 4544e08..553e493 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -16,7 +16,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; @@ -36,7 +36,7 @@ public IEnumerator GetEnumerator() { return _ranges.Cast() - .OrderBy(r=>r.Worksheet.Position) + .OrderBy(r => r.Worksheet.Position) .ThenBy(r => r.RowNumber()) .GetEnumerator(); } @@ -76,7 +76,7 @@ return this; } - #endregion + #endregion IXLRangeRows Members #region IXLStylized Members @@ -118,7 +118,7 @@ } } - #endregion + #endregion IXLStylized Members public void Dispose() { @@ -131,6 +131,5 @@ foreach (var range in this) range.Select(); } - } } diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index eecb266..a41fd8b 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -16,7 +16,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; @@ -136,7 +136,7 @@ _ranges.ForEach(r => r.Dispose()); } - #endregion + #endregion IXLRanges Members #region IXLStylized Members @@ -172,7 +172,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 a85da84..12da17a 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -208,7 +208,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; diff --git a/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/Excel/Rows/XLRows.cs index 97df87f..c7a9321 100644 --- a/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/Excel/Rows/XLRows.cs @@ -20,7 +20,7 @@ public IEnumerator GetEnumerator() { - return _rows.Cast().OrderBy(r=>r.RowNumber()).GetEnumerator(); + return _rows.Cast().OrderBy(r => r.RowNumber()).GetEnumerator(); } IEnumerator IEnumerable.GetEnumerator() @@ -101,7 +101,6 @@ return this; } - public void Hide() { _rows.ForEach(r => r.Hide()); @@ -189,7 +188,7 @@ return this; } - #endregion + #endregion IXLRows Members #region IXLStylized Members protected override IEnumerable Children @@ -233,14 +232,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; @@ -258,4 +257,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 b343b51..97f5c5f 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 a07a871..bbc1479 100644 --- a/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/Excel/Tables/XLTableRows.cs @@ -8,8 +8,9 @@ internal class XLTableRows : XLStylizedBase, IXLTableRows, IXLStylized { private readonly List _ranges = new List(); - + public XLTableRows(IXLStyle defaultStyle) : base(defaultStyle.Value) + { } @@ -53,11 +54,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; @@ -104,7 +105,7 @@ return cells; } - #endregion + #endregion IXLTableRows Members public void Select() { @@ -112,4 +113,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_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/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 4bb58e0..56ecb40 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -88,6 +88,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index 3ad7f8e..a4435ef 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 a0bb2a2..7a432e1 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] diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 754d158..a6daf62 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 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/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/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()); + } } }