diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 0b68acf..e761588 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -54,6 +54,7 @@ + diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index dcf4b76..a01a138 100644 --- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -128,7 +128,7 @@ { Boolean match = isText ? filter.Condition(row.Cell(kp.Key).GetString()) - : row.Cell(kp.Key).DataType == XLCellValues.Number && + : row.Cell(kp.Key).DataType == XLDataType.Number && filter.Condition(row.Cell(kp.Key).GetDouble()); if (firstFilter) { diff --git a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs index a045910..a624708 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -206,7 +206,7 @@ } var cell = row.Cell(_column); - if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue; + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; row.WorksheetRow().Unhide(); foundOne = true; } @@ -224,7 +224,7 @@ { using (var subColumn = column.Column(2, column.CellCount())) { - var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number); + var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLDataType.Number); if (takeTop) { if (type == XLTopBottomType.Items) @@ -284,7 +284,7 @@ } var cell = row.Cell(_column); - if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble())) continue; + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; row.WorksheetRow().Unhide(); foundOne = true; } @@ -304,17 +304,17 @@ { using (var subColumn = column.Column(2, column.CellCount())) { - Double average = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Average(); + Double average = subColumn.CellsUsed(c => c.DataType == XLDataType.Number).Select(c => c.GetDouble()).Average(); if (aboveAverage) { return - subColumn.CellsUsed(c => c.DataType == XLCellValues.Number). + subColumn.CellsUsed(c => c.DataType == XLDataType.Number). Select(c => c.GetDouble()).Where(c => c > average).Distinct(); } return - subColumn.CellsUsed(c => c.DataType == XLCellValues.Number). + subColumn.CellsUsed(c => c.DataType == XLDataType.Number). Select(c => c.GetDouble()).Where(c => c < average).Distinct(); } @@ -371,7 +371,7 @@ { Boolean match = isText ? condition(row.Cell(_column).GetString()) - : row.Cell(_column).DataType == XLCellValues.Number && + : row.Cell(_column).DataType == XLDataType.Number && condition(row.Cell(_column).GetDouble()); if (match) row.WorksheetRow().Unhide(); @@ -390,4 +390,4 @@ public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs index f9d2bd3..1f11d83 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -46,7 +46,7 @@ if ((isText && condition(row.Cell(_column).GetString())) || ( !isText && row.Cell(_column).DataType == - XLCellValues.Number && + XLDataType.Number && condition( row.Cell(_column).GetValue())) ) diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index 83d872a..e1b6e51 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -44,6 +44,9 @@ var cs = criteria as string; if (cs != null) { + if (value is string && (value as string).Trim() == "") + return cs == ""; + if (cs == "") return cs.Equals(value); @@ -54,7 +57,7 @@ var expression = string.Format("{0}{1}", value, cs); // add quotes if necessary - var pattern = @"(\w+)(\W+)(\w+)"; + var pattern = @"([\w\s]+)(\W+)(\w+)"; var m = Regex.Match(expression, pattern); if (m.Groups.Count == 4) { diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index b9a29ca..ff1288c 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -113,6 +113,11 @@ return ((DateTime)v).ToOADate(); } + if (v is TimeSpan) + { + return ((TimeSpan)v).TotalDays; + } + // handle nulls if (v == null || v is string) { diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 43fce93..dfd9d25 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -17,17 +17,23 @@ ce.RegisterFunction("ABS", 1, Abs); ce.RegisterFunction("ACOS", 1, Acos); ce.RegisterFunction("ACOSH", 1, Acosh); + ce.RegisterFunction("ACOT", 1, Acot); + ce.RegisterFunction("ACOTH", 1, Acoth); + ce.RegisterFunction("ARABIC", 1, Arabic); ce.RegisterFunction("ASIN", 1, Asin); ce.RegisterFunction("ASINH", 1, Asinh); ce.RegisterFunction("ATAN", 1, Atan); ce.RegisterFunction("ATAN2", 2, Atan2); ce.RegisterFunction("ATANH", 1, Atanh); + ce.RegisterFunction("BASE", 2, 3, Base); 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("COTH", 1, Coth); + ce.RegisterFunction("CSC", 1, Csc); ce.RegisterFunction("CSCH", 1, Csch); ce.RegisterFunction("DECIMAL", 2, MathTrig.Decimal); ce.RegisterFunction("DEGREES", 1, Degrees); @@ -61,6 +67,8 @@ ce.RegisterFunction("ROUND", 2, Round); ce.RegisterFunction("ROUNDDOWN", 2, RoundDown); ce.RegisterFunction("ROUNDUP", 1, 2, RoundUp); + ce.RegisterFunction("SEC", 1, Sec); + ce.RegisterFunction("SECH", 1, Sech); ce.RegisterFunction("SERIESSUM", 4, SeriesSum); ce.RegisterFunction("SIGN", 1, Sign); ce.RegisterFunction("SIN", 1, Sin); @@ -88,12 +96,20 @@ private static object Acos(List p) { + double input = p[0]; + if (Math.Abs(input) > 1) + throw new NumberException(); + return Math.Acos(p[0]); } private static object Asin(List p) { - return Math.Asin(p[0]); + double input = p[0]; + if (Math.Abs(input) > 1) + throw new NumberException(); + + return Math.Asin(input); } private static object Atan(List p) @@ -103,7 +119,12 @@ private static object Atan2(List p) { - return Math.Atan2(p[0], p[1]); + double x = p[0]; + double y = p[1]; + if (x == 0 && y == 0) + throw new DivisionByZeroException(); + + return Math.Atan2(y, x); } private static object Ceiling(List p) @@ -131,6 +152,24 @@ return 1 / tan; } + private static object Coth(List p) + { + double input = p[0]; + if (input == 0) + throw new DivisionByZeroException(); + + return 1 / Math.Tanh(input); + } + + private static object Csc(List p) + { + double input = p[0]; + if (input == 0) + throw new DivisionByZeroException(); + + return 1 / Math.Sin(input); + } + private static object Csch(List p) { if (Math.Abs((double)p[0].Evaluate()) < Double.Epsilon) @@ -290,7 +329,9 @@ { // get parameters var range = p[0] as IEnumerable; // range of values to match the criteria against - var sumRange = p.Count < 3 ? range : p[2] as IEnumerable; // range of values to sum up + var sumRange = p.Count < 3 ? + p[0] as XObjectExpression : + p[2] as XObjectExpression; // range of values to sum up var criteria = p[1].Evaluate(); // the criteria to evaluate // build list of values in range and sumRange @@ -299,10 +340,10 @@ { rangeValues.Add(value); } - var sumRangeValues = new List(); - foreach (var value in sumRange) + var sumRangeValues = new List(); + foreach (var cell in ((CellRangeReference)sumRange.Value).Range.Cells()) { - sumRangeValues.Add(value); + sumRangeValues.Add(cell); } // compute total @@ -312,7 +353,7 @@ { if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) { - tally.AddValue(sumRangeValues[i]); + tally.AddValue(sumRangeValues[i].Value); } } @@ -341,7 +382,7 @@ for(int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) { var criterion = p[criteriaPair * 2 + 1].Evaluate(); - var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; + var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; var criteriaRangeValues = new List(); foreach (var value in criteriaRange) { @@ -398,7 +439,19 @@ var values = p .Cast() - .Select(range => range.Cast().ToList()); + .Select(range => + { + var results = new List(); + foreach (var c in range) + { + if (c.IsNumber()) + results.Add(c.CastTo()); + else + results.Add(0.0); + } + return results; + }) + .ToArray(); return Enumerable.Range(0, counts.Single()) .Aggregate(0d, (t, i) => @@ -460,9 +513,59 @@ private static object Acosh(List p) { + double number = p[0]; + if (number < 1) + throw new NumberException(); + return XLMath.ACosh(p[0]); } + private static object Acot(List p) + { + double x = Math.Atan(1.0 / p[0]); + + // Acot in Excel calculates the modulus of the function above. + // as the % operator is not the modulus, but the remainder, we have to calculate the modulus by hand: + while (x < 0) + x = x + Math.PI; + + return x; + } + + private static object Acoth(List p) + { + double number = p[0]; + if (Math.Abs(number) < 1) + throw new NumberException(); + + return 0.5 * Math.Log((number + 1) / (number - 1)); + } + + private static object Arabic(List p) + { + string input = ((string)p[0]).Trim(); + + try + { + if (input == "") + return 0; + if (input == "-") + throw new NumberException(); + else if (input[0] == '-') + return -XLMath.RomanToArabic(input.Substring(1)); + else + return XLMath.RomanToArabic(input); + } + catch (ArgumentOutOfRangeException) + { + throw new CellValueException(); + } + catch + { + throw; + } + } + private static object Asinh(List p) { return XLMath.ASinh(p[0]); @@ -470,13 +573,70 @@ private static object Atanh(List p) { + double input = p[0]; + if (Math.Abs(input) >= 1) + throw new NumberException(); + return XLMath.ATanh(p[0]); } + private static object Base(List p) + { + long number; + int radix; + int minLength = 0; + + var rawNumber = p[0].Evaluate(); + if (rawNumber is long || rawNumber is int || rawNumber is byte || rawNumber is double || rawNumber is float) + number = Convert.ToInt64(rawNumber); + else + throw new CellValueException(); + + var rawRadix = p[1].Evaluate(); + if (rawRadix is long || rawRadix is int || rawRadix is byte || rawRadix is double || rawRadix is float) + radix = Convert.ToInt32(rawRadix); + else + throw new CellValueException(); + + if (p.Count > 2) + { + var rawMinLength = p[2].Evaluate(); + if (rawMinLength is long || rawMinLength is int || rawMinLength is byte || rawMinLength is double || rawMinLength is float) + minLength = Convert.ToInt32(rawMinLength); + else + throw new CellValueException(); + } + + if (number < 0 || radix < 2 || radix > 36) + throw new NumberException(); + + return XLMath.ChangeBase(number, radix).PadLeft(minLength, '0'); + } + private static object Combin(List p) { - Int32 n = (int)p[0]; - Int32 k = (int)p[1]; + Int32 n; + Int32 k; + + var rawN = p[0].Evaluate(); + var rawK = p[1].Evaluate(); + if (rawN is long || rawN is int || rawN is byte || rawN is double || rawN is float) + n = (int)Math.Floor((double)rawN); + else + throw new NumberException(); + + if (rawK is long || rawK is int || rawK is byte || rawK is double || rawK is float) + k = (int)Math.Floor((double)rawK); + else + throw new NumberException(); + + + n = (int)p[0]; + k = (int)p[1]; + + if (n < 0 || n < k || k < 0) + throw new NumberException(); + return XLMath.Combin(n, k); } @@ -505,8 +665,19 @@ private static object Fact(List p) { - var num = Math.Floor(p[0]); + var input = p[0].Evaluate(); + + if (!(input is long || input is int || input is byte || input is double || input is float)) + throw new CellValueException(); + + + var num = Math.Floor((double)input); double fact = 1.0; + + + if (num < 0) + throw new NumberException(); + if (num > 1) for (int i = 2; i <= num; i++) fact *= i; @@ -515,8 +686,17 @@ private static object FactDouble(List p) { + var input = p[0].Evaluate(); + + if (!(input is long || input is int || input is byte || input is double || input is float)) + throw new CellValueException(); + var num = Math.Floor(p[0]); double fact = 1.0; + + if (num < -1) + throw new NumberException(); + if (num > 1) { var start = Math.Abs(num % 2) < XLHelper.Epsilon ? 2 : 1; @@ -699,6 +879,20 @@ return Math.Floor(value * Math.Pow(10, digits)) / Math.Pow(10, digits); } + private static object Sec(List p) + { + double number; + if (double.TryParse(p[0], out number)) + return 1.0 / Math.Cos(number); + else + throw new CellValueException(); + } + + private static object Sech(List p) + { + return 1.0 / Math.Cosh(p[0]); + } + private static object SeriesSum(List p) { var x = (Double)p[0]; diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index bbf58ca..552c79d 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel.CalcEngine.Exceptions; +using ExcelNumberFormat; using System; using System.Collections.Generic; using System.Globalization; @@ -267,14 +268,12 @@ var format = (string)p[1]; if (string.IsNullOrEmpty(format.Trim())) return ""; - // We'll have to guess as to whether the format represents a date and/or time. - // Not sure whether there's a better way to detect this. - bool isDateFormat = new string[] { "y", "m", "d", "h", "s" }.Any(part => format.ToLower().Contains(part.ToLower())); + var nf = new NumberFormat(format); - if (isDateFormat) - return DateTime.FromOADate(number).ToString(format, CultureInfo.CurrentCulture); + if (nf.IsDateTimeFormat) + return nf.Format(DateTime.FromOADate(number), CultureInfo.InvariantCulture); else - return number.ToString(format, CultureInfo.CurrentCulture); + return nf.Format(number, CultureInfo.InvariantCulture); } private static object Trim(List p) diff --git a/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs b/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs index 693f68f..4354e04 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/XLMath.cs @@ -118,5 +118,69 @@ throw new ArgumentOutOfRangeException("something bad happened"); } + public static int RomanToArabic(string text) + { + if (text == "") + return 0; + if (text.StartsWith("M", StringComparison.InvariantCultureIgnoreCase)) + return 1000 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("CM", StringComparison.InvariantCultureIgnoreCase)) + return 900 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("D", StringComparison.InvariantCultureIgnoreCase)) + return 500 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("CD", StringComparison.InvariantCultureIgnoreCase)) + return 400 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("C", StringComparison.InvariantCultureIgnoreCase)) + return 100 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("XC", StringComparison.InvariantCultureIgnoreCase)) + return 90 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("L", StringComparison.InvariantCultureIgnoreCase)) + return 50 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("XL", StringComparison.InvariantCultureIgnoreCase)) + return 40 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("X", StringComparison.InvariantCultureIgnoreCase)) + return 10 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("IX", StringComparison.InvariantCultureIgnoreCase)) + return 9 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("V", StringComparison.InvariantCultureIgnoreCase)) + return 5 + RomanToArabic(text.Substring(1)); + if (text.StartsWith("IV", StringComparison.InvariantCultureIgnoreCase)) + return 4 + RomanToArabic(text.Substring(2)); + if (text.StartsWith("I", StringComparison.InvariantCultureIgnoreCase)) + return 1 + RomanToArabic(text.Substring(1)); + + throw new ArgumentOutOfRangeException("text is not a valid roman number"); + } + + public static string ChangeBase(long number, int radix) + { + if (number < 0) + throw new ArgumentOutOfRangeException("number must be greater or equal to 0"); + if (radix < 2) + throw new ArgumentOutOfRangeException("radix must be greater or equal to 2"); + if (radix > 36) + throw new ArgumentOutOfRangeException("radix must be smaller than or equal to 36"); + + StringBuilder sb = new StringBuilder(); + long remaining = number; + + if (remaining == 0) + { + sb.Insert(0, '0'); + } + + while (remaining > 0) + { + var nextDigitDecimal = remaining % radix; + remaining = remaining / radix; + + if (nextDigitDecimal < 10) + sb.Insert(0, nextDigitDecimal); + else + sb.Insert(0, (char)(nextDigitDecimal + 55)); + } + + return sb.ToString(); + } } } diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 17fdf43..28309e3 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - public enum XLCellValues { Text, Number, Boolean, DateTime, TimeSpan } + public enum XLDataType { Text, Number, Boolean, DateTime, TimeSpan } public enum XLTableCellType { None, Header, Data, Total } @@ -51,7 +51,7 @@ /// The type of the cell's data. /// /// - XLCellValues DataType { get; set; } + XLDataType DataType { get; set; } /// /// Sets the type of this cell's data. @@ -60,7 +60,7 @@ /// /// Type of the data. /// - IXLCell SetDataType(XLCellValues dataType); + IXLCell SetDataType(XLDataType dataType); /// /// Sets the cell's value. diff --git a/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/Excel/Cells/IXLCells.cs index d17a1c0..e42a5ae 100644 --- a/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/Excel/Cells/IXLCells.cs @@ -26,9 +26,9 @@ /// The type of the cell's data. /// /// - XLCellValues DataType { set; } + XLDataType DataType { set; } - IXLCells SetDataType(XLCellValues dataType); + IXLCells SetDataType(XLDataType dataType); /// /// Clears the contents of these cells. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 80a7eb2..d9d8fbe 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -70,7 +70,7 @@ internal string _cellValue = String.Empty; private XLComment _comment; - internal XLCellValues _dataType; + internal XLDataType _dataType; private XLHyperlink _hyperlink; private XLRichText _richText; private Int32? _styleCacheId; @@ -219,20 +219,20 @@ if (value is String || value is char) { _cellValue = value.ToString(); - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; if (_cellValue.Contains(Environment.NewLine) && !GetStyleForRead().Alignment.WrapText) Style.Alignment.WrapText = true; } else if (value is TimeSpan) { _cellValue = value.ToString(); - _dataType = XLCellValues.TimeSpan; + _dataType = XLDataType.TimeSpan; if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } else if (value is DateTime) { - _dataType = XLCellValues.DateTime; + _dataType = XLDataType.DateTime; var dtTest = (DateTime)Convert.ChangeType(value, typeof(DateTime)); if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; @@ -245,23 +245,23 @@ || Double.IsInfinity((Double)Convert.ChangeType(value, typeof(Double))))) { _cellValue = value.ToString(); - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; } else { - _dataType = XLCellValues.Number; + _dataType = XLDataType.Number; _cellValue = ((Double)Convert.ChangeType(value, typeof(Double))).ToInvariantString(); } } else if (value is Boolean) { - _dataType = XLCellValues.Boolean; + _dataType = XLDataType.Boolean; _cellValue = (Boolean)Convert.ChangeType(value, typeof(Boolean)) ? "1" : "0"; } else { _cellValue = Convert.ToString(value); - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; } return this; @@ -320,35 +320,35 @@ cValue = _cellValue; } - if (_dataType == XLCellValues.Boolean) - return (cValue != "0").ToString(); - if (_dataType == XLCellValues.TimeSpan) - return cValue; - if (_dataType == XLCellValues.DateTime || IsDateFormat()) + var format = GetFormat(); + + if (_dataType == XLDataType.Boolean) + return (cValue != "0").ToExcelFormat(format); + + else if (_dataType == XLDataType.TimeSpan || _dataType == XLDataType.DateTime || IsDateFormat()) { double dTest; if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest) && dTest.IsValidOADateNumber()) { - var format = GetFormat(); - return DateTime.FromOADate(dTest).ToString(format); + return DateTime.FromOADate(dTest).ToExcelFormat(format); } return cValue; } - if (_dataType == XLCellValues.Number) + else if (_dataType == XLDataType.Number) { double dTest; if (Double.TryParse(cValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) { - var format = GetFormat(); - return dTest.ToString(format); + return dTest.ToExcelFormat(format); } return cValue; } + else return cValue; } @@ -382,7 +382,13 @@ w => String.Compare(w.Name, sName, true) == 0) && XLHelper.IsValidA1Address(cAddress) ) - return _worksheet.Workbook.Worksheet(sName).Cell(cAddress).Value; + { + var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); + if (referenceCell.IsEmpty(false)) + return 0; + else + return referenceCell.Value; + } var retVal = Worksheet.Evaluate(fA1); var retValEnumerable = retVal as IEnumerable; @@ -395,10 +401,10 @@ var cellValue = HasRichText ? _richText.ToString() : _cellValue; - if (_dataType == XLCellValues.Boolean) + if (_dataType == XLDataType.Boolean) return cellValue != "0"; - if (_dataType == XLCellValues.DateTime) + if (_dataType == XLDataType.DateTime) { Double d; if (Double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out d) @@ -406,14 +412,14 @@ return DateTime.FromOADate(d); } - if (_dataType == XLCellValues.Number) + if (_dataType == XLDataType.Number) { Double d; if (double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out d)) return d; } - if (_dataType == XLCellValues.TimeSpan) + if (_dataType == XLDataType.TimeSpan) { TimeSpan t; if (TimeSpan.TryParse(cellValue, out t)) @@ -884,13 +890,13 @@ set { SetStyle(value); } } - public IXLCell SetDataType(XLCellValues dataType) + public IXLCell SetDataType(XLDataType dataType) { DataType = dataType; return this; } - public XLCellValues DataType + public XLDataType DataType { get { return _dataType; } set @@ -905,7 +911,7 @@ if (_cellValue.Length > 0) { - if (value == XLCellValues.Boolean) + if (value == XLDataType.Boolean) { bool bTest; if (Boolean.TryParse(_cellValue, out bTest)) @@ -913,7 +919,7 @@ else _cellValue = _cellValue == "0" || String.IsNullOrEmpty(_cellValue) ? "0" : "1"; } - else if (value == XLCellValues.DateTime) + else if (value == XLDataType.DateTime) { DateTime dtTest; double dblTest; @@ -932,7 +938,7 @@ if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; } - else if (value == XLCellValues.TimeSpan) + else if (value == XLDataType.TimeSpan) { TimeSpan tsTest; if (TimeSpan.TryParse(_cellValue, out tsTest)) @@ -957,11 +963,19 @@ } } } - else if (value == XLCellValues.Number) + else if (value == XLDataType.Number) { + var v = _cellValue; double dTest; - if (Double.TryParse(_cellValue, XLHelper.NumberStyle, CultureInfo.InvariantCulture, out dTest)) - _cellValue = dTest.ToInvariantString(); + double factor = 1.0; + if (v.EndsWith("%")) + { + v = v.Substring(0, v.Length - 1); + factor = 1 / 100.0; + } + + if (Double.TryParse(v, XLHelper.NumberStyle, CultureInfo.InvariantCulture, out dTest)) + _cellValue = (dTest * factor).ToInvariantString(); else { throw new ArgumentException( @@ -972,9 +986,9 @@ } else { - if (_dataType == XLCellValues.Boolean) + if (_dataType == XLDataType.Boolean) _cellValue = (_cellValue != "0").ToString(); - else if (_dataType == XLCellValues.TimeSpan) + else if (_dataType == XLDataType.TimeSpan) _cellValue = BaseDate.Add(GetTimeSpan()).ToOADate().ToInvariantString(); } } @@ -1074,6 +1088,8 @@ set { _formulaR1C1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; + + _formulaA1 = null; } } @@ -1554,7 +1570,7 @@ field.TotalsRowFunction = XLTotalsRowFunction.None; field.TotalsRowLabel = value.ToString(); this._cellValue = value.ToString(); - this.DataType = XLCellValues.Text; + this.DataType = XLDataType.Text; return true; } } @@ -1686,7 +1702,7 @@ private bool IsDateFormat() { var style = GetStyleForRead(); - return _dataType == XLCellValues.Number + return _dataType == XLDataType.Number && XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format) && ((style.NumberFormat.NumberFormatId >= 14 && style.NumberFormat.NumberFormatId <= 22) @@ -1717,7 +1733,7 @@ return false; _richText = asRichString; - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; return true; } @@ -1748,7 +1764,7 @@ Worksheet.Cell( Address.RowNumber + sourceCell.Address.RowNumber - minRow, Address.ColumnNumber + sourceCell.Address.ColumnNumber - minColumn - ).CopyFrom(sourceCell, true); + ).CopyFromInternal(sourceCell as XLCell, true); } var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges @@ -1765,7 +1781,9 @@ Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, initialCo + mergedRange.ColumnCount() - 1)).Cast(). ToList(); - rangesToMerge.ForEach(r => r.Merge()); + rangesToMerge.ForEach(r => r.Merge(false)); + + CopyConditionalFormatsFrom(asRange); return true; } @@ -1773,6 +1791,72 @@ return false; } + private void CopyConditionalFormatsFrom(XLRangeBase fromRange) + { + var srcSheet = fromRange.Worksheet; + int minRo = fromRange.RangeAddress.FirstAddress.RowNumber; + int minCo = fromRange.RangeAddress.FirstAddress.ColumnNumber; + if (srcSheet.ConditionalFormats.Any(r => r.Range.Intersects(fromRange))) + { + var fs = srcSheet.ConditionalFormats.Where(r => r.Range.Intersects(fromRange)).ToArray(); + if (fs.Any()) + { + minRo = fs.Max(r => r.Range.RangeAddress.LastAddress.RowNumber); + minCo = fs.Max(r => r.Range.RangeAddress.LastAddress.ColumnNumber); + } + } + int rCnt = minRo - fromRange.RangeAddress.FirstAddress.RowNumber + 1; + int cCnt = minCo - fromRange.RangeAddress.FirstAddress.ColumnNumber + 1; + rCnt = Math.Min(rCnt, fromRange.RowCount()); + cCnt = Math.Min(cCnt, fromRange.ColumnCount()); + var toRange = Worksheet.Range(this, Worksheet.Cell(Address.RowNumber + rCnt - 1, Address.ColumnNumber + cCnt - 1)); + var formats = srcSheet.ConditionalFormats.Where(f => f.Range.Intersects(fromRange)); + foreach (var cf in formats.ToList()) + { + var fmtRange = Relative(Intersection(cf.Range, fromRange), fromRange, toRange); + var c = new XLConditionalFormat((XLRange) fmtRange, true); + c.CopyFrom(cf); + foreach (var v in c.Values.ToList()) + { + var f = v.Value.Value; + if (v.Value.IsFormula) + { + var r1c1 = ((XLCell) cf.Range.FirstCell()).GetFormulaR1C1(f); + f = ((XLCell)fmtRange.FirstCell()).GetFormulaA1(r1c1); + } + + c.Values[v.Key] = new XLFormula {_value = f, IsFormula = v.Value.IsFormula}; + } + + _worksheet.ConditionalFormats.Add(c); + } + } + + private static IXLRangeBase Intersection(IXLRangeBase range, IXLRangeBase crop) + { + var sheet = range.Worksheet; + using (var xlRange = sheet.Range( + Math.Max(range.RangeAddress.FirstAddress.RowNumber, crop.RangeAddress.FirstAddress.RowNumber), + Math.Max(range.RangeAddress.FirstAddress.ColumnNumber, crop.RangeAddress.FirstAddress.ColumnNumber), + Math.Min(range.RangeAddress.LastAddress.RowNumber, crop.RangeAddress.LastAddress.RowNumber), + Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber))) + { + return sheet.Range(xlRange.RangeAddress); + } + } + + private static IXLRange Relative(IXLRangeBase range, IXLRangeBase baseRange, IXLRangeBase targetBase) + { + using (var xlRange = targetBase.Worksheet.Range( + range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1, + range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1)) + { + return ((XLRangeBase)targetBase).Range(xlRange.RangeAddress); + } + } + private bool SetDataTable(object o) { var dataTable = o as DataTable; @@ -1807,12 +1891,12 @@ else if (value is DateTime) val = ((DateTime)value).ToString("o"); else if (value.IsNumber()) - val = Convert.ToDecimal(value).ToInvariantString(); + val = value.ToInvariantString(); else val = value.ToString(); _richText = null; if (val.Length == 0) - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; else { double dTest; @@ -1822,14 +1906,14 @@ var style = GetStyleForRead(); if (style.NumberFormat.Format == "@") { - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } else if (val[0] == '\'') { val = val.Substring(1, val.Length - 1); - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } @@ -1838,15 +1922,15 @@ if (!(value is TimeSpan) && TimeSpan.TryParse(val, out tsTest)) val = tsTest.ToString(); - _dataType = XLCellValues.TimeSpan; + _dataType = XLDataType.TimeSpan; if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } else if (val.Trim() != "NaN" && Double.TryParse(val, XLHelper.NumberStyle, XLHelper.ParseCulture, out dTest)) - _dataType = XLCellValues.Number; + _dataType = XLDataType.Number; else if (DateTime.TryParse(val, out dtTest) && dtTest >= BaseDate) { - _dataType = XLCellValues.DateTime; + _dataType = XLDataType.DateTime; if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; @@ -1864,12 +1948,12 @@ } else if (Boolean.TryParse(val, out bTest)) { - _dataType = XLCellValues.Boolean; + _dataType = XLDataType.Boolean; val = bTest ? "1" : "0"; } else { - _dataType = XLCellValues.Text; + _dataType = XLDataType.Text; if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } @@ -1882,7 +1966,7 @@ _cellValue = val; } - private string GetFormulaR1C1(string value) + internal string GetFormulaR1C1(string value) { return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0); } @@ -2106,14 +2190,35 @@ return defaultWorksheet.Workbook.Worksheet(wsName).Cell(pair[1]); } + internal IXLCell CopyFromInternal(XLCell otherCell, Boolean copyDataValidations) + { + CopyValuesFrom(otherCell); + + if (otherCell._styleCacheId.HasValue) + SetStyle(otherCell._style ?? otherCell.Worksheet.Workbook.GetStyleById(otherCell._styleCacheId.Value)); + + if (copyDataValidations) + { + var eventTracking = Worksheet.EventTrackingEnabled; + Worksheet.EventTrackingEnabled = false; + if (otherCell.HasDataValidation) + CopyDataValidation(otherCell, otherCell.DataValidation); + else if (HasDataValidation) + { + using (var asRange = AsRange()) + Worksheet.DataValidations.Delete(asRange); + } + Worksheet.EventTrackingEnabled = eventTracking; + } + + return this; + } + public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations) { var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc - CopyValuesFrom(source); - - if (source._styleCacheId.HasValue) - SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId.Value)); + CopyFromInternal(source, copyDataValidations); var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) @@ -2136,20 +2241,6 @@ _worksheet.ConditionalFormats.Add(c); } - if (copyDataValidations) - { - var eventTracking = Worksheet.EventTrackingEnabled; - Worksheet.EventTrackingEnabled = false; - if (source.HasDataValidation) - CopyDataValidation(source, source.DataValidation); - else if (HasDataValidation) - { - using (var asRange = AsRange()) - Worksheet.DataValidations.Delete(asRange); - } - Worksheet.EventTrackingEnabled = eventTracking; - } - return this; } @@ -2235,7 +2326,7 @@ row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) + ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), row1, row2) : String.Format("{0}:{1}", row1, row2)); } else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= @@ -2246,7 +2337,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), new XLAddress(worksheetInAction, XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. @@ -2290,7 +2381,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), new XLAddress(worksheetInAction, XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), matchRange.RangeAddress. @@ -2319,7 +2410,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), matchRange.RangeAddress.FirstAddress, new XLAddress(worksheetInAction, XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), @@ -2460,7 +2551,7 @@ } sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) + ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), column1, column2) : String.Format("{0}:{1}", column1, column2)); } else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= @@ -2471,7 +2562,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, @@ -2515,7 +2606,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, @@ -2544,7 +2635,7 @@ if (useSheetName) { sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), + sheetName.EscapeSheetName(), matchRange.RangeAddress.FirstAddress, new XLAddress(worksheetInAction, matchRange.RangeAddress. diff --git a/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/Excel/Cells/XLCells.cs index c8fef10..bde0c7f 100644 --- a/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/Excel/Cells/XLCells.cs @@ -184,13 +184,13 @@ set { this.ForEach(c => c.Value = value); } } - public IXLCells SetDataType(XLCellValues dataType) + public IXLCells SetDataType(XLDataType dataType) { this.ForEach(c => c.DataType = dataType); return this; } - public XLCellValues DataType + public XLDataType DataType { set { this.ForEach(c => c.DataType = value); } } diff --git a/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/Excel/Columns/IXLColumn.cs index d0c64fd..1021190 100644 --- a/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/Excel/Columns/IXLColumn.cs @@ -166,7 +166,7 @@ /// IXLColumn AddVerticalPageBreak(); - IXLColumn SetDataType(XLCellValues dataType); + IXLColumn SetDataType(XLDataType dataType); IXLColumn ColumnLeft(); IXLColumn ColumnLeft(Int32 step); diff --git a/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/Excel/Columns/IXLColumns.cs index a362fca..9be1cf5 100644 --- a/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/Excel/Columns/IXLColumns.cs @@ -112,7 +112,7 @@ /// IXLColumns AddVerticalPageBreaks(); - IXLColumns SetDataType(XLCellValues dataType); + IXLColumns SetDataType(XLDataType dataType); /// /// Clears the contents of these columns. diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index 37e70b0..3f51208 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -644,7 +644,7 @@ return this; } - public IXLColumn SetDataType(XLCellValues dataType) + public IXLColumn SetDataType(XLDataType dataType) { DataType = dataType; return this; diff --git a/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/Excel/Columns/XLColumns.cs index 570f2fc..9f5b571 100644 --- a/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/Excel/Columns/XLColumns.cs @@ -207,7 +207,7 @@ return this; } - public IXLColumns SetDataType(XLCellValues dataType) + public IXLColumns SetDataType(XLDataType dataType) { _columns.ForEach(c => c.DataType = dataType); return this; diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index b47d0e7..01aedba 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -18,6 +15,7 @@ ThisMonth, NextMonth } + public enum XLIconSetStyle { ThreeArrows, @@ -38,6 +36,7 @@ FiveRating, FiveQuarters } + public enum XLConditionalFormatType { Expression, @@ -59,47 +58,79 @@ TimePeriod, AboveAverage } + public enum XLCFOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan, Between, NotBetween, Contains, NotContains, StartsWith, EndsWith } + public interface IXLConditionalFormat { IXLStyle Style { get; set; } IXLStyle WhenIsBlank(); + IXLStyle WhenNotBlank(); + IXLStyle WhenIsError(); + IXLStyle WhenNotError(); + IXLStyle WhenDateIs(XLTimePeriod timePeriod); + IXLStyle WhenContains(String value); + IXLStyle WhenNotContains(String value); + IXLStyle WhenStartsWith(String value); + IXLStyle WhenEndsWith(String value); + IXLStyle WhenEquals(String value); + IXLStyle WhenNotEquals(String value); + IXLStyle WhenGreaterThan(String value); + IXLStyle WhenLessThan(String value); + IXLStyle WhenEqualOrGreaterThan(String value); + IXLStyle WhenEqualOrLessThan(String value); + IXLStyle WhenBetween(String minValue, String maxValue); + IXLStyle WhenNotBetween(String minValue, String maxValue); IXLStyle WhenEquals(Double value); + IXLStyle WhenNotEquals(Double value); + IXLStyle WhenGreaterThan(Double value); + IXLStyle WhenLessThan(Double value); + IXLStyle WhenEqualOrGreaterThan(Double value); + IXLStyle WhenEqualOrLessThan(Double value); + IXLStyle WhenBetween(Double minValue, Double maxValue); + IXLStyle WhenNotBetween(Double minValue, Double maxValue); IXLStyle WhenIsDuplicate(); + IXLStyle WhenIsUnique(); + IXLStyle WhenIsTrue(String formula); + IXLStyle WhenIsTop(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items); + IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType); 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; } @@ -108,6 +139,7 @@ Boolean ReverseIconOrder { get; } Boolean ShowIconOnly { get; } Boolean ShowBarOnly { get; } + Boolean StopIfTrue { get; } IXLRange Range { get; set; } XLDictionary Values { get; } @@ -116,10 +148,10 @@ XLDictionary IconSetOperators { get; } XLCFOperator Operator { get; } - Boolean Bottom { get; } + Boolean Bottom { get; } Boolean Percent { get; } - IXLConditionalFormat StopIfTrue(bool value = true); - + IXLConditionalFormat SetStopIfTrue(); + IXLConditionalFormat SetStopIfTrue(Boolean value); } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs index d00f871..5461406 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs @@ -7,7 +7,12 @@ { public static ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority) { - return new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, StopIfTrue = OpenXmlHelper.GetBooleanValue(((XLConditionalFormat)cf).StopIfTrueInternal, false) }; + return new ConditionalFormattingRule + { + Type = cf.ConditionalFormatType.ToOpenXml(), + Priority = priority, + StopIfTrue = OpenXmlHelper.GetBooleanValue(cf.StopIfTrue, false) + }; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs index 2186baf..06f9a6e 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs @@ -28,11 +28,22 @@ Converters.Add(XLConditionalFormatType.Top10, new XLCFTopConverter()); Converters.Add(XLConditionalFormatType.DataBar, new XLCFDataBarConverter()); Converters.Add(XLConditionalFormatType.IconSet, new XLCFIconSetConverter()); + Converters.Add(XLConditionalFormatType.TimePeriod, new XLCFDatesOccurringConverter()); + + foreach (var cft in Enum.GetValues(typeof(XLConditionalFormatType)).Cast()) + { + if (!Converters.ContainsKey(cft)) + Converters.Add(cft, null); + } } public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, Int32 priority, XLWorkbook.SaveContext context) { - return Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, priority, context); + var converter = Converters[conditionalFormat.ConditionalFormatType]; + if (converter == null) + throw new NotImplementedException(string.Format("Conditional formatting rule '{0}' hasn't been implemented", conditionalFormat.ConditionalFormatType)); + + return converter.Convert(conditionalFormat, priority, context); } } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDatesOccuringConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDatesOccuringConverter.cs new file mode 100644 index 0000000..24e1129 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDatesOccuringConverter.cs @@ -0,0 +1,40 @@ +using DocumentFormat.OpenXml.Spreadsheet; +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCFDatesOccurringConverter : IXLCFConverter + { + private static readonly IDictionary formulaTemplates = new Dictionary() + { + [XLTimePeriod.Today] = "FLOOR({0},1)=TODAY()", + [XLTimePeriod.Yesterday] = "FLOOR({0},1)=TODAY()-1", + [XLTimePeriod.Tomorrow] = "FLOOR({0},1)=TODAY()+1", + [XLTimePeriod.InTheLast7Days] = "AND(TODAY()-FLOOR({0},1)<=6,FLOOR({0},1)<=TODAY())", + [XLTimePeriod.ThisMonth] = "AND(MONTH({0})=MONTH(TODAY()),YEAR({0})=YEAR(TODAY()))", + [XLTimePeriod.LastMonth] = "AND(MONTH({0})=MONTH(EDATE(TODAY(),0-1)),YEAR({0})=YEAR(EDATE(TODAY(),0-1)))", + [XLTimePeriod.NextMonth] = "AND(MONTH({0})=MONTH(EDATE(TODAY(),0+1)),YEAR({0})=YEAR(EDATE(TODAY(),0+1)))", + [XLTimePeriod.ThisWeek] = "AND(TODAY()-ROUNDDOWN({0},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN({0},0)-TODAY()<=7-WEEKDAY(TODAY()))", + [XLTimePeriod.LastWeek] = "AND(TODAY()-ROUNDDOWN({0},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN({0},0)-TODAY()<=7-WEEKDAY(TODAY()))", + [XLTimePeriod.NextWeek] = "AND(ROUNDDOWN({0},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN({0},0)-TODAY()<(15-WEEKDAY(TODAY())))" + }; + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + conditionalFormattingRule.TimePeriod = cf.TimePeriod.ToOpenXml(); + + var address = cf.Range.RangeAddress.FirstAddress.ToStringRelative(false); + var formula = new Formula { Text = String.Format(formulaTemplates[cf.TimePeriod], address) }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + } +} diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 03aa8ca..1e4cdb5 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -1,11 +1,108 @@ using System; using System.Collections.Generic; +using System.Linq; using ClosedXML.Utils; namespace ClosedXML.Excel { internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized { + private sealed class FullEqualityComparer : IEqualityComparer + { + private readonly bool _compareRange; + private readonly DictionaryComparer _colorsComparer = new DictionaryComparer(); + private readonly EnumerableComparer _listComparer = new EnumerableComparer(); + private readonly DictionaryComparer _contentsTypeComparer = new DictionaryComparer(); + private readonly DictionaryComparer _iconSetTypeComparer = new DictionaryComparer(); + + public FullEqualityComparer(bool compareRange) + { + _compareRange = compareRange; + } + + public bool Equals(IXLConditionalFormat x, IXLConditionalFormat y) + { + var xx = (XLConditionalFormat) x; + var yy = (XLConditionalFormat) y; + if (ReferenceEquals(xx, yy)) return true; + if (ReferenceEquals(xx, null)) return false; + if (ReferenceEquals(yy, null)) return false; + if (xx.GetType() != yy.GetType()) return false; + + var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v=>v.Value); + var yyValues = yy.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value); + var xxFormulas = xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Range.FirstCell()).GetFormulaR1C1(f.Value)); + var yyFormulas = yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Range.FirstCell()).GetFormulaR1C1(f.Value)); + + var xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId); + var yStyle = yy._style ?? yy.Range.Worksheet.Workbook.GetStyleById(yy._styleCacheId); + + return Equals(xStyle, yStyle) + && xx.CopyDefaultModify == yy.CopyDefaultModify + && xx.UpdatingStyle == yy.UpdatingStyle + && xx.ConditionalFormatType == yy.ConditionalFormatType + && xx.TimePeriod == yy.TimePeriod + && xx.IconSetStyle == yy.IconSetStyle + && xx.Operator == yy.Operator + && xx.Bottom == yy.Bottom + && xx.Percent == yy.Percent + && xx.ReverseIconOrder == yy.ReverseIconOrder + && xx.StopIfTrue == yy.StopIfTrue + && xx.ShowIconOnly == yy.ShowIconOnly + && xx.ShowBarOnly == yy.ShowBarOnly + && _listComparer.Equals(xxValues, yyValues) + && _listComparer.Equals(xxFormulas, yyFormulas) + && _colorsComparer.Equals(xx.Colors, yy.Colors) + && _contentsTypeComparer.Equals(xx.ContentTypes, yy.ContentTypes) + && _iconSetTypeComparer.Equals(xx.IconSetOperators, yy.IconSetOperators) + && (!_compareRange || Equals(xx.Range.RangeAddress, yy.Range.RangeAddress)) ; + } + + public int GetHashCode(IXLConditionalFormat obj) + { + var xx = (XLConditionalFormat)obj; + var xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId); + var xValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value) + .Union(xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)obj.Range.FirstCell()).GetFormulaR1C1(f.Value))); + + unchecked + { + var hashCode = xStyle.GetHashCode(); + hashCode = (hashCode * 397) ^ xx._styleCacheId; + hashCode = (hashCode * 397) ^ xx.CopyDefaultModify.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.UpdatingStyle.GetHashCode(); + hashCode = (hashCode * 397) ^ xValues.GetHashCode(); + hashCode = (hashCode * 397) ^ (xx.Colors != null ? xx.Colors.GetHashCode() : 0); + hashCode = (hashCode * 397) ^ (xx.ContentTypes != null ? xx.ContentTypes.GetHashCode() : 0); + hashCode = (hashCode * 397) ^ (xx.IconSetOperators != null ? xx.IconSetOperators.GetHashCode() : 0); + hashCode = (hashCode * 397) ^ (_compareRange && xx.Range != null ? xx.Range.GetHashCode() : 0); + hashCode = (hashCode * 397) ^ (int)xx.ConditionalFormatType; + hashCode = (hashCode * 397) ^ (int)xx.TimePeriod; + hashCode = (hashCode * 397) ^ (int)xx.IconSetStyle; + hashCode = (hashCode * 397) ^ (int)xx.Operator; + hashCode = (hashCode * 397) ^ xx.Bottom.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.Percent.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ReverseIconOrder.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ShowIconOnly.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ShowBarOnly.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.StopIfTrue.GetHashCode(); + return hashCode; + } + } + } + + private static readonly IEqualityComparer FullComparerInstance = new FullEqualityComparer(true); + public static IEqualityComparer FullComparer + { + get { return FullComparerInstance; } + } + + private static readonly IEqualityComparer NoRangeComparerInstance = new FullEqualityComparer(false); + public static IEqualityComparer NoRangeComparer + { + get { return NoRangeComparerInstance; } + } + public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false) { Id = Guid.NewGuid(); @@ -39,7 +136,7 @@ ReverseIconOrder = conditionalFormat.ReverseIconOrder; ShowIconOnly = conditionalFormat.ShowIconOnly; ShowBarOnly = conditionalFormat.ShowBarOnly; - StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrueInternal, true); + StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrue, true); } @@ -100,12 +197,16 @@ public Boolean ReverseIconOrder { get; set; } public Boolean ShowIconOnly { get; set; } public Boolean ShowBarOnly { get; set; } + public Boolean StopIfTrue { get; set; } - internal bool StopIfTrueInternal { get; set; } - - public IXLConditionalFormat StopIfTrue(bool value = true) + public IXLConditionalFormat SetStopIfTrue() { - StopIfTrueInternal = value; + return SetStopIfTrue(true); + } + + public IXLConditionalFormat SetStopIfTrue(bool value) + { + this.StopIfTrue = value; return this; } @@ -121,7 +222,7 @@ ReverseIconOrder = other.ReverseIconOrder; ShowIconOnly = other.ShowIconOnly; ShowBarOnly = other.ShowBarOnly; - StopIfTrueInternal = ((XLConditionalFormat)other).StopIfTrueInternal; + StopIfTrue = other.StopIfTrue; Values.Clear(); other.Values.ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value))); @@ -376,5 +477,59 @@ return new XLCFIconSet(this); } } + + internal class DictionaryComparer : + IEqualityComparer> + { + private readonly IEqualityComparer _valueComparer; + public DictionaryComparer(IEqualityComparer valueComparer = null) + { + this._valueComparer = valueComparer ?? EqualityComparer.Default; + } + public bool Equals(Dictionary x, Dictionary y) + { + if (x.Count != y.Count) + return false; + if (x.Keys.Except(y.Keys).Any()) + return false; + if (y.Keys.Except(x.Keys).Any()) + return false; + foreach (var pair in x) + if (!_valueComparer.Equals(pair.Value, y[pair.Key])) + return false; + return true; + } + + public int GetHashCode(Dictionary obj) + { + throw new NotImplementedException(); + } + } + + internal class EnumerableComparer : IEqualityComparer> + { + private readonly IEqualityComparer _valueComparer; + public EnumerableComparer(IEqualityComparer valueComparer = null) + { + this._valueComparer = valueComparer ?? EqualityComparer.Default; + } + + public bool Equals(IEnumerable x, IEnumerable y) + { + return SetEquals(x, y, _valueComparer); + } + + public int GetHashCode(IEnumerable obj) + { + throw new NotImplementedException(); + } + + public static bool SetEquals(IEnumerable first, IEnumerable second, + IEqualityComparer comparer) + { + return new HashSet(second, comparer ?? EqualityComparer.Default) + .SetEquals(first); + } + } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index c968484..940bd22 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -13,6 +13,22 @@ _conditionalFormats.Add(conditionalFormat); } + private bool IsRangeAbove(IXLRangeAddress newAddr, IXLRangeAddress addr) + { + return newAddr.FirstAddress.ColumnNumber == addr.FirstAddress.ColumnNumber + && newAddr.LastAddress.ColumnNumber == addr.LastAddress.ColumnNumber + && newAddr.FirstAddress.RowNumber < addr.FirstAddress.RowNumber + && (newAddr.LastAddress.RowNumber+1).Between(addr.FirstAddress.RowNumber, addr.LastAddress.RowNumber); + } + + private bool IsRangeToLeft(IXLRangeAddress newAddr, IXLRangeAddress addr) + { + return newAddr.FirstAddress.RowNumber == addr.FirstAddress.RowNumber + && newAddr.LastAddress.RowNumber == addr.LastAddress.RowNumber + && newAddr.FirstAddress.ColumnNumber < addr.FirstAddress.ColumnNumber + && (newAddr.LastAddress.ColumnNumber+1).Between(addr.FirstAddress.ColumnNumber, addr.LastAddress.ColumnNumber); + } + public IEnumerator GetEnumerator() { return _conditionalFormats.GetEnumerator(); @@ -29,6 +45,66 @@ _conditionalFormats.RemoveAll(predicate); } + /// + /// The method consolidate the same conditional formats, which are located in adjacent ranges. + /// + internal void Consolidate() + { + var formats = _conditionalFormats + .OrderByDescending(x => x.Range.RangeAddress.FirstAddress.RowNumber) + .ThenByDescending(x => x.Range.RangeAddress.FirstAddress.ColumnNumber); + + var orderedFormats = formats.ToList(); + + foreach (var item in formats) + { + var itemAddr = item.Range.RangeAddress; + var itemRowNum = itemAddr.FirstAddress.RowNumber; + + Func IsSameFormat = f => f != item && f.Range.Worksheet.Position == item.Range.Worksheet.Position && + XLConditionalFormat.NoRangeComparer.Equals(f, item); + + // search for an adjacent range + var format = orderedFormats + .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= itemRowNum) + .FirstOrDefault(f => (IsRangeAbove(itemAddr, f.Range.RangeAddress) || IsRangeToLeft(itemAddr, f.Range.RangeAddress)) && IsSameFormat(f)); + if (format != null) + { + Merge(format, item); + _conditionalFormats.Remove(item); + orderedFormats.Remove(item); + // compress with bottom range + var newaddr = format.Range.RangeAddress; + var newRowNum = newaddr.FirstAddress.RowNumber; + var bottom = orderedFormats + .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= newRowNum) + .FirstOrDefault(f => IsRangeAbove(newaddr, f.Range.RangeAddress) && IsSameFormat(f)); + if (bottom != null) + { + Merge(bottom, format); + _conditionalFormats.Remove(format); + orderedFormats.Remove(format); + } + continue; + } + + // search for an encompassable range + format = _conditionalFormats.FirstOrDefault(f => f.Range.Contains(item.Range) && IsSameFormat(f)); + if (format != null) + { + _conditionalFormats.Remove(item); + orderedFormats.Remove(item); + } + } + } + + private static void Merge(IXLConditionalFormat format, IXLConditionalFormat item) + { + foreach (var v in format.Values.ToList()) + format.Values[v.Key] = item.Values[v.Key]; + format.Range.RangeAddress.FirstAddress = item.Range.RangeAddress.FirstAddress; + } + public void RemoveAll() { _conditionalFormats.ForEach(cf => cf.Range.Dispose()); diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index 81f4977..56f3922 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -252,7 +252,7 @@ if (includeSheet) return String.Format("{0}!{1}", - Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + Worksheet.Name.EscapeSheetName(), address); return address; @@ -389,7 +389,7 @@ { if (includeSheet) return String.Format("{0}!{1}", - Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + Worksheet.Name.EscapeSheetName(), GetTrimmedAddress()); return GetTrimmedAddress(); @@ -414,7 +414,7 @@ if (includeSheet) return String.Format("{0}!{1}", - Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + Worksheet.Name.EscapeSheetName(), address); return address; diff --git a/ClosedXML/Excel/Drawings/XLPictures.cs b/ClosedXML/Excel/Drawings/XLPictures.cs index 8bd7651..79c2030 100644 --- a/ClosedXML/Excel/Drawings/XLPictures.cs +++ b/ClosedXML/Excel/Drawings/XLPictures.cs @@ -12,10 +12,12 @@ { private readonly List _pictures = new List(); private readonly XLWorksheet _worksheet; + internal ICollection Deleted { get; private set; } public XLPictures(XLWorksheet worksheet) { _worksheet = worksheet; + Deleted = new HashSet(); } public int Count @@ -96,7 +98,17 @@ public void Delete(string pictureName) { - _pictures.RemoveAll(picture => picture.Name.Equals(pictureName, StringComparison.OrdinalIgnoreCase)); + var picturesToDelete = _pictures + .Where(picture => picture.Name.Equals(pictureName, StringComparison.OrdinalIgnoreCase)) + .ToList(); + + foreach (var picture in picturesToDelete) + { + if (!string.IsNullOrEmpty(picture.RelId)) + Deleted.Add(picture.RelId); + + _pictures.Remove(picture); + } } IEnumerator IEnumerable.GetEnumerator() diff --git a/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/Excel/EnumConverter.cs index 5b8ea92..af5ebe8 100644 --- a/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/Excel/EnumConverter.cs @@ -30,12 +30,8 @@ case XLFontUnderlineValues.SingleAccounting: return UnderlineValues.SingleAccounting; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -52,12 +48,8 @@ case XLPageOrientation.Portrait: return OrientationValues.Portrait; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -74,12 +66,8 @@ case XLFontVerticalTextAlignmentValues.Superscript: return VerticalAlignmentRunValues.Superscript; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -144,12 +132,8 @@ case XLFillPatternValues.Solid: return PatternValues.Solid; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -199,12 +183,8 @@ case XLBorderStyleValues.Thin: return BorderStyleValues.Thin; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -236,12 +216,8 @@ case XLAlignmentHorizontalValues.Right: return HorizontalAlignmentValues.Right; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -264,12 +240,8 @@ case XLAlignmentVerticalValues.Top: return VerticalAlignmentValues.Top; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -283,12 +255,8 @@ case XLPageOrderValues.OverThenDown: return PageOrderValues.OverThenDown; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -305,12 +273,8 @@ case XLShowCommentsValues.None: return CellCommentsValues.None; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -330,12 +294,8 @@ case XLPrintErrorValues.NA: return PrintErrorValues.NA; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -352,12 +312,8 @@ case XLCalculateMode.Manual: return CalculateModeValues.Manual; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -371,12 +327,8 @@ case XLReferenceStyle.A1: return ReferenceModeValues.A1; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -393,12 +345,8 @@ case XLAlignmentReadingOrderValues.RightToLeft: return 2; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -436,12 +384,8 @@ case XLTotalsRowFunction.Custom: return TotalsRowFunctionValues.Custom; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -473,12 +417,8 @@ case XLAllowedValues.WholeNumber: return DataValidationValues.Whole; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -495,12 +435,8 @@ case XLErrorStyle.Stop: return DataValidationErrorStyleValues.Stop; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -532,12 +468,8 @@ case XLOperator.NotEqualTo: return DataValidationOperatorValues.NotEqual; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -554,12 +486,8 @@ case XLWorksheetVisibility.VeryHidden: return SheetStateValues.VeryHidden; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -579,12 +507,8 @@ case XLPhoneticAlignment.NoControl: return PhoneticAlignmentValues.NoControl; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -604,12 +528,8 @@ case XLPhoneticType.NoConversion: return PhoneticValues.NoConversion; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -629,12 +549,8 @@ case XLPivotSummary.Variance: return DataConsolidateFunctionValues.Variance; case XLPivotSummary.PopulationVariance: return DataConsolidateFunctionValues.VarianceP; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -652,12 +568,8 @@ case XLPivotCalculation.PercentageOfTotal: return ShowDataAsValues.PercentOfTotal; case XLPivotCalculation.Index: return ShowDataAsValues.Index; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -672,12 +584,8 @@ case XLFilterOperator.LessThan: return FilterOperatorValues.LessThan; case XLFilterOperator.EqualOrLessThan: return FilterOperatorValues.LessThanOrEqual; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -688,12 +596,8 @@ case XLFilterDynamicType.AboveAverage: return DynamicFilterValues.AboveAverage; case XLFilterDynamicType.BelowAverage: return DynamicFilterValues.BelowAverage; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -705,12 +609,8 @@ case XLSheetViewOptions.PageBreakPreview: return SheetViewValues.PageBreakPreview; case XLSheetViewOptions.PageLayout: return SheetViewValues.PageLayout; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -724,12 +624,8 @@ case XLLineStyle.ThinThick: return Vml.StrokeLineStyleValues.ThinThick; case XLLineStyle.ThinThin: return Vml.StrokeLineStyleValues.ThinThin; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -756,12 +652,8 @@ case XLConditionalFormatType.TimePeriod: return ConditionalFormatValues.TimePeriod; case XLConditionalFormatType.AboveAverage: return ConditionalFormatValues.AboveAverage; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -776,12 +668,8 @@ case XLCFContentType.Formula: return ConditionalFormatValueObjectValues.Formula; case XLCFContentType.Percentile: return ConditionalFormatValueObjectValues.Percentile; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -802,12 +690,8 @@ case XLCFOperator.StartsWith: return ConditionalFormattingOperatorValues.BeginsWith; case XLCFOperator.EndsWith: return ConditionalFormattingOperatorValues.EndsWith; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -833,12 +717,28 @@ case XLIconSetStyle.FiveRating: return IconSetValues.FiveRating; case XLIconSetStyle.FiveQuarters: return IconSetValues.FiveQuarters; - #region default - default: throw new ArgumentOutOfRangeException("Not implemented value!"); + } + } - #endregion default + public static TimePeriodValues ToOpenXml(this XLTimePeriod value) + { + switch (value) + { + case XLTimePeriod.Yesterday: return TimePeriodValues.Yesterday; + case XLTimePeriod.Today: return TimePeriodValues.Today; + case XLTimePeriod.Tomorrow: return TimePeriodValues.Tomorrow; + case XLTimePeriod.InTheLast7Days: return TimePeriodValues.Last7Days; + case XLTimePeriod.LastWeek: return TimePeriodValues.LastWeek; + case XLTimePeriod.ThisWeek: return TimePeriodValues.ThisWeek; + case XLTimePeriod.NextWeek: return TimePeriodValues.NextWeek; + case XLTimePeriod.LastMonth: return TimePeriodValues.LastMonth; + case XLTimePeriod.ThisMonth: return TimePeriodValues.ThisMonth; + case XLTimePeriod.NextMonth: return TimePeriodValues.NextMonth; + + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -853,10 +753,13 @@ { case XLPicturePlacement.FreeFloating: return Xdr.EditAsValues.Absolute; + case XLPicturePlacement.Move: return Xdr.EditAsValues.OneCell; + case XLPicturePlacement.MoveAndSize: return Xdr.EditAsValues.TwoCell; + default: throw new ArgumentOutOfRangeException("Not implemented value!"); } @@ -885,12 +788,8 @@ case UnderlineValues.SingleAccounting: return XLFontUnderlineValues.SingleAccounting; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -907,12 +806,8 @@ case OrientationValues.Portrait: return XLPageOrientation.Portrait; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -929,12 +824,8 @@ case VerticalAlignmentRunValues.Superscript: return XLFontVerticalTextAlignmentValues.Superscript; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -999,12 +890,8 @@ case PatternValues.Solid: return XLFillPatternValues.Solid; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1054,12 +941,8 @@ case BorderStyleValues.Thin: return XLBorderStyleValues.Thin; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1091,12 +974,8 @@ case HorizontalAlignmentValues.Right: return XLAlignmentHorizontalValues.Right; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1119,12 +998,8 @@ case VerticalAlignmentValues.Top: return XLAlignmentVerticalValues.Top; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1138,12 +1013,8 @@ case PageOrderValues.OverThenDown: return XLPageOrderValues.OverThenDown; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1160,12 +1031,8 @@ case CellCommentsValues.None: return XLShowCommentsValues.None; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1185,12 +1052,8 @@ case PrintErrorValues.NA: return XLPrintErrorValues.NA; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1207,12 +1070,8 @@ case CalculateModeValues.Manual: return XLCalculateMode.Manual; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1226,12 +1085,8 @@ case ReferenceModeValues.A1: return XLReferenceStyle.A1; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1248,12 +1103,8 @@ case 2: return XLAlignmentReadingOrderValues.RightToLeft; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1291,12 +1142,8 @@ case TotalsRowFunctionValues.Custom: return XLTotalsRowFunction.Custom; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1328,12 +1175,8 @@ case DataValidationValues.Whole: return XLAllowedValues.WholeNumber; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1350,12 +1193,8 @@ case DataValidationErrorStyleValues.Stop: return XLErrorStyle.Stop; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1387,12 +1226,8 @@ case DataValidationOperatorValues.NotEqual: return XLOperator.NotEqualTo; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1409,12 +1244,8 @@ case SheetStateValues.VeryHidden: return XLWorksheetVisibility.VeryHidden; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1434,12 +1265,8 @@ case PhoneticAlignmentValues.NoControl: return XLPhoneticAlignment.NoControl; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1457,12 +1284,8 @@ case PhoneticValues.NoConversion: return XLPhoneticType.NoConversion; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1482,12 +1305,8 @@ case DataConsolidateFunctionValues.Variance: return XLPivotSummary.Variance; case DataConsolidateFunctionValues.VarianceP: return XLPivotSummary.PopulationVariance; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1505,12 +1324,8 @@ case ShowDataAsValues.PercentOfTotal: return XLPivotCalculation.PercentageOfTotal; case ShowDataAsValues.Index: return XLPivotCalculation.Index; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1525,12 +1340,8 @@ case FilterOperatorValues.GreaterThanOrEqual: return XLFilterOperator.EqualOrGreaterThan; case FilterOperatorValues.LessThanOrEqual: return XLFilterOperator.EqualOrLessThan; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1541,12 +1352,8 @@ case DynamicFilterValues.AboveAverage: return XLFilterDynamicType.AboveAverage; case DynamicFilterValues.BelowAverage: return XLFilterDynamicType.BelowAverage; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1558,12 +1365,8 @@ case SheetViewValues.PageBreakPreview: return XLSheetViewOptions.PageBreakPreview; case SheetViewValues.PageLayout: return XLSheetViewOptions.PageLayout; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1577,12 +1380,8 @@ case Vml.StrokeLineStyleValues.ThinThick: return XLLineStyle.ThinThick; case Vml.StrokeLineStyleValues.ThinThin: return XLLineStyle.ThinThin; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1609,12 +1408,8 @@ case ConditionalFormatValues.TimePeriod: return XLConditionalFormatType.TimePeriod; case ConditionalFormatValues.AboveAverage: return XLConditionalFormatType.AboveAverage; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1629,12 +1424,8 @@ case ConditionalFormatValueObjectValues.Formula: return XLCFContentType.Formula; case ConditionalFormatValueObjectValues.Percentile: return XLCFContentType.Percentile; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1655,12 +1446,8 @@ case ConditionalFormattingOperatorValues.BeginsWith: return XLCFOperator.StartsWith; case ConditionalFormattingOperatorValues.EndsWith: return XLCFOperator.EndsWith; - #region default - default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1686,12 +1473,28 @@ case IconSetValues.FiveRating: return XLIconSetStyle.FiveRating; case IconSetValues.FiveQuarters: return XLIconSetStyle.FiveQuarters; - #region default + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); + } + } + + public static XLTimePeriod ToClosedXml(this TimePeriodValues value) + { + switch (value) + { + case TimePeriodValues.Yesterday: return XLTimePeriod.Yesterday; + case TimePeriodValues.Today: return XLTimePeriod.Today; + case TimePeriodValues.Tomorrow: return XLTimePeriod.Tomorrow; + case TimePeriodValues.Last7Days: return XLTimePeriod.InTheLast7Days; + case TimePeriodValues.LastWeek: return XLTimePeriod.LastWeek; + case TimePeriodValues.ThisWeek: return XLTimePeriod.ThisWeek; + case TimePeriodValues.NextWeek: return XLTimePeriod.NextWeek; + case TimePeriodValues.LastMonth: return XLTimePeriod.LastMonth; + case TimePeriodValues.ThisMonth: return XLTimePeriod.ThisMonth; + case TimePeriodValues.NextMonth: return XLTimePeriod.NextMonth; default: - throw new ApplicationException("Not implemented value!"); - - #endregion default + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } @@ -1706,12 +1509,15 @@ { case Xdr.EditAsValues.Absolute: return XLPicturePlacement.FreeFloating; + case Xdr.EditAsValues.OneCell: return XLPicturePlacement.Move; + case Xdr.EditAsValues.TwoCell: return XLPicturePlacement.MoveAndSize; + default: - throw new ArgumentOutOfRangeException(); + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); } } diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs index c347d16..f60e516 100644 --- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs +++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs @@ -78,11 +78,11 @@ ? String.Format("{0}!{1}", _internalAddress .Substring(0, _internalAddress.IndexOf('!')) - .WrapSheetNameInQuotesIfRequired(), + .EscapeSheetName(), _internalAddress.Substring(_internalAddress.IndexOf('!') + 1)) : _internalAddress; } - return String.Format("{0}!{1}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), _internalAddress); + return String.Format("{0}!{1}", Worksheet.Name.EscapeSheetName(), _internalAddress); } set { diff --git a/ClosedXML/Excel/IXLWorkbook.cs b/ClosedXML/Excel/IXLWorkbook.cs new file mode 100644 index 0000000..702c490 --- /dev/null +++ b/ClosedXML/Excel/IXLWorkbook.cs @@ -0,0 +1,230 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Globalization; +using System.IO; + +namespace ClosedXML.Excel +{ + public interface IXLWorkbook : IDisposable + { + String Author { get; set; } + + /// + /// Gets or sets the workbook's calculation mode. + /// + XLCalculateMode CalculateMode { get; set; } + + Boolean CalculationOnSave { get; set; } + + /// + /// Gets or sets the default column width for the workbook. + /// All new worksheets will use this column width. + /// + Double ColumnWidth { get; set; } + + IXLCustomProperties CustomProperties { get; } + + Boolean DefaultRightToLeft { get; } + + Boolean DefaultShowFormulas { get; } + + Boolean DefaultShowGridLines { get; } + + Boolean DefaultShowOutlineSymbols { get; } + + Boolean DefaultShowRowColHeaders { get; } + + Boolean DefaultShowRuler { get; } + + Boolean DefaultShowWhiteSpace { get; } + + Boolean DefaultShowZeros { get; } + + Boolean ForceFullCalculation { get; set; } + + Boolean FullCalculationOnLoad { get; set; } + + Boolean FullPrecision { get; set; } + + Boolean IsPasswordProtected { get; } + + Boolean LockStructure { get; set; } + + Boolean LockWindows { get; set; } + + /// + /// Gets an object to manipulate this workbook's named ranges. + /// + IXLNamedRanges NamedRanges { get; } + + /// + /// Gets or sets the default outline options for the workbook. + /// All new worksheets will use these outline options. + /// + IXLOutline Outline { get; set; } + + /// + /// Gets or sets the default page options for the workbook. + /// All new worksheets will use these page options. + /// + IXLPageSetup PageOptions { get; set; } + + /// + /// Gets or sets the workbook's properties. + /// + XLWorkbookProperties Properties { get; set; } + + /// + /// Gets or sets the workbook's reference style. + /// + XLReferenceStyle ReferenceStyle { get; set; } + + Boolean RightToLeft { get; set; } + + /// + /// Gets or sets the default row height for the workbook. + /// All new worksheets will use this row height. + /// + Double RowHeight { get; set; } + + Boolean ShowFormulas { get; set; } + + Boolean ShowGridLines { get; set; } + + Boolean ShowOutlineSymbols { get; set; } + + Boolean ShowRowColHeaders { get; set; } + + Boolean ShowRuler { get; set; } + + Boolean ShowWhiteSpace { get; set; } + + Boolean ShowZeros { get; set; } + + /// + /// Gets or sets the default style for the workbook. + /// All new worksheets will use this style. + /// + IXLStyle Style { get; set; } + + /// + /// Gets an object to manipulate this workbook's theme. + /// + IXLTheme Theme { get; } + + Boolean Use1904DateSystem { get; set; } + + /// + /// Gets an object to manipulate the worksheets. + /// + IXLWorksheets Worksheets { get; } + + IXLWorksheet AddWorksheet(String sheetName); + + IXLWorksheet AddWorksheet(String sheetName, Int32 position); + + IXLWorksheet AddWorksheet(DataTable dataTable); + + void AddWorksheet(DataSet dataSet); + + void AddWorksheet(IXLWorksheet worksheet); + + IXLWorksheet AddWorksheet(DataTable dataTable, String sheetName); + + IXLCell Cell(String namedCell); + + IXLCells Cells(String namedCells); + + IXLCustomProperty CustomProperty(String name); + + Object Evaluate(String expression); + + IXLCells FindCells(Func predicate); + + IXLColumns FindColumns(Func predicate); + + IXLRows FindRows(Func predicate); + + IXLNamedRange NamedRange(String rangeName); + + void Protect(Boolean lockStructure, Boolean lockWindows, String workbookPassword); + + void Protect(); + + void Protect(string workbookPassword); + + void Protect(Boolean lockStructure); + + void Protect(Boolean lockStructure, Boolean lockWindows); + + IXLRange Range(String range); + + IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws); + + IXLRanges Ranges(String ranges); + + /// + /// Saves the current workbook. + /// + void Save(); + + /// + /// Saves the current workbook and optionally performs validation + /// + void Save(Boolean validate, Boolean evaluateFormulae = false); + + void Save(SaveOptions options); + + /// + /// Saves the current workbook to a file. + /// + void SaveAs(String file); + + /// + /// Saves the current workbook to a file and optionally validates it. + /// + void SaveAs(String file, Boolean validate, Boolean evaluateFormulae = false); + + void SaveAs(String file, SaveOptions options); + + /// + /// Saves the current workbook to a stream. + /// + void SaveAs(Stream stream); + + /// + /// Saves the current workbook to a stream and optionally validates it. + /// + void SaveAs(Stream stream, Boolean validate, Boolean evaluateFormulae = false); + + void SaveAs(Stream stream, SaveOptions options); + + /// + /// Searches the cells' contents for a given piece of text + /// + /// The search text. + /// The compare options. + /// if set to true search formulae instead of cell values. + /// + IEnumerable Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false); + + XLWorkbook SetLockStructure(Boolean value); + + XLWorkbook SetLockWindows(Boolean value); + + XLWorkbook SetUse1904DateSystem(); + + XLWorkbook SetUse1904DateSystem(Boolean value); + + Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet); + + void Unprotect(); + + void Unprotect(string workbookPassword); + + IXLWorksheet Worksheet(String name); + + IXLWorksheet Worksheet(Int32 position); + } +} diff --git a/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs b/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs index 77f37ff..21ae91e 100644 --- a/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs +++ b/ClosedXML/Excel/PageSetup/XLHeaderFooter.cs @@ -80,13 +80,15 @@ private static IEnumerable ParseFormattedHeaderFooterText(string text) { + Func IsAtPositionIndicator = i => i < text.Length - 1 && text[i] == '&' && (new char[] { 'L', 'C', 'R' }.Contains(text[i + 1])); + var parsedElements = new List(); var currentPosition = 'L'; // default is LEFT var hfElement = ""; for (int i = 0; i < text.Length; i++) { - if (i < text.Length - 1 && text[i] == '&' && (new char[] { 'L', 'C', 'R' }.Contains(text[i + 1]))) + if (IsAtPositionIndicator(i)) { if ("" != hfElement) parsedElements.Add(new ParsedHeaderFooterElement() { @@ -99,7 +101,13 @@ hfElement = ""; } - hfElement += text[i]; + if (i < text.Length) + { + if (IsAtPositionIndicator(i)) + i--; + else + hfElement += text[i]; + } } if ("" != hfElement) diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 20b3ef4..cc6fe91 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -35,6 +35,7 @@ Boolean ShowBlankItems { get; set; } Boolean InsertPageBreaks { get; set; } Boolean Collapsed { get; set; } + XLPivotSortType SortType { get; set; } IXLPivotField SetCustomName(String value); @@ -48,6 +49,7 @@ IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); + IXLPivotField SetSort(XLPivotSortType value); IList SelectedValues { get; } IXLPivotField AddSelectedValue(Object value); diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 027dd94..bcc4292 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -91,6 +91,13 @@ PivotStyleMedium9 } + public enum XLPivotSortType + { + Default = 0, + Ascending = 1, + Descending = 2 + } + public enum XLPivotSubtotals { DoNotShow, diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index acf0b1c..64a0b99 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -12,6 +12,7 @@ SourceName = sourceName; Subtotals = new List(); SelectedValues = new List(); + SortType = XLPivotSortType.Default; } public String SourceName { get; private set; } @@ -69,6 +70,10 @@ public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } + public XLPivotSortType SortType { get; set; } + + public IXLPivotField SetSort(XLPivotSortType value) { SortType = value; return this; } + public IList SelectedValues { get; private set; } public IXLPivotField AddSelectedValue(Object value) { diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 6832af3..1d7895d 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -295,19 +295,35 @@ private void SetExcelDefaults() { EmptyCellReplacement = String.Empty; - AutofitColumns = true; - PreserveCellFormatting = true; - ShowGrandTotalsColumns = true; - ShowGrandTotalsRows = true; - UseCustomListsForSorting = true; - ShowExpandCollapseButtons = true; - ShowContextualTooltips = true; - DisplayCaptionsAndDropdowns = true; - RepeatRowLabels = true; SaveSourceData = true; - EnableShowDetails = true; ShowColumnHeaders = true; ShowRowHeaders = true; + + // source http://www.datypic.com/sc/ooxml/e-ssml_pivotTableDefinition.html + DisplayItemLabels = true; // Show Item Names + ShowExpandCollapseButtons = true; // Show Expand Collapse + PrintExpandCollapsedButtons = false; // Print Drill Indicators + ShowPropertiesInTooltips = true; // Show Member Property ToolTips + ShowContextualTooltips = true; // Show ToolTips on Data + EnableShowDetails = true; // Enable Drill Down + PreserveCellFormatting = true; // Preserve Formatting + AutofitColumns = false; // Auto Formatting + FilterAreaOrder = XLFilterAreaOrder.DownThenOver; // Page Over Then Down + FilteredItemsInSubtotals = false; // Subtotal Hidden Items + ShowGrandTotalsRows = true; // Row Grand Totals + ShowGrandTotalsColumns = true; // Grand Totals On Columns + PrintTitles = false; // Field Print Titles + RepeatRowLabels = false; // Item Print Titles + MergeAndCenterWithLabels = false; // Merge Titles + RowLabelIndent = 1; // Indentation for Compact Axis + ShowEmptyItemsOnRows = false; // Show Empty Row + ShowEmptyItemsOnColumns = false; // Show Empty Column + DisplayCaptionsAndDropdowns = true; // Show Field Headers + ClassicPivotTableLayout = false; // Enable Drop Zones + AllowMultipleFilters = true; // Multiple Field Filters + SortFieldsAtoZ = false; // Default Sort Order + UseCustomListsForSorting = true; // Custom List AutoSort + } } } diff --git a/ClosedXML/Excel/Ranges/IXLBaseCollection.cs b/ClosedXML/Excel/Ranges/IXLBaseCollection.cs index ea38cb1..ef6b851 100644 --- a/ClosedXML/Excel/Ranges/IXLBaseCollection.cs +++ b/ClosedXML/Excel/Ranges/IXLBaseCollection.cs @@ -66,7 +66,7 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeFormats); - TMultiple SetDataType(XLCellValues dataType); + TMultiple SetDataType(XLDataType dataType); /// /// Clears the contents of these ranges. diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index 945c0c9..291e290 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -274,7 +274,7 @@ IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); - IXLRange SetDataType(XLCellValues dataType); + IXLRange SetDataType(XLDataType dataType); /// /// Clears the contents of this range. diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index b0dd238..3c5d25e 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -39,7 +39,7 @@ /// The type of the cell's data. /// /// - XLCellValues DataType { set; } + XLDataType DataType { set; } /// /// Sets the cells' formula with A1 references. diff --git a/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index c70a5ff..3e701bf 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -84,7 +84,7 @@ IXLRangeColumn Column(IXLCell start, IXLCell end); IXLRangeColumns Columns(String columns); - IXLRangeColumn SetDataType(XLCellValues dataType); + IXLRangeColumn SetDataType(XLDataType dataType); IXLRangeColumn ColumnLeft(); IXLRangeColumn ColumnLeft(Int32 step); diff --git a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index 80b22e9..89c820e 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -35,7 +35,7 @@ IXLStyle Style { get; set; } - IXLRangeColumns SetDataType(XLCellValues dataType); + IXLRangeColumns SetDataType(XLDataType dataType); /// /// Clears the contents of these columns. diff --git a/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/Excel/Ranges/IXLRangeRow.cs index c13753c..7c34ad3 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -92,7 +92,7 @@ IXLRangeRow Row(IXLCell start, IXLCell end); IXLRangeRows Rows(String rows); - IXLRangeRow SetDataType(XLCellValues dataType); + IXLRangeRow SetDataType(XLDataType dataType); IXLRangeRow RowAbove(); IXLRangeRow RowAbove(Int32 step); diff --git a/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 6c2da5a..cc3e3df 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -34,7 +34,7 @@ IXLStyle Style { get; set; } - IXLRangeRows SetDataType(XLCellValues dataType); + IXLRangeRows SetDataType(XLDataType dataType); /// /// Clears the contents of these rows. diff --git a/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/Excel/Ranges/IXLRanges.cs index 48aeaff..f767b18 100644 --- a/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -82,7 +82,7 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeFormats); - IXLRanges SetDataType(XLCellValues dataType); + IXLRanges SetDataType(XLDataType dataType); /// /// Clears the contents of these ranges. diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 1991c89..4a19fcc 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -327,7 +327,7 @@ lastColumnNumber); } - public IXLRange SetDataType(XLCellValues dataType) + public IXLRange SetDataType(XLDataType dataType) { DataType = dataType; return this; @@ -355,12 +355,12 @@ #endregion IXLRange Members - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { ShiftColumns(RangeAddress, range, columnsShifted); } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { ShiftRows(RangeAddress, range, rowsShifted); } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 13f986a..b1d20c4 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -143,7 +143,7 @@ { if (includeSheet) return String.Format("{0}!{1}:{2}", - Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + Worksheet.Name.EscapeSheetName(), _firstAddress.ToStringRelative(), _lastAddress.ToStringRelative()); @@ -159,7 +159,7 @@ { if (includeSheet) return String.Format("{0}!{1}:{2}", - Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + Worksheet.Name.EscapeSheetName(), _firstAddress.ToStringFixed(referenceStyle), _lastAddress.ToStringFixed(referenceStyle)); diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index a44e45f..cc6a57c 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -251,7 +251,7 @@ set { Cells().ForEach(c => c.Value = value); } } - public XLCellValues DataType + public XLDataType DataType { set { Cells().ForEach(c => c.DataType = value); } } @@ -1597,121 +1597,109 @@ public override string ToString() { - return String.Format("{0}!{1}:{2}", Worksheet.Name.WrapSheetNameInQuotesIfRequired(), RangeAddress.FirstAddress, RangeAddress.LastAddress); + return String.Format("{0}!{1}:{2}", Worksheet.Name.EscapeSheetName(), RangeAddress.FirstAddress, RangeAddress.LastAddress); } protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) { if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if ((columnsShifted < 0 - // all columns - && - thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && - thisRangeAddress.LastAddress.ColumnNumber <= - shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - ) || ( - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber - && shiftedRange.ColumnCount() > - (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) - + - (thisRangeAddress.FirstAddress.ColumnNumber - - shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted > 0) - || - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < - thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber, - thisRangeAddress.FirstAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + bool allRowsAreCovered = thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && + thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber; - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - thisRangeAddress.LastAddress.ColumnNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber, - thisRangeAddress.LastAddress.ColumnNumber + - columnsShifted, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } - } + if (!allRowsAreCovered) + return; + + bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) || + (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); + + bool shiftRightBoundary = thisRangeAddress.LastAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber; + + int newLeftBoundary = thisRangeAddress.FirstAddress.ColumnNumber; + if (shiftLeftBoundary) + { + if (newLeftBoundary + columnsShifted > shiftedRange.RangeAddress.FirstAddress.ColumnNumber) + newLeftBoundary = newLeftBoundary + columnsShifted; + else + newLeftBoundary = shiftedRange.RangeAddress.FirstAddress.ColumnNumber; } + + int newRightBoundary = thisRangeAddress.LastAddress.ColumnNumber; + if (shiftRightBoundary) + newRightBoundary += columnsShifted; + + bool destroyedByShift = newRightBoundary < newLeftBoundary; + + if (destroyedByShift) + { + thisRangeAddress.IsInvalid = true; + return; + } + + if (shiftLeftBoundary) + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + thisRangeAddress.FirstAddress.RowNumber, + newLeftBoundary, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + + if (shiftRightBoundary) + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + thisRangeAddress.LastAddress.RowNumber, + newRightBoundary, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; - if ((rowsShifted < 0 - // all columns - && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - // all rows - && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted - ) || ( - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber - && shiftedRange.RowCount() > - (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) - + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) - thisRangeAddress.IsInvalid = true; - else - { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) - { - if ( - (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && rowsShifted > 0) - || (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && rowsShifted < 0) - ) - { - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber + - rowsShifted, - thisRangeAddress.FirstAddress.ColumnNumber, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); - } + bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && + thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) - { - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber + - rowsShifted, - thisRangeAddress.LastAddress.ColumnNumber, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); - } - } + if (!allColumnsAreCovered) + return; + + bool shiftTopBoundary = (rowsShifted > 0 && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber) || + (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber); + + bool shiftBottomBoundary = thisRangeAddress.LastAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber; + + int newTopBoundary = thisRangeAddress.FirstAddress.RowNumber; + if (shiftTopBoundary) + { + if (newTopBoundary + rowsShifted > shiftedRange.RangeAddress.FirstAddress.RowNumber) + newTopBoundary = newTopBoundary + rowsShifted; + else + newTopBoundary = shiftedRange.RangeAddress.FirstAddress.RowNumber; } + + int newBottomBoundary = thisRangeAddress.LastAddress.RowNumber; + if (shiftBottomBoundary) + newBottomBoundary += rowsShifted; + + bool destroyedByShift = newBottomBoundary < newTopBoundary; + + if (destroyedByShift) + { + thisRangeAddress.IsInvalid = true; + return; + } + + if (shiftTopBoundary) + thisRangeAddress.FirstAddress = new XLAddress(Worksheet, + newTopBoundary, + thisRangeAddress.FirstAddress.ColumnNumber, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); + + if (shiftBottomBoundary) + thisRangeAddress.LastAddress = new XLAddress(Worksheet, + newBottomBoundary, + thisRangeAddress.LastAddress.ColumnNumber, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); } public IXLRange RangeUsed() @@ -2189,4 +2177,4 @@ return cells; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 34710ea..ae85bf2 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -175,7 +175,7 @@ return retVal; } - public IXLRangeColumn SetDataType(XLCellValues dataType) + public IXLRangeColumn SetDataType(XLDataType dataType) { DataType = dataType; return this; @@ -253,13 +253,13 @@ { if (thisCell.DataType == otherCell.DataType) { - if (thisCell.DataType == XLCellValues.Text) + if (thisCell.DataType == XLDataType.Text) { comparison = e.MatchCase ? thisCell.InnerText.CompareTo(otherCell.InnerText) : String.Compare(thisCell.InnerText, otherCell.InnerText, true); } - else if (thisCell.DataType == XLCellValues.TimeSpan) + else if (thisCell.DataType == XLDataType.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); else comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); diff --git a/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 5c78861..f2481ed 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -83,7 +83,7 @@ return cells; } - public IXLRangeColumns SetDataType(XLCellValues dataType) + public IXLRangeColumns SetDataType(XLDataType dataType) { _ranges.ForEach(c => c.DataType = dataType); return this; diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index a579975..9b6be16 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -174,7 +174,7 @@ return retVal; } - public IXLRangeRow SetDataType(XLCellValues dataType) + public IXLRangeRow SetDataType(XLDataType dataType) { DataType = dataType; return this; @@ -247,13 +247,13 @@ { if (thisCell.DataType == otherCell.DataType) { - if (thisCell.DataType == XLCellValues.Text) + if (thisCell.DataType == XLDataType.Text) { comparison = e.MatchCase ? thisCell.InnerText.CompareTo(otherCell.InnerText) : String.Compare(thisCell.InnerText, otherCell.InnerText, true); } - else if (thisCell.DataType == XLCellValues.TimeSpan) + else if (thisCell.DataType == XLDataType.TimeSpan) comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); else comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); diff --git a/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/Excel/Ranges/XLRangeRows.cs index 205d04b..bbd51f8 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -83,7 +83,7 @@ return cells; } - public IXLRangeRows SetDataType(XLCellValues dataType) + public IXLRangeRows SetDataType(XLDataType dataType) { _ranges.ForEach(c => c.DataType = dataType); return this; diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index 2fc41c0..7942134 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -160,7 +160,7 @@ return cells; } - public IXLRanges SetDataType(XLCellValues dataType) + public IXLRanges SetDataType(XLDataType dataType) { _ranges.ForEach(c => c.DataType = dataType); return this; diff --git a/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/Excel/Rows/IXLRow.cs index 70816fb..a89ddda 100644 --- a/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/Excel/Rows/IXLRow.cs @@ -177,7 +177,7 @@ /// IXLRow AddHorizontalPageBreak(); - IXLRow SetDataType(XLCellValues dataType); + IXLRow SetDataType(XLDataType dataType); IXLRow RowAbove(); IXLRow RowAbove(Int32 step); diff --git a/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/Excel/Rows/IXLRows.cs index 5249e64..5f4c6bc 100644 --- a/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/Excel/Rows/IXLRows.cs @@ -112,7 +112,7 @@ /// IXLRows AddHorizontalPageBreaks(); - IXLRows SetDataType(XLCellValues dataType); + IXLRows SetDataType(XLDataType dataType); /// /// Clears the contents of these rows. diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index e6aab85..145e37e 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -578,7 +578,7 @@ return this; } - public IXLRow SetDataType(XLCellValues dataType) + public IXLRow SetDataType(XLDataType dataType) { DataType = dataType; return this; diff --git a/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/Excel/Rows/XLRows.cs index f44cb48..27583d1 100644 --- a/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/Excel/Rows/XLRows.cs @@ -203,7 +203,7 @@ return this; } - public IXLRows SetDataType(XLCellValues dataType) + public IXLRows SetDataType(XLDataType dataType) { _rows.ForEach(c => c.DataType = dataType); return this; diff --git a/ClosedXML/Excel/Style/Colors/XLColor_Static.cs b/ClosedXML/Excel/Style/Colors/XLColor_Static.cs index 2ae6314..efd6b7c 100644 --- a/ClosedXML/Excel/Style/Colors/XLColor_Static.cs +++ b/ClosedXML/Excel/Style/Colors/XLColor_Static.cs @@ -1,3 +1,4 @@ +using ClosedXML.Utils; using System; using System.Collections.Generic; using System.Drawing; @@ -52,7 +53,7 @@ public static XLColor FromHtml(String htmlColor) { - return FromColor(ColorTranslator.FromHtml(htmlColor)); + return FromColor(ColorStringParser.ParseFromHtml(htmlColor)); } private static readonly Dictionary ByIndex = new Dictionary(); diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 61d8ba9..019cf5d 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -100,7 +100,7 @@ { name = GetUniqueName("Column", cellPos + 1, true); cell.SetValue(name); - cell.DataType = XLCellValues.Text; + cell.DataType = XLDataType.Text; } if (_fieldNames.ContainsKey(name)) throw new ArgumentException("The header row contains more than one field name '" + name + "'."); @@ -383,7 +383,7 @@ if (!c.IsEmpty() && newHeaders.Contains(f.Name)) { f.TotalsRowLabel = c.GetFormattedString(); - c.DataType = XLCellValues.Text; + c.DataType = XLDataType.Text; } } @@ -395,7 +395,7 @@ var c = this.TotalsRow().Cell(f.Index + 1); if (!XLHelper.IsNullOrWhiteSpace(f.TotalsRowLabel)) { - c.DataType = XLCellValues.Text; + c.DataType = XLDataType.Text; //Remove previous row's label var oldTotalsCell = this.Worksheet.Cell(oldTotalsRowNumber, f.Column.ColumnNumber()); @@ -404,7 +404,7 @@ } if (f.TotalsRowFunction != XLTotalsRowFunction.None) - c.DataType = XLCellValues.Number; + c.DataType = XLDataType.Number; } } } @@ -545,7 +545,7 @@ if (setAutofilter) InitializeAutoFilter(); - AsRange().Row(1).DataType = XLCellValues.Text; + AsRange().Row(1).DataType = XLDataType.Text; if (RowCount() == 1) InsertRowsBelow(1); @@ -678,7 +678,7 @@ _showHeaderRow = value; if (_showHeaderRow) - HeadersRow().DataType = XLCellValues.Text; + HeadersRow().DataType = XLDataType.Text; } } @@ -789,23 +789,23 @@ var c = f.Column.Cells().Skip(this.ShowHeaderRow ? 1 : 0).First(); switch (c.DataType) { - case XLCellValues.Text: + case XLDataType.Text: type = typeof(String); break; - case XLCellValues.Boolean: + case XLDataType.Boolean: type = typeof(Boolean); break; - case XLCellValues.DateTime: + case XLDataType.DateTime: type = typeof(DateTime); break; - case XLCellValues.TimeSpan: + case XLDataType.TimeSpan: type = typeof(TimeSpan); break; - case XLCellValues.Number: + case XLDataType.Number: type = typeof(Double); break; } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index ed65bed..4ddcd73 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -194,7 +194,7 @@ cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; var lastCell = table.LastRow().Cell(Index + 1); - if (lastCell.DataType != XLCellValues.Text) + if (lastCell.DataType != XLDataType.Text) { cell.DataType = lastCell.DataType; cell.Style.NumberFormat = lastCell.Style.NumberFormat; diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index b1173de..941db42 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -41,7 +41,7 @@ Simple = 1, } - public partial class XLWorkbook: IDisposable + public partial class XLWorkbook : IXLWorkbook { #region Static @@ -53,60 +53,60 @@ { return _defaultStyle ?? (_defaultStyle = new XLStyle(null) + { + Font = new XLFont(null, 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 - } - }); + 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 + } + }); } } @@ -123,14 +123,14 @@ 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 - }, + { + 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, @@ -190,7 +190,7 @@ return _stylesById[id]; } - #region Nested Type: XLLoadSource + #region Nested Type: XLLoadSource private enum XLLoadSource { @@ -402,7 +402,7 @@ public Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet) { return Worksheets.TryGetWorksheet(name, out worksheet); - } + } public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws) { @@ -498,7 +498,10 @@ else if (_loadSource == XLLoadSource.File) { if (String.Compare(_originalFile.Trim(), file.Trim(), true) != 0) + { File.Copy(_originalFile, file, true); + File.SetAttributes(file, FileAttributes.Normal); + } CreatePackage(file, GetSpreadsheetDocumentType(file), options); } @@ -699,7 +702,7 @@ } } -#region Fields + #region Fields private XLLoadSource _loadSource = XLLoadSource.New; private String _originalFile; @@ -707,13 +710,13 @@ #endregion Fields -#region Constructor + #region Constructor /// /// Creates a new Excel workbook. /// public XLWorkbook() - :this(XLEventTracking.Enabled) + : this(XLEventTracking.Enabled) { } @@ -774,7 +777,7 @@ /// Opens an existing workbook from a stream. /// /// The stream to open. - public XLWorkbook(Stream stream):this(stream, XLEventTracking.Enabled) + public XLWorkbook(Stream stream) : this(stream, XLEventTracking.Enabled) { } @@ -788,7 +791,7 @@ #endregion Constructor -#region Nested type: UnsupportedSheet + #region Nested type: UnsupportedSheet internal sealed class UnsupportedSheet { @@ -953,7 +956,7 @@ LockStructure = lockStructure; LockWindows = lockWindows; } - + public void Protect() { Protect(true); @@ -977,16 +980,29 @@ public void Unprotect() { Protect(false, false); - } + } public void Unprotect(string workbookPassword) { Protect(false, false, workbookPassword); -} + } public override string ToString() { - return _originalFile ?? String.Format("XLWorkbook({0})", _originalStream.ToString()); + switch (_loadSource) + { + case XLLoadSource.New: + return "XLWorkbook(new)"; + + case XLLoadSource.File: + return String.Format("XLWorkbook({0})", _originalFile); + + case XLLoadSource.Stream: + return String.Format("XLWorkbook({0})", _originalStream.ToString()); + + default: + throw new NotImplementedException(); + } + } } } -} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 730da8a..c39990e 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -61,7 +61,7 @@ SetProperties(dSpreadsheet); SharedStringItem[] sharedStrings = null; - if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + if (dSpreadsheet.WorkbookPart.GetPartsOfType().Any()) { var shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); @@ -71,7 +71,11 @@ { foreach (var m in dSpreadsheet.CustomFilePropertiesPart.Properties.Elements()) { - String name = m.Name.Value; + String name = m.Name?.Value; + + if (string.IsNullOrWhiteSpace(name)) + continue; + if (m.VTLPWSTR != null) CustomProperties.Add(name, m.VTLPWSTR.Text); else if (m.VTFileTime != null) @@ -88,7 +92,7 @@ } var wbProps = dSpreadsheet.WorkbookPart.Workbook.WorkbookProperties; - Use1904DateSystem = wbProps != null && wbProps.Date1904 != null && wbProps.Date1904.Value; + Use1904DateSystem = wbProps?.Date1904?.Value ?? false; var wbProtection = dSpreadsheet.WorkbookPart.Workbook.WorkbookProtection; if (wbProtection != null) @@ -506,6 +510,15 @@ if (pivotTableDefinition.ItemPrintTitles != null) pt.RepeatRowLabels = pivotTableDefinition.ItemPrintTitles.Value; if (pivotTableDefinition.FieldPrintTitles != null) pt.PrintTitles = pivotTableDefinition.FieldPrintTitles.Value; if (pivotTableDefinition.EnableDrill != null) pt.EnableShowDetails = pivotTableDefinition.EnableDrill.Value; + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData != null) pt.SaveSourceData = pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData.Value; + + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit != null) + { + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == 0U) + pt.ItemsToRetainPerField = XLItemsToRetain.None; + else if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == XLHelper.MaxRowNumber) + pt.ItemsToRetainPerField = XLItemsToRetain.Max; + } if (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null) pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value; @@ -513,6 +526,25 @@ if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null) pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; + var pivotTableDefinitionExtensionList = pivotTableDefinition.GetFirstChild(); + var pivotTableDefinitionExtension = pivotTableDefinitionExtensionList?.GetFirstChild(); + var pivotTableDefinition2 = pivotTableDefinitionExtension?.GetFirstChild(); + if (pivotTableDefinition2 != null) + { + if (pivotTableDefinition2.EnableEdit != null) pt.EnableCellEditing = pivotTableDefinition2.EnableEdit.Value; + if (pivotTableDefinition2.HideValuesRow != null) pt.ShowValuesRow = !pivotTableDefinition2.HideValuesRow.Value; + } + + var pivotTableStyle = pivotTableDefinition.GetFirstChild(); + if (pivotTableStyle != null) + { + pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); + pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders; + pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders; + pt.ShowRowStripes = pivotTableStyle.ShowRowStripes; + pt.ShowColumnStripes = pivotTableStyle.ShowColumnStripes; + } + // Subtotal configuration if (pivotTableDefinition.PivotFields.Cast().All(pf => pf.SubtotalTop != null && pf.SubtotalTop.HasValue && pf.SubtotalTop.Value)) pt.SetSubtotals(XLPivotSubtotals.AtTop); @@ -547,14 +579,41 @@ if (pivotField != null) { + if (pf.AverageSubTotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Average); + if (pf.CountASubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Count); + if (pf.CountSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); + if (pf.MaxSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Maximum); + if (pf.MinSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Minimum); + if (pf.ApplyStandardDeviationPInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); + if (pf.ApplyVariancePInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); + if (pf.ApplyProductInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Product); + if (pf.ApplyStandardDeviationInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); + if (pf.SumSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Sum); + if (pf.ApplyVarianceInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Variance); var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) pivotField.SetCollapsed(); + + if (pf.SortType != null) + { + pivotField.SetSort((XLPivotSortType)pf.SortType.Value); } } } } } + } // Column labels if (pivotTableDefinition.ColumnFields != null) @@ -580,13 +639,40 @@ if (pivotField != null) { + if (pf.AverageSubTotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Average); + if (pf.CountASubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Count); + if (pf.CountSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); + if (pf.MaxSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Maximum); + if (pf.MinSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Minimum); + if (pf.ApplyStandardDeviationPInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); + if (pf.ApplyVariancePInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); + if (pf.ApplyProductInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Product); + if (pf.ApplyStandardDeviationInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); + if (pf.SumSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Sum); + if (pf.ApplyVarianceInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Variance); var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) pivotField.SetCollapsed(); + + if (pf.SortType != null) + { + pivotField.SetSort((XLPivotSortType)pf.SortType.Value); } } } } + } // Values if (pivotTableDefinition.DataFields != null) @@ -676,7 +762,7 @@ else throw new NotImplementedException(); } - else if (BooleanValue.ToBoolean(pf.MultipleItemSelectionAllowed)) + else if (OpenXmlHelper.GetBooleanValueAsBool(pf.MultipleItemSelectionAllowed, false)) { foreach (var item in pf.Items.Cast()) { @@ -735,7 +821,11 @@ Xdr.ShapeProperties spPr = anchor.Descendants().First(); picture.Placement = XLPicturePlacement.FreeFloating; + + if (spPr?.Transform2D?.Extents?.Cx.HasValue ?? false) picture.Width = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cx, GraphicsUtils.Graphics.DpiX); + + if (spPr?.Transform2D?.Extents?.Cy.HasValue ?? false) picture.Height = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cy, GraphicsUtils.Graphics.DpiY); if (anchor is Xdr.AbsoluteAnchor) @@ -1129,7 +1219,7 @@ { string sheetName, sheetArea; ParseReference(area, out sheetName, out sheetArea); - if (!(sheetArea.Equals("#REF") || sheetArea.EndsWith("#REF!") || sheetArea.Length == 0)) + if (!(sheetArea.Equals("#REF") || sheetArea.EndsWith("#REF!") || sheetArea.Length == 0 || sheetName.Length == 0)) WorksheetsInternal.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); } } @@ -1229,7 +1319,7 @@ } else { - sheetName = sections[0].Replace("\'", ""); + sheetName = sections[0].UnescapeSheetName(); sheetArea = sections[1]; } } @@ -1322,7 +1412,7 @@ else xlCell._cellValue = String.Empty; - xlCell._dataType = XLCellValues.Text; + xlCell._dataType = XLDataType.Text; xlCell.ShareString = false; } else if (cell.DataType == CellValues.SharedString) @@ -1335,19 +1425,19 @@ else xlCell._cellValue = String.Empty; - xlCell._dataType = XLCellValues.Text; + xlCell._dataType = XLDataType.Text; } else if (cell.DataType == CellValues.Date) { if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); - xlCell._dataType = XLCellValues.DateTime; + xlCell._dataType = XLDataType.DateTime; } else if (cell.DataType == CellValues.Boolean) { if (cell.CellValue != null) xlCell._cellValue = cell.CellValue.Text; - xlCell._dataType = XLCellValues.Boolean; + xlCell._dataType = XLDataType.Boolean; } else if (cell.DataType == CellValues.Number) { @@ -1355,7 +1445,7 @@ xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); if (s == null) - xlCell._dataType = XLCellValues.Number; + xlCell._dataType = XLDataType.Number; else xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } @@ -1364,7 +1454,7 @@ { if (s == null) { - xlCell._dataType = XLCellValues.Number; + xlCell._dataType = XLDataType.Number; } else { @@ -1387,6 +1477,13 @@ xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } + + if (Use1904DateSystem && xlCell.DataType == XLDataType.DateTime) + { + // Internally ClosedXML stores cells as standard 1900-based style + // so if a workbook is in 1904-format, we do that adjustment here and when saving. + xlCell.SetValue(xlCell.GetDateTime().AddDays(1462)); + } } /// @@ -1673,29 +1770,29 @@ } } - private static XLCellValues GetDataTypeFromCell(IXLNumberFormat numberFormat) + private static XLDataType GetDataTypeFromCell(IXLNumberFormat numberFormat) { var numberFormatId = numberFormat.NumberFormatId; if (numberFormatId == 46U) - return XLCellValues.TimeSpan; + return XLDataType.TimeSpan; else if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) - return XLCellValues.DateTime; + return XLDataType.DateTime; else if (numberFormatId == 49) - return XLCellValues.Text; + return XLDataType.Text; else { if (!XLHelper.IsNullOrWhiteSpace(numberFormat.Format)) { var dataType = GetDataTypeFromFormat(numberFormat.Format); - return dataType.HasValue ? dataType.Value : XLCellValues.Number; + return dataType.HasValue ? dataType.Value : XLDataType.Number; } else - return XLCellValues.Number; + return XLDataType.Number; } } - private static XLCellValues? GetDataTypeFromFormat(String format) + private static XLDataType? GetDataTypeFromFormat(String format) { int length = format.Length; String f = format.ToLower(); @@ -1705,9 +1802,9 @@ if (c == '"') i = f.IndexOf('"', i + 1); else if (c == '0' || c == '#' || c == '?') - return XLCellValues.Number; + return XLDataType.Number; else if (c == 'y' || c == 'm' || c == 'd' || c == 'h' || c == 's') - return XLCellValues.DateTime; + return XLDataType.DateTime; } return null; } @@ -1889,6 +1986,7 @@ if (sp.AutoFilter != null) ws.Protection.AutoFilter = !sp.AutoFilter.Value; if (sp.PivotTables != null) ws.Protection.PivotTables = !sp.PivotTables.Value; if (sp.Sort != null) ws.Protection.Sort = !sp.Sort.Value; + if (sp.Objects != null) ws.Protection.Objects = !sp.Objects.Value; if (sp.SelectLockedCells != null) ws.Protection.SelectLockedCells = sp.SelectLockedCells.Value; if (sp.SelectUnlockedCells != null) ws.Protection.SelectUnlockedCells = sp.SelectUnlockedCells.Value; } @@ -1934,7 +2032,7 @@ { var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value)); - conditionalFormat.StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false); + conditionalFormat.StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false); if (fr.FormatId != null) { @@ -1963,6 +2061,13 @@ if (fr.Rank != null) conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString())); } + else if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.TimePeriod) + { + if (fr.TimePeriod != null) + conditionalFormat.TimePeriod = fr.TimePeriod.Value.ToClosedXml(); + else + conditionalFormat.TimePeriod = XLTimePeriod.Yesterday; + } if (fr.Elements().Any()) { @@ -2332,7 +2437,7 @@ Color thisColor; if (!_colorList.ContainsKey(htmlColor)) { - thisColor = ColorTranslator.FromHtml(htmlColor); + thisColor = ColorStringParser.ParseFromHtml(htmlColor); _colorList.Add(htmlColor, thisColor); } else diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index c4a9961..35f43af 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -150,7 +150,7 @@ internal struct PivotTableFieldInfo { - public XLCellValues DataType; + public XLDataType DataType; public Boolean MixedDataType; public IEnumerable DistinctValues; } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 69dcf2f..bdb6320 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -65,20 +65,20 @@ { switch (xlCell.DataType) { - case XLCellValues.Text: + case XLDataType.Text: { return xlCell.ShareString ? CvSharedString : CvInlineString; } - case XLCellValues.Number: + case XLDataType.Number: return CvNumber; - case XLCellValues.DateTime: + case XLDataType.DateTime: return CvDate; - case XLCellValues.Boolean: + case XLDataType.Boolean: return CvBoolean; - case XLCellValues.TimeSpan: + case XLDataType.TimeSpan: return CvNumber; default: @@ -733,7 +733,7 @@ var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, (current, printArea) => current + - ("'" + worksheetName + "'!" + + (worksheetName.EscapeSheetName() + "!" + printArea.RangeAddress. FirstAddress.ToStringFixed( XLReferenceStyle.A1) + @@ -752,7 +752,7 @@ { Name = "_xlnm._FilterDatabase", LocalSheetId = sheetId, - Text = "'" + worksheet.Name + "'!" + + Text = worksheet.Name.EscapeSheetName() + "!" + worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed( XLReferenceStyle.A1) + ":" + @@ -784,14 +784,14 @@ var definedNameTextColumn = String.Empty; if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0) { - definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop + definedNameTextRow = worksheet.Name.EscapeSheetName() + "!" + worksheet.PageSetup.FirstRowToRepeatAtTop + ":" + worksheet.PageSetup.LastRowToRepeatAtTop; } if (worksheet.PageSetup.FirstColumnToRepeatAtLeft > 0) { var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft; var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft; - definedNameTextColumn = "'" + worksheet.Name + "'!" + + definedNameTextColumn = worksheet.Name.EscapeSheetName() + "!" + XLHelper.GetColumnLetterFromNumber(minColumn) + ":" + XLHelper.GetColumnLetterFromNumber(maxColumn); } @@ -873,12 +873,12 @@ Worksheets.Cast().SelectMany( w => w.Internals.CellsCollection.GetCells( - c => ((c.DataType == XLCellValues.Text && c.ShareString) || c.HasRichText) + c => ((c.DataType == XLDataType.Text && c.ShareString) || c.HasRichText) && (c as XLCell).InnerText.Length > 0 && XLHelper.IsNullOrWhiteSpace(c.FormulaA1) ))) { - c.DataType = XLCellValues.Text; + c.DataType = XLDataType.Text; if (c.HasRichText) { if (newRichStrings.ContainsKey(c.RichText)) @@ -1953,7 +1953,6 @@ // TODO: Avoid duplicate pivot caches of same source range - var workbookCacheRelId = pt.WorkbookCacheRelId; PivotCache pivotCache; PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) @@ -1963,7 +1962,7 @@ } else { - workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + var workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); pivotCache = new PivotCache { CacheId = cacheId++, Id = workbookCacheRelId }; pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); } @@ -2036,6 +2035,7 @@ if (field != null) { xlpf.CustomName = field.CustomName; + xlpf.SortType = field.SortType; xlpf.Subtotals.AddRange(field.Subtotals); } @@ -2043,23 +2043,30 @@ var ptfi = new PivotTableFieldInfo(); - var fieldValueCells = source.Cells(cell => cell.Address.ColumnNumber == columnNumber + var fieldValueCells = source.CellsUsed(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()); - var types = fieldValueCells.Select(cell => cell.DataType).Distinct(); + var types = fieldValueCells.Select(cell => cell.DataType).Distinct().ToArray(); + var containsBlank = source.CellsUsed(true, cell => cell.Address.ColumnNumber == columnNumber + && cell.Address.RowNumber > source.FirstRow().RowNumber() + && cell.IsEmpty()).Any(); - - if (types.Count() == 1 && types.Single() == XLCellValues.Number) + if (types.Length == 1 && types.Single() == XLDataType.Number) { sharedItems.ContainsSemiMixedTypes = false; sharedItems.ContainsString = false; sharedItems.ContainsNumber = true; - ptfi.DataType = XLCellValues.Number; + ptfi.DataType = XLDataType.Number; ptfi.MixedDataType = false; ptfi.DistinctValues = fieldValueCells .Select(cell => cell.GetDouble()) .Distinct() - .Cast(); + .Cast() + .ToArray(); + + int val; + var allInteger = ptfi.DistinctValues.All(v => int.TryParse(v.ToString(), out val)); + if (allInteger) sharedItems.ContainsInteger = true; pti.Fields.Add(xlpf.SourceName, ptfi); @@ -2070,24 +2077,27 @@ { foreach (var value in ptfi.DistinctValues) sharedItems.AppendChild(new NumberItem { Val = (double)value }); + + if (containsBlank) sharedItems.AppendChild(new MissingItem()); } sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); } - else if (types.Count() == 1 && types.Single() == XLCellValues.DateTime) + else if (types.Length == 1 && types.Single() == XLDataType.DateTime) { sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsNonDate = false; sharedItems.ContainsString = false; - sharedItems.ContainsNumber = false; sharedItems.ContainsDate = true; - ptfi.DataType = XLCellValues.DateTime; + ptfi.DataType = XLDataType.DateTime; ptfi.MixedDataType = false; ptfi.DistinctValues = fieldValueCells .Select(cell => cell.GetDateTime()) .Distinct() - .Cast(); + .Cast() + .ToArray(); pti.Fields.Add(xlpf.SourceName, ptfi); @@ -2098,6 +2108,8 @@ { foreach (var value in ptfi.DistinctValues) sharedItems.AppendChild(new DateTimeItem { Val = (DateTime)value }); + + if (containsBlank) sharedItems.AppendChild(new MissingItem()); } sharedItems.MinDate = (DateTime)ptfi.DistinctValues.Min(); @@ -2108,26 +2120,30 @@ if (types.Any()) { ptfi.DataType = types.First(); - ptfi.MixedDataType = types.Count() > 1; + ptfi.MixedDataType = types.Length > 1; - if (!ptfi.MixedDataType && ptfi.DataType == XLCellValues.Text) + if (!ptfi.MixedDataType && ptfi.DataType == XLDataType.Text) ptfi.DistinctValues = fieldValueCells .Select(cell => cell.Value) .Cast() - .Distinct(StringComparer.OrdinalIgnoreCase); + .Distinct(StringComparer.OrdinalIgnoreCase) + .ToArray(); else ptfi.DistinctValues = fieldValueCells .Select(cell => cell.GetString()) - .Cast() - .Distinct(StringComparer.OrdinalIgnoreCase); + .Distinct(StringComparer.OrdinalIgnoreCase) + .ToArray(); pti.Fields.Add(xlpf.SourceName, ptfi); foreach (var value in ptfi.DistinctValues) sharedItems.AppendChild(new StringItem { Val = (string)value }); + + if (containsBlank) sharedItems.AppendChild(new MissingItem()); } } + if (containsBlank) sharedItems.ContainsBlank = true; if (ptfi.DistinctValues.Any()) sharedItems.Count = Convert.ToUInt32(ptfi.DistinctValues.Count()); @@ -2165,16 +2181,16 @@ Name = pt.Name, CacheId = cacheId, DataCaption = "Values", - MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, true), + MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, false), Indent = Convert.ToUInt32(pt.RowLabelIndent), PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown), PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), - UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, true), + UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, false), PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true), RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), - SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true), + SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, false), MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), @@ -2182,13 +2198,13 @@ 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), + ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, false), + ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, false), 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), + FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, false), + PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, false), + ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, false), + FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, false), EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true) }; @@ -2311,6 +2327,11 @@ pf.Compact = false; } + if (xlpf.SortType != XLPivotSortType.Default) + { + pf.SortType = new EnumValue((FieldSortValues)xlpf.SortType); + } + switch (pt.Subtotals) { case XLPivotSubtotals.DoNotShow: @@ -2354,7 +2375,7 @@ if (labelOrFilterField.SelectedValues.Count == 1) { - if (ptfi.MixedDataType || ptfi.DataType == XLCellValues.Text) + if (ptfi.MixedDataType || ptfi.DataType == XLDataType.Text) { var values = ptfi.DistinctValues .Select(v => v.ToString().ToLower()) @@ -2363,7 +2384,7 @@ if (values.Contains(selectedValue)) pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); } - else if (ptfi.DataType == XLCellValues.DateTime) + else if (ptfi.DataType == XLDataType.DateTime) { var values = ptfi.DistinctValues .Select(v => Convert.ToDateTime(v)) @@ -2372,7 +2393,7 @@ if (values.Contains(selectedValue)) pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); } - else if (ptfi.DataType == XLCellValues.Number) + else if (ptfi.DataType == XLDataType.Number) { var values = ptfi.DistinctValues .Select(v => Convert.ToDouble(v)) @@ -2563,7 +2584,7 @@ var df = new DataField { Name = value.CustomName, - Field = (UInt32)sourceColumn.ColumnNumber() - 1, + Field = (UInt32)(sourceColumn.ColumnNumber() - pt.SourceRange.RangeAddress.FirstAddress.ColumnNumber), Subtotal = value.SummaryFormula.ToOpenXml(), ShowDataAs = value.Calculation.ToOpenXml(), NumberFormatId = numberFormatId @@ -4122,6 +4143,8 @@ private static void GenerateWorksheetPartContent( WorksheetPart worksheetPart, XLWorksheet xlWorksheet, bool evaluateFormulae, SaveContext context) { + ((XLConditionalFormats)xlWorksheet.ConditionalFormats).Consolidate(); + #region Worksheet if (worksheetPart.Worksheet == null) @@ -4274,18 +4297,28 @@ pane.HorizontalSplit = hSplit; pane.VerticalSplit = ySplit; + pane.ActivePane = (ySplit == 0 ? PaneValues.TopRight : 0) + | (hSplit == 0 ? PaneValues.BottomLeft : 0); + pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + (xlWorksheet.SheetView.SplitRow + 1); if (hSplit == 0 && ySplit == 0) + { + pane = null; sheetView.RemoveAllChildren(); + } + else + sheetView.TopLeftCell = null; if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null) { sheetView.RemoveAllChildren(); var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault(); - var selection = new Selection(); + + Action populateSelection = (Selection selection) => + { if (xlWorksheet.ActiveCell != null) selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false); else if (firstSelection != null) @@ -4293,11 +4326,28 @@ var seqRef = new List { selection.ActiveCell.Value }; seqRef.AddRange(xlWorksheet.SelectedRanges - .Select(range => range.RangeAddress.ToStringRelative(false))); + .Select(range => + { + if (range.RangeAddress.FirstAddress.Equals(range.RangeAddress.LastAddress)) + return range.RangeAddress.FirstAddress.ToStringRelative(false); + else + return range.RangeAddress.ToStringRelative(false); + })); selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; sheetView.Append(selection); + }; + + populateSelection(new Selection()); + // If a pane exists, we need to set the active pane too + if (pane != null) + { + populateSelection(new Selection() + { + Pane = pane.ActivePane + }); + } } if (xlWorksheet.SheetView.ZoomScale == 100) @@ -4682,7 +4732,7 @@ else { cell.CellFormula = null; - cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); + cell.DataType = xlCell.DataType == XLDataType.DateTime ? null : GetCellValueType(xlCell); } if (evaluateFormulae || field != null || !xlCell.HasFormula) @@ -4765,6 +4815,7 @@ sheetProtection.AutoFilter = OpenXmlHelper.GetBooleanValue(!protection.AutoFilter, true); sheetProtection.PivotTables = OpenXmlHelper.GetBooleanValue(!protection.PivotTables, true); sheetProtection.Sort = OpenXmlHelper.GetBooleanValue(!protection.Sort, true); + sheetProtection.Objects = OpenXmlHelper.GetBooleanValue(!protection.Objects, true); sheetProtection.SelectLockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectLockedCells, false); sheetProtection.SelectUnlockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectUnlockedCells, false); } @@ -5107,6 +5158,12 @@ pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; } + // For some reason some Excel files already contains pageSetup.Copies = 0 + // The validation fails for this + // Let's remove the attribute of that's the case. + if ((pageSetup?.Copies ?? 0) <= 0) + pageSetup.Copies = null; + #endregion PageSetup #region HeaderFooter @@ -5125,8 +5182,6 @@ if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed || ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed) { - //var headerFooter = worksheetPart.Worksheet.Elements().First(); - headerFooter.RemoveAllChildren(); headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; @@ -5154,6 +5209,9 @@ #region RowBreaks + var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; + if (rowBreakCount > 0) + { if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); @@ -5162,13 +5220,24 @@ var rowBreaks = worksheetPart.Worksheet.Elements().First(); - var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; - if (rowBreakCount > 0) + var existingBreaks = rowBreaks.ChildElements.OfType(); + var rowBreaksToDelete = existingBreaks + .Where(rb => !rb.Id.HasValue || + !xlWorksheet.PageSetup.RowBreaks.Contains((int)rb.Id.Value)) + .ToList(); + + foreach (var rb in rowBreaksToDelete) { + rowBreaks.RemoveChild(rb); + } + + var rowBreaksToAdd = xlWorksheet.PageSetup.RowBreaks + .Where(xlRb => !existingBreaks.Any(rb => rb.Id.HasValue && rb.Id.Value == xlRb)); + rowBreaks.Count = (UInt32)rowBreakCount; rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber; - foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break + foreach (var break1 in rowBreaksToAdd.Select(rb => new Break { Id = (UInt32)rb, Max = lastRowNum, @@ -5187,6 +5256,9 @@ #region ColumnBreaks + var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; + if (columnBreakCount > 0) + { if (!worksheetPart.Worksheet.Elements().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); @@ -5195,13 +5267,24 @@ var columnBreaks = worksheetPart.Worksheet.Elements().First(); - var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; - if (columnBreakCount > 0) + var existingBreaks = columnBreaks.ChildElements.OfType(); + var columnBreaksToDelete = existingBreaks + .Where(cb => !cb.Id.HasValue || + !xlWorksheet.PageSetup.ColumnBreaks.Contains((int)cb.Id.Value)) + .ToList(); + + foreach (var rb in columnBreaksToDelete) { + columnBreaks.RemoveChild(rb); + } + + var columnBreaksToAdd = xlWorksheet.PageSetup.ColumnBreaks + .Where(xlCb => !existingBreaks.Any(cb => cb.Id.HasValue && cb.Id.Value == xlCb)); + columnBreaks.Count = (UInt32)columnBreakCount; columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber; - foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break + foreach (var break1 in columnBreaksToAdd.Select(cb => new Break { Id = (UInt32)cb, Max = maxColumnNumber, @@ -5239,6 +5322,16 @@ #region Drawings + if (worksheetPart.DrawingsPart != null) + { + var xlPictures = xlWorksheet.Pictures as Drawings.XLPictures; + foreach (var removedPicture in xlPictures.Deleted) + { + worksheetPart.DrawingsPart.DeletePart(removedPicture); + } + xlPictures.Deleted.Clear(); + } + foreach (var pic in xlWorksheet.Pictures) { AddPictureAnchor(worksheetPart, pic, context); @@ -5254,6 +5347,14 @@ worksheetPart.Worksheet.InsertBefore(worksheetDrawing, tableParts); } + if (!xlWorksheet.Pictures.Any() && worksheetPart.DrawingsPart != null) + { + var id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart); + worksheetPart.Worksheet.RemoveChild(worksheetPart.Worksheet.OfType().FirstOrDefault(p => p.Id == id)); + worksheetPart.DeletePart(worksheetPart.DrawingsPart); + } + + #endregion Drawings #region LegacyDrawing @@ -5326,7 +5427,7 @@ } var dataType = xlCell.DataType; - if (dataType == XLCellValues.Text) + if (dataType == XLDataType.Text) { if (xlCell.InnerText.Length == 0) openXmlCell.CellValue = null; @@ -5349,7 +5450,7 @@ } } } - else if (dataType == XLCellValues.TimeSpan) + else if (dataType == XLDataType.TimeSpan) { var timeSpan = xlCell.GetTimeSpan(); var cellValue = new CellValue(); @@ -5357,12 +5458,21 @@ XLCell.BaseDate.Add(timeSpan).ToOADate().ToInvariantString(); openXmlCell.CellValue = cellValue; } - else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + else if (dataType == XLDataType.DateTime || dataType == XLDataType.Number) { if (!XLHelper.IsNullOrWhiteSpace(xlCell.InnerText)) { var cellValue = new CellValue(); - cellValue.Text = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); + var d = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture); + + if (xlCell.Worksheet.Workbook.Use1904DateSystem && xlCell.DataType == XLDataType.DateTime) + { + // Internally ClosedXML stores cells as standard 1900-based style + // so if a workbook is in 1904-format, we do that adjustment here and when loading. + d -= 1462; + } + + cellValue.Text = d.ToInvariantString(); openXmlCell.CellValue = cellValue; } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 23ff835..b362d68 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -182,6 +182,12 @@ if (value.Length > 31) throw new ArgumentException("Worksheet names cannot be more than 31 characters"); + if (value.StartsWith("'", StringComparison.Ordinal)) + throw new ArgumentException("Worksheet names cannot start with an apostrophe"); + + if (value.EndsWith("'", StringComparison.Ordinal)) + throw new ArgumentException("Worksheet names cannot end with an apostrophe"); + Workbook.WorksheetsInternal.Rename(_name, value); _name = value; } @@ -379,8 +385,9 @@ firstRow = tPair; lastRow = tPair; } - foreach (IXLRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - retVal.Add((XLRow)row); + using (var xlRows = Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) + foreach (IXLRow row in xlRows) + retVal.Add((XLRow)row); } return retVal; } @@ -583,8 +590,9 @@ foreach (var picture in Pictures) { var newPic = targetSheet.AddPicture(picture.ImageStream, picture.Format, picture.Name) - .WithPlacement(picture.Placement) - .WithSize(picture.Width, picture.Height); + .WithPlacement(XLPicturePlacement.FreeFloating) + .WithSize(picture.Width, picture.Height) + .WithPlacement(picture.Placement); switch (picture.Placement) { @@ -675,7 +683,7 @@ String name = sheetName.ToLower().Equals(Name.ToLower()) ? newSheetName : sheetName; - newValue.Append(String.Format("{0}!{1}", name.WrapSheetNameInQuotesIfRequired(), pair[1])); + newValue.Append(String.Format("{0}!{1}", name.EscapeSheetName(), pair[1])); } else { @@ -933,15 +941,23 @@ foreach (string rangeAddressStr in ranges.Split(',').Select(s => s.Trim())) { if (XLHelper.IsValidRangeAddress(rangeAddressStr)) - retVal.Add(Range(new XLRangeAddress(Worksheet, rangeAddressStr))); + { + using (var range = Range(new XLRangeAddress(Worksheet, rangeAddressStr))) + retVal.Add(range); + } else if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0)) - NamedRange(rangeAddressStr).Ranges.ForEach(retVal.Add); + { + using (var xlRanges = NamedRange(rangeAddressStr).Ranges) + xlRanges.ForEach(retVal.Add); + } else { - Workbook.NamedRanges.First(n => - String.Compare(n.Name, rangeAddressStr, true) == 0 - && n.Ranges.First().Worksheet == this) - .Ranges.ForEach(retVal.Add); + using (var xlRanges = Workbook.NamedRanges.First(n => + String.Compare(n.Name, rangeAddressStr, true) == 0 + && n.Ranges.First().Worksheet == this).Ranges) + { + xlRanges.ForEach(retVal.Add); + } } } return retVal; @@ -1235,11 +1251,11 @@ { for (Int32 ro = firstRow; ro <= lastRow; ro++) { - var cellModel = model.Cell(ro - modelFirstRow + 1); - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel.AsRange())).ToList()) - { - Range(ro, firstColumn, ro, lastColumn).AddConditionalFormat(cf); - } + using (var cellModel = model.Cell(ro - modelFirstRow + 1).AsRange()) + foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) + { + using (var r = Range(ro, firstColumn, ro, lastColumn)) r.AddConditionalFormat(cf); + } } } insertedRange.Dispose(); @@ -1293,12 +1309,11 @@ if (firstRow == 1) return; SuspendEvents(); - var rangeUsed = range.Worksheet.RangeUsed(true); IXLRangeAddress usedAddress; - if (rangeUsed == null) - usedAddress = range.RangeAddress; - else - usedAddress = rangeUsed.RangeAddress; + using (var rangeUsed = range.Worksheet.RangeUsed(true)) + { + usedAddress = rangeUsed == null ? range.RangeAddress : rangeUsed.RangeAddress; + } ResumeEvents(); if (firstRow < usedAddress.FirstAddress.RowNumber) firstRow = usedAddress.FirstAddress.RowNumber; @@ -1320,11 +1335,11 @@ { for (Int32 co = firstColumn; co <= lastColumn; co++) { - var cellModel = model.Cell(co - modelFirstColumn + 1); - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel.AsRange())).ToList()) - { - Range(firstRow, co, lastRow, co).AddConditionalFormat(cf); - } + using (var cellModel = model.Cell(co - modelFirstColumn + 1).AsRange()) + foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) + { + using (var r = Range(firstRow, co, lastRow, co)) r.AddConditionalFormat(cf); + } } } insertedRange.Dispose(); @@ -1351,6 +1366,7 @@ conditionalFormat.Range.Dispose(); } ResumeEvents(); + newConditionalFormats.Consolidate(); ConditionalFormats = newConditionalFormats; } @@ -1469,7 +1485,8 @@ && n.Ranges.Count == 1); if (namedRanges == null || !namedRanges.Ranges.Any()) return null; - return (XLCell)namedRanges.Ranges.First().FirstCell(); + using (var rs = namedRanges.Ranges) + return (XLCell)rs.First().FirstCell(); } internal XLCell CellFast(String cellAddressInRange) diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 1a36a5a..0ab034c 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using System; using System.Collections; using System.Collections.Generic; @@ -47,7 +48,7 @@ public bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet) { XLWorksheet w; - if (_worksheets.TryGetValue(TrimSheetName(sheetName).ToLowerInvariant(), out w)) + if (_worksheets.TryGetValue(sheetName.UnescapeSheetName().ToLowerInvariant(), out w)) { worksheet = w; return true; @@ -56,17 +57,9 @@ return false; } - internal static string TrimSheetName(string sheetName) - { - if (sheetName.StartsWith("'") && sheetName.EndsWith("'") && sheetName.Length > 2) - sheetName = sheetName.Substring(1, sheetName.Length - 2); - - return sheetName; - } - public IXLWorksheet Worksheet(String sheetName) { - sheetName = TrimSheetName(sheetName); + sheetName = sheetName.UnescapeSheetName(); XLWorksheet w; diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 4464ca9..88c1f8d 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -176,6 +176,11 @@ { return value.ToString(CultureInfo.InvariantCulture.NumberFormat); } + + public static bool Between(this int val, int from, int to) + { + return val >= from && val <= to; + } } public static class DecimalExtensions @@ -345,5 +350,33 @@ || value is double || value is decimal; } + + public static string ToInvariantString(this object value) + { + if (value is sbyte) + return ((sbyte)value).ToString(CultureInfo.InvariantCulture); + else if (value is byte) + return ((byte)value).ToString(CultureInfo.InvariantCulture); + else if (value is short) + return ((short)value).ToString(CultureInfo.InvariantCulture); + else if (value is ushort) + return ((ushort)value).ToString(CultureInfo.InvariantCulture); + else if (value is int) + return ((int)value).ToString(CultureInfo.InvariantCulture); + else if (value is uint) + return ((uint)value).ToString(CultureInfo.InvariantCulture); + else if (value is long) + return ((long)value).ToString(CultureInfo.InvariantCulture); + else if (value is ulong) + return ((ulong)value).ToString(CultureInfo.InvariantCulture); + else if (value is float) + return ((float)value).ToString(CultureInfo.InvariantCulture); + else if (value is double) + return ((double)value).ToString(CultureInfo.InvariantCulture); + else if (value is decimal) + return ((decimal)value).ToString(CultureInfo.InvariantCulture); + else + return value.ToString(); + } } } diff --git a/ClosedXML/Extensions/FormatExtensions.cs b/ClosedXML/Extensions/FormatExtensions.cs new file mode 100644 index 0000000..4a183bb --- /dev/null +++ b/ClosedXML/Extensions/FormatExtensions.cs @@ -0,0 +1,17 @@ +using ExcelNumberFormat; +using System.Globalization; + +namespace ClosedXML.Extensions +{ + internal static class FormatExtensions + { + public static string ToExcelFormat(this object o, string format) + { + var nf = new NumberFormat(format); + if (!nf.IsValid) + return format; + + return nf.Format(o, CultureInfo.InvariantCulture); + } + } +} diff --git a/ClosedXML/Extensions/StringExtensions.cs b/ClosedXML/Extensions/StringExtensions.cs index 13adc8c..60af2bd 100644 --- a/ClosedXML/Extensions/StringExtensions.cs +++ b/ClosedXML/Extensions/StringExtensions.cs @@ -5,12 +5,20 @@ { internal static class StringExtensions { - internal static String WrapSheetNameInQuotesIfRequired(this String sheetName) + internal static string EscapeSheetName(this String sheetName) { - if (sheetName.Contains(' ')) - return "'" + sheetName + "'"; - else - return sheetName; + if (sheetName.Contains("'") || + sheetName.Contains(" ")) + return string.Format("'{0}'", sheetName.Replace("'", "''")); + + return sheetName; + } + + internal static string UnescapeSheetName(this String sheetName) + { + return sheetName + .Trim('\'') + .Replace("''", "'"); } internal static String HashPassword(this String password) diff --git a/ClosedXML/Utils/ColorStringParser.cs b/ClosedXML/Utils/ColorStringParser.cs new file mode 100644 index 0000000..60203a1 --- /dev/null +++ b/ClosedXML/Utils/ColorStringParser.cs @@ -0,0 +1,22 @@ +using System.Drawing; +using System.Globalization; + +namespace ClosedXML.Utils +{ + internal static class ColorStringParser + { + public static Color ParseFromHtml(string htmlColor) + { + try + { + return ColorTranslator.FromHtml(htmlColor); + } + catch + { + // https://github.com/ClosedXML/ClosedXML/issues/675 + // When regional settings list separator is # , the standard ColorTranslator.FromHtml fails + return Color.FromArgb(int.Parse(htmlColor.Replace("#", ""), NumberStyles.AllowHexSpecifier)); + } + } + } +} diff --git a/ClosedXML/Utils/XmlEncoder.cs b/ClosedXML/Utils/XmlEncoder.cs index 4219901..3177a12 100644 --- a/ClosedXML/Utils/XmlEncoder.cs +++ b/ClosedXML/Utils/XmlEncoder.cs @@ -1,10 +1,13 @@ using System.Text; +using System.Text.RegularExpressions; using System.Xml; namespace ClosedXML.Utils { public static class XmlEncoder { + private static readonly Regex xHHHHRegex = new Regex("_(x[\\dA-F]{4})_", RegexOptions.Compiled); + /// /// Checks if a character is not allowed to the XML Spec http://www.w3.org/TR/REC-xml/#charsets /// @@ -23,20 +26,23 @@ { if (encodeStr == null) return null; - var newString = new StringBuilder(); + encodeStr = xHHHHRegex.Replace(encodeStr, "_x005F_$1_"); + + var sb = new StringBuilder(encodeStr.Length); foreach (var ch in encodeStr) { if (IsXmlChar(ch)) //this method is new in .NET 4 { - newString.Append(ch); + sb.Append(ch); } else { - newString.Append(XmlConvert.EncodeName(ch.ToString())); + sb.Append(XmlConvert.EncodeName(ch.ToString())); } } - return newString.ToString(); + + return sb.ToString(); } public static string DecodeString(string decodeStr) diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index 769afa2..e1873c1 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -542,7 +542,7 @@ .LowestValue() .HighestValue(); - ws.Cell(1,3).SetValue(-20) + ws.Cell(1, 3).SetValue(-20) .CellBelow().SetValue(40) .CellBelow().SetValue(-60) .CellBelow().SetValue(30); @@ -676,8 +676,7 @@ .CellBelow().SetValue(2) .CellBelow().SetValue(3); - ws.RangeUsed().AddConditionalFormat().StopIfTrue().WhenGreaterThan(5); - + ws.RangeUsed().AddConditionalFormat().SetStopIfTrue().WhenGreaterThan(5); ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) @@ -687,4 +686,36 @@ workbook.SaveAs(filePath); } } + + public class CFDatesOccurring : IXLExample + { + public void Create(String filePath) + { + using (var workbook = new XLWorkbook()) + { + var ws = workbook.AddWorksheet("Sheet1"); + + using (var range = ws.Range("A1:A10")) + { + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.Tomorrow) + .Fill.SetBackgroundColor(XLColor.GrannySmithApple); + + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.Yesterday) + .Fill.SetBackgroundColor(XLColor.Orange); + + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.InTheLast7Days) + .Fill.SetBackgroundColor(XLColor.Blue); + + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.ThisMonth) + .Fill.SetBackgroundColor(XLColor.Red); + } + + workbook.SaveAs(filePath); + } + } + } } diff --git a/ClosedXML_Examples/Loading/ChangingBasicTable.cs b/ClosedXML_Examples/Loading/ChangingBasicTable.cs index a10c7a4..c64d0d3 100644 --- a/ClosedXML_Examples/Loading/ChangingBasicTable.cs +++ b/ClosedXML_Examples/Loading/ChangingBasicTable.cs @@ -28,7 +28,7 @@ foreach (var cell in rngNumbers.Cells()) { string formattedString = cell.GetFormattedString(); - cell.DataType = XLCellValues.Text; + cell.DataType = XLDataType.Text; cell.Value = formattedString + " Dollars"; } diff --git a/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML_Examples/Misc/DataTypes.cs index a396328..e642a7f 100644 --- a/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML_Examples/Misc/DataTypes.cs @@ -77,6 +77,9 @@ ws.Cell(++ro, co).Value = "Double Number:"; ws.Cell(ro, co + 1).Value = 123.45d; + ws.Cell(++ro, co).Value = "Large Double Number:"; + ws.Cell(ro, co + 1).Value = 9.999E307d; + ro++; ws.Cell(++ro, co).Value = "Explicit Text:"; @@ -113,67 +116,72 @@ ws.Cell(++ro, co).Value = "Date to Text:"; ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "DateTime to Text:"; ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2, 13, 45, 22); - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "Boolean to Text:"; ws.Cell(ro, co + 1).Value = true; - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "Number to Text:"; ws.Cell(ro, co + 1).Value = 123.45; - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "TimeSpan to Text:"; ws.Cell(ro, co + 1).Value = new TimeSpan(33, 45, 22); - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "Text to Date:"; ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2).ToString(); - ws.Cell(ro, co + 1).DataType = XLCellValues.DateTime; + ws.Cell(ro, co + 1).DataType = XLDataType.DateTime; ws.Cell(++ro, co).Value = "Text to DateTime:"; ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2, 13, 45, 22).ToString(); - ws.Cell(ro, co + 1).DataType = XLCellValues.DateTime; + ws.Cell(ro, co + 1).DataType = XLDataType.DateTime; ws.Cell(++ro, co).Value = "Text to Boolean:"; ws.Cell(ro, co + 1).Value = "'" + true.ToString(); - ws.Cell(ro, co + 1).DataType = XLCellValues.Boolean; + ws.Cell(ro, co + 1).DataType = XLDataType.Boolean; ws.Cell(++ro, co).Value = "Text to Number:"; ws.Cell(ro, co + 1).Value = "'123.45"; - ws.Cell(ro, co + 1).DataType = XLCellValues.Number; + ws.Cell(ro, co + 1).DataType = XLDataType.Number; + + ws.Cell(++ro, co).Value = "Percentage Text to Number:"; + ws.Cell(ro, co + 1).Value = "'55.12%"; + ws.Cell(ro, co + 1).Style.NumberFormat.SetNumberFormatId((int)XLPredefinedFormat.Number.PercentPrecision2); + ws.Cell(ro, co + 1).DataType = XLDataType.Number; ws.Cell(++ro, co).Value = "@ format to Number:"; ws.Cell(ro, co + 1).Style.NumberFormat.Format = "@"; ws.Cell(ro, co + 1).Value = 123.45; - ws.Cell(ro, co + 1).DataType = XLCellValues.Number; + ws.Cell(ro, co + 1).DataType = XLDataType.Number; ws.Cell(++ro, co).Value = "Text to TimeSpan:"; ws.Cell(ro, co + 1).Value = "'" + new TimeSpan(33, 45, 22).ToString(); - ws.Cell(ro, co + 1).DataType = XLCellValues.TimeSpan; + ws.Cell(ro, co + 1).DataType = XLDataType.TimeSpan; ro++; ws.Cell(++ro, co).Value = "Formatted Date to Text:"; ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); ws.Cell(ro, co + 1).Style.DateFormat.Format = "yyyy-MM-dd"; - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(++ro, co).Value = "Formatted Number to Text:"; ws.Cell(ro, co + 1).Value = 12345.6789; ws.Cell(ro, co + 1).Style.NumberFormat.Format = "#,##0.00"; - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ro++; ws.Cell(++ro, co).Value = "Blank Text:"; ws.Cell(ro, co + 1).Value = 12345.6789; ws.Cell(ro, co + 1).Style.NumberFormat.Format = "#,##0.00"; - ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(ro, co + 1).DataType = XLDataType.Text; ws.Cell(ro, co + 1).Value = ""; ro++; @@ -192,17 +200,17 @@ // workbook.GetSharedStrings() ws.Cell(++ro, co) - .SetDataType(XLCellValues.Text) - .SetDataType(XLCellValues.Boolean) - .SetDataType(XLCellValues.DateTime) - .SetDataType(XLCellValues.Number) - .SetDataType(XLCellValues.TimeSpan) - .SetDataType(XLCellValues.Text) - .SetDataType(XLCellValues.TimeSpan) - .SetDataType(XLCellValues.Number) - .SetDataType(XLCellValues.DateTime) - .SetDataType(XLCellValues.Boolean) - .SetDataType(XLCellValues.Text); + .SetDataType(XLDataType.Text) + .SetDataType(XLDataType.Boolean) + .SetDataType(XLDataType.DateTime) + .SetDataType(XLDataType.Number) + .SetDataType(XLDataType.TimeSpan) + .SetDataType(XLDataType.Text) + .SetDataType(XLDataType.TimeSpan) + .SetDataType(XLDataType.Number) + .SetDataType(XLDataType.DateTime) + .SetDataType(XLDataType.Boolean) + .SetDataType(XLDataType.Text); ws.Columns(2, 3).AdjustToContents(); diff --git a/ClosedXML_Examples/Misc/FreezePanes.cs b/ClosedXML_Examples/Misc/FreezePanes.cs index 2fd9465..d831af3 100644 --- a/ClosedXML_Examples/Misc/FreezePanes.cs +++ b/ClosedXML_Examples/Misc/FreezePanes.cs @@ -1,72 +1,36 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples.Misc { public class FreezePanes : 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(); - var wsFreeze = wb.Worksheets.Add("Freeze View"); - - // Freeze rows and columns in one shot - wsFreeze.SheetView.Freeze(3, 3); + using (var wb = new XLWorkbook()) + { + // Freeze rows and columns in one shot + var ws1 = wb.AddWorksheet("Freeze1"); + ws1.Cell(5, 5).SetActive(); + ws1.SheetView.Freeze(3, 3); - // You can also be more specific on what you want to freeze - // For example: - // wsFreeze.SheetView.FreezeRows(3); - // wsFreeze.SheetView.FreezeColumns(3); + // You can also be more specific on what you want to freeze + // For example: + var ws2 = wb.AddWorksheet("FreezeRows"); + ws2.Cell(5, 5).SetActive(); + ws2.SheetView.FreezeRows(3); + var ws3 = wb.AddWorksheet("FreezeColumns"); + ws3.Cell(5, 5).SetActive(); + ws3.SheetView.FreezeColumns(3); - ////////////////////////////// - //var wsSplit = wb.Worksheets.Add("Split View"); - //wsSplit.SheetView.SplitRow = 3; - //wsSplit.SheetView.SplitColumn = 3; + var wsSplit = wb.AddWorksheet("Split View"); + wsSplit.Cell(2, 2).SetActive(); + wsSplit.SheetView.SplitRow = 3; + wsSplit.SheetView.SplitColumn = 3; - wb.SaveAs(filePath); + wb.SaveAs(filePath); + } } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/LambdaExpressions.cs b/ClosedXML_Examples/Misc/LambdaExpressions.cs index 7b5e4f3..5e2721b 100644 --- a/ClosedXML_Examples/Misc/LambdaExpressions.cs +++ b/ClosedXML_Examples/Misc/LambdaExpressions.cs @@ -32,7 +32,7 @@ // .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray var cells = rngData.Cells(); - var filtered = cells.Where(c => c.DataType == XLCellValues.Text); + var filtered = cells.Where(c => c.DataType == XLDataType.Text); var list = filtered.ToList(); foreach (var c in list) { diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index b8f6023..8f19d35 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -8,9 +8,10 @@ { private class Pastry { - public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate) + public Pastry(string name, int? code, int numberOfOrders, double quality, string month, DateTime? bakeDate) { Name = name; + Code = code; NumberOfOrders = numberOfOrders; Quality = quality; Month = month; @@ -18,33 +19,37 @@ } public string Name { get; set; } + public int? Code { get; } public int NumberOfOrders { get; set; } public double Quality { get; set; } public string Month { get; set; } - public DateTime BakeDate { get; set; } + public DateTime? BakeDate { get; set; } } public void Create(String filePath) { var pastries = new List { - new Pastry("Croissant", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), - new Pastry("Croissant", 250, 50.42, "May", new DateTime(2016, 05, 03)), - new Pastry("Croissant", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), - new Pastry("Doughnut", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), - new Pastry("Doughnut", 225, 70, "May", new DateTime(2016, 05, 24)), - new Pastry("Doughnut", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), - new Pastry("Bearclaw", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), - new Pastry("Bearclaw", 184, 33.33, "May", new DateTime(2016, 05, 20)), - new Pastry("Bearclaw", 124, 25, "Jun", new DateTime(2017, 06, 05)), - new Pastry("Danish", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), - new Pastry("Danish", 190, 60, "May", new DateTime(2017, 05, 08)), - new Pastry("Danish", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), + new Pastry("Croissant", 101, 150, 60.2, "Apr", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "May", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "Jun", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "Apr", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "May", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "Jun", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "Apr", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "May", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "Jun", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "Apr", new DateTime(2017, 04, 24)), + new Pastry("Danish", 104, 190, 60, "May", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "Jun", new DateTime(2016, 06, 21)), // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. - new Pastry("Scone", 135, 0, "Apr", new DateTime(2017, 04, 22)), - new Pastry("SconE", 122, 5.19, "May", new DateTime(2017, 05, 03)), - new Pastry("SCONE", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), + new Pastry("Scone", 105, 135, 0, "Apr", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "May", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "Jun", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, null, null), }; using (var wb = new XLWorkbook()) @@ -193,6 +198,38 @@ .AddSelectedValue(new DateTime(2017, 05, 03)); #endregion Pivot Table with filter + + #region Pivot table sorting + + ptSheet = wb.Worksheets.Add("pvtSort"); + pt = ptSheet.PivotTables.AddNew("pvtSort", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name").SetSort(XLPivotSortType.Ascending); + pt.RowLabels.Add("Month").SetSort(XLPivotSortType.Descending); + + 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 interger rows + + ptSheet = wb.Worksheets.Add("pvtInteger"); + + pt = ptSheet.PivotTables.AddNew("pvtInteger", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name"); + pt.RowLabels.Add("Code"); + pt.RowLabels.Add("BakeDate"); + + pt.ColumnLabels.Add("Month"); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + #endregion Pivot Table with filter wb.SaveAs(filePath); } diff --git a/ClosedXML_Examples/app.config b/ClosedXML_Examples/app.config index af399dd..21ac8f3 100644 --- a/ClosedXML_Examples/app.config +++ b/ClosedXML_Examples/app.config @@ -1,6 +1,6 @@ - + diff --git a/ClosedXML_Sandbox/PerformanceRunner.cs b/ClosedXML_Sandbox/PerformanceRunner.cs index ec834ce..d64e02b 100644 --- a/ClosedXML_Sandbox/PerformanceRunner.cs +++ b/ClosedXML_Sandbox/PerformanceRunner.cs @@ -91,7 +91,7 @@ } foreach (var str in strings) { - str.SetValue(row, tmpString.ToString()); + str.SetValue(row, tmpString.ToString(), null); } // Format decimals @@ -99,7 +99,7 @@ foreach (var dec in decimals) { - dec.SetValue(row, tmpDec); + dec.SetValue(row, tmpDec, null); } // Format ints @@ -107,7 +107,7 @@ foreach (var intValue in ints) { - intValue.SetValue(row, tmpInt); + intValue.SetValue(row, tmpInt, null); } // Format dates @@ -115,7 +115,7 @@ tmpDate = tmpDate.AddSeconds(rnd.Next(-10000, 100000)); foreach (var dt in dates) { - dt.SetValue(row, tmpDate); + dt.SetValue(row, tmpDate, null); } // Format timespans @@ -123,14 +123,14 @@ foreach (var ts in timeSpans) { - ts.SetValue(row, tmpTimespan); + ts.SetValue(row, tmpTimespan, null); } // Format booleans var tmpBool = (rnd.Next(0, 2) > 0); foreach (var bl in booleans) { - bl.SetValue(row, tmpBool); + bl.SetValue(row, tmpBool, null); } return row; diff --git a/ClosedXML_Sandbox/app.config b/ClosedXML_Sandbox/app.config index af399dd..21ac8f3 100644 --- a/ClosedXML_Sandbox/app.config +++ b/ClosedXML_Sandbox/app.config @@ -1,6 +1,6 @@ - + diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 333c347..2d010eb 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -125,5 +125,11 @@ { TestHelper.RunTestExample(@"ConditionalFormatting\CFStopIfTrue.xlsx"); } + + [Test] + public void CFDatesOccurring() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFDatesOccurring.xlsx"); + } } } diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 9a4356a..6b68014 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -125,5 +125,36 @@ } } } + + [Test] + [TestCase("A1:A4")] + [TestCase("A1:B4")] + [TestCase("A1:C4")] + public void AutoFilterRangeRemainsValidOnInsertColumn(string rangeAddress) + { + //Arrange + using (var ms1 = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Ids"; + ws.Cell("B1").Value = "Names"; + ws.Cell("B2").Value = "John"; + ws.Cell("B3").Value = "Hank"; + ws.Cell("B4").Value = "Dagny"; + ws.Cell("C1").Value = "Phones"; + + ws.Range("B1:B4").SetAutoFilter(true); + + //Act + var range = ws.Range(rangeAddress); + range.InsertColumnsBefore(1); + + //Assert + Assert.IsFalse(ws.AutoFilter.Range.RangeAddress.IsInvalid); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 8c95b10..2bdb25a 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -12,6 +12,507 @@ { private readonly double tolerance = 1e-10; + [Theory] + public void Abs_ReturnsItselfOnPositiveNumbers([Range(0, 10, 0.1)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ABS({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(input, actual, tolerance * 10); + } + + [Theory] + public void Abs_ReturnsTheCorrectValueOnNegativeInput([Range(-10, -0.1, 0.1)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ABS({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(-input, actual, tolerance * 10); + } + + [TestCase(-10, 3.041924001)] + [TestCase(-9, 3.030935432)] + [TestCase(-8, 3.017237659)] + [TestCase(-7, 2.999695599)] + [TestCase(-6, 2.976443976)] + [TestCase(-5, 2.944197094)] + [TestCase(-4, 2.89661399)] + [TestCase(-3, 2.819842099)] + [TestCase(-2, 2.677945045)] + [TestCase(-1, 2.35619449)] + [TestCase(0, 1.570796327)] + [TestCase(1, 0.785398163)] + [TestCase(2, 0.463647609)] + [TestCase(3, 0.321750554)] + [TestCase(4, 0.244978663)] + [TestCase(5, 0.19739556)] + [TestCase(6, 0.165148677)] + [TestCase(7, 0.141897055)] + [TestCase(8, 0.124354995)] + [TestCase(9, 0.110657221)] + [TestCase(10, 0.099668652)] + public void Acot_ReturnsCorrectValue(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ACOT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [Theory] + public void Acos_ThrowsNumberExceptionOutsideRange([Range(1.1, 3, 0.1)] double input) + { + // checking input and it's additive inverse as both are outside range. + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ACOS({0})", input.ToString(CultureInfo.InvariantCulture)))); + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ACOS({0})", (-input).ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase(-1, 3.141592654)] + [TestCase(-0.9, 2.690565842)] + [TestCase(-0.8, 2.498091545)] + [TestCase(-0.7, 2.346193823)] + [TestCase(-0.6, 2.214297436)] + [TestCase(-0.5, 2.094395102)] + [TestCase(-0.4, 1.982313173)] + [TestCase(-0.3, 1.875488981)] + [TestCase(-0.2, 1.772154248)] + [TestCase(-0.1, 1.670963748)] + [TestCase(0, 1.570796327)] + [TestCase(0.1, 1.470628906)] + [TestCase(0.2, 1.369438406)] + [TestCase(0.3, 1.266103673)] + [TestCase(0.4, 1.159279481)] + [TestCase(0.5, 1.047197551)] + [TestCase(0.6, 0.927295218)] + [TestCase(0.7, 0.79539883)] + [TestCase(0.8, 0.643501109)] + [TestCase(0.9, 0.451026812)] + [TestCase(1, 0)] + public void Acos_ReturnsCorrectValue(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ACOS({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [Theory] + public void Acosh_NumbersBelow1ThrowNumberException([Range(-1, 0.9, 0.1)] double input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ACOSH({0})", input.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase(1.2, 0.622362504)] + [TestCase(1.5, 0.96242365)] + [TestCase(1.8, 1.192910731)] + [TestCase(2.1, 1.372859144)] + [TestCase(2.4, 1.522079367)] + [TestCase(2.7, 1.650193455)] + [TestCase(3, 1.762747174)] + [TestCase(3.3, 1.863279351)] + [TestCase(3.6, 1.954207529)] + [TestCase(3.9, 2.037266466)] + [TestCase(4.2, 2.113748231)] + [TestCase(4.5, 2.184643792)] + [TestCase(4.8, 2.250731414)] + [TestCase(5.1, 2.312634419)] + [TestCase(5.4, 2.370860342)] + [TestCase(5.7, 2.425828318)] + [TestCase(6, 2.47788873)] + [TestCase(1, 0)] + public void Acosh_ReturnsCorrectValue(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ACOSH({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [TestCase(-10, -0.100335348)] + [TestCase(-9, -0.111571776)] + [TestCase(-8, -0.125657214)] + [TestCase(-7, -0.143841036)] + [TestCase(-6, -0.168236118)] + [TestCase(-5, -0.202732554)] + [TestCase(-4, -0.255412812)] + [TestCase(-3, -0.34657359)] + [TestCase(-2, -0.549306144)] + [TestCase(2, 0.549306144)] + [TestCase(3, 0.34657359)] + [TestCase(4, 0.255412812)] + [TestCase(5, 0.202732554)] + [TestCase(6, 0.168236118)] + [TestCase(7, 0.143841036)] + [TestCase(8, 0.125657214)] + [TestCase(9, 0.111571776)] + [TestCase(10, 0.100335348)] + public void Acoth_ReturnsCorrectValue(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ACOTH({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [Theory] + public void Acoth_ForPlusMinusXSmallerThan1_ThrowsNumberException([Range(-0.9, 0.9, 0.1)] double input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ACOTH({0})", input.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase("LVII", 57)] + [TestCase("mcmxii", 1912)] + [TestCase("", 0)] + [TestCase("-IV", -4)] + [TestCase(" XIV", 14)] + [TestCase("MCMLXXXIII ", 1983)] + public void Arabic_ReturnsCorrectNumber(string roman, int arabic) + { + var actual = (int)XLWorkbook.EvaluateExpr(string.Format($"ARABIC(\"{roman}\")")); + Assert.AreEqual(arabic, actual); + } + + [Test] + public void Arabic_ThrowsNumberExceptionOnMinus() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("ARABIC(\"-\")")); + } + + [TestCase("- I")] + [TestCase("roman")] + public void Arabic_ThrowsValueExceptionOnInvalidNumber(string invalidRoman) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr($"ARABIC(\"{invalidRoman}\")")); + } + + [Theory] + public void Asin_ThrowsNumberExceptionWhenAbsOfInputGreaterThan1([Range(-3, -1.1, 0.1)] double input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ASIN({0})", input.ToString(CultureInfo.InvariantCulture)))); + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ASIN({0})", (-input).ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase(-1, -1.570796327)] + [TestCase(-0.9, -1.119769515)] + [TestCase(-0.8, -0.927295218)] + [TestCase(-0.7, -0.775397497)] + [TestCase(-0.6, -0.643501109)] + [TestCase(-0.5, -0.523598776)] + [TestCase(-0.4, -0.411516846)] + [TestCase(-0.3, -0.304692654)] + [TestCase(-0.2, -0.201357921)] + [TestCase(-0.1, -0.100167421)] + [TestCase(0, 0)] + [TestCase(0.1, 0.100167421)] + [TestCase(0.2, 0.201357921)] + [TestCase(0.3, 0.304692654)] + [TestCase(0.4, 0.411516846)] + [TestCase(0.5, 0.523598776)] + [TestCase(0.6, 0.643501109)] + [TestCase(0.7, 0.775397497)] + [TestCase(0.8, 0.927295218)] + [TestCase(0.9, 1.119769515)] + [TestCase(1, 1.570796327)] + public void Asin_ReturnsCorrectResult(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ASIN({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [TestCase(0, 0)] + [TestCase(0.1, 0.0998340788992076)] + [TestCase(0.2, 0.198690110349241)] + [TestCase(0.3, 0.295673047563422)] + [TestCase(0.4, 0.390035319770715)] + [TestCase(0.5, 0.481211825059603)] + [TestCase(0.6, 0.568824898732248)] + [TestCase(0.7, 0.652666566082356)] + [TestCase(0.8, 0.732668256045411)] + [TestCase(0.9, 0.808866935652783)] + [TestCase(1, 0.881373587019543)] + [TestCase(2, 1.44363547517881)] + [TestCase(3, 1.81844645923207)] + [TestCase(4, 2.0947125472611)] + [TestCase(5, 2.31243834127275)] + public void Asinh_ReturnsCorrectResult(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ASINH({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance); + var minusActual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ASINH({0})", (-input).ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(-expectedResult, minusActual, tolerance); + } + + [TestCase(0, 0)] + [TestCase(0.1, 0.099668652491162)] + [TestCase(0.2, 0.197395559849881)] + [TestCase(0.3, 0.291456794477867)] + [TestCase(0.4, 0.380506377112365)] + [TestCase(0.5, 0.463647609000806)] + [TestCase(0.6, 0.540419500270584)] + [TestCase(0.7, 0.610725964389209)] + [TestCase(0.8, 0.674740942223553)] + [TestCase(0.9, 0.732815101786507)] + [TestCase(1, 0.785398163397448)] + [TestCase(2, 1.10714871779409)] + [TestCase(3, 1.24904577239825)] + [TestCase(4, 1.32581766366803)] + [TestCase(5, 1.37340076694502)] + public void Atan_ReturnsCorrectResult(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance); + var minusActual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN({0})", (-input).ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(-expectedResult, minusActual, tolerance); + } + + [Test] + public void Atan2_ThrowsDiv0ExceptionOn0And0() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"ATAN2(0, 0)")); + } + + [Test] + public void Atan2_ReturnsPiOn0AsSecondInputWhenFirstSmaller0([Range(-5, -0.1, 0.4)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2({0}, 0)", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(Math.PI, actual, tolerance); + } + + [Test] + public void Atan2_ReturnsHalfPiOn0AsFirstInputWhenSecondGreater0([Range(0.1, 5, 0.4)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2(0, {0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(0.5 * Math.PI, actual, tolerance); + } + + [Test] + public void Atan2_ReturnsMinusHalfPiOn0AsFirstInputWhenSecondSmaller0([Range(-5, -0.1, 0.4)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2(0, {0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(-0.5 * Math.PI, actual, tolerance); + } + + [Test] + public void Atan2_Returns0OnSecond0AndFirstGreater0([Range(0.1, 5, 0.4)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2({0}, 0)", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(0, actual, tolerance); + } + + public void Atan2_ReturnsQuarterOfPiWhenInputsAreEqualAndGreater0([Range(-5, 5, 0.3)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2(0, {0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(0.25 * Math.PI, actual, tolerance); + } + + public void Atan2_Returns3QuartersOfPiWhenFirstSmaller0AndSecondItsNegative([Range(-5, 5, 0.3)] double input) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"ATAN2(0, {0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(0.25 * Math.PI, actual, tolerance); + } + + [TestCase(1, 2, 1.10714871779409)] + [TestCase(1, 3, 1.24904577239825)] + [TestCase(2, 3, 0.98279372324733)] + [TestCase(1, 4, 1.32581766366803)] + [TestCase(3, 4, 0.92729521800161)] + [TestCase(1, 5, 1.37340076694502)] + [TestCase(2, 5, 1.19028994968253)] + [TestCase(3, 5, 1.03037682652431)] + [TestCase(4, 5, 0.89605538457134)] + [TestCase(1, 6, 1.40564764938027)] + [TestCase(5, 6, 0.87605805059819)] + [TestCase(1, 7, 1.42889927219073)] + [TestCase(2, 7, 1.29249666778979)] + [TestCase(3, 7, 1.16590454050981)] + [TestCase(4, 7, 1.05165021254837)] + [TestCase(5, 7, 0.95054684081208)] + [TestCase(6, 7, 0.86217005466723)] + public void Atan2_ReturnsCorrectResults_EqualOnAllMultiplesOfFraction(double x, double y, double expectedResult) + { + for (int i = 1; i < 5; i++) + { + var actual = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"ATAN2({0}, {1})", + (x * i).ToString(CultureInfo.InvariantCulture), + (y * i).ToString(CultureInfo.InvariantCulture))); + + Assert.AreEqual(expectedResult, actual, tolerance); + } + } + + [Theory] + public void Atanh_ThrowsNumberExceptionWhenAbsOfInput1OrGreater([Range(1, 5, 0.2)] double input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ATANH({0})", input.ToString(CultureInfo.InvariantCulture)))); + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"ATANH({0})", (-input).ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase(-0.99, -2.64665241236225)] + [TestCase(-0.9, -1.47221948958322)] + [TestCase(-0.8, -1.09861228866811)] + [TestCase(-0.6, -0.693147180559945)] + [TestCase(-0.4, -0.423648930193602)] + [TestCase(-0.2, -0.202732554054082)] + [TestCase(0, 0)] + [TestCase(0.2, 0.202732554054082)] + [TestCase(0.4, 0.423648930193602)] + [TestCase(0.6, 0.693147180559945)] + [TestCase(0.8, 1.09861228866811)] + [TestCase(-0.9, -1.47221948958322)] + [TestCase(-0.990, -2.64665241236225)] + [TestCase(-0.999, -3.8002011672502)] + public void Atanh_ReturnsCorrectResults(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"ATANH({0})", + input.ToString(CultureInfo.InvariantCulture))); + + Assert.AreEqual(expectedResult, actual, tolerance * 10); + } + + [Theory] + public void Base_ThrowsNumberExceptionOnBaseSmallerThan2([Range(-2, 1)] int theBase) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE(0, {0})", theBase.ToString(CultureInfo.InvariantCulture)))); + } + + [Theory] + public void Base_ThrowsNumberExceptionOnInputSmallerThan0([Range(-5, -1)] int input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, 2)", input.ToString(CultureInfo.InvariantCulture)))); + } + + [Theory] + public void Base_ThrowsNumberExceptionOnRadixGreaterThan36([Range(37, 40)] int radix) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"BASE(1, {0})", radix.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase("x", "2", "2")] + [TestCase("0", "x", "2")] + [TestCase("0", "2", "x")] + public void Base_ThrowsValueExceptionOnAnyInputNotANumber(string input, string theBase, string minLength) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"BASE({0}, {1}, {2})", + input, + theBase, + minLength))); + } + + [TestCase(0, 36, "0")] + [TestCase(1, 36, "1")] + [TestCase(2, 36, "2")] + [TestCase(3, 36, "3")] + [TestCase(4, 36, "4")] + [TestCase(5, 36, "5")] + [TestCase(6, 36, "6")] + [TestCase(7, 36, "7")] + [TestCase(8, 36, "8")] + [TestCase(9, 36, "9")] + [TestCase(10, 36, "A")] + [TestCase(11, 36, "B")] + [TestCase(12, 36, "C")] + [TestCase(13, 36, "D")] + [TestCase(14, 36, "E")] + [TestCase(15, 36, "F")] + [TestCase(16, 36, "G")] + [TestCase(17, 36, "H")] + [TestCase(18, 36, "I")] + [TestCase(19, 36, "J")] + [TestCase(20, 36, "K")] + [TestCase(21, 36, "L")] + [TestCase(22, 36, "M")] + [TestCase(23, 36, "N")] + [TestCase(24, 36, "O")] + [TestCase(25, 36, "P")] + [TestCase(26, 36, "Q")] + [TestCase(27, 36, "R")] + [TestCase(28, 36, "S")] + [TestCase(29, 36, "T")] + [TestCase(30, 36, "U")] + [TestCase(31, 36, "V")] + [TestCase(32, 36, "W")] + [TestCase(33, 36, "X")] + [TestCase(34, 36, "Y")] + [TestCase(35, 36, "Z")] + [TestCase(36, 36, "10")] + [TestCase(255, 29, "8N")] + [TestCase(255, 2, "11111111")] + public void Base_ReturnsCorrectResultOnInput(int input, int theBase, string expectedResult) + { + var actual = (string)XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, {1})", input, theBase)); + Assert.AreEqual(expectedResult, actual); + } + + [TestCase(255, 2, 3, "11111111")] + [TestCase(255, 2, 8, "11111111")] + [TestCase(255, 2, 10, "0011111111")] + [TestCase(10, 3, 4, "0101")] + public void Base_ReturnsCorrectResultOnInputWithMinimalLength(int input, int theBase, int minLength, string expectedResult) + { + var actual = (string)XLWorkbook.EvaluateExpr(string.Format(@"BASE({0}, {1}, {2})", input, theBase, minLength)); + Assert.AreEqual(expectedResult, actual); + } + + [Theory] + public void Combin_ThrowsNumberExceptionForAnyArgumentSmaller0([Range(-4, -1)] int smaller0) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"COMBIN({0}, {1})", + smaller0.ToString(CultureInfo.InvariantCulture), + (-smaller0).ToString(CultureInfo.InvariantCulture)))); + + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"COMBIN({0}, {1})", + (-smaller0).ToString(CultureInfo.InvariantCulture), + smaller0.ToString(CultureInfo.InvariantCulture)))); + } + + [TestCase("\"no number\"")] + [TestCase("\"\"")] + public void Combin_ThrowsNumericExceptionForAnyArgumentNotNumeric(string input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"COMBIN({0}, 1)", + input?.ToString(CultureInfo.InvariantCulture)))); + + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"COMBIN(1, {0})", + input?.ToString(CultureInfo.InvariantCulture)))); + } + + [Theory] + public void Combin_Returns1ForKis0OrKEqualsN([Range(0, 10)] int n) + { + var actual = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, 0)", n)); + Assert.AreEqual(1, actual); + + var actual2 = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, {0})", n)); + Assert.AreEqual(1, actual2); + } + + [Theory] + public void Combin_ReturnsNforKis1OrKisNminus1([Range(1, 10)] int n) + { + var actual = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, 1)", n)); + Assert.AreEqual(n, actual); + + var actual2 = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, {1})", n, n - 1)); + Assert.AreEqual(n, actual2); + } + + [TestCase(4, 2, 6)] + [TestCase(5, 2, 10)] + [TestCase(6, 2, 15)] + [TestCase(6, 3, 20)] + [TestCase(7, 2, 21)] + [TestCase(7, 3, 35)] + public void Combin_ReturnsCorrectResults(int n, int k, int expectedResult) + { + var actual = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, {1})", n, k)); + Assert.AreEqual(expectedResult, actual); + + var actual2 = XLWorkbook.EvaluateExpr(string.Format(@"COMBIN({0}, {1})", n, n - k)); + Assert.AreEqual(expectedResult, actual2); + } + [TestCase(4, 3, 20)] [TestCase(10, 3, 220)] [TestCase(0, 0, 1)] @@ -52,6 +553,64 @@ chosen.ToString(CultureInfo.InvariantCulture)))); } + [TestCase(0, 1)] + [TestCase(0.4, 0.921060994002885)] + [TestCase(0.8, 0.696706709347165)] + [TestCase(1.2, 0.362357754476674)] + [TestCase(1.6, -0.0291995223012888)] + [TestCase(2, -0.416146836547142)] + [TestCase(2.4, -0.737393715541245)] + [TestCase(2.8, -0.942222340668658)] + [TestCase(3.2, -0.998294775794753)] + [TestCase(3.6, -0.896758416334147)] + [TestCase(4, -0.653643620863612)] + [TestCase(4.4, -0.307332869978419)] + [TestCase(4.8, 0.0874989834394464)] + [TestCase(5.2, 0.468516671300377)] + [TestCase(5.6, 0.77556587851025)] + [TestCase(6, 0.960170286650366)] + [TestCase(6.4, 0.993184918758193)] + [TestCase(6.8, 0.869397490349825)] + [TestCase(7.2, 0.608351314532255)] + [TestCase(7.6, 0.251259842582256)] + [TestCase(8, -0.145500033808614)] + [TestCase(8.4, -0.519288654116686)] + public void Cos_ReturnsCorrectResult(double input, double expectedResult) + { + var actualResult = (double)XLWorkbook.EvaluateExpr(string.Format("COS({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actualResult, tolerance); + } + + [TestCase(0, 1)] + [TestCase(0.4, 1.08107237183845)] + [TestCase(0.8, 1.33743494630484)] + [TestCase(1.2, 1.81065556732437)] + [TestCase(1.6, 2.57746447119489)] + [TestCase(2, 3.76219569108363)] + [TestCase(2.4, 5.55694716696551)] + [TestCase(2.8, 8.25272841686113)] + [TestCase(3.2, 12.2866462005439)] + [TestCase(3.6, 18.3127790830626)] + [TestCase(4, 27.3082328360165)] + [TestCase(4.4, 40.7315730024356)] + [TestCase(4.8, 60.7593236328919)] + [TestCase(5.2, 90.638879219786)] + [TestCase(5.6, 135.215052644935)] + [TestCase(6, 201.715636122456)] + [TestCase(6.4, 300.923349714678)] + [TestCase(6.8, 448.924202712783)] + [TestCase(7.2, 669.715755490113)] + [TestCase(7.6, 999.098197777775)] + [TestCase(8, 1490.47916125218)] + [TestCase(8.4, 2223.53348628359)] + public void Cosh_ReturnsCorrectResult(double input, double expectedResult) + { + var actualResult = (double)XLWorkbook.EvaluateExpr(string.Format("COSH({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actualResult, tolerance); + var actualResult2 = (double)XLWorkbook.EvaluateExpr(string.Format("COSH({0})", (-input).ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actualResult2, tolerance); + } + [TestCase(1, 0.642092616)] [TestCase(2, -0.457657554)] [TestCase(3, -7.015252551)] @@ -79,6 +638,71 @@ Assert.Throws(() => XLWorkbook.EvaluateExpr("COT(0)")); } + [TestCase(-10, -1.000000004)] + [TestCase(-9, -1.00000003)] + [TestCase(-8, -1.000000225)] + [TestCase(-7, -1.000001663)] + [TestCase(-6, -1.000012289)] + [TestCase(-5, -1.000090804)] + [TestCase(-4, -1.00067115)] + [TestCase(-3, -1.004969823)] + [TestCase(-2, -1.037314721)] + [TestCase(-1, -1.313035285)] + [TestCase(1, 1.313035285)] + [TestCase(2, 1.037314721)] + [TestCase(3, 1.004969823)] + [TestCase(4, 1.00067115)] + [TestCase(5, 1.000090804)] + [TestCase(6, 1.000012289)] + [TestCase(7, 1.000001663)] + [TestCase(8, 1.000000225)] + [TestCase(9, 1.00000003)] + [TestCase(10, 1.000000004)] + public void Coth_Examples(double input, double expected) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"COTH({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual, tolerance * 10.0); + } + + [Test] + public void Cot_On0_ThrowsDivisionByZeroException() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"COTH(0)")); + } + + [TestCase(-10, 1.838163961)] + [TestCase(-9, -2.426486644)] + [TestCase(-8, -1.010756218)] + [TestCase(-7, -1.522101063)] + [TestCase(-6, 3.578899547)] + [TestCase(-5, 1.042835213)] + [TestCase(-4, 1.321348709)] + [TestCase(-3, -7.086167396)] + [TestCase(-2, -1.09975017)] + [TestCase(-1, -1.188395106)] + [TestCase(1, 1.188395106)] + [TestCase(2, 1.09975017)] + [TestCase(3, 7.086167396)] + [TestCase(4, -1.321348709)] + [TestCase(5, -1.042835213)] + [TestCase(6, -3.578899547)] + [TestCase(7, 1.522101063)] + [TestCase(8, 1.010756218)] + [TestCase(9, 2.426486644)] + [TestCase(10, -1.838163961)] + public void Csc_ReturnsCorrectValues(double input, double expected) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"CSC({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual, tolerance * 10); + } + + [Test] + public void Csc_On0_ThrowsDivisionByZeroException() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"CSC(0)")); + } + + [TestCase("FF", 16, 255)] [TestCase("111", 2, 7)] [TestCase("zap", 36, 45745)] @@ -100,12 +724,145 @@ Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); } + [TestCase(0, 0)] + [TestCase(Math.PI, 180)] + [TestCase(Math.PI * 2, 360)] + [TestCase(1, 57.2957795130823)] + [TestCase(2, 114.591559026165)] + [TestCase(3, 171.887338539247)] + [TestCase(4, 229.183118052329)] + [TestCase(5, 286.478897565412)] + [TestCase(6, 343.774677078494)] + [TestCase(7, 401.070456591576)] + [TestCase(8, 458.366236104659)] + [TestCase(9, 515.662015617741)] + [TestCase(10, 572.957795130823)] + [TestCase(Math.PI * 0.5, 90)] + [TestCase(Math.PI * 1.5, 270)] + [TestCase(Math.PI * 0.25, 45)] + [TestCase(-1, -57.2957795130823)] + public void Degrees_ReturnsCorrectResult(double input, double expected) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"DEGREES({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expected, actual, tolerance); + } + [Theory] public void Decimal_ReturnsErrorForRadiansSmaller2([Range(-5, 1)] int radix) { Assert.Throws(() => XLWorkbook.EvaluateExpr($"DECIMAL(\"0\", {radix})")); } + [TestCase(1.5, 2)] + [TestCase(3, 4)] + [TestCase(2, 2)] + [TestCase(-1, -2)] + [TestCase(0, 0)] + [TestCase(Math.PI, 4)] + public void Even_ReturnsCorrectResults(double input, int expectedResult) + { + var actual = (int)XLWorkbook.EvaluateExpr(string.Format(@"EVEN({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual); + } + + [TestCase(0, 1)] + [TestCase(1, Math.E)] + [TestCase(2, 7.38905609893065)] + [TestCase(3, 20.0855369231877)] + [TestCase(4, 54.5981500331442)] + [TestCase(5, 148.413159102577)] + [TestCase(6, 403.428793492735)] + [TestCase(7, 1096.63315842846)] + [TestCase(8, 2980.95798704173)] + [TestCase(9, 8103.08392757538)] + [TestCase(10, 22026.4657948067)] + [TestCase(11, 59874.1417151978)] + [TestCase(12, 162754.791419004)] + public void Exp_ReturnsCorrectResults(double input, double expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"EXP({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual, tolerance); + } + + [TestCase(0, 1L)] + [TestCase(1, 1L)] + [TestCase(2, 2L)] + [TestCase(3, 6L)] + [TestCase(4, 24L)] + [TestCase(5, 120L)] + [TestCase(6, 720L)] + [TestCase(7, 5040L)] + [TestCase(8, 40320L)] + [TestCase(9, 362880L)] + [TestCase(10, 3628800L)] + [TestCase(11, 39916800L)] + [TestCase(12, 479001600L)] + [TestCase(13, 6227020800L)] + [TestCase(14, 87178291200L)] + [TestCase(15, 1307674368000L)] + [TestCase(16, 20922789888000L)] + [TestCase(0.1, 1L)] + [TestCase(2.3, 2L)] + [TestCase(2.8, 2L)] + public void Fact_ReturnsCorrectResult(double input, long expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"FACT({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual); + } + + [Theory] + public void Fact_ThrowsNumberExceptionForNegativeInput([Range(-10, -1)] int input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"FACT({0})", input.ToString(CultureInfo.InvariantCulture)))); + } + + [Test] + public void Fact_ThrowsValueExceptionForNonNumericInput() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"FACT(""x"")"))); + } + + [TestCase(0, 1L)] + [TestCase(1, 1L)] + [TestCase(2, 2L)] + [TestCase(3, 3L)] + [TestCase(4, 8L)] + [TestCase(5, 15L)] + [TestCase(6, 48L)] + [TestCase(7, 105L)] + [TestCase(8, 384L)] + [TestCase(9, 945L)] + [TestCase(10, 3840L)] + [TestCase(11, 10395L)] + [TestCase(12, 46080L)] + [TestCase(13, 135135L)] + [TestCase(14, 645120)] + [TestCase(15, 2027025)] + [TestCase(16, 10321920)] + [TestCase(-1, 1L)] + [TestCase(0, 1)] + [TestCase(0.1, 1L)] + [TestCase(1.4, 1L)] + [TestCase(2.3, 2L)] + [TestCase(2.8, 2L)] + public void FactDouble_ReturnsCorrectResult(double input, long expectedResult) + { + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"FACTDOUBLE({0})", input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedResult, actual); + } + + [Theory] + public void FactDouble_ThrowsNumberExceptionForInputSmallerThanMinus1([Range(-10, -2)] int input) + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"FACTDOUBLE({0})", input.ToString(CultureInfo.InvariantCulture)))); + } + + [Test] + public void FactDouble_ThrowsValueExceptionForNonNumericInput() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr(string.Format(@"FACTDOUBLE(""x"")"))); + } + [Test] public void Floor() { @@ -144,42 +901,29 @@ [Test] // Functions have to support a period first before we can implement this - public void FloorMath() + [TestCase(24.3, 5, null, 20)] + [TestCase(6.7, null, null, 6)] + [TestCase(-8.1, 2, null, -10)] + [TestCase(5.5, 2.1, 0, 4.2)] + [TestCase(5.5, -2.1, 0, 4.2)] + [TestCase(5.5, 2.1, -1, 4.2)] + [TestCase(5.5, -2.1, -1, 4.2)] + [TestCase(-5.5, 2.1, 0, -6.3)] + [TestCase(-5.5, -2.1, 0, -6.3)] + [TestCase(-5.5, 2.1, -1, -4.2)] + [TestCase(-5.5, -2.1, -1, -4.2)] + public void FloorMath(double input, double? step, int? mode, double expectedResult) { - double actual; + string parameters = input.ToString(CultureInfo.InvariantCulture); + if (step != null) + { + parameters = parameters + ", " + step?.ToString(CultureInfo.InvariantCulture); + if (mode != null) + parameters = parameters + ", " + mode?.ToString(CultureInfo.InvariantCulture); + } - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(24.3, 5)"); - Assert.AreEqual(20, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(6.7)"); - Assert.AreEqual(6, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-8.1, 2)"); - Assert.AreEqual(-10, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, 2.1, 0)"); - Assert.AreEqual(4.2, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, -2.1, 0)"); - Assert.AreEqual(4.2, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, 2.1, -1)"); - Assert.AreEqual(4.2, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(5.5, -2.1, -1)"); - Assert.AreEqual(4.2, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, 2.1, 0)"); - Assert.AreEqual(-6.3, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, -2.1, 0)"); - Assert.AreEqual(-6.3, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, 2.1, -1)"); - Assert.AreEqual(-4.2, actual, tolerance); - - actual = (double)XLWorkbook.EvaluateExpr(@"FLOOR.MATH(-5.5, -2.1, -1)"); - Assert.AreEqual(-4.2, actual, tolerance); + var actual = (double)XLWorkbook.EvaluateExpr(string.Format(@"FLOOR.MATH({0})", parameters)); + Assert.AreEqual(expectedResult, actual, tolerance); } [Test] @@ -217,6 +961,97 @@ Assert.AreEqual(0.7, actual, tolerance); } + [TestCase(0, 1)] + [TestCase(0.3, 1.0467516)] + [TestCase(0.6, 1.21162831)] + [TestCase(0.9, 1.60872581)] + [TestCase(1.2, 2.759703601)] + [TestCase(1.5, 14.1368329)] + [TestCase(1.8, -4.401367872)] + [TestCase(2.1, -1.980801656)] + [TestCase(2.4, -1.356127641)] + [TestCase(2.7, -1.10610642)] + [TestCase(3.0, -1.010108666)] + [TestCase(3.3, -1.012678974)] + [TestCase(3.6, -1.115127532)] + [TestCase(3.9, -1.377538917)] + [TestCase(4.2, -2.039730601)] + [TestCase(4.5, -4.743927548)] + [TestCase(4.8, 11.42870421)] + [TestCase(5.1, 2.645658426)] + [TestCase(5.4, 1.575565187)] + [TestCase(5.7, 1.198016873)] + [TestCase(6.0, 1.041481927)] + [TestCase(6.3, 1.000141384)] + [TestCase(6.6, 1.052373922)] + [TestCase(6.9, 1.225903187)] + [TestCase(7.2, 1.643787029)] + [TestCase(7.5, 2.884876262)] + [TestCase(7.8, 18.53381902)] + [TestCase(8.1, -4.106031636)] + [TestCase(8.4, -1.925711244)] + [TestCase(8.7, -1.335743646)] + [TestCase(9.0, -1.097537906)] + [TestCase(9.3, -1.007835594)] + [TestCase(9.6, -1.015550252)] + [TestCase(9.9, -1.124617578)] + [TestCase(10.2, -1.400039323)] + [TestCase(10.5, -2.102886109)] + [TestCase(10.8, -5.145888341)] + [TestCase(11.1, 9.593612018)] + [TestCase(11.4, 2.541355049)] + [TestCase(45, 1.90359)] + [TestCase(30, 6.48292)] + public void Sec_ReturnsCorrectNumber(double input, double expectedOutput) + { + double result = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"SEC({0})", + input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedOutput, result, 0.00001); + + // as the secant is symmetric for positive and negative numbers, let's assert twice: + double resultForNegative = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"SEC({0})", + (-input).ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedOutput, resultForNegative, 0.00001); + } + + [Test] + public void Sec_ThrowsCellValueExceptionOnNonNumericValue() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr( + string.Format( + @"SEC(number)"))); + } + + [TestCase(-9, 0.00024682)] + [TestCase(-8, 0.000670925)] + [TestCase(-7, 0.001823762)] + [TestCase(-6, 0.004957474)] + [TestCase(-5, 0.013475282)] + [TestCase(-4, 0.036618993)] + [TestCase(-3, 0.099327927)] + [TestCase(-2, 0.265802229)] + [TestCase(-1, 0.648054274)] + [TestCase(0, 1)] + public void Sech_ReturnsCorrectNumber(double input, double expectedOutput) + { + double result = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"SECH({0})", + input.ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedOutput, result, 0.00001); + + // as the secant is symmetric for positive and negative numbers, let's assert twice: + double resultForNegative = (double)XLWorkbook.EvaluateExpr( + string.Format( + @"SECH({0})", + (-input).ToString(CultureInfo.InvariantCulture))); + Assert.AreEqual(expectedOutput, resultForNegative, 0.00001); + } + /// /// refers to Example 1 from the Excel documentation, /// @@ -293,6 +1128,54 @@ } } + [Test] + public void SumIf_ReturnsCorrectValues_WhenCalledOnFullColumn() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new { Id = "A", Value = 2}, + new { Id = "B", Value = 3}, + new { Id = "C", Value = 2}, + new { Id = "A", Value = 1}, + new { Id = "B", Value = 4} + }; + ws.Cell("A1").InsertTable(data); + var formula = "=SUMIF(A:A,\"=A\",B:B)"; + var value = ws.Evaluate(formula); + Assert.AreEqual(3, value); + } + } + + [Test] + public void SumIf_ReturnsCorrectValues_WhenFormulaBelongToSameRange() + { + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new { Id = "A", Value = 2}, + new { Id = "B", Value = 3}, + new { Id = "C", Value = 2}, + new { Id = "A", Value = 1}, + new { Id = "B", Value = 4}, + + }; + ws.Cell("A1").InsertTable(data); + ws.Cell("A7").SetValue("Sum A"); + // SUMIF formula + var formula = "=SUMIF(A:A,\"=A\",B:B)"; + ws.Cell("B7").SetFormulaA1(formula); + var value = ws.Cell("B7").Value; + Assert.AreEqual(3, value); + } + } + + /// /// refers to Example 1 to SumIf from the Excel documentation. /// As SumIfs should behave the same if called with three parameters, we can take that example here again. @@ -435,6 +1318,13 @@ Assert.AreEqual(220, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); Assert.Throws(() => ws.Evaluate("SUMPRODUCT(A1:A10, B1:B5)")); + + // Blank cells and cells with text should be treated as zeros + ws.Range("A1:A5").Clear(); + Assert.AreEqual(110, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); + + ws.Range("A1:A5").SetValue("asdf"); + Assert.AreEqual(110, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index 8511a5f..3644161 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -27,7 +27,7 @@ [Test] public void Char_Input_Too_Large() { - Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char(9797)")); } [Test] @@ -459,7 +459,8 @@ [Test] public void Text_Value() { - Object actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")"); + Object actual; + actual = XLWorkbook.EvaluateExpr(@"Text(Date(2010, 1, 1), ""yyyy-MM-dd"")"); Assert.AreEqual("2010-01-01", actual); actual = XLWorkbook.EvaluateExpr(@"Text(1469.07, ""0,000,000.00"")"); @@ -488,6 +489,18 @@ Assert.AreEqual("211x", actual); } + [TestCase(2020, 11, 1, 9, 23, 11, "m/d/yyyy h:mm:ss", "11/1/2020 9:23:11")] + [TestCase(2023, 7, 14, 2, 12, 3, "m/d/yyyy h:mm:ss", "7/14/2023 2:12:03")] + [TestCase(2025, 10, 14, 2, 48, 55, "m/d/yyyy h:mm:ss", "10/14/2025 2:48:55")] + [TestCase(2023, 2, 19, 22, 1, 38, "m/d/yyyy h:mm:ss", "2/19/2023 22:01:38")] + [TestCase(2025, 12, 19, 19, 43, 58, "m/d/yyyy h:mm:ss", "12/19/2025 19:43:58")] + [TestCase(2034, 11, 16, 1, 48, 9, "m/d/yyyy h:mm:ss", "11/16/2034 1:48:09")] + [TestCase(2018, 12, 10, 11, 22, 42, "m/d/yyyy h:mm:ss", "12/10/2018 11:22:42")] + public void Text_DateFormats(int year, int months, int days, int hour, int minutes, int seconds, string format, string expected) + { + Assert.AreEqual(expected, XLWorkbook.EvaluateExpr($@"TEXT(DATE({year}, {months}, {days}) + TIME({hour}, {minutes}, {seconds}), ""{format}"")")); + } + [Test] public void Trim_EmptyInput_Striong() { diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 161f17a..754d158 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -60,7 +60,7 @@ var doubleList = new List { 1.0 / 0.0 }; cell.Value = doubleList; - Assert.AreNotEqual(XLCellValues.Number, cell.DataType); + Assert.AreNotEqual(XLDataType.Number, cell.DataType); } [Test] @@ -71,7 +71,7 @@ var doubleList = new List { 0.0 / 0.0 }; cell.Value = doubleList; - Assert.AreNotEqual(XLCellValues.Number, cell.DataType); + Assert.AreNotEqual(XLDataType.Number, cell.DataType); } [Test] @@ -169,7 +169,7 @@ IXLCell cell = ws.Cell("A1"); cell.Value = "NaN"; - Assert.AreNotEqual(XLCellValues.Number, cell.DataType); + Assert.AreNotEqual(XLDataType.Number, cell.DataType); } [Test] @@ -179,7 +179,7 @@ IXLCell cell = ws.Cell("A1"); cell.Value = "Nan"; - Assert.AreNotEqual(XLCellValues.Number, cell.DataType); + Assert.AreNotEqual(XLDataType.Number, cell.DataType); } [Test] @@ -241,7 +241,7 @@ IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); DateTime outValue; var date = 5545454; - ws.FirstCell().SetValue(date).DataType = XLCellValues.DateTime; + ws.FirstCell().SetValue(date).DataType = XLDataType.DateTime; bool success = ws.FirstCell().TryGetValue(out outValue); Assert.IsFalse(success); } @@ -414,7 +414,7 @@ cell.Value = "Test"; Assert.AreEqual("Test", cell.Value); - Assert.AreEqual(XLCellValues.Text, cell.DataType); + Assert.AreEqual(XLDataType.Text, cell.DataType); string s = null; cell.SetValue(s); @@ -510,5 +510,71 @@ Assert.AreEqual("E8:J13", ws.Cell("J13").CurrentRegion.RangeAddress.ToString()); } } + + // https://github.com/ClosedXML/ClosedXML/issues/630 + [Test] + public void ConsiderEmptyValueAsNumericInSumFormula() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell("A1").SetValue("Empty"); + ws.Cell("A2").SetValue("Numeric"); + ws.Cell("A3").SetValue("Copy of numeric"); + + ws.Cell("B2").SetFormulaA1("=B1"); + ws.Cell("B3").SetFormulaA1("=B2"); + + ws.Cell("C2").SetFormulaA1("=SUM(C1)"); + ws.Cell("C3").SetFormulaA1("=C2"); + + object b1 = ws.Cell("B1").Value; + object b2 = ws.Cell("B2").Value; + object b3 = ws.Cell("B3").Value; + + Assert.AreEqual("", b1); + Assert.AreEqual(0, b2); + Assert.AreEqual(0, b3); + + object c1 = ws.Cell("C1").Value; + object c2 = ws.Cell("C2").Value; + object c3 = ws.Cell("C3").Value; + + Assert.AreEqual("", c1); + Assert.AreEqual(0, c2); + Assert.AreEqual(0, c3); + } + } + + [Test] + public void SetFormulaA1AffectsR1C1() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var cell = ws.Cell(1, 1); + cell.FormulaR1C1 = "R[1]C"; + + cell.FormulaA1 = "B2"; + + Assert.AreEqual("R[1]C[1]", cell.FormulaR1C1); + } + } + + [Test] + public void SetFormulaR1C1AffectsA1() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var cell = ws.Cell(1, 1); + cell.FormulaA1 = "A2"; + + cell.FormulaR1C1 = "R[1]C[1]"; + + Assert.AreEqual("B2", cell.FormulaA1); + } + } } } diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs new file mode 100644 index 0000000..222fbeb --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs @@ -0,0 +1,136 @@ +using System.Linq; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatsConsolidateTests + { + [Test] + public void ConsecutivelyRowsConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B2:C2").AddConditionalFormat()); + SetFormat1(ws.Range("B4:C4").AddConditionalFormat()); + SetFormat1(ws.Range("B3:C3").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B2:C4", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F2", format.Values.Values.First().Value); + } + + [Test] + public void ConsecutivelyColumnsConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("D2:D3").AddConditionalFormat()); + SetFormat1(ws.Range("B2:B3").AddConditionalFormat()); + SetFormat1(ws.Range("C2:C3").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B2:D3", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F2", format.Values.Values.First().Value); + } + + [Test] + public void Contains1ConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B11:D12").AddConditionalFormat()); + SetFormat1(ws.Range("C12:D12").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B11:D12", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F11", format.Values.Values.First().Value); + } + + [Test] + public void Contains2ConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B14:C14").AddConditionalFormat()); + SetFormat1(ws.Range("B14:B14").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B14:C14", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F14", format.Values.Values.First().Value); + } + + [Test] + public void SuperimposedConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B16:D18").AddConditionalFormat()); + SetFormat1(ws.Range("B18:D19").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B16:D19", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F16", format.Values.Values.First().Value); + } + + [Test] + public void DifferentRangesNoConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B7:C7").AddConditionalFormat()); + SetFormat1(ws.Range("B8:B8").AddConditionalFormat()); + SetFormat1(ws.Range("B9:C9").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(3, ws.ConditionalFormats.Count()); + } + + [Test] + public void DifferentFormatNoConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B11:D12").AddConditionalFormat()); + SetFormat2(ws.Range("C12:D12").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + } + + private static void SetFormat1(IXLConditionalFormat format) + { + format.WhenEquals("="+format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue); + } + + private static void SetFormat2(IXLConditionalFormat format) + { + format.WhenEquals(5).Fill.SetBackgroundColor(XLColor.AliceBlue); + } + } +} diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index bfb9e96..392bc75 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -251,15 +251,30 @@ [Test] public void CanDeletePictures() { - using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageAnchors.xlsx"))) - using (var wb = new XLWorkbook(stream)) + using (var ms = new MemoryStream()) { - var ws = wb.Worksheets.First(); - ws.Pictures.Delete(ws.Pictures.First()); + int originalCount; - var pictureName = ws.Pictures.First().Name; - ws.Pictures.Delete(pictureName); + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageAnchors.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + originalCount = ws.Pictures.Count; + ws.Pictures.Delete(ws.Pictures.First()); + + var pictureName = ws.Pictures.First().Name; + ws.Pictures.Delete(pictureName); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual(originalCount - 2, ws.Pictures.Count); + } } + } [Test] diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 5b1fc42..d62b9f2 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -32,7 +32,10 @@ @"Misc\AllShapes.xlsx", @"Misc\TableHeadersWithLineBreaks.xlsx", @"Misc\TableWithNameNull.xlsx", - @"Misc\DuplicateImageNames.xlsx" + @"Misc\DuplicateImageNames.xlsx", + @"Misc\InvalidPrintArea.xlsx", + @"Misc\Date1904System.xlsx", + @"Misc\LoadImageWithoutTransform2D.xlsx" }; foreach (var file in files) @@ -54,6 +57,35 @@ } [Test] + public void CanLoadDate1904SystemCorrectly() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\Date1904System.xlsx"))) + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + var c = ws.Cell("A2"); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 27, 21, 0, 0), c.GetDateTime()); + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + var c = ws.Cell("A2"); + Assert.AreEqual(XLDataType.DateTime, c.DataType); + Assert.AreEqual(new DateTime(2017, 10, 27, 21, 0, 0), c.GetDateTime()); + wb.SaveAs(ms); + } + + } + } + + [Test] public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() { // This file's workbook.xml contains: @@ -91,9 +123,40 @@ } } + [Test] + public void CanLoadOrderedPivotTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("OrderedPivotTable"); + var pt = ws.PivotTable("OrderedPivotTable"); + + Assert.AreEqual(XLPivotSortType.Ascending, pt.RowLabels.Single().SortType); + Assert.AreEqual(XLPivotSortType.Descending, pt.ColumnLabels.Single().SortType); + } + } + + [Test] + public void CanLoadPivotTableSubtotals() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PivotTableSubtotals"); + var pt = ws.PivotTable("PivotTableSubtotals"); + + var subtotals = pt.RowLabels.Get("Group").Subtotals.ToArray(); + Assert.AreEqual(3, subtotals.Length); + Assert.AreEqual(XLSubtotalFunction.Average, subtotals[0]); + Assert.AreEqual(XLSubtotalFunction.Count, subtotals[1]); + Assert.AreEqual(XLSubtotalFunction.Sum, subtotals[2]); + } + } + /// /// For non-English locales, the default style ("Normal" in English) can be - /// another piece of text (e.g. Обычный in Russian). + /// another piece of text (e.g. ??????? in Russian). /// This test ensures that the default style is correctly detected and /// no style conflicts occur on save. /// @@ -125,7 +188,7 @@ var ws = wb.Worksheets.First(); foreach (var cell in ws.CellsUsed()) { - Assert.AreEqual(XLCellValues.DateTime, cell.DataType); + Assert.AreEqual(XLDataType.DateTime, cell.DataType); } } } @@ -204,5 +267,50 @@ workbook.SaveAs(tf2.Path); } } + + /// + /// Excel escapes symbol ' in worksheet title so we have to process this correctly. + /// + [Test] + public void CanOpenWorksheetWithEscapedApostrophe() + { + string title = ""; + TestDelegate openWorkbook = () => + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\EscapedApostrophe.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + title = ws.Name; + } + }; + + Assert.DoesNotThrow(openWorkbook); + Assert.AreEqual("L'E", title); + } + + [Test] + public void CanRoundTripSheetProtectionForObjects() + { + using (var book = new XLWorkbook()) + { + var sheet = book.AddWorksheet("TestSheet"); + sheet.Protect() + .SetObjects(true) + .SetScenarios(true); + + using (var xlStream = new MemoryStream()) + { + book.SaveAs(xlStream); + + using (var persistedBook = new XLWorkbook(xlStream)) + { + var persistedSheet = persistedBook.Worksheets.Worksheet(1); + + Assert.AreEqual(sheet.Protection.Objects, persistedSheet.Protection.Objects); + } + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 3ff4c55..f514295 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -112,7 +112,7 @@ { var ws = wb.AddWorksheet("Sheet1"); ws.Cell("A1").Value = new DateTime(2016, 1, 1); - ws.Cell("A1").DataType = XLCellValues.DateTime; + ws.Cell("A1").DataType = XLDataType.DateTime; ws.Cell("A2").FormulaA1 = @"=IF(A1 = """", ""A"", ""B"")"; var actual = ws.Cell("A2").Value; diff --git a/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs b/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs index 18fe221..002df23 100644 --- a/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs +++ b/ClosedXML_Tests/Excel/PageSetup/HeaderFooterTests.cs @@ -33,5 +33,19 @@ var newHeader = ws.PageSetup.Header.Center.GetText(XLHFOccurrence.EvenPages); Assert.AreEqual("Changed header", newHeader); } + + [TestCase("")] + [TestCase("&L&C&\"Arial\"&9 19-10-2017 \n&9&\"Arial\" &P &N &R")] // https://github.com/ClosedXML/ClosedXML/issues/563 + public void CanSetHeaderFooter(string s) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + { + var header = ws.PageSetup.Header as XLHeaderFooter; + header.SetInnerText(XLHFOccurrence.AllPages, s); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index f61b84b..8ee4606 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -29,5 +29,120 @@ } } } + + [Test] + public void PivotTableOptionsSaveTest() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\PivotTables\PivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PastrySalesData"); + var table = ws.Table("PastrySalesData"); + + var range = table.DataRange; + var header = ws.Range(1, 1, 1, 3); + var dataRange = ws.Range(header.FirstCell(), range.LastCell()); + + var ptSheet = wb.Worksheets.Add("BlankPivotTable"); + var pt = ptSheet.PivotTables.AddNew("pvtOptionsTest", ptSheet.Cell(1, 1), dataRange); + + pt.ColumnHeaderCaption = "clmn header"; + pt.RowHeaderCaption = "row header"; + + pt.AutofitColumns = true; + pt.PreserveCellFormatting = false; + pt.ShowGrandTotalsColumns = true; + pt.ShowGrandTotalsRows = true; + pt.UseCustomListsForSorting = false; + pt.ShowExpandCollapseButtons = false; + pt.ShowContextualTooltips = false; + pt.DisplayCaptionsAndDropdowns = false; + pt.RepeatRowLabels = true; + pt.SaveSourceData = false; + pt.EnableShowDetails = false; + pt.ShowColumnHeaders = false; + pt.ShowRowHeaders = false; + + pt.MergeAndCenterWithLabels = true; // MergeItem + pt.RowLabelIndent = 12; // Indent + pt.FilterAreaOrder = XLFilterAreaOrder.OverThenDown; // PageOverThenDown + pt.FilterFieldsPageWrap = 14; // PageWrap + pt.ErrorValueReplacement = "error test"; // ErrorCaption + pt.EmptyCellReplacement = "empty test"; // MissingCaption + + pt.FilteredItemsInSubtotals = true; // Subtotal filtered page items + pt.AllowMultipleFilters = false; // MultipleFieldFilters + + pt.ShowPropertiesInTooltips = false; + pt.ClassicPivotTableLayout = true; + pt.ShowEmptyItemsOnRows = true; + pt.ShowEmptyItemsOnColumns = true; + pt.DisplayItemLabels = false; + pt.SortFieldsAtoZ = true; + + pt.PrintExpandCollapsedButtons = true; + pt.PrintTitles = true; + + // TODO pt.RefreshDataOnOpen = false; + pt.ItemsToRetainPerField = XLItemsToRetain.Max; + pt.EnableCellEditing = true; + pt.ShowValuesRow = true; + pt.ShowRowStripes = true; + pt.ShowColumnStripes = true; + pt.Theme = XLPivotTableTheme.PivotStyleDark13; + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + + ms.Position = 0; + + using (var wbassert = new XLWorkbook(ms)) + { + var wsassert = wbassert.Worksheet("BlankPivotTable"); + var ptassert = wsassert.PivotTable("pvtOptionsTest"); + Assert.AreNotEqual(null, ptassert, "name save failure"); + Assert.AreEqual("clmn header", ptassert.ColumnHeaderCaption, "ColumnHeaderCaption save failure"); + Assert.AreEqual("row header", ptassert.RowHeaderCaption, "RowHeaderCaption save failure"); + Assert.AreEqual(true, ptassert.MergeAndCenterWithLabels, "MergeAndCenterWithLabels save failure"); + Assert.AreEqual(12, ptassert.RowLabelIndent, "RowLabelIndent save failure"); + Assert.AreEqual(XLFilterAreaOrder.OverThenDown, ptassert.FilterAreaOrder, "FilterAreaOrder save failure"); + Assert.AreEqual(14, ptassert.FilterFieldsPageWrap, "FilterFieldsPageWrap save failure"); + Assert.AreEqual("error test", ptassert.ErrorValueReplacement, "ErrorValueReplacement save failure"); + Assert.AreEqual("empty test", ptassert.EmptyCellReplacement, "EmptyCellReplacement save failure"); + Assert.AreEqual(true, ptassert.AutofitColumns, "AutofitColumns save failure"); + Assert.AreEqual(false, ptassert.PreserveCellFormatting, "PreserveCellFormatting save failure"); + Assert.AreEqual(true, ptassert.ShowGrandTotalsRows, "ShowGrandTotalsRows save failure"); + Assert.AreEqual(true, ptassert.ShowGrandTotalsColumns, "ShowGrandTotalsColumns save failure"); + Assert.AreEqual(true, ptassert.FilteredItemsInSubtotals, "FilteredItemsInSubtotals save failure"); + Assert.AreEqual(false, ptassert.AllowMultipleFilters, "AllowMultipleFilters save failure"); + Assert.AreEqual(false, ptassert.UseCustomListsForSorting, "UseCustomListsForSorting save failure"); + Assert.AreEqual(false, ptassert.ShowExpandCollapseButtons, "ShowExpandCollapseButtons save failure"); + Assert.AreEqual(false, ptassert.ShowContextualTooltips, "ShowContextualTooltips save failure"); + Assert.AreEqual(false, ptassert.ShowPropertiesInTooltips, "ShowPropertiesInTooltips save failure"); + Assert.AreEqual(false, ptassert.DisplayCaptionsAndDropdowns, "DisplayCaptionsAndDropdowns save failure"); + Assert.AreEqual(true, ptassert.ClassicPivotTableLayout, "ClassicPivotTableLayout save failure"); + Assert.AreEqual(true, ptassert.ShowEmptyItemsOnRows, "ShowEmptyItemsOnRows save failure"); + Assert.AreEqual(true, ptassert.ShowEmptyItemsOnColumns, "ShowEmptyItemsOnColumns save failure"); + Assert.AreEqual(false, ptassert.DisplayItemLabels, "DisplayItemLabels save failure"); + Assert.AreEqual(true, ptassert.SortFieldsAtoZ, "SortFieldsAtoZ save failure"); + Assert.AreEqual(true, ptassert.PrintExpandCollapsedButtons, "PrintExpandCollapsedButtons save failure"); + Assert.AreEqual(true, ptassert.RepeatRowLabels, "RepeatRowLabels save failure"); + Assert.AreEqual(true, ptassert.PrintTitles, "PrintTitles save failure"); + Assert.AreEqual(false, ptassert.SaveSourceData, "SaveSourceData save failure"); + Assert.AreEqual(false, ptassert.EnableShowDetails, "EnableShowDetails save failure"); + // TODO Assert.AreEqual(false, ptassert.RefreshDataOnOpen, "RefreshDataOnOpen save failure"); + Assert.AreEqual(XLItemsToRetain.Max, ptassert.ItemsToRetainPerField, "ItemsToRetainPerField save failure"); + Assert.AreEqual(true, ptassert.EnableCellEditing, "EnableCellEditing save failure"); + Assert.AreEqual(XLPivotTableTheme.PivotStyleDark13, ptassert.Theme, "Theme save failure"); + Assert.AreEqual(true, ptassert.ShowValuesRow, "ShowValuesRow save failure"); + Assert.AreEqual(false, ptassert.ShowRowHeaders, "ShowRowHeaders save failure"); + Assert.AreEqual(false, ptassert.ShowColumnHeaders, "ShowColumnHeaders save failure"); + Assert.AreEqual(true, ptassert.ShowRowStripes, "ShowRowStripes save failure"); + Assert.AreEqual(true, ptassert.ShowColumnStripes, "ShowColumnStripes save failure"); + } + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs index 05f1217..45eb96c 100644 --- a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs @@ -1,4 +1,5 @@ using System.Drawing; +using System.Linq; using ClosedXML.Excel; using NUnit.Framework; @@ -53,13 +54,7 @@ IXLWorksheet ws = wb.Worksheets.Add("Sheet"); IXLRow row1 = ws.Row(1); - row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red); - row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1)); - row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC")); - row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26)); - row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen)); - row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue")); - row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3)); + FillRow(row1); ws.Cell(2, 1).Value = row1; ws.Cell(3, 1).Value = row1.Row(1, 7); @@ -81,6 +76,43 @@ Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor); + + Assert.AreEqual(3, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula)); + } + + [Test] + public void CopyingConditionalFormats() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + FillRow(ws.Row(1)); + FillRow(ws.Row(2)); + FillRow(ws.Row(3)); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + ws.Cell(5, 2).Value = ws.Row(2).Row(1, 7); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B3").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "C5:C5").Values.Any(v => v.Value.Value == "H5" && v.Value.IsFormula)); + } + + private static void FillRow(IXLRow row1) + { + row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red); + row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1)); + row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC")); + row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26)); + row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen)); + row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue")); + row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3)); + + row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue); } } } diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index fb0c98f..87e90ed 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -109,5 +109,108 @@ ws.Column(1).InsertColumnsAfter(2); Assert.AreEqual("Comment here", ws.Cell("E1").Comment.Text); } + + [Test] + [TestCase("C4:F7", "C4:F7", 2, "E4:H7")] // Coincide, shift right + [TestCase("C4:F7", "C4:F7", -2, "C4:D7")] // Coincide, shift left + [TestCase("D5:E6", "C4:F7", 2, "F5:G6")] // Inside, shift right + [TestCase("D5:E6", "C4:F7", -2, "C5:C6")] // Inside, shift left + [TestCase("B4:G7", "C4:F7", 2, "B4:I7")] // Includes, shift right + [TestCase("B4:G7", "C4:F7", -2, "B4:E7")] // Includes, shift left + [TestCase("B4:E7", "C4:F7", 2, "B4:G7")] // Intersects at left, shift right + [TestCase("B4:E7", "C4:F7", -2, "B4:C7")] // Intersects at left, shift left + [TestCase("D4:G7", "C4:F7", 2, "F4:I7")] // Intersects at right, shift right + [TestCase("D4:G7", "C4:F7", -2, "C4:E7")] // Intersects at right, shift left + [TestCase("A5:B6", "C4:F7", 2, "A5:B6")] // No intersection, at left, shift right + [TestCase("A5:B6", "C4:F7", -1, "A5:B6")] // No intersection, at left, shift left + [TestCase("H5:I6", "C4:F7", 2, "J5:K6")] // No intersection, at right, shift right + [TestCase("H5:I6", "C4:F7", -2, "F5:G6")] // No intersection, at right, shift left + [TestCase("C8:F11", "C4:F7", 2, "C8:F11")] // Different rows + [TestCase("B1:B8", "A1:C4", 1, "B1:B8")] // More rows, shift right + [TestCase("B1:B8", "A1:C4", -1, "B1:B8")] // More rows, shift left + + public void ShiftColumnsValid(string thisRangeAddress, string shiftedRangeAddress, int shiftedColumns, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); + + Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); + } + } + + + [Test] + [TestCase("B1:B4", "A1:C4", -2)] // Shift left too much + public void ShiftColumnsInvalid(string thisRangeAddress, string shiftedRangeAddress, int shiftedColumns) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedColumns(shiftedRange, shiftedColumns); + + Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + } + } + + + [Test] + [TestCase("C4:F7", "C4:F7", 2, "C6:F9")] // Coincide, shift down + [TestCase("C4:F7", "C4:F7", -2, "C4:F5")] // Coincide, shift up + [TestCase("D5:E6", "C4:F7", 2, "D7:E8")] // Inside, shift down + [TestCase("D5:E6", "C4:F7", -2, "D4:E4")] // Inside, shift up + [TestCase("C3:F8", "C4:F7", 2, "C3:F10")] // Includes, shift down + [TestCase("C3:F8", "C4:F7", -2, "C3:F6")] // Includes, shift up + [TestCase("C3:F6", "C4:F7", 2, "C3:F8")] // Intersects at top, shift down + [TestCase("C2:F6", "C4:F7", -3, "C2:F3")] // Intersects at top, shift up to the sheet boundary + [TestCase("C3:F6", "C4:F7", -2, "C3:F4")] // Intersects at top, shift up + [TestCase("C5:F8", "C4:F7", 2, "C7:F10")] // Intersects at bottom, shift down + [TestCase("C5:F8", "C4:F7", -2, "C4:F6")] // Intersects at bottom, shift up + [TestCase("C1:F3", "C4:F7", 2, "C1:F3")] // No intersection, at top, shift down + [TestCase("C1:F3", "C4:F7", -2, "C1:F3")] // No intersection, at top, shift up + [TestCase("C8:F10","C4:F7", 2, "C10:F12")] // No intersection, at bottom, shift down + [TestCase("C8:F10","C4:F7", -2, "C6:F8")] // No intersection, at bottom, shift up + [TestCase("G4:J7", "C4:F7", 2, "G4:J7")] // Different columns + [TestCase("A2:D2", "A1:C4", 1, "A2:D2")] // More columns, shift down + [TestCase("A2:D2", "A1:C4", -1, "A2:D2")] // More columns, shift up + + public void ShiftRowsValid(string thisRangeAddress, string shiftedRangeAddress, int shiftedRows, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); + + Assert.IsFalse(thisRange.RangeAddress.IsInvalid); + Assert.AreEqual(expectedRange, thisRange.RangeAddress.ToString()); + } + } + + [Test] + [TestCase("A2:C2", "A1:C4", -2)] // Shift up too much + public void ShiftRowsInvalid(string thisRangeAddress, string shiftedRangeAddress, int shiftedRows) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + var thisRange = ws.Range(thisRangeAddress) as XLRange; + var shiftedRange = ws.Range(shiftedRangeAddress) as XLRange; + + thisRange.WorksheetRangeShiftedRows(shiftedRange, shiftedRows); + + Assert.IsTrue(thisRange.RangeAddress.IsInvalid); + } + } } } diff --git a/ClosedXML_Tests/Excel/RichText/XLRichStringTests.cs b/ClosedXML_Tests/Excel/RichText/XLRichStringTests.cs index 0bef674..ab15754 100644 --- a/ClosedXML_Tests/Excel/RichText/XLRichStringTests.cs +++ b/ClosedXML_Tests/Excel/RichText/XLRichStringTests.cs @@ -18,7 +18,7 @@ IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.RichText.AddText("12"); - cell.DataType = XLCellValues.Number; + cell.DataType = XLDataType.Number; Assert.AreEqual(12.0, cell.GetDouble()); @@ -30,7 +30,7 @@ Assert.AreEqual("1234", cell.GetString()); - Assert.AreEqual(XLCellValues.Number, cell.DataType); + Assert.AreEqual(XLDataType.Number, cell.DataType); Assert.AreEqual(1234.0, cell.GetDouble()); } @@ -147,11 +147,11 @@ Assert.AreEqual(true, cell.HasRichText); - cell.DataType = XLCellValues.Text; + cell.DataType = XLDataType.Text; Assert.AreEqual(true, cell.HasRichText); - cell.DataType = XLCellValues.Number; + cell.DataType = XLDataType.Number; Assert.AreEqual(false, cell.HasRichText); diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index 9fb35f5..51dc2a0 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -1,14 +1,28 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; using System.Globalization; using System.IO; +using System.Linq; using System.Threading; +using System.Threading.Tasks; namespace ClosedXML_Tests.Excel.Saving { [TestFixture] public class SavingTests { + private string _tempFolder; + private List _tempFiles; + + [SetUp] + public void Setup() + { + _tempFolder = Path.GetTempPath(); + _tempFiles = new List(); + } + [Test] public void CanSaveEmptyFile() { @@ -45,11 +59,32 @@ } [Test] + public void CanEscape_xHHHH_Correctly() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().Value = "Reserve_TT_A_BLOCAGE_CAG_x6904_2"; + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual("Reserve_TT_A_BLOCAGE_CAG_x6904_2", ws.FirstCell().Value); + } + } + } + + [Test] public void CanSaveFileMultipleTimesAfterDeletingWorksheet() { // https://github.com/ClosedXML/ClosedXML/issues/435 - using (var ms = new MemoryStream()) { using (XLWorkbook book1 = new XLWorkbook()) @@ -72,7 +107,6 @@ } } - [Test] public void CanSaveAndValidateFileInAnotherCulture() { @@ -91,5 +125,123 @@ } } } + + [Test] + public void CanSaveAsCopyReadOnlyFile() + { + // Arrange + string id = Guid.NewGuid().ToString(); + string original = string.Format("{0}original{1}.xlsx", _tempFolder, id); + string copy = string.Format("{0}copy_of_{1}.xlsx", _tempFolder, id); + + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(original); + _tempFiles.Add(original); + } + System.IO.File.SetAttributes(original, FileAttributes.ReadOnly); + + // Act + using (var wb = new XLWorkbook(original)) + { + wb.SaveAs(copy); + _tempFiles.Add(copy); + } + + // Assert + Assert.IsTrue(System.IO.File.Exists(copy)); + Assert.IsFalse(System.IO.File.GetAttributes(copy).HasFlag(FileAttributes.ReadOnly)); + } + + [Test] + public void CanSaveAsOverwriteExistingFile() + { + // Arrange + string id = Guid.NewGuid().ToString(); + string existing = string.Format("{0}existing{1}.xlsx", _tempFolder, id); + + System.IO.File.WriteAllText(existing, ""); + _tempFiles.Add(existing); + + // Act + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(existing); + } + + // Assert + Assert.IsTrue(System.IO.File.Exists(existing)); + Assert.Greater(new System.IO.FileInfo(existing).Length, 0); + } + + + [Test] + public void CannotSaveAsOverwriteExistingReadOnlyFile() + { + // Arrange + string id = Guid.NewGuid().ToString(); + string existing = string.Format("{0}existing{1}.xlsx", _tempFolder, id); + + System.IO.File.WriteAllText(existing, ""); + _tempFiles.Add(existing); + System.IO.File.SetAttributes(existing, FileAttributes.ReadOnly); + + // Act + TestDelegate saveAs = () => + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(existing); + } + }; + + // Assert + Assert.Throws(typeof(UnauthorizedAccessException), saveAs); + } + + [Test] + public void PageBreaksDontDuplicateAtSaving() + { + // https://github.com/ClosedXML/ClosedXML/issues/666 + + using (var ms = new MemoryStream()) + { + using (var wb1 = new XLWorkbook()) + { + var ws = wb1.Worksheets.Add("Page Breaks"); + ws.PageSetup.PrintAreas.Add("A1:D5"); + ws.PageSetup.AddHorizontalPageBreak(2); + ws.PageSetup.AddVerticalPageBreak(2); + wb1.SaveAs(ms); + wb1.Save(); + } + using (var wb2 = new XLWorkbook(ms)) + { + var ws = wb2.Worksheets.First(); + + Assert.AreEqual(1, ws.PageSetup.ColumnBreaks.Count); + Assert.AreEqual(1, ws.PageSetup.RowBreaks.Count); + } + } + } + + + [TearDown] + public void DeleteTempFiles() + { + foreach (var fileName in _tempFiles) + { + try + { + System.IO.File.Delete(fileName); + } + catch + { } + } + _tempFiles.Clear(); + } } } diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs index 7847ae5..76bf394 100644 --- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs +++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs @@ -31,5 +31,21 @@ Assert.AreEqual("yy-MM-dd", ws.Cell("E1").Style.DateFormat.Format); } } + + [Test] + public void TestExcelNumberFormats() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var c = ws.FirstCell() + .SetValue(41573.875) + .SetDataType(XLDataType.DateTime); + + c.Style.NumberFormat.SetFormat("m/d/yy\\ h:mm;@"); + + Assert.AreEqual("10/26/13 21:00", c.GetFormattedString()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 33fafed..3a41da8 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -214,5 +214,82 @@ } } } + + [Test] + public void CanCopySheetsWithAllAnchorTypes() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageAnchors.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + ws.CopyTo("Copy1"); + + var ws2 = wb.Worksheets.Skip(1).First(); + ws2.CopyTo("Copy2"); + + var ws3 = wb.Worksheets.Skip(2).First(); + ws3.CopyTo("Copy3"); + + var ws4 = wb.Worksheets.Skip(3).First(); + ws3.CopyTo("Copy4"); + } + } + + [Test] + public void WorksheetNameCannotStartWithApostrophe() + { + var title = "'StartsWithApostrophe"; + TestDelegate addWorksheet = () => + { + using (var wb = new XLWorkbook()) + { + wb.Worksheets.Add(title); + } + }; + + Assert.Throws(typeof(ArgumentException), addWorksheet); + } + + [Test] + public void WorksheetNameCannotEndWithApostrophe() + { + var title = "EndsWithApostrophe'"; + TestDelegate addWorksheet = () => + { + using (var wb = new XLWorkbook()) + { + wb.Worksheets.Add(title); + } + }; + + Assert.Throws(typeof(ArgumentException), addWorksheet); + } + + [Test] + public void WorksheetNameCanContainApostrophe() + { + var title = "With'Apostrophe"; + var savedTitle = ""; + TestDelegate saveAndOpenWorkbook = () => + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + wb.Worksheets.Add(title); + wb.Worksheets.First().Cell(1, 1).FormulaA1 = $"{title}!A2"; + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + savedTitle = wb.Worksheets.First().Name; + } + } + }; + + Assert.DoesNotThrow(saveAndOpenWorkbook); + Assert.AreEqual(title, savedTitle); + } } } diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx new file mode 100644 index 0000000..6ec8da4 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx index 5b1a0db..cbc6465 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx index 9765c38..da9b21d 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index 7b3e5a2..cab157a 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 e86beba..2b49926 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/FreezePanes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/FreezePanes.xlsx index ac2714c..5110cc2 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/FreezePanes.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/FreezePanes.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx index f91eecc..2ac5d1d 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index d6f65c7..4ad1e7f 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/SelectingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx index b4e6aeb..26eeab7 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/Misc/Date1904System.xlsx b/ClosedXML_Tests/Resource/Misc/Date1904System.xlsx new file mode 100644 index 0000000..8a7da48 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/Date1904System.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx b/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx new file mode 100644 index 0000000..29d15ff --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/EscapedApostrophe.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/InvalidPrintArea.xlsx b/ClosedXML_Tests/Resource/Misc/InvalidPrintArea.xlsx new file mode 100644 index 0000000..361a7de --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/InvalidPrintArea.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LoadImageWithoutTransform2D.xlsx b/ClosedXML_Tests/Resource/Misc/LoadImageWithoutTransform2D.xlsx new file mode 100644 index 0000000..f02d8c0 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadImageWithoutTransform2D.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx index 6fad7fe..fb63015 100644 --- a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx Binary files differ diff --git a/README.md b/README.md index 89339db..89b801b 100644 --- a/README.md +++ b/README.md @@ -1,6 +1,8 @@ - # ClosedXML -[![Build status](https://ci.appveyor.com/api/projects/status/wobbmnlbukxejjgb?svg=true)](https://ci.appveyor.com/project/Pyropace/closedxml) +[![Build status](https://ci.appveyor.com/api/projects/status/wobbmnlbukxejjgb?svg=true)](https://ci.appveyor.com/project/Pyropace/ClosedXML/branch/develop/artifacts) +[![Open Source Helpers](https://www.codetriage.com/closedxml/closedxml/badges/users.svg)](https://www.codetriage.com/closedxml/closedxml) + +[💾 Download unstable CI build](https://ci.appveyor.com/project/Pyropace/ClosedXML/branch/develop/artifacts) ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). @@ -37,6 +39,7 @@ ## Developer guidelines _Full guidelines to follow later_ * Please submit pull requests that are based on the `develop` branch. + ![sample work](https://i.imgur.com/cZfW7iW.png) Your pull request will take "yourWork" branch in your repo and merge into our develop branch. * Where possible, pull requests should include unit tests that cover as many uses cases as possible. This is especially relevant when implementing Excel functions. * Install [NUnit 3.0 Test Adapter](https://github.com/nunit/docs/wiki/Adapter-Installation) if you want to run the test suite in Visual Studio. * We use 4 spaces for code indentation. This is the default in Visual Studio. Don't leave any trailing white space at the end of lines or files. To make this easier, ClosedXML has an [editorconfig](http://www.editorconfig.org) configuration file. It is recommended you install editorconfig from the Visual Studio Extension Manager. diff --git a/appveyor.yml b/appveyor.yml index 04b9da3..f8f9290 100644 --- a/appveyor.yml +++ b/appveyor.yml @@ -1,5 +1,8 @@ +version: 0.92.{build} + os: Visual Studio 2017 image: Visual Studio 2017 + environment: AppVeyor: APPVEYOR @@ -16,7 +19,6 @@ assembly_file_version: "{version}" assembly_informational_version: "{version}" -version: 0.8.{build} platform: Any CPU configuration : Release