diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs index 46e96e1..83d872a 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -1,11 +1,28 @@ using System; +using System.Collections.Generic; using System.Diagnostics; +using System.Linq; using System.Text.RegularExpressions; namespace ClosedXML.Excel.CalcEngine { internal class CalcEngineHelpers { + private static Lazy>> patternReplacements = + new Lazy>>(() => + { + var patternReplacements = new Dictionary>(); + // key: the literal string to match + // value: a tuple: first item: the search pattern, second item: the replacement + patternReplacements.Add(@"~~", new Tuple(@"~~", "~")); + patternReplacements.Add(@"~*", new Tuple(@"~\*", @"\*")); + patternReplacements.Add(@"~?", new Tuple(@"~\?", @"\?")); + patternReplacements.Add(@"?", new Tuple(@"\?", ".?")); + patternReplacements.Add(@"*", new Tuple(@"\*", ".*")); + + return patternReplacements; + }); + internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) { // safety... @@ -25,8 +42,11 @@ // convert criteria to string var cs = criteria as string; - if (!string.IsNullOrEmpty(cs)) + if (cs != null) { + if (cs == "") + return cs.Equals(value); + // if criteria is an expression (e.g. ">20"), use calc engine if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') { @@ -54,11 +74,17 @@ } // if criteria is a regular expression, use regex - if (cs.IndexOf('*') > -1) + if (cs.IndexOfAny(new[] { '*', '?' }) > -1) { - var pattern = cs.Replace(@"\", @"\\"); - pattern = pattern.Replace(".", @"\"); - pattern = pattern.Replace("*", ".*"); + var pattern = Regex.Replace( + cs, + "(" + String.Join( + "|", + patternReplacements.Value.Values.Select(t => t.Item1)) + + ")", + m => patternReplacements.Value[m.Value].Item2); + pattern = $"^{pattern}$"; + return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 801a195..dfd6c1b 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -17,7 +17,7 @@ //ce.RegisterFunction("FORMULATEXT", , Formulatext); // Returns the formula at the given reference as text //ce.RegisterFunction("GETPIVOTDATA", , Getpivotdata); // Returns data stored in a PivotTable report ce.RegisterFunction("HLOOKUP", 3, 4, Hlookup); // Looks in the top row of an array and returns the value of the indicated cell - //ce.RegisterFunction("HYPERLINK", , Hyperlink); // Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet + ce.RegisterFunction("HYPERLINK", 1, 2, Hyperlink); // Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet //ce.RegisterFunction("INDEX", , Index); // Uses an index to choose a value from a reference or array //ce.RegisterFunction("INDIRECT", , Indirect); // Returns a reference indicated by a text value //ce.RegisterFunction("LOOKUP", , Lookup); // Looks up values in a vector or array @@ -80,6 +80,13 @@ .Value; } + private static object Hyperlink(List p) + { + String address = p[0]; + String toolTip = p.Count == 2 ? p[1] : String.Empty; + return new XLHyperlink(address, toolTip); + } + private static object Vlookup(List p) { var lookup_value = p[0]; diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 12ea4e7..43fce93 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -70,6 +70,7 @@ ce.RegisterFunction("SUBTOTAL", 2, 255, Subtotal); ce.RegisterFunction("SUM", 1, int.MaxValue, Sum); ce.RegisterFunction("SUMIF", 2, 3, SumIf); + ce.RegisterFunction("SUMIFS", 3, 255, SumIfs); ce.RegisterFunction("SUMPRODUCT", 1, 30, SumProduct); ce.RegisterFunction("SUMSQ", 1, 255, SumSq); //ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1); @@ -288,9 +289,9 @@ private static object SumIf(List p) { // get parameters - var range = p[0] as IEnumerable; - var sumRange = p.Count < 3 ? range : p[2] as IEnumerable; - var criteria = p[1].Evaluate(); + 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 criteria = p[1].Evaluate(); // the criteria to evaluate // build list of values in range and sumRange var rangeValues = new List(); @@ -319,6 +320,63 @@ return tally.Sum(); } + private static object SumIfs(List p) + { + // get parameters + var sumRange = p[0] as IEnumerable; + + var sumRangeValues = new List(); + foreach (var value in sumRange) + { + sumRangeValues.Add(value); + } + + var ce = new CalcEngine(); + var tally = new Tally(); + + int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want. + + // prepare criteria-parameters: + var criteriaRanges = new Tuple>[numberOfCriteria]; + for(int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + { + var criterion = p[criteriaPair * 2 + 1].Evaluate(); + var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; + var criteriaRangeValues = new List(); + foreach (var value in criteriaRange) + { + criteriaRangeValues.Add(value); + } + + criteriaRanges[criteriaPair] = new Tuple>( + criterion, + criteriaRangeValues); + } + + for (var i = 0; i < sumRangeValues.Count; i++) + { + bool shouldUseValue = true; + + foreach(var criteriaPair in criteriaRanges) + { + if (!CalcEngineHelpers.ValueSatisfiesCriteria( + criteriaPair.Item2[i], + criteriaPair.Item1, + ce)) + { + shouldUseValue = false; + break; // we're done with the inner loop as we can't ever get true again. + } + } + + if (shouldUseValue) + tally.AddValue(sumRangeValues[i]); + } + + // done + return tally.Sum(); + } + private static object SumProduct(List p) { // all parameters should be IEnumerable diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 160af64..58baace 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -24,6 +24,7 @@ ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA); ce.RegisterFunction("COUNTBLANK", 1, CountBlank); ce.RegisterFunction("COUNTIF", 2, CountIf); + ce.RegisterFunction("COUNTIFS", 2, 144, CountIfs); //COVAR Returns covariance, the average of the products of paired deviations //CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value //DEVSQ Returns the sum of squares of deviations @@ -135,12 +136,14 @@ } return cnt; } + internal static bool IsBlank(object value) { return value == null || value is string && ((string)value).Length == 0; } + static object CountIf(List p) { CalcEngine ce = new CalcEngine(); @@ -151,23 +154,72 @@ var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { - if (!IsBlank(value)) - { - if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) - cnt++; - } + if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) + cnt++; } } return cnt; } + + private static object CountIfs(List p) + { + // get parameters + var ce = new CalcEngine(); + int count = 0; + + int numberOfCriteria = p.Count / 2; + + // prepare criteria-parameters: + var criteriaRanges = new Tuple>[numberOfCriteria]; + for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + { + var criteriaRange = p[criteriaPair * 2] as IEnumerable; + var criterion = p[(criteriaPair * 2) + 1].Evaluate(); + var criteriaRangeValues = new List(); + foreach (var value in criteriaRange) + { + criteriaRangeValues.Add(value); + } + + criteriaRanges[criteriaPair] = new Tuple>( + criterion, + criteriaRangeValues); + } + + for (var i = 0; i < criteriaRanges[0].Item2.Count; i++) + { + bool shouldUseValue = true; + + foreach (var criteriaPair in criteriaRanges) + { + if (!CalcEngineHelpers.ValueSatisfiesCriteria( + criteriaPair.Item2[i], + criteriaPair.Item1, + ce)) + { + shouldUseValue = false; + break; // we're done with the inner loop as we can't ever get true again. + } + } + + if (shouldUseValue) + count++; + } + + // done + return count; + } + static object Max(List p) { return GetTally(p, true).Max(); } + static object MaxA(List p) { return GetTally(p, false).Max(); } + static object Min(List p) { return GetTally(p, true).Min(); diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 8ff1da3..bbf58ca 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -39,7 +39,6 @@ ce.RegisterFunction("TRIM", 1, Trim); // Removes spaces from text ce.RegisterFunction("UPPER", 1, Upper); // Converts text to uppercase ce.RegisterFunction("VALUE", 1, Value); // Converts a text argument to a number - ce.RegisterFunction("HYPERLINK", 2, Hyperlink); } private static object _Char(List p) @@ -300,13 +299,6 @@ return (string)p[0]; } - private static object Hyperlink(List p) - { - String address = p[0]; - String toolTip = p.Count == 2 ? p[1] : String.Empty; - return new XLHyperlink(address, toolTip); - } - private static object Clean(List p) { var s = (string)p[0]; diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 0c335d5..fa03857 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -19,7 +19,6 @@ internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); - private static Dictionary _formatCodes; private static readonly Regex A1Regex = new Regex( @"(?<=\W)(\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1 @@ -1701,7 +1700,7 @@ var style = GetStyleForRead(); if (String.IsNullOrWhiteSpace(style.NumberFormat.Format)) { - var formatCodes = GetFormatCodes(); + var formatCodes = XLPredefinedFormat.FormatCodes; if (formatCodes.ContainsKey(style.NumberFormat.NumberFormatId)) format = formatCodes[style.NumberFormat.NumberFormatId]; } @@ -1738,8 +1737,8 @@ { var maxRows = asRange.RowCount(); var maxColumns = asRange.ColumnCount(); - using (var rng = Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns)) - rng.Clear(); + using (var rng = Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns)) + rng.Clear(); } var minRow = asRange.RangeAddress.FirstAddress.RowNumber; @@ -1883,48 +1882,6 @@ _cellValue = val; } - private static Dictionary GetFormatCodes() - { - if (_formatCodes == null) - { - var fCodes = new Dictionary - { - {0, string.Empty}, - {1, "0"}, - {2, "0.00"}, - {3, "#,##0"}, - {4, "#,##0.00"}, - {7, "$#,##0.00_);($#,##0.00)"}, - {9, "0%"}, - {10, "0.00%"}, - {11, "0.00E+00"}, - {12, "# ?/?"}, - {13, "# ??/??"}, - {14, "M/d/yyyy"}, - {15, "d-MMM-yy"}, - {16, "d-MMM"}, - {17, "MMM-yy"}, - {18, "h:mm tt"}, - {19, "h:mm:ss tt"}, - {20, "H:mm"}, - {21, "H:mm:ss"}, - {22, "M/d/yyyy H:mm"}, - {37, "#,##0 ;(#,##0)"}, - {38, "#,##0 ;[Red](#,##0)"}, - {39, "#,##0.00;(#,##0.00)"}, - {40, "#,##0.00;[Red](#,##0.00)"}, - {45, "mm:ss"}, - {46, "[h]:mm:ss"}, - {47, "mmss.0"}, - {48, "##0.0E+0"}, - {49, "@"} - }; - _formatCodes = fCodes; - } - - return _formatCodes; - } - private string GetFormulaR1C1(string value) { return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0); @@ -2249,148 +2206,148 @@ if (!A1ColumnRegex.IsMatch(rangeAddress)) { using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) - { - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) - { - if (A1RowRegex.IsMatch(rangeAddress)) - { - var rows = rangeAddress.Split(':'); - var row1String = rows[0]; - var row2String = rows[1]; - string row1; - if (row1String[0] == '$') - { - row1 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber + && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) + { + if (A1RowRegex.IsMatch(rangeAddress)) + { + var rows = rangeAddress.Split(':'); + var row1String = rows[0]; + var row2String = rows[1]; + string row1; + if (row1String[0] == '$') + { + row1 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); - string row2; - if (row2String[0] == '$') - { - row2 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); + string row2; + if (row2String[0] == '$') + { + row2 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) - : String.Format("{0}:{1}", row1, row2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); - } + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) + : String.Format("{0}:{1}", row1, row2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); @@ -2455,167 +2412,167 @@ if (!A1RowRegex.IsMatch(rangeAddress)) { using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) - { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.LastAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= - matchRange.RangeAddress.LastAddress.RowNumber) - { - if (A1ColumnRegex.IsMatch(rangeAddress)) - { - var columns = rangeAddress.Split(':'); - var column1String = columns[0]; - var column2String = columns[1]; - string column1; - if (column1String[0] == '$') - { - column1 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column1String.Substring(1)) + columnsShifted, true); - } - else - { - column1 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column1String) + - columnsShifted, true); - } + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.LastAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= + matchRange.RangeAddress.LastAddress.RowNumber) + { + if (A1ColumnRegex.IsMatch(rangeAddress)) + { + var columns = rangeAddress.Split(':'); + var column1String = columns[0]; + var column2String = columns[1]; + string column1; + if (column1String[0] == '$') + { + column1 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column1String.Substring(1)) + columnsShifted, true); + } + else + { + column1 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column1String) + + columnsShifted, true); + } - string column2; - if (column2String[0] == '$') - { - column2 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column2String.Substring(1)) + columnsShifted, true); - } - else - { - column2 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column2String) + - columnsShifted, true); - } + string column2; + if (column2String[0] == '$') + { + column2 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column2String.Substring(1)) + columnsShifted, true); + } + else + { + column2 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column2String) + + columnsShifted, true); + } - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) - : String.Format("{0}:{1}", column1, column2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.FirstAddress.ColumnNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); - } + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) + : String.Format("{0}:{1}", column1, column2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.FirstAddress.ColumnNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); @@ -2783,4 +2740,4 @@ return FindCurrentRegion(this.Worksheet.Range(grownRangeAddress)); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs index f883286..d846ce3 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs @@ -1,5 +1,5 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -10,7 +10,10 @@ String val = GetQuoted(cf.Values[1]); var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = cf.Operator.ToOpenXml(); var formula = new Formula(); @@ -20,10 +23,10 @@ formula.Text = val; conditionalFormattingRule.Append(formula); - if(cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween) + if (cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween) { var formula2 = new Formula { Text = GetQuoted(cf.Values[2]) }; - conditionalFormattingRule.Append(formula2); + conditionalFormattingRule.Append(formula2); } return conditionalFormattingRule; @@ -38,7 +41,5 @@ return value; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs index 01a4694..5e33f41 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -1,5 +1,5 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -9,17 +9,18 @@ { String val = cf.Values[1].Value; var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.ContainsText; conditionalFormattingRule.Text = val; - + var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; conditionalFormattingRule.Append(formula); return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs index 54364f7..230f0f6 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -9,7 +9,10 @@ { String val = cf.Values[1].Value; var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.EndsWith; conditionalFormattingRule.Text = val; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs index a9dac0f..fdd7442 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs @@ -1,15 +1,16 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { internal class XLCFIsBlankConverter : IXLCFConverter { - public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))=0" }; @@ -17,6 +18,5 @@ return conditionalFormattingRule; } - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs index eb65890..88ad11c 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs @@ -1,15 +1,16 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { internal class XLCFIsErrorConverter : IXLCFConverter { - public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")" }; @@ -17,6 +18,5 @@ return conditionalFormattingRule; } - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs index 753e754..134a763 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs @@ -1,15 +1,16 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { internal class XLCFNotBlankConverter : IXLCFConverter { - public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))>0" }; @@ -17,6 +18,5 @@ return conditionalFormattingRule; } - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs index 3e3c081..a0342f1 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -1,5 +1,5 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -9,7 +9,10 @@ { String val = cf.Values[1].Value; var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.NotContains; conditionalFormattingRule.Text = val; @@ -19,7 +22,5 @@ return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs index 108c244..57ccd6f 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs @@ -1,15 +1,16 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { internal class XLCFNotErrorConverter : IXLCFConverter { - public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; @@ -17,6 +18,5 @@ return conditionalFormattingRule; } - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs index 77abaf9..6442a59 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -1,5 +1,5 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -9,9 +9,12 @@ { String val = cf.Values[1].Value; var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; - conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.BeginsWith; - conditionalFormattingRule.Text = val; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.BeginsWith; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; @@ -19,7 +22,5 @@ return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs index 3ca8dbc..9a1f572 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs @@ -1,5 +1,5 @@ -using System; using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -9,13 +9,14 @@ { UInt32 val = UInt32.Parse(cf.Values[1].Value); var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Percent = cf.Percent; conditionalFormattingRule.Rank = val; conditionalFormattingRule.Bottom = cf.Bottom; return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs index f4945bc..bba890e 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs @@ -1,4 +1,5 @@ using DocumentFormat.OpenXml.Spreadsheet; +using System; namespace ClosedXML.Excel { @@ -7,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); - conditionalFormattingRule.FormatId = (uint)context.DifferentialFormats[cf.Style]; + + if (!cf.Style.Equals(XLWorkbook.DefaultStyle)) + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + return conditionalFormattingRule; } } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 8ce227b..20b3ef4 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -1,7 +1,5 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -51,6 +49,7 @@ IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); - List SharedStrings { get; set; } + IList SelectedValues { get; } + IXLPivotField AddSelectedValue(Object value); } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 9bd7ccb..acf0b1c 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -1,15 +1,17 @@ using System; using System.Collections.Generic; +using System.Diagnostics; namespace ClosedXML.Excel { + [DebuggerDisplay("{SourceName}")] public class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { SourceName = sourceName; - SharedStrings = new List(); Subtotals = new List(); + SelectedValues = new List(); } public String SourceName { get; private set; } @@ -67,6 +69,11 @@ public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } - public List SharedStrings { get; set; } + public IList SelectedValues { get; private set; } + public IXLPivotField AddSelectedValue(Object value) + { + SelectedValues.Add(value); + return this; + } } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 6e7c1f0..6832af3 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -1,18 +1,22 @@ using System; using System.Collections.Generic; +using System.Diagnostics; using System.Linq; -using System.Text; namespace ClosedXML.Excel { - internal class XLPivotTable: IXLPivotTable + [DebuggerDisplay("{Name}")] + internal class XLPivotTable : IXLPivotTable { + public Guid Guid { get; private set; } public XLPivotTable() { + this.Guid = Guid.NewGuid(); + Fields = new XLPivotFields(this); ReportFilters = new XLPivotFields(this); - ColumnLabels=new XLPivotFields(this); + ColumnLabels = new XLPivotFields(this); RowLabels = new XLPivotFields(this); Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; @@ -22,6 +26,7 @@ public IXLCell TargetCell { get; set; } public IXLRange SourceRange { get; set; } + public IEnumerable SourceRangeFieldsAvailable { get { return this.SourceRange.FirstRow().Cells().Select(c => c.GetString()); } @@ -33,12 +38,24 @@ public IXLPivotFields RowLabels { get; private set; } public IXLPivotValues Values { get; private set; } - public XLPivotTableTheme Theme { get; set; } public IXLPivotTable SetTheme(XLPivotTableTheme value) { Theme = value; return this; } - public String Name { get; set; } public IXLPivotTable SetName(String value) { Name = value; return this; } - public String Title { get; set; } public IXLPivotTable SetTitle(String value) { Title = value; return this; } - public String Description { get; set; } public IXLPivotTable SetDescription(String value) { Description = value; return this; } + public XLPivotTableTheme Theme { get; set; } + + public IXLPivotTable SetTheme(XLPivotTableTheme value) { Theme = value; return this; } + + public String Name { get; set; } + + public IXLPivotTable SetName(String value) { Name = value; return this; } + + public String Title { get; set; } + + public IXLPivotTable SetTitle(String value) { Title = value; return this; } + + public String Description { get; set; } + + public IXLPivotTable SetDescription(String value) { Description = value; return this; } public String ColumnHeaderCaption { get; set; } + public IXLPivotTable SetColumnHeaderCaption(String value) { ColumnHeaderCaption = value; @@ -46,68 +63,230 @@ } public String RowHeaderCaption { get; set; } + public IXLPivotTable SetRowHeaderCaption(String value) { RowHeaderCaption = value; return this; } - public Boolean MergeAndCenterWithLabels { get; set; } public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } - public Int32 RowLabelIndent { get; set; } public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } - public XLFilterAreaOrder FilterAreaOrder { get; set; } public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } - public Int32 FilterFieldsPageWrap { get; set; } public IXLPivotTable SetFilterFieldsPageWrap(Int32 value) { FilterFieldsPageWrap = value; return this; } - public String ErrorValueReplacement { get; set; } public IXLPivotTable SetErrorValueReplacement(String value) { ErrorValueReplacement = value; return this; } - public String EmptyCellReplacement { get; set; } public IXLPivotTable SetEmptyCellReplacement(String value) { EmptyCellReplacement = value; return this; } - public Boolean AutofitColumns { get; set; } public IXLPivotTable SetAutofitColumns() { AutofitColumns = true; return this; } public IXLPivotTable SetAutofitColumns(Boolean value) { AutofitColumns = value; return this; } - public Boolean PreserveCellFormatting { get; set; } public IXLPivotTable SetPreserveCellFormatting() { PreserveCellFormatting = true; return this; } public IXLPivotTable SetPreserveCellFormatting(Boolean value) { PreserveCellFormatting = value; return this; } + public Boolean MergeAndCenterWithLabels { get; set; } - public Boolean ShowGrandTotalsRows { get; set; } public IXLPivotTable SetShowGrandTotalsRows() { ShowGrandTotalsRows = true; return this; } public IXLPivotTable SetShowGrandTotalsRows(Boolean value) { ShowGrandTotalsRows = value; return this; } - public Boolean ShowGrandTotalsColumns { get; set; } public IXLPivotTable SetShowGrandTotalsColumns() { ShowGrandTotalsColumns = true; return this; } public IXLPivotTable SetShowGrandTotalsColumns(Boolean value) { ShowGrandTotalsColumns = value; return this; } - public Boolean FilteredItemsInSubtotals { get; set; } public IXLPivotTable SetFilteredItemsInSubtotals() { FilteredItemsInSubtotals = true; return this; } public IXLPivotTable SetFilteredItemsInSubtotals(Boolean value) { FilteredItemsInSubtotals = value; return this; } - public Boolean AllowMultipleFilters { get; set; } public IXLPivotTable SetAllowMultipleFilters() { AllowMultipleFilters = true; return this; } public IXLPivotTable SetAllowMultipleFilters(Boolean value) { AllowMultipleFilters = value; return this; } - public Boolean UseCustomListsForSorting { get; set; } public IXLPivotTable SetUseCustomListsForSorting() { UseCustomListsForSorting = true; return this; } public IXLPivotTable SetUseCustomListsForSorting(Boolean value) { UseCustomListsForSorting = value; return this; } + public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } - public Boolean ShowExpandCollapseButtons { get; set; } public IXLPivotTable SetShowExpandCollapseButtons() { ShowExpandCollapseButtons = true; return this; } public IXLPivotTable SetShowExpandCollapseButtons(Boolean value) { ShowExpandCollapseButtons = value; return this; } - public Boolean ShowContextualTooltips { get; set; } public IXLPivotTable SetShowContextualTooltips() { ShowContextualTooltips = true; return this; } public IXLPivotTable SetShowContextualTooltips(Boolean value) { ShowContextualTooltips = value; return this; } - public Boolean ShowPropertiesInTooltips { get; set; } public IXLPivotTable SetShowPropertiesInTooltips() { ShowPropertiesInTooltips = true; return this; } public IXLPivotTable SetShowPropertiesInTooltips(Boolean value) { ShowPropertiesInTooltips = value; return this; } - public Boolean DisplayCaptionsAndDropdowns { get; set; } public IXLPivotTable SetDisplayCaptionsAndDropdowns() { DisplayCaptionsAndDropdowns = true; return this; } public IXLPivotTable SetDisplayCaptionsAndDropdowns(Boolean value) { DisplayCaptionsAndDropdowns = value; return this; } - public Boolean ClassicPivotTableLayout { get; set; } public IXLPivotTable SetClassicPivotTableLayout() { ClassicPivotTableLayout = true; return this; } public IXLPivotTable SetClassicPivotTableLayout(Boolean value) { ClassicPivotTableLayout = value; return this; } - public Boolean ShowValuesRow { get; set; } public IXLPivotTable SetShowValuesRow() { ShowValuesRow = true; return this; } public IXLPivotTable SetShowValuesRow(Boolean value) { ShowValuesRow = value; return this; } - public Boolean ShowEmptyItemsOnRows { get; set; } public IXLPivotTable SetShowEmptyItemsOnRows() { ShowEmptyItemsOnRows = true; return this; } public IXLPivotTable SetShowEmptyItemsOnRows(Boolean value) { ShowEmptyItemsOnRows = value; return this; } - public Boolean ShowEmptyItemsOnColumns { get; set; } public IXLPivotTable SetShowEmptyItemsOnColumns() { ShowEmptyItemsOnColumns = true; return this; } public IXLPivotTable SetShowEmptyItemsOnColumns(Boolean value) { ShowEmptyItemsOnColumns = value; return this; } - public Boolean DisplayItemLabels { get; set; } public IXLPivotTable SetDisplayItemLabels() { DisplayItemLabels = true; return this; } public IXLPivotTable SetDisplayItemLabels(Boolean value) { DisplayItemLabels = value; return this; } - public Boolean SortFieldsAtoZ { get; set; } public IXLPivotTable SetSortFieldsAtoZ() { SortFieldsAtoZ = true; return this; } public IXLPivotTable SetSortFieldsAtoZ(Boolean value) { SortFieldsAtoZ = value; return this; } + public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } - public Boolean PrintExpandCollapsedButtons { get; set; } public IXLPivotTable SetPrintExpandCollapsedButtons() { PrintExpandCollapsedButtons = true; return this; } public IXLPivotTable SetPrintExpandCollapsedButtons(Boolean value) { PrintExpandCollapsedButtons = value; return this; } - public Boolean RepeatRowLabels { get; set; } public IXLPivotTable SetRepeatRowLabels() { RepeatRowLabels = true; return this; } public IXLPivotTable SetRepeatRowLabels(Boolean value) { RepeatRowLabels = value; return this; } - public Boolean PrintTitles { get; set; } public IXLPivotTable SetPrintTitles() { PrintTitles = true; return this; } public IXLPivotTable SetPrintTitles(Boolean value) { PrintTitles = value; return this; } + public Int32 RowLabelIndent { get; set; } - public Boolean SaveSourceData { get; set; } public IXLPivotTable SetSaveSourceData() { SaveSourceData = true; return this; } public IXLPivotTable SetSaveSourceData(Boolean value) { SaveSourceData = value; return this; } - public Boolean EnableShowDetails { get; set; } public IXLPivotTable SetEnableShowDetails() { EnableShowDetails = true; return this; } public IXLPivotTable SetEnableShowDetails(Boolean value) { EnableShowDetails = value; return this; } - public Boolean RefreshDataOnOpen { get; set; } public IXLPivotTable SetRefreshDataOnOpen() { RefreshDataOnOpen = true; return this; } public IXLPivotTable SetRefreshDataOnOpen(Boolean value) { RefreshDataOnOpen = value; return this; } - public XLItemsToRetain ItemsToRetainPerField { get; set; } public IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value) { ItemsToRetainPerField = value; return this; } - public Boolean EnableCellEditing { get; set; } public IXLPivotTable SetEnableCellEditing() { EnableCellEditing = true; return this; } public IXLPivotTable SetEnableCellEditing(Boolean value) { EnableCellEditing = value; return this; } + public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } + public XLFilterAreaOrder FilterAreaOrder { get; set; } - public Boolean ShowRowHeaders { get; set; } public IXLPivotTable SetShowRowHeaders() { ShowRowHeaders = true; return this; } public IXLPivotTable SetShowRowHeaders(Boolean value) { ShowRowHeaders = value; return this; } - public Boolean ShowColumnHeaders { get; set; } public IXLPivotTable SetShowColumnHeaders() { ShowColumnHeaders = true; return this; } public IXLPivotTable SetShowColumnHeaders(Boolean value) { ShowColumnHeaders = value; return this; } - public Boolean ShowRowStripes { get; set; } public IXLPivotTable SetShowRowStripes() { ShowRowStripes = true; return this; } public IXLPivotTable SetShowRowStripes(Boolean value) { ShowRowStripes = value; return this; } - public Boolean ShowColumnStripes { get; set; } public IXLPivotTable SetShowColumnStripes() { ShowColumnStripes = true; return this; } public IXLPivotTable SetShowColumnStripes(Boolean value) { ShowColumnStripes = value; return this; } - public XLPivotSubtotals Subtotals { get; set; } public IXLPivotTable SetSubtotals(XLPivotSubtotals value) { Subtotals = value; return this; } + public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } + + public Int32 FilterFieldsPageWrap { get; set; } + + public IXLPivotTable SetFilterFieldsPageWrap(Int32 value) { FilterFieldsPageWrap = value; return this; } + + public String ErrorValueReplacement { get; set; } + + public IXLPivotTable SetErrorValueReplacement(String value) { ErrorValueReplacement = value; return this; } + + public String EmptyCellReplacement { get; set; } + + public IXLPivotTable SetEmptyCellReplacement(String value) { EmptyCellReplacement = value; return this; } + + public Boolean AutofitColumns { get; set; } + + public IXLPivotTable SetAutofitColumns() { AutofitColumns = true; return this; } + + public IXLPivotTable SetAutofitColumns(Boolean value) { AutofitColumns = value; return this; } + + public Boolean PreserveCellFormatting { get; set; } + + public IXLPivotTable SetPreserveCellFormatting() { PreserveCellFormatting = true; return this; } + + public IXLPivotTable SetPreserveCellFormatting(Boolean value) { PreserveCellFormatting = value; return this; } + + public Boolean ShowGrandTotalsRows { get; set; } + + public IXLPivotTable SetShowGrandTotalsRows() { ShowGrandTotalsRows = true; return this; } + + public IXLPivotTable SetShowGrandTotalsRows(Boolean value) { ShowGrandTotalsRows = value; return this; } + + public Boolean ShowGrandTotalsColumns { get; set; } + + public IXLPivotTable SetShowGrandTotalsColumns() { ShowGrandTotalsColumns = true; return this; } + + public IXLPivotTable SetShowGrandTotalsColumns(Boolean value) { ShowGrandTotalsColumns = value; return this; } + + public Boolean FilteredItemsInSubtotals { get; set; } + + public IXLPivotTable SetFilteredItemsInSubtotals() { FilteredItemsInSubtotals = true; return this; } + + public IXLPivotTable SetFilteredItemsInSubtotals(Boolean value) { FilteredItemsInSubtotals = value; return this; } + + public Boolean AllowMultipleFilters { get; set; } + + public IXLPivotTable SetAllowMultipleFilters() { AllowMultipleFilters = true; return this; } + + public IXLPivotTable SetAllowMultipleFilters(Boolean value) { AllowMultipleFilters = value; return this; } + + public Boolean UseCustomListsForSorting { get; set; } + + public IXLPivotTable SetUseCustomListsForSorting() { UseCustomListsForSorting = true; return this; } + + public IXLPivotTable SetUseCustomListsForSorting(Boolean value) { UseCustomListsForSorting = value; return this; } + + public Boolean ShowExpandCollapseButtons { get; set; } + + public IXLPivotTable SetShowExpandCollapseButtons() { ShowExpandCollapseButtons = true; return this; } + + public IXLPivotTable SetShowExpandCollapseButtons(Boolean value) { ShowExpandCollapseButtons = value; return this; } + + public Boolean ShowContextualTooltips { get; set; } + + public IXLPivotTable SetShowContextualTooltips() { ShowContextualTooltips = true; return this; } + + public IXLPivotTable SetShowContextualTooltips(Boolean value) { ShowContextualTooltips = value; return this; } + + public Boolean ShowPropertiesInTooltips { get; set; } + + public IXLPivotTable SetShowPropertiesInTooltips() { ShowPropertiesInTooltips = true; return this; } + + public IXLPivotTable SetShowPropertiesInTooltips(Boolean value) { ShowPropertiesInTooltips = value; return this; } + + public Boolean DisplayCaptionsAndDropdowns { get; set; } + + public IXLPivotTable SetDisplayCaptionsAndDropdowns() { DisplayCaptionsAndDropdowns = true; return this; } + + public IXLPivotTable SetDisplayCaptionsAndDropdowns(Boolean value) { DisplayCaptionsAndDropdowns = value; return this; } + + public Boolean ClassicPivotTableLayout { get; set; } + + public IXLPivotTable SetClassicPivotTableLayout() { ClassicPivotTableLayout = true; return this; } + + public IXLPivotTable SetClassicPivotTableLayout(Boolean value) { ClassicPivotTableLayout = value; return this; } + + public Boolean ShowValuesRow { get; set; } + + public IXLPivotTable SetShowValuesRow() { ShowValuesRow = true; return this; } + + public IXLPivotTable SetShowValuesRow(Boolean value) { ShowValuesRow = value; return this; } + + public Boolean ShowEmptyItemsOnRows { get; set; } + + public IXLPivotTable SetShowEmptyItemsOnRows() { ShowEmptyItemsOnRows = true; return this; } + + public IXLPivotTable SetShowEmptyItemsOnRows(Boolean value) { ShowEmptyItemsOnRows = value; return this; } + + public Boolean ShowEmptyItemsOnColumns { get; set; } + + public IXLPivotTable SetShowEmptyItemsOnColumns() { ShowEmptyItemsOnColumns = true; return this; } + + public IXLPivotTable SetShowEmptyItemsOnColumns(Boolean value) { ShowEmptyItemsOnColumns = value; return this; } + + public Boolean DisplayItemLabels { get; set; } + + public IXLPivotTable SetDisplayItemLabels() { DisplayItemLabels = true; return this; } + + public IXLPivotTable SetDisplayItemLabels(Boolean value) { DisplayItemLabels = value; return this; } + + public Boolean SortFieldsAtoZ { get; set; } + + public IXLPivotTable SetSortFieldsAtoZ() { SortFieldsAtoZ = true; return this; } + + public IXLPivotTable SetSortFieldsAtoZ(Boolean value) { SortFieldsAtoZ = value; return this; } + + public Boolean PrintExpandCollapsedButtons { get; set; } + + public IXLPivotTable SetPrintExpandCollapsedButtons() { PrintExpandCollapsedButtons = true; return this; } + + public IXLPivotTable SetPrintExpandCollapsedButtons(Boolean value) { PrintExpandCollapsedButtons = value; return this; } + + public Boolean RepeatRowLabels { get; set; } + + public IXLPivotTable SetRepeatRowLabels() { RepeatRowLabels = true; return this; } + + public IXLPivotTable SetRepeatRowLabels(Boolean value) { RepeatRowLabels = value; return this; } + + public Boolean PrintTitles { get; set; } + + public IXLPivotTable SetPrintTitles() { PrintTitles = true; return this; } + + public IXLPivotTable SetPrintTitles(Boolean value) { PrintTitles = value; return this; } + + public Boolean SaveSourceData { get; set; } + + public IXLPivotTable SetSaveSourceData() { SaveSourceData = true; return this; } + + public IXLPivotTable SetSaveSourceData(Boolean value) { SaveSourceData = value; return this; } + + public Boolean EnableShowDetails { get; set; } + + public IXLPivotTable SetEnableShowDetails() { EnableShowDetails = true; return this; } + + public IXLPivotTable SetEnableShowDetails(Boolean value) { EnableShowDetails = value; return this; } + + public Boolean RefreshDataOnOpen { get; set; } + + public IXLPivotTable SetRefreshDataOnOpen() { RefreshDataOnOpen = true; return this; } + + public IXLPivotTable SetRefreshDataOnOpen(Boolean value) { RefreshDataOnOpen = value; return this; } + + public XLItemsToRetain ItemsToRetainPerField { get; set; } + + public IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value) { ItemsToRetainPerField = value; return this; } + + public Boolean EnableCellEditing { get; set; } + + public IXLPivotTable SetEnableCellEditing() { EnableCellEditing = true; return this; } + + public IXLPivotTable SetEnableCellEditing(Boolean value) { EnableCellEditing = value; return this; } + + public Boolean ShowRowHeaders { get; set; } + + public IXLPivotTable SetShowRowHeaders() { ShowRowHeaders = true; return this; } + + public IXLPivotTable SetShowRowHeaders(Boolean value) { ShowRowHeaders = value; return this; } + + public Boolean ShowColumnHeaders { get; set; } + + public IXLPivotTable SetShowColumnHeaders() { ShowColumnHeaders = true; return this; } + + public IXLPivotTable SetShowColumnHeaders(Boolean value) { ShowColumnHeaders = value; return this; } + + public Boolean ShowRowStripes { get; set; } + + public IXLPivotTable SetShowRowStripes() { ShowRowStripes = true; return this; } + + public IXLPivotTable SetShowRowStripes(Boolean value) { ShowRowStripes = value; return this; } + + public Boolean ShowColumnStripes { get; set; } + + public IXLPivotTable SetShowColumnStripes() { ShowColumnStripes = true; return this; } + + public IXLPivotTable SetShowColumnStripes(Boolean value) { ShowColumnStripes = value; return this; } + + public XLPivotSubtotals Subtotals { get; set; } + + public IXLPivotTable SetSubtotals(XLPivotSubtotals value) { Subtotals = value; return this; } public XLPivotLayout Layout { - set { Fields.ForEach(f=>f.SetLayout(value)); } + set { Fields.ForEach(f => f.SetLayout(value)); } } public IXLPivotTable SetLayout(XLPivotLayout value) { Layout = value; return this; } public Boolean InsertBlankLines { - set { Fields.ForEach(f=>f.SetInsertBlankLines(value)); } + set { Fields.ForEach(f => f.SetInsertBlankLines(value)); } } - public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } + + public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } internal String RelId { get; set; } internal String CacheDefinitionRelId { get; set; } @@ -130,6 +309,5 @@ ShowColumnHeaders = true; ShowRowHeaders = true; } - } } diff --git a/ClosedXML/Excel/Style/IXLFill.cs b/ClosedXML/Excel/Style/IXLFill.cs index b39356e..fdc6359 100644 --- a/ClosedXML/Excel/Style/IXLFill.cs +++ b/ClosedXML/Excel/Style/IXLFill.cs @@ -25,17 +25,16 @@ Solid } - public interface IXLFill:IEquatable + public interface IXLFill : IEquatable { XLColor BackgroundColor { get; set; } XLColor PatternColor { get; set; } - XLColor PatternBackgroundColor { get; set; } XLFillPatternValues PatternType { get; set; } IXLStyle SetBackgroundColor(XLColor value); - IXLStyle SetPatternColor(XLColor value); - IXLStyle SetPatternBackgroundColor(XLColor value); - IXLStyle SetPatternType(XLFillPatternValues value); + IXLStyle SetPatternColor(XLColor value); + + IXLStyle SetPatternType(XLFillPatternValues value); } } diff --git a/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/Excel/Style/XLFill.cs index c4e02b9..d7155af 100644 --- a/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/Excel/Style/XLFill.cs @@ -1,5 +1,5 @@ using System; -using System.Text; +using System.Linq; namespace ClosedXML.Excel { @@ -11,12 +11,12 @@ { return _patternType == other.PatternType + && _backgroundColor.Equals(other.BackgroundColor) && _patternColor.Equals(other.PatternColor) - && _patternBackgroundColor.Equals(other.PatternBackgroundColor) ; } - #endregion + #endregion IXLFill Members private void SetStyleChanged() { @@ -37,13 +37,13 @@ #region Properties - private XLColor _patternBackgroundColor; + private XLColor _backgroundColor; private XLColor _patternColor; private XLFillPatternValues _patternType; public XLColor BackgroundColor { - get { return _patternColor; } + get { return _backgroundColor; } set { SetStyleChanged(); @@ -51,18 +51,20 @@ _container.Styles.ForEach(s => s.Fill.BackgroundColor = value); else { - _patternType = value.HasValue ? XLFillPatternValues.Solid : XLFillPatternValues.None; - _patternColor = value; - _patternBackgroundColor = value; - - PatternTypeModified = true; - PatternColorModified = true; - PatternBackgroundColorModified = true; + // 4 ways of determining an "empty" color + if (new XLFillPatternValues[] { XLFillPatternValues.None, XLFillPatternValues.Solid }.Contains(_patternType) + && (_backgroundColor == null + || !_backgroundColor.HasValue + || _backgroundColor == XLColor.NoColor + || _backgroundColor.ColorType == XLColorType.Indexed && _backgroundColor.Indexed == 64)) + { + _patternType = value.HasValue ? XLFillPatternValues.Solid : XLFillPatternValues.None; + } + _backgroundColor = value; } } } - public Boolean PatternColorModified; public XLColor PatternColor { get { return _patternColor; } @@ -74,29 +76,10 @@ else { _patternColor = value; - PatternColorModified = true; } } } - public Boolean PatternBackgroundColorModified; - public XLColor PatternBackgroundColor - { - get { return _patternBackgroundColor; } - set - { - SetStyleChanged(); - if (_container != null && !_container.UpdatingStyle) - _container.Styles.ForEach(s => s.Fill.PatternBackgroundColor = value); - else - { - _patternBackgroundColor = value; - PatternBackgroundColorModified = true; - } - } - } - - public Boolean PatternTypeModified; public XLFillPatternValues PatternType { get { return _patternType; } @@ -108,7 +91,6 @@ else { _patternType = value; - PatternTypeModified = true; } } } @@ -125,19 +107,13 @@ return _container.Style; } - public IXLStyle SetPatternBackgroundColor(XLColor value) - { - PatternBackgroundColor = value; - return _container.Style; - } - public IXLStyle SetPatternType(XLFillPatternValues value) { PatternType = value; return _container.Style; } - #endregion + #endregion Properties #region Constructors @@ -152,33 +128,34 @@ _container = container; if (defaultFill == null) return; _patternType = defaultFill.PatternType; + _backgroundColor = defaultFill.BackgroundColor; _patternColor = defaultFill.PatternColor; - _patternBackgroundColor = defaultFill.PatternBackgroundColor; if (useDefaultModify) { var d = defaultFill as XLFill; - PatternBackgroundColorModified = d.PatternBackgroundColorModified; - PatternColorModified = d.PatternColorModified; - PatternTypeModified = d.PatternTypeModified; } } - #endregion + #endregion Constructors #region Overridden public override string ToString() { - var sb = new StringBuilder(); - sb.Append(BackgroundColor); - sb.Append("-"); - sb.Append(PatternType.ToString()); - sb.Append("-"); - sb.Append(PatternColor); - return sb.ToString(); + switch (PatternType) + { + case XLFillPatternValues.None: + return "None"; + + case XLFillPatternValues.Solid: + return string.Concat("Solid ", BackgroundColor.ToString()); + + default: + return string.Concat(PatternType.ToString(), " pattern: ", PatternColor.ToString(), " on ", BackgroundColor.ToString()); + } } - #endregion + #endregion Overridden } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Style/XLPredefinedFormat.cs b/ClosedXML/Excel/Style/XLPredefinedFormat.cs index 32c6e1a..461cf73 100644 --- a/ClosedXML/Excel/Style/XLPredefinedFormat.cs +++ b/ClosedXML/Excel/Style/XLPredefinedFormat.cs @@ -1,4 +1,6 @@ -namespace ClosedXML.Excel +using System.Collections.Generic; + +namespace ClosedXML.Excel { /// /// Reference point of date/number formats available. @@ -6,6 +8,11 @@ /// public static class XLPredefinedFormat { + /// + /// General + /// + public static int General { get { return 0; } } + public enum Number { /// @@ -37,22 +44,22 @@ /// 0% /// PercentInteger = 9, - + /// /// 0.00% /// PercentPrecision2 = 10, - + /// /// 0.00E+00 /// ScientificPrecision2 = 11, - + /// /// # ?/? /// FractionPrecision1 = 12, - + /// /// # ??/?? /// @@ -67,7 +74,7 @@ /// #,##0 ,[Red](#,##0) /// IntegerWithSeparatorAndParensRed = 38, - + /// /// #,##0.00,(#,##0.00) /// @@ -100,42 +107,42 @@ /// d/m/yyyy /// DayMonthYear4WithSlashes = 14, - + /// /// d-mmm-yy /// DayMonthAbbrYear2WithDashes = 15, - + /// /// d-mmm /// DayMonthAbbrWithDash = 16, - + /// /// mmm-yy /// MonthAbbrYear2WithDash = 17, - + /// /// h:mm tt /// Hour12MinutesAmPm = 18, - + /// /// h:mm:ss tt /// Hour12MinutesSecondsAmPm = 19, - + /// /// H:mm /// Hour24Minutes = 20, - + /// /// H:mm:ss /// Hour24MinutesSeconds = 21, - + /// /// m/d/yyyy H:mm /// @@ -150,16 +157,62 @@ /// [h]:mm:ss /// Hour12MinutesSeconds = 46, - + /// /// mmss.0 /// MinutesSecondsMillis1 = 47, - + /// /// @ /// Text = 49 } + + private static IDictionary _formatCodes; + internal static IDictionary FormatCodes + { + get + { + if (_formatCodes == null) + { + var fCodes = new Dictionary + { + {0, string.Empty}, + {1, "0"}, + {2, "0.00"}, + {3, "#,##0"}, + {4, "#,##0.00"}, + {7, "$#,##0.00_);($#,##0.00)"}, + {9, "0%"}, + {10, "0.00%"}, + {11, "0.00E+00"}, + {12, "# ?/?"}, + {13, "# ??/??"}, + {14, "M/d/yyyy"}, + {15, "d-MMM-yy"}, + {16, "d-MMM"}, + {17, "MMM-yy"}, + {18, "h:mm tt"}, + {19, "h:mm:ss tt"}, + {20, "H:mm"}, + {21, "H:mm:ss"}, + {22, "M/d/yyyy H:mm"}, + {37, "#,##0 ;(#,##0)"}, + {38, "#,##0 ;[Red](#,##0)"}, + {39, "#,##0.00;(#,##0.00)"}, + {40, "#,##0.00;[Red](#,##0.00)"}, + {45, "mm:ss"}, + {46, "[h]:mm:ss"}, + {47, "mmss.0"}, + {48, "##0.0E+0"}, + {49, "@"} + }; + _formatCodes = fCodes; + } + + return _formatCodes; + } + } } } diff --git a/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/Excel/Style/XLStyle.cs index b392a84..939aa96 100644 --- a/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/Excel/Style/XLStyle.cs @@ -38,7 +38,7 @@ public IXLFill Fill { get; set; } private IXLNumberFormat numberFormat; - public IXLNumberFormat NumberFormat + public IXLNumberFormat NumberFormat { get { diff --git a/ClosedXML/Excel/Style/XLStylizedContainer.cs b/ClosedXML/Excel/Style/XLStylizedContainer.cs index bc2e5ec..e9177a3 100644 --- a/ClosedXML/Excel/Style/XLStylizedContainer.cs +++ b/ClosedXML/Excel/Style/XLStylizedContainer.cs @@ -19,11 +19,12 @@ public IEnumerable Styles { - get + get { + var _backupUpdatingStyle = _container.UpdatingStyle; _container.UpdatingStyle = true; yield return Style; - _container.UpdatingStyle = false; + _container.UpdatingStyle = _backupUpdatingStyle; } } diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 1030c02..9a6fa97 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,5 +1,6 @@ using System; using System.Collections.Generic; +using System.Data; namespace ClosedXML.Excel { @@ -116,5 +117,17 @@ IXLTable SetShowTotalsRow(Boolean value); IXLRangeRow TotalsRow(); + + /// + /// Converts the table to an enumerable of dynamic objects + /// + /// + IEnumerable AsDynamicEnumerable(); + + /// + /// Converts the table to a standard .NET System.Data.DataTable + /// + /// + DataTable AsNativeDataTable(); } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 91ba4e8..4c2fb00 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -18,14 +18,86 @@ public interface IXLTableField { + /// + /// Gets the corresponding column for this table field. + /// + /// + /// The column. + /// IXLRangeColumn Column { get; } + + /// + /// Gets the index of the column (0-based). + /// + /// + /// The index. + /// Int32 Index { get; } + + /// + /// Gets or sets the name/header of this table field. + /// The corresponding header cell's value will change if you set this. + /// + /// + /// The name. + /// String Name { get; set; } + + /// + /// Gets the underlying table for this table field. + /// + IXLTable Table { get; } + + /// + /// Gets or sets the totals row formula in A1 format. + /// + /// + /// The totals row formula a1. + /// String TotalsRowFormulaA1 { get; set; } + + /// + /// Gets or sets the totals row formula in R1C1 format. + /// + /// + /// The totals row formula r1 c1. + /// String TotalsRowFormulaR1C1 { get; set; } + + /// + /// Gets or sets the totals row function. + /// + /// + /// The totals row function. + /// XLTotalsRowFunction TotalsRowFunction { get; set; } + + /// + /// Gets or sets the totals row label. + /// + /// + /// The totals row label. + /// String TotalsRowLabel { get; set; } + /// + /// Deletes this table field from the table. + /// void Delete(); + + /// + /// Determines whether all cells this table field have a consistent data type. + /// + Boolean IsConsistentDataType(); + + /// + /// Determines whether all cells this table field have a consistent formula. + /// + Boolean IsConsistentFormula(); + + /// + /// Determines whether all cells this table field have a consistent style. + /// + Boolean IsConsistentStyle(); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index c468d76..6eefffc 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,5 +1,7 @@ using System; using System.Collections.Generic; +using System.Data; +using System.Dynamic; using System.Linq; using System.Text; @@ -626,8 +628,6 @@ RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); - - HeadersRow().DataType = XLCellValues.Text; } else { @@ -676,6 +676,9 @@ } } _showHeaderRow = value; + + if (_showHeaderRow) + HeadersRow().DataType = XLCellValues.Text; } } @@ -753,5 +756,77 @@ columns.Cast().ForEach(column => column.Table = this); return columns; } + + public IEnumerable AsDynamicEnumerable() + { + foreach (var row in this.DataRange.Rows()) + { + dynamic expando = new ExpandoObject(); + foreach (var f in this.Fields) + { + var value = row.Cell(f.Index + 1).Value; + // ExpandoObject supports IDictionary so we can extend it like this + var expandoDict = expando as IDictionary; + if (expandoDict.ContainsKey(f.Name)) + expandoDict[f.Name] = value; + else + expandoDict.Add(f.Name, value); + } + + yield return expando; + } + } + + public DataTable AsNativeDataTable() + { + var table = new DataTable(this.Name); + + foreach (var f in Fields.Cast()) + { + Type type = typeof(object); + if (f.IsConsistentDataType()) + { + var c = f.Column.Cells().Skip(this.ShowHeaderRow ? 1 : 0).First(); + switch (c.DataType) + { + case XLCellValues.Text: + type = typeof(String); + break; + + case XLCellValues.Boolean: + type = typeof(Boolean); + break; + + case XLCellValues.DateTime: + type = typeof(DateTime); + break; + + case XLCellValues.TimeSpan: + type = typeof(TimeSpan); + break; + + case XLCellValues.Number: + type = typeof(Double); + break; + } + } + + table.Columns.Add(f.Name, type); + } + + foreach (var row in this.DataRange.Rows()) + { + var dr = table.NewRow(); + + foreach (var f in this.Fields) + { + dr[f.Name] = row.Cell(f.Index + 1).Value; + } + + table.Rows.Add(dr); + } + + return table; + } } } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index c737adc..ed65bed 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -11,6 +11,8 @@ internal String totalsRowLabel; private readonly XLTable table; + private IXLRangeColumn _column; + private Int32 index; private String name; public XLTableField(XLTable table, String name) @@ -19,15 +21,18 @@ this.name = name; } - private IXLRangeColumn _column; - public IXLRangeColumn Column { - get { return _column ?? (_column = table.HeadersRow(false).Cell(this.Index + 1).AsRange().Columns().Single()); } + get + { + if (_column == null) + { + _column = this.table.AsRange().Column(this.Index + 1); + } + return _column; + } } - private Int32 index; - public Int32 Index { get { return index; } @@ -55,6 +60,8 @@ } } + public IXLTable Table { get { return table; } } + public String TotalsRowFormulaA1 { get { return table.TotalsRow().Cell(Index + 1).FormulaA1; } @@ -85,6 +92,88 @@ } } + public String TotalsRowLabel + { + get { return totalsRowLabel; } + set + { + totalsRowFunction = XLTotalsRowFunction.None; + (table.TotalsRow().Cell(Index + 1) as XLCell).SetValue(value, false); + totalsRowLabel = value; + } + } + + public void Delete() + { + Delete(true); + } + + internal void Delete(Boolean deleteUnderlyingRangeColumn) + { + var fields = table.Fields.Cast().ToArray(); + + if (deleteUnderlyingRangeColumn) + { + table.AsRange().ColumnQuick(this.Index + 1).Delete(); + } + + fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); + table.FieldNames.Remove(this.Name); + } + + public bool IsConsistentDataType() + { + var dataTypes = this.Column + .Cells() + .Skip(this.table.ShowHeaderRow ? 1 : 0) + .Select(c => c.DataType); + + if (this.table.ShowTotalsRow) + dataTypes = dataTypes.Take(dataTypes.Count() - 1); + + var distinctDataTypes = dataTypes + .GroupBy(dt => dt) + .Select(g => new { Key = g.Key, Count = g.Count() }); + + return distinctDataTypes.Count() == 1; + } + + public Boolean IsConsistentFormula() + { + var formulas = this.Column + .Cells() + .Skip(this.table.ShowHeaderRow ? 1 : 0) + .Select(c => c.FormulaR1C1); + + if (this.table.ShowTotalsRow) + formulas = formulas.Take(formulas.Count() - 1); + + var distinctFormulas = formulas + .GroupBy(f => f) + .Select(g => new { Key = g.Key, Count = g.Count() }); + + return distinctFormulas.Count() == 1; + } + + public bool IsConsistentStyle() + { + var styles = this.Column + .Cells() + .Skip(this.table.ShowHeaderRow ? 1 : 0) + .Select(c => c.Style); + + if (this.table.ShowTotalsRow) + styles = styles.Take(styles.Count() - 1); + + var distinctStyles = styles + .GroupBy(f => f) + .Select(g => new { Key = g.Key, Count = g.Count() }); + + var ie = distinctStyles.First().Key.Equals(distinctStyles.Last().Key); + + return distinctStyles.Count() == 1; + } + internal void UpdateUnderlyingCellFormula() { if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom) @@ -112,35 +201,5 @@ } } } - - public String TotalsRowLabel - { - get { return totalsRowLabel; } - set - { - totalsRowFunction = XLTotalsRowFunction.None; - (table.TotalsRow().Cell(Index + 1) as XLCell).SetValue(value, false); - totalsRowLabel = value; - } - } - - public void Delete() - { - Delete(true); - } - - internal void Delete(Boolean deleteUnderlyingRangeColumn) - { - var fields = table.Fields.Cast().ToArray(); - - if (deleteUnderlyingRangeColumn) - { - table.AsRange().ColumnQuick(this.Index + 1).Delete(); - // (this.Column as XLRangeColumn).Delete(false); - } - - fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); - table.FieldNames.Remove(this.Name); - } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs index 4948bb2..c9d87d0 100644 --- a/ClosedXML/Excel/XLConstants.cs +++ b/ClosedXML/Excel/XLConstants.cs @@ -4,7 +4,7 @@ public static class XLConstants { public const string PivotTableValuesSentinalLabel = "{{Values}}"; - public const int NumberOfBuiltInStyles = 163; + public const int NumberOfBuiltInStyles = 164; // But they are stored as 0-based (0 - 163) internal static class Comment { diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 3af1993..2e9c4cd 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -642,6 +642,73 @@ } } } + + // Filters + if (pivotTableDefinition.PageFields != null) + { + foreach (var pageField in pivotTableDefinition.PageFields.Cast()) + { + var pf = pivotTableDefinition.PivotFields.ElementAt((int)pageField.Field.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)pageField.Field.Value) as CacheField; + + var filterName = pf.Name?.Value ?? cacheField.Name?.Value; + + IXLPivotField rf; + if (pageField.Name?.Value != null) + rf = pt.ReportFilters.Add(filterName, pageField.Name.Value); + else + rf = pt.ReportFilters.Add(filterName); + + if ((pageField.Item?.HasValue ?? false) + && pf.Items.Any() && cacheField.SharedItems.Any()) + { + var item = pf.Items.ElementAt(Convert.ToInt32(pageField.Item.Value)) as Item; + if (item == null) + continue; + + var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); + var numberItem = sharedItem as NumberItem; + var stringItem = sharedItem as StringItem; + var dateTimeItem = sharedItem as DateTimeItem; + + if (numberItem != null) + rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); + else if (dateTimeItem != null) + rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); + else if (stringItem != null) + rf.AddSelectedValue(stringItem.Val.Value); + else + throw new NotImplementedException(); + } + else if (BooleanValue.ToBoolean(pf.MultipleItemSelectionAllowed)) + { + foreach (var item in pf.Items.Cast()) + { + if (item.Hidden == null || !BooleanValue.ToBoolean(item.Hidden)) + { + var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); + var numberItem = sharedItem as NumberItem; + var stringItem = sharedItem as StringItem; + var dateTimeItem = sharedItem as DateTimeItem; + + if (numberItem != null) + rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); + else if (dateTimeItem != null) + rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); + else if (stringItem != null) + rf.AddSelectedValue(stringItem.Val.Value); + else + throw new NotImplementedException(); + } + } + } + } + + pt.TargetCell = pt.TargetCell.CellAbove(pt.ReportFilters.Count() + 1); + } } } } @@ -1388,10 +1455,11 @@ { if (nfSource == null) return; - if (nfSource.FormatCode != null) + if (nfSource.NumberFormatId != null && nfSource.NumberFormatId.Value < XLConstants.NumberOfBuiltInStyles) + nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value; + else if (nfSource.FormatCode != null) nf.Format = nfSource.FormatCode.Value; - //if (nfSource.NumberFormatId != null) - // nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value; + } private void LoadBorder(Border borderSource, IXLBorder border) @@ -1422,21 +1490,44 @@ } } - private void LoadFill(Fill fillSource, IXLFill fill) + // Differential fills store the patterns differently than other fills + // Actually differential fills make more sense. bg is bg and fg is fg + // 'Other' fills store the bg color in the fg field when pattern type is solid + private void LoadFill(Fill openXMLFill, IXLFill closedXMLFill, Boolean differentialFillFormat) { - if (fillSource == null) return; + if (openXMLFill == null || openXMLFill.PatternFill == null) return; - if (fillSource.PatternFill != null) + if (openXMLFill.PatternFill.PatternType != null) + closedXMLFill.PatternType = openXMLFill.PatternFill.PatternType.Value.ToClosedXml(); + else + closedXMLFill.PatternType = XLFillPatternValues.Solid; + + switch (closedXMLFill.PatternType) { - if (fillSource.PatternFill.PatternType != null) - fill.PatternType = fillSource.PatternFill.PatternType.Value.ToClosedXml(); - else - fill.PatternType = XLFillPatternValues.Solid; + case XLFillPatternValues.None: + break; - if (fillSource.PatternFill.ForegroundColor != null) - fill.PatternColor = GetColor(fillSource.PatternFill.ForegroundColor); - if (fillSource.PatternFill.BackgroundColor != null) - fill.PatternBackgroundColor = GetColor(fillSource.PatternFill.BackgroundColor); + case XLFillPatternValues.Solid: + if (differentialFillFormat) + { + if (openXMLFill.PatternFill.BackgroundColor != null) + closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + } + else + { + // yes, source is foreground! + if (openXMLFill.PatternFill.ForegroundColor != null) + closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.ForegroundColor); + } + break; + + default: + if (openXMLFill.PatternFill.ForegroundColor != null) + closedXMLFill.PatternColor = GetColor(openXMLFill.PatternFill.ForegroundColor); + + if (openXMLFill.PatternFill.BackgroundColor != null) + closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + break; } } @@ -1853,7 +1944,7 @@ if (fr.FormatId != null) { LoadFont(differentialFormats[(Int32)fr.FormatId.Value].Font, conditionalFormat.Style.Font); - LoadFill(differentialFormats[(Int32)fr.FormatId.Value].Fill, conditionalFormat.Style.Fill); + LoadFill(differentialFormats[(Int32)fr.FormatId.Value].Fill, conditionalFormat.Style.Fill, differentialFillFormat: true); LoadBorder(differentialFormats[(Int32)fr.FormatId.Value].Border, conditionalFormat.Style.Border); LoadNumberFormat(differentialFormats[(Int32)fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); } @@ -2253,7 +2344,7 @@ thisColor = _colorList[htmlColor]; retVal = XLColor.FromColor(thisColor); } - else if (color.Indexed != null && color.Indexed < 64) + else if (color.Indexed != null && color.Indexed <= 64) retVal = XLColor.FromIndex((Int32)color.Indexed.Value); else if (color.Theme != null) { @@ -2290,15 +2381,7 @@ var fill = (Fill)fills.ElementAt((Int32)cellFormat.FillId.Value); if (fill.PatternFill != null) { - if (fill.PatternFill.PatternType != null) - xlStylized.InnerStyle.Fill.PatternType = fill.PatternFill.PatternType.Value.ToClosedXml(); - - var fgColor = GetColor(fill.PatternFill.ForegroundColor); - if (fgColor.HasValue) xlStylized.InnerStyle.Fill.PatternColor = fgColor; - - var bgColor = GetColor(fill.PatternFill.BackgroundColor); - if (bgColor.HasValue) - xlStylized.InnerStyle.Fill.PatternBackgroundColor = bgColor; + LoadFill(fill, xlStylized.InnerStyle.Fill, differentialFillFormat: false); } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 491b37c..c4a9961 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -1,6 +1,5 @@ using System; using System.Collections.Generic; -using System.Diagnostics; using System.Linq; namespace ClosedXML.Excel @@ -8,81 +7,57 @@ public partial class XLWorkbook { #region Nested type: SaveContext + internal sealed class SaveContext { - #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly RelIdGenerator _relIdGenerator; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedStyles; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedNumberFormats; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedFonts; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly HashSet _tableNames; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private uint _tableId; - #endregion - #region Constructor public SaveContext() { - _relIdGenerator = new RelIdGenerator(); - _sharedStyles = new Dictionary(); - _sharedNumberFormats = new Dictionary(); - _sharedFonts = new Dictionary(); - _tableNames = new HashSet(); - _tableId = 0; + DifferentialFormats = new Dictionary(); + PivotTables = new Dictionary(); + RelIdGenerator = new RelIdGenerator(); + SharedFonts = new Dictionary(); + SharedNumberFormats = new Dictionary(); + SharedStyles = new Dictionary(); + TableId = 0; + TableNames = new HashSet(); } - #endregion - #region Public properties - public RelIdGenerator RelIdGenerator - { - [DebuggerStepThrough] - get { return _relIdGenerator; } - } - public Dictionary SharedStyles - { - [DebuggerStepThrough] - get { return _sharedStyles; } - } - public Dictionary SharedNumberFormats - { - [DebuggerStepThrough] - get { return _sharedNumberFormats; } - } - public Dictionary SharedFonts - { - [DebuggerStepThrough] - get { return _sharedFonts; } - } - public HashSet TableNames - { - [DebuggerStepThrough] - get { return _tableNames; } - } - public uint TableId - { - [DebuggerStepThrough] - get { return _tableId; } - [DebuggerStepThrough] - set { _tableId = value; } - } - public Dictionary DifferentialFormats = new Dictionary(); - #endregion + + public Dictionary DifferentialFormats { get; private set; } + public IDictionary PivotTables { get; private set; } + public RelIdGenerator RelIdGenerator { get; private set; } + public Dictionary SharedFonts { get; private set; } + public Dictionary SharedNumberFormats { get; private set; } + public Dictionary SharedStyles { get; private set; } + public uint TableId { get; set; } + public HashSet TableNames { get; private set; } } - #endregion + + #endregion Nested type: SaveContext + #region Nested type: RelType + internal enum RelType { Workbook//, Worksheet } - #endregion + + #endregion Nested type: RelType + #region Nested type: RelIdGenerator + internal sealed class RelIdGenerator { private readonly Dictionary> _relIds = new Dictionary>(); + public void AddValues(IEnumerable values, RelType relType) + { + if (!_relIds.ContainsKey(relType)) + { + _relIds.Add(relType, new List()); + } + _relIds[relType].AddRange(values.Where(v => !_relIds[relType].Contains(v))); + } + public String GetNext() { return GetNext(RelType.Workbook); @@ -107,59 +82,85 @@ id++; } } - public void AddValues(IEnumerable values, RelType relType) - { - if (!_relIds.ContainsKey(relType)) - { - _relIds.Add(relType, new List()); - } - _relIds[relType].AddRange(values.Where(v => !_relIds[relType].Contains(v))); - } + public void Reset(RelType relType) { if (_relIds.ContainsKey(relType)) _relIds.Remove(relType); } } - #endregion + + #endregion Nested type: RelIdGenerator + #region Nested type: FontInfo + internal struct FontInfo { - public UInt32 FontId; public XLFont Font; + public UInt32 FontId; }; - #endregion + + #endregion Nested type: FontInfo + #region Nested type: FillInfo + internal struct FillInfo { - public UInt32 FillId; public XLFill Fill; + public UInt32 FillId; } - #endregion + + #endregion Nested type: FillInfo + #region Nested type: BorderInfo + internal struct BorderInfo { - public UInt32 BorderId; public XLBorder Border; + public UInt32 BorderId; } - #endregion + + #endregion Nested type: BorderInfo + #region Nested type: NumberFormatInfo + internal struct NumberFormatInfo { - public Int32 NumberFormatId; public IXLNumberFormatBase NumberFormat; + public Int32 NumberFormatId; } - #endregion + + #endregion Nested type: NumberFormatInfo + #region Nested type: StyleInfo + internal struct StyleInfo { - public UInt32 StyleId; - public UInt32 FontId; - public UInt32 FillId; public UInt32 BorderId; + public UInt32 FillId; + public UInt32 FontId; public Int32 NumberFormatId; public IXLStyle Style; + public UInt32 StyleId; } - #endregion + + #endregion Nested type: StyleInfo + + #region Nested type: Pivot tables + + internal struct PivotTableFieldInfo + { + public XLCellValues DataType; + public Boolean MixedDataType; + public IEnumerable DistinctValues; + } + + internal struct PivotTableInfo + { + public IDictionary Fields; + public Guid Guid; + } + + #endregion Nested type: Pivot tables } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index a60076a..4979c7d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -53,7 +53,6 @@ { private const Double ColumnWidthOffset = 0.710625; - //private Dictionary sharedStrings; //private Dictionary context.SharedStyles; private static readonly EnumValue CvSharedString = new EnumValue(CellValues.SharedString); @@ -1819,32 +1818,68 @@ else table.TotalsRowShown = false; - var tableColumns1 = new TableColumns { Count = (UInt32)xlTable.ColumnCount() }; + var tableColumns = new TableColumns { Count = (UInt32)xlTable.ColumnCount() }; UInt32 columnId = 0; foreach (var xlField in xlTable.Fields) { columnId++; var fieldName = xlField.Name; - var tableColumn1 = new TableColumn + var tableColumn = new TableColumn { Id = columnId, Name = fieldName.Replace("_x000a_", "_x005f_x000a_").Replace(Environment.NewLine, "_x000a_") }; + + // https://github.com/ClosedXML/ClosedXML/issues/513 + if (xlField.IsConsistentStyle()) + { + var style = xlField.Column.Cells() + .Skip(xlTable.ShowHeaderRow ? 1 : 0) + .First() + .Style; + + if (!DefaultStyle.Equals(style) && context.DifferentialFormats.ContainsKey(style)) + tableColumn.DataFormatId = UInt32Value.FromUInt32(Convert.ToUInt32(context.DifferentialFormats[style])); + } + else + tableColumn.DataFormatId = null; + + if (xlField.IsConsistentFormula()) + { + string formula = xlField.Column.Cells() + .Skip(xlTable.ShowHeaderRow ? 1 : 0) + .First() + .FormulaA1; + + while (formula.StartsWith("=") && formula.Length > 1) + formula = formula.Substring(1); + + if (!String.IsNullOrWhiteSpace(formula)) + { + tableColumn.CalculatedColumnFormula = new CalculatedColumnFormula + { + Text = formula + }; + } + } + else + tableColumn.CalculatedColumnFormula = null; + if (xlTable.ShowTotalsRow) { if (xlField.TotalsRowFunction != XLTotalsRowFunction.None) { - tableColumn1.TotalsRowFunction = xlField.TotalsRowFunction.ToOpenXml(); + tableColumn.TotalsRowFunction = xlField.TotalsRowFunction.ToOpenXml(); if (xlField.TotalsRowFunction == XLTotalsRowFunction.Custom) - tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); + tableColumn.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); } if (!String.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) - tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel; + tableColumn.TotalsRowLabel = xlField.TotalsRowLabel; } - tableColumns1.AppendChild(tableColumn1); + tableColumns.AppendChild(tableColumn); } var tableStyleInfo1 = new TableStyleInfo @@ -1875,7 +1910,7 @@ table.AppendChild(autoFilter1); } - table.AppendChild(tableColumns1); + table.AppendChild(tableColumns); table.AppendChild(tableStyleInfo1); tableDefinitionPart.Table = table; @@ -1898,6 +1933,8 @@ foreach (var pt in xlWorksheet.PivotTables.Cast()) { + context.PivotTables.Clear(); + // TODO: Avoid duplicate pivot caches of same source range var workbookCacheRelId = pt.WorkbookCacheRelId; @@ -1915,7 +1952,7 @@ pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); } - GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); + GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt, context); if (String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) pivotCaches.AppendChild(pivotCache); @@ -1935,10 +1972,16 @@ // Generates content of pivotTableCacheDefinitionPart private static void GeneratePivotTableCacheDefinitionPartContent( - PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt) + PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt, + SaveContext context) { - var source = pt.SourceRange; + var pti = new PivotTableInfo + { + Guid = (pt as XLPivotTable).Guid, + Fields = new Dictionary() + }; + var source = pt.SourceRange; var pivotCacheDefinition = new PivotCacheDefinition { Id = "rId1", @@ -1962,14 +2005,18 @@ { var columnNumber = c.ColumnNumber(); var columnName = c.FirstCell().Value.ToString(); - IXLPivotField xlpf; - if (pt.Fields.Contains(columnName)) - xlpf = pt.Fields.Get(columnName); - else - xlpf = pt.Fields.Add(columnName); - var field = - pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName); + XLPivotField xlpf; + if (pt.Fields.Contains(columnName)) + xlpf = pt.Fields.Get(columnName) as XLPivotField; + else + xlpf = pt.Fields.Add(columnName) as XLPivotField; + + var field = pt.RowLabels + .Union(pt.ColumnLabels) + .Union(pt.ReportFilters) + .FirstOrDefault(f => f.SourceName == columnName); + if (field != null) { xlpf.CustomName = field.CustomName; @@ -1978,32 +2025,96 @@ var sharedItems = new SharedItems(); - var onlyNumbers = - !source.Cells().Any( - cell => - cell.Address.ColumnNumber == columnNumber && - cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number); - if (onlyNumbers) + var ptfi = new PivotTableFieldInfo(); + + var fieldValueCells = source.Cells(cell => cell.Address.ColumnNumber == columnNumber + && cell.Address.RowNumber > source.FirstRow().RowNumber()); + var types = fieldValueCells.Select(cell => cell.DataType).Distinct(); + + + if (types.Count() == 1 && types.Single() == XLCellValues.Number) { - sharedItems = new SharedItems - { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true }; + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsString = false; + sharedItems.ContainsNumber = true; + + ptfi.DataType = XLCellValues.Number; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDouble()) + .Distinct() + .Cast(); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new NumberItem { Val = (double)value }); + } + + sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); + sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); + } + else if (types.Count() == 1 && types.Single() == XLCellValues.DateTime) + { + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsString = false; + sharedItems.ContainsNumber = false; + sharedItems.ContainsDate = true; + + ptfi.DataType = XLCellValues.DateTime; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDateTime()) + .Distinct() + .Cast(); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new DateTimeItem { Val = (DateTime)value }); + } + + sharedItems.MinDate = (DateTime)ptfi.DistinctValues.Min(); + sharedItems.MaxDate = (DateTime)ptfi.DistinctValues.Max(); } else { - foreach (var cellValue in source.Cells() - .Where(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()) - .Select(cell => cell.Value.ToString()) - .Where(cellValue => !xlpf.SharedStrings.Select(ss => ss.ToLower()).Contains(cellValue.ToLower()))) + if (types.Any()) { - xlpf.SharedStrings.Add(cellValue); - } + ptfi.DataType = types.First(); + ptfi.MixedDataType = types.Count() > 1; - foreach (var li in xlpf.SharedStrings) - { - sharedItems.AppendChild(new StringItem { Val = li }); + if (!ptfi.MixedDataType && ptfi.DataType == XLCellValues.Text) + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.Value) + .Cast() + .Distinct(StringComparer.OrdinalIgnoreCase); + else + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetString()) + .Cast() + .Distinct(StringComparer.OrdinalIgnoreCase); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new StringItem { Val = (string)value }); } } + if (ptfi.DistinctValues.Any()) + sharedItems.Count = Convert.ToUInt32(ptfi.DistinctValues.Count()); + var cacheField = new CacheField { Name = xlpf.SourceName }; cacheField.AppendChild(sharedItems); cacheFields.AppendChild(cacheField); @@ -2020,13 +2131,19 @@ var pivotCacheRecords = new PivotCacheRecords(); pivotCacheRecords.AddNamespaceDeclaration("r", - "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords; + + context.PivotTables.Add(pti.Guid, pti); } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart, IXLPivotTable pt, uint cacheId, SaveContext context) + private static void GeneratePivotTablePartContent( + PivotTablePart pivotTablePart, IXLPivotTable pt, + uint cacheId, SaveContext context) { + var pti = context.PivotTables[(pt as XLPivotTable).Guid]; + var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, @@ -2093,12 +2210,17 @@ var location = new Location { - Reference = pt.TargetCell.Address.ToString(), FirstHeaderRow = 1U, FirstDataRow = 1U, FirstDataColumn = 1U }; + if (pt.ReportFilters.Any()) + // Reference cell is the part BELOW the report filters + location.Reference = pt.TargetCell.CellBelow(pt.ReportFilters.Count() + 1).Address.ToString(); + else + location.Reference = pt.TargetCell.Address.ToString(); + var rowFields = new RowFields(); var columnFields = new ColumnFields(); var rowItems = new RowItems(); @@ -2106,14 +2228,21 @@ var pageFields = new PageFields { Count = (uint)pt.ReportFilters.Count() }; var pivotFields = new PivotFields { Count = Convert.ToUInt32(pt.SourceRange.ColumnCount()) }; - foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue)) + foreach (var xlpf in pt + .Fields + .Cast() + .OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) + ? pt.RowLabels.IndexOf(f) + : Int32.MaxValue)) { + var ptfi = pti.Fields[xlpf.SourceName]; + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; rowFields.AppendChild(f); - for (var i = 0; i < xlpf.SharedStrings.Count; i++) + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex { Val = i }); @@ -2129,7 +2258,7 @@ var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); - for (var i = 0; i < xlpf.SharedStrings.Count; i++) + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex { Val = i }); @@ -2154,9 +2283,10 @@ } } - foreach (var xlpf in pt.Fields) + foreach (var xlpf in pt.Fields.Cast()) { - IXLPivotField labelField = null; + var ptfi = pti.Fields[xlpf.SourceName]; + IXLPivotField labelOrFilterField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.ClassicPivotTableLayout) @@ -2182,37 +2312,95 @@ break; } - if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) + if (pt.RowLabels.Contains(xlpf.SourceName)) { - labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); + labelOrFilterField = pt.RowLabels.Get(xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisRow; } - else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) + else if (pt.ColumnLabels.Contains(xlpf.SourceName)) { - labelField = pt.ColumnLabels.Single(p => p.SourceName == xlpf.SourceName); + labelOrFilterField = pt.ColumnLabels.Get(xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisColumn; } - else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + else if (pt.ReportFilters.Contains(xlpf.SourceName)) { + labelOrFilterField = pt.ReportFilters.Get(xlpf.SourceName); + location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; - pageFields.AppendChild(new PageField { Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf) }); + + var pageField = new PageField + { + Hierarchy = -1, + Field = pt.Fields.IndexOf(xlpf) + }; + + if (labelOrFilterField.SelectedValues.Count == 1) + { + if (ptfi.MixedDataType || ptfi.DataType == XLCellValues.Text) + { + var values = ptfi.DistinctValues + .Select(v => v.ToString().ToLower()) + .ToList(); + var selectedValue = labelOrFilterField.SelectedValues.Single().ToString().ToLower(); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else if (ptfi.DataType == XLCellValues.DateTime) + { + var values = ptfi.DistinctValues + .Select(v => Convert.ToDateTime(v)) + .ToList(); + var selectedValue = Convert.ToDateTime(labelOrFilterField.SelectedValues.Single()); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else if (ptfi.DataType == XLCellValues.Number) + { + var values = ptfi.DistinctValues + .Select(v => Convert.ToDouble(v)) + .ToList(); + var selectedValue = Convert.ToDouble(labelOrFilterField.SelectedValues.Single()); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else + throw new NotImplementedException(); + } + + pageFields.AppendChild(pageField); } + if ((labelOrFilterField?.SelectedValues?.Count ?? 0) > 1) + pf.MultipleItemSelectionAllowed = true; + if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) pf.DataField = true; var fieldItems = new Items(); - if (xlpf.SharedStrings.Any()) + // Output items only for row / column / filter fields + if (ptfi.DistinctValues.Any() + && (pt.RowLabels.Contains(xlpf.SourceName) + || pt.ColumnLabels.Contains(xlpf.SourceName) + || pt.ReportFilters.Contains(xlpf.SourceName))) { - for (uint i = 0; i < xlpf.SharedStrings.Count; i++) + uint i = 0; + foreach (var value in ptfi.DistinctValues) { var item = new Item { Index = i }; - if (labelField != null && labelField.Collapsed) + + if (labelOrFilterField != null && labelOrFilterField.Collapsed) item.HideDetails = BooleanValue.FromBoolean(false); + + if (labelOrFilterField.SelectedValues.Count > 1 + && !labelOrFilterField.SelectedValues.Contains(value)) + item.Hidden = BooleanValue.FromBoolean(true); + fieldItems.AppendChild(item); + + i++; } } @@ -2949,7 +3137,7 @@ UInt32 fontCount = 1; UInt32 fillCount = 3; UInt32 borderCount = 1; - var numberFormatCount = 1; + var numberFormatCount = 0; // 0-based var xlStyles = new HashSet(); var pivotTableNumberFormats = new HashSet(); @@ -3090,8 +3278,24 @@ { foreach (var cf in ws.ConditionalFormats) { - if (!context.DifferentialFormats.ContainsKey(cf.Style)) - AddDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, cf, context); + //var ie = context.DifferentialFormats.Keys.First().Equals(cf.Style); + + if (!cf.Style.Equals(DefaultStyle) && !context.DifferentialFormats.ContainsKey(cf.Style)) + AddConditionalDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, cf, context); + } + + foreach (var tf in ws.Tables.SelectMany(t => t.Fields)) + { + if (tf.IsConsistentStyle()) + { + var style = tf.Column.Cells() + .Skip(tf.Table.ShowHeaderRow ? 1 : 0) + .First() + .Style; + + if (!style.Equals(DefaultStyle) && !context.DifferentialFormats.ContainsKey(style)) + AddStyleAsDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, style, context); + } } } @@ -3105,19 +3309,26 @@ { dictionary.Clear(); var id = 0; + foreach (var df in differentialFormats.Elements()) { - var style = new XLStyle(new XLStylizedEmpty(DefaultStyle), DefaultStyle); + var emptyContainer = new XLStylizedEmpty(DefaultStyle); + emptyContainer.UpdatingStyle = true; + + var style = new XLStyle(emptyContainer, DefaultStyle); LoadFont(df.Font, style.Font); LoadBorder(df.Border, style.Border); LoadNumberFormat(df.NumberingFormat, style.NumberFormat); - LoadFill(df.Fill, style.Fill); + LoadFill(df.Fill, style.Fill, differentialFillFormat: true); + + emptyContainer.UpdatingStyle = false; + if (!dictionary.ContainsKey(style)) - dictionary.Add(style, ++id); + dictionary.Add(style, id++); } } - private static void AddDifferentialFormat(DifferentialFormats differentialFormats, IXLConditionalFormat cf, + private static void AddConditionalDifferentialFormat(DifferentialFormats differentialFormats, IXLConditionalFormat cf, SaveContext context) { var differentialFormat = new DifferentialFormat(); @@ -3131,7 +3342,7 @@ }; differentialFormat.Append(numberFormat); } - differentialFormat.Append(GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, false)); + differentialFormat.Append(GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false)); differentialFormat.Append(GetNewBorder(new BorderInfo { Border = cf.Style.Border as XLBorder }, false)); differentialFormats.Append(differentialFormat); @@ -3139,6 +3350,42 @@ context.DifferentialFormats.Add(cf.Style, differentialFormats.Count() - 1); } + private static void AddStyleAsDifferentialFormat(DifferentialFormats differentialFormats, IXLStyle style, + SaveContext context) + { + var differentialFormat = new DifferentialFormat(); + differentialFormat.Append(GetNewFont(new FontInfo { Font = style.Font as XLFont }, false)); + if (!String.IsNullOrWhiteSpace(style.NumberFormat.Format) || style.NumberFormat.NumberFormatId != 0) + { + var numberFormat = new NumberingFormat(); + + if (style.NumberFormat.NumberFormatId == -1) + { + numberFormat.FormatCode = style.NumberFormat.Format; + numberFormat.NumberFormatId = (UInt32)(XLConstants.NumberOfBuiltInStyles + + differentialFormats + .Descendants() + .Count(df => df.NumberingFormat != null && df.NumberingFormat.NumberFormatId != null && df.NumberingFormat.NumberFormatId.Value >= XLConstants.NumberOfBuiltInStyles)); + } + else + { + numberFormat.NumberFormatId = (UInt32)(style.NumberFormat.NumberFormatId); + if (!string.IsNullOrEmpty(style.NumberFormat.Format)) + numberFormat.FormatCode = style.NumberFormat.Format; + else if (XLPredefinedFormat.FormatCodes.ContainsKey(style.NumberFormat.NumberFormatId)) + numberFormat.FormatCode = XLPredefinedFormat.FormatCodes[style.NumberFormat.NumberFormatId]; + } + + differentialFormat.Append(numberFormat); + } + differentialFormat.Append(GetNewFill(new FillInfo { Fill = style.Fill as XLFill }, differentialFillFormat: true, ignoreMod: false)); + differentialFormat.Append(GetNewBorder(new BorderInfo { Border = style.Border as XLBorder }, false)); + + differentialFormats.Append(differentialFormat); + + context.DifferentialFormats.Add(style, differentialFormats.Count() - 1); + } + private static void ResolveRest(WorkbookStylesPart workbookStylesPart, SaveContext context) { if (workbookStylesPart.Stylesheet.CellFormats == null) @@ -3467,7 +3714,7 @@ var foundOne = false; foreach (Fill f in workbookStylesPart.Stylesheet.Fills) { - if (FillsAreEqual(f, fillInfo.Fill)) + if (FillsAreEqual(f, fillInfo.Fill, fromDifferentialFormat: false)) { foundOne = true; break; @@ -3476,7 +3723,7 @@ } if (!foundOne) { - var fill = GetNewFill(fillInfo); + var fill = GetNewFill(fillInfo, differentialFillFormat: false); workbookStylesPart.Stylesheet.Fills.AppendChild(fill); } allSharedFills.Add(fillInfo.Fill, new FillInfo { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); @@ -3500,44 +3747,122 @@ fills.AppendChild(fill1); } - private static Fill GetNewFill(FillInfo fillInfo, Boolean ignoreMod = true) + private static Fill GetNewFill(FillInfo fillInfo, Boolean differentialFillFormat, Boolean ignoreMod = true) { var fill = new Fill(); var patternFill = new PatternFill(); - if (fillInfo.Fill.PatternTypeModified || ignoreMod) - patternFill.PatternType = fillInfo.Fill.PatternType.ToOpenXml(); - if (fillInfo.Fill.PatternColorModified || ignoreMod) - { - var foregroundColor = new ForegroundColor(); - if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Color) - foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); - else if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Indexed) - foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; - else - { - foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; - if (fillInfo.Fill.PatternColor.ThemeTint != 0) - foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; - } - patternFill.AppendChild(foregroundColor); - } + patternFill.PatternType = fillInfo.Fill.PatternType.ToOpenXml(); - if (fillInfo.Fill.PatternBackgroundColorModified || ignoreMod) + BackgroundColor backgroundColor; + ForegroundColor foregroundColor; + + switch (fillInfo.Fill.PatternType) { - var backgroundColor = new BackgroundColor(); - if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Color) - backgroundColor.Rgb = fillInfo.Fill.PatternBackgroundColor.Color.ToHex(); - else if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Indexed) - backgroundColor.Indexed = (UInt32)fillInfo.Fill.PatternBackgroundColor.Indexed; - else - { - backgroundColor.Theme = (UInt32)fillInfo.Fill.PatternBackgroundColor.ThemeColor; - if (fillInfo.Fill.PatternBackgroundColor.ThemeTint != 0) - backgroundColor.Tint = fillInfo.Fill.PatternBackgroundColor.ThemeTint; - } - patternFill.AppendChild(backgroundColor); + case XLFillPatternValues.None: + break; + + case XLFillPatternValues.Solid: + if (differentialFillFormat) + { + patternFill.AppendChild(new ForegroundColor { Auto = true }); + backgroundColor = new BackgroundColor(); + switch (fillInfo.Fill.BackgroundColor.ColorType) + { + case XLColorType.Color: + backgroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); + break; + + case XLColorType.Indexed: + backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; + break; + + case XLColorType.Theme: + backgroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; + + if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) + backgroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; + + break; + } + + patternFill.AppendChild(backgroundColor); + } + else + { + // ClosedXML Background color to be populated into OpenXML fgColor + foregroundColor = new ForegroundColor(); + switch (fillInfo.Fill.BackgroundColor.ColorType) + { + case XLColorType.Color: + foregroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); + break; + + case XLColorType.Indexed: + foregroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; + break; + + case XLColorType.Theme: + foregroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; + + if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) + foregroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; + + break; + } + + patternFill.AppendChild(foregroundColor); + } + break; + + default: + + foregroundColor = new ForegroundColor(); + switch (fillInfo.Fill.PatternColor.ColorType) + { + case XLColorType.Color: + foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); + break; + + case XLColorType.Indexed: + foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; + break; + + case XLColorType.Theme: + foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; + + if (fillInfo.Fill.PatternColor.ThemeTint != 0) + foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; + + break; + } + + patternFill.AppendChild(foregroundColor); + + backgroundColor = new BackgroundColor(); + switch (fillInfo.Fill.BackgroundColor.ColorType) + { + case XLColorType.Color: + backgroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); + break; + + case XLColorType.Indexed: + backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; + break; + + case XLColorType.Theme: + backgroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; + + if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) + backgroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; + + break; + } + + patternFill.AppendChild(backgroundColor); + + break; } fill.AppendChild(patternFill); @@ -3545,22 +3870,12 @@ return fill; } - private bool FillsAreEqual(Fill f, IXLFill xlFill) + private bool FillsAreEqual(Fill f, IXLFill xlFill, Boolean fromDifferentialFormat) { var nF = new XLFill(); - if (f.PatternFill != null) - { - if (f.PatternFill.PatternType != null) - nF.PatternType = f.PatternFill.PatternType.Value.ToClosedXml(); - var fColor = GetColor(f.PatternFill.ForegroundColor); - if (fColor.HasValue) - nF.PatternColor = fColor; + LoadFill(f, nF, fromDifferentialFormat); - var bColor = GetColor(f.PatternFill.BackgroundColor); - if (bColor.HasValue) - nF.PatternBackgroundColor = bColor; - } return nF.Equals(xlFill); } @@ -3735,7 +4050,7 @@ var allSharedNumberFormats = new Dictionary(); foreach (var numberFormatInfo in sharedNumberFormats.Values.Where(nf => nf.NumberFormatId != defaultFormatId)) { - var numberingFormatId = XLConstants.NumberOfBuiltInStyles + 1; + var numberingFormatId = XLConstants.NumberOfBuiltInStyles; // 0-based var foundOne = false; foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) { @@ -4527,7 +4842,6 @@ } } - var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; if (exlst != null && exlst.Count() > 0) { @@ -4580,7 +4894,6 @@ } } - #endregion Conditional Formatting #region DataValidations diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 6cde6da..c2d1067 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -229,7 +229,7 @@ var textSize = GraphicsUtils.MeasureString(text, font); double width = (((textSize.Width / (double)7) * 256) - (128 / 7)) / 256; - width = (double)decimal.Round((decimal)width + 0.2M, 2); + width = Math.Round(width + 0.2, 2); return width; } diff --git a/ClosedXML/Properties/AssemblyVersionInfo.cs b/ClosedXML/Properties/AssemblyVersionInfo.cs index ec9596d..6db1e31 100644 --- a/ClosedXML/Properties/AssemblyVersionInfo.cs +++ b/ClosedXML/Properties/AssemblyVersionInfo.cs @@ -9,4 +9,4 @@ // [assembly: AssemblyVersion("0.90.0.0")] [assembly: AssemblyFileVersion("0.90.0.0")] -[assembly: AssemblyInformationalVersion("0.90.0-beta2")] +[assembly: AssemblyInformationalVersion("0.90.0")] diff --git a/ClosedXML_Examples/Columns/InsertColumns.cs b/ClosedXML_Examples/Columns/InsertColumns.cs index 58b05ee..e908c01 100644 --- a/ClosedXML_Examples/Columns/InsertColumns.cs +++ b/ClosedXML_Examples/Columns/InsertColumns.cs @@ -1,46 +1,11 @@ +using ClosedXML.Excel; using System; using System.Linq; -using ClosedXML.Excel; - namespace ClosedXML_Examples.Columns { public class InsertColumns : 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 workbook = new XLWorkbook(); @@ -54,14 +19,12 @@ foreach (var c in Enumerable.Range(1, 5)) ws.Cell(r, c).Value = "X"; - var blueColumn = ws.Column(2); var redColumn = ws.Column(5); blueColumn.Style.Fill.BackgroundColor = XLColor.Blue; blueColumn.InsertColumnsAfter(2); - redColumn.Style.Fill.BackgroundColor = XLColor.Red; redColumn.InsertColumnsBefore(2); @@ -73,12 +36,5 @@ workbook.SaveAs(filePath); } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index a94bb10..b8f6023 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -8,41 +8,43 @@ { private class Pastry { - public Pastry(string name, int numberOfOrders, double quality, string month) + public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate) { Name = name; NumberOfOrders = numberOfOrders; Quality = quality; Month = month; + BakeDate = bakeDate; } public string Name { get; set; } public int NumberOfOrders { get; set; } public double Quality { get; set; } public string Month { get; set; } + public DateTime BakeDate { get; set; } } public void Create(String filePath) { var pastries = new List { - new Pastry("Croissant", 150, 60.2, "Apr"), - new Pastry("Croissant", 250, 50.42, "May"), - new Pastry("Croissant", 134, 22.12, "June"), - new Pastry("Doughnut", 250, 89.99, "Apr"), - new Pastry("Doughnut", 225, 70, "May"), - new Pastry("Doughnut", 210, 75.33, "June"), - new Pastry("Bearclaw", 134, 10.24, "Apr"), - new Pastry("Bearclaw", 184, 33.33, "May"), - new Pastry("Bearclaw", 124, 25, "June"), - new Pastry("Danish", 394, -20.24, "Apr"), - new Pastry("Danish", 190, 60, "May"), - new Pastry("Danish", 221, 24.76, "June"), + 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)), // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. - new Pastry("Scone", 135, 0, "Apr"), - new Pastry("SconE", 122, 5.19, "May"), - new Pastry("SCONE", 243, 44.2, "June") + 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)), }; using (var wb = new XLWorkbook()) @@ -170,6 +172,28 @@ #endregion Pivot table with subtotals disabled + #region Pivot Table with filter + + ptSheet = wb.Worksheets.Add("pvtFilter"); + + pt = ptSheet.PivotTables.AddNew("pvtFilter", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Month"); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + + pt.ReportFilters.Add("Name") + .AddSelectedValue("Scone") + .AddSelectedValue("Doughnut"); + + pt.ReportFilters.Add("Quality") + .AddSelectedValue(5.19); + + pt.ReportFilters.Add("BakeDate") + .AddSelectedValue(new DateTime(2017, 05, 03)); + + #endregion Pivot Table with filter + wb.SaveAs(filePath); } } diff --git a/ClosedXML_Examples/Styles/StyleFill.cs b/ClosedXML_Examples/Styles/StyleFill.cs index a5d0800..8a85791 100644 --- a/ClosedXML_Examples/Styles/StyleFill.cs +++ b/ClosedXML_Examples/Styles/StyleFill.cs @@ -1,11 +1,8 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples.Styles { - - public class StyleFill : IXLExample { public void Create(String filePath) @@ -19,12 +16,12 @@ ws.Cell(++ro, co + 1).Value = "BackgroundColor = Red"; ws.Cell(ro, co).Style.Fill.BackgroundColor = XLColor.Red; - ws.Cell(++ro, co + 1).Value = "PatternType = DarkTrellis; PatternColor = Orange; PatternBackgroundColor = Blue"; + ws.Cell(++ro, co + 1).Value = "PatternType = DarkTrellis; PatternColor = Orange; BackgroundColor = Blue"; ws.Cell(ro, co).Style.Fill.PatternType = XLFillPatternValues.DarkTrellis; ws.Cell(ro, co).Style.Fill.PatternColor = XLColor.Orange; - ws.Cell(ro, co).Style.Fill.PatternBackgroundColor = XLColor.Blue; + ws.Cell(ro, co).Style.Fill.BackgroundColor = XLColor.Blue; workbook.SaveAs(filePath); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Examples/Tables/UsingTables.cs b/ClosedXML_Examples/Tables/UsingTables.cs index 639a0a0..24f4903 100644 --- a/ClosedXML_Examples/Tables/UsingTables.cs +++ b/ClosedXML_Examples/Tables/UsingTables.cs @@ -21,7 +21,7 @@ ws.Name = "Contacts Table"; var firstCell = ws.FirstCellUsed(); var lastCell = ws.LastCellUsed(); - var range = ws.Range(firstCell.Address, lastCell.Address); + var range = ws.Range(firstCell.Address, lastCell.CellRight().Address); range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) // We want to use a theme for table, not the hard coded format of the BasicTable @@ -30,6 +30,15 @@ range.Column(4).Style.NumberFormat.NumberFormatId = 15; range.Column(5).Style.NumberFormat.Format = "$ #,##0"; + // Add a field + range.Column(6).FirstCell().SetValue("Age"); + var c = range.Column(6).FirstCell().CellBelow(); + c.Style.NumberFormat.SetFormat("0.00"); + c.FormulaA1 = "=(DATE(2017, 10, 3) - E3) / 365"; + + c.CopyTo(c.CellBelow()) + .CopyTo(c.CellBelow().CellBelow()); + var table = range.CreateTable(); // You can also use range.AsTable() if you want to // manipulate the range as a table but don't want // to create the table in the worksheet. @@ -40,8 +49,10 @@ // Just for fun let's add the text "Sum Of Income" to the totals row table.Field(0).TotalsRowLabel = "Sum Of Income"; + table.Field("Age").TotalsRowFunction = XLTotalsRowFunction.Average; + // Copy all the headers - Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; + Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 3; Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; foreach (var cell in table.HeadersRow().Cells()) @@ -82,7 +93,7 @@ namesTable.ShowAutoFilter = false; ws.Columns().AdjustToContents(); - ws.Columns("A,G,I").Width = 3; + ws.Columns("A,H,J").Width = 3; wb.SaveAs(filePath); } diff --git a/ClosedXML_Tests/Examples/ColumnsTests.cs b/ClosedXML_Tests/Examples/ColumnsTests.cs index 1ecd215..b5007bf 100644 --- a/ClosedXML_Tests/Examples/ColumnsTests.cs +++ b/ClosedXML_Tests/Examples/ColumnsTests.cs @@ -30,5 +30,11 @@ { TestHelper.RunTestExample(@"Columns\DeletingColumns.xlsx"); } + + //[Test] // Not working yet + public void InsertColumns() + { + TestHelper.RunTestExample(@"Columns\InsertColumns.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/RowsTests.cs b/ClosedXML_Tests/Examples/RowsTests.cs index 3042957..446b812 100644 --- a/ClosedXML_Tests/Examples/RowsTests.cs +++ b/ClosedXML_Tests/Examples/RowsTests.cs @@ -24,5 +24,11 @@ { TestHelper.RunTestExample(@"Rows\RowSettings.xlsx"); } + + //[Test] // Not working yet + public void InsertRows() + { + TestHelper.RunTestExample(@"Rows\InsertRows.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index a18e509..b7b7151 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -129,5 +129,20 @@ Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)")); } + + [Test] + public void Hyperlink() + { + XLHyperlink hl; + hl = XLWorkbook.EvaluateExpr("HYPERLINK(\"http://github.com/ClosedXML/ClosedXML\")") as XLHyperlink; + Assert.IsNotNull(hl); + Assert.AreEqual("http://github.com/ClosedXML/ClosedXML", hl.ExternalAddress.ToString()); + Assert.AreEqual(string.Empty, hl.Tooltip); + + hl = XLWorkbook.EvaluateExpr("HYPERLINK(\"mailto:jsmith@github.com\", \"jsmith@github.com\")") as XLHyperlink; + Assert.IsNotNull(hl); + Assert.AreEqual("mailto:jsmith@github.com", hl.ExternalAddress.ToString()); + Assert.AreEqual("jsmith@github.com", hl.Tooltip); + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index c3cd7ce..8c95b10 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -217,6 +217,209 @@ Assert.AreEqual(0.7, actual, tolerance); } + /// + /// refers to Example 1 from the Excel documentation, + /// + /// + /// + /// + [TestCase(63000, "SUMIF(A1:A4,\">160000\", B1:B4)")] + [TestCase(900000, "SUMIF(A1:A4,\">160000\")")] + [TestCase(21000, "SUMIF(A1:A4, 300000, B1:B4)")] + [TestCase(28000, "SUMIF(A1:A4, \">\" &C1, B1:B4)")] + public void SumIf_ReturnsCorrectValues_ReferenceExample1FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = 100000; + ws.Cell(1, 2).Value = 7000; + ws.Cell(2, 1).Value = 200000; + ws.Cell(2, 2).Value = 14000; + ws.Cell(3, 1).Value = 300000; + ws.Cell(3, 2).Value = 21000; + ws.Cell(4, 1).Value = 400000; + ws.Cell(4, 2).Value = 28000; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to Example 2 from the Excel documentation, + /// + /// + /// + /// + [TestCase(2000, "SUMIF(A2:A7,\"Fruits\", C2:C7)")] + [TestCase(12000, "SUMIF(A2:A7,\"Vegetables\", C2:C7)")] + [TestCase(4300, "SUMIF(B2:B7, \"*es\", C2:C7)")] + [TestCase(400, "SUMIF(A2:A7, \"\", C2:C7)")] + public void SumIf_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(2, 1).Value = "Vegetables"; + ws.Cell(3, 1).Value = "Vegetables"; + ws.Cell(4, 1).Value = "Fruits"; + ws.Cell(5, 1).Value = ""; + ws.Cell(6, 1).Value = "Vegetables"; + ws.Cell(7, 1).Value = "Fruits"; + + ws.Cell(2, 2).Value = "Tomatoes"; + ws.Cell(3, 2).Value = "Celery"; + ws.Cell(4, 2).Value = "Oranges"; + ws.Cell(5, 2).Value = "Butter"; + ws.Cell(6, 2).Value = "Carrots"; + ws.Cell(7, 2).Value = "Apples"; + + ws.Cell(2, 3).Value = 2300; + ws.Cell(3, 3).Value = 5500; + ws.Cell(4, 3).Value = 800; + ws.Cell(5, 3).Value = 400; + ws.Cell(6, 3).Value = 4200; + ws.Cell(7, 3).Value = 1200; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// 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. + /// + /// + /// + /// + [TestCase(63000, "SUMIFS(B1:B4, \">160000\", A1:A4)")] + [TestCase(21000, "SUMIFS(B1:B4, 300000, A1:A4)")] + [TestCase(28000, "SUMIFS(B1:B4, \">\" &C1, A1:A4)")] + public void SumIfs_ReturnsCorrectValues_ReferenceExampleForSumIf1FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = 100000; + ws.Cell(1, 2).Value = 7000; + ws.Cell(2, 1).Value = 200000; + ws.Cell(2, 2).Value = 14000; + ws.Cell(3, 1).Value = 300000; + ws.Cell(3, 2).Value = 21000; + ws.Cell(4, 1).Value = 400000; + ws.Cell(4, 2).Value = 28000; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to Example 2 to SumIf from the Excel documentation. + /// As SumIfs should behave the same if called with three parameters, we can take that example here again. + /// + /// + /// + /// + [TestCase(2000, "SUMIFS(C2:C7, \"Fruits\", A2:A7)")] + [TestCase(12000, "SUMIFS(C2:C7, \"Vegetables\", A2:A7)")] + [TestCase(4300, "SUMIFS(C2:C7, \"*es\", B2:B7)")] + [TestCase(400, "SUMIFS(C2:C7, \"\", A2:A7)")] + public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(2, 1).Value = "Vegetables"; + ws.Cell(3, 1).Value = "Vegetables"; + ws.Cell(4, 1).Value = "Fruits"; + ws.Cell(5, 1).Value = ""; + ws.Cell(6, 1).Value = "Vegetables"; + ws.Cell(7, 1).Value = "Fruits"; + + ws.Cell(2, 2).Value = "Tomatoes"; + ws.Cell(3, 2).Value = "Celery"; + ws.Cell(4, 2).Value = "Oranges"; + ws.Cell(5, 2).Value = "Butter"; + ws.Cell(6, 2).Value = "Carrots"; + ws.Cell(7, 2).Value = "Apples"; + + ws.Cell(2, 3).Value = 2300; + ws.Cell(3, 3).Value = 5500; + ws.Cell(4, 3).Value = 800; + ws.Cell(5, 3).Value = 400; + ws.Cell(6, 3).Value = 4200; + ws.Cell(7, 3).Value = 1200; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to example data and formula to SumIfs in the Excel documentation, + /// + /// + [TestCase(20, "=SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")")] + [TestCase(30, "=SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")")] + public void SumIfs_ReturnsCorrectValues_ReferenceExampleFromMicrosoft( + int result, + string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = 5; + ws.Cell(1, 2).Value = "Apples"; + ws.Cell(1, 3).Value = "Tom"; + + ws.Cell(2, 1).Value = 4; + ws.Cell(2, 2).Value = "Apples"; + ws.Cell(2, 3).Value = "Sarah"; + + ws.Cell(3, 1).Value = 15; + ws.Cell(3, 2).Value = "Artichokes"; + ws.Cell(3, 3).Value = "Tom"; + + ws.Cell(4, 1).Value = 3; + ws.Cell(4, 2).Value = "Artichokes"; + ws.Cell(4, 3).Value = "Sarah"; + + ws.Cell(5, 1).Value = 22; + ws.Cell(5, 2).Value = "Bananas"; + ws.Cell(5, 3).Value = "Tom"; + + ws.Cell(6, 1).Value = 12; + ws.Cell(6, 2).Value = "Bananas"; + ws.Cell(6, 3).Value = "Sarah"; + + ws.Cell(7, 1).Value = 10; + ws.Cell(7, 2).Value = "Carrots"; + ws.Cell(7, 3).Value = "Tom"; + + ws.Cell(8, 1).Value = 33; + ws.Cell(8, 2).Value = "Carrots"; + ws.Cell(8, 3).Value = "Sarah"; + } + } + + [Test] public void SumProduct() { diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs index 2550868..a0bb2a2 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -91,6 +91,117 @@ Assert.AreEqual(24, value); } + [TestCase(@"=COUNTIF(Data!E:E, ""J*"")", 13)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i*"")", 21)] + [TestCase(@"=COUNTIF(Data!E:E, ""*in*"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i*l"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*i?e*"")", 9)] + [TestCase(@"=COUNTIF(Data!E:E, ""*o??s*"")", 10)] + [TestCase(@"=COUNTIF(Data!X1:X1000, """")", 1000)] + [TestCase(@"=COUNTIF(Data!E1:E44, """")", 1)] + public void CountIf_ConditionWithWildcards(string formula, int expectedResult) + { + var ws = workbook.Worksheets.First(); + + int value = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, value); + } + + [TestCase("x", @"=COUNTIF(A1:A1, ""?"")", 1)] + [TestCase("x", @"=COUNTIF(A1:A1, ""~?"")", 0)] + [TestCase("?", @"=COUNTIF(A1:A1, ""~?"")", 1)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~?"")", 0)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~~~?"")", 1)] + [TestCase("?", @"=COUNTIF(A1:A1, ""~~?"")", 0)] + [TestCase("~?", @"=COUNTIF(A1:A1, ""~~?"")", 1)] + [TestCase("~x", @"=COUNTIF(A1:A1, ""~~?"")", 1)] + [TestCase("*", @"=COUNTIF(A1:A1, ""~*"")", 1)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~*"")", 0)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~~~*"")", 1)] + [TestCase("*", @"=COUNTIF(A1:A1, ""~~*"")", 0)] + [TestCase("~*", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + [TestCase("~x", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + [TestCase("~xyz", @"=COUNTIF(A1:A1, ""~~*"")", 1)] + public void CountIf_MoreWildcards(string cellContent, string formula, int expectedResult) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = cellContent; + + Assert.AreEqual(expectedResult, (double)ws.Evaluate(formula)); + } + } + + [TestCase("=COUNTIFS(B1:D1, \"=Yes\")", 1)] + [TestCase("=COUNTIFS(B1:B4, \"=Yes\", C1:C4, \"=Yes\")", 2)] + [TestCase("= COUNTIFS(B4:D4, \"=Yes\", B2:D2, \"=Yes\")", 1)] + public void CountIfs_ReferenceExample1FromExcelDocumentations( + string formula, + int expectedOutcome) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = "Davidoski"; + ws.Cell(1, 2).Value = "Yes"; + ws.Cell(1, 3).Value = "No"; + ws.Cell(1, 4).Value = "No"; + + ws.Cell(2, 1).Value = "Burke"; + ws.Cell(2, 2).Value = "Yes"; + ws.Cell(2, 3).Value = "Yes"; + ws.Cell(2, 4).Value = "No"; + + ws.Cell(3, 1).Value = "Sundaram"; + ws.Cell(3, 2).Value = "Yes"; + ws.Cell(3, 3).Value = "Yes"; + ws.Cell(3, 4).Value = "Yes"; + + ws.Cell(4, 1).Value = "Levitan"; + ws.Cell(4, 2).Value = "No"; + ws.Cell(4, 3).Value = "Yes"; + ws.Cell(4, 4).Value = "Yes"; + + Assert.AreEqual(expectedOutcome, (int)ws.Evaluate(formula)); + } + } + + [Test] + public void CountIfs_SingleCondition() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTIFS(D3:D45,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = ws.Evaluate(@"=COUNTIFS(D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = workbook.Evaluate(@"=COUNTIFS(Data!D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + } + + [TestCase(@"=COUNTIFS(Data!E:E, ""J*"")", 13)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i*"")", 21)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*in*"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i*l"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i?e*"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*o??s*"")", 10)] + [TestCase(@"=COUNTIFS(Data!X1:X1000, """")", 1000)] + [TestCase(@"=COUNTIFS(Data!E1:E44, """")", 1)] + public void CountIfs_SingleConditionWithWildcards(string formula, int expectedResult) + { + var ws = workbook.Worksheets.First(); + + int value = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, value); + } + [OneTimeTearDown] public void Dispose() { diff --git a/ClosedXML_Tests/Excel/Styles/XLFillTests.cs b/ClosedXML_Tests/Excel/Styles/XLFillTests.cs index 6177890..72abc22 100644 --- a/ClosedXML_Tests/Excel/Styles/XLFillTests.cs +++ b/ClosedXML_Tests/Excel/Styles/XLFillTests.cs @@ -26,16 +26,16 @@ [Test] public void BackgroundPatternEqualCheck() { - var fill1 = new XLFill {PatternBackgroundColor = XLColor.Blue}; - var fill2 = new XLFill {PatternBackgroundColor = XLColor.Blue}; + var fill1 = new XLFill {BackgroundColor = XLColor.Blue}; + var fill2 = new XLFill {BackgroundColor = XLColor.Blue}; Assert.IsTrue(fill1.Equals(fill2)); } [Test] public void BackgroundPatternNotEqualCheck() { - var fill1 = new XLFill {PatternBackgroundColor = XLColor.Blue}; - var fill2 = new XLFill {PatternBackgroundColor = XLColor.Red}; + var fill1 = new XLFill {BackgroundColor = XLColor.Blue}; + var fill2 = new XLFill {BackgroundColor = XLColor.Red}; Assert.IsFalse(fill1.Equals(fill2)); } @@ -48,8 +48,8 @@ var cf = worksheet.Cell(2, 2).AddConditionalFormat(); var style = cf.WhenNotBlank(); style - .Border.SetOutsideBorder(XLBorderStyleValues.Thick) - .Border.SetOutsideBorderColor(XLColor.Blue); + .Border.SetOutsideBorder(XLBorderStyleValues.Thick) + .Border.SetOutsideBorderColor(XLColor.Blue); Assert.AreEqual(style.Border.BottomBorder, XLBorderStyleValues.Thick); Assert.AreEqual(style.Border.TopBorder, XLBorderStyleValues.Thick); @@ -62,4 +62,4 @@ Assert.AreEqual(style.Border.RightBorderColor, XLColor.Blue); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 25eeb50..9d30ab8 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -566,6 +566,73 @@ } [Test] + public void TableAsDynamicEnumerable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l); + + foreach (var d in table.AsDynamicEnumerable()) + { + Assert.DoesNotThrow(() => + { + object value; + value = d.FirstColumn; + value = d.SecondColumn; + value = d.UnOrderedColumn; + value = d.SomeFieldNotProperty; + }); + } + } + } + + [Test] + public void TableAsDotNetDataTable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l).AsNativeDataTable(); + + Assert.AreEqual(4, table.Columns.Count); + Assert.AreEqual("FirstColumn", table.Columns[0].ColumnName); + Assert.AreEqual("SecondColumn", table.Columns[1].ColumnName); + Assert.AreEqual("SomeFieldNotProperty", table.Columns[2].ColumnName); + Assert.AreEqual("UnOrderedColumn", table.Columns[3].ColumnName); + + Assert.AreEqual(typeof(String), table.Columns[0].DataType); + Assert.AreEqual(typeof(String), table.Columns[1].DataType); + Assert.AreEqual(typeof(Double), table.Columns[2].DataType); + Assert.AreEqual(typeof(Double), table.Columns[3].DataType); + + var dr = table.Rows[0]; + Assert.AreEqual("b", dr["FirstColumn"]); + Assert.AreEqual("a", dr["SecondColumn"]); + Assert.AreEqual(4, dr["SomeFieldNotProperty"]); + Assert.AreEqual(999, dr["UnOrderedColumn"]); + + dr = table.Rows[1]; + Assert.AreEqual("d", dr["FirstColumn"]); + Assert.AreEqual("c", dr["SecondColumn"]); + Assert.AreEqual(5, dr["SomeFieldNotProperty"]); + Assert.AreEqual(777, dr["UnOrderedColumn"]); + } + } + + [Test] public void TestTableCellTypes() { using (var wb = new XLWorkbook()) diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx index 2ee0c8d..156af42 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx index 0b2fb27..ca26674 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx index 7221dae..1e430a1 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx index 4e069ba..e396345 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Columns/ColumnCells.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/ColumnCells.xlsx index 2e35af2..70c8e96 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/ColumnCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/ColumnCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Columns/ColumnCollection.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/ColumnCollection.xlsx index 8210ecf..be26aca 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/ColumnCollection.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/ColumnCollection.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Columns/ColumnSettings.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/ColumnSettings.xlsx index d729b73..ea65770 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/ColumnSettings.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/ColumnSettings.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx index e2ac29f..83b5a8d 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx index 5c9ae0a..775345e 100644 --- a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowHigh.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowHigh.xlsx index 8e28b85..80e81a7 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowHigh.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowHigh.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx index a9216c5..797dc0b 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx index 965c332..e58bfc1 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx index 4721b41..a0c3791 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFContains.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBar.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBar.xlsx index 4db382d..8e1cc60 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBar.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBar.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx index fa46948..8ad081e 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx index e2d4308..bfb06cd 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEndsWith.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx index a75c503..bbfa6d4 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsNumber.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx index 07e8628..4b6a481 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFEqualsString.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIconSet.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIconSet.xlsx index fb91ef7..0ef2377 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIconSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIconSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx index e57b38d..84f5931 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx index e952a7f..0677026 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsError.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx index 0d31d2a..d396c20 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx index e233171..f2a8f5b 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx index 1b3b379..22026b6 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotContains.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx index 7d1bd15..4eabc7c 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsNumber.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx index 531411d..72e5631 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotEqualsString.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx index 9b07849..c808164 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotError.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStartsWith.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStartsWith.xlsx index e19f30e..cc432eb 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStartsWith.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStartsWith.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx index 9894731..de67d1e 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Delete/DeleteFewWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Delete/DeleteFewWorksheets.xlsx index 0bfcae5..df718c9 100644 --- a/ClosedXML_Tests/Resource/Examples/Delete/DeleteFewWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Delete/DeleteFewWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Delete/RemoveRows.xlsx b/ClosedXML_Tests/Resource/Examples/Delete/RemoveRows.xlsx index 3a4822a..31ef954 100644 --- a/ClosedXML_Tests/Resource/Examples/Delete/RemoveRows.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Delete/RemoveRows.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx index d2a90ba..40e2d5d 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx index 3ec78e0..ecd11ca 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx index 30a397a..a27f51c 100644 --- a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx index c5abda2..cd861ef 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx index 3dcec19..7c3a2ec 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx index ec9111e..0d98696 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContentsWithAutoFilter.xlsx index c71500f..5b1a0db 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 e18cf50..9765c38 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/BasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx index 7109c2e..fc3c33f 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx index 3f4195a..6c5eccc 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx index ec4c484..d7b945b 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx index d791985..d628b28 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx index 961f52c..f313701 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index 28c4b9b..03b9b20 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index d7b9afd..7b3e5a2 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 f451273..e86beba 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/DataValidation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx index d2e119b..c5979d8 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx index c49d20a..513d951 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx index f1dde09..131fba0 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/FormulasWithEvaluation.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/FreezePanes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/FreezePanes.xlsx index 65e4082..ac2714c 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/Misc/HideSheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/HideSheets.xlsx index 084e291..2587ee6 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/HideSheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/HideSheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx index 1f99477..792f768 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx index d724e36..a4ab6f6 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx index d9d1a57..f5db9c4 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx index 939001d..368f016 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx index e5120d4..3860d72 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx index 2cbaac9..f0c9c03 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Outline.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Outline.xlsx index 4590850..5f01c13 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Outline.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Outline.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx index 235f17a..1017afd 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/RightToLeft.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/SheetProtection.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/SheetProtection.xlsx index abeeb15..65936c4 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/SheetProtection.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/SheetProtection.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx index 9102f0a..4d275a2 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx index a1aa8d0..1a15711 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx index b02ab54..d988cd1 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/TabColors.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/TabColors.xlsx index 3b08f53..5c55455 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/TabColors.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/TabColors.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx index 7e28ec6..d9fba2c 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx index 2e7c839..12aad44 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx index 5464356..f0d7374 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/Margins.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/Margins.xlsx index 062d427..2f6805b 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/Margins.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/Margins.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/Page.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/Page.xlsx index 13debb0..c55e060 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/Page.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/Page.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/SheetTab.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/SheetTab.xlsx index e03a6b5..30bfbcc 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/SheetTab.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/SheetTab.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/Sheets.xlsx index fd83a80..f91eecc 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/PageSetup/TwoPages.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx index 03fb5e2..6096e00 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 247daba..d6f65c7 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/AddingRowToTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx index 95291f8..014ea30 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx index 5e18500..95daf5a 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx index 064601a..faa24e2 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx index 6148e44..741e75f 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx index 8e79c44..31c5290 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx index 1fbbe5a..ed8b923 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingColumns.xlsx index dabfb5f..0b17544 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx index 9588f7d..bfafa65 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/MultipleRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/MultipleRanges.xlsx index e3717c5..b0621bc 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/MultipleRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/MultipleRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx index 2b450d6..a4a86da 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx index adecfee..b4e6aeb 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx index 453328f..1c6a1ed 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index bd2c3d6..e426613 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx index 08344e9..36b889d 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx index 4d136e2..c7f029d 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx index 037fc86..0303805 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx index e12dfc5..ee46beb 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Rows/RowCells.xlsx b/ClosedXML_Tests/Resource/Examples/Rows/RowCells.xlsx index 2f0be53..f7a57c8 100644 --- a/ClosedXML_Tests/Resource/Examples/Rows/RowCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Rows/RowCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Rows/RowCollection.xlsx b/ClosedXML_Tests/Resource/Examples/Rows/RowCollection.xlsx index 5c98025..8855354 100644 --- a/ClosedXML_Tests/Resource/Examples/Rows/RowCollection.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Rows/RowCollection.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Rows/RowSettings.xlsx b/ClosedXML_Tests/Resource/Examples/Rows/RowSettings.xlsx index 69a0c15..457bb99 100644 --- a/ClosedXML_Tests/Resource/Examples/Rows/RowSettings.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Rows/RowSettings.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/DefaultStyles.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/DefaultStyles.xlsx index 86e20d3..9e9a109 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/DefaultStyles.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/DefaultStyles.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/PurpleWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/PurpleWorksheet.xlsx index 4422537..ea00b0c 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/PurpleWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/PurpleWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx index abe42a8..b2670c0 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx index 2af3891..4d52bd6 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleFill.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFill.xlsx index 389f0f6..9e19202 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFill.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFill.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx index a9c9728..af7d4b4 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleNumberFormat.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleNumberFormat.xlsx index 5a5ee64..23184a3 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleNumberFormat.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleNumberFormat.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx index 472f826..f10a5f6 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleWorksheet.xlsx index 407d189..2201621 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/UsingColors.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/UsingColors.xlsx index f7421af..2ada7d9 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/UsingColors.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/UsingColors.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx index 86fe3e6..b334a6d 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx index 90ef8fc..b83ccde 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/UsingRichText.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx index 89de679..05cfa2c 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx index dad3954..5d223ac 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx index 288d1fe..39402fe 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index bf85115..646d9d0 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -75,36 +75,23 @@ using (var wb = new XLWorkbook(filePath1)) wb.SaveAs(filePath2, true, evaluateFormulae); - bool success = true; -#pragma warning disable 162 - try + if (CompareWithResources) + { - //Compare - // ReSharper disable ConditionIsAlwaysTrueOrFalse - if (CompareWithResources) - // ReSharper restore ConditionIsAlwaysTrueOrFalse - + string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); + using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) + using (var streamActual = File.OpenRead(filePath2)) { - string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); - using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) - using (var streamActual = File.OpenRead(filePath2)) - { - string message; - success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); - var formattedMessage = - String.Format( - "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", - filePath2, resourcePath, message); + string message; + var success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); + var formattedMessage = + String.Format( + "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", + filePath2, resourcePath, message); - Assert.IsTrue(success, formattedMessage); - } + Assert.IsTrue(success, formattedMessage); } } - finally - { - //if (success && File.Exists(filePath)) File.Delete(filePath); - } -#pragma warning restore 162 } public static string GetResourcePath(string filePartName)