diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 3212d2e..17fdf43 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -19,11 +19,11 @@ public interface IXLCell { /// - /// Gets or sets the cell's value. To get a strongly typed object use the method GetValue<T>. - /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from this cell. - /// If the object is a range ClosedXML will copy the range starting from this cell. + /// Gets or sets the cell's value. To get or set a strongly typed value, use the GetValue<T> and SetValue methods. + /// ClosedXML will try to detect the data type through parsing. If it can't then the value will be left as a string. + /// If the object is an IEnumerable, ClosedXML will copy the collection's data into a table starting from this cell. + /// If the object is a range, ClosedXML will copy the range starting from this cell. /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. - /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. /// /// /// The object containing the value(s) to set. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index ef28e44..d2b88c2 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -993,7 +993,7 @@ { //Note: We have to check if the cell is part of a merged range. If so we have to clear the whole range //Checking if called from range to avoid stack overflow - if (IsMerged() && !calledFromRange) + if (!calledFromRange && IsMerged()) { using (var asRange = AsRange()) { @@ -1187,7 +1187,7 @@ public Boolean IsMerged() { - return Worksheet.Internals.MergedRanges.Any(r => r.Contains(this)); + return Worksheet.Internals.MergedRanges.Contains(this); } public Boolean IsEmpty() @@ -1277,7 +1277,10 @@ get { using (var asRange = AsRange()) - return Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange) && dv.IsDirty()); + return Worksheet.DataValidations.Any(dv => + { + using (var rngs = dv.Ranges) return dv.IsDirty() && rngs.Contains(asRange); + }); } } @@ -1735,7 +1738,8 @@ { var maxRows = asRange.RowCount(); var maxColumns = asRange.ColumnCount(); - Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns).Clear(); + using (var rng = Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns)) + rng.Clear(); } var minRow = asRange.RangeAddress.FirstAddress.RowNumber; @@ -2244,147 +2248,149 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1ColumnRegex.IsMatch(rangeAddress)) { - var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) - { - if (A1RowRegex.IsMatch(rangeAddress)) - { - var rows = rangeAddress.Split(':'); - var row1String = rows[0]; - var row2String = rows[1]; - string row1; - if (row1String[0] == '$') - { - row1 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); + using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) + { + if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber + && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) + { + if (A1RowRegex.IsMatch(rangeAddress)) + { + var rows = rangeAddress.Split(':'); + var row1String = rows[0]; + var row2String = rows[1]; + string row1; + if (row1String[0] == '$') + { + row1 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); - string row2; - if (row2String[0] == '$') - { - row2 = "$" + - (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); - } - else - row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); + string row2; + if (row2String[0] == '$') + { + row2 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) - : String.Format("{0}:{1}", row1, row2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), row1, row2) + : String.Format("{0}:{1}", row1, row2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + FirstAddress.ColumnLetter, + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress. + LastAddress.ColumnLetter, + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); @@ -2448,166 +2454,168 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1RowRegex.IsMatch(rangeAddress)) { - var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.LastAddress.ColumnNumber - && - shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber - && - shiftedRange.RangeAddress.LastAddress.RowNumber >= - matchRange.RangeAddress.LastAddress.RowNumber) - { - if (A1ColumnRegex.IsMatch(rangeAddress)) - { - var columns = rangeAddress.Split(':'); - var column1String = columns[0]; - var column2String = columns[1]; - string column1; - if (column1String[0] == '$') - { - column1 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column1String.Substring(1)) + columnsShifted, true); - } - else - { - column1 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column1String) + - columnsShifted, true); - } + using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress)) + { + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.LastAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= + matchRange.RangeAddress.LastAddress.RowNumber) + { + if (A1ColumnRegex.IsMatch(rangeAddress)) + { + var columns = rangeAddress.Split(':'); + var column1String = columns[0]; + var column2String = columns[1]; + string column1; + if (column1String[0] == '$') + { + column1 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column1String.Substring(1)) + columnsShifted, true); + } + else + { + column1 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column1String) + + columnsShifted, true); + } - string column2; - if (column2String[0] == '$') - { - column2 = "$" + - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter( - column2String.Substring(1)) + columnsShifted, true); - } - else - { - column2 = - XLHelper.GetColumnLetterFromNumber( - XLHelper.GetColumnNumberFromLetter(column2String) + - columnsShifted, true); - } + string column2; + if (column2String[0] == '$') + { + column2 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column2String.Substring(1)) + columnsShifted, true); + } + else + { + column2 = + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter(column2String) + + columnsShifted, true); + } - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) - : String.Format("{0}:{1}", column1, column2)); - } - else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.FirstAddress.ColumnNumber) - { - if (rangeAddress.Contains(':')) - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.WrapSheetNameInQuotesIfRequired(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - } - } - else - { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.WrapSheetNameInQuotesIfRequired(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - } - } - else - sb.Append(matchString); + sb.Append(useSheetName + ? String.Format("{0}!{1}:{2}", sheetName.WrapSheetNameInQuotesIfRequired(), column1, column2) + : String.Format("{0}:{1}", column1, column2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.FirstAddress.ColumnNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}", + sheetName.WrapSheetNameInQuotesIfRequired(), + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}", + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + FirstAddress.FixedRow, + matchRange.RangeAddress. + FirstAddress.FixedColumn))); + } + } + } + else + { + if (useSheetName) + { + sb.Append(String.Format("{0}!{1}:{2}", + sheetName.WrapSheetNameInQuotesIfRequired(), + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + else + { + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress, + new XLAddress(worksheetInAction, + matchRange.RangeAddress. + LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress. + LastAddress.FixedRow, + matchRange.RangeAddress. + LastAddress.FixedColumn))); + } + } + } + else + sb.Append(matchString); + } } else sb.Append(matchString); diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index 8428f7e..b47d0e7 100644 --- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -119,6 +119,7 @@ Boolean Bottom { get; } Boolean Percent { get; } + IXLConditionalFormat StopIfTrue(bool value = true); } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs new file mode 100644 index 0000000..d00f871 --- /dev/null +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFBaseConverter.cs @@ -0,0 +1,13 @@ +using ClosedXML.Utils; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal static class XLCFBaseConverter + { + public static ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority) + { + return new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, StopIfTrue = OpenXmlHelper.GetBooleanValue(((XLConditionalFormat)cf).StopIfTrueInternal, false) }; + } + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs index 98c63ef..f883286 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -12,8 +9,9 @@ { String val = GetQuoted(cf.Values[1]); - - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = cf.Operator.ToOpenXml(); var formula = new Formula(); if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual) diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs index ca1ce64..a27df4d 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs @@ -7,7 +7,7 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var colorScale = new ColorScale(); for (Int32 i = 1; i <= cf.ContentTypes.Count; i++) diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs index c12c3b8..01a4694 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,8 +8,11 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.ContainsText, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32) context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.ContainsText; + conditionalFormattingRule.Text = val; + var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; conditionalFormattingRule.Append(formula); diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs index fc84360..2186baf 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs @@ -29,6 +29,7 @@ Converters.Add(XLConditionalFormatType.DataBar, new XLCFDataBarConverter()); Converters.Add(XLConditionalFormatType.IconSet, new XLCFIconSetConverter()); } + public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, Int32 priority, XLWorkbook.SaveContext context) { return Converters[conditionalFormat.ConditionalFormatType].Convert(conditionalFormat, priority, context); diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index 3f46e38..67b19a0 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -9,7 +9,7 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var dataBar = new DataBar { ShowValue = !cf.ShowBarOnly }; @@ -39,7 +39,6 @@ dataBar.Append(conditionalFormatValueObject2); dataBar.Append(color); - conditionalFormattingRule.Append(dataBar); if (cf.Colors.Count > 1) diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs index 4c1f2f8..54364f7 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.EndsWith; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs index bdb78d2..722465d 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs @@ -1,8 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +7,7 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); var iconSet = new IconSet {ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()}; Int32 count = cf.Values.Count; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs index 0a37418..a9dac0f 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))=0" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs index 7faacff..eb65890 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs index ebebc5b..753e754 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))>0" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs index 6b46bf4..3e3c081 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.NotContains, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.NotContains; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs index 63f9ba9..108c244 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,8 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs index 20cb843..77abaf9 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,10 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.BeginsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Operator = ConditionalFormattingOperatorValues.BeginsWith; + conditionalFormattingRule.Text = val; var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs index 277144d..3ca8dbc 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -11,7 +8,11 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { UInt32 val = UInt32.Parse(cf.Values[1].Value); - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom}; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (UInt32)context.DifferentialFormats[cf.Style]; + conditionalFormattingRule.Percent = cf.Percent; + conditionalFormattingRule.Rank = val; + conditionalFormattingRule.Bottom = cf.Bottom; return conditionalFormattingRule; } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs index da68ca8..f4945bc 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs @@ -1,7 +1,3 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; using DocumentFormat.OpenXml.Spreadsheet; namespace ClosedXML.Excel @@ -10,10 +6,9 @@ { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = XLCFBaseConverter.Convert(cf, priority); + conditionalFormattingRule.FormatId = (uint)context.DifferentialFormats[cf.Style]; return conditionalFormattingRule; } - - } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index b42905d..03aa8ca 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -1,7 +1,6 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; +using ClosedXML.Utils; namespace ClosedXML.Excel { @@ -17,6 +16,7 @@ ContentTypes = new XLDictionary(); IconSetOperators = new XLDictionary(); CopyDefaultModify = copyDefaultModify; + } public XLConditionalFormat(XLConditionalFormat conditionalFormat) @@ -39,6 +39,9 @@ ReverseIconOrder = conditionalFormat.ReverseIconOrder; ShowIconOnly = conditionalFormat.ShowIconOnly; ShowBarOnly = conditionalFormat.ShowBarOnly; + StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrueInternal, true); + + } public Guid Id { get; internal set; } @@ -98,6 +101,14 @@ public Boolean ShowIconOnly { get; set; } public Boolean ShowBarOnly { get; set; } + internal bool StopIfTrueInternal { get; set; } + + public IXLConditionalFormat StopIfTrue(bool value = true) + { + StopIfTrueInternal = value; + return this; + } + public void CopyFrom(IXLConditionalFormat other) { Style = other.Style; @@ -110,6 +121,7 @@ ReverseIconOrder = other.ReverseIconOrder; ShowIconOnly = other.ShowIconOnly; ShowBarOnly = other.ShowBarOnly; + StopIfTrueInternal = ((XLConditionalFormat)other).StopIfTrueInternal; Values.Clear(); other.Values.ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value))); diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index dc00b56..c968484 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -25,11 +25,13 @@ public void Remove(Predicate predicate) { + _conditionalFormats.Where(cf=>predicate(cf)).ForEach(cf=>cf.Range.Dispose()); _conditionalFormats.RemoveAll(predicate); } public void RemoveAll() { + _conditionalFormats.ForEach(cf => cf.Range.Dispose()); _conditionalFormats.Clear(); } } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 03f76d1..027dd94 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -107,7 +107,7 @@ IXLPivotFields Fields { get; } IXLPivotFields ReportFilters { get; } - IXLPivotFields ColumnLabels { get; } + IXLPivotFields ColumnLabels { get; } IXLPivotFields RowLabels { get; } IXLPivotValues Values { get; } @@ -115,6 +115,9 @@ String Title { get; set; } String Description { get; set; } + String ColumnHeaderCaption { get; set; } + String RowHeaderCaption { get; set; } + IXLCell TargetCell { get; set; } IXLRange SourceRange { get; set; } @@ -196,6 +199,9 @@ IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value); IXLPivotTable SetEnableCellEditing(); IXLPivotTable SetEnableCellEditing(Boolean value); + IXLPivotTable SetColumnHeaderCaption(String value); + IXLPivotTable SetRowHeaderCaption(String value); + Boolean ShowRowHeaders { get; set; } Boolean ShowColumnHeaders { get; set; } Boolean ShowRowStripes { get; set; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index e1232cb..6e7c1f0 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -38,6 +38,20 @@ public String Title { get; set; } public IXLPivotTable SetTitle(String value) { Title = value; return this; } public String Description { get; set; } public IXLPivotTable SetDescription(String value) { Description = value; return this; } + public String ColumnHeaderCaption { get; set; } + public IXLPivotTable SetColumnHeaderCaption(String value) + { + ColumnHeaderCaption = value; + return this; + } + + public String RowHeaderCaption { get; set; } + public IXLPivotTable SetRowHeaderCaption(String value) + { + RowHeaderCaption = value; + return this; + } + public Boolean MergeAndCenterWithLabels { get; set; } public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } public Int32 RowLabelIndent { get; set; } public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } public XLFilterAreaOrder FilterAreaOrder { get; set; } public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index fceca26..a44e45f 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1214,7 +1214,8 @@ - model.RangeAddress.FirstAddress.RowNumber + 1; for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) { - rangeToReturn.Row(ro).Style = model.Cell(ro).Style; + using (var row = rangeToReturn.Row(ro)) + row.Style = model.Cell(ro).Style; } } } @@ -1231,13 +1232,17 @@ var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(ro) ? Worksheet.Internals.RowsCollection[ro].Style : Worksheet.Style; - rangeToReturn.Row(ro).Style = styleToUse; + using (var row = rangeToReturn.Row(ro)) + row.Style = styleToUse; } } } if (nullReturn) + { + rangeToReturn.Dispose(); return null; + } return rangeToReturn.Columns(); } @@ -1453,7 +1458,8 @@ - model.RangeAddress.FirstAddress.ColumnNumber + 1; for (Int32 co = firstCoReturned; co <= lastCoReturned; co++) { - rangeToReturn.Column(co).Style = model.Cell(co).Style; + using (var column = rangeToReturn.Column(co)) + column.Style = model.Cell(co).Style; } } } @@ -1470,14 +1476,18 @@ var styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(co) ? Worksheet.Internals.ColumnsCollection[co].Style : Worksheet.Style; - rangeToReturn.Column(co).Style = styleToUse; + using (var column = rangeToReturn.Column(co)) + column.Style = styleToUse; } } } // Skip calling .Rows() for performance reasons if required. if (nullReturn) + { + rangeToReturn.Dispose(); return null; + } return rangeToReturn.Rows(); } diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index b783762..2fc41c0 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -72,6 +72,11 @@ } } + public Boolean Contains(IXLCell cell) + { + return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(cell)); + } + public Boolean Contains(IXLRange range) { return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range)); diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 526db74..4ab4125 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -475,6 +475,13 @@ if (target != null && source != null) { var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable; + + if (!XLHelper.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.ColumnHeaderCaption ?? String.Empty))) + pt.SetColumnHeaderCaption(StringValue.ToString(pivotTableDefinition.ColumnHeaderCaption)); + + if (!XLHelper.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) + pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption)); + pt.RelId = wsPart.GetIdOfPart(pivotTablePart); pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); @@ -1439,14 +1446,11 @@ if (fontColor.HasValue) fontBase.FontColor = fontColor; - var fontFamilyNumbering = fontSource.Elements().FirstOrDefault(); + var fontFamilyNumbering = + fontSource.Elements().FirstOrDefault(); if (fontFamilyNumbering != null && fontFamilyNumbering.Val != null) - fontBase.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); - - var fontCharSet = fontSource.Elements().FirstOrDefault(); - if (fontCharSet != null && fontCharSet.Val != null) - fontBase.FontCharSet = (XLFontCharSet)Int32.Parse(fontCharSet.Val.ToString()); - + fontBase.FontFamilyNumbering = + (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); var runFont = fontSource.Elements().FirstOrDefault(); if (runFont != null) { @@ -1840,6 +1844,9 @@ foreach (var fr in conditionalFormatting.Elements()) { var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value)); + + conditionalFormat.StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false); + if (fr.FormatId != null) { LoadFont(differentialFormats[(Int32)fr.FormatId.Value].Font, conditionalFormat.Style.Font); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index d50c1d1..7a82720 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2055,33 +2055,39 @@ Name = pt.Name, CacheId = cacheId, DataCaption = "Values", - MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), + MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown), PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), - UseAutoFormatting = GetBooleanValue(pt.AutofitColumns, true), - PreserveFormatting = GetBooleanValue(pt.PreserveCellFormatting, true), - RowGrandTotals = GetBooleanValue(pt.ShowGrandTotalsRows, true), - ColumnGrandTotals = GetBooleanValue(pt.ShowGrandTotalsColumns, true), - SubtotalHiddenItems = GetBooleanValue(pt.FilteredItemsInSubtotals, true), - MultipleFieldFilters = GetBooleanValue(pt.AllowMultipleFilters, true), - CustomListSort = GetBooleanValue(pt.UseCustomListsForSorting, true), - ShowDrill = GetBooleanValue(pt.ShowExpandCollapseButtons, true), - ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true), - ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true), - ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), - GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, false), - ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true), - ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), - ShowItems = GetBooleanValue(pt.DisplayItemLabels, true), - FieldListSortAscending = GetBooleanValue(pt.SortFieldsAtoZ, true), - PrintDrill = GetBooleanValue(pt.PrintExpandCollapsedButtons, true), - ItemPrintTitles = GetBooleanValue(pt.RepeatRowLabels, true), - FieldPrintTitles = GetBooleanValue(pt.PrintTitles, true), - EnableDrill = GetBooleanValue(pt.EnableShowDetails, true) + UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, true), + PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true), + RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), + ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), + SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true), + MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), + CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), + ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), + ShowDataTips = OpenXmlHelper.GetBooleanValue(pt.ShowContextualTooltips, true), + ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true), + ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), + GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false), + ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, true), + ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), + ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true), + FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, true), + PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, true), + ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true), + FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, true), + EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true) }; + if (!String.IsNullOrEmpty(pt.ColumnHeaderCaption)) + pivotTableDefinition.ColumnHeaderCaption = StringValue.FromString(pt.ColumnHeaderCaption); + + if (!String.IsNullOrEmpty(pt.RowHeaderCaption)) + pivotTableDefinition.RowHeaderCaption = StringValue.FromString(pt.RowHeaderCaption); + if (pt.ClassicPivotTableLayout) { pivotTableDefinition.Compact = false; @@ -4440,19 +4446,19 @@ sheetProtection.Sheet = protection.Protected; if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; - sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); - sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); - sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); - sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); - sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); - sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); - sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); - sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); - sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); - sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); - sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); - sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); - sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); + sheetProtection.FormatCells = OpenXmlHelper.GetBooleanValue(!protection.FormatCells, true); + sheetProtection.FormatColumns = OpenXmlHelper.GetBooleanValue(!protection.FormatColumns, true); + sheetProtection.FormatRows = OpenXmlHelper.GetBooleanValue(!protection.FormatRows, true); + sheetProtection.InsertColumns = OpenXmlHelper.GetBooleanValue(!protection.InsertColumns, true); + sheetProtection.InsertHyperlinks = OpenXmlHelper.GetBooleanValue(!protection.InsertHyperlinks, true); + sheetProtection.InsertRows = OpenXmlHelper.GetBooleanValue(!protection.InsertRows, true); + sheetProtection.DeleteColumns = OpenXmlHelper.GetBooleanValue(!protection.DeleteColumns, true); + sheetProtection.DeleteRows = OpenXmlHelper.GetBooleanValue(!protection.DeleteRows, true); + sheetProtection.AutoFilter = OpenXmlHelper.GetBooleanValue(!protection.AutoFilter, true); + sheetProtection.PivotTables = OpenXmlHelper.GetBooleanValue(!protection.PivotTables, true); + sheetProtection.Sort = OpenXmlHelper.GetBooleanValue(!protection.Sort, true); + sheetProtection.SelectLockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectLockedCells, false); + sheetProtection.SelectUnlockedCells = OpenXmlHelper.GetBooleanValue(!protection.SelectUnlockedCells, false); } else { @@ -5140,11 +5146,6 @@ } } - private static BooleanValue GetBooleanValue(bool value, bool defaultValue) - { - return value == defaultValue ? null : new BooleanValue(value); - } - private static void CollapseColumns(Columns columns, Dictionary sheetColumns) { UInt32 lastMin = 1; diff --git a/ClosedXML/Utils/OpenXmlHelper.cs b/ClosedXML/Utils/OpenXmlHelper.cs new file mode 100644 index 0000000..6f0c7a2 --- /dev/null +++ b/ClosedXML/Utils/OpenXmlHelper.cs @@ -0,0 +1,17 @@ +using DocumentFormat.OpenXml; + +namespace ClosedXML.Utils +{ + internal static class OpenXmlHelper + { + public static BooleanValue GetBooleanValue(bool value, bool defaultValue) + { + return value == defaultValue ? null : new BooleanValue(value); + } + + public static bool GetBooleanValueAsBool(BooleanValue value, bool defaultValue) + { + return value == null ? defaultValue : value.Value; + } + } +} \ No newline at end of file diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index 27656bf..769afa2 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -663,4 +663,28 @@ workbook.SaveAs(filePath); } } + + public class CFStopIfTrue : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue(6) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.RangeUsed().AddConditionalFormat().StopIfTrue().WhenGreaterThan(5); + + + ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number); + + workbook.SaveAs(filePath); + } + } } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index fa479da..a94bb10 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -110,6 +110,8 @@ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + pt.SetRowHeaderCaption("Pastry name"); + #endregion Different kind of pivot #region Pivot table with collapsed fields @@ -162,6 +164,8 @@ pt.Subtotals = XLPivotSubtotals.DoNotShow; + pt.SetColumnHeaderCaption("Measures"); + ptSheet.Columns().AdjustToContents(); #endregion Pivot table with subtotals disabled diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 8a298ae..333c347 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -119,5 +119,11 @@ { TestHelper.RunTestExample(@"ConditionalFormatting\CFMultipleConditions.xlsx"); } + + [Test] + public void CFStopIfTrue() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFStopIfTrue.xlsx"); + } } } diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx new file mode 100644 index 0000000..9894731 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFStopIfTrue.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 369bb0c..247daba 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ