diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 4bc2ce0..b17bde2 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -65,6 +65,14 @@ + + + + + + + + @@ -73,6 +81,10 @@ + + + + @@ -83,12 +95,15 @@ + + + diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs index 88ca907..f13ce40 100644 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -1,25 +1,30 @@ using System; + namespace ClosedXML.Excel { - using System.Collections.Generic; public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterDynamicType { AboveAverage, BelowAverage } - public enum XLTopBottomPart { Top, Bottom} + + public enum XLTopBottomPart { Top, Bottom } + public interface IXLBaseAutoFilter { Boolean Enabled { get; set; } IXLRange Range { get; set; } + IXLBaseAutoFilter Set(IXLRangeBase range); + IXLBaseAutoFilter Clear(); IXLFilterColumn Column(String column); + IXLFilterColumn Column(Int32 column); IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + Boolean Sorted { get; set; } XLSortOrder SortOrder { get; set; } Int32 SortColumn { get; set; } - - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 8cc752e..c1a1aa1 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -253,6 +253,18 @@ #region ** token/keyword tables + private static readonly IDictionary ErrorMap = new Dictionary() + { + ["#REF!"] = ErrorExpression.ExpressionErrorType.CellReference, + ["#VALUE!"] = ErrorExpression.ExpressionErrorType.CellValue, + ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivisionByZero, + ["#NAME?"] = ErrorExpression.ExpressionErrorType.NameNotRecognized, + ["#N/A"] = ErrorExpression.ExpressionErrorType.NoValueAvailable, + ["#NULL!"] = ErrorExpression.ExpressionErrorType.NullValue, + ["#NUM!"] = ErrorExpression.ExpressionErrorType.NumberInvalid + }; + + // build/get static token table private Dictionary GetSymbolTable() { @@ -463,6 +475,10 @@ } break; + + case TKTYPE.ERROR: + x = new ErrorExpression((ErrorExpression.ExpressionErrorType)_token.Value); + break; } // make sure we got something... @@ -663,26 +679,12 @@ return; } - // parse dates (review) - if (c == '#') + // parse #REF! (and other errors) in formula + if (c == '#' && ErrorMap.Any(pair => _len > _ptr+pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase))) { - // look for end # - for (i = 1; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; - if (c == '#') break; - } - - // check that we got the end of the date - if (c != '#') - { - Throw("Can't find final date delimiter ('#')."); - } - - // end of date - var lit = _expr.Substring(_ptr + 1, i - 1); - _ptr += i + 1; - _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL); + var errorPair = ErrorMap.Single(pair => _len > _ptr + pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase)); + _ptr += errorPair.Key.Length; + _token = new Token(errorPair.Value, TKID.ATOM, TKTYPE.ERROR); return; } diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs new file mode 100644 index 0000000..962b1c6 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs @@ -0,0 +1,18 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + internal abstract class CalcEngineException : ArgumentException + { + protected CalcEngineException() + : base() + { } + protected CalcEngineException(string message) + : base(message) + { } + + protected CalcEngineException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs new file mode 100644 index 0000000..f0fd88c --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error occurs when you delete a cell referred to in the + /// formula or if you paste cells over the ones referred to in the + /// formula. + /// Corresponds to the #REF! error in Excel + /// + /// + internal class CellReferenceException : CalcEngineException + { + public CellReferenceException() + : base() + { } + + public CellReferenceException(string message) + : base(message) + { } + + public CellReferenceException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs new file mode 100644 index 0000000..0716353 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error is most often the result of specifying a + /// mathematical operation with one or more cells that contain + /// text. + /// Corresponds to the #VALUE! error in Excel + /// + /// + internal class CellValueException : CalcEngineException + { + public CellValueException() + : base() + { } + + public CellValueException(string message) + : base(message) + { } + + public CellValueException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs new file mode 100644 index 0000000..53e2ed1 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// The division operation in your formula refers to a cell that + /// contains the value 0 or is blank. + /// Corresponds to the #DIV/0! error in Excel + /// + /// + internal class DivisionByZeroException : CalcEngineException + { + public DivisionByZeroException() + : base() + { } + + public DivisionByZeroException(string message) + : base(message) + { } + + public DivisionByZeroException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs new file mode 100644 index 0000000..0f51e56 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error value appears when you incorrectly type the range + /// name, refer to a deleted range name, or forget to put quotation + /// marks around a text string in a formula. + /// Corresponds to the #NAME? error in Excel + /// + /// + internal class NameNotRecognizedException : CalcEngineException + { + public NameNotRecognizedException() + : base() + { } + + public NameNotRecognizedException(string message) + : base(message) + { } + + public NameNotRecognizedException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs new file mode 100644 index 0000000..0e97fe5 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Technically, this is not an error value but a special value + /// that you can manually enter into a cell to indicate that you + /// don’t yet have a necessary value. + /// Corresponds to the #N/A error in Excel. + /// + /// + internal class NoValueAvailableException : CalcEngineException + { + public NoValueAvailableException() + : base() + { } + + public NoValueAvailableException(string message) + : base(message) + { } + + public NoValueAvailableException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs new file mode 100644 index 0000000..d3153d7 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Because a space indicates an intersection, this error will + /// occur if you insert a space instead of a comma(the union operator) + /// between ranges used in function arguments. + /// Corresponds to the #NULL! error in Excel + /// + /// + internal class NullValueException : CalcEngineException + { + public NullValueException() + : base() + { } + + public NullValueException(string message) + : base(message) + { } + + public NullValueException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs new file mode 100644 index 0000000..4ce87d0 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error can be caused by an invalid argument in an Excel + /// function or a formula that produces a number too large or too small + /// to be represented in the worksheet. + /// Corresponds to the #NUM! error in Excel + /// + /// + internal class NumberException : CalcEngineException + { + public NumberException() + : base() + { } + + public NumberException(string message) + : base(message) + { } + + public NumberException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 9dfdf28..b9a29ca 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections; using System.Collections.Generic; @@ -79,12 +80,18 @@ public static implicit operator string(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + var v = x.Evaluate(); return v == null ? string.Empty : v.ToString(); } public static implicit operator double(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -119,6 +126,9 @@ public static implicit operator bool(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -146,6 +156,9 @@ public static implicit operator DateTime(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -490,6 +503,52 @@ } } + internal class ErrorExpression : Expression + { + internal enum ExpressionErrorType + { + CellReference, + CellValue, + DivisionByZero, + NameNotRecognized, + NoValueAvailable, + NullValue, + NumberInvalid + } + + internal ErrorExpression(ExpressionErrorType eet) + : base(new Token(eet, TKID.ATOM, TKTYPE.ERROR)) + { } + + public override object Evaluate() + { + return this._token.Value; + } + + public void ThrowApplicableException() + { + var eet = (ExpressionErrorType)_token.Value; + switch (eet) + { + // TODO: include last token in exception message + case ExpressionErrorType.CellReference: + throw new CellReferenceException(); + case ExpressionErrorType.CellValue: + throw new CellValueException(); + case ExpressionErrorType.DivisionByZero: + throw new DivisionByZeroException(); + case ExpressionErrorType.NameNotRecognized: + throw new NameNotRecognizedException(); + case ExpressionErrorType.NoValueAvailable: + throw new NoValueAvailableException(); + case ExpressionErrorType.NullValue: + throw new NullValueException(); + case ExpressionErrorType.NumberInvalid: + throw new NumberException(); + } + } + } + /// /// Interface supported by external objects that have to return a value /// other than themselves (e.g. a cell range object should return the diff --git a/ClosedXML/Excel/CalcEngine/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs index 6df1a1e..6b19dfb 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Information.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Information.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -9,27 +10,42 @@ public static void Register(CalcEngine ce) { //TODO: Add documentation - ce.RegisterFunction("ERRORTYPE",1,ErrorType); - ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); - ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); - ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); - ce.RegisterFunction("ISEVEN",1, IsEven); - ce.RegisterFunction("ISLOGICAL",1,int.MaxValue,IsLogical); - ce.RegisterFunction("ISNA",1, int.MaxValue, IsNa); - ce.RegisterFunction("ISNONTEXT",1, int.MaxValue, IsNonText); - ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); - ce.RegisterFunction("ISODD",1,IsOdd); - ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); + ce.RegisterFunction("ERRORTYPE", 1, ErrorType); + ce.RegisterFunction("ISBLANK", 1, int.MaxValue, IsBlank); + ce.RegisterFunction("ISERR", 1, int.MaxValue, IsErr); + ce.RegisterFunction("ISERROR", 1, int.MaxValue, IsError); + ce.RegisterFunction("ISEVEN", 1, IsEven); + ce.RegisterFunction("ISLOGICAL", 1, int.MaxValue, IsLogical); + ce.RegisterFunction("ISNA", 1, int.MaxValue, IsNa); + ce.RegisterFunction("ISNONTEXT", 1, int.MaxValue, IsNonText); + ce.RegisterFunction("ISNUMBER", 1, int.MaxValue, IsNumber); + ce.RegisterFunction("ISODD", 1, IsOdd); + ce.RegisterFunction("ISREF", 1, int.MaxValue, IsRef); ce.RegisterFunction("ISTEXT", 1, int.MaxValue, IsText); - ce.RegisterFunction("N",1,N); - ce.RegisterFunction("NA",0,NA); - ce.RegisterFunction("TYPE",1,Type); + ce.RegisterFunction("N", 1, N); + ce.RegisterFunction("NA", 0, NA); + ce.RegisterFunction("TYPE", 1, Type); } + static IDictionary errorTypes = new Dictionary() + { + [ErrorExpression.ExpressionErrorType.NullValue] = 1, + [ErrorExpression.ExpressionErrorType.DivisionByZero] = 2, + [ErrorExpression.ExpressionErrorType.CellValue] = 3, + [ErrorExpression.ExpressionErrorType.CellReference] = 4, + [ErrorExpression.ExpressionErrorType.NameNotRecognized] = 5, + [ErrorExpression.ExpressionErrorType.NumberInvalid] = 6, + [ErrorExpression.ExpressionErrorType.NoValueAvailable] = 7 + }; + static object ErrorType(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + if (v is ErrorExpression.ExpressionErrorType) + return errorTypes[(ErrorExpression.ExpressionErrorType)v]; + else + throw new NoValueAvailableException(); } static object IsBlank(List p) @@ -46,17 +62,19 @@ return isBlank; } - //TODO: Support for Error Values static object IsErr(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) != ErrorExpression.ExpressionErrorType.NoValueAvailable; } - + static object IsError(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType; } static object IsEven(List p) @@ -74,7 +92,7 @@ { var v = p[0].Evaluate(); var isLogical = v is bool; - + if (isLogical && p.Count > 1) { var sublist = p.GetRange(1, p.Count); @@ -86,8 +104,10 @@ static object IsNa(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) == ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object IsNonText(List p) @@ -110,16 +130,16 @@ try { var stringValue = (string) v; - double.Parse(stringValue.TrimEnd('%', ' '), NumberStyles.Any); - isNumber = true; + double dv; + return double.TryParse(stringValue.TrimEnd('%', ' '), NumberStyles.Any, null, out dv); } catch (Exception) { isNumber = false; } } - - if (isNumber && p.Count > 1) + + if (isNumber && p.Count > 1) { var sublist = p.GetRange(1, p.Count); isNumber = (bool)IsNumber(sublist); @@ -135,8 +155,13 @@ static object IsRef(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var oe = p[0] as XObjectExpression; + if (oe == null) + return false; + + var crr = oe.Value as CellRangeReference; + + return crr != null; } static object IsText(List p) @@ -161,8 +186,7 @@ static object NA(List p) { - //TODO: Write Code - throw new NotSupportedException();; + return ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object Type(List p) @@ -190,4 +214,4 @@ return null; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 964e72b..801a195 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Linq; @@ -35,11 +36,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -49,10 +50,10 @@ || (bool)(p[3]); if (row_index_num < 1) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); if (row_index_num > range.RowCount()) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); IXLRangeColumn matching_column; matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); @@ -72,7 +73,7 @@ } if (matching_column == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_column .Cell(row_index_num) @@ -85,11 +86,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -99,13 +100,20 @@ || (bool)(p[3]); if (col_index_num < 1) - throw new ArgumentOutOfRangeException("Column index", "col_index_num has to be positive"); + throw new CellReferenceException("Column index has to be positive"); if (col_index_num > range.ColumnCount()) - throw new ArgumentOutOfRangeException("Column index", "col_index_num must be smaller or equal to the number of columns in the table array"); + throw new CellReferenceException("Colum index must be smaller or equal to the number of columns in the table array"); IXLRangeRow matching_row; - matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + try + { + matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + } + catch (Exception ex) + { + throw new NoValueAvailableException("No matches found", ex); + } if (range_lookup && matching_row == null) { var first_row = range.FirstRow().RowNumber(); @@ -122,7 +130,7 @@ } if (matching_row == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_row .Cell(col_index_num) diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 12e0995..12ea4e7 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,8 +1,10 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using ClosedXML.Excel.CalcEngine.Functions; using System; using System.Collections; using System.Collections.Generic; using System.Linq; +using System.Text; namespace ClosedXML.Excel.CalcEngine { @@ -22,8 +24,12 @@ ce.RegisterFunction("ATANH", 1, Atanh); ce.RegisterFunction("CEILING", 1, Ceiling); ce.RegisterFunction("COMBIN", 2, Combin); + ce.RegisterFunction("COMBINA", 2, CombinA); ce.RegisterFunction("COS", 1, Cos); ce.RegisterFunction("COSH", 1, Cosh); + ce.RegisterFunction("COT", 1, Cot); + ce.RegisterFunction("CSCH", 1, Csch); + ce.RegisterFunction("DECIMAL", 2, MathTrig.Decimal); ce.RegisterFunction("DEGREES", 1, Degrees); ce.RegisterFunction("EVEN", 1, Even); ce.RegisterFunction("EXP", 1, Exp); @@ -64,7 +70,7 @@ ce.RegisterFunction("SUBTOTAL", 2, 255, Subtotal); ce.RegisterFunction("SUM", 1, int.MaxValue, Sum); ce.RegisterFunction("SUMIF", 2, 3, SumIf); - //ce.RegisterFunction("SUMPRODUCT", 1, SumProduct); + ce.RegisterFunction("SUMPRODUCT", 1, 30, SumProduct); ce.RegisterFunction("SUMSQ", 1, 255, SumSq); //ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1); //ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1); @@ -114,6 +120,58 @@ return Math.Cosh(p[0]); } + private static object Cot(List p) + { + var tan = (double)Math.Tan(p[0]); + + if (tan == 0) + throw new DivisionByZeroException(); + + return 1 / tan; + } + + private static object Csch(List p) + { + if (Math.Abs((double)p[0].Evaluate()) < Double.Epsilon) + throw new DivisionByZeroException(); + + return 1 / Math.Sinh(p[0]); + } + + private static object Decimal(List p) + { + string source = p[0]; + double radix = p[1]; + + if (radix < 2 || radix > 36) + throw new NumberException(); + + var asciiValues = Encoding.ASCII.GetBytes(source.ToUpperInvariant()); + + double result = 0; + int i = 0; + + foreach (byte digit in asciiValues) + { + if (digit > 90) + { + throw new NumberException(); + } + + int digitNumber = digit >= 48 && digit < 58 + ? digit - 48 + : digit - 55; + + if (digitNumber > radix - 1) + throw new NumberException(); + + result = result * radix + digitNumber; + i++; + } + + return result; + } + private static object Exp(List p) { return Math.Exp(p[0]); @@ -158,7 +216,7 @@ private static object Int(List p) { - return (int)((double)p[0]); + return Math.Floor(p[0]); } private static object Ln(List p) @@ -261,6 +319,37 @@ return tally.Sum(); } + private static object SumProduct(List p) + { + // all parameters should be IEnumerable + if (p.Any(param => !(param is IEnumerable))) + throw new NoValueAvailableException(); + + var counts = p.Cast().Select(param => + { + int i = 0; + foreach (var item in param) + i++; + return i; + }) + .Distinct(); + + // All parameters should have the same length + if (counts.Count() > 1) + throw new NoValueAvailableException(); + + var values = p + .Cast() + .Select(range => range.Cast().ToList()); + + return Enumerable.Range(0, counts.Single()) + .Aggregate(0d, (t, i) => + t + values.Aggregate(1d, + (product, list) => product * list[i] + ) + ); + } + private static object Tan(List p) { return Math.Tan(p[0]); @@ -333,6 +422,24 @@ return XLMath.Combin(n, k); } + private static object CombinA(List p) + { + Int32 number = (int)p[0]; // casting truncates towards 0 as specified + Int32 chosen = (int)p[1]; + + if (number < 0 || number < chosen) + throw new NumberException(); + if (chosen < 0) + throw new NumberException(); + + int n = number + chosen - 1; + int k = number - 1; + + return n == k || k == 0 + ? 1 + : (long)XLMath.Combin(n, k); + } + private static object Degrees(List p) { return p[0] * (180.0 / Math.PI); diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 6046455..8ff1da3 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -44,7 +45,9 @@ private static object _Char(List p) { var i = (int)p[0]; - if (i < 1 || i > 255) throw new IndexOutOfRangeException(); + if (i < 1 || i > 255) + throw new CellValueException(string.Format("The number {0} is out of the required range (1 to 255)", i)); + var c = (char)i; return c.ToString(); } diff --git a/ClosedXML/Excel/CalcEngine/Token.cs b/ClosedXML/Excel/CalcEngine/Token.cs index 67351df..4766a51 100644 --- a/ClosedXML/Excel/CalcEngine/Token.cs +++ b/ClosedXML/Excel/CalcEngine/Token.cs @@ -1,12 +1,13 @@ namespace ClosedXML.Excel.CalcEngine { /// - /// Represents a node in the expression tree. + /// Represents a node in the expression tree. /// internal class Token - { + { // ** fields - public TKID ID; + public TKID ID; + public TKTYPE Type; public object Value; @@ -15,22 +16,25 @@ { Value = value; ID = id; - Type = type; - } + Type = type; + } } + /// /// Token types (used when building expressions, sequence defines operator priority) /// internal enum TKTYPE { - COMPARE, // < > = <= >= - ADDSUB, // + - - MULDIV, // * / - POWER, // ^ - GROUP, // ( ) , . - LITERAL, // 123.32, "Hello", etc. - IDENTIFIER // functions, external objects, bindings + COMPARE, // < > = <= >= + ADDSUB, // + - + MULDIV, // * / + POWER, // ^ + GROUP, // ( ) , . + LITERAL, // 123.32, "Hello", etc. + IDENTIFIER, // functions, external objects, bindings + ERROR // e.g. #REF! } + /// /// Token ID (used when evaluating expressions) /// diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 8df2759..17fdf43 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -7,6 +7,8 @@ { public enum XLCellValues { Text, Number, Boolean, DateTime, TimeSpan } + public enum XLTableCellType { None, Header, Data, Total } + public enum XLClearOptions { ContentsAndFormats, @@ -17,11 +19,11 @@ public interface IXLCell { /// - /// Gets or sets the cell's value. To get a strongly typed object use the method GetValue<T>. - /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from this cell. - /// If the object is a range ClosedXML will copy the range starting from this cell. + /// Gets or sets the cell's value. To get or set a strongly typed value, use the GetValue<T> and SetValue methods. + /// ClosedXML will try to detect the data type through parsing. If it can't then the value will be left as a string. + /// If the object is an IEnumerable, ClosedXML will copy the collection's data into a table starting from this cell. + /// If the object is a range, ClosedXML will copy the range starting from this cell. /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. - /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. /// /// /// The object containing the value(s) to set. @@ -30,7 +32,15 @@ /// Gets this cell's address, relative to the worksheet. /// The cell's address. - IXLAddress Address { get; } + IXLAddress Address { get; } + + /// + /// Returns the current region. The current region is a range bounded by any combination of blank rows and blank columns + /// + /// + /// The current region. + /// + IXLRange CurrentRegion { get; } /// /// Gets or sets the type of this cell's data. @@ -115,8 +125,6 @@ Boolean TryGetValue(out T value); - - Boolean HasHyperlink { get; } /// @@ -152,7 +160,6 @@ /// IXLRange AsRange(); - /// /// Gets or sets the cell's style. /// @@ -181,6 +188,13 @@ IXLRange InsertData(IEnumerable data, Boolean tranpose); /// + /// Inserts the data of a data table. + /// + /// The data table. + /// The range occupied by the inserted data + IXLRange InsertData(DataTable dataTable); + + /// /// Inserts the IEnumerable data elements as a table and returns it. /// The new table will receive a generic name: Table# /// @@ -216,7 +230,6 @@ /// IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable); - /// /// Inserts the DataTable data elements as a table and returns it. /// The new table will receive a generic name: Table# @@ -253,18 +266,22 @@ /// IXLTable InsertTable(DataTable data, String tableName, Boolean createTable); + XLTableCellType TableCellType(); XLHyperlink Hyperlink { get; set; } IXLWorksheet Worksheet { get; } IXLDataValidation DataValidation { get; } IXLDataValidation NewDataValidation { get; } + IXLDataValidation SetDataValidation(); - IXLCells InsertCellsAbove(int numberOfRows); + IXLCells InsertCellsBelow(int numberOfRows); + IXLCells InsertCellsAfter(int numberOfColumns); + IXLCells InsertCellsBefore(int numberOfColumns); /// @@ -293,8 +310,11 @@ IXLCell AddToNamed(String rangeName, XLScope scope, String comment); IXLCell CopyFrom(IXLCell otherCell); + IXLCell CopyFrom(String otherCell); + IXLCell CopyTo(IXLCell target); + IXLCell CopyTo(String target); String ValueCached { get; } @@ -305,19 +325,29 @@ Boolean HasComment { get; } Boolean IsMerged(); + Boolean IsEmpty(); + Boolean IsEmpty(Boolean includeFormats); IXLCell CellAbove(); + IXLCell CellAbove(Int32 step); + IXLCell CellBelow(); + IXLCell CellBelow(Int32 step); + IXLCell CellLeft(); + IXLCell CellLeft(Int32 step); + IXLCell CellRight(); + IXLCell CellRight(Int32 step); IXLColumn WorksheetColumn(); + IXLRow WorksheetRow(); Boolean HasDataValidation { get; } @@ -327,6 +357,7 @@ void Select(); Boolean Active { get; set; } + IXLCell SetActive(Boolean value = true); Boolean HasFormula { get; } diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index f5e9084..0c335d5 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -15,6 +15,7 @@ using Attributes; using ClosedXML.Extensions; + [DebuggerDisplay("{Address}")] internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); @@ -73,49 +74,7 @@ internal XLCellValues _dataType; private XLHyperlink _hyperlink; private XLRichText _richText; - - #endregion Fields - - #region Constructor - - private Int32 _styleCacheId; - - public XLCell(XLWorksheet worksheet, XLAddress address, Int32 styleId) - { - Address = address; - ShareString = true; - _worksheet = worksheet; - SetStyle(styleId); - } - - private IXLStyle GetStyleForRead() - { - return Worksheet.Workbook.GetStyleById(GetStyleId()); - } - - public Int32 GetStyleId() - { - if (StyleChanged) - SetStyle(Style); - - return _styleCacheId; - } - - private void SetStyle(IXLStyle styleToUse) - { - _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); - _style = null; - StyleChanged = false; - } - - private void SetStyle(Int32 styleId) - { - _styleCacheId = styleId; - _style = null; - StyleChanged = false; - } - - #endregion Constructor + private Int32? _styleCacheId; public bool SettingHyperlink; public int SharedStringId; @@ -123,6 +82,25 @@ private string _formulaR1C1; private IXLStyle _style; + #endregion Fields + + #region Constructor + + public XLCell(XLWorksheet worksheet, XLAddress address, Int32 styleId) + : this(worksheet, address) + { + SetStyle(styleId); + } + + public XLCell(XLWorksheet worksheet, XLAddress address) + { + Address = address; + ShareString = true; + _worksheet = worksheet; + } + + #endregion Constructor + public XLWorksheet Worksheet { get { return _worksheet; } @@ -221,11 +199,23 @@ public IXLCell SetValue(T value) { + return SetValue(value, true); + } + + internal IXLCell SetValue(T value, bool setTableHeader) + { if (value == null) return this.Clear(XLClearOptions.Contents); FormulaA1 = String.Empty; _richText = null; + + if (setTableHeader) + { + if (SetTableHeaderValue(value)) return this; + if (SetTableTotalsRowLabel(value)) return this; + } + var style = GetStyleForRead(); if (value is String || value is char) { @@ -399,8 +389,7 @@ var retValEnumerable = retVal as IEnumerable; if (retValEnumerable != null && !(retVal is String)) - foreach (var v in retValEnumerable) - return v; + return retValEnumerable.Cast().First(); return retVal; } @@ -439,14 +428,16 @@ { FormulaA1 = String.Empty; - if (value as XLCells != null) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); + if (value is XLCells) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); - if (SetTableHeader(value)) return; + if (SetTableHeaderValue(value)) return; if (SetRangeRows(value)) return; if (SetRangeColumns(value)) return; + if (SetDataTable(value)) return; + if (SetEnumerable(value)) return; if (SetRange(value)) return; @@ -454,7 +445,7 @@ if (!SetRichText(value)) SetValue(value); - if (_cellValue.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters."); + if (_cellValue.Length > 32767) throw new ArgumentException("Cells can hold only 32,767 characters."); } } @@ -475,6 +466,9 @@ public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable) { + if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) + throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); + if (data != null && !(data is String)) { var ro = Address.RowNumber + 1; @@ -504,12 +498,12 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); hasTitles = true; co = Address.ColumnNumber; } - SetValue(o, ro, co); + _worksheet.SetValue(o, ro, co); co++; if (co > maxCo) @@ -520,7 +514,7 @@ } else { - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance; + const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; var memberCache = new Dictionary>(); var accessorCache = new Dictionary(); IEnumerable members = null; @@ -566,7 +560,7 @@ { foreach (var item in (m as Array)) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -583,7 +577,7 @@ ? column.ColumnName : column.Caption) { - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); co++; } @@ -593,7 +587,7 @@ foreach (var item in row.ItemArray) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -609,7 +603,7 @@ { for (var i = 0; i < fieldCount; i++) { - SetValue(record.GetName(i), fRo, co); + _worksheet.SetValue(record.GetName(i), fRo, co); co++; } @@ -619,7 +613,7 @@ for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], ro, co); + _worksheet.SetValue(record[i], ro, co); co++; } } @@ -629,13 +623,13 @@ { foreach (var mi in members) { - if ((mi as IEnumerable) == null) + if (!(mi is IEnumerable)) { var fieldName = XLColumnAttribute.GetHeader(mi); if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); } co++; @@ -647,7 +641,13 @@ foreach (var mi in members) { - SetValue(accessor[m, mi.Name], ro, co); + if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) + _worksheet.SetValue((mi as PropertyInfo).GetValue(null), ro, co); + else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) + _worksheet.SetValue((mi as FieldInfo).GetValue(null), ro, co); + else + _worksheet.SetValue(accessor[m, mi.Name], ro, co); + co++; } } @@ -691,16 +691,19 @@ public IXLTable InsertTable(DataTable data, string tableName, bool createTable) { - if (data == null) return null; + if (data == null || data.Columns.Count == 0) + return null; - if (data.Rows.Count > 0) return InsertTable(data.AsEnumerable(), tableName, createTable); + if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) + throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); + if (data.Rows.Cast().Any()) return InsertTable(data.Rows.Cast(), tableName, createTable); var ro = Address.RowNumber; var co = Address.ColumnNumber; foreach (DataColumn col in data.Columns) { - SetValue(col.ColumnName, ro, co); + _worksheet.SetValue(col.ColumnName, ro, co); co++; } @@ -716,6 +719,17 @@ return tableName == null ? range.AsTable() : range.AsTable(tableName); } + public XLTableCellType TableCellType() + { + var table = this.Worksheet.Tables.FirstOrDefault(t => t.AsRange().Contains(this)); + if (table == null) return XLTableCellType.None; + + if (table.ShowHeaderRow && table.HeadersRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Header; + if (table.ShowTotalsRow && table.TotalsRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Total; + + return XLTableCellType.Data; + } + public IXLRange InsertData(IEnumerable data) { return InsertData(data, false); @@ -733,7 +747,7 @@ var isDataTable = false; var isDataReader = false; - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance; + const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; var memberCache = new Dictionary>(); var accessorCache = new Dictionary(); IEnumerable members = null; @@ -742,31 +756,15 @@ foreach (var m in data) { var itemType = m.GetType(); - if (!memberCache.ContainsKey(itemType)) - { - var _accessor = TypeAccessor.Create(itemType); - - var _members = itemType.GetFields(bindingFlags).Cast() - .Concat(itemType.GetProperties(bindingFlags)) - .Where(mi => !XLColumnAttribute.IgnoreMember(mi)) - .OrderBy(mi => XLColumnAttribute.GetOrder(mi)); - - memberCache.Add(itemType, _members); - accessorCache.Add(itemType, _accessor); - } - - members = memberCache[itemType]; - accessor = accessorCache[itemType]; if (transpose) rowNumber = Address.RowNumber; else columnNumber = Address.ColumnNumber; - if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { - SetValue(m, rowNumber, columnNumber); + _worksheet.SetValue(m, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -777,7 +775,7 @@ { foreach (var item in (Array)m) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -792,7 +790,7 @@ foreach (var item in (m as DataRow).ItemArray) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -810,7 +808,7 @@ var fieldCount = record.FieldCount; for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], rowNumber, columnNumber); + _worksheet.SetValue(record[i], rowNumber, columnNumber); if (transpose) rowNumber++; @@ -820,9 +818,30 @@ } else { + if (!memberCache.ContainsKey(itemType)) + { + var _accessor = TypeAccessor.Create(itemType); + + var _members = itemType.GetFields(bindingFlags).Cast() + .Concat(itemType.GetProperties(bindingFlags)) + .Where(mi => !XLColumnAttribute.IgnoreMember(mi)) + .OrderBy(mi => XLColumnAttribute.GetOrder(mi)); + + memberCache.Add(itemType, _members); + accessorCache.Add(itemType, _accessor); + } + + accessor = accessorCache[itemType]; + members = memberCache[itemType]; + foreach (var mi in members) { - SetValue(accessor[m, mi.Name], rowNumber, columnNumber); + if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) + _worksheet.SetValue((mi as PropertyInfo).GetValue(null), rowNumber, columnNumber); + else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) + _worksheet.SetValue((mi as FieldInfo).GetValue(null), rowNumber, columnNumber); + else + _worksheet.SetValue(accessor[m, mi.Name], rowNumber, columnNumber); if (transpose) rowNumber++; @@ -854,6 +873,11 @@ return null; } + public IXLRange InsertData(DataTable dataTable) + { + return InsertData(dataTable.Rows); + } + public IXLStyle Style { get { return GetStyle(); } @@ -969,7 +993,7 @@ { //Note: We have to check if the cell is part of a merged range. If so we have to clear the whole range //Checking if called from range to avoid stack overflow - if (IsMerged() && !calledFromRange) + if (!calledFromRange && IsMerged()) { using (var asRange = AsRange()) { @@ -1163,7 +1187,7 @@ public Boolean IsMerged() { - return Worksheet.Internals.MergedRanges.Any(r => r.Contains(this)); + return Worksheet.Internals.MergedRanges.Contains(this); } public Boolean IsEmpty() @@ -1173,10 +1197,15 @@ public Boolean IsEmpty(Boolean includeFormats) { + return IsEmpty(includeFormats, includeFormats); + } + + public Boolean IsEmpty(Boolean includeNormalFormats, Boolean includeConditionalFormats) + { if (InnerText.Length > 0) return false; - if (includeFormats) + if (includeNormalFormats) { if (!Style.Equals(Worksheet.Style) || IsMerged() || HasComment || HasDataValidation) return false; @@ -1191,10 +1220,12 @@ if (Worksheet.Internals.ColumnsCollection.TryGetValue(Address.ColumnNumber, out column) && !column.Style.Equals(Worksheet.Style)) return false; } - - if (Worksheet.ConditionalFormats.Any(cf => cf.Range.Contains(this))) - return false; } + + if (includeConditionalFormats + && Worksheet.ConditionalFormats.Any(cf => cf.Range.Contains(this))) + return false; + return true; } @@ -1246,7 +1277,10 @@ get { using (var asRange = AsRange()) - return Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange) && dv.IsDirty()); + return Worksheet.DataValidations.Any(dv => + { + using (var rngs = dv.Ranges) return dv.IsDirty() && rngs.Contains(asRange); + }); } } @@ -1492,7 +1526,7 @@ #endregion IXLStylized Members - private bool SetTableHeader(object value) + private Boolean SetTableHeaderValue(object value) { foreach (var table in Worksheet.Tables.Where(t => t.ShowHeaderRow)) { @@ -1509,6 +1543,26 @@ return false; } + private Boolean SetTableTotalsRowLabel(object value) + { + foreach (var table in Worksheet.Tables.Where(t => t.ShowTotalsRow)) + { + var cells = table.TotalsRow().Cells(c => c.Address.Equals(this.Address)); + if (cells.Any()) + { + var cell = cells.First(); + var field = table.Fields.First(f => f.Column.ColumnNumber() == cell.WorksheetColumn().ColumnNumber()); + field.TotalsRowFunction = XLTotalsRowFunction.None; + field.TotalsRowLabel = value.ToString(); + this._cellValue = value.ToString(); + this.DataType = XLCellValues.Text; + return true; + } + } + + return false; + } + private bool SetRangeColumns(object value) { var columns = value as XLRangeColumns; @@ -1574,14 +1628,57 @@ return _worksheet.Range(Address, Address); } + #region Styles + private IXLStyle GetStyle() { if (_style != null) return _style; - return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)); + return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(StyleCacheId())); } + private IXLStyle GetStyleForRead() + { + return Worksheet.Workbook.GetStyleById(GetStyleId()); + } + + public Int32 GetStyleId() + { + if (StyleChanged) + SetStyle(Style); + + return StyleCacheId(); + } + + private void SetStyle(IXLStyle styleToUse) + { + _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); + _style = null; + StyleChanged = false; + } + + private void SetStyle(Int32 styleId) + { + _styleCacheId = styleId; + _style = null; + StyleChanged = false; + } + + public Int32 StyleCacheId() + { + if (!_styleCacheId.HasValue) + _styleCacheId = Worksheet.GetStyleId(); + return _styleCacheId.Value; + } + + public Boolean IsDefaultWorksheetStyle() + { + return !_styleCacheId.HasValue && !StyleChanged || GetStyleId() == Worksheet.GetStyleId(); + } + + #endregion Styles + public void DeleteComment() { _comment = null; @@ -1641,7 +1738,8 @@ { var maxRows = asRange.RowCount(); var maxColumns = asRange.ColumnCount(); - Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns).Clear(); + using (var rng = Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns)) + rng.Clear(); } var minRow = asRange.RangeAddress.FirstAddress.RowNumber; @@ -1676,6 +1774,13 @@ return false; } + private bool SetDataTable(object o) + { + var dataTable = o as DataTable; + if (dataTable == null) return false; + return InsertData(dataTable) != null; + } + private bool SetEnumerable(object collectionObject) { // IXLRichText implements IEnumerable, but we don't want to handle this here. @@ -1694,16 +1799,6 @@ mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } - private void SetValue(T value, int ro, int co) where T : class - { - if (value == null) - _worksheet.Cell(ro, co).SetValue(String.Empty); - else if (value is IConvertible) - _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); - else - _worksheet.Cell(ro, co).SetValue(value); - } - private void SetValue(object value) { FormulaA1 = String.Empty; @@ -1781,6 +1876,10 @@ } } if (val.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters."); + + if (SetTableHeaderValue(val)) return; + if (SetTableTotalsRowLabel(val)) return; + _cellValue = val; } @@ -2056,7 +2155,8 @@ CopyValuesFrom(source); - SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); + if (source._styleCacheId.HasValue) + SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId.Value)); var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) @@ -2148,147 +2248,149 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1ColumnRegex.IsMatch(rangeAddress)) { - var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) - { - if (A1RowRegex.IsMatch(rangeAddress)) - { - var rows = rangeAddress.Split(':'); - var row1String = rows[0]; - var row2String = rows[1]; - string row1; - if (row1String[0] == '$') - { - row1 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); + using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber + && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) + { + if (A1RowRegex.IsMatch(rangeAddress)) + { + var rows = rangeAddress.Split(':'); + var row1String = rows[0]; + var row2String = rows[1]; + string row1; + if (row1String[0] == '$') + { + row1 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); - string row2; - if (row2String[0] == '$') - { - row2 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); + string row2; + if (row2String[0] == '$') + { + row2 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) - : String.Format("{0}:{1}", row1, row2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) + : String.Format("{0}:{1}", row1, row2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); @@ -2352,166 +2454,168 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1RowRegex.IsMatch(rangeAddress)) { - var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.LastAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= - matchRange.RangeAddress.LastAddress.RowNumber) - { - if (A1ColumnRegex.IsMatch(rangeAddress)) - { - var columns = rangeAddress.Split(':'); - var column1String = columns[0]; - var column2String = columns[1]; - string column1; - if (column1String[0] == '$') - { - column1 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column1String.Substring(1)) + columnsShifted, true); - } - else - { - column1 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column1String) + - columnsShifted, true); - } + using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.LastAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= + matchRange.RangeAddress.LastAddress.RowNumber) + { + if (A1ColumnRegex.IsMatch(rangeAddress)) + { + var columns = rangeAddress.Split(':'); + var column1String = columns[0]; + var column2String = columns[1]; + string column1; + if (column1String[0] == '$') + { + column1 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column1String.Substring(1)) + columnsShifted, true); + } + else + { + column1 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column1String) + + columnsShifted, true); + } - string column2; - if (column2String[0] == '$') - { - column2 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column2String.Substring(1)) + columnsShifted, true); - } - else - { - column2 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column2String) + - columnsShifted, true); - } + string column2; + if (column2String[0] == '$') + { + column2 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column2String.Substring(1)) + columnsShifted, true); + } + else + { + column2 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column2String) + + columnsShifted, true); + } - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) - : String.Format("{0}:{1}", column1, column2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.FirstAddress.ColumnNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) + : String.Format("{0}:{1}", column1, column2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.FirstAddress.ColumnNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); @@ -2651,5 +2755,32 @@ public Boolean HasArrayFormula { get { return FormulaA1.StartsWith("{"); } } public IXLRangeAddress FormulaReference { get; set; } + + public IXLRange CurrentRegion + { + get + { + return this.Worksheet.Range(FindCurrentRegion(this.AsRange())); + } + } + + internal IXLRangeAddress FindCurrentRegion(IXLRangeBase range) + { + var rangeAddress = range.RangeAddress; + + var filledCells = range + .SurroundingCells(c => !(c as XLCell).IsEmpty(false, false)) + .Concat(this.Worksheet.Range(rangeAddress).Cells()); + + var grownRangeAddress = new XLRangeAddress( + new XLAddress(this.Worksheet, filledCells.Min(c => c.Address.RowNumber), filledCells.Min(c => c.Address.ColumnNumber), false, false), + new XLAddress(this.Worksheet, filledCells.Max(c => c.Address.RowNumber), filledCells.Max(c => c.Address.ColumnNumber), false, false) + ); + + if (rangeAddress.Equals(grownRangeAddress)) + return this.Worksheet.Range(grownRangeAddress).RangeAddress; + else + return FindCurrentRegion(this.Worksheet.Range(grownRangeAddress)); + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/Excel/Cells/XLCells.cs index 5f49b98..c8fef10 100644 --- a/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/Excel/Cells/XLCells.cs @@ -52,16 +52,18 @@ { if (oneRange) { - var cellRange = range.Worksheet.Internals.CellsCollection - .GetCells( - range.FirstAddress.RowNumber, - range.FirstAddress.ColumnNumber, - range.LastAddress.RowNumber, - range.LastAddress.ColumnNumber) - .Where(c => - !c.IsEmpty(_includeFormats) - && (_predicate == null || _predicate(c)) - ); + var cellRange = range + .Worksheet + .Internals + .CellsCollection + .GetCells( + range.FirstAddress.RowNumber, + range.FirstAddress.ColumnNumber, + range.LastAddress.RowNumber, + range.LastAddress.ColumnNumber) + .Where(c => !c.IsEmpty(_includeFormats) + && (_predicate == null || _predicate(c)) + ); foreach(var cell in cellRange) { @@ -121,13 +123,16 @@ { if (_usedCellsOnly) { - var cellRange = cellsInRanges.SelectMany( - cir => - cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( - cell => cell != null && ( - !cell.IsEmpty(_includeFormats) - && (_predicate == null || _predicate(cell)) - ))); + var cellRange = cellsInRanges + .SelectMany( + cir => + cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( + cell => + cell != null + && !cell.IsEmpty(_includeFormats) + && (_predicate == null || _predicate(cell)) + ) + ); foreach (var cell in cellRange) { @@ -277,4 +282,4 @@ cell.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Cells/XLCellsCollection.cs b/ClosedXML/Excel/Cells/XLCellsCollection.cs index 32a89aa..8f11a98 100644 --- a/ClosedXML/Excel/Cells/XLCellsCollection.cs +++ b/ClosedXML/Excel/Cells/XLCellsCollection.cs @@ -95,7 +95,7 @@ Count--; DecrementUsage(RowsUsed, row); DecrementUsage(ColumnsUsed, row); - + HashSet delHash; if (deleted.TryGetValue(row, out delHash)) { @@ -119,7 +119,7 @@ } } - + } internal IEnumerable GetCells(Int32 rowStart, Int32 columnStart, @@ -148,14 +148,22 @@ { HashSet ids = new HashSet(); ids.Add(initial); - foreach (var row in rowsCollection) + foreach (var row in rowsCollection.Values) { - foreach (var column in row.Value) + foreach (var cell in row.Values) { - var id = column.Value.GetStyleId(); - if (!ids.Contains(id)) + Int32? id = null; + + if (cell.StyleChanged) + id = cell.GetStyleId(); + else if (cell.StyleCacheId() != cell.Worksheet.GetStyleId()) { - ids.Add(id); + id = cell.GetStyleId(); + } + + if (id.HasValue && !ids.Contains(id.Value)) + { + ids.Add(id.Value); } } } @@ -494,4 +502,4 @@ return GetCells(row, 1, row, MaxColumnUsed); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index 962cc21..b47d0e7 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -68,7 +68,7 @@ IXLStyle WhenNotBlank(); IXLStyle WhenIsError(); IXLStyle WhenNotError(); - IXLStyle WhenDateIs(XLTimePeriod timePeriod ); + IXLStyle WhenDateIs(XLTimePeriod timePeriod); IXLStyle WhenContains(String value); IXLStyle WhenNotContains(String value); IXLStyle WhenStartsWith(String value); @@ -99,6 +99,7 @@ IXLCFColorScaleMin ColorScale(); IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false); + IXLCFDataBarMin DataBar(XLColor positiveColor, XLColor negativeColor, Boolean showBarOnly = false); IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false); XLConditionalFormatType ConditionalFormatType { get; } @@ -113,12 +114,12 @@ XLDictionary Colors { get; } XLDictionary ContentTypes { get; } XLDictionary IconSetOperators { get; } - + XLCFOperator Operator { get; } Boolean Bottom { get; } Boolean Percent { get; } - - + + IXLConditionalFormat StopIfTrue(bool value = true); + } } - \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs new file mode 100644 index 0000000..82b11a0 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/IXLCFConverterExtension.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Office2010.Excel; + +namespace ClosedXML.Excel +{ + internal interface IXLCFConverterExtension + { + ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context); + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs new file mode 100644 index 0000000..d00f871 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs @@ -0,0 +1,13 @@ +using ClosedXML.Utils; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal static class XLCFBaseConverter + { + public static ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority) + { + return new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, StopIfTrue = OpenXmlHelper.GetBooleanValue(((XLConditionalFormat)cf).StopIfTrueInternal, false) }; + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs index 98c63ef..f883286 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -12,8 +9,9 @@ { String val = GetQuoted(cf.Values[1]); - - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = cf.Operator.ToOpenXml(); var formula = new Formula(); if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual) diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs index 952a72d..e7fabb8 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs @@ -1,8 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +7,7 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var colorScale = new ColorScale(); for (Int32 i = 1; i <= cf.ContentTypes.Count; i++) diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs index c12c3b8..01a4694 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,8 +8,11 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.ContainsText, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.ContainsText; + conditionalFormattingRule.Text = val; + var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; conditionalFormattingRule.Append(formula); diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs index fc84360..2186baf 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs @@ -29,6 +29,7 @@ Converters.Add(XLConditionalFormatType.DataBar, new XLCFDataBarConverter()); Converters.Add(XLConditionalFormatType.IconSet, new XLCFIconSetConverter()); } + public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, Int32 priority, XLWorkbook.SaveContext context) { return Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, priority, context); diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs new file mode 100644 index 0000000..7b85951 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConvertersExtension.cs @@ -0,0 +1,28 @@ +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCFConvertersExtension + { + private readonly static Dictionary Converters; + + static XLCFConvertersExtension() + { + XLCFConvertersExtension.Converters = new Dictionary() + { + { XLConditionalFormatType.DataBar, new XLCFDataBarConverterExtension() } + }; + } + + public XLCFConvertersExtension() + { + } + + public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, XLWorkbook.SaveContext context) + { + return XLCFConvertersExtension.Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, context); + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index ff2d3c0..29300fa 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -1,8 +1,6 @@ using System; -using System.Collections.Generic; using System.Linq; -using System.Text; -using DocumentFormat.OpenXml; +using ClosedXML.Extensions; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,9 +9,10 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var dataBar = new DataBar { ShowValue = !cf.ShowBarOnly }; + var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() }; if (cf.Values.Any() && cf.Values[1]?.Value != null) conditionalFormatValueObject1.Val = cf.Values[1].Value; @@ -38,8 +37,25 @@ dataBar.Append(conditionalFormatValueObject2); dataBar.Append(color); + conditionalFormattingRule.Append(dataBar); + if (cf.Colors.Count > 1) + { + ConditionalFormattingRuleExtensionList conditionalFormattingRuleExtensionList = new ConditionalFormattingRuleExtensionList(); + + ConditionalFormattingRuleExtension conditionalFormattingRuleExtension = new ConditionalFormattingRuleExtension { Uri = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}" }; + conditionalFormattingRuleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + DocumentFormat.OpenXml.Office2010.Excel.Id id = new DocumentFormat.OpenXml.Office2010.Excel.Id + { + Text = (cf as XLConditionalFormat).Id.WrapInBraces() + }; + conditionalFormattingRuleExtension.Append(id); + + conditionalFormattingRuleExtensionList.Append(conditionalFormattingRuleExtension); + + conditionalFormattingRule.Append(conditionalFormattingRuleExtensionList); + } return conditionalFormattingRule; } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs new file mode 100644 index 0000000..3b172f8 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs @@ -0,0 +1,88 @@ +using ClosedXML.Extensions; +using DocumentFormat.OpenXml.Office.Excel; +using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Linq; + +namespace ClosedXML.Excel +{ + internal class XLCFDataBarConverterExtension : IXLCFConverterExtension + { + public XLCFDataBarConverterExtension() + { + } + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context) + { + ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule() + { + Type = DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValues.DataBar, + Id = (cf as XLConditionalFormat).Id.WrapInBraces() + }; + + DataBar dataBar = new DataBar() + { + MinLength = 0, + MaxLength = 100, + Gradient = false, + AxisPosition = DataBarAxisPositionValues.Middle, + ShowValue = !cf.ShowBarOnly + }; + + ConditionalFormattingValueObjectTypeValues cfMinType = Convert(cf.ContentTypes[1].ToOpenXml()); + var cfMin = new ConditionalFormattingValueObject { Type = cfMinType }; + if (cf.Values.Any() && cf.Values[1]?.Value != null) + { + cfMin.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMin.Append(new Formula() { Text = cf.Values[1].Value }); + } + + ConditionalFormattingValueObjectTypeValues cfMaxType = Convert(cf.ContentTypes[2].ToOpenXml()); + var cfMax = new ConditionalFormattingValueObject { Type = cfMaxType }; + if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null) + { + cfMax.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMax.Append(new Formula() { Text = cf.Values[2].Value }); + } + + var barAxisColor = new BarAxisColor { Rgb = XLColor.Black.Color.ToHex() }; + + var negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[1].Color.ToHex() }; + if (cf.Colors.Count == 2) + { + negativeFillColor = new NegativeFillColor { Rgb = cf.Colors[2].Color.ToHex() }; + } + + dataBar.Append(cfMin); + dataBar.Append(cfMax); + + dataBar.Append(negativeFillColor); + dataBar.Append(barAxisColor); + + conditionalFormattingRule.Append(dataBar); + + return conditionalFormattingRule; + } + + private ConditionalFormattingValueObjectTypeValues Convert(DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues obj) + { + switch (obj) + { + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Max: + return ConditionalFormattingValueObjectTypeValues.AutoMax; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Min: + return ConditionalFormattingValueObjectTypeValues.AutoMin; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Number: + return ConditionalFormattingValueObjectTypeValues.Numeric; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percent: + return ConditionalFormattingValueObjectTypeValues.Percent; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percentile: + return ConditionalFormattingValueObjectTypeValues.Percentile; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Formula: + return ConditionalFormattingValueObjectTypeValues.Formula; + default: + throw new NotImplementedException(); + } + } + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs index 4c1f2f8..54364f7 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.EndsWith; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs index bdb78d2..722465d 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs @@ -1,8 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +7,7 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var iconSet = new IconSet {ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()}; Int32 count = cf.Values.Count; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs index 0a37418..a9dac0f 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))=0" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs index 7faacff..eb65890 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs index ebebc5b..753e754 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))>0" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs index 6b46bf4..3e3c081 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.NotContains, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.NotContains; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs index 63f9ba9..108c244 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs index 20cb843..77abaf9 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.BeginsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.BeginsWith; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs index 277144d..3ca8dbc 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,11 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { UInt32 val = UInt32.Parse(cf.Values[1].Value); - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom}; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Percent = cf.Percent; + conditionalFormattingRule.Rank = val; + conditionalFormattingRule.Bottom = cf.Bottom; return conditionalFormattingRule; } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs index da68ca8..f4945bc 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs @@ -1,7 +1,3 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -10,10 +6,9 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (uint)context.DifferentialFormats[cf.Style]; return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 96847d6..03aa8ca 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -1,15 +1,14 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; +using ClosedXML.Utils; namespace ClosedXML.Excel { - internal class XLConditionalFormat: IXLConditionalFormat, IXLStylized + internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized { - public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false) { + Id = Guid.NewGuid(); Range = range; Style = new XLStyle(this, range.Worksheet.Style); Values = new XLDictionary(); @@ -17,32 +16,39 @@ ContentTypes = new XLDictionary(); IconSetOperators = new XLDictionary(); CopyDefaultModify = copyDefaultModify; + } - public XLConditionalFormat(XLConditionalFormat other) + + public XLConditionalFormat(XLConditionalFormat conditionalFormat) { - Range = other.Range; - Style = new XLStyle(this, other.Style); - Values = new XLDictionary(other.Values); - Colors = new XLDictionary(other.Colors); - ContentTypes = new XLDictionary(other.ContentTypes); - IconSetOperators = new XLDictionary(other.IconSetOperators); + Id = Guid.NewGuid(); + Range = conditionalFormat.Range; + Style = new XLStyle(this, conditionalFormat.Style); + Values = new XLDictionary(conditionalFormat.Values); + Colors = new XLDictionary(conditionalFormat.Colors); + ContentTypes = new XLDictionary(conditionalFormat.ContentTypes); + IconSetOperators = new XLDictionary(conditionalFormat.IconSetOperators); - ConditionalFormatType = other.ConditionalFormatType; - TimePeriod = other.TimePeriod; - IconSetStyle = other.IconSetStyle; - Operator = other.Operator; - Bottom = other.Bottom; - Percent = other.Percent; - ReverseIconOrder = other.ReverseIconOrder; - ShowIconOnly = other.ShowIconOnly; - ShowBarOnly = other.ShowBarOnly; + ConditionalFormatType = conditionalFormat.ConditionalFormatType; + TimePeriod = conditionalFormat.TimePeriod; + IconSetStyle = conditionalFormat.IconSetStyle; + Operator = conditionalFormat.Operator; + Bottom = conditionalFormat.Bottom; + Percent = conditionalFormat.Percent; + ReverseIconOrder = conditionalFormat.ReverseIconOrder; + ShowIconOnly = conditionalFormat.ShowIconOnly; + ShowBarOnly = conditionalFormat.ShowBarOnly; + StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrueInternal, true); + + } + public Guid Id { get; internal set; } public Boolean CopyDefaultModify { get; set; } private IXLStyle _style; private Int32 _styleCacheId; - public IXLStyle Style{ get { return GetStyle(); } set { SetStyle(value); } } + public IXLStyle Style { get { return GetStyle(); } set { SetStyle(value); } } private IXLStyle GetStyle() { //return _style; @@ -88,12 +94,20 @@ public XLConditionalFormatType ConditionalFormatType { get; set; } public XLTimePeriod TimePeriod { get; set; } public XLIconSetStyle IconSetStyle { get; set; } - public XLCFOperator Operator { get; set; } - public Boolean Bottom { get; set; } - public Boolean Percent { get; set; } - public Boolean ReverseIconOrder { get; set; } - public Boolean ShowIconOnly { get; set; } - public Boolean ShowBarOnly { get; set; } + public XLCFOperator Operator { get; set; } + public Boolean Bottom { get; set; } + public Boolean Percent { get; set; } + public Boolean ReverseIconOrder { get; set; } + public Boolean ShowIconOnly { get; set; } + public Boolean ShowBarOnly { get; set; } + + internal bool StopIfTrueInternal { get; set; } + + public IXLConditionalFormat StopIfTrue(bool value = true) + { + StopIfTrueInternal = value; + return this; + } public void CopyFrom(IXLConditionalFormat other) { @@ -107,6 +121,7 @@ ReverseIconOrder = other.ReverseIconOrder; ShowIconOnly = other.ShowIconOnly; ShowBarOnly = other.ShowBarOnly; + StopIfTrueInternal = ((XLConditionalFormat)other).StopIfTrueInternal; Values.Clear(); other.Values.ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value))); @@ -279,8 +294,8 @@ return Style; } public IXLStyle WhenBetween(Double minValue, Double maxValue) - { - Values.Initialize(new XLFormula (minValue)); + { + Values.Initialize(new XLFormula(minValue)); Values.Add(new XLFormula(maxValue)); Operator = XLCFOperator.Between; ConditionalFormatType = XLConditionalFormatType.CellIs; @@ -308,7 +323,7 @@ public IXLStyle WhenIsTrue(String formula) { String f = formula.TrimStart()[0] == '=' ? formula : "=" + formula; - Values.Initialize(new XLFormula {Value = f}); + Values.Initialize(new XLFormula { Value = f }); ConditionalFormatType = XLConditionalFormatType.Expression; return Style; } @@ -328,7 +343,7 @@ Bottom = true; return Style; } - + public IXLCFColorScaleMin ColorScale() { ConditionalFormatType = XLConditionalFormatType.ColorScale; @@ -341,6 +356,14 @@ ConditionalFormatType = XLConditionalFormatType.DataBar; return new XLCFDataBarMin(this); } + public IXLCFDataBarMin DataBar(XLColor positiveColor, XLColor negativeColor, Boolean showBarOnly = false) + { + Colors.Initialize(positiveColor); + Colors.Add(negativeColor); + ShowBarOnly = showBarOnly; + ConditionalFormatType = XLConditionalFormatType.DataBar; + return new XLCFDataBarMin(this); + } public IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false) { IconSetOperators.Clear(); diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index 5d1ea42..c968484 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - internal class XLConditionalFormats: IXLConditionalFormats + internal class XLConditionalFormats : IXLConditionalFormats { private readonly List _conditionalFormats = new List(); public void Add(IXLConditionalFormat conditionalFormat) @@ -25,11 +25,13 @@ public void Remove(Predicate predicate) { + _conditionalFormats.Where(cf=>predicate(cf)).ForEach(cf=>cf.Range.Dispose()); _conditionalFormats.RemoveAll(predicate); } public void RemoveAll() { + _conditionalFormats.ForEach(cf => cf.Range.Dispose()); _conditionalFormats.Clear(); } } diff --git a/ClosedXML/Excel/Coordinates/IXLAddress.cs b/ClosedXML/Excel/Coordinates/IXLAddress.cs index bc70a90..6158251 100644 --- a/ClosedXML/Excel/Coordinates/IXLAddress.cs +++ b/ClosedXML/Excel/Coordinates/IXLAddress.cs @@ -5,18 +5,27 @@ { public interface IXLAddress : IEqualityComparer, IEquatable { - IXLWorksheet Worksheet { get; } - Int32 RowNumber { get; } - Int32 ColumnNumber { get; } String ColumnLetter { get; } - Boolean FixedRow { get; } + Int32 ColumnNumber { get; } Boolean FixedColumn { get; } - String ToStringRelative(); - String ToStringRelative(Boolean includeSheet); - String ToStringFixed(); - String ToStringFixed(XLReferenceStyle referenceStyle); - String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); - String ToString(XLReferenceStyle referenceStyle); + Boolean FixedRow { get; } + Int32 RowNumber { get; } String UniqueId { get; } + IXLWorksheet Worksheet { get; } + + String ToString(XLReferenceStyle referenceStyle); + + String ToString(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringFixed(); + + String ToStringFixed(XLReferenceStyle referenceStyle); + + String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringRelative(); + + + String ToStringRelative(Boolean includeSheet); } } diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index b1d7ff8..81f4977 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -7,6 +7,7 @@ internal class XLAddress : IXLAddress { #region Static + /// /// Create address without worksheet. For calculation only! /// @@ -72,22 +73,32 @@ } return new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn); } - #endregion + + #endregion Static + #region Private fields + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private bool _fixedRow; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private bool _fixedColumn; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private string _columnLetter; [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly int _rowNumber; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly int _columnNumber; + private string _trimmedAddress; - #endregion + + #endregion Private fields + #region Constructors + /// /// Initializes a new struct using a mixed notation. Attention: without worksheet for calculation only! /// @@ -99,6 +110,7 @@ : this(null, rowNumber, columnLetter, fixedRow, fixedColumn) { } + /// /// Initializes a new struct using a mixed notation. /// @@ -124,6 +136,7 @@ : this(null, rowNumber, columnNumber, fixedRow, fixedColumn) { } + /// /// Initializes a new struct using R1C1 notation. /// @@ -142,12 +155,14 @@ _columnLetter = null; _fixedColumn = fixedColumn; _fixedRow = fixedRow; - - } - #endregion + + #endregion Constructors + #region Properties + public XLWorksheet Worksheet { get; internal set; } + IXLWorksheet IXLAddress.Worksheet { [DebuggerStepThrough] @@ -195,8 +210,11 @@ { get { return _columnLetter ?? (_columnLetter = XLHelper.GetColumnLetterFromNumber(_columnNumber)); } } - #endregion + + #endregion Properties + #region Overrides + public override string ToString() { String retVal = ColumnLetter; @@ -214,31 +232,45 @@ public string ToString(XLReferenceStyle referenceStyle) { - if (referenceStyle == XLReferenceStyle.A1) - { - return ColumnLetter + _rowNumber.ToInvariantString(); - } - if (referenceStyle == XLReferenceStyle.R1C1) - { - return String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - } - if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) - { - return String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - } - return ColumnLetter + _rowNumber.ToInvariantString(); + return ToString(referenceStyle, false); } - #endregion + + public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) + { + string address = string.Empty; + if (referenceStyle == XLReferenceStyle.A1) + + address = ColumnLetter + _rowNumber.ToInvariantString(); + else if (referenceStyle == XLReferenceStyle.R1C1) + + address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); + else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) + + address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); + else + address = ColumnLetter + _rowNumber.ToInvariantString(); + + if (includeSheet) + return String.Format("{0}!{1}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + address); + + return address; + } + + #endregion Overrides + #region Methods + public string GetTrimmedAddress() { return _trimmedAddress ?? (_trimmedAddress = ColumnLetter + _rowNumber.ToInvariantString()); } + #endregion Methods - - #endregion #region Operator Overloads + public static XLAddress operator +(XLAddress left, XLAddress right) { return new XLAddress(left.Worksheet, @@ -288,9 +320,13 @@ { return !(left == right); } - #endregion + + #endregion Operator Overloads + #region Interface Requirements + #region IEqualityComparer Members + public Boolean Equals(IXLAddress x, IXLAddress y) { return x == y; @@ -315,8 +351,11 @@ { return _rowNumber ^ _columnNumber; } - #endregion + + #endregion IEqualityComparer Members + #region IEquatable Members + public bool Equals(IXLAddress other) { var right = other as XLAddress; @@ -329,10 +368,12 @@ public override Boolean Equals(Object other) { - return Equals((XLAddress) other); + return Equals((XLAddress)other); } - #endregion - #endregion + + #endregion IEquatable Members + + #endregion Interface Requirements public String ToStringRelative() { diff --git a/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs index 94b6850..878f23b 100644 --- a/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs @@ -17,6 +17,6 @@ IXLDrawingStyle SetFontColor(XLColor value); IXLDrawingStyle SetFontName(String value); IXLDrawingStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); - + IXLDrawingStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs index 886e249..744d781 100644 --- a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -29,6 +26,7 @@ public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLDrawingStyle SetBold() { @@ -120,5 +118,10 @@ return _style; } + public IXLDrawingStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _style; + } } } diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index 9266215..b1e44e8 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -147,6 +147,8 @@ { if ((_worksheet.Pictures.FirstOrDefault(p => p.Id.Equals(value)) ?? this) != this) throw new ArgumentException($"The picture ID '{value}' already exists."); + + id = value; } } diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs index 2e5bebe..caf81d2 100644 --- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs +++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs @@ -5,7 +5,7 @@ public partial class XLHyperlink { internal XLHyperlink() - { + { } @@ -36,7 +36,7 @@ else { _internalAddress = address; - IsExternal = false; + IsExternal = false; } } } @@ -51,14 +51,14 @@ internal void SetValues(IXLCell cell, String tooltip) { Tooltip = tooltip; - _internalAddress = cell.Address.ToString(); + _internalAddress = cell.Address.ToString(XLReferenceStyle.A1, true); IsExternal = false; } internal void SetValues(IXLRangeBase range, String tooltip) { Tooltip = tooltip; - _internalAddress = range.RangeAddress.ToString(); + _internalAddress = range.RangeAddress.ToString(XLReferenceStyle.A1, true); IsExternal = false; } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 91bcafd..027dd94 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; namespace ClosedXML.Excel { @@ -106,7 +107,7 @@ IXLPivotFields Fields { get; } IXLPivotFields ReportFilters { get; } - IXLPivotFields ColumnLabels { get; } + IXLPivotFields ColumnLabels { get; } IXLPivotFields RowLabels { get; } IXLPivotValues Values { get; } @@ -114,23 +115,28 @@ String Title { get; set; } String Description { get; set; } + String ColumnHeaderCaption { get; set; } + String RowHeaderCaption { get; set; } + IXLCell TargetCell { get; set; } IXLRange SourceRange { get; set; } + IEnumerable SourceRangeFieldsAvailable { get; } + Boolean MergeAndCenterWithLabels { get; set; } // MergeItem Int32 RowLabelIndent { get; set; } // Indent - XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown + XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown Int32 FilterFieldsPageWrap { get; set; } // PageWrap String ErrorValueReplacement { get; set; } // ErrorCaption String EmptyCellReplacement { get; set; } // MissingCaption - Boolean AutofitColumns { get; set; } //UseAutoFormatting - Boolean PreserveCellFormatting { get; set; } // PreserveFormatting - - Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals - Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals + Boolean AutofitColumns { get; set; } //UseAutoFormatting + Boolean PreserveCellFormatting { get; set; } // PreserveFormatting + + Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals + Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals Boolean FilteredItemsInSubtotals { get; set; } // Subtotal filtered page items - Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters - Boolean UseCustomListsForSorting { get; set; } // CustomListSort + Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters + Boolean UseCustomListsForSorting { get; set; } // CustomListSort Boolean ShowExpandCollapseButtons { get; set; } Boolean ShowContextualTooltips { get; set; } @@ -193,6 +199,9 @@ IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value); IXLPivotTable SetEnableCellEditing(); IXLPivotTable SetEnableCellEditing(Boolean value); + IXLPivotTable SetColumnHeaderCaption(String value); + IXLPivotTable SetRowHeaderCaption(String value); + Boolean ShowRowHeaders { get; set; } Boolean ShowColumnHeaders { get; set; } Boolean ShowRowStripes { get; set; } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index 4e09f16..ef16caf 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -32,6 +32,9 @@ } public IXLPivotValue Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; _pivotValues.Add(customName, pivotValue); diff --git a/ClosedXML/Excel/PivotTables/XLPivotFields.cs b/ClosedXML/Excel/PivotTables/XLPivotFields.cs index 19accb7..d35e0e8 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotFields.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotFields.cs @@ -4,9 +4,16 @@ namespace ClosedXML.Excel { - public class XLPivotFields : IXLPivotFields + internal class XLPivotFields : IXLPivotFields { + private readonly Dictionary _pivotFields = new Dictionary(); + private readonly IXLPivotTable _pivotTable; + + internal XLPivotFields(IXLPivotTable pivotTable) + { + this._pivotTable = pivotTable; + } public IXLPivotField Add(String sourceName) { @@ -15,6 +22,9 @@ public IXLPivotField Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotField = new XLPivotField(sourceName) { CustomName = customName }; _pivotFields.Add(sourceName, pivotField); return pivotField; @@ -49,7 +59,8 @@ { var selectedItem = _pivotFields.Select((item, index) => new { Item = item, Position = index }).FirstOrDefault(i => i.Item.Key == pf.SourceName); if (selectedItem == null) - throw new IndexOutOfRangeException("Invalid field name."); + throw new ArgumentNullException(nameof(pf), "Invalid field name."); + return selectedItem.Position; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index acb4a84..6e7c1f0 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -10,10 +10,10 @@ public XLPivotTable() { - Fields = new XLPivotFields(); - ReportFilters = new XLPivotFields(); - ColumnLabels=new XLPivotFields(); - RowLabels = new XLPivotFields(); + Fields = new XLPivotFields(this); + ReportFilters = new XLPivotFields(this); + ColumnLabels=new XLPivotFields(this); + RowLabels = new XLPivotFields(this); Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; @@ -22,6 +22,10 @@ public IXLCell TargetCell { get; set; } public IXLRange SourceRange { get; set; } + public IEnumerable SourceRangeFieldsAvailable + { + get { return this.SourceRange.FirstRow().Cells().Select(c => c.GetString()); } + } public IXLPivotFields Fields { get; private set; } public IXLPivotFields ReportFilters { get; private set; } @@ -34,6 +38,20 @@ public String Title { get; set; } public IXLPivotTable SetTitle(String value) { Title = value; return this; } public String Description { get; set; } public IXLPivotTable SetDescription(String value) { Description = value; return this; } + public String ColumnHeaderCaption { get; set; } + public IXLPivotTable SetColumnHeaderCaption(String value) + { + ColumnHeaderCaption = value; + return this; + } + + public String RowHeaderCaption { get; set; } + public IXLPivotTable SetRowHeaderCaption(String value) + { + RowHeaderCaption = value; + return this; + } + public Boolean MergeAndCenterWithLabels { get; set; } public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } public Int32 RowLabelIndent { get; set; } public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } public XLFilterAreaOrder FilterAreaOrder { get; set; } public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index 992ebf9..945c0c9 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -46,7 +46,7 @@ /// /// Gets the specified column of the range. /// - /// Column letter. + /// Column letter. IXLRangeColumn Column(string columnLetter); /// diff --git a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 9da98e4..86ed2b1 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -4,8 +4,6 @@ { public interface IXLRangeAddress { - IXLWorksheet Worksheet { get; } - /// /// Gets or sets the first address in the range. /// @@ -13,13 +11,7 @@ /// The first address. /// IXLAddress FirstAddress { get; set; } - /// - /// Gets or sets the last address in the range. - /// - /// - /// The last address. - /// - IXLAddress LastAddress { get; set; } + /// /// Gets or sets a value indicating whether this range is invalid. /// @@ -28,10 +20,28 @@ /// Boolean IsInvalid { get; set; } - String ToStringRelative(); - String ToStringRelative(Boolean includeSheet); + /// + /// Gets or sets the last address in the range. + /// + /// + /// The last address. + /// + IXLAddress LastAddress { get; set; } + + IXLWorksheet Worksheet { get; } + + String ToString(XLReferenceStyle referenceStyle); + + String ToString(XLReferenceStyle referenceStyle, Boolean includeSheet); + String ToStringFixed(); + String ToStringFixed(XLReferenceStyle referenceStyle); + String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringRelative(); + + String ToStringRelative(Boolean includeSheet); } } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 064d975..b0dd238 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -262,10 +262,71 @@ IXLAutoFilter SetAutoFilter(); + IXLAutoFilter SetAutoFilter(Boolean value); + IXLDataValidation SetDataValidation(); IXLConditionalFormat AddConditionalFormat(); void Select(); + + /// + /// Grows this the current range by one cell to each side + /// + IXLRangeBase Grow(); + + /// + /// Grows this the current range by the specified number of cells to each side. + /// + /// The grow count. + /// + IXLRangeBase Grow(Int32 growCount); + + /// + /// Shrinks this current range by one cell. + /// + IXLRangeBase Shrink(); + + /// + /// Shrinks the current range by the specified number of cells from each side. + /// + /// The shrink count. + /// + IXLRangeBase Shrink(Int32 shrinkCount); + + /// + /// Returns the intersection of this range with another range on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns the set of cells surrounding the current range. + /// + /// The predicate to apply on the resulting set of cells. + IXLCells SurroundingCells(Func predicate = null); + + /// + /// Calculates the union of two ranges on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + /// The union + /// + IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns all cells in the current range that are not in the other range. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index fbe5135..13f986a 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -1,7 +1,6 @@ using ClosedXML.Extensions; using System; using System.Diagnostics; -using System.Globalization; using System.Linq; namespace ClosedXML.Excel @@ -10,16 +9,18 @@ { #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _firstAddress; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _lastAddress; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private XLAddress _firstAddress; - #endregion + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private XLAddress _lastAddress; + + #endregion Private fields #region Constructor - public XLRangeAddress(XLRangeAddress rangeAddress): this(rangeAddress.FirstAddress, rangeAddress.LastAddress) + public XLRangeAddress(XLRangeAddress rangeAddress) : this(rangeAddress.FirstAddress, rangeAddress.LastAddress) { - } public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) @@ -73,7 +74,7 @@ Worksheet = worksheet; } - #endregion + #endregion Constructor #region Public properties @@ -122,10 +123,9 @@ set { LastAddress = value as XLAddress; } } - public bool IsInvalid { get; set; } - #endregion + #endregion Public properties #region Public methods @@ -168,7 +168,20 @@ public override string ToString() { - return _firstAddress + ":" + _lastAddress; + return String.Concat(_firstAddress, ':', _lastAddress); + } + + public string ToString(XLReferenceStyle referenceStyle) + { + return ToString(referenceStyle, false); + } + + public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) + { + if (referenceStyle == XLReferenceStyle.R1C1) + return ToStringFixed(referenceStyle, true); + else + return ToStringRelative(includeSheet); } public override bool Equals(object obj) @@ -187,6 +200,6 @@ ^ LastAddress.GetHashCode(); } - #endregion + #endregion Public methods } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 0534107..ab227be 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -803,14 +803,18 @@ if (absRow <= 0 || absRow > XLHelper.MaxRowNumber) { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - XLHelper.MaxRowNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber) + ); } if (absColumn <= 0 || absColumn > XLHelper.MaxColumnNumber) { - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", - XLHelper.MaxColumnNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber) + ); } var cell = Worksheet.Internals.CellsCollection.GetCell(absRow, @@ -841,12 +845,14 @@ Int32 newCellStyleId = styleId; + XLCell newCell; // If the default style for this range base is empty, but the worksheet // has a default style, use the worksheet's default style - if (styleId == 0 && worksheetStyleId != 0) - newCellStyleId = worksheetStyleId; + if (styleId == 0 && worksheetStyleId != 0 || styleId == worksheetStyleId) + newCell = new XLCell(Worksheet, absoluteAddress); + else + newCell = new XLCell(Worksheet, absoluteAddress, newCellStyleId); - var newCell = new XLCell(Worksheet, absoluteAddress, newCellStyleId); Worksheet.Internals.CellsCollection.Add(absRow, absColumn, newCell); return newCell; } @@ -1208,7 +1214,8 @@ - model.RangeAddress.FirstAddress.RowNumber + 1; for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) { - rangeToReturn.Row(ro).Style = model.Cell(ro).Style; + using (var row = rangeToReturn.Row(ro)) + row.Style = model.Cell(ro).Style; } } } @@ -1225,13 +1232,17 @@ var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(ro) ? Worksheet.Internals.RowsCollection[ro].Style : Worksheet.Style; - rangeToReturn.Row(ro).Style = styleToUse; + using (var row = rangeToReturn.Row(ro)) + row.Style = styleToUse; } } } if (nullReturn) + { + rangeToReturn.Dispose(); return null; + } return rangeToReturn.Columns(); } @@ -1447,7 +1458,8 @@ - model.RangeAddress.FirstAddress.ColumnNumber + 1; for (Int32 co = firstCoReturned; co <= lastCoReturned; co++) { - rangeToReturn.Column(co).Style = model.Cell(co).Style; + using (var column = rangeToReturn.Column(co)) + column.Style = model.Cell(co).Style; } } } @@ -1464,14 +1476,18 @@ var styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(co) ? Worksheet.Internals.ColumnsCollection[co].Style : Worksheet.Style; - rangeToReturn.Column(co).Style = styleToUse; + using (var column = rangeToReturn.Column(co)) + column.Style = styleToUse; } } } // Skip calling .Rows() for performance reasons if required. if (nullReturn) + { + rangeToReturn.Dispose(); return null; + } return rangeToReturn.Rows(); } @@ -1755,8 +1771,16 @@ public IXLAutoFilter SetAutoFilter() { - using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return SetAutoFilter(true); + } + + public IXLAutoFilter SetAutoFilter(Boolean value) + { + if (value) + using (var asRange = AsRange()) + return Worksheet.AutoFilter.Set(asRange); + else + return Worksheet.AutoFilter.Clear(); } #region Sort @@ -2065,5 +2089,104 @@ { Worksheet.SelectedRanges.Add(AsRange()); } + + public IXLRangeBase Grow() + { + return Grow(1); + } + + public IXLRangeBase Grow(int growCount) + { + var firstRow = Math.Max(1, this.RangeAddress.FirstAddress.RowNumber - growCount); + var firstColumn = Math.Max(1, this.RangeAddress.FirstAddress.ColumnNumber - growCount); + + var lastRow = Math.Min(XLHelper.MaxRowNumber, this.RangeAddress.LastAddress.RowNumber + growCount); + var lastColumn = Math.Min(XLHelper.MaxColumnNumber, this.RangeAddress.LastAddress.ColumnNumber + growCount); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Shrink() + { + return Shrink(1); + } + + public IXLRangeBase Shrink(int shrinkCount) + { + var firstRow = this.RangeAddress.FirstAddress.RowNumber + shrinkCount; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + shrinkCount; + + var lastRow = this.RangeAddress.LastAddress.RowNumber - shrinkCount; + var lastColumn = this.RangeAddress.LastAddress.ColumnNumber - shrinkCount; + + if (firstRow > lastRow || firstColumn > lastColumn) + return null; + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return null; + + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return null; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + var intersectionCells = this.Cells(c => thisRangePredicate(c) && otherRange.Cells(otherRangePredicate).Contains(c)); + + if (!intersectionCells.Any()) + return null; + + var firstRow = intersectionCells.Min(c => c.Address.RowNumber); + var firstColumn = intersectionCells.Min(c => c.Address.ColumnNumber); + + var lastRow = intersectionCells.Max(c => c.Address.RowNumber); + var lastColumn = intersectionCells.Max(c => c.Address.ColumnNumber); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLCells SurroundingCells(Func predicate = null) + { + var cells = new XLCells(false, false, predicate); + this.Grow().Cells(c => !this.Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(thisRangePredicate).Concat(otherRange.Cells(otherRangePredicate)).Distinct().ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(c => thisRangePredicate(c) && !otherRange.Cells(otherRangePredicate).Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index b783762..2fc41c0 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -72,6 +72,11 @@ } } + public Boolean Contains(IXLCell cell) + { + return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(cell)); + } + public Boolean Contains(IXLRange range) { return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range)); diff --git a/ClosedXML/Excel/RichText/IXLPhonetics.cs b/ClosedXML/Excel/RichText/IXLPhonetics.cs index 0330b61..67cd96d 100644 --- a/ClosedXML/Excel/RichText/IXLPhonetics.cs +++ b/ClosedXML/Excel/RichText/IXLPhonetics.cs @@ -17,6 +17,7 @@ IXLPhonetics SetFontColor(XLColor value); IXLPhonetics SetFontName(String value); IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLPhonetics SetFontCharSet(XLFontCharSet value); IXLPhonetics Add(String text, Int32 start, Int32 end); IXLPhonetics ClearText(); diff --git a/ClosedXML/Excel/RichText/IXLRichString.cs b/ClosedXML/Excel/RichText/IXLRichString.cs index b41b537..a090460 100644 --- a/ClosedXML/Excel/RichText/IXLRichString.cs +++ b/ClosedXML/Excel/RichText/IXLRichString.cs @@ -10,7 +10,7 @@ public interface IXLRichString: IXLFontBase, IEquatable, IXLWithRichString { String Text { get; set; } - + IXLRichString SetBold(); IXLRichString SetBold(Boolean value); IXLRichString SetItalic(); IXLRichString SetItalic(Boolean value); @@ -22,5 +22,6 @@ IXLRichString SetFontColor(XLColor value); IXLRichString SetFontName(String value); IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLRichString SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/RichText/XLPhonetics.cs b/ClosedXML/Excel/RichText/XLPhonetics.cs index 2cf1e02..b048267 100644 --- a/ClosedXML/Excel/RichText/XLPhonetics.cs +++ b/ClosedXML/Excel/RichText/XLPhonetics.cs @@ -1,6 +1,6 @@ using System; -using System.Linq; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel { @@ -8,7 +8,8 @@ { private readonly List _phonetics = new List(); - readonly IXLFontBase _defaultFont; + private readonly IXLFontBase _defaultFont; + public XLPhonetics(IXLFontBase defaultFont) { _defaultFont = defaultFont; @@ -22,7 +23,7 @@ _defaultFont = defaultFont; Type = defaultPhonetics.Type; Alignment = defaultPhonetics.Alignment; - + this.CopyFont(defaultPhonetics); } @@ -36,28 +37,52 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } - public IXLPhonetics SetBold() { Bold = true; return this; } public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } - public IXLPhonetics SetItalic() { Italic = true; return this; } public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } - public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } - public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetBold() { Bold = true; return this; } + + public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } + + public IXLPhonetics SetItalic() { Italic = true; return this; } + + public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } + + public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } + + public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } + + public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } + + public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) { VerticalAlignment = value; return this; } - public IXLPhonetics SetShadow() { Shadow = true; return this; } public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + + public IXLPhonetics SetShadow() { Shadow = true; return this; } + + public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + public IXLPhonetics SetFontSize(Double value) { FontSize = value; return this; } + public IXLPhonetics SetFontColor(XLColor value) { FontColor = value; return this; } + public IXLPhonetics SetFontName(String value) { FontName = value; return this; } + public IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLPhonetics SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } + public IXLPhonetics Add(String text, Int32 start, Int32 end) { _phonetics.Add(new XLPhonetic(text, start, end)); return this; } + public IXLPhonetics ClearText() { _phonetics.Clear(); return this; } + public IXLPhonetics ClearFont() { this.CopyFont(_defaultFont); @@ -70,6 +95,7 @@ public XLPhoneticType Type { get; set; } public IXLPhonetics SetAlignment(XLPhoneticAlignment phoneticAlignment) { Alignment = phoneticAlignment; return this; } + public IXLPhonetics SetType(XLPhoneticType phoneticType) { Type = phoneticType; return this; } public IEnumerator GetEnumerator() @@ -104,8 +130,7 @@ && FontSize == other.FontSize && FontColor.Equals(other.FontColor) && FontName == other.FontName - && FontFamilyNumbering == other.FontFamilyNumbering - ; + && FontFamilyNumbering == other.FontFamilyNumbering; } } } diff --git a/ClosedXML/Excel/RichText/XLRichString.cs b/ClosedXML/Excel/RichText/XLRichString.cs index b9e9cdd..9837fdb 100644 --- a/ClosedXML/Excel/RichText/XLRichString.cs +++ b/ClosedXML/Excel/RichText/XLRichString.cs @@ -33,6 +33,7 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLRichString SetBold() { Bold = true; return this; } public IXLRichString SetBold(Boolean value) { Bold = value; return this; } public IXLRichString SetItalic() { Italic = true; return this; } public IXLRichString SetItalic(Boolean value) { Italic = value; return this; } @@ -44,6 +45,7 @@ public IXLRichString SetFontColor(XLColor value) { FontColor = value; return this; } public IXLRichString SetFontName(String value) { FontName = value; return this; } public IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLRichString SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } public Boolean Equals(IXLRichString other) { diff --git a/ClosedXML/Excel/Style/IXLFont.cs b/ClosedXML/Excel/Style/IXLFont.cs index 2262d35..a996b62 100644 --- a/ClosedXML/Excel/Style/IXLFont.cs +++ b/ClosedXML/Excel/Style/IXLFont.cs @@ -19,7 +19,7 @@ } public enum XLFontFamilyNumberingValues - { + { NotApplicable = 0, Roman = 1, Swiss = 2, @@ -28,18 +28,131 @@ Decorative = 5 } + public enum XLFontCharSet + { + /// + /// ASCII character set. + /// + Ansi = 0, + + /// + /// System default character set. + /// + Default = 1, + + /// + /// Symbol character set. + /// + Symbol = 2, + + /// + /// Characters used by Macintosh. + /// + Mac = 77, + + /// + /// Japanese character set. + /// + ShiftJIS = 128, + + /// + /// Korean character set. + /// + Hangul = 129, + + /// + /// Another common spelling of the Korean character set. + /// + Hangeul = 129, + + /// + /// Korean character set. + /// + Johab = 130, + + /// + /// Chinese character set used in mainland China. + /// + GB2312 = 134, + + /// + /// Chinese character set used mostly in Hong Kong SAR and Taiwan. + /// + ChineseBig5 = 136, + + /// + /// Greek character set. + /// + Greek = 161, + + /// + /// Turkish character set. + /// + Turkish = 162, + + /// + /// Vietnamese character set. + /// + Vietnamese = 163, + + /// + /// Hebrew character set. + /// + Hebrew = 177, + + /// + /// Arabic character set. + /// + Arabic = 178, + + /// + /// Baltic character set. + /// + Baltic = 186, + + /// + /// Russian character set. + /// + Russian = 204, + + /// + /// Thai character set. + /// + Thai = 222, + + /// + /// Eastern European character set. + /// + EastEurope = 238, + + /// + /// Extended ASCII character set used with disk operating system (DOS) and some Microsoft Windows fonts. + /// + Oem = 255 + } + public interface IXLFont : IXLFontBase, IEquatable { IXLStyle SetBold(); IXLStyle SetBold(Boolean value); + IXLStyle SetItalic(); IXLStyle SetItalic(Boolean value); + IXLStyle SetUnderline(); IXLStyle SetUnderline(XLFontUnderlineValues value); + IXLStyle SetStrikethrough(); IXLStyle SetStrikethrough(Boolean value); + IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value); + IXLStyle SetShadow(); IXLStyle SetShadow(Boolean value); + IXLStyle SetFontSize(Double value); + IXLStyle SetFontColor(XLColor value); + IXLStyle SetFontName(String value); + IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Style/IXLFontBase.cs b/ClosedXML/Excel/Style/IXLFontBase.cs index 11d8a72..629cab1 100644 --- a/ClosedXML/Excel/Style/IXLFontBase.cs +++ b/ClosedXML/Excel/Style/IXLFontBase.cs @@ -2,7 +2,7 @@ namespace ClosedXML.Excel { - + public interface IXLFontBase { @@ -16,7 +16,6 @@ XLColor FontColor { get; set; } String FontName { get; set; } XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } - - + XLFontCharSet FontCharSet { get; set; } } } diff --git a/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/Excel/Style/XLFont.cs index 3c52c20..87915b4 100644 --- a/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/Excel/Style/XLFont.cs @@ -9,6 +9,7 @@ private Boolean _bold; private XLColor _fontColor; private XLFontFamilyNumberingValues _fontFamilyNumbering; + private XLFontCharSet _fontCharSet; private String _fontName; private Double _fontSize; private Boolean _italic; @@ -37,6 +38,7 @@ _fontColor = defaultFont.FontColor; _fontName = defaultFont.FontName; _fontFamilyNumbering = defaultFont.FontFamilyNumbering; + _fontCharSet = defaultFont.FontCharSet; if (useDefaultModify) { @@ -52,6 +54,7 @@ FontColorModified = d.FontColorModified; FontNameModified = d.FontNameModified; FontFamilyNumberingModified = d.FontFamilyNumberingModified; + FontCharSetModified = d.FontCharSetModified; } } @@ -105,7 +108,7 @@ _underline = value; UnderlineModified = true; } - + } } @@ -236,6 +239,23 @@ } } + public Boolean FontCharSetModified { get; set; } + public XLFontCharSet FontCharSet + { + get { return _fontCharSet; } + set + { + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontCharSet = value); + else + { + _fontCharSet = value; + FontCharSetModified = true; + } + } + } + public IXLStyle SetBold() { Bold = true; @@ -326,6 +346,12 @@ return _container.Style; } + public IXLStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _container.Style; + } + public Boolean Equals(IXLFont other) { var otherF = other as XLFont; @@ -397,4 +423,4 @@ ^ (Int32)FontFamilyNumbering; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Style/XLPredefinedFormat.cs b/ClosedXML/Excel/Style/XLPredefinedFormat.cs new file mode 100644 index 0000000..32c6e1a --- /dev/null +++ b/ClosedXML/Excel/Style/XLPredefinedFormat.cs @@ -0,0 +1,165 @@ +namespace ClosedXML.Excel +{ + /// + /// Reference point of date/number formats available. + /// See more at: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx + /// + public static class XLPredefinedFormat + { + public enum Number + { + /// + /// General + /// + General = 0, + + /// + /// 0 + /// + Integer = 1, + + /// + /// 0.00 + /// + Precision2 = 2, + + /// + /// #,##0 + /// + IntegerWithSeparator = 3, + + /// + /// #,##0.00 + /// + Precision2WithSeparator = 4, + + /// + /// 0% + /// + PercentInteger = 9, + + /// + /// 0.00% + /// + PercentPrecision2 = 10, + + /// + /// 0.00E+00 + /// + ScientificPrecision2 = 11, + + /// + /// # ?/? + /// + FractionPrecision1 = 12, + + /// + /// # ??/?? + /// + FractionPrecision2 = 13, + + /// + /// #,##0 ,(#,##0) + /// + IntegerWithSeparatorAndParens = 37, + + /// + /// #,##0 ,[Red](#,##0) + /// + IntegerWithSeparatorAndParensRed = 38, + + /// + /// #,##0.00,(#,##0.00) + /// + Precision2WithSeparatorAndParens = 39, + + /// + /// #,##0.00,[Red](#,##0.00) + /// + Precision2WithSeparatorAndParensRed = 40, + + /// + /// ##0.0E+0 + /// + ScientificUpToHundredsAndPrecision1 = 48, + + /// + /// @ + /// + Text = 49 + } + + public enum DateTime + { + /// + /// General + /// + General = 0, + + /// + /// d/m/yyyy + /// + DayMonthYear4WithSlashes = 14, + + /// + /// d-mmm-yy + /// + DayMonthAbbrYear2WithDashes = 15, + + /// + /// d-mmm + /// + DayMonthAbbrWithDash = 16, + + /// + /// mmm-yy + /// + MonthAbbrYear2WithDash = 17, + + /// + /// h:mm tt + /// + Hour12MinutesAmPm = 18, + + /// + /// h:mm:ss tt + /// + Hour12MinutesSecondsAmPm = 19, + + /// + /// H:mm + /// + Hour24Minutes = 20, + + /// + /// H:mm:ss + /// + Hour24MinutesSeconds = 21, + + /// + /// m/d/yyyy H:mm + /// + MonthDayYear4WithDashesHour24Minutes = 22, + + /// + /// mm:ss + /// + MinutesSeconds = 45, + + /// + /// [h]:mm:ss + /// + Hour12MinutesSeconds = 46, + + /// + /// mmss.0 + /// + MinutesSecondsMillis1 = 47, + + /// + /// @ + /// + Text = 49 + } + } +} diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 386c669..9a6fa97 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,31 +1,23 @@ using System; using System.Collections.Generic; +using System.Data; + namespace ClosedXML.Excel { public interface IXLTable : IXLRange { - string Name { get; set; } + IXLBaseAutoFilter AutoFilter { get; } + IXLTableRange DataRange { get; } Boolean EmphasizeFirstColumn { get; set; } Boolean EmphasizeLastColumn { get; set; } - Boolean ShowRowStripes { get; set; } - Boolean ShowColumnStripes { get; set; } - Boolean ShowTotalsRow { get; set; } - Boolean ShowAutoFilter { get; set; } - XLTableTheme Theme { get; set; } - IXLRangeRow HeadersRow(); - IXLRangeRow TotalsRow(); - IXLTableField Field(string fieldName); - IXLTableField Field(int fieldIndex); IEnumerable Fields { get; } - - - - IXLTable SetEmphasizeFirstColumn(); IXLTable SetEmphasizeFirstColumn(Boolean value); - IXLTable SetEmphasizeLastColumn(); IXLTable SetEmphasizeLastColumn(Boolean value); - IXLTable SetShowRowStripes(); IXLTable SetShowRowStripes(Boolean value); - IXLTable SetShowColumnStripes(); IXLTable SetShowColumnStripes(Boolean value); - IXLTable SetShowTotalsRow(); IXLTable SetShowTotalsRow(Boolean value); - IXLTable SetShowAutoFilter(); IXLTable SetShowAutoFilter(Boolean value); + string Name { get; set; } + Boolean ShowAutoFilter { get; set; } + Boolean ShowColumnStripes { get; set; } + Boolean ShowHeaderRow { get; set; } + Boolean ShowRowStripes { get; set; } + Boolean ShowTotalsRow { get; set; } + XLTableTheme Theme { get; set; } /// /// Clears the contents of this table. @@ -33,13 +25,109 @@ /// Specify what you want to clear. new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); - IXLBaseAutoFilter AutoFilter { get; } + IXLTableField Field(string fieldName); + + IXLTableField Field(int fieldIndex); + + IXLRangeRow HeadersRow(); + + /// + /// Resizes the table to the specified range. + /// + /// The new table range. + /// + IXLTable Resize(IXLRange range); + + /// + /// Resizes the table to the specified range address. + /// + /// The range boundaries. + /// + IXLTable Resize(IXLRangeAddress rangeAddress); + + /// + /// Resizes the table to the specified range address. + /// + /// The range boundaries. + /// + IXLTable Resize(string rangeAddress); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell in the range. + /// The last cell in the range. + /// + IXLTable Resize(IXLCell firstCell, IXLCell lastCell); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell address in the worksheet. + /// The last cell address in the worksheet. + /// + IXLTable Resize(string firstCellAddress, string lastCellAddress); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell address in the worksheet. + /// The last cell address in the worksheet. + /// + IXLTable Resize(IXLAddress firstCellAddress, IXLAddress lastCellAddress); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell's row of the range to return. + /// The first cell's column of the range to return. + /// The last cell's row of the range to return. + /// The last cell's column of the range to return. + /// + IXLTable Resize(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); new IXLBaseAutoFilter SetAutoFilter(); - Boolean ShowHeaderRow { get; set; } - IXLTable SetShowHeaderRow(); IXLTable SetShowHeaderRow(Boolean value); + IXLTable SetEmphasizeFirstColumn(); - IXLTableRange DataRange { get; } + IXLTable SetEmphasizeFirstColumn(Boolean value); + + IXLTable SetEmphasizeLastColumn(); + + IXLTable SetEmphasizeLastColumn(Boolean value); + + IXLTable SetShowAutoFilter(); + + IXLTable SetShowAutoFilter(Boolean value); + + IXLTable SetShowColumnStripes(); + + IXLTable SetShowColumnStripes(Boolean value); + + IXLTable SetShowHeaderRow(); + + IXLTable SetShowHeaderRow(Boolean value); + + IXLTable SetShowRowStripes(); + + IXLTable SetShowRowStripes(Boolean value); + + IXLTable SetShowTotalsRow(); + + IXLTable SetShowTotalsRow(Boolean value); + + IXLRangeRow TotalsRow(); + + /// + /// Converts the table to an enumerable of dynamic objects + /// + /// + IEnumerable AsDynamicEnumerable(); + + /// + /// Converts the table to a standard .NET System.Data.DataTable + /// + /// + DataTable AsNativeDataTable(); } } \ No newline at end of file diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 91ba4e8..abd8029 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -27,5 +27,10 @@ String TotalsRowLabel { get; set; } void Delete(); + + /// + /// Determines whether all cells this table field have a consistent data type. + /// + Boolean IsConsistentDataType(); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 6048e33..6eefffc 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,5 +1,7 @@ using System; using System.Collections.Generic; +using System.Data; +using System.Dynamic; using System.Linq; using System.Text; @@ -54,43 +56,76 @@ { get { - if (_fieldNames != null && _lastRangeAddress != null && _lastRangeAddress.Equals(RangeAddress)) return _fieldNames; + if (_fieldNames != null && _lastRangeAddress != null && _lastRangeAddress.Equals(RangeAddress)) + return _fieldNames; - _fieldNames = new Dictionary(); - _lastRangeAddress = RangeAddress; - - if (ShowHeaderRow) + if (_fieldNames == null) { - var headersRow = HeadersRow(); - Int32 cellPos = 0; - foreach (var cell in headersRow.Cells()) - { - var name = cell.GetString(); - if (String.IsNullOrWhiteSpace(name)) - { - name = "Column" + (cellPos + 1); - cell.SetValue(name); - } - if (_fieldNames.ContainsKey(name)) - throw new ArgumentException("The header row contains more than one field name '" + name + "'."); - - _fieldNames.Add(name, new XLTableField(this, name) { Index = cellPos++ }); - } + _fieldNames = new Dictionary(); + _lastRangeAddress = RangeAddress; + HeadersRow(); } else { - Int32 colCount = ColumnCount(); - for (Int32 i = 1; i <= colCount; i++) - { - if (!_fieldNames.Values.Any(f => f.Index == i - 1)) - { - var name = "Column" + i; + HeadersRow(false); + } - _fieldNames.Add(name, new XLTableField(this, name) { Index = i - 1 }); - } + RescanFieldNames(); + + _lastRangeAddress = RangeAddress; + + return _fieldNames; + } + } + + private void RescanFieldNames() + { + if (ShowHeaderRow) + { + var detectedFieldNames = new Dictionary(); + var headersRow = HeadersRow(false); + Int32 cellPos = 0; + foreach (var cell in headersRow.Cells()) + { + var name = cell.GetString(); + if (_fieldNames.ContainsKey(name) && _fieldNames[name].Column.ColumnNumber() == cell.Address.ColumnNumber) + { + (_fieldNames[name] as XLTableField).Index = cellPos; + detectedFieldNames.Add(name, _fieldNames[name]); + cellPos++; + continue; + } + + if (String.IsNullOrWhiteSpace(name)) + { + name = GetUniqueName("Column", cellPos + 1, true); + cell.SetValue(name); + cell.DataType = XLCellValues.Text; + } + if (_fieldNames.ContainsKey(name)) + throw new ArgumentException("The header row contains more than one field name '" + name + "'."); + + _fieldNames.Add(name, new XLTableField(this, name) { Index = cellPos++ }); + detectedFieldNames.Add(name, _fieldNames[name]); + } + + _fieldNames.Keys + .Where(key => !detectedFieldNames.ContainsKey(key)) + .ToArray() + .ForEach(key => _fieldNames.Remove(key)); + } + else + { + Int32 colCount = ColumnCount(); + for (Int32 i = 1; i <= colCount; i++) + { + if (!_fieldNames.Values.Any(f => f.Index == i - 1)) + { + var name = "Column" + i; + + _fieldNames.Add(name, new XLTableField(this, name) { Index = i - 1 }); } } - return _fieldNames; } } @@ -122,9 +157,6 @@ get { XLRange range; - //var ws = Worksheet; - //var tracking = ws.EventTrackingEnabled; - //ws.EventTrackingEnabled = false; if (_showHeaderRow) { @@ -138,7 +170,7 @@ ? Range(1, 1, RowCount() - 1, ColumnCount()) : Range(1, 1, RowCount(), ColumnCount()); } - //ws.EventTrackingEnabled = tracking; + return new XLTableRange(range, this); } } @@ -222,9 +254,18 @@ public IXLRangeRow HeadersRow() { + return HeadersRow(true); + } + + internal IXLRangeRow HeadersRow(Boolean scanForNewFieldsNames) + { if (!ShowHeaderRow) return null; - var m = FieldNames; + if (scanForNewFieldsNames) + { + var tempResult = FieldNames; + } + return FirstRow(); } @@ -253,6 +294,124 @@ } } + public IXLTable Resize(IXLRangeAddress rangeAddress) + { + return Resize(Worksheet.Range(RangeAddress)); + } + + public IXLTable Resize(string rangeAddress) + { + return Resize(Worksheet.Range(RangeAddress)); + } + + public IXLTable Resize(IXLCell firstCell, IXLCell lastCell) + { + return Resize(Worksheet.Range(firstCell, lastCell)); + } + + public IXLTable Resize(string firstCellAddress, string lastCellAddress) + { + return Resize(Worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public IXLTable Resize(IXLAddress firstCellAddress, IXLAddress lastCellAddress) + { + return Resize(Worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public IXLTable Resize(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) + { + return Resize(Worksheet.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn)); + } + + public IXLTable Resize(IXLRange range) + { + if (!this.ShowHeaderRow) + throw new NotImplementedException("Resizing of tables with no headers not supported yet."); + + if (this.Worksheet != range.Worksheet) + throw new InvalidOperationException("You cannot resize a table to a range on a different sheet."); + + var totalsRowChanged = this.ShowTotalsRow ? range.LastRow().RowNumber() - this.TotalsRow().RowNumber() : 0; + var oldTotalsRowNumber = this.ShowTotalsRow ? this.TotalsRow().RowNumber() : -1; + + var existingHeaders = this.FieldNames.Keys; + var newHeaders = new HashSet(); + var tempArray = this.Fields.Select(f => f.Column).ToArray(); + + var firstRow = range.Row(1); + if (!firstRow.FirstCell().Address.Equals(this.HeadersRow().FirstCell().Address) + || !firstRow.LastCell().Address.Equals(this.HeadersRow().LastCell().Address)) + { + _uniqueNames.Clear(); + var co = 1; + foreach (var c in firstRow.Cells()) + { + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column", co, true); + + var header = c.GetString(); + _uniqueNames.Add(header); + + if (!existingHeaders.Contains(header)) + newHeaders.Add(header); + + co++; + } + } + + if (totalsRowChanged < 0) + { + range.Rows(r => r.RowNumber().Equals(this.TotalsRow().RowNumber() + totalsRowChanged)).Single().InsertRowsAbove(1); + range = Worksheet.Range(range.FirstCell(), range.LastCell().CellAbove()); + oldTotalsRowNumber++; + } + else if (totalsRowChanged > 0) + { + this.TotalsRow().RowBelow(totalsRowChanged + 1).InsertRowsAbove(1); + this.TotalsRow().AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); + } + + this.RangeAddress = range.RangeAddress as XLRangeAddress; + RescanFieldNames(); + + if (this.ShowTotalsRow) + { + foreach (var f in this._fieldNames.Values) + { + var c = this.TotalsRow().Cell(f.Index + 1); + if (!c.IsEmpty() && newHeaders.Contains(f.Name)) + { + f.TotalsRowLabel = c.GetFormattedString(); + c.DataType = XLCellValues.Text; + } + } + + if (totalsRowChanged != 0) + { + foreach (var f in this._fieldNames.Values.Cast()) + { + f.UpdateUnderlyingCellFormula(); + var c = this.TotalsRow().Cell(f.Index + 1); + if (!String.IsNullOrWhiteSpace(f.TotalsRowLabel)) + { + c.DataType = XLCellValues.Text; + + //Remove previous row's label + var oldTotalsCell = this.Worksheet.Cell(oldTotalsRowNumber, f.Column.ColumnNumber()); + if (oldTotalsCell.Value.ToString() == f.TotalsRowLabel) + oldTotalsCell.Value = null; + } + + if (f.TotalsRowFunction != XLTotalsRowFunction.None) + c.DataType = XLCellValues.Number; + } + } + } + + return this; + } + public IXLTable SetEmphasizeFirstColumn() { EmphasizeFirstColumn = true; @@ -386,7 +545,7 @@ if (setAutofilter) InitializeAutoFilter(); - HeadersRow().DataType = XLCellValues.Text; + AsRange().Row(1).DataType = XLCellValues.Text; if (RowCount() == 1) InsertRowsBelow(1); @@ -406,19 +565,19 @@ foreach (IXLCell c in range.Row(1).Cells()) { if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) - c.Value = GetUniqueName("Column" + co.ToInvariantString()); + c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; } Worksheet.Tables.Add(this); } - private String GetUniqueName(String originalName) + private String GetUniqueName(String originalName, Int32 initialOffset, Boolean enforceOffset) { - String name = originalName; - if (_uniqueNames.Contains(name)) + String name = String.Concat(originalName, enforceOffset ? initialOffset.ToInvariantString() : string.Empty); + if (_uniqueNames?.Contains(name) ?? false) { - Int32 i = 1; + Int32 i = initialOffset; name = originalName + i.ToInvariantString(); while (_uniqueNames.Contains(name)) { @@ -427,7 +586,6 @@ } } - _uniqueNames.Add(name); return name; } @@ -460,7 +618,7 @@ foreach (IXLCell c in headersRow.Cells()) { if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) - c.Value = GetUniqueName("Column" + co.ToInvariantString()); + c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; } @@ -470,8 +628,6 @@ RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); - - HeadersRow().DataType = XLCellValues.Text; } else { @@ -520,6 +676,9 @@ } } _showHeaderRow = value; + + if (_showHeaderRow) + HeadersRow().DataType = XLCellValues.Text; } } @@ -597,5 +756,77 @@ columns.Cast().ForEach(column => column.Table = this); return columns; } + + public IEnumerable AsDynamicEnumerable() + { + foreach (var row in this.DataRange.Rows()) + { + dynamic expando = new ExpandoObject(); + foreach (var f in this.Fields) + { + var value = row.Cell(f.Index + 1).Value; + // ExpandoObject supports IDictionary so we can extend it like this + var expandoDict = expando as IDictionary; + if (expandoDict.ContainsKey(f.Name)) + expandoDict[f.Name] = value; + else + expandoDict.Add(f.Name, value); + } + + yield return expando; + } + } + + public DataTable AsNativeDataTable() + { + var table = new DataTable(this.Name); + + foreach (var f in Fields.Cast()) + { + Type type = typeof(object); + if (f.IsConsistentDataType()) + { + var c = f.Column.Cells().Skip(this.ShowHeaderRow ? 1 : 0).First(); + switch (c.DataType) + { + case XLCellValues.Text: + type = typeof(String); + break; + + case XLCellValues.Boolean: + type = typeof(Boolean); + break; + + case XLCellValues.DateTime: + type = typeof(DateTime); + break; + + case XLCellValues.TimeSpan: + type = typeof(TimeSpan); + break; + + case XLCellValues.Number: + type = typeof(Double); + break; + } + } + + table.Columns.Add(f.Name, type); + } + + foreach (var row in this.DataRange.Rows()) + { + var dr = table.NewRow(); + + foreach (var f in this.Fields) + { + dr[f.Name] = row.Cell(f.Index + 1).Value; + } + + table.Rows.Add(dr); + } + + return table; + } } } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 528d7d2..f4bfe87 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -19,12 +19,32 @@ this.name = name; } + private IXLRangeColumn _column; + public IXLRangeColumn Column { - get { return table.Column(this.Index); } + get + { + if (_column == null) + { + _column = this.table.AsRange().Column(this.Index + 1); + } + return _column; + } } - public Int32 Index { get; internal set; } + private Int32 index; + + public Int32 Index + { + get { return index; } + internal set + { + if (index == value) return; + index = value; + _column = null; + } + } public String Name { @@ -35,7 +55,7 @@ set { if (table.ShowHeaderRow) - table.HeadersRow().Cell(Index + 1).SetValue(value); + (table.HeadersRow(false).Cell(Index + 1) as XLCell).SetValue(value, false); table.RenameField(name, value); name = value; @@ -67,31 +87,36 @@ get { return totalsRowFunction; } set { - if (value != XLTotalsRowFunction.None && value != XLTotalsRowFunction.Custom) - { - var cell = table.TotalsRow().Cell(Index + 1); - String formula = String.Empty; - switch (value) - { - case XLTotalsRowFunction.Sum: formula = "109"; break; - case XLTotalsRowFunction.Minimum: formula = "105"; break; - case XLTotalsRowFunction.Maximum: formula = "104"; break; - case XLTotalsRowFunction.Average: formula = "101"; break; - case XLTotalsRowFunction.Count: formula = "103"; break; - case XLTotalsRowFunction.CountNumbers: formula = "102"; break; - case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; - case XLTotalsRowFunction.Variance: formula = "110"; break; - } - - cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; - var lastCell = table.LastRow().Cell(Index + 1); - if (lastCell.DataType != XLCellValues.Text) - { - cell.DataType = lastCell.DataType; - cell.Style.NumberFormat = lastCell.Style.NumberFormat; - } - } totalsRowFunction = value; + UpdateUnderlyingCellFormula(); + } + } + + internal void UpdateUnderlyingCellFormula() + { + if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom) + { + var cell = table.TotalsRow().Cell(Index + 1); + String formula = String.Empty; + switch (TotalsRowFunction) + { + case XLTotalsRowFunction.Sum: formula = "109"; break; + case XLTotalsRowFunction.Minimum: formula = "105"; break; + case XLTotalsRowFunction.Maximum: formula = "104"; break; + case XLTotalsRowFunction.Average: formula = "101"; break; + case XLTotalsRowFunction.Count: formula = "103"; break; + case XLTotalsRowFunction.CountNumbers: formula = "102"; break; + case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; + case XLTotalsRowFunction.Variance: formula = "110"; break; + } + + cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; + var lastCell = table.LastRow().Cell(Index + 1); + if (lastCell.DataType != XLCellValues.Text) + { + cell.DataType = lastCell.DataType; + cell.Style.NumberFormat = lastCell.Style.NumberFormat; + } } } @@ -101,7 +126,7 @@ set { totalsRowFunction = XLTotalsRowFunction.None; - table.TotalsRow().Cell(Index + 1).SetValue(value); + (table.TotalsRow().Cell(Index + 1) as XLCell).SetValue(value, false); totalsRowLabel = value; } } @@ -113,12 +138,32 @@ internal void Delete(Boolean deleteUnderlyingRangeColumn) { - var fields = table.Fields.Cast(); - fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); - table.FieldNames.Remove(this.Name); + var fields = table.Fields.Cast().ToArray(); if (deleteUnderlyingRangeColumn) - (this.Column as XLRangeColumn).Delete(false); + { + table.AsRange().ColumnQuick(this.Index + 1).Delete(); + } + + fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); + table.FieldNames.Remove(this.Name); + } + + public bool IsConsistentDataType() + { + var dataTypes = this.Column + .Cells() + .Skip(this.table.ShowHeaderRow ? 1 : 0) + .Select(c => c.DataType); + + if (this.table.ShowTotalsRow) + dataTypes = dataTypes.Take(dataTypes.Count() - 1); + + var distinctDataTypes = dataTypes + .GroupBy(dt => dt) + .Select(g => new { Key = g.Key, Count = g.Count() }); + + return distinctDataTypes.Count() == 1; } } } diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index c014def..5f73324 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -51,60 +51,62 @@ { get { - return _defaultStyle ?? (_defaultStyle = new XLStyle(null) - { - Font = new XLFont(null, null) - { - Bold = false, - Italic = false, - Underline = XLFontUnderlineValues.None, - Strikethrough = false, - VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, - FontSize = 11, - FontColor = XLColor.FromArgb(0, 0, 0), - FontName = "Calibri", - FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss - }, - Fill = new XLFill(null) - { - BackgroundColor = XLColor.FromIndex(64), - PatternType = XLFillPatternValues.None, - PatternColor = XLColor.FromIndex(64) - }, - Border = new XLBorder(null, null) - { - BottomBorder = XLBorderStyleValues.None, - DiagonalBorder = XLBorderStyleValues.None, - DiagonalDown = false, - DiagonalUp = false, - LeftBorder = XLBorderStyleValues.None, - RightBorder = XLBorderStyleValues.None, - TopBorder = XLBorderStyleValues.None, - BottomBorderColor = XLColor.Black, - DiagonalBorderColor = XLColor.Black, - LeftBorderColor = XLColor.Black, - RightBorderColor = XLColor.Black, - TopBorderColor = XLColor.Black - }, - NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, - Alignment = new XLAlignment(null) - { - Indent = 0, - Horizontal = XLAlignmentHorizontalValues.General, - JustifyLastLine = false, - ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, - RelativeIndent = 0, - ShrinkToFit = false, - TextRotation = 0, - Vertical = XLAlignmentVerticalValues.Bottom, - WrapText = false - }, - Protection = new XLProtection(null) - { - Locked = true, - Hidden = false - } - }); + return _defaultStyle + ?? (_defaultStyle = new XLStyle(null) + { + Font = new XLFont(null, null) + { + Bold = false, + Italic = false, + Underline = XLFontUnderlineValues.None, + Strikethrough = false, + VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, + FontSize = 11, + FontColor = XLColor.FromArgb(0, 0, 0), + FontName = "Calibri", + FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss, + FontCharSet = XLFontCharSet.Default + }, + Fill = new XLFill(null) + { + BackgroundColor = XLColor.FromIndex(64), + PatternType = XLFillPatternValues.None, + PatternColor = XLColor.FromIndex(64) + }, + Border = new XLBorder(null, null) + { + BottomBorder = XLBorderStyleValues.None, + DiagonalBorder = XLBorderStyleValues.None, + DiagonalDown = false, + DiagonalUp = false, + LeftBorder = XLBorderStyleValues.None, + RightBorder = XLBorderStyleValues.None, + TopBorder = XLBorderStyleValues.None, + BottomBorderColor = XLColor.Black, + DiagonalBorderColor = XLColor.Black, + LeftBorderColor = XLColor.Black, + RightBorderColor = XLColor.Black, + TopBorderColor = XLColor.Black + }, + NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, + Alignment = new XLAlignment(null) + { + Indent = 0, + Horizontal = XLAlignmentHorizontalValues.General, + JustifyLastLine = false, + ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, + RelativeIndent = 0, + ShrinkToFit = false, + TextRotation = 0, + Vertical = XLAlignmentVerticalValues.Bottom, + WrapText = false + }, + Protection = new XLProtection(null) + { + Locked = true, + Hidden = false + } + }); } } @@ -116,24 +118,24 @@ get { var defaultPageOptions = new XLPageSetup(null, null) - { - PageOrientation = XLPageOrientation.Default, - Scale = 100, - PaperSize = XLPaperSize.LetterPaper, - Margins = new XLMargins - { - Top = 0.75, - Bottom = 0.5, - Left = 0.75, - Right = 0.75, - Header = 0.5, - Footer = 0.75 - }, - ScaleHFWithDocument = true, - AlignHFWithMargins = true, - PrintErrorValue = XLPrintErrorValues.Displayed, - ShowComments = XLShowCommentsValues.None - }; + { + PageOrientation = XLPageOrientation.Default, + Scale = 100, + PaperSize = XLPaperSize.LetterPaper, + Margins = new XLMargins + { + Top = 0.75, + Bottom = 0.5, + Left = 0.75, + Right = 0.75, + Header = 0.5, + Footer = 0.75 + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None + }; return defaultPageOptions; } } @@ -143,10 +145,10 @@ get { return new XLOutline(null) - { - SummaryHLocation = XLOutlineSummaryHLocation.Right, - SummaryVLocation = XLOutlineSummaryVLocation.Bottom - }; + { + SummaryHLocation = XLOutlineSummaryHLocation.Right, + SummaryVLocation = XLOutlineSummaryVLocation.Bottom + }; } } @@ -323,20 +325,20 @@ private void InitializeTheme() { Theme = new XLTheme - { - Text1 = XLColor.FromHtml("#FF000000"), - Background1 = XLColor.FromHtml("#FFFFFFFF"), - Text2 = XLColor.FromHtml("#FF1F497D"), - Background2 = XLColor.FromHtml("#FFEEECE1"), - Accent1 = XLColor.FromHtml("#FF4F81BD"), - Accent2 = XLColor.FromHtml("#FFC0504D"), - Accent3 = XLColor.FromHtml("#FF9BBB59"), - Accent4 = XLColor.FromHtml("#FF8064A2"), - Accent5 = XLColor.FromHtml("#FF4BACC6"), - Accent6 = XLColor.FromHtml("#FFF79646"), - Hyperlink = XLColor.FromHtml("#FF0000FF"), - FollowedHyperlink = XLColor.FromHtml("#FF800080") - }; + { + Text1 = XLColor.FromHtml("#FF000000"), + Background1 = XLColor.FromHtml("#FFFFFFFF"), + Text2 = XLColor.FromHtml("#FF1F497D"), + Background2 = XLColor.FromHtml("#FFEEECE1"), + Accent1 = XLColor.FromHtml("#FF4F81BD"), + Accent2 = XLColor.FromHtml("#FFC0504D"), + Accent3 = XLColor.FromHtml("#FF9BBB59"), + Accent4 = XLColor.FromHtml("#FF8064A2"), + Accent5 = XLColor.FromHtml("#FF4BACC6"), + Accent6 = XLColor.FromHtml("#FFF79646"), + Hyperlink = XLColor.FromHtml("#FF0000FF"), + FollowedHyperlink = XLColor.FromHtml("#FF800080") + }; } internal XLColor GetXLColor(XLThemeColor themeColor) @@ -399,14 +401,7 @@ public Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet) { - if (Worksheets.Any(w => string.Equals(w.Name, XLWorksheets.TrimSheetName(name), StringComparison.OrdinalIgnoreCase))) - { - worksheet = Worksheet(name); - return true; - } - - worksheet = null; - return false; + return Worksheets.TryGetWorksheet(name, out worksheet); } public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws) @@ -521,6 +516,7 @@ _loadSource = XLLoadSource.File; _originalFile = file; + _originalStream = null; } private static SpreadsheetDocumentType GetSpreadsheetDocumentType(string filePath) @@ -622,6 +618,7 @@ _loadSource = XLLoadSource.Stream; _originalStream = stream; + _originalFile = null; } internal static void CopyStream(Stream input, Stream output) @@ -988,5 +985,10 @@ { Protect(false, false, workbookPassword); } + + public override string ToString() + { + return _originalFile ?? String.Format("XLWorkbook({0})", _originalStream.ToString()); + } } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index be0d818..3af1993 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1,5 +1,6 @@ #region +using ClosedXML.Extensions; using ClosedXML.Utils; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; @@ -263,6 +264,8 @@ LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(ColumnBreaks)) LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof(WorksheetExtensionList)) + LoadExtensions((WorksheetExtensionList)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(LegacyDrawing)) ws.LegacyDrawingId = (reader.LoadCurrentElement() as LegacyDrawing).Id.Value; } @@ -475,6 +478,13 @@ if (target != null && source != null) { var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable; + + if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.ColumnHeaderCaption ?? String.Empty))) + pt.SetColumnHeaderCaption(StringValue.ToString(pivotTableDefinition.ColumnHeaderCaption)); + + if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) + pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption)); + pt.RelId = wsPart.GetIdOfPart(pivotTablePart); pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); @@ -718,8 +728,10 @@ private static IXLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) { + var row = Math.Max(1, Convert.ToInt32(marker.RowId.InnerText) + 1); + var column = Math.Min(XLHelper.MaxColumnNumber, Convert.ToInt32(marker.ColumnId.InnerText) + 1); return new XLMarker( - ws.Cell(Convert.ToInt32(marker.RowId.InnerText) + 1, Convert.ToInt32(marker.ColumnId.InnerText) + 1).Address, + ws.Cell(row, column).Address, new Point( ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.ColumnOffset.InnerText), GraphicsUtils.Graphics.DpiX), ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.RowOffset.InnerText), GraphicsUtils.Graphics.DpiY) @@ -1835,6 +1847,9 @@ foreach (var fr in conditionalFormatting.Elements()) { var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value)); + + conditionalFormat.StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false); + if (fr.FormatId != null) { LoadFont(differentialFormats[(Int32)fr.FormatId.Value].Font, conditionalFormat.Style.Font); @@ -1873,6 +1888,11 @@ var dataBar = fr.Elements().First(); if (dataBar.ShowValue != null) conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value; + + var id = fr.Descendants().FirstOrDefault(); + if (id != null && id.Text != null && !String.IsNullOrWhiteSpace(id.Text)) + conditionalFormat.Id = Guid.Parse(id.Text.Substring(1, id.Text.Length - 2)); + ExtractConditionalFormatValueObjects(conditionalFormat, dataBar); } else if (fr.Elements().Any()) @@ -1907,6 +1927,32 @@ } } + private void LoadExtensions(WorksheetExtensionList extensions, XLWorksheet ws) + { + if (extensions == null) + { + return; + } + + foreach (var conditionalFormattingRule in extensions + .Descendants() + .Where(cf => + cf.Type != null + && cf.Type.HasValue + && cf.Type.Value == ConditionalFormatValues.DataBar)) + { + var xlConditionalFormat = ws.ConditionalFormats + .Cast() + .SingleOrDefault(cf => cf.Id.WrapInBraces() == conditionalFormattingRule.Id); + if (xlConditionalFormat != null) + { + var negativeFillColor = conditionalFormattingRule.Descendants().SingleOrDefault(); + var color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = negativeFillColor.Rgb }; + xlConditionalFormat.Colors.Add(this.GetColor(color)); + } + } + } + private static XLFormula GetFormula(String value) { var formula = new XLFormula(); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 464ad88..a60076a 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -148,19 +148,18 @@ string sheetName = worksheet.Name; // Get the pivot Table Parts - IEnumerable pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; - Dictionary pvtTableCacheDefinationPart = new Dictionary(); + var pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; + var pvtTableCacheDefinitionPart = new Dictionary(); foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts) { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete - var pvtCache = pvtCacheDef.Descendants().Where(s => s.WorksheetSource.Sheet == sheetName); - if (pvtCache.Any()) + if (pvtCacheDef.Descendants().Any(s => s.WorksheetSource != null && s.WorksheetSource.Sheet == sheetName)) { - pvtTableCacheDefinationPart.Add(Item, Item.ToString()); + pvtTableCacheDefinitionPart.Add(Item, Item.ToString()); } } - foreach (var Item in pvtTableCacheDefinationPart) + foreach (var Item in pvtTableCacheDefinitionPart) { wbPart.DeletePart(Item.Key); } @@ -2033,33 +2032,45 @@ Name = pt.Name, CacheId = cacheId, DataCaption = "Values", - MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), + MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown), PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), - UseAutoFormatting = GetBooleanValue(pt.AutofitColumns, true), - PreserveFormatting = GetBooleanValue(pt.PreserveCellFormatting, true), - RowGrandTotals = GetBooleanValue(pt.ShowGrandTotalsRows, true), - ColumnGrandTotals = GetBooleanValue(pt.ShowGrandTotalsColumns, true), - SubtotalHiddenItems = GetBooleanValue(pt.FilteredItemsInSubtotals, true), - MultipleFieldFilters = GetBooleanValue(pt.AllowMultipleFilters, true), - CustomListSort = GetBooleanValue(pt.UseCustomListsForSorting, true), - ShowDrill = GetBooleanValue(pt.ShowExpandCollapseButtons, true), - ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true), - ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true), - ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), - GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, false), - ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true), - ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), - ShowItems = GetBooleanValue(pt.DisplayItemLabels, true), - FieldListSortAscending = GetBooleanValue(pt.SortFieldsAtoZ, true), - PrintDrill = GetBooleanValue(pt.PrintExpandCollapsedButtons, true), - ItemPrintTitles = GetBooleanValue(pt.RepeatRowLabels, true), - FieldPrintTitles = GetBooleanValue(pt.PrintTitles, true), - EnableDrill = GetBooleanValue(pt.EnableShowDetails, true) + UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, true), + PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true), + RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), + ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), + SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true), + MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), + CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), + ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), + ShowDataTips = OpenXmlHelper.GetBooleanValue(pt.ShowContextualTooltips, true), + ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true), + ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), + GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false), + ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, true), + ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), + ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true), + FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, true), + PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, true), + ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true), + FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, true), + EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true) }; + if (!String.IsNullOrEmpty(pt.ColumnHeaderCaption)) + pivotTableDefinition.ColumnHeaderCaption = StringValue.FromString(pt.ColumnHeaderCaption); + + if (!String.IsNullOrEmpty(pt.RowHeaderCaption)) + pivotTableDefinition.RowHeaderCaption = StringValue.FromString(pt.RowHeaderCaption); + + if (pt.ClassicPivotTableLayout) + { + pivotTableDefinition.Compact = false; + pivotTableDefinition.CompactData = false; + } + if (pt.EmptyCellReplacement != null) { pivotTableDefinition.ShowMissing = true; @@ -2148,6 +2159,12 @@ IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; + if (pt.ClassicPivotTableLayout) + { + pf.Outline = false; + pf.Compact = false; + } + switch (pt.Subtotals) { case XLPivotSubtotals.DoNotShow: @@ -3608,6 +3625,10 @@ ? new FontFamilyNumbering { Val = (Int32)fontInfo.Font.FontFamilyNumbering } : null; + var fontCharSet = (fontInfo.Font.FontCharSetModified || ignoreMod) && fontInfo.Font.FontCharSet != XLFontCharSet.Default + ? new FontCharSet { Val = (Int32)fontInfo.Font.FontCharSet } + : null; + if (bold != null) font.AppendChild(bold); if (italic != null) @@ -3628,6 +3649,8 @@ font.AppendChild(fontName); if (fontFamilyNumbering != null) font.AppendChild(fontFamilyNumbering); + if (fontCharSet != null) + font.AppendChild(fontCharSet); return font; } @@ -4149,15 +4172,15 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var lastRow = 0; - var sheetDataRows = + var existingSheetDataRows = sheetData.Elements().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value, r => r); foreach ( var r in - xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key))) + xlWorksheet.Internals.RowsCollection.Deleted.Where(r => existingSheetDataRows.ContainsKey(r.Key))) { - sheetData.RemoveChild(sheetDataRows[r.Key]); - sheetDataRows.Remove(r.Key); + sheetData.RemoveChild(existingSheetDataRows[r.Key]); + existingSheetDataRows.Remove(r.Key); xlWorksheet.Internals.CellsCollection.deleted.Remove(r.Key); } @@ -4166,28 +4189,10 @@ foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row; - if (sheetDataRows.ContainsKey(distinctRow)) - row = sheetDataRows[distinctRow]; + if (existingSheetDataRows.ContainsKey(distinctRow)) + row = existingSheetDataRows[distinctRow]; else - { row = new Row { RowIndex = (UInt32)distinctRow }; - if (noRows) - { - sheetData.AppendChild(row); - noRows = false; - } - else - { - if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) - { - var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); - var rowBeforeInsert = sheetDataRows[minRow]; - sheetData.InsertBefore(row, rowBeforeInsert); - } - else - sheetData.AppendChild(row); - } - } if (maxColumn > 0) row.Spans = new ListValue { InnerText = "1:" + maxColumn.ToInvariantString() }; @@ -4241,104 +4246,158 @@ xlWorksheet.Internals.CellsCollection.deleted.Remove(kpDel.Key); } - if (!xlWorksheet.Internals.CellsCollection.RowsCollection.ContainsKey(distinctRow)) continue; - - var isNewRow = !row.Elements().Any(); - lastCell = 0; - var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null - ? (XLHelper.GetColumnLetterFromNumber(++lastCell) + distinctRow) : c.CellReference.Value), c => c); - foreach (var xlCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values - .OrderBy(c => c.Address.ColumnNumber) - .Select(c => c)) + if (xlWorksheet.Internals.CellsCollection.RowsCollection.ContainsKey(distinctRow)) { - var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; - var cellReference = (xlCell.Address).GetTrimmedAddress(); - var isEmpty = xlCell.IsEmpty(true); - - Cell cell = null; - if (cellsByReference.ContainsKey(cellReference)) + var isNewRow = !row.Elements().Any(); + lastCell = 0; + var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null + ? (XLHelper.GetColumnLetterFromNumber(++lastCell) + distinctRow) : c.CellReference.Value), c => c); + foreach (var xlCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values + .OrderBy(c => c.Address.ColumnNumber) + .Select(c => c)) { - cell = cellsByReference[cellReference]; - if (isEmpty) - { - cell.Remove(); - } - } + XLTableField field = null; - if (!isEmpty) - { - if (cell == null) - { - cell = new Cell(); - cell.CellReference = new StringValue(cellReference); + var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; + var cellReference = (xlCell.Address).GetTrimmedAddress(); - if (isNewRow) - row.AppendChild(cell); - else + // For saving cells to file, ignore conditional formatting. They just bloat the file + var isEmpty = xlCell.IsEmpty(true, false); + + Cell cell = null; + if (cellsByReference.ContainsKey(cellReference)) + { + cell = cellsByReference[cellReference]; + if (isEmpty) { - var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); + cell.Remove(); + } + } - Cell cellBeforeInsert = null; - int[] lastCo = { Int32.MaxValue }; - foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) - { - cellBeforeInsert = c.Value; - lastCo[0] = c.Key; - } - if (cellBeforeInsert == null) + if (!isEmpty) + { + if (cell == null) + { + cell = new Cell(); + cell.CellReference = new StringValue(cellReference); + + if (isNewRow) row.AppendChild(cell); else - row.InsertBefore(cell, cellBeforeInsert); - } - } - - cell.StyleIndex = styleId; - if (xlCell.HasFormula) - { - var formula = xlCell.FormulaA1; - if (xlCell.HasArrayFormula) - { - formula = formula.Substring(1, formula.Length - 2); - var f = new CellFormula { FormulaType = CellFormulaValues.Array }; - - if (xlCell.FormulaReference == null) - xlCell.FormulaReference = xlCell.AsRange().RangeAddress; - - if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address)) { - f.Text = formula; - f.Reference = xlCell.FormulaReference.ToStringRelative(); + var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); + + Cell cellBeforeInsert = null; + int[] lastCo = { Int32.MaxValue }; + foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) + { + cellBeforeInsert = c.Value; + lastCo[0] = c.Key; + } + if (cellBeforeInsert == null) + row.AppendChild(cell); + else + row.InsertBefore(cell, cellBeforeInsert); + } + } + + cell.StyleIndex = styleId; + if (xlCell.HasFormula) + { + var formula = xlCell.FormulaA1; + if (xlCell.HasArrayFormula) + { + formula = formula.Substring(1, formula.Length - 2); + var f = new CellFormula { FormulaType = CellFormulaValues.Array }; + + if (xlCell.FormulaReference == null) + xlCell.FormulaReference = xlCell.AsRange().RangeAddress; + + if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address)) + { + f.Text = formula; + f.Reference = xlCell.FormulaReference.ToStringRelative(); + } + + cell.CellFormula = f; + } + else + { + cell.CellFormula = new CellFormula(); + cell.CellFormula.Text = formula; } - cell.CellFormula = f; + cell.CellValue = null; + } + else if (xlCell.TableCellType() == XLTableCellType.Total) + { + var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); + field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; + + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) + { + cell.DataType = XLWorkbook.CvSharedString; + } + else + { + cell.DataType = null; + } + cell.CellFormula = null; } else { - cell.CellFormula = new CellFormula(); - cell.CellFormula.Text = formula; + cell.CellFormula = null; + cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); } - cell.CellValue = null; + if (evaluateFormulae || field != null || !xlCell.HasFormula) + SetCellValue(xlCell, field, cell); + } + } + xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); + } + + // If we're adding a new row (not in sheet already and it's not "empty" + if (!existingSheetDataRows.ContainsKey(distinctRow)) + { + var invalidRow = row.Height == null + && row.CustomHeight == null + && row.Hidden == null + && row.StyleIndex == null + && row.CustomFormat == null + && row.Collapsed == null + && row.OutlineLevel == null + && !row.Elements().Any(); + + if (!invalidRow) + { + if (noRows) + { + sheetData.AppendChild(row); + noRows = false; } else { - cell.CellFormula = null; - cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); + if (existingSheetDataRows.Any(r => r.Key > row.RowIndex.Value)) + { + var minRow = existingSheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); + var rowBeforeInsert = existingSheetDataRows[minRow]; + sheetData.InsertBefore(row, rowBeforeInsert); + } + else + sheetData.AppendChild(row); } - - if (!xlCell.HasFormula || evaluateFormulae) - SetCellValue(xlCell, cell); } } - xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); } + foreach ( var r in xlWorksheet.Internals.CellsCollection.deleted.Keys.Where( - sheetDataRows.ContainsKey)) + existingSheetDataRows.ContainsKey)) { - sheetData.RemoveChild(sheetDataRows[r]); - sheetDataRows.Remove(r); + sheetData.RemoveChild(existingSheetDataRows[r]); + existingSheetDataRows.Remove(r); } #endregion SheetData @@ -4360,19 +4419,19 @@ sheetProtection.Sheet = protection.Protected; if (!String.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; - sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); - sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); - sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); - sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); - sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); - sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); - sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); - sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); - sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); - sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); - sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); - sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); - sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); + sheetProtection.FormatCells = OpenXmlHelper.GetBooleanValue(!protection.FormatCells, true); + sheetProtection.FormatColumns = OpenXmlHelper.GetBooleanValue(!protection.FormatColumns, true); + sheetProtection.FormatRows = OpenXmlHelper.GetBooleanValue(!protection.FormatRows, true); + sheetProtection.InsertColumns = OpenXmlHelper.GetBooleanValue(!protection.InsertColumns, true); + sheetProtection.InsertHyperlinks = OpenXmlHelper.GetBooleanValue(!protection.InsertHyperlinks, true); + sheetProtection.InsertRows = OpenXmlHelper.GetBooleanValue(!protection.InsertRows, true); + sheetProtection.DeleteColumns = OpenXmlHelper.GetBooleanValue(!protection.DeleteColumns, true); + sheetProtection.DeleteRows = OpenXmlHelper.GetBooleanValue(!protection.DeleteRows, true); + sheetProtection.AutoFilter = OpenXmlHelper.GetBooleanValue(!protection.AutoFilter, true); + sheetProtection.PivotTables = OpenXmlHelper.GetBooleanValue(!protection.PivotTables, true); + sheetProtection.Sort = OpenXmlHelper.GetBooleanValue(!protection.Sort, true); + sheetProtection.SelectLockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectLockedCells, false); + sheetProtection.SelectUnlockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectUnlockedCells, false); } else { @@ -4468,6 +4527,60 @@ } } + + var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; + if (exlst != null && exlst.Count() > 0) + { + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.WorksheetExtensionList); + worksheetPart.Worksheet.InsertAfter(new WorksheetExtensionList(), previousElement); + } + + WorksheetExtensionList worksheetExtensionList = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.WorksheetExtensionList, worksheetExtensionList); + + var conditionalFormattings = worksheetExtensionList.Descendants().SingleOrDefault(); + if (conditionalFormattings == null || !conditionalFormattings.Any()) + { + WorksheetExtension worksheetExtension1 = new WorksheetExtension { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }; + worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + worksheetExtensionList.Append(worksheetExtension1); + + conditionalFormattings = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattings(); + worksheetExtension1.Append(conditionalFormattings); + } + + foreach (var cfGroup in exlst + .GroupBy( + c => c.Range.RangeAddress.ToStringRelative(false), + c => c, + (key, g) => new { RangeId = key, CfList = g.ToList() } + ) + ) + { + foreach (var xlConditionalFormat in cfGroup.CfList.Cast()) + { + var conditionalFormattingRule = conditionalFormattings.Descendants() + .SingleOrDefault(r => r.Id == xlConditionalFormat.Id.WrapInBraces()); + if (conditionalFormattingRule != null) + { + var conditionalFormat = conditionalFormattingRule.Ancestors().SingleOrDefault(); + conditionalFormattings.RemoveChild(conditionalFormat); + } + + var conditionalFormatting = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormatting(); + conditionalFormatting.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); + conditionalFormatting.Append(XLCFConvertersExtension.Convert(xlConditionalFormat, context)); + var referenceSequence = new DocumentFormat.OpenXml.Office.Excel.ReferenceSequence { Text = cfGroup.RangeId }; + conditionalFormatting.Append(referenceSequence); + + conditionalFormattings.Append(conditionalFormatting); + } + } + } + + #endregion Conditional Formatting #region DataValidations @@ -4843,8 +4956,25 @@ #endregion LegacyDrawingHeaderFooter } - private static void SetCellValue(XLCell xlCell, Cell openXmlCell) + private static void SetCellValue(XLCell xlCell, XLTableField field, Cell openXmlCell) { + if (field != null) + { + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) + { + var cellValue = new CellValue(); + cellValue.Text = xlCell.SharedStringId.ToString(); + openXmlCell.DataType = CvSharedString; + openXmlCell.CellValue = cellValue; + } + else if (field.TotalsRowFunction == XLTotalsRowFunction.None) + { + openXmlCell.DataType = CvSharedString; + openXmlCell.CellValue = null; + } + return; + } + if (xlCell.HasFormula) { var cellValue = new CellValue(); @@ -4989,11 +5119,6 @@ } } - private static BooleanValue GetBooleanValue(bool value, bool defaultValue) - { - return value == defaultValue ? null : new BooleanValue(value); - } - private static void CollapseColumns(Columns columns, Dictionary sheetColumns) { UInt32 lastMin = 1; diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c40ad1b..7e37bba 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -651,7 +651,8 @@ } if (AutoFilter.Enabled) - targetSheet.Range(AutoFilter.Range.RangeAddress).SetAutoFilter(); + using (var range = targetSheet.Range(AutoFilter.Range.RangeAddress.FirstAddress.RowNumber, AutoFilter.Range.RangeAddress.FirstAddress.ColumnNumber, AutoFilter.Range.RangeAddress.LastAddress.RowNumber, AutoFilter.Range.RangeAddress.LastAddress.ColumnNumber)) + range.SetAutoFilter(); return targetSheet; } @@ -1595,5 +1596,16 @@ { return true; } + + internal void SetValue(T value, int ro, int co) where T : class + { + if (value == null) + this.Cell(ro, co).SetValue(String.Empty); + else if (value is IConvertible) + this.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); + else + this.Cell(ro, co).SetValue(value); + } + } } diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 9061e1d..af69de6 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -47,7 +47,7 @@ public bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet) { XLWorksheet w; - if (_worksheets.TryGetValue(sheetName, out w)) + if (_worksheets.TryGetValue(TrimSheetName(sheetName).ToLowerInvariant(), out w)) { worksheet = w; return true; @@ -70,7 +70,7 @@ XLWorksheet w; - if (_worksheets.TryGetValue(sheetName, out w)) + if (_worksheets.TryGetValue(sheetName.ToLowerInvariant(), out w)) return w; var wss = _worksheets.Where(ws => string.Equals(ws.Key, sheetName, StringComparison.OrdinalIgnoreCase)); @@ -118,12 +118,12 @@ if (_worksheets.Any(ws => ws.Key.Equals(sheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", sheetName), nameof(sheetName)); - _worksheets.Add(sheetName, sheet); + _worksheets.Add(sheetName.ToLowerInvariant(), sheet); } public void Delete(String sheetName) { - Delete(_worksheets[sheetName].Position); + Delete(_worksheets[sheetName.ToLowerInvariant()].Position); } public void Delete(Int32 position) @@ -178,14 +178,14 @@ public void Rename(String oldSheetName, String newSheetName) { - if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; + if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName.ToLowerInvariant())) return; if (!oldSheetName.Equals(newSheetName, StringComparison.OrdinalIgnoreCase) && _worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName)); - var ws = _worksheets[oldSheetName]; - _worksheets.Remove(oldSheetName); + var ws = _worksheets[oldSheetName.ToLowerInvariant()]; + _worksheets.Remove(oldSheetName.ToLowerInvariant()); Add(newSheetName, ws); } } diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 699d251..6cde6da 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -263,6 +263,7 @@ font.FontColor = sourceFont.FontColor; font.FontName = sourceFont.FontName; font.FontFamilyNumbering = sourceFont.FontFamilyNumbering; + font.FontCharSet = sourceFont.FontCharSet; } } diff --git a/ClosedXML/Extensions/GuidExtensions.cs b/ClosedXML/Extensions/GuidExtensions.cs new file mode 100644 index 0000000..c9d2eba --- /dev/null +++ b/ClosedXML/Extensions/GuidExtensions.cs @@ -0,0 +1,12 @@ +using System; + +namespace ClosedXML.Extensions +{ + internal static class GuidExtensions + { + internal static String WrapInBraces(this Guid guid) + { + return string.Concat('{', guid.ToString(), '}'); + } + } +} diff --git a/ClosedXML/Properties/AssemblyVersionInfo.cs b/ClosedXML/Properties/AssemblyVersionInfo.cs index 57466c5..6db1e31 100644 --- a/ClosedXML/Properties/AssemblyVersionInfo.cs +++ b/ClosedXML/Properties/AssemblyVersionInfo.cs @@ -7,6 +7,6 @@ // Build Number // Revision // -[assembly: AssemblyVersion("0.89.0.0")] -[assembly: AssemblyFileVersion("0.89.0.0")] -[assembly: AssemblyInformationalVersion("0.89.0-beta1")] +[assembly: AssemblyVersion("0.90.0.0")] +[assembly: AssemblyFileVersion("0.90.0.0")] +[assembly: AssemblyInformationalVersion("0.90.0")] diff --git a/ClosedXML/Utils/OpenXmlHelper.cs b/ClosedXML/Utils/OpenXmlHelper.cs new file mode 100644 index 0000000..6f0c7a2 --- /dev/null +++ b/ClosedXML/Utils/OpenXmlHelper.cs @@ -0,0 +1,17 @@ +using DocumentFormat.OpenXml; + +namespace ClosedXML.Utils +{ + internal static class OpenXmlHelper + { + public static BooleanValue GetBooleanValue(bool value, bool defaultValue) + { + return value == defaultValue ? null : new BooleanValue(value); + } + + public static bool GetBooleanValueAsBool(BooleanValue value, bool defaultValue) + { + return value == null ? defaultValue : value.Value; + } + } +} \ No newline at end of file diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9f4871b..a870a50 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -105,7 +105,6 @@ - @@ -118,7 +117,6 @@ - @@ -169,6 +167,9 @@ + + + diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index a87bf0c..769afa2 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -1,6 +1,5 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples { @@ -27,7 +26,6 @@ public class CFColorScaleLowHigh : IXLExample { - public void Create(String filePath) { var workbook = new XLWorkbook(); @@ -221,7 +219,6 @@ } } - public class CFEqualsString : IXLExample { public void Create(String filePath) @@ -527,6 +524,39 @@ } } + public class CFDataBarNegative : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).SetValue(-1) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.Range(ws.Cell(1, 1), ws.Cell(4, 1)) + .AddConditionalFormat() + .DataBar(XLColor.Green, XLColor.Red, showBarOnly: false) + .LowestValue() + .HighestValue(); + + ws.Cell(1,3).SetValue(-20) + .CellBelow().SetValue(40) + .CellBelow().SetValue(-60) + .CellBelow().SetValue(30); + + ws.Range(ws.Cell(1, 3), ws.Cell(4, 3)) + .AddConditionalFormat() + .DataBar(XLColor.Green, XLColor.Red, showBarOnly: true) + .Minimum(XLCFContentType.Number, -100) + .Maximum(XLCFContentType.Number, 100); + + workbook.SaveAs(filePath); + } + } + public class CFIconSet : IXLExample { public void Create(String filePath) @@ -579,7 +609,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - ws.Cell(2,1).SetValue(1) + ws.Cell(2, 1).SetValue(1) .CellRight().SetValue(1) .CellRight().SetValue(2) .CellRight().SetValue(3); @@ -588,7 +618,6 @@ range.AddConditionalFormat().WhenEquals("1").Font.SetBold(); range.InsertRowsAbove(1); - workbook.SaveAs(filePath); } } @@ -606,7 +635,7 @@ .CellBelow().SetValue(3) .CellBelow().SetValue(4); - ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red) + ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, XLColor.Green) .LowestValue() .HighestValue(); @@ -621,7 +650,7 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - using(var range = ws.Range("A1:A10")) + using (var range = ws.Range("A1:A10")) { range.AddConditionalFormat().WhenEquals("3") .Fill.SetBackgroundColor(XLColor.Blue); @@ -634,4 +663,28 @@ workbook.SaveAs(filePath); } } + + public class CFStopIfTrue : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue(6) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.RangeUsed().AddConditionalFormat().StopIfTrue().WhenGreaterThan(5); + + + ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number); + + workbook.SaveAs(filePath); + } + } } diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 1e639dd..8f1978f 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -4,6 +4,7 @@ using ClosedXML_Examples.Ranges; using ClosedXML_Examples.Rows; using ClosedXML_Examples.Styles; +using ClosedXML_Examples.Tables; using System.IO; namespace ClosedXML_Examples @@ -67,6 +68,7 @@ new RowCells().Create(Path.Combine(path, "RowCells.xlsx")); new FreezePanes().Create(Path.Combine(path, "FreezePanes.xlsx")); new UsingTables().Create(Path.Combine(path, "UsingTables.xlsx")); + new ResizingTables().Create(Path.Combine(path, "ResizingTables.xlsx")); new AddingRowToTables().Create(Path.Combine(path, "AddingRowToTables.xlsx")); new RightToLeft().Create(Path.Combine(path, "RightToLeft.xlsx")); new ShowCase().Create(Path.Combine(path, "ShowCase.xlsx")); diff --git a/ClosedXML_Examples/Misc/AddingDataSet.cs b/ClosedXML_Examples/Misc/AddingDataSet.cs index 11c4b9c..a8d1e68 100644 --- a/ClosedXML_Examples/Misc/AddingDataSet.cs +++ b/ClosedXML_Examples/Misc/AddingDataSet.cs @@ -1,45 +1,11 @@ +using ClosedXML.Excel; using System; using System.Data; -using ClosedXML.Excel; namespace ClosedXML_Examples.Misc { public class AddingDataSet : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - - // Public public void Create(String filePath) { var wb = new XLWorkbook(); @@ -52,7 +18,6 @@ wb.SaveAs(filePath); } - // Private private DataSet GetDataSet() { var ds = new DataSet(); @@ -78,9 +43,5 @@ table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); return table; } - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/AutoFilter.cs b/ClosedXML_Examples/Misc/AutoFilter.cs index 21b8402..bb119b2 100644 --- a/ClosedXML_Examples/Misc/AutoFilter.cs +++ b/ClosedXML_Examples/Misc/AutoFilter.cs @@ -50,11 +50,11 @@ ws.Cell("A4").Value = "Dagny"; ws.RangeUsed().SetAutoFilter(); - - // Your can turn off the autofilter in three ways: - // 1) worksheet.AutoFilterRange.SetAutoFilter(false) - // 2) worksheet.AutoFilterRange = null - // 3) Pick any range in the worksheet and call range.SetAutoFilter(false); + + // Your can turn off the autofilter by: + // 1) worksheet.AutoFilter.Clear() + // 2) worksheet.SetAutoFilter(false) + // 3) Pick any range in the worksheet and call the above methods on the range wb.SaveAs(filePath); } diff --git a/ClosedXML_Examples/Misc/Collections.cs b/ClosedXML_Examples/Misc/Collections.cs index 39b416d..a9b9fe5 100644 --- a/ClosedXML_Examples/Misc/Collections.cs +++ b/ClosedXML_Examples/Misc/Collections.cs @@ -68,7 +68,7 @@ var dataTable = GetTable(); ws.Cell(6, 1).Value = "DataTable"; ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles"); - ws.Cell(7, 1).Value = dataTable.AsEnumerable(); + ws.Cell(7, 1).Value = dataTable; // From a query var list = new List(); @@ -83,8 +83,7 @@ ws.Cell(6, 6).Value = "Query"; ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); - ws.Cell(7, 6).Value = people.AsEnumerable(); // Very Important to call the AsEnumerable method - // otherwise it won't be copied. + ws.Cell(7, 6).Value = people; // Prepare the style for the titles @@ -92,7 +91,7 @@ titlesStyle.Font.Bold = true; titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - + // Format all titles in one shot wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; diff --git a/ClosedXML_Examples/Misc/CopyingWorksheets.cs b/ClosedXML_Examples/Misc/CopyingWorksheets.cs index b0843eb..e06cd66 100644 --- a/ClosedXML_Examples/Misc/CopyingWorksheets.cs +++ b/ClosedXML_Examples/Misc/CopyingWorksheets.cs @@ -1,6 +1,6 @@ -using System.IO; using ClosedXML.Excel; -using ClosedXML_Examples.Ranges; +using ClosedXML_Examples.Tables; +using System.IO; namespace ClosedXML_Examples.Misc { @@ -18,7 +18,7 @@ var wsSource = wb.Worksheet(1); // Copy the worksheet to a new sheet in this workbook wsSource.CopyTo("Copy"); - + // We're going to open another workbook to show that you can // copy a sheet from one workbook to another: new BasicTable().Create(tempFile2); @@ -40,6 +40,5 @@ } } } - } } diff --git a/ClosedXML_Examples/Misc/InsertingData.cs b/ClosedXML_Examples/Misc/InsertingData.cs index d1035d5..626a9ec 100644 --- a/ClosedXML_Examples/Misc/InsertingData.cs +++ b/ClosedXML_Examples/Misc/InsertingData.cs @@ -1,8 +1,8 @@ +using ClosedXML.Excel; using System; using System.Collections.Generic; using System.Data; using System.Linq; -using ClosedXML.Excel; namespace ClosedXML_Examples.Misc { @@ -38,7 +38,7 @@ var dataTable = GetTable(); ws.Cell(6, 1).Value = "From DataTable"; ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles"); - ws.Cell(7, 1).InsertData(dataTable.AsEnumerable()); + ws.Cell(7, 1).InsertData(dataTable); // From a query var list = new List(); @@ -53,11 +53,15 @@ ws.Cell(6, 6).Value = "From Query"; ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); - ws.Cell(7, 6).InsertData(people.AsEnumerable()); + ws.Cell(7, 6).InsertData(people); - ws.Cell("F13").Value = "Transposed"; - ws.Range(13, 6, 13, 8).Merge().AddToNamed("Titles"); - ws.Cell("F14").InsertData(people.AsEnumerable(), true); + ws.Cell(11, 6).Value = "From List"; + ws.Range(11, 6, 11, 9).Merge().AddToNamed("Titles"); + ws.Cell(12, 6).InsertData(list); + + ws.Cell("A13").Value = "Transposed"; + ws.Range(13, 1, 13, 3).Merge().AddToNamed("Titles"); + ws.Cell("A14").InsertData(people.AsEnumerable(), true); // Prepare the style for the titles var titlesStyle = wb.Style; @@ -74,17 +78,17 @@ } } - class Person + private class Person { public String House { get; set; } public String Name { get; set; } public Int32 Age { get; set; } + public static String ClassType { get { return nameof(Person); } } } // Private private DataTable GetTable() { - DataTable table = new DataTable(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); @@ -98,9 +102,9 @@ table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); return table; } + // Override - - #endregion + #endregion Methods } } diff --git a/ClosedXML_Examples/Misc/InsertingTables.cs b/ClosedXML_Examples/Misc/InsertingTables.cs deleted file mode 100644 index 50be801..0000000 --- a/ClosedXML_Examples/Misc/InsertingTables.cs +++ /dev/null @@ -1,102 +0,0 @@ -using ClosedXML.Attributes; -using ClosedXML.Excel; -using System; -using System.Collections.Generic; -using System.Data; -using System.Linq; - -namespace ClosedXML_Examples.Misc -{ - public class InsertingTables : IXLExample - { - #region Methods - - // Public - public void Create(String filePath) - { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Inserting Tables"); - - // From a list of strings - var listOfStrings = new List(); - listOfStrings.Add("House"); - listOfStrings.Add("Car"); - ws.Cell(1, 1).Value = "From Strings"; - ws.Cell(1, 1).AsRange().AddToNamed("Titles"); - ws.Cell(2, 1).InsertTable(listOfStrings); - - // From a list of arrays - var listOfArr = new List(); - listOfArr.Add(new Int32[] { 1, 2, 3 }); - listOfArr.Add(new Int32[] { 1 }); - listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); - ws.Cell(1, 3).Value = "From Arrays"; - ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); - ws.Cell(2, 3).InsertTable(listOfArr); - - // From a DataTable - var dataTable = GetTable(); - ws.Cell(7, 1).Value = "From DataTable"; - ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); - ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); - - // From a query - var list = new List(); - list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); - list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); - list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); - list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); - - var people = from p in list - where p.Age >= 21 - select p; - - ws.Cell(7, 6).Value = "From Query"; - ws.Range(7, 6, 7, 8).Merge().AddToNamed("Titles"); - ws.Cell(8, 6).InsertTable(people.AsEnumerable()); - - // Prepare the style for the titles - var titlesStyle = wb.Style; - titlesStyle.Font.Bold = true; - titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - - // Format all titles in one shot - wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; - - ws.Columns().AdjustToContents(); - - wb.SaveAs(filePath); - } - - private class Person - { - [XLColumn(Header = "House Street")] - public String House { get; set; } - - public String Name { get; set; } - public Int32 Age { get; set; } - } - - // Private - private DataTable GetTable() - { - DataTable table = new DataTable(); - table.Columns.Add("Dosage", typeof(int)); - table.Columns.Add("Drug", typeof(string)); - table.Columns.Add("Patient", typeof(string)); - table.Columns.Add("Date", typeof(DateTime)); - - table.Rows.Add(25, "Indocin", "David", new DateTime(2000, 1, 1)); - table.Rows.Add(50, "Enebrel", "Sam", new DateTime(2000, 1, 2)); - table.Rows.Add(10, "Hydralazine", "Christoff", new DateTime(2000, 1, 3)); - table.Rows.Add(21, "Combivent", "Janet", new DateTime(2000, 1, 4)); - table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); - return table; - } - - // Override - - #endregion Methods - } -} diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index fa479da..a94bb10 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -110,6 +110,8 @@ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + pt.SetRowHeaderCaption("Pastry name"); + #endregion Different kind of pivot #region Pivot table with collapsed fields @@ -162,6 +164,8 @@ pt.Subtotals = XLPivotSubtotals.DoNotShow; + pt.SetColumnHeaderCaption("Measures"); + ptSheet.Columns().AdjustToContents(); #endregion Pivot table with subtotals disabled diff --git a/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML_Examples/Ranges/UsingTables.cs deleted file mode 100644 index e5c24b2..0000000 --- a/ClosedXML_Examples/Ranges/UsingTables.cs +++ /dev/null @@ -1,106 +0,0 @@ -using System; -using System.IO; -using ClosedXML.Excel; -using System.Linq; - - -namespace ClosedXML_Examples.Ranges -{ - public class UsingTables : IXLExample - { - #region Methods - - // Public - public void Create(String filePath) - { - string tempFile = ExampleHelper.GetTempFilePath(filePath); - try - { - new BasicTable().Create(tempFile); - var wb = new XLWorkbook(tempFile); - var ws = wb.Worksheet(1); - ws.Name = "Contacts Table"; - var firstCell = ws.FirstCellUsed(); - var lastCell = ws.LastCellUsed(); - var range = ws.Range(firstCell.Address, lastCell.Address); - 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); - // Put back the date and number formats - range.Column(4).Style.NumberFormat.NumberFormatId = 15; - range.Column(5).Style.NumberFormat.Format = "$ #,##0"; - - var table = range.CreateTable(); // You can also use range.AsTable() if you want to - // manipulate the range as a table but don't want - // to create the table in the worksheet. - - // Let's activate the Totals row and add the sum of Income - table.ShowTotalsRow = true; - table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; - // Just for fun let's add the text "Sum Of Income" to the totals row - table.Field(0).TotalsRowLabel = "Sum Of Income"; - - // Copy all the headers - Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; - Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; - ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; - foreach (var cell in table.HeadersRow().Cells()) - { - currentRow++; - ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; - } - - // Format the headers as a table with a different style and no autofilters - var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); - var htLastCell = ws.Cell(currentRow, columnWithHeaders); - var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); - headersTable.Theme = XLTableTheme.TableStyleLight10; - headersTable.ShowAutoFilter = false; - - // Add a custom formula to the headersTable - headersTable.ShowTotalsRow = true; - headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; - - // Copy the names - Int32 columnWithNames = columnWithHeaders + 2; - currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow - ws.Cell(currentRow, columnWithNames).Value = "Names"; - foreach (var row in table.DataRange.Rows()) - { - currentRow++; - var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name - var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name - var name = String.Format("{0} {1}", fName, lName); - ws.Cell(currentRow, columnWithNames).Value = name; - } - - // Format the names as a table with a different style and no autofilters - var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); - var ntLastCell = ws.Cell(currentRow, columnWithNames); - var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); - namesTable.Theme = XLTableTheme.TableStyleLight12; - namesTable.ShowAutoFilter = false; - - ws.Columns().AdjustToContents(); - ws.Columns("A,G,I").Width = 3; - - wb.SaveAs(filePath); - } - finally - { - if (File.Exists(tempFile)) - { - File.Delete(tempFile); - } - } - } - - // Private - - // Override - - - #endregion - } -} diff --git a/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML_Examples/Styles/StyleFont.cs index 7da4adf..6fed30f 100644 --- a/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML_Examples/Styles/StyleFont.cs @@ -1,6 +1,5 @@ +using ClosedXML.Excel; using System; -using ClosedXML.Excel; - namespace ClosedXML_Examples.Styles { @@ -23,6 +22,11 @@ ws.Cell(++ro, co).Value = "FontFamilyNumbering - Script"; ws.Cell(ro, co).Style.Font.FontFamilyNumbering = XLFontFamilyNumberingValues.Script; + ws.Cell(++ro, co).Value = "FontCharSet - العربية التنضيد"; + ws.Cell(ro, co).Style + .Font.SetFontName("Arabic Typesetting") + .Font.SetFontCharSet(XLFontCharSet.Arabic); + ws.Cell(++ro, co).Value = "FontName - Stencil"; ws.Cell(ro, co).Style.Font.FontName = "Stencil"; diff --git a/ClosedXML_Examples/Tables/InsertingTables.cs b/ClosedXML_Examples/Tables/InsertingTables.cs new file mode 100644 index 0000000..176eed1 --- /dev/null +++ b/ClosedXML_Examples/Tables/InsertingTables.cs @@ -0,0 +1,111 @@ +using ClosedXML.Attributes; +using ClosedXML.Excel; +using System; +using System.Collections.Generic; +using System.Data; +using System.Linq; + +namespace ClosedXML_Examples.Tables +{ + public class InsertingTables : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Inserting Tables"); + + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertTable(listOfStrings); + + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertTable(listOfArr); + + // From a DataTable + var dataTable = GetTable(); + ws.Cell(7, 1).Value = "From DataTable"; + ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); + ws.Cell(8, 1).InsertTable(dataTable); + + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + + var people = from p in list + where p.Age >= 21 + select p; + + ws.Cell(7, 6).Value = "From Query"; + ws.Range(7, 6, 7, 9).Merge().AddToNamed("Titles"); + ws.Cell(8, 6).InsertTable(people); + + ws.Cell(15, 6).Value = "From List"; + ws.Range(15, 6, 15, 9).Merge().AddToNamed("Titles"); + ws.Cell(16, 6).InsertTable(people); + + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; + + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + } + + private class Person + { + [XLColumn(Header = "House Street")] + public String House { get; set; } + + public String Name { get; set; } + public Int32 Age { get; set; } + + [XLColumn(Header = "Class Type")] + public static String ClassType { get { return nameof(Person); } } + } + + // Private + private DataTable GetTable() + { + DataTable table = new DataTable(); + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", new DateTime(2000, 1, 1)); + table.Rows.Add(50, "Enebrel", "Sam", new DateTime(2000, 1, 2)); + table.Rows.Add(10, "Hydralazine", "Christoff", new DateTime(2000, 1, 3)); + table.Rows.Add(21, "Combivent", "Janet", new DateTime(2000, 1, 4)); + table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); + return table; + } + + // Override + + #endregion Methods + } +} diff --git a/ClosedXML_Examples/Tables/ResizingTables.cs b/ClosedXML_Examples/Tables/ResizingTables.cs new file mode 100644 index 0000000..64ad34e --- /dev/null +++ b/ClosedXML_Examples/Tables/ResizingTables.cs @@ -0,0 +1,47 @@ +using ClosedXML.Excel; +using System; +using System.Linq; + +// TODO: Add example to Wiki + +namespace ClosedXML_Examples.Tables +{ + public class ResizingTables : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i), + Integer = 64 + i + }); + + var table1 = ws1.Cell("B2").InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + + table1.Fields.First().TotalsRowLabel = "Sum of Integer"; + table1.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var ws2 = ws1.CopyTo("Sheet2"); + var table2 = ws2.Tables.First(); + table2.Resize(table2.FirstCell(), table2.LastCell().CellLeft().CellAbove(3)); + + var ws3 = ws2.CopyTo("Sheet3"); + var table3 = ws3.Tables.First(); + table3.Resize(table3.FirstCell().CellLeft(), table3.LastCell().CellRight().CellBelow(1)); + + wb.Worksheets.ForEach(ws => ws.Columns().AdjustToContents()); + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/Tables/UsingTables.cs b/ClosedXML_Examples/Tables/UsingTables.cs new file mode 100644 index 0000000..639a0a0 --- /dev/null +++ b/ClosedXML_Examples/Tables/UsingTables.cs @@ -0,0 +1,105 @@ +using ClosedXML.Excel; +using System; +using System.IO; + +namespace ClosedXML_Examples.Tables +{ + public class UsingTables : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + string tempFile = ExampleHelper.GetTempFilePath(filePath); + try + { + new BasicTable().Create(tempFile); + using (var wb = new XLWorkbook(tempFile)) + { + var ws = wb.Worksheet(1); + ws.Name = "Contacts Table"; + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var range = ws.Range(firstCell.Address, lastCell.Address); + 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); + // Put back the date and number formats + range.Column(4).Style.NumberFormat.NumberFormatId = 15; + range.Column(5).Style.NumberFormat.Format = "$ #,##0"; + + var table = range.CreateTable(); // You can also use range.AsTable() if you want to + // manipulate the range as a table but don't want + // to create the table in the worksheet. + + // Let's activate the Totals row and add the sum of Income + table.ShowTotalsRow = true; + table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; + // Just for fun let's add the text "Sum Of Income" to the totals row + table.Field(0).TotalsRowLabel = "Sum Of Income"; + + // Copy all the headers + Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; + Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; + ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; + foreach (var cell in table.HeadersRow().Cells()) + { + currentRow++; + ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; + } + + // Format the headers as a table with a different style and no autofilters + var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); + var htLastCell = ws.Cell(currentRow, columnWithHeaders); + var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); + headersTable.Theme = XLTableTheme.TableStyleLight10; + headersTable.ShowAutoFilter = false; + + // Add a custom formula to the headersTable + headersTable.ShowTotalsRow = true; + headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; + + // Copy the names + Int32 columnWithNames = columnWithHeaders + 2; + currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow + ws.Cell(currentRow, columnWithNames).Value = "Names"; + foreach (var row in table.DataRange.Rows()) + { + currentRow++; + var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name + var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name + var name = String.Format("{0} {1}", fName, lName); + ws.Cell(currentRow, columnWithNames).Value = name; + } + + // Format the names as a table with a different style and no autofilters + var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); + var ntLastCell = ws.Cell(currentRow, columnWithNames); + var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); + namesTable.Theme = XLTableTheme.TableStyleLight12; + namesTable.ShowAutoFilter = false; + + ws.Columns().AdjustToContents(); + ws.Columns("A,G,I").Width = 3; + + wb.SaveAs(filePath); + } + } + finally + { + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } + } + } + + // Private + + // Override + + #endregion Methods + } +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index a0834a3..08ec404 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -76,7 +76,9 @@ + + @@ -85,6 +87,7 @@ + @@ -172,7 +175,6 @@ - @@ -222,7 +224,6 @@ - @@ -292,6 +293,11 @@ + + + + + diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 0760da0..333c347 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -37,6 +37,12 @@ } [Test] + public void CFDataBarNegative() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFDataBarNegative.xlsx"); + } + + [Test] public void CFEndsWith() { TestHelper.RunTestExample(@"ConditionalFormatting\CFEndsWith.xlsx"); @@ -113,5 +119,11 @@ { TestHelper.RunTestExample(@"ConditionalFormatting\CFMultipleConditions.xlsx"); } + + [Test] + public void CFStopIfTrue() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFStopIfTrue.xlsx"); + } } } diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index 4f14453..d74a9fc 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -133,11 +133,6 @@ TestHelper.RunTestExample(@"Misc\InsertingData.xlsx"); } - [Test] - public void InsertingTables() - { - TestHelper.RunTestExample(@"Misc\InsertingTables.xlsx"); - } [Test] public void LambdaExpressions() @@ -204,11 +199,11 @@ { TestHelper.RunTestExample(@"Misc\WorkbookProperties.xlsx"); } - + [Test] public void WorkbookProtection() { TestHelper.RunTestExample(@"Misc\WorkbookProtection.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/RangesTests.cs b/ClosedXML_Tests/Examples/RangesTests.cs index fe3e1e2..59cd62b 100644 --- a/ClosedXML_Tests/Examples/RangesTests.cs +++ b/ClosedXML_Tests/Examples/RangesTests.cs @@ -99,12 +99,6 @@ } [Test] - public void UsingTables() - { - TestHelper.RunTestExample(@"Ranges\UsingTables.xlsx"); - } - - [Test] public void AddingRowToTables() { TestHelper.RunTestExample(@"Ranges\AddingRowToTables.xlsx"); @@ -116,4 +110,4 @@ TestHelper.RunTestExample(@"Ranges\WalkingRanges.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/TablesTests.cs b/ClosedXML_Tests/Examples/TablesTests.cs new file mode 100644 index 0000000..934bf65 --- /dev/null +++ b/ClosedXML_Tests/Examples/TablesTests.cs @@ -0,0 +1,27 @@ +using ClosedXML_Examples.Tables; +using NUnit.Framework; + +namespace ClosedXML_Tests.Examples +{ + [TestFixture] + public class TablesTests + { + [Test] + public void InsertingTables() + { + TestHelper.RunTestExample(@"Tables\InsertingTables.xlsx"); + } + + [Test] + public void ResizingTables() + { + TestHelper.RunTestExample(@"Tables\ResizingTables.xlsx"); + } + + [Test] + public void UsingTables() + { + TestHelper.RunTestExample(@"Tables\UsingTables.xlsx"); + } + } +} diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index c630f5a..9a4356a 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -1,8 +1,9 @@ -using System; -using System.Collections.Generic; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; namespace ClosedXML_Tests { @@ -29,7 +30,7 @@ listOfArr.Add(6); table.DataRange.InsertRowsBelow(listOfArr.Count - table.DataRange.RowCount()); - table.DataRange.FirstCell().InsertData(listOfArr.AsEnumerable()); + table.DataRange.FirstCell().InsertData(listOfArr); Assert.AreEqual("A1:A5", table.AutoFilter.Range.RangeAddress.ToStringRelative()); } @@ -72,5 +73,57 @@ ws.AutoFilter.Clear(); Assert.That(!ws.AutoFilter.Enabled); } + + [Test] + public void CanClearAutoFilter2() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Names"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(); + Assert.That(ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + } + } + + [Test] + public void CanCopyAutoFilterToNewSheetOnNewWorkbook() + { + using (var ms1 = new MemoryStream()) + using (var ms2 = new MemoryStream()) + { + using (var wb1 = new XLWorkbook()) + using (var wb2 = new XLWorkbook()) + { + var ws = wb1.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Names"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.RangeUsed().SetAutoFilter(); + + wb1.SaveAs(ms1); + + ws.CopyTo(wb2, ws.Name); + wb2.SaveAs(ms2); + } + + using (var wb2 = new XLWorkbook(ms2)) + { + Assert.IsTrue(wb2.Worksheets.First().AutoFilter.Enabled); + } + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs new file mode 100644 index 0000000..b1be35f --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs @@ -0,0 +1,29 @@ +using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Globalization; +using System.Linq; +using System.Text; +using System.Threading; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class CalcEngineExceptionTests + { + [OneTimeSetUp] + public void SetCultureInfo() + { + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US"); + } + + [Test] + public void InvalidCharNumber() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(-2)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(270)")); + } + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs index 93ad203..19de6ca 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -158,6 +158,17 @@ } #endregion IsLogical Tests + [Test] + public void IsNA() + { + object actual; + actual = XLWorkbook.EvaluateExpr("ISNA(#N/A)"); + Assert.AreEqual(true, actual); + + actual = XLWorkbook.EvaluateExpr("ISNA(#REF!)"); + Assert.AreEqual(false, actual); + } + #region IsNotText Tests [Test] @@ -288,6 +299,30 @@ } #endregion IsOdd Test + [Test] + public void IsRef() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; + + ws.Cell("B1").FormulaA1 = "ISREF(A1)"; + ws.Cell("B2").FormulaA1 = "ISREF(5)"; + ws.Cell("B3").FormulaA1 = "ISREF(YEAR(TODAY()))"; + + bool actual; + actual = ws.Cell("B1").GetValue(); + Assert.AreEqual(true, actual); + + actual = ws.Cell("B2").GetValue(); + Assert.AreEqual(false, actual); + + actual = ws.Cell("B3").GetValue(); + Assert.AreEqual(false, actual); + } + } + #region IsText Tests [Test] diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index f416784..a18e509 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; @@ -122,11 +123,11 @@ [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.TypeOf()); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,2,TRUE)")); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)")); } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 5012bb5..c3cd7ce 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -1,6 +1,9 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; +using System.Globalization; +using System.Linq; namespace ClosedXML_Tests.Excel.CalcEngine { @@ -9,6 +12,100 @@ { private readonly double tolerance = 1e-10; + [TestCase(4, 3, 20)] + [TestCase(10, 3, 220)] + [TestCase(0, 0, 1)] + public void Combina_CalculatesCorrectValues(int number, int chosen, int expectedResult) + { + var actualResult = XLWorkbook.EvaluateExpr($"COMBINA({number}, {chosen})"); + Assert.AreEqual(expectedResult, (long)actualResult); + } + + [Theory] + public void Combina_Returns1WhenChosenIs0([Range(0, 10)]int number) + { + Combina_CalculatesCorrectValues(number, 0, 1); + } + + [TestCase(4.23, 3, 20)] + [TestCase(10.4, 3.14, 220)] + [TestCase(0, 0.4, 1)] + public void Combina_TruncatesNumbersCorrectly(double number, double chosen, int expectedResult) + { + var actualResult = XLWorkbook.EvaluateExpr(string.Format( + @"COMBINA({0}, {1})", + number.ToString(CultureInfo.InvariantCulture), + chosen.ToString(CultureInfo.InvariantCulture))); + + Assert.AreEqual(expectedResult, (long)actualResult); + } + + [TestCase(-1, 2)] + [TestCase(-3, -2)] + [TestCase(2, -2)] + public void Combina_ThrowsNumExceptionOnInvalidValues(int number, int chosen) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"COMBINA({0}, {1})", + number.ToString(CultureInfo.InvariantCulture), + chosen.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase(1, 0.642092616)] + [TestCase(2, -0.457657554)] + [TestCase(3, -7.015252551)] + [TestCase(4, 0.863691154)] + [TestCase(5, -0.295812916)] + [TestCase(6, -3.436353004)] + [TestCase(7, 1.147515422)] + [TestCase(8, -0.147065064)] + [TestCase(9, -2.210845411)] + [TestCase(10, 1.542351045)] + [TestCase(11, -0.004425741)] + [TestCase(Math.PI * 0.5, 0)] + [TestCase(45, 0.617369624)] + [TestCase(-2, 0.457657554)] + [TestCase(-3, 7.015252551)] + public void Cot(double input, double expected) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"COT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual, tolerance * 10.0); + } + + [Test] + public void Cot_Input0() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("COT(0)")); + } + + [TestCase("FF", 16, 255)] + [TestCase("111", 2, 7)] + [TestCase("zap", 36, 45745)] + public void Decimal(string inputString, int radix, int expectedResult) + { + var actualResult = XLWorkbook.EvaluateExpr($"DECIMAL(\"{inputString}\", {radix})"); + Assert.AreEqual(expectedResult, actualResult); + } + + [Test] + public void Decimal_ZeroIsZeroInAnyRadix([Range(2, 36)] int radix) + { + Assert.AreEqual(0, XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + + [Theory] + public void Decimal_ReturnsErrorForRadiansGreater36([Range(37, 255)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + + [Theory] + public void Decimal_ReturnsErrorForRadiansSmaller2([Range(-5, 1)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); + } + [Test] public void Floor() { @@ -119,5 +216,54 @@ actual = (double)XLWorkbook.EvaluateExpr(@"MOD(6.2, 1.1)"); Assert.AreEqual(0.7, actual, tolerance); } + + [Test] + public void SumProduct() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.FirstCell().Value = Enumerable.Range(1, 10); + ws.FirstCell().CellRight().Value = Enumerable.Range(1, 10).Reverse(); + + Assert.AreEqual(2, ws.Evaluate("SUMPRODUCT(A2)")); + Assert.AreEqual(55, ws.Evaluate("SUMPRODUCT(A1:A10)")); + Assert.AreEqual(220, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); + + Assert.Throws(() => ws.Evaluate("SUMPRODUCT(A1:A10, B1:B5)")); + } + } + + [TestCase(1, 0.850918128)] + [TestCase(2, 0.275720565)] + [TestCase(3, 0.09982157)] + [TestCase(4, 0.03664357)] + [TestCase(5, 0.013476506)] + [TestCase(6, 0.004957535)] + [TestCase(7, 0.001823765)] + [TestCase(8, 0.000670925)] + [TestCase(9, 0.00024682)] + [TestCase(10, 0.000090799859712122200000)] + [TestCase(11, 0.0000334034)] + public void CSch_CalculatesCorrectValues(double input, double expectedOutput) + { + Assert.AreEqual(expectedOutput, (double)XLWorkbook.EvaluateExpr($@"CSCH({input})"), 0.000000001); + } + + [Test] + public void Csch_ReturnsDivisionByZeroErrorOnInput0() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("CSCH(0)")); + } + + [TestCase(8.9, 8)] + [TestCase(-8.9, -9)] + public void Int(double input, double expected) + { + var actual = XLWorkbook.EvaluateExpr(string.Format(@"INT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual); + + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index efb58d3..59d0a59 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using ClosedXML.Excel.CalcEngine; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Globalization; @@ -20,13 +21,13 @@ [Test] public void Char_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char("""")"), Throws.TypeOf()); + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char("""")")); } [Test] public void Char_Input_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char(9797)"), Throws.TypeOf()); + Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)")); } [Test] diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 2fb6166..161f17a 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -59,7 +59,7 @@ IXLCell cell = ws.Cell("A1"); var doubleList = new List { 1.0 / 0.0 }; - cell.Value = doubleList.AsEnumerable(); + cell.Value = doubleList; Assert.AreNotEqual(XLCellValues.Number, cell.DataType); } @@ -70,7 +70,7 @@ IXLCell cell = ws.Cell("A1"); var doubleList = new List { 0.0 / 0.0 }; - cell.Value = doubleList.AsEnumerable(); + cell.Value = doubleList; Assert.AreNotEqual(XLCellValues.Number, cell.DataType); } @@ -235,7 +235,6 @@ Assert.IsFalse(success); } - [Test] public void TryGetValue_DateTime_BadString2() { @@ -426,5 +425,90 @@ Assert.AreEqual(string.Empty, cell.Value); } } + + [Test] + public void CurrentRegion() + { + // Partially based on sample in https://github.com/ClosedXML/ClosedXML/issues/120 + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell("B1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("C1").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + //Deliberately D2 + ws.Cell("D2").SetValue("x") + .CellBelow().SetValue("x"); + + ws.Cell("G1").SetValue("x") + .CellBelow() // skip a cell + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // Deliberately H2 + ws.Cell("H2").SetValue("x") + .CellBelow().SetValue("x") + .CellBelow().SetValue("x"); + + // A diagonal + ws.Cell("E8").SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x") + .CellBelow().CellRight().SetValue("x"); + + Assert.AreEqual("A10:A10", ws.Cell("A10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B5:B5", ws.Cell("B5").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("P1:P1", ws.Cell("P1").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("B1:D3", ws.Cell("D3").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:D4", ws.Cell("D4").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("B1:D3").Cells()) + { + Assert.AreEqual("B1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + foreach (var c in ws.Range("A1:A3").Cells()) + { + Assert.AreEqual("A1:D3", c.CurrentRegion.RangeAddress.ToString()); + } + + Assert.AreEqual("A1:D4", ws.Cell("A4").CurrentRegion.RangeAddress.ToString()); + + foreach (var c in ws.Range("E1:E3").Cells()) + { + Assert.AreEqual("B1:E3", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("B1:E4", ws.Cell("E4").CurrentRegion.RangeAddress.ToString()); + + //// SECOND REGION + foreach (var c in ws.Range("F1:F4").Cells()) + { + Assert.AreEqual("F1:H4", c.CurrentRegion.RangeAddress.ToString()); + } + Assert.AreEqual("F1:H5", ws.Cell("F5").CurrentRegion.RangeAddress.ToString()); + + //// DIAGONAL + Assert.AreEqual("E8:I12", ws.Cell("E8").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("G10").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("H11").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("I12").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("E8:I12", ws.Cell("G9").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:I12", ws.Cell("F10").CurrentRegion.RangeAddress.ToString()); + + Assert.AreEqual("D7:I12", ws.Cell("D7").CurrentRegion.RangeAddress.ToString()); + Assert.AreEqual("E8:J13", ws.Cell("J13").CurrentRegion.RangeAddress.ToString()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs index 4d0329f..686c1f4 100644 --- a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs +++ b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs @@ -9,13 +9,14 @@ [Test] public void ToStringTest() { - IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLAddress address = ws.Cell(1, 1).Address; Assert.AreEqual("A1", address.ToString()); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("Sheet1!A1", address.ToString(XLReferenceStyle.Default, true)); Assert.AreEqual("A1", address.ToStringRelative()); Assert.AreEqual("Sheet1!A1", address.ToStringRelative(true)); @@ -39,6 +40,7 @@ Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!A1", address.ToString(XLReferenceStyle.Default, true)); Assert.AreEqual("A1", address.ToStringRelative()); Assert.AreEqual("'Sheet 1'!A1", address.ToStringRelative(true)); diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index a06902e..0cd0edf 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -260,5 +260,40 @@ Assert.Throws(() => picture.Name = "picTURE 1"); } } + + [Test] + public void HandleDuplicatePictureIdsAcrossWorksheets() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png")) + { + (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2); + (ws1 as XLWorksheet).AddPicture(stream, "Picture 2", 3); + + //Internal method - used for loading files + var pic = (ws2 as XLWorksheet).AddPicture(stream, "Picture 1", 2) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(220, 155) as XLPicture; + + var id = pic.Id; + + pic.Id = id; + Assert.AreEqual(id, pic.Id); + + pic.Id = 3; + Assert.AreEqual(3, pic.Id); + + pic.Id = id; + + var pic2 = (ws2 as XLWorksheet).AddPicture(stream, "Picture 2", 3) + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(440, 300) as XLPicture; + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 16ac2a1..3ff4c55 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Linq; @@ -173,5 +174,17 @@ actual = XLWorkbook.EvaluateExpr("+MID(\"This is a test\", 6, 2)"); Assert.AreEqual("is", actual); } + + [Test] + public void FormulasWithErrors() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#REF!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#VALUE!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#DIV/0!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NAME?)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#N/A)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NULL!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NUM!)")); + } } } diff --git a/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs b/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs new file mode 100644 index 0000000..95b2431 --- /dev/null +++ b/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs @@ -0,0 +1,32 @@ +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.Misc +{ + [TestFixture] + public class HyperlinkTests + { + [Test] + public void TestHyperlinks() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet1"); + var ws2 = wb.Worksheets.Add("Sheet2"); + + var targetCell = ws2.Cell("A1"); + var targetRange = ws2.Range("A1", "B1"); + + var linkCell1 = ws1.Cell("A1"); + linkCell1.Value = "Link to IXLCell"; + linkCell1.Hyperlink = new XLHyperlink(targetCell); + Assert.AreEqual("Sheet2!A1", linkCell1.Hyperlink.InternalAddress); + + var linkRange1 = ws1.Cell("A2"); + linkRange1.Value = "Link to IXLRangeBase"; + linkRange1.Hyperlink = new XLHyperlink(targetRange); + Assert.AreEqual("Sheet2!A1:B1", linkRange1.Hyperlink.InternalAddress); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index 669e811..c103c6e 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -13,6 +13,7 @@ IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; Assert.AreEqual("A1:A1", address.ToString()); + Assert.AreEqual("Sheet1!R1C1:R1C1", address.ToString(XLReferenceStyle.R1C1, true)); Assert.AreEqual("A1:A1", address.ToStringRelative()); Assert.AreEqual("Sheet1!A1:A1", address.ToStringRelative(true)); @@ -33,6 +34,7 @@ IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; Assert.AreEqual("A1:A1", address.ToString()); + Assert.AreEqual("'Sheet 1'!R1C1:R1C1", address.ToString(XLReferenceStyle.R1C1, true)); Assert.AreEqual("A1:A1", address.ToStringRelative()); Assert.AreEqual("'Sheet 1'!A1:A1", address.ToStringRelative(true)); diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 348b213..28a7b6f 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests { @@ -106,7 +106,7 @@ IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( - namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] {"John", "Hank", "Dagny"})); + namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); } [Test] @@ -184,7 +184,119 @@ // memoryStream.Close(); // } - //} + + [Test] + public void GrowRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.AreEqual("A1:B2", ws.Cell("A1").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:B3", ws.Cell("A2").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:C2", ws.Cell("B1").AsRange().Grow().RangeAddress.ToString()); + + Assert.AreEqual("E4:G6", ws.Cell("F5").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("D3:H7", ws.Cell("F5").AsRange().Grow(2).RangeAddress.ToString()); + Assert.AreEqual("A1:DB105", ws.Cell("F5").AsRange().Grow(100).RangeAddress.ToString()); + } + } + + [Test] + public void ShrinkRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.Null(ws.Cell("A1").AsRange().Shrink()); + Assert.Null(ws.Range("B2:C3").Shrink()); + Assert.AreEqual("C3:C3", ws.Range("B2:D4").Shrink().RangeAddress.ToString()); + Assert.AreEqual("K11:P16", ws.Range("A1:Z26").Shrink(10).RangeAddress.ToString()); + + // Grow and shrink back + Assert.AreEqual("Z26:Z26", ws.Cell("Z26").AsRange().Grow(10).Shrink(10).RangeAddress.ToString()); + } + } + + [Test] + public void Intersection() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual("D9:G11", ws.Range("B9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:G11", ws.Range("E9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Cell("E9").AsRange().Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Range("D4:G16").Intersection(ws.Cell("E9").AsRange()).RangeAddress.ToString()); + + Assert.Null(ws.Cell("A1").AsRange().Intersection(ws.Cell("C3").AsRange())); + + Assert.Null(ws.Range("A1:C3").Intersection(null)); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.Null(ws.Intersection(otherWs)); + Assert.Null(ws.Cell("A1").AsRange().Intersection(otherWs.Cell("A2").AsRange())); + } + } + + [Test] + public void Union() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(64, ws.Range("B9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(58, ws.Range("E9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Cell("E9").AsRange().Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Range("D4:G16").Union(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(2, ws.Cell("A1").AsRange().Union(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Union(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Union(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Union(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void Difference() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(12, ws.Range("B9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(6, ws.Range("E9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(0, ws.Cell("E9").AsRange().Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(51, ws.Range("D4:G16").Difference(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(1, ws.Cell("A1").AsRange().Difference(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Difference(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Difference(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Difference(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void SurroundingCells() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(3, ws.FirstCell().AsRange().SurroundingCells().Count()); + Assert.AreEqual(8, ws.Cell("C3").AsRange().SurroundingCells().Count()); + Assert.AreEqual(16, ws.Range("C3:D6").AsRange().SurroundingCells().Count()); + + Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs index 326340b..87ab21a 100644 --- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs +++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs @@ -2,7 +2,6 @@ using NUnit.Framework; using System; using System.Data; -using System.Linq; namespace ClosedXML_Tests.Excel { @@ -24,7 +23,7 @@ } ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd"; - ws.Cell("A1").InsertData(table.AsEnumerable()); + ws.Cell("A1").InsertData(table); Assert.AreEqual("yy-MM-dd", ws.Cell("A5").Style.DateFormat.Format); ws.Row(1).Style.NumberFormat.Format = "yy-MM-dd"; diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 1a35952..9d30ab8 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -52,6 +52,20 @@ } [Test] + public void PreventAddingOfEmptyDataTable() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var dt = new DataTable(); + var table = ws.FirstCell().InsertTable(dt); + + Assert.AreEqual(null, table); + } + } + + [Test] public void CanSaveTableCreatedFromSingleRow() { using (var wb = new XLWorkbook()) @@ -133,7 +147,7 @@ DataRow dr = dt.NewRow(); dr[columnName] = "some text"; dt.Rows.Add(dr); - ws.Cell(1, 1).InsertTable(dt.AsEnumerable()); + ws.Cell(1, 1).InsertTable(dt); IXLTable table1 = ws.Table(0); string fieldName1 = table1.Field(0).Name; @@ -451,5 +465,201 @@ Assert.AreEqual(2, table.Fields.Last().Index); } } + + [Test] + public void OverlappingTablesThrowsException() + { + var dt = new DataTable("sheet1"); + dt.Columns.Add("col1", typeof(string)); + dt.Columns.Add("col2", typeof(double)); + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(dt, true); + Assert.Throws(() => ws.FirstCell().CellRight().InsertTable(dt, true)); + } + } + + [Test] + public void OverwritingTableTotalsRow() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var data2 = Enumerable.Range(1, 20) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('b', i), + Int = 64 + i + }); + + ws.FirstCell().CellBelow().InsertData(data2); + + table.Fields.ForEach(f => Assert.AreEqual(XLTotalsRowFunction.None, f.TotalsRowFunction)); + + Assert.AreEqual("11", table.Field(0).TotalsRowLabel); + Assert.AreEqual("K", table.Field(1).TotalsRowLabel); + Assert.AreEqual("bbbbbbbbbbb", table.Field(2).TotalsRowLabel); + } + } + + [Test] + public void CanResizeTable() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var data2 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('b', i), + Integer = 64 + i + }); + + ws.FirstCell().CellBelow().InsertData(data2); + table.Resize(table.FirstCell().Address, table.AsRange().LastCell().CellRight().Address); + + Assert.AreEqual(4, table.Fields.Count()); + + Assert.AreEqual("Column4", table.Field(3).Name); + + ws.Cell("D1").Value = "Integer"; + Assert.AreEqual("Integer", table.Field(3).Name); + } + } + + [Test] + public void TableAsDynamicEnumerable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l); + + foreach (var d in table.AsDynamicEnumerable()) + { + Assert.DoesNotThrow(() => + { + object value; + value = d.FirstColumn; + value = d.SecondColumn; + value = d.UnOrderedColumn; + value = d.SomeFieldNotProperty; + }); + } + } + } + + [Test] + public void TableAsDotNetDataTable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l).AsNativeDataTable(); + + Assert.AreEqual(4, table.Columns.Count); + Assert.AreEqual("FirstColumn", table.Columns[0].ColumnName); + Assert.AreEqual("SecondColumn", table.Columns[1].ColumnName); + Assert.AreEqual("SomeFieldNotProperty", table.Columns[2].ColumnName); + Assert.AreEqual("UnOrderedColumn", table.Columns[3].ColumnName); + + Assert.AreEqual(typeof(String), table.Columns[0].DataType); + Assert.AreEqual(typeof(String), table.Columns[1].DataType); + Assert.AreEqual(typeof(Double), table.Columns[2].DataType); + Assert.AreEqual(typeof(Double), table.Columns[3].DataType); + + var dr = table.Rows[0]; + Assert.AreEqual("b", dr["FirstColumn"]); + Assert.AreEqual("a", dr["SecondColumn"]); + Assert.AreEqual(4, dr["SomeFieldNotProperty"]); + Assert.AreEqual(999, dr["UnOrderedColumn"]); + + dr = table.Rows[1]; + Assert.AreEqual("d", dr["FirstColumn"]); + Assert.AreEqual("c", dr["SecondColumn"]); + Assert.AreEqual(5, dr["SomeFieldNotProperty"]); + Assert.AreEqual(777, dr["UnOrderedColumn"]); + } + } + + [Test] + public void TestTableCellTypes() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + Assert.AreEqual(XLTableCellType.Header, table.HeadersRow().Cell(1).TableCellType()); + Assert.AreEqual(XLTableCellType.Data, table.HeadersRow().Cell(1).CellBelow().TableCellType()); + Assert.AreEqual(XLTableCellType.Total, table.TotalsRow().Cell(1).TableCellType()); + Assert.AreEqual(XLTableCellType.None, ws.Cell("Z100").TableCellType()); + } + } + + //TODO: Delete table (not underlying range) } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 76f612e..33fafed 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -161,6 +161,27 @@ } [Test] + public void TryGetWorksheet() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + var ws2 = wb.AddWorksheet("Sheet2"); + + IXLWorksheet ws; + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("Sheet1", out ws)); + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("sheet1", out ws)); + Assert.IsTrue(wb.Worksheets.TryGetWorksheet("sHEeT1", out ws)); + Assert.IsFalse(wb.Worksheets.TryGetWorksheet("Sheeeet2", out ws)); + + Assert.IsTrue(wb.TryGetWorksheet("Sheet1", out ws)); + Assert.IsTrue(wb.TryGetWorksheet("sheet1", out ws)); + Assert.IsTrue(wb.TryGetWorksheet("sHEeT1", out ws)); + Assert.IsFalse(wb.TryGetWorksheet("Sheeeet2", out ws)); + } + } + + [Test] public void HideWorksheet() { using (var ms = new MemoryStream()) diff --git a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx index bac6e30..e2ac29f 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx index 3879998..5c9ae0a 100644 --- a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx new file mode 100644 index 0000000..fa46948 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx index 80be4ab..e57b38d 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/CFNotBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx index 5f4f6ce..e233171 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/CFStopIfTrue.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx new file mode 100644 index 0000000..9894731 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx index 53aa802..d2a90ba 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx index 237d910..3ec78e0 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx index 21ea101..3f4195a 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index 87f74be..d7b9afd 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx index 5d0b538..f451273 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx index a3c6ea5..1f99477 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx index 222ba7b..d724e36 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx index c8ae77a..d9d1a57 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx deleted file mode 100644 index 212cbfe..0000000 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx +++ /dev/null Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx index 9839a6f..2cbaac9 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx index 81bc2a4..a1aa8d0 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 7c0b68e..247daba 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx index c1c9431..5e18500 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx index 6cdd101..9588f7d 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx index a33e2a6..adecfee 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx index 3f1bafd..453328f 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index 8b26d86..bd2c3d6 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx index 44fbbbe..037fc86 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx deleted file mode 100644 index 288d1fe..0000000 --- a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx +++ /dev/null Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx index 4a705f7..e12dfc5 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx index 0dadd37..a9c9728 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx new file mode 100644 index 0000000..89de679 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx new file mode 100644 index 0000000..dad3954 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx new file mode 100644 index 0000000..288d1fe --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Utils/StreamHelper.cs b/ClosedXML_Tests/Utils/StreamHelper.cs index b68a11f..8ecb4f9 100644 --- a/ClosedXML_Tests/Utils/StreamHelper.cs +++ b/ClosedXML_Tests/Utils/StreamHelper.cs @@ -51,7 +51,7 @@ throw new ArgumentException("Can't write to stream", "pStream"); } - #endregion + #endregion Check params foreach (byte b in pBynaryArray) { @@ -86,7 +86,7 @@ throw new ArgumentException("Can't write to stream", "streamToWrite"); } - #endregion + #endregion Check params var buf = new byte[512]; long length; @@ -135,37 +135,53 @@ throw new ArgumentException("Must be in position 0", "other"); } - #endregion + #endregion Check - var stringOne = new StreamReader(one).ReadToEnd().StripColumnWidths(stripColumnWidths); - var stringOther = new StreamReader(other).ReadToEnd().StripColumnWidths(stripColumnWidths); + var stringOne = new StreamReader(one).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); + var stringOther = new StreamReader(other).ReadToEnd().RemoveIgnoredParts(stripColumnWidths, ignoreGuids: true); return stringOne == stringOther; } + private static string RemoveIgnoredParts(this string s, Boolean ignoreColumnWidths, Boolean ignoreGuids) + { + if (ignoreColumnWidths) + s = RemoveColumnWidths(s); + + if (ignoreGuids) + s = RemoveGuids(s); + + return s; + } + private static Regex columnRegex = new Regex("", RegexOptions.Compiled); private static Regex widthRegex = new Regex("width=\"\\d+(\\.\\d+)?\"\\s+", RegexOptions.Compiled); - private static string StripColumnWidths(this string s, bool stripIt) + private static String RemoveColumnWidths(String s) { - if (!stripIt) - return s; - else - { - var replacements = new Dictionary(); - - foreach (var m in columnRegex.Matches(s).OfType()) - { - var original = m.Groups[0].Value; - var replacement = widthRegex.Replace(original, ""); - replacements.Add(original, replacement); - } + var replacements = new Dictionary(); - foreach (var r in replacements) - { - s = s.Replace(r.Key, r.Value); - } - return s; + foreach (var m in columnRegex.Matches(s).OfType()) + { + var original = m.Groups[0].Value; + var replacement = widthRegex.Replace(original, ""); + replacements.Add(original, replacement); } + + foreach (var r in replacements) + { + s = s.Replace(r.Key, r.Value); + } + return s; + } + + private static Regex guidRegex = new Regex(@"{[0-9A-Fa-f]{8}-([0-9A-Fa-f]{4}-){3}[0-9A-Fa-f]{12}}", RegexOptions.Compiled | RegexOptions.Multiline); + + private static String RemoveGuids(String s) + { + return guidRegex.Replace(s, delegate (Match m) + { + return string.Empty; + }); } } -} \ No newline at end of file +}