diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs index 88ca907..f13ce40 100644 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -1,25 +1,30 @@ using System; + namespace ClosedXML.Excel { - using System.Collections.Generic; public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterDynamicType { AboveAverage, BelowAverage } - public enum XLTopBottomPart { Top, Bottom} + + public enum XLTopBottomPart { Top, Bottom } + public interface IXLBaseAutoFilter { Boolean Enabled { get; set; } IXLRange Range { get; set; } + IXLBaseAutoFilter Set(IXLRangeBase range); + IXLBaseAutoFilter Clear(); IXLFilterColumn Column(String column); + IXLFilterColumn Column(Int32 column); IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + Boolean Sorted { get; set; } XLSortOrder SortOrder { get; set; } Int32 SortColumn { get; set; } - - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 12e0995..2d1368d 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using ClosedXML.Excel.CalcEngine.Functions; using System; using System.Collections; @@ -64,7 +65,7 @@ ce.RegisterFunction("SUBTOTAL", 2, 255, Subtotal); ce.RegisterFunction("SUM", 1, int.MaxValue, Sum); ce.RegisterFunction("SUMIF", 2, 3, SumIf); - //ce.RegisterFunction("SUMPRODUCT", 1, SumProduct); + ce.RegisterFunction("SUMPRODUCT", 1, 30, SumProduct); ce.RegisterFunction("SUMSQ", 1, 255, SumSq); //ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1); //ce.RegisterFunction("SUMX2PY2", SumX2PY2, 1); @@ -261,6 +262,37 @@ return tally.Sum(); } + private static object SumProduct(List p) + { + // all parameters should be IEnumerable + if (p.Any(param => !(param is IEnumerable))) + throw new NoValueAvailableException(); + + var counts = p.Cast().Select(param => + { + int i = 0; + foreach (var item in param) + i++; + return i; + }) + .Distinct(); + + // All parameters should have the same length + if (counts.Count() > 1) + throw new NoValueAvailableException(); + + var values = p + .Cast() + .Select(range => range.Cast().ToList()); + + return Enumerable.Range(0, counts.Single()) + .Aggregate(0d, (t, i) => + t + values.Aggregate(1d, + (product, list) => product * list[i] + ) + ); + } + private static object Tan(List p) { return Math.Tan(p[0]); diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index e085c64..0c335d5 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -691,7 +691,8 @@ public IXLTable InsertTable(DataTable data, string tableName, bool createTable) { - if (data == null) return null; + if (data == null || data.Columns.Count == 0) + return null; if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); @@ -992,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()) { @@ -1186,7 +1187,7 @@ public Boolean IsMerged() { - return Worksheet.Internals.MergedRanges.Any(r => r.Contains(this)); + return Worksheet.Internals.MergedRanges.Contains(this); } public Boolean IsEmpty() @@ -1276,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); + }); } } @@ -1734,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; @@ -2243,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); @@ -2447,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); @@ -2751,31 +2760,16 @@ { get { - return this.Worksheet.Range(SearchCurrentRegion(this.AsRange())); + return this.Worksheet.Range(FindCurrentRegion(this.AsRange())); } } - internal IEnumerable SurroundingCells(IXLRangeBase range) - { - var rowNumbers = Enumerable.Range(range.RangeAddress.FirstAddress.RowNumber - 1, range.RangeAddress.LastAddress.RowNumber - range.RangeAddress.FirstAddress.RowNumber + 3); - var columnNumbers = Enumerable.Range(range.RangeAddress.FirstAddress.ColumnNumber - 1, range.RangeAddress.LastAddress.ColumnNumber - range.RangeAddress.FirstAddress.ColumnNumber + 3); - - // Cartesian product - var addresses = rowNumbers.SelectMany(row => columnNumbers, (row, column) => new { row, column }); - - addresses = addresses.Where(a => a.row >= 1 && a.row <= XLHelper.MaxRowNumber && a.column >= 1 && a.column <= XLHelper.MaxColumnNumber) - // Don't select range cells self - .Where(a => !range.Cells().Any(rc => a.row == rc.Address.RowNumber && a.column == rc.Address.ColumnNumber)); - - return addresses.Select(a => this.Worksheet.Cell(a.row, a.column)); - } - - internal IXLRangeAddress SearchCurrentRegion(IXLRangeBase range) + internal IXLRangeAddress FindCurrentRegion(IXLRangeBase range) { var rangeAddress = range.RangeAddress; - var filledCells = SurroundingCells(this.Worksheet.Range(rangeAddress)) - .Where(c => !c.IsEmpty(false, false)) + var filledCells = range + .SurroundingCells(c => !(c as XLCell).IsEmpty(false, false)) .Concat(this.Worksheet.Range(rangeAddress).Cells()); var grownRangeAddress = new XLRangeAddress( @@ -2783,11 +2777,10 @@ new XLAddress(this.Worksheet, filledCells.Max(c => c.Address.RowNumber), filledCells.Max(c => c.Address.ColumnNumber), false, false) ); - if (rangeAddress.Equals(grownRangeAddress)) return this.Worksheet.Range(grownRangeAddress).RangeAddress; else - return SearchCurrentRegion(this.Worksheet.Range(grownRangeAddress)); + return FindCurrentRegion(this.Worksheet.Range(grownRangeAddress)); } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs index 703d006..3b172f8 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverterExtension.cs @@ -1,5 +1,8 @@ using ClosedXML.Extensions; +using DocumentFormat.OpenXml.Office.Excel; using DocumentFormat.OpenXml.Office2010.Excel; +using System; +using System.Linq; namespace ClosedXML.Excel { @@ -22,11 +25,25 @@ MinLength = 0, MaxLength = 100, Gradient = false, - AxisPosition = DataBarAxisPositionValues.Middle + AxisPosition = DataBarAxisPositionValues.Middle, + ShowValue = !cf.ShowBarOnly }; - var cfMin = new ConditionalFormattingValueObject { Type = ConditionalFormattingValueObjectTypeValues.AutoMin }; - var cfMax = new ConditionalFormattingValueObject() { Type = ConditionalFormattingValueObjectTypeValues.AutoMax }; + ConditionalFormattingValueObjectTypeValues cfMinType = Convert(cf.ContentTypes[1].ToOpenXml()); + var cfMin = new ConditionalFormattingValueObject { Type = cfMinType }; + if (cf.Values.Any() && cf.Values[1]?.Value != null) + { + cfMin.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMin.Append(new Formula() { Text = cf.Values[1].Value }); + } + + ConditionalFormattingValueObjectTypeValues cfMaxType = Convert(cf.ContentTypes[2].ToOpenXml()); + var cfMax = new ConditionalFormattingValueObject { Type = cfMaxType }; + if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null) + { + cfMax.Type = ConditionalFormattingValueObjectTypeValues.Numeric; + cfMax.Append(new Formula() { Text = cf.Values[2].Value }); + } var barAxisColor = new BarAxisColor { Rgb = XLColor.Black.Color.ToHex() }; @@ -46,5 +63,26 @@ return conditionalFormattingRule; } + + private ConditionalFormattingValueObjectTypeValues Convert(DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues obj) + { + switch (obj) + { + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Max: + return ConditionalFormattingValueObjectTypeValues.AutoMax; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Min: + return ConditionalFormattingValueObjectTypeValues.AutoMin; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Number: + return ConditionalFormattingValueObjectTypeValues.Numeric; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percent: + return ConditionalFormattingValueObjectTypeValues.Percent; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Percentile: + return ConditionalFormattingValueObjectTypeValues.Percentile; + case DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValueObjectValues.Formula: + return ConditionalFormattingValueObjectTypeValues.Formula; + default: + throw new NotImplementedException(); + } + } } } diff --git a/ClosedXML/Excel/ConditionalFormats/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/Drawings/Style/IXLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs index 94b6850..878f23b 100644 --- a/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/IXLDrawingFont.cs @@ -17,6 +17,6 @@ IXLDrawingStyle SetFontColor(XLColor value); IXLDrawingStyle SetFontName(String value); IXLDrawingStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); - + IXLDrawingStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs index 886e249..744d781 100644 --- a/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs +++ b/ClosedXML/Excel/Drawings/Style/XLDrawingFont.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -29,6 +26,7 @@ public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLDrawingStyle SetBold() { @@ -120,5 +118,10 @@ return _style; } + public IXLDrawingStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _style; + } } } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 91bcafd..03f76d1 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; namespace ClosedXML.Excel { @@ -117,20 +118,22 @@ IXLCell TargetCell { get; set; } IXLRange SourceRange { get; set; } + IEnumerable SourceRangeFieldsAvailable { get; } + Boolean MergeAndCenterWithLabels { get; set; } // MergeItem Int32 RowLabelIndent { get; set; } // Indent - XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown + XLFilterAreaOrder FilterAreaOrder { get; set; } // PageOverThenDown Int32 FilterFieldsPageWrap { get; set; } // PageWrap String ErrorValueReplacement { get; set; } // ErrorCaption String EmptyCellReplacement { get; set; } // MissingCaption - Boolean AutofitColumns { get; set; } //UseAutoFormatting - Boolean PreserveCellFormatting { get; set; } // PreserveFormatting - - Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals - Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals + Boolean AutofitColumns { get; set; } //UseAutoFormatting + Boolean PreserveCellFormatting { get; set; } // PreserveFormatting + + Boolean ShowGrandTotalsRows { get; set; } // RowGrandTotals + Boolean ShowGrandTotalsColumns { get; set; } // ColumnGrandTotals Boolean FilteredItemsInSubtotals { get; set; } // Subtotal filtered page items - Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters - Boolean UseCustomListsForSorting { get; set; } // CustomListSort + Boolean AllowMultipleFilters { get; set; } // MultipleFieldFilters + Boolean UseCustomListsForSorting { get; set; } // CustomListSort Boolean ShowExpandCollapseButtons { get; set; } Boolean ShowContextualTooltips { get; set; } diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index 4e09f16..ef16caf 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -32,6 +32,9 @@ } public IXLPivotValue Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; _pivotValues.Add(customName, pivotValue); diff --git a/ClosedXML/Excel/PivotTables/XLPivotFields.cs b/ClosedXML/Excel/PivotTables/XLPivotFields.cs index 19accb7..d35e0e8 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotFields.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotFields.cs @@ -4,9 +4,16 @@ namespace ClosedXML.Excel { - public class XLPivotFields : IXLPivotFields + internal class XLPivotFields : IXLPivotFields { + private readonly Dictionary _pivotFields = new Dictionary(); + private readonly IXLPivotTable _pivotTable; + + internal XLPivotFields(IXLPivotTable pivotTable) + { + this._pivotTable = pivotTable; + } public IXLPivotField Add(String sourceName) { @@ -15,6 +22,9 @@ public IXLPivotField Add(String sourceName, String customName) { + if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) + throw new ArgumentOutOfRangeException(nameof(sourceName), String.Format("The column '{0}' does not appear in the source range.", sourceName)); + var pivotField = new XLPivotField(sourceName) { CustomName = customName }; _pivotFields.Add(sourceName, pivotField); return pivotField; @@ -49,7 +59,8 @@ { var selectedItem = _pivotFields.Select((item, index) => new { Item = item, Position = index }).FirstOrDefault(i => i.Item.Key == pf.SourceName); if (selectedItem == null) - throw new IndexOutOfRangeException("Invalid field name."); + throw new ArgumentNullException(nameof(pf), "Invalid field name."); + return selectedItem.Position; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index acb4a84..e1232cb 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -10,10 +10,10 @@ public XLPivotTable() { - Fields = new XLPivotFields(); - ReportFilters = new XLPivotFields(); - ColumnLabels=new XLPivotFields(); - RowLabels = new XLPivotFields(); + Fields = new XLPivotFields(this); + ReportFilters = new XLPivotFields(this); + ColumnLabels=new XLPivotFields(this); + RowLabels = new XLPivotFields(this); Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; @@ -22,6 +22,10 @@ public IXLCell TargetCell { get; set; } public IXLRange SourceRange { get; set; } + public IEnumerable SourceRangeFieldsAvailable + { + get { return this.SourceRange.FirstRow().Cells().Select(c => c.GetString()); } + } public IXLPivotFields Fields { get; private set; } public IXLPivotFields ReportFilters { get; private set; } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 064d975..b0dd238 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -262,10 +262,71 @@ IXLAutoFilter SetAutoFilter(); + IXLAutoFilter SetAutoFilter(Boolean value); + IXLDataValidation SetDataValidation(); IXLConditionalFormat AddConditionalFormat(); void Select(); + + /// + /// Grows this the current range by one cell to each side + /// + IXLRangeBase Grow(); + + /// + /// Grows this the current range by the specified number of cells to each side. + /// + /// The grow count. + /// + IXLRangeBase Grow(Int32 growCount); + + /// + /// Shrinks this current range by one cell. + /// + IXLRangeBase Shrink(); + + /// + /// Shrinks the current range by the specified number of cells from each side. + /// + /// The shrink count. + /// + IXLRangeBase Shrink(Int32 shrinkCount); + + /// + /// Returns the intersection of this range with another range on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns the set of cells surrounding the current range. + /// + /// The predicate to apply on the resulting set of cells. + IXLCells SurroundingCells(Func predicate = null); + + /// + /// Calculates the union of two ranges on the same worksheet. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + /// The union + /// + IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); + + /// + /// Returns all cells in the current range that are not in the other range. + /// + /// The other range. + /// Predicate applied to this range's cells. + /// Predicate applied to the other range's cells. + /// + IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 190d473..ab227be 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -803,14 +803,18 @@ if (absRow <= 0 || absRow > XLHelper.MaxRowNumber) { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - XLHelper.MaxRowNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber) + ); } if (absColumn <= 0 || absColumn > XLHelper.MaxColumnNumber) { - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", - XLHelper.MaxColumnNumber)); + throw new ArgumentOutOfRangeException( + nameof(cellAddressInRange), + String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber) + ); } var cell = Worksheet.Internals.CellsCollection.GetCell(absRow, @@ -1210,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; } } } @@ -1227,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(); } @@ -1449,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; } } } @@ -1466,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(); } @@ -1757,8 +1771,16 @@ public IXLAutoFilter SetAutoFilter() { - using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return SetAutoFilter(true); + } + + public IXLAutoFilter SetAutoFilter(Boolean value) + { + if (value) + using (var asRange = AsRange()) + return Worksheet.AutoFilter.Set(asRange); + else + return Worksheet.AutoFilter.Clear(); } #region Sort @@ -2067,5 +2089,104 @@ { Worksheet.SelectedRanges.Add(AsRange()); } + + public IXLRangeBase Grow() + { + return Grow(1); + } + + public IXLRangeBase Grow(int growCount) + { + var firstRow = Math.Max(1, this.RangeAddress.FirstAddress.RowNumber - growCount); + var firstColumn = Math.Max(1, this.RangeAddress.FirstAddress.ColumnNumber - growCount); + + var lastRow = Math.Min(XLHelper.MaxRowNumber, this.RangeAddress.LastAddress.RowNumber + growCount); + var lastColumn = Math.Min(XLHelper.MaxColumnNumber, this.RangeAddress.LastAddress.ColumnNumber + growCount); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Shrink() + { + return Shrink(1); + } + + public IXLRangeBase Shrink(int shrinkCount) + { + var firstRow = this.RangeAddress.FirstAddress.RowNumber + shrinkCount; + var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + shrinkCount; + + var lastRow = this.RangeAddress.LastAddress.RowNumber - shrinkCount; + var lastColumn = this.RangeAddress.LastAddress.ColumnNumber - shrinkCount; + + if (firstRow > lastRow || firstColumn > lastColumn) + return null; + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLRangeBase Intersection(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return null; + + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return null; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + var intersectionCells = this.Cells(c => thisRangePredicate(c) && otherRange.Cells(otherRangePredicate).Contains(c)); + + if (!intersectionCells.Any()) + return null; + + var firstRow = intersectionCells.Min(c => c.Address.RowNumber); + var firstColumn = intersectionCells.Min(c => c.Address.ColumnNumber); + + var lastRow = intersectionCells.Max(c => c.Address.RowNumber); + var lastColumn = intersectionCells.Max(c => c.Address.ColumnNumber); + + return this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); + } + + public IXLCells SurroundingCells(Func predicate = null) + { + var cells = new XLCells(false, false, predicate); + this.Grow().Cells(c => !this.Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Union(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(thisRangePredicate).Concat(otherRange.Cells(otherRangePredicate)).Distinct().ForEach(c => cells.Add(c as XLCell)); + return cells; + } + + public IXLCells Difference(IXLRangeBase otherRange, Func thisRangePredicate = null, Func otherRangePredicate = null) + { + if (otherRange == null) + return this.Cells(thisRangePredicate); + + var cells = new XLCells(false, false); + if (!this.Worksheet.Equals(otherRange.Worksheet)) + return cells; + + if (thisRangePredicate == null) thisRangePredicate = c => true; + if (otherRangePredicate == null) otherRangePredicate = c => true; + + this.Cells(c => thisRangePredicate(c) && !otherRange.Cells(otherRangePredicate).Contains(c)).ForEach(c => cells.Add(c as XLCell)); + return cells; + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index b783762..2fc41c0 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -72,6 +72,11 @@ } } + public Boolean Contains(IXLCell cell) + { + return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(cell)); + } + public Boolean Contains(IXLRange range) { return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range)); diff --git a/ClosedXML/Excel/RichText/IXLPhonetics.cs b/ClosedXML/Excel/RichText/IXLPhonetics.cs index 0330b61..67cd96d 100644 --- a/ClosedXML/Excel/RichText/IXLPhonetics.cs +++ b/ClosedXML/Excel/RichText/IXLPhonetics.cs @@ -17,6 +17,7 @@ IXLPhonetics SetFontColor(XLColor value); IXLPhonetics SetFontName(String value); IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLPhonetics SetFontCharSet(XLFontCharSet value); IXLPhonetics Add(String text, Int32 start, Int32 end); IXLPhonetics ClearText(); diff --git a/ClosedXML/Excel/RichText/IXLRichString.cs b/ClosedXML/Excel/RichText/IXLRichString.cs index b41b537..a090460 100644 --- a/ClosedXML/Excel/RichText/IXLRichString.cs +++ b/ClosedXML/Excel/RichText/IXLRichString.cs @@ -10,7 +10,7 @@ public interface IXLRichString: IXLFontBase, IEquatable, IXLWithRichString { String Text { get; set; } - + IXLRichString SetBold(); IXLRichString SetBold(Boolean value); IXLRichString SetItalic(); IXLRichString SetItalic(Boolean value); @@ -22,5 +22,6 @@ IXLRichString SetFontColor(XLColor value); IXLRichString SetFontName(String value); IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLRichString SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/RichText/XLPhonetics.cs b/ClosedXML/Excel/RichText/XLPhonetics.cs index 2cf1e02..b048267 100644 --- a/ClosedXML/Excel/RichText/XLPhonetics.cs +++ b/ClosedXML/Excel/RichText/XLPhonetics.cs @@ -1,6 +1,6 @@ using System; -using System.Linq; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel { @@ -8,7 +8,8 @@ { private readonly List _phonetics = new List(); - readonly IXLFontBase _defaultFont; + private readonly IXLFontBase _defaultFont; + public XLPhonetics(IXLFontBase defaultFont) { _defaultFont = defaultFont; @@ -22,7 +23,7 @@ _defaultFont = defaultFont; Type = defaultPhonetics.Type; Alignment = defaultPhonetics.Alignment; - + this.CopyFont(defaultPhonetics); } @@ -36,28 +37,52 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } - public IXLPhonetics SetBold() { Bold = true; return this; } public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } - public IXLPhonetics SetItalic() { Italic = true; return this; } public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } - public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } - public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetBold() { Bold = true; return this; } + + public IXLPhonetics SetBold(Boolean value) { Bold = value; return this; } + + public IXLPhonetics SetItalic() { Italic = true; return this; } + + public IXLPhonetics SetItalic(Boolean value) { Italic = value; return this; } + + public IXLPhonetics SetUnderline() { Underline = XLFontUnderlineValues.Single; return this; } + + public IXLPhonetics SetUnderline(XLFontUnderlineValues value) { Underline = value; return this; } + + public IXLPhonetics SetStrikethrough() { Strikethrough = true; return this; } + + public IXLPhonetics SetStrikethrough(Boolean value) { Strikethrough = value; return this; } + public IXLPhonetics SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) { VerticalAlignment = value; return this; } - public IXLPhonetics SetShadow() { Shadow = true; return this; } public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + + public IXLPhonetics SetShadow() { Shadow = true; return this; } + + public IXLPhonetics SetShadow(Boolean value) { Shadow = value; return this; } + public IXLPhonetics SetFontSize(Double value) { FontSize = value; return this; } + public IXLPhonetics SetFontColor(XLColor value) { FontColor = value; return this; } + public IXLPhonetics SetFontName(String value) { FontName = value; return this; } + public IXLPhonetics SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLPhonetics SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } + public IXLPhonetics Add(String text, Int32 start, Int32 end) { _phonetics.Add(new XLPhonetic(text, start, end)); return this; } + public IXLPhonetics ClearText() { _phonetics.Clear(); return this; } + public IXLPhonetics ClearFont() { this.CopyFont(_defaultFont); @@ -70,6 +95,7 @@ public XLPhoneticType Type { get; set; } public IXLPhonetics SetAlignment(XLPhoneticAlignment phoneticAlignment) { Alignment = phoneticAlignment; return this; } + public IXLPhonetics SetType(XLPhoneticType phoneticType) { Type = phoneticType; return this; } public IEnumerator GetEnumerator() @@ -104,8 +130,7 @@ && FontSize == other.FontSize && FontColor.Equals(other.FontColor) && FontName == other.FontName - && FontFamilyNumbering == other.FontFamilyNumbering - ; + && FontFamilyNumbering == other.FontFamilyNumbering; } } } diff --git a/ClosedXML/Excel/RichText/XLRichString.cs b/ClosedXML/Excel/RichText/XLRichString.cs index b9e9cdd..9837fdb 100644 --- a/ClosedXML/Excel/RichText/XLRichString.cs +++ b/ClosedXML/Excel/RichText/XLRichString.cs @@ -33,6 +33,7 @@ public XLColor FontColor { get; set; } public String FontName { get; set; } public XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + public XLFontCharSet FontCharSet { get; set; } public IXLRichString SetBold() { Bold = true; return this; } public IXLRichString SetBold(Boolean value) { Bold = value; return this; } public IXLRichString SetItalic() { Italic = true; return this; } public IXLRichString SetItalic(Boolean value) { Italic = value; return this; } @@ -44,6 +45,7 @@ public IXLRichString SetFontColor(XLColor value) { FontColor = value; return this; } public IXLRichString SetFontName(String value) { FontName = value; return this; } public IXLRichString SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return this; } + public IXLRichString SetFontCharSet(XLFontCharSet value) { FontCharSet = value; return this; } public Boolean Equals(IXLRichString other) { diff --git a/ClosedXML/Excel/Style/IXLFont.cs b/ClosedXML/Excel/Style/IXLFont.cs index 2262d35..a996b62 100644 --- a/ClosedXML/Excel/Style/IXLFont.cs +++ b/ClosedXML/Excel/Style/IXLFont.cs @@ -19,7 +19,7 @@ } public enum XLFontFamilyNumberingValues - { + { NotApplicable = 0, Roman = 1, Swiss = 2, @@ -28,18 +28,131 @@ Decorative = 5 } + public enum XLFontCharSet + { + /// + /// ASCII character set. + /// + Ansi = 0, + + /// + /// System default character set. + /// + Default = 1, + + /// + /// Symbol character set. + /// + Symbol = 2, + + /// + /// Characters used by Macintosh. + /// + Mac = 77, + + /// + /// Japanese character set. + /// + ShiftJIS = 128, + + /// + /// Korean character set. + /// + Hangul = 129, + + /// + /// Another common spelling of the Korean character set. + /// + Hangeul = 129, + + /// + /// Korean character set. + /// + Johab = 130, + + /// + /// Chinese character set used in mainland China. + /// + GB2312 = 134, + + /// + /// Chinese character set used mostly in Hong Kong SAR and Taiwan. + /// + ChineseBig5 = 136, + + /// + /// Greek character set. + /// + Greek = 161, + + /// + /// Turkish character set. + /// + Turkish = 162, + + /// + /// Vietnamese character set. + /// + Vietnamese = 163, + + /// + /// Hebrew character set. + /// + Hebrew = 177, + + /// + /// Arabic character set. + /// + Arabic = 178, + + /// + /// Baltic character set. + /// + Baltic = 186, + + /// + /// Russian character set. + /// + Russian = 204, + + /// + /// Thai character set. + /// + Thai = 222, + + /// + /// Eastern European character set. + /// + EastEurope = 238, + + /// + /// Extended ASCII character set used with disk operating system (DOS) and some Microsoft Windows fonts. + /// + Oem = 255 + } + public interface IXLFont : IXLFontBase, IEquatable { IXLStyle SetBold(); IXLStyle SetBold(Boolean value); + IXLStyle SetItalic(); IXLStyle SetItalic(Boolean value); + IXLStyle SetUnderline(); IXLStyle SetUnderline(XLFontUnderlineValues value); + IXLStyle SetStrikethrough(); IXLStyle SetStrikethrough(Boolean value); + IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value); + IXLStyle SetShadow(); IXLStyle SetShadow(Boolean value); + IXLStyle SetFontSize(Double value); + IXLStyle SetFontColor(XLColor value); + IXLStyle SetFontName(String value); + IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + IXLStyle SetFontCharSet(XLFontCharSet value); } } diff --git a/ClosedXML/Excel/Style/IXLFontBase.cs b/ClosedXML/Excel/Style/IXLFontBase.cs index 11d8a72..629cab1 100644 --- a/ClosedXML/Excel/Style/IXLFontBase.cs +++ b/ClosedXML/Excel/Style/IXLFontBase.cs @@ -2,7 +2,7 @@ namespace ClosedXML.Excel { - + public interface IXLFontBase { @@ -16,7 +16,6 @@ XLColor FontColor { get; set; } String FontName { get; set; } XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } - - + XLFontCharSet FontCharSet { get; set; } } } diff --git a/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/Excel/Style/XLFont.cs index 3c52c20..87915b4 100644 --- a/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/Excel/Style/XLFont.cs @@ -9,6 +9,7 @@ private Boolean _bold; private XLColor _fontColor; private XLFontFamilyNumberingValues _fontFamilyNumbering; + private XLFontCharSet _fontCharSet; private String _fontName; private Double _fontSize; private Boolean _italic; @@ -37,6 +38,7 @@ _fontColor = defaultFont.FontColor; _fontName = defaultFont.FontName; _fontFamilyNumbering = defaultFont.FontFamilyNumbering; + _fontCharSet = defaultFont.FontCharSet; if (useDefaultModify) { @@ -52,6 +54,7 @@ FontColorModified = d.FontColorModified; FontNameModified = d.FontNameModified; FontFamilyNumberingModified = d.FontFamilyNumberingModified; + FontCharSetModified = d.FontCharSetModified; } } @@ -105,7 +108,7 @@ _underline = value; UnderlineModified = true; } - + } } @@ -236,6 +239,23 @@ } } + public Boolean FontCharSetModified { get; set; } + public XLFontCharSet FontCharSet + { + get { return _fontCharSet; } + set + { + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontCharSet = value); + else + { + _fontCharSet = value; + FontCharSetModified = true; + } + } + } + public IXLStyle SetBold() { Bold = true; @@ -326,6 +346,12 @@ return _container.Style; } + public IXLStyle SetFontCharSet(XLFontCharSet value) + { + FontCharSet = value; + return _container.Style; + } + public Boolean Equals(IXLFont other) { var otherF = other as XLFont; @@ -397,4 +423,4 @@ ^ (Int32)FontFamilyNumbering; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index c014def..1177678 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -51,60 +51,62 @@ { get { - return _defaultStyle ?? (_defaultStyle = new XLStyle(null) - { - Font = new XLFont(null, null) - { - Bold = false, - Italic = false, - Underline = XLFontUnderlineValues.None, - Strikethrough = false, - VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, - FontSize = 11, - FontColor = XLColor.FromArgb(0, 0, 0), - FontName = "Calibri", - FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss - }, - Fill = new XLFill(null) - { - BackgroundColor = XLColor.FromIndex(64), - PatternType = XLFillPatternValues.None, - PatternColor = XLColor.FromIndex(64) - }, - Border = new XLBorder(null, null) - { - BottomBorder = XLBorderStyleValues.None, - DiagonalBorder = XLBorderStyleValues.None, - DiagonalDown = false, - DiagonalUp = false, - LeftBorder = XLBorderStyleValues.None, - RightBorder = XLBorderStyleValues.None, - TopBorder = XLBorderStyleValues.None, - BottomBorderColor = XLColor.Black, - DiagonalBorderColor = XLColor.Black, - LeftBorderColor = XLColor.Black, - RightBorderColor = XLColor.Black, - TopBorderColor = XLColor.Black - }, - NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, - Alignment = new XLAlignment(null) - { - Indent = 0, - Horizontal = XLAlignmentHorizontalValues.General, - JustifyLastLine = false, - ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, - RelativeIndent = 0, - ShrinkToFit = false, - TextRotation = 0, - Vertical = XLAlignmentVerticalValues.Bottom, - WrapText = false - }, - Protection = new XLProtection(null) - { - Locked = true, - Hidden = false - } - }); + return _defaultStyle + ?? (_defaultStyle = new XLStyle(null) + { + Font = new XLFont(null, null) + { + Bold = false, + Italic = false, + Underline = XLFontUnderlineValues.None, + Strikethrough = false, + VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, + FontSize = 11, + FontColor = XLColor.FromArgb(0, 0, 0), + FontName = "Calibri", + FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss, + FontCharSet = XLFontCharSet.Default + }, + Fill = new XLFill(null) + { + BackgroundColor = XLColor.FromIndex(64), + PatternType = XLFillPatternValues.None, + PatternColor = XLColor.FromIndex(64) + }, + Border = new XLBorder(null, null) + { + BottomBorder = XLBorderStyleValues.None, + DiagonalBorder = XLBorderStyleValues.None, + DiagonalDown = false, + DiagonalUp = false, + LeftBorder = XLBorderStyleValues.None, + RightBorder = XLBorderStyleValues.None, + TopBorder = XLBorderStyleValues.None, + BottomBorderColor = XLColor.Black, + DiagonalBorderColor = XLColor.Black, + LeftBorderColor = XLColor.Black, + RightBorderColor = XLColor.Black, + TopBorderColor = XLColor.Black + }, + NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, + Alignment = new XLAlignment(null) + { + Indent = 0, + Horizontal = XLAlignmentHorizontalValues.General, + JustifyLastLine = false, + ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, + RelativeIndent = 0, + ShrinkToFit = false, + TextRotation = 0, + Vertical = XLAlignmentVerticalValues.Bottom, + WrapText = false + }, + Protection = new XLProtection(null) + { + Locked = true, + Hidden = false + } + }); } } @@ -116,24 +118,24 @@ get { var defaultPageOptions = new XLPageSetup(null, null) - { - PageOrientation = XLPageOrientation.Default, - Scale = 100, - PaperSize = XLPaperSize.LetterPaper, - Margins = new XLMargins - { - Top = 0.75, - Bottom = 0.5, - Left = 0.75, - Right = 0.75, - Header = 0.5, - Footer = 0.75 - }, - ScaleHFWithDocument = true, - AlignHFWithMargins = true, - PrintErrorValue = XLPrintErrorValues.Displayed, - ShowComments = XLShowCommentsValues.None - }; + { + PageOrientation = XLPageOrientation.Default, + Scale = 100, + PaperSize = XLPaperSize.LetterPaper, + Margins = new XLMargins + { + Top = 0.75, + Bottom = 0.5, + Left = 0.75, + Right = 0.75, + Header = 0.5, + Footer = 0.75 + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None + }; return defaultPageOptions; } } @@ -143,10 +145,10 @@ get { return new XLOutline(null) - { - SummaryHLocation = XLOutlineSummaryHLocation.Right, - SummaryVLocation = XLOutlineSummaryVLocation.Bottom - }; + { + SummaryHLocation = XLOutlineSummaryHLocation.Right, + SummaryVLocation = XLOutlineSummaryVLocation.Bottom + }; } } @@ -323,20 +325,20 @@ private void InitializeTheme() { Theme = new XLTheme - { - Text1 = XLColor.FromHtml("#FF000000"), - Background1 = XLColor.FromHtml("#FFFFFFFF"), - Text2 = XLColor.FromHtml("#FF1F497D"), - Background2 = XLColor.FromHtml("#FFEEECE1"), - Accent1 = XLColor.FromHtml("#FF4F81BD"), - Accent2 = XLColor.FromHtml("#FFC0504D"), - Accent3 = XLColor.FromHtml("#FF9BBB59"), - Accent4 = XLColor.FromHtml("#FF8064A2"), - Accent5 = XLColor.FromHtml("#FF4BACC6"), - Accent6 = XLColor.FromHtml("#FFF79646"), - Hyperlink = XLColor.FromHtml("#FF0000FF"), - FollowedHyperlink = XLColor.FromHtml("#FF800080") - }; + { + Text1 = XLColor.FromHtml("#FF000000"), + Background1 = XLColor.FromHtml("#FFFFFFFF"), + Text2 = XLColor.FromHtml("#FF1F497D"), + Background2 = XLColor.FromHtml("#FFEEECE1"), + Accent1 = XLColor.FromHtml("#FF4F81BD"), + Accent2 = XLColor.FromHtml("#FFC0504D"), + Accent3 = XLColor.FromHtml("#FF9BBB59"), + Accent4 = XLColor.FromHtml("#FF8064A2"), + Accent5 = XLColor.FromHtml("#FF4BACC6"), + Accent6 = XLColor.FromHtml("#FFF79646"), + Hyperlink = XLColor.FromHtml("#FF0000FF"), + FollowedHyperlink = XLColor.FromHtml("#FF800080") + }; } internal XLColor GetXLColor(XLThemeColor themeColor) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 008f99a..3d3386b 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1440,11 +1440,14 @@ 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()); + 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()); + var runFont = fontSource.Elements().FirstOrDefault(); if (runFont != null) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 1b83829..9e8f1a1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2060,6 +2060,12 @@ EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true) }; + if (pt.ClassicPivotTableLayout) + { + pivotTableDefinition.Compact = false; + pivotTableDefinition.CompactData = false; + } + if (pt.EmptyCellReplacement != null) { pivotTableDefinition.ShowMissing = true; @@ -2148,6 +2154,12 @@ IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; + if (pt.ClassicPivotTableLayout) + { + pf.Outline = false; + pf.Compact = false; + } + switch (pt.Subtotals) { case XLPivotSubtotals.DoNotShow: @@ -3608,6 +3620,10 @@ ? new FontFamilyNumbering { Val = (Int32)fontInfo.Font.FontFamilyNumbering } : null; + var fontCharSet = (fontInfo.Font.FontCharSetModified || ignoreMod) && fontInfo.Font.FontCharSet != XLFontCharSet.Default + ? new FontCharSet { Val = (Int32)fontInfo.Font.FontCharSet } + : null; + if (bold != null) font.AppendChild(bold); if (italic != null) @@ -3628,6 +3644,8 @@ font.AppendChild(fontName); if (fontFamilyNumbering != null) font.AppendChild(fontFamilyNumbering); + if (fontCharSet != null) + font.AppendChild(fontCharSet); return font; } @@ -4517,6 +4535,17 @@ WorksheetExtensionList worksheetExtensionList = worksheetPart.Worksheet.Elements().First(); cm.SetElement(XLWSContentManager.XLWSContents.WorksheetExtensionList, worksheetExtensionList); + var conditionalFormattings = worksheetExtensionList.Descendants().SingleOrDefault(); + if (conditionalFormattings == null || !conditionalFormattings.Any()) + { + WorksheetExtension worksheetExtension1 = new WorksheetExtension { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }; + worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + worksheetExtensionList.Append(worksheetExtension1); + + conditionalFormattings = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattings(); + worksheetExtension1.Append(conditionalFormattings); + } + foreach (var cfGroup in exlst .GroupBy( c => c.Range.RangeAddress.ToStringRelative(false), @@ -4527,16 +4556,13 @@ { foreach (var xlConditionalFormat in cfGroup.CfList.Cast()) { - var conditionalFormattingRule = worksheetExtensionList.Descendants() + var conditionalFormattingRule = conditionalFormattings.Descendants() .SingleOrDefault(r => r.Id == xlConditionalFormat.Id.WrapInBraces()); if (conditionalFormattingRule != null) { - WorksheetExtension worksheetExtension = conditionalFormattingRule.Ancestors().SingleOrDefault(); - worksheetExtensionList.RemoveChild(worksheetExtension); + var conditionalFormat = conditionalFormattingRule.Ancestors().SingleOrDefault(); + conditionalFormattings.RemoveChild(conditionalFormat); } - WorksheetExtension worksheetExtension1 = new WorksheetExtension { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }; - worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); - var conditionalFormattings = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormattings(); var conditionalFormatting = new DocumentFormat.OpenXml.Office2010.Excel.ConditionalFormatting(); conditionalFormatting.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); @@ -4545,9 +4571,6 @@ conditionalFormatting.Append(referenceSequence); conditionalFormattings.Append(conditionalFormatting); - worksheetExtension1.Append(conditionalFormattings); - - worksheetExtensionList.Append(worksheetExtension1); } } } diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 699d251..6cde6da 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -263,6 +263,7 @@ font.FontColor = sourceFont.FontColor; font.FontName = sourceFont.FontName; font.FontFamilyNumbering = sourceFont.FontFamilyNumbering; + font.FontCharSet = sourceFont.FontCharSet; } } diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index 9087063..a90c1b1 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -531,16 +531,27 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue(-1) + ws.Cell(1, 1).SetValue(-1) .CellBelow().SetValue(1) .CellBelow().SetValue(2) .CellBelow().SetValue(3); - ws.RangeUsed() + ws.Range(ws.Cell(1, 1), ws.Cell(4, 1)) .AddConditionalFormat() .DataBar(XLColor.Green, XLColor.Red, showBarOnly: false) .LowestValue() - .Maximum(XLCFContentType.Percent, "100"); + .HighestValue(); + + ws.Cell(1,3).SetValue(-20) + .CellBelow().SetValue(40) + .CellBelow().SetValue(-60) + .CellBelow().SetValue(30); + + ws.Range(ws.Cell(1, 3), ws.Cell(4, 3)) + .AddConditionalFormat() + .DataBar(XLColor.Green, XLColor.Red, showBarOnly: true) + .Minimum(XLCFContentType.Number, -100) + .Maximum(XLCFContentType.Number, 100); workbook.SaveAs(filePath); } @@ -676,4 +687,4 @@ workbook.SaveAs(filePath); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Examples/Misc/AutoFilter.cs b/ClosedXML_Examples/Misc/AutoFilter.cs index 21b8402..bb119b2 100644 --- a/ClosedXML_Examples/Misc/AutoFilter.cs +++ b/ClosedXML_Examples/Misc/AutoFilter.cs @@ -50,11 +50,11 @@ ws.Cell("A4").Value = "Dagny"; ws.RangeUsed().SetAutoFilter(); - - // Your can turn off the autofilter in three ways: - // 1) worksheet.AutoFilterRange.SetAutoFilter(false) - // 2) worksheet.AutoFilterRange = null - // 3) Pick any range in the worksheet and call range.SetAutoFilter(false); + + // Your can turn off the autofilter by: + // 1) worksheet.AutoFilter.Clear() + // 2) worksheet.SetAutoFilter(false) + // 3) Pick any range in the worksheet and call the above methods on the range wb.SaveAs(filePath); } diff --git a/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML_Examples/Styles/StyleFont.cs index 7da4adf..6fed30f 100644 --- a/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML_Examples/Styles/StyleFont.cs @@ -1,6 +1,5 @@ +using ClosedXML.Excel; using System; -using ClosedXML.Excel; - namespace ClosedXML_Examples.Styles { @@ -23,6 +22,11 @@ ws.Cell(++ro, co).Value = "FontFamilyNumbering - Script"; ws.Cell(ro, co).Style.Font.FontFamilyNumbering = XLFontFamilyNumberingValues.Script; + ws.Cell(++ro, co).Value = "FontCharSet - العربية التنضيد"; + ws.Cell(ro, co).Style + .Font.SetFontName("Arabic Typesetting") + .Font.SetFontCharSet(XLFontCharSet.Arabic); + ws.Cell(++ro, co).Value = "FontName - Stencil"; ws.Cell(ro, co).Style.Font.FontName = "Stencil"; diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 25dff84..da8bde2 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -72,5 +72,27 @@ ws.AutoFilter.Clear(); Assert.That(!ws.AutoFilter.Enabled); } + + [Test] + public void CanClearAutoFilter2() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("AutoFilter"); + ws.Cell("A1").Value = "Names"; + ws.Cell("A2").Value = "John"; + ws.Cell("A3").Value = "Hank"; + ws.Cell("A4").Value = "Dagny"; + + ws.SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(); + Assert.That(ws.AutoFilter.Enabled); + + ws.RangeUsed().SetAutoFilter(false); + Assert.That(!ws.AutoFilter.Enabled); + } + } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index 5012bb5..b9003d2 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -1,6 +1,8 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; +using System.Linq; namespace ClosedXML_Tests.Excel.CalcEngine { @@ -119,5 +121,23 @@ actual = (double)XLWorkbook.EvaluateExpr(@"MOD(6.2, 1.1)"); Assert.AreEqual(0.7, actual, tolerance); } + + [Test] + public void SumProduct() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + ws.FirstCell().Value = Enumerable.Range(1, 10); + ws.FirstCell().CellRight().Value = Enumerable.Range(1, 10).Reverse(); + + Assert.AreEqual(2, ws.Evaluate("SUMPRODUCT(A2)")); + Assert.AreEqual(55, ws.Evaluate("SUMPRODUCT(A1:A10)")); + Assert.AreEqual(220, ws.Evaluate("SUMPRODUCT(A1:A10, B1:B10)")); + + Assert.Throws(() => ws.Evaluate("SUMPRODUCT(A1:A10, B1:B5)")); + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 348b213..28a7b6f 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -1,7 +1,7 @@ -using System; -using System.Linq; using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Linq; namespace ClosedXML_Tests { @@ -106,7 +106,7 @@ IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( - namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] {"John", "Hank", "Dagny"})); + namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); } [Test] @@ -184,7 +184,119 @@ // memoryStream.Close(); // } - //} + + [Test] + public void GrowRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.AreEqual("A1:B2", ws.Cell("A1").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:B3", ws.Cell("A2").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("A1:C2", ws.Cell("B1").AsRange().Grow().RangeAddress.ToString()); + + Assert.AreEqual("E4:G6", ws.Cell("F5").AsRange().Grow().RangeAddress.ToString()); + Assert.AreEqual("D3:H7", ws.Cell("F5").AsRange().Grow(2).RangeAddress.ToString()); + Assert.AreEqual("A1:DB105", ws.Cell("F5").AsRange().Grow(100).RangeAddress.ToString()); + } + } + + [Test] + public void ShrinkRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + Assert.Null(ws.Cell("A1").AsRange().Shrink()); + Assert.Null(ws.Range("B2:C3").Shrink()); + Assert.AreEqual("C3:C3", ws.Range("B2:D4").Shrink().RangeAddress.ToString()); + Assert.AreEqual("K11:P16", ws.Range("A1:Z26").Shrink(10).RangeAddress.ToString()); + + // Grow and shrink back + Assert.AreEqual("Z26:Z26", ws.Cell("Z26").AsRange().Grow(10).Shrink(10).RangeAddress.ToString()); + } + } + + [Test] + public void Intersection() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual("D9:G11", ws.Range("B9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:G11", ws.Range("E9:I11").Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Cell("E9").AsRange().Intersection(ws.Range("D4:G16")).RangeAddress.ToString()); + Assert.AreEqual("E9:E9", ws.Range("D4:G16").Intersection(ws.Cell("E9").AsRange()).RangeAddress.ToString()); + + Assert.Null(ws.Cell("A1").AsRange().Intersection(ws.Cell("C3").AsRange())); + + Assert.Null(ws.Range("A1:C3").Intersection(null)); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.Null(ws.Intersection(otherWs)); + Assert.Null(ws.Cell("A1").AsRange().Intersection(otherWs.Cell("A2").AsRange())); + } + } + + [Test] + public void Union() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(64, ws.Range("B9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(58, ws.Range("E9:I11").Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Cell("E9").AsRange().Union(ws.Range("D4:G16")).Count()); + Assert.AreEqual(52, ws.Range("D4:G16").Union(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(2, ws.Cell("A1").AsRange().Union(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Union(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Union(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Union(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void Difference() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(12, ws.Range("B9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(6, ws.Range("E9:I11").Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(0, ws.Cell("E9").AsRange().Difference(ws.Range("D4:G16")).Count()); + Assert.AreEqual(51, ws.Range("D4:G16").Difference(ws.Cell("E9").AsRange()).Count()); + + Assert.AreEqual(1, ws.Cell("A1").AsRange().Difference(ws.Cell("C3").AsRange()).Count()); + + Assert.AreEqual(9, ws.Range("A1:C3").Difference(null).Count()); + + var otherWs = wb.AddWorksheet("Sheet2"); + Assert.False(ws.Difference(otherWs).Any()); + Assert.False(ws.Cell("A1").AsRange().Difference(otherWs.Cell("A2").AsRange()).Any()); + } + } + + [Test] + public void SurroundingCells() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + Assert.AreEqual(3, ws.FirstCell().AsRange().SurroundingCells().Count()); + Assert.AreEqual(8, ws.Cell("C3").AsRange().SurroundingCells().Count()); + Assert.AreEqual(16, ws.Range("C3:D6").AsRange().SurroundingCells().Count()); + + Assert.AreEqual(0, ws.Range("C3:D6").AsRange().SurroundingCells(c => !c.IsEmpty()).Count()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index adbe14b..25eeb50 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -52,6 +52,20 @@ } [Test] + public void PreventAddingOfEmptyDataTable() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var dt = new DataTable(); + var table = ws.FirstCell().InsertTable(dt); + + Assert.AreEqual(null, table); + } + } + + [Test] public void CanSaveTableCreatedFromSingleRow() { using (var wb = new XLWorkbook()) diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDataBarNegative.xlsx index a3f79f6..fa46948 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/Styles/StyleFont.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx index 0dadd37..a9c9728 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleFont.xlsx Binary files differ