diff --git a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs index e1a87a7..99445b7 100644 --- a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs @@ -3,7 +3,7 @@ { using System.Collections.Generic; - public interface IXLAutoFilter: IDisposable + public interface IXLAutoFilter { IXLFilterColumn Column(String column); IXLFilterColumn Column(Int32 column); @@ -13,4 +13,4 @@ XLSortOrder SortOrder { get; set; } Int32 SortColumn { get; set; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index 6fb573e..84a54a8 100644 --- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -24,12 +24,6 @@ return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); } - public void Dispose() - { - if (Range != null) - Range.Dispose(); - } - #endregion #region IXLBaseAutoFilter Members @@ -115,11 +109,9 @@ SortColumn = columnToSortBy; // Recalculate shown / hidden rows - using (var rows = Range.Rows(2, Range.RowCount())) - { - foreach (IXLRangeRow row in rows) - row.WorksheetRow().Unhide(); - } + var rows = Range.Rows(2, Range.RowCount()); + foreach (IXLRangeRow row in rows) + row.WorksheetRow().Unhide(); foreach (var kp in Filters) { @@ -130,47 +122,48 @@ var isText = filter.Value is String; var isDateTime = filter.Value is DateTime; - using (var rows = Range.Rows(2, Range.RowCount())) + foreach (IXLRangeRow row in rows) { - foreach (IXLRangeRow row in rows) + //TODO : clean up filter matching - it's done in different place + Boolean match; + + if (isText) + match = condition(row.Cell(kp.Key).GetFormattedString()); + else if (isDateTime) + match = row.Cell(kp.Key).DataType == XLDataType.DateTime && + condition(row.Cell(kp.Key).GetDateTime()); + else + match = row.Cell(kp.Key).DataType == XLDataType.Number && + condition(row.Cell(kp.Key).GetDouble()); + + if (firstFilter) { - //TODO : clean up filter matching - it's done in different place - Boolean match; - - if (isText) - match = condition(row.Cell(kp.Key).GetFormattedString()); - else if (isDateTime) - match = row.Cell(kp.Key).DataType == XLDataType.DateTime && condition(row.Cell(kp.Key).GetDateTime()); + if (match) + row.WorksheetRow().Unhide(); else - match = row.Cell(kp.Key).DataType == XLDataType.Number && condition(row.Cell(kp.Key).GetDouble()); - - if (firstFilter) - { - if (match) - row.WorksheetRow().Unhide(); - else - row.WorksheetRow().Hide(); - } - else - { - if (filter.Connector == XLConnector.And) - { - if (!row.WorksheetRow().IsHidden) - { - if (match) - row.WorksheetRow().Unhide(); - else - row.WorksheetRow().Hide(); - } - } - else if (match) - row.WorksheetRow().Unhide(); - } + row.WorksheetRow().Hide(); } - firstFilter = false; + else + { + if (filter.Connector == XLConnector.And) + { + if (!row.WorksheetRow().IsHidden) + { + if (match) + row.WorksheetRow().Unhide(); + else + row.WorksheetRow().Hide(); + } + } + else if (match) + row.WorksheetRow().Unhide(); + } } + + firstFilter = false; } } + ws.ResumeEvents(); return this; } diff --git a/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs index d2cb44d..4c6bd52 100644 --- a/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs @@ -111,33 +111,31 @@ #endregion private void ApplyCustomFilter(T value, XLFilterOperator op, Func condition) - where T: IComparable + where T : IComparable { _autoFilter.Filters[_column].Add(new XLFilter - { - Value = value, - Operator = op, - Connector = _connector, - Condition = condition - }); - using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) { - foreach (IXLRangeRow row in rows) + Value = value, + Operator = op, + Connector = _connector, + Condition = condition + }); + var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); + foreach (IXLRangeRow row in rows) + { + if (_connector == XLConnector.And) { - if (_connector == XLConnector.And) + if (!row.WorksheetRow().IsHidden) { - if (!row.WorksheetRow().IsHidden) - { - if (condition(row.Cell(_column).GetValue())) - row.WorksheetRow().Unhide().Dispose(); - else - row.WorksheetRow().Hide().Dispose(); - } + if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide(); + else + row.WorksheetRow().Hide(); } - else if (condition(row.Cell(_column).GetValue())) - row.WorksheetRow().Unhide().Dispose(); } + else if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide(); } } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs index b2c151b..981532a 100644 --- a/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs @@ -20,7 +20,7 @@ public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping) { - Func condition = date2 => IsMatch(date, (DateTime)date2, dateTimeGrouping); + Func condition = date2 => IsMatch(date, (DateTime) date2, dateTimeGrouping); _autoFilter.Filters[_column].Add(new XLFilter { @@ -31,14 +31,13 @@ DateTimeGrouping = dateTimeGrouping }); - using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) + var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); + foreach (IXLRangeRow row in rows) { - foreach (IXLRangeRow row in rows) - { - if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) - row.WorksheetRow().Unhide().Dispose(); - } + if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) + row.WorksheetRow().Unhide(); } + return this; } diff --git a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs index a6cd574..eba12e7 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -274,32 +274,29 @@ private IEnumerable GetValues(int value, XLTopBottomType type, bool takeTop) { - using (var column = _autoFilter.Range.Column(_column)) + var column = _autoFilter.Range.Column(_column); + var subColumn = column.Column(2, column.CellCount()); + var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLDataType.Number); + if (takeTop) { - using (var subColumn = column.Column(2, column.CellCount())) + if (type == XLTopBottomType.Items) { - var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLDataType.Number); - if (takeTop) - { - if (type == XLTopBottomType.Items) - { - return cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct(); - } - var numerics1 = cellsUsed.Select(c => c.GetDouble()); - Int32 valsToTake1 = numerics1.Count() * value / 100; - return numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct(); - } - - if (type == XLTopBottomType.Items) - { - return cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct(); - } - - var numerics = cellsUsed.Select(c => c.GetDouble()); - Int32 valsToTake = numerics.Count() * value / 100; - return numerics.OrderBy(d => d).Take(valsToTake).Distinct(); + return cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct(); } + + var numerics1 = cellsUsed.Select(c => c.GetDouble()); + Int32 valsToTake1 = numerics1.Count() * value / 100; + return numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct(); } + + if (type == XLTopBottomType.Items) + { + return cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct(); + } + + var numerics = cellsUsed.Select(c => c.GetDouble()); + Int32 valsToTake = numerics.Count() * value / 100; + return numerics.OrderBy(d => d).Take(valsToTake).Distinct(); } private void ShowAverage(Boolean aboveAverage) @@ -353,24 +350,21 @@ private IEnumerable GetAverageValues(bool aboveAverage) { - using (var column = _autoFilter.Range.Column(_column)) + var column = _autoFilter.Range.Column(_column); + var subColumn = column.Column(2, column.CellCount()); + Double average = subColumn.CellsUsed(c => c.DataType == XLDataType.Number).Select(c => c.GetDouble()) + .Average(); + + if (aboveAverage) { - using (var subColumn = column.Column(2, column.CellCount())) - { - Double average = subColumn.CellsUsed(c => c.DataType == XLDataType.Number).Select(c => c.GetDouble()).Average(); - - if (aboveAverage) - { - return - subColumn.CellsUsed(c => c.DataType == XLDataType.Number). - Select(c => c.GetDouble()).Where(c => c > average).Distinct(); - } - - return - subColumn.CellsUsed(c => c.DataType == XLDataType.Number). - Select(c => c.GetDouble()).Where(c => c < average).Distinct(); - } + return + subColumn.CellsUsed(c => c.DataType == XLDataType.Number).Select(c => c.GetDouble()) + .Where(c => c > average).Distinct(); } + + return + subColumn.CellsUsed(c => c.DataType == XLDataType.Number).Select(c => c.GetDouble()) + .Where(c => c < average).Distinct(); } private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition, diff --git a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs index 2fe3555..fde9dba 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -38,18 +38,15 @@ Connector = XLConnector.Or }); - using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) + var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); + + foreach (IXLRangeRow row in rows) { - foreach (IXLRangeRow row in rows) + if ((isText && condition(row.Cell(_column).GetString())) || + (!isText && row.Cell(_column).DataType == XLDataType.Number && + condition(row.Cell(_column).GetValue()))) { - if ((isText && condition(row.Cell(_column).GetString())) || ( - !isText && - row.Cell(_column).DataType == - XLDataType.Number && - condition( - row.Cell(_column).GetValue())) - ) - row.WorksheetRow().Unhide().Dispose(); + row.WorksheetRow().Unhide(); } } return this; diff --git a/ClosedXML/Excel/Caching/XLRangeRepository.cs b/ClosedXML/Excel/Caching/XLRangeRepository.cs new file mode 100644 index 0000000..173322d --- /dev/null +++ b/ClosedXML/Excel/Caching/XLRangeRepository.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel.Caching +{ + internal class XLRangeRepository : XLWorkbookElementRepositoryBase + { + public XLRangeRepository(XLWorkbook workbook, Func createNew) : base(workbook, createNew) + { + } + + public XLRangeRepository(XLWorkbook workbook, Func createNew, IEqualityComparer сomparer) : base(workbook, createNew, сomparer) + { + } + } +} diff --git a/ClosedXML/Excel/Caching/XLRepositoryBase.cs b/ClosedXML/Excel/Caching/XLRepositoryBase.cs index 85cc546..db3c2f4 100644 --- a/ClosedXML/Excel/Caching/XLRepositoryBase.cs +++ b/ClosedXML/Excel/Caching/XLRepositoryBase.cs @@ -49,6 +49,7 @@ return false; } + /// /// Put the entity into the repository under the specified key if no other entity with /// the same key is presented. @@ -81,10 +82,6 @@ } } - /// - /// Create a new entity using specified key and put it into the repository or - /// return the existing entity ith this key. - /// public Tvalue GetOrCreate(Tkey key) { if (_storage.TryGetValue(key, out WeakReference cachedReference)) @@ -104,6 +101,25 @@ return Store(key, value); } + public Tvalue Replace(Tkey oldKey, Tkey newKey) + { + WeakReference cachedReference; + _storage.TryRemove(oldKey, out cachedReference); + if (cachedReference != null) + { + _storage.TryAdd(newKey, cachedReference); + return GetOrCreate(newKey); + } + + return null; + } + + public void Remove(Tkey key) + { + WeakReference _; + _storage.TryRemove(key, out _); + } + public override void Clear() { _storage.Clear(); diff --git a/ClosedXML/Excel/CalcEngine/CellRangeReference.cs b/ClosedXML/Excel/CalcEngine/CellRangeReference.cs index 1b26891..b4101ab 100644 --- a/ClosedXML/Excel/CalcEngine/CellRangeReference.cs +++ b/ClosedXML/Excel/CalcEngine/CellRangeReference.cs @@ -27,8 +27,9 @@ { var maxRow = Math.Min(_range.RangeAddress.LastAddress.RowNumber, _range.Worksheet.LastCellUsed().Address.RowNumber); var maxCol = Math.Min(_range.RangeAddress.LastAddress.ColumnNumber, _range.Worksheet.LastCellUsed().Address.ColumnNumber); - using (var trimmedRange = (XLRangeBase)_range.Worksheet.Range(_range.FirstCell().Address, new XLAddress(maxRow, maxCol, false, false))) - return trimmedRange.CellValues().GetEnumerator(); + var trimmedRange = (XLRangeBase) _range.Worksheet.Range(_range.FirstCell().Address, + new XLAddress(maxRow, maxCol, false, false)); + return trimmedRange.CellValues().GetEnumerator(); } private Boolean _evaluating; diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index a4625e0..a573c74 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -145,10 +145,7 @@ { get { - using (var asRange = AsRange()) - { - return asRange.NewDataValidation; // Call the data validation without breaking it into pieces - } + return AsRange().NewDataValidation; // Call the data validation without breaking it into pieces } } @@ -1065,12 +1062,10 @@ //Checking if called from range to avoid stack overflow if (!calledFromRange && IsMerged()) { - using (var asRange = AsRange()) - { - var firstOrDefault = Worksheet.Internals.MergedRanges.FirstOrDefault(asRange.Intersects); - if (firstOrDefault != null) - firstOrDefault.Clear(clearOptions); - } + var asRange = AsRange(); + var firstOrDefault = Worksheet.Internals.MergedRanges.FirstOrDefault(asRange.Intersects); + if (firstOrDefault != null) + firstOrDefault.Clear(clearOptions); } else { @@ -1090,8 +1085,7 @@ if (clearOptions.HasFlag(XLClearOptions.ConditionalFormats)) { - using (var r = this.AsRange()) - r.RemoveConditionalFormatting(); + AsRange().RemoveConditionalFormatting(); } if (clearOptions.HasFlag(XLClearOptions.Comments)) @@ -1448,11 +1442,8 @@ var validation = GetDataValidation(); if (validation == null) { - using (var range = this.AsRange()) - { - validation = new XLDataValidation(range); - Worksheet.DataValidations.Add(validation); - } + validation = new XLDataValidation(AsRange()); + Worksheet.DataValidations.Add(validation); } return validation; } @@ -1464,8 +1455,7 @@ public IXLConditionalFormat AddConditionalFormat() { - using (var r = AsRange()) - return r.AddConditionalFormat(); + return AsRange().AddConditionalFormat(); } public Boolean Active @@ -1864,8 +1854,7 @@ { var maxRows = asRange.RowCount(); var maxColumns = asRange.ColumnCount(); - using (var rng = Worksheet.Range(_rowNumber, _columnNumber, maxRows, maxColumns)) - rng.Clear(); + Worksheet.Range(_rowNumber, _columnNumber, maxRows, maxColumns).Clear(); } var minRow = asRange.RangeAddress.FirstAddress.RowNumber; @@ -1940,26 +1929,26 @@ private static IXLRangeBase Intersection(IXLRangeBase range, IXLRangeBase crop) { var sheet = range.Worksheet; - using (var xlRange = sheet.Range( + return sheet.Range( Math.Max(range.RangeAddress.FirstAddress.RowNumber, crop.RangeAddress.FirstAddress.RowNumber), Math.Max(range.RangeAddress.FirstAddress.ColumnNumber, crop.RangeAddress.FirstAddress.ColumnNumber), Math.Min(range.RangeAddress.LastAddress.RowNumber, crop.RangeAddress.LastAddress.RowNumber), - Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber))) - { - return sheet.Range(xlRange.RangeAddress); - } + Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber)); } private static IXLRange Relative(IXLRangeBase range, IXLRangeBase baseRange, IXLRangeBase targetBase) { - using (var xlRange = targetBase.Worksheet.Range( - range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, - range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1, - range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, - range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1)) - { - return ((XLRangeBase)targetBase).Range(xlRange.RangeAddress); - } + var sheet = (XLWorksheet)range.Worksheet; + var xlRangeAddress = new XLRangeAddress( + new XLAddress(sheet, + range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1, + false, false), + new XLAddress(sheet, + range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1, + false, false)); + return ((XLRangeBase)targetBase).Range(xlRangeAddress); } private bool SetDataTable(object o) @@ -1980,9 +1969,8 @@ private void ClearMerged() { - List mergeToDelete; - using (var asRange = AsRange()) - mergeToDelete = Worksheet.Internals.MergedRanges.Where(merge => merge.Intersects(asRange)).ToList(); + List mergeToDelete = Worksheet.Internals.MergedRanges + .Where(merge => merge.Intersects(AsRange())).ToList(); mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } @@ -2305,8 +2293,7 @@ CopyDataValidation(otherCell, otherCell.DataValidation); else if (HasDataValidation) { - using (var asRange = AsRange()) - Worksheet.DataValidations.Delete(asRange); + Worksheet.DataValidations.Delete(AsRange()); } Worksheet.EventTrackingEnabled = eventTracking; } @@ -2391,80 +2378,56 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1ColumnRegex.IsMatch(rangeAddress)) { - using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(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 (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 (useSheetName) { - if (useSheetName) - { - sb.Append(sheetName.EscapeSheetName()); - sb.Append('!'); - } + sb.Append(sheetName.EscapeSheetName()); + sb.Append('!'); + } - if (A1RowRegex.IsMatch(rangeAddress)) + if (A1RowRegex.IsMatch(rangeAddress)) + { + var rows = rangeAddress.Split(':'); + var row1String = rows[0]; + var row2String = rows[1]; + string row1; + if (row1String[0] == '$') { - 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(); - - sb.Append(row1); - sb.Append(':'); - sb.Append(row2); - } - else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= - matchRange.RangeAddress.FirstAddress.RowNumber) - { - if (rangeAddress.Contains(':')) - { - sb.Append( - new XLAddress( - worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress.FirstAddress.ColumnLetter, - matchRange.RangeAddress.FirstAddress.FixedRow, - matchRange.RangeAddress.FirstAddress.FixedColumn)); - sb.Append(':'); - sb.Append( - 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( - new XLAddress( - worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress.FirstAddress.ColumnLetter, - matchRange.RangeAddress.FirstAddress.FixedRow, - matchRange.RangeAddress.FirstAddress.FixedColumn)); - } + row1 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row1String.Substring(1)) + rowsShifted)).ToInvariantString(); } else + row1 = (XLHelper.TrimRowNumber(Int32.Parse(row1String) + rowsShifted)).ToInvariantString(); + + string row2; + if (row2String[0] == '$') { - sb.Append(matchRange.RangeAddress.FirstAddress); + row2 = "$" + + (XLHelper.TrimRowNumber(Int32.Parse(row2String.Substring(1)) + rowsShifted)).ToInvariantString(); + } + else + row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); + + sb.Append(row1); + sb.Append(':'); + sb.Append(row2); + } + else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= + matchRange.RangeAddress.FirstAddress.RowNumber) + { + if (rangeAddress.Contains(':')) + { + sb.Append( + new XLAddress( + worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); sb.Append(':'); sb.Append( new XLAddress( @@ -2474,10 +2437,32 @@ matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn)); } + else + { + sb.Append( + 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(matchString); + { + sb.Append(matchRange.RangeAddress.FirstAddress); + sb.Append(':'); + sb.Append( + 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); @@ -2539,99 +2524,76 @@ var rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1RowRegex.IsMatch(rangeAddress)) { - using (var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(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 (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 (useSheetName) { - if (useSheetName) - { - sb.Append(sheetName.EscapeSheetName()); - sb.Append('!'); - } + sb.Append(sheetName.EscapeSheetName()); + sb.Append('!'); + } - if (A1ColumnRegex.IsMatch(rangeAddress)) + if (A1ColumnRegex.IsMatch(rangeAddress)) + { + var columns = rangeAddress.Split(':'); + var column1String = columns[0]; + var column2String = columns[1]; + string column1; + if (column1String[0] == '$') { - 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); - } - - sb.Append(column1); - sb.Append(':'); - sb.Append(column2); - } - else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= - matchRange.RangeAddress.FirstAddress.ColumnNumber) - { - if (rangeAddress.Contains(':')) - { - sb.Append( - new XLAddress( - worksheetInAction, - matchRange.RangeAddress.FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress.FirstAddress.FixedRow, - matchRange.RangeAddress.FirstAddress.FixedColumn)); - sb.Append(':'); - sb.Append( - 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( - new XLAddress( - worksheetInAction, - matchRange.RangeAddress.FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress.FirstAddress.FixedRow, - matchRange.RangeAddress.FirstAddress.FixedColumn)); - } + column1 = "$" + + XLHelper.GetColumnLetterFromNumber( + XLHelper.GetColumnNumberFromLetter( + column1String.Substring(1)) + columnsShifted, true); } else { - sb.Append(matchRange.RangeAddress.FirstAddress); + 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); + } + + sb.Append(column1); + sb.Append(':'); + sb.Append(column2); + } + else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + matchRange.RangeAddress.FirstAddress.ColumnNumber) + { + if (rangeAddress.Contains(':')) + { + sb.Append( + new XLAddress( + worksheetInAction, + matchRange.RangeAddress.FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); sb.Append(':'); sb.Append( new XLAddress( @@ -2641,10 +2603,32 @@ matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn)); } + else + { + sb.Append( + 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(matchString); + { + sb.Append(matchRange.RangeAddress.FirstAddress); + sb.Append(':'); + sb.Append( + 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/Columns/IXLColumns.cs b/ClosedXML/Excel/Columns/IXLColumns.cs index ac7cf30..56a8edf 100644 --- a/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/Excel/Columns/IXLColumns.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLColumns : IEnumerable, IDisposable + public interface IXLColumns : IEnumerable { /// /// Sets the width of all columns. diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index 791c595..0a94932 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -8,64 +8,35 @@ internal class XLColumn : XLRangeBase, IXLColumn { #region Private fields - - private bool _collapsed; - private bool _isHidden; private int _outlineLevel; - private Double _width; - #endregion Private fields #region Constructor - public XLColumn(Int32 column, XLColumnParameters xlColumnParameters) - : base( - new XLRangeAddress(new XLAddress(xlColumnParameters.Worksheet, 1, column, false, false), - new XLAddress(xlColumnParameters.Worksheet, XLHelper.MaxRowNumber, column, false, - false)), - xlColumnParameters.IsReference ? xlColumnParameters.Worksheet.Internals.ColumnsCollection[column].StyleValue - : (xlColumnParameters.DefaultStyle as XLStyle).Value) + /// + /// The direct contructor should only be used in . + /// + public XLColumn(XLWorksheet worksheet, Int32 column) + : base(XLRangeAddress.EntireColumn(worksheet, column), worksheet.StyleValue) { SetColumnNumber(column); - IsReference = xlColumnParameters.IsReference; - if (IsReference) - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - else - { - _width = xlColumnParameters.Worksheet.ColumnWidth; - } - } - - public XLColumn(XLColumn column) - : base( - new XLRangeAddress(new XLAddress(column.Worksheet, 1, column.ColumnNumber(), false, false), - new XLAddress(column.Worksheet, XLHelper.MaxRowNumber, column.ColumnNumber(), - false, false)), - column.StyleValue) - { - _width = column._width; - IsReference = column.IsReference; - if (IsReference) - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - _collapsed = column._collapsed; - _isHidden = column._isHidden; - _outlineLevel = column._outlineLevel; + Width = worksheet.ColumnWidth; } #endregion Constructor - public Boolean IsReference { get; private set; } + public override XLRangeType RangeType + { + get { return XLRangeType.Column; } + } public override IEnumerable Styles { get { - if (IsReference) - yield return Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style; - else - yield return Style; + yield return Style; int column = ColumnNumber(); @@ -79,59 +50,23 @@ get { int column = ColumnNumber(); - if (IsReference) - yield return Worksheet.Internals.ColumnsCollection[column]; - else - { - foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInColumn(column)) - yield return cell; - } + foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInColumn(column)) + yield return cell; } } - public Boolean Collapsed - { - get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Collapsed : _collapsed; } - set - { - if (IsReference) - Worksheet.Internals.ColumnsCollection[ColumnNumber()].Collapsed = value; - else - _collapsed = value; - } - } + public Boolean Collapsed { get; set; } + #region IXLColumn Members - public Double Width - { - get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Width : _width; } - set - { - if (IsReference) - Worksheet.Internals.ColumnsCollection[ColumnNumber()].Width = value; - else - _width = value; - } - } + public Double Width { get; set; } public void Delete() { int columnNumber = ColumnNumber(); - using (var asRange = AsRange()) - { - asRange.Delete(XLShiftDeletedCells.ShiftCellsLeft); - } - - Worksheet.Internals.ColumnsCollection.Remove(columnNumber); - var columnsToMove = new List(); - columnsToMove.AddRange( - Worksheet.Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); - foreach (int column in columnsToMove.OrderBy(c => c)) - { - Worksheet.Internals.ColumnsCollection.Add(column - 1, Worksheet.Internals.ColumnsCollection[column]); - Worksheet.Internals.ColumnsCollection.Remove(column); - } + Delete(XLShiftDeletedCells.ShiftCellsLeft); + Worksheet.DeleteColumn(columnNumber); } public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.All) @@ -176,14 +111,7 @@ { int columnNum = ColumnNumber(); Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); - using (var column = Worksheet.Column(columnNum)) - { - using (var asRange = column.AsRange()) - { - asRange.InsertColumnsAfterVoid(true, numberOfColumns); - } - } - + Worksheet.Column(columnNum).InsertColumnsAfterVoid(true, numberOfColumns); var newColumns = Worksheet.Columns(columnNum + 1, columnNum + numberOfColumns); CopyColumns(newColumns); return newColumns; @@ -194,21 +122,11 @@ int columnNum = ColumnNumber(); if (columnNum > 1) { - using (var column = Worksheet.Column(columnNum - 1)) - { - return column.InsertColumnsAfter(numberOfColumns); - } + return Worksheet.Column(columnNum - 1).InsertColumnsAfter(numberOfColumns); } Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); - - using (var column = Worksheet.Column(columnNum)) - { - using (var asRange = column.AsRange()) - { - asRange.InsertColumnsBeforeVoid(true, numberOfColumns); - } - } + Worksheet.Column(columnNum).InsertColumnsBeforeVoid(true, numberOfColumns); return Worksheet.Columns(columnNum, columnNum + numberOfColumns - 1); } @@ -218,10 +136,10 @@ foreach (var newColumn in newColumns) { var internalColumn = Worksheet.Internals.ColumnsCollection[newColumn.ColumnNumber()]; - internalColumn._width = Width; + internalColumn.Width = Width; internalColumn.InnerStyle = InnerStyle; - internalColumn._collapsed = Collapsed; - internalColumn._isHidden = IsHidden; + internalColumn.Collapsed = Collapsed; + internalColumn.IsHidden = IsHidden; internalColumn._outlineLevel = OutlineLevel; } } @@ -450,34 +368,19 @@ return this; } - public Boolean IsHidden - { - get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].IsHidden : _isHidden; } - set - { - if (IsReference) - Worksheet.Internals.ColumnsCollection[ColumnNumber()].IsHidden = value; - else - _isHidden = value; - } - } + public Boolean IsHidden { get; set; } public Int32 OutlineLevel { - get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].OutlineLevel : _outlineLevel; } + get { return _outlineLevel; } set { if (value < 0 || value > 8) throw new ArgumentOutOfRangeException("value", "Outline level must be between 0 and 8."); - if (IsReference) - Worksheet.Internals.ColumnsCollection[ColumnNumber()].OutlineLevel = value; - else - { - Worksheet.IncrementColumnOutline(value); - Worksheet.DecrementColumnOutline(_outlineLevel); - _outlineLevel = value; - } + Worksheet.IncrementColumnOutline(value); + Worksheet.DecrementColumnOutline(_outlineLevel); + _outlineLevel = value; } } @@ -550,27 +453,24 @@ IXLRangeColumn IXLColumn.CopyTo(IXLCell target) { - using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Column(1); + var copy = AsRange().CopyTo(target); + return copy.Column(1); } IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target) { - using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Column(1); + var copy = AsRange().CopyTo(target); + return copy.Column(1); } public IXLColumn CopyTo(IXLColumn column) { column.Clear(); var newColumn = (XLColumn)column; - newColumn._width = _width; + newColumn.Width = Width; newColumn.InnerStyle = InnerStyle; - using (var asRange = AsRange()) - asRange.CopyTo(column).Dispose(); + AsRange().CopyTo(column); return newColumn; } @@ -590,8 +490,7 @@ var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); foreach (string pair in columnPairs) - using (var asRange = AsRange()) - asRange.Columns(pair.Trim()).ForEach(retVal.Add); + AsRange().Columns(pair.Trim()).ForEach(retVal.Add); return retVal; } @@ -616,38 +515,35 @@ } #endregion IXLColumn Members - + public override XLRange AsRange() { return Range(1, 1, XLHelper.MaxRowNumber, 1); } - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.RangeAddress.IsValid && - RangeAddress.IsValid && - range.RangeAddress.FirstAddress.ColumnNumber <= ColumnNumber()) - SetColumnNumber(ColumnNumber() + columnsShifted); + return; // Columns are shifted by XLColumnCollection } - private void SetColumnNumber(int column) + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - if (column <= 0) - RangeAddress.IsValid = false; - else - { - RangeAddress.IsValid = true; - RangeAddress.FirstAddress = new XLAddress(Worksheet, - 1, - column, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn); - RangeAddress.LastAddress = new XLAddress(Worksheet, - XLHelper.MaxRowNumber, - column, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn); - } + //do nothing + } + + internal void SetColumnNumber(int column) + { + RangeAddress = new XLRangeAddress( + new XLAddress(Worksheet, + 1, + column, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + XLHelper.MaxRowNumber, + column, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } public override XLRange Range(String rangeAddressStr) diff --git a/ClosedXML/Excel/Columns/XLColumnCollection.cs b/ClosedXML/Excel/Columns/XLColumnCollection.cs index 7140fed..4c33ee9 100644 --- a/ClosedXML/Excel/Columns/XLColumnCollection.cs +++ b/ClosedXML/Excel/Columns/XLColumnCollection.cs @@ -4,31 +4,21 @@ namespace ClosedXML.Excel { - internal class XLColumnsCollection : IDictionary, IDisposable + internal class XLColumnsCollection : IDictionary { public void ShiftColumnsRight(Int32 startingColumn, Int32 columnsToShift) { - foreach (var ro in _dictionary.Keys.Where(k => k >= startingColumn).OrderByDescending(k => k)) + foreach (var co in _dictionary.Keys.Where(k => k >= startingColumn).OrderByDescending(k => k)) { - var columnToMove = _dictionary[ro]; - Int32 newColumnNum = ro + columnsToShift; + var columnToMove = _dictionary[co]; + _dictionary.Remove(co); + Int32 newColumnNum = co + columnsToShift; if (newColumnNum <= XLHelper.MaxColumnNumber) { - var newColumn = new XLColumn(columnToMove) - { - RangeAddress = - { - FirstAddress = new XLAddress(1, newColumnNum, false, false), - LastAddress = - new XLAddress(XLHelper.MaxRowNumber, newColumnNum, false, false) - } - }; - - _dictionary.Add(newColumnNum, newColumn); + columnToMove.SetColumnNumber(newColumnNum); + _dictionary.Add(newColumnNum, columnToMove); } - _dictionary.Remove(ro); } - } private readonly Dictionary _dictionary = new Dictionary(); @@ -124,10 +114,5 @@ { _dictionary.RemoveAll(predicate); } - - public void Dispose() - { - _dictionary.Values.ForEach(c=>c.Dispose()); - } } } diff --git a/ClosedXML/Excel/Columns/XLColumnParameters.cs b/ClosedXML/Excel/Columns/XLColumnParameters.cs index bf7056b..131b80a 100644 --- a/ClosedXML/Excel/Columns/XLColumnParameters.cs +++ b/ClosedXML/Excel/Columns/XLColumnParameters.cs @@ -5,14 +5,14 @@ { internal class XLColumnParameters { - public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle, Boolean isReference) + public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle) { Worksheet = worksheet; DefaultStyle = defaultStyle; - IsReference = isReference; + //IsReference = isReference; } public IXLStyle DefaultStyle { get; private set; } public XLWorksheet Worksheet { get; private set; } - public Boolean IsReference { get; private set; } + //public Boolean IsReference { get; private set; } } } diff --git a/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/Excel/Columns/XLColumns.cs index 8a992d2..80cd9eb 100644 --- a/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/Excel/Columns/XLColumns.cs @@ -257,12 +257,6 @@ return this; } - public void Dispose() - { - if (_columns != null) - _columns.ForEach(c => c.Dispose()); - } - public void Select() { foreach (var range in this) diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index 38aecec..a122b9d 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -25,7 +25,6 @@ public void Remove(Predicate predicate) { - _conditionalFormats.Where(cf => predicate(cf)).SelectMany(cf => cf.Ranges).ForEach(range => range.Dispose()); _conditionalFormats.RemoveAll(predicate); } @@ -103,7 +102,6 @@ public void RemoveAll() { - _conditionalFormats.SelectMany(cf => cf.Ranges).ForEach(range => range.Dispose()); _conditionalFormats.Clear(); } diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index 9e55f78..818ade9 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -7,7 +7,6 @@ internal struct XLAddress : IXLAddress, IEquatable { #region Static - /// /// Create address without worksheet. For calculation only! /// @@ -168,13 +167,11 @@ public bool FixedRow { get { return _fixedRow; } - set { _fixedRow = value; } } public bool FixedColumn { get { return _fixedColumn; } - set { _fixedColumn = value; } } /// @@ -319,26 +316,11 @@ return x == y; } - public Int32 GetHashCode(IXLAddress obj) - { - return obj.GetHashCode(); - } - public new Boolean Equals(object x, object y) { return x == y; } - public Int32 GetHashCode(object obj) - { - return (obj).GetHashCode(); - } - - public override int GetHashCode() - { - return _rowNumber ^ _columnNumber; - } - #endregion IEqualityComparer Members #region IEquatable Members @@ -349,12 +331,17 @@ return false; return _rowNumber == other.RowNumber && - _columnNumber == other.ColumnNumber; + _columnNumber == other.ColumnNumber && + _fixedRow == other.FixedRow && + _fixedColumn == other.FixedColumn; } + public bool Equals(XLAddress other) { return _rowNumber == other._rowNumber && - _columnNumber == other._columnNumber; + _columnNumber == other._columnNumber && + _fixedRow == other._fixedRow && + _fixedColumn == other._fixedColumn; } public override Boolean Equals(Object other) @@ -362,6 +349,21 @@ return Equals(other as IXLAddress); } + public override int GetHashCode() + { + var hashCode = 2122234362; + hashCode = hashCode * -1521134295 + _fixedRow.GetHashCode(); + hashCode = hashCode * -1521134295 + _fixedColumn.GetHashCode(); + hashCode = hashCode * -1521134295 + _rowNumber.GetHashCode(); + hashCode = hashCode * -1521134295 + _columnNumber.GetHashCode(); + return hashCode; + } + + public int GetHashCode(IXLAddress obj) + { + return ((XLAddress)obj).GetHashCode(); + } + #endregion IEquatable Members #endregion Interface Requirements @@ -429,5 +431,14 @@ } public String UniqueId { get { return RowNumber.ToString("0000000") + ColumnNumber.ToString("00000"); } } + + public bool IsValid + { + get + { + return 0 < RowNumber && RowNumber <= XLHelper.MaxRowNumber && + 0 < ColumnNumber && ColumnNumber <= XLHelper.MaxColumnNumber; + } + } } } diff --git a/ClosedXML/Excel/DataValidation/IXLDataValidation.cs b/ClosedXML/Excel/DataValidation/IXLDataValidation.cs index 0d87c3c..1c7b9fd 100644 --- a/ClosedXML/Excel/DataValidation/IXLDataValidation.cs +++ b/ClosedXML/Excel/DataValidation/IXLDataValidation.cs @@ -5,7 +5,7 @@ public enum XLErrorStyle { Stop, Warning, Information } public enum XLAllowedValues { AnyValue, WholeNumber, Decimal, Date, Time, TextLength, List, Custom } public enum XLOperator { EqualTo, NotEqualTo, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan, Between, NotBetween } - public interface IXLDataValidation : IDisposable + public interface IXLDataValidation { IXLRanges Ranges { get; set; } //void Delete(); diff --git a/ClosedXML/Excel/DataValidation/IXLDataValidations.cs b/ClosedXML/Excel/DataValidation/IXLDataValidations.cs index dccd8d1..9dfca5f 100644 --- a/ClosedXML/Excel/DataValidation/IXLDataValidations.cs +++ b/ClosedXML/Excel/DataValidation/IXLDataValidations.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLDataValidations: IEnumerable, IDisposable + public interface IXLDataValidations: IEnumerable { void Add(IXLDataValidation dataValidation); Boolean ContainsSingle(IXLRange range); diff --git a/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 9d5ec8b..fc2e4cd 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -13,7 +13,7 @@ public XLDataValidation(IXLRange range) :this() { - Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style))); + Ranges.Add(new XLRange(new XLRangeParameters((XLRangeAddress)range.RangeAddress, range.Worksheet.Style))); } public XLDataValidation(IXLRanges ranges) @@ -21,7 +21,7 @@ { ranges.ForEach(range => { - Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style))); + Ranges.Add(new XLRange(new XLRangeParameters((XLRangeAddress)range.RangeAddress, range.Worksheet.Style))); }); } @@ -197,10 +197,5 @@ { Initialize(); } - - public void Dispose() - { - Ranges?.Dispose(); - } } } diff --git a/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/Excel/DataValidation/XLDataValidations.cs index 92fb784..2518248 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidations.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidations.cs @@ -87,10 +87,5 @@ _dataValidations.Add(consRule); } } - - public void Dispose() - { - _dataValidations.ForEach(dv => dv.Dispose()); - } } } diff --git a/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/Excel/IXLWorksheet.cs index d761576..dbad83a 100644 --- a/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/Excel/IXLWorksheet.cs @@ -7,7 +7,7 @@ { public enum XLWorksheetVisibility { Visible, Hidden, VeryHidden } - public interface IXLWorksheet : IXLRangeBase + public interface IXLWorksheet : IXLRangeBase, IDisposable { /// /// Gets the workbook that contains this worksheet diff --git a/ClosedXML/Excel/Misc/XLCallbackAction.cs b/ClosedXML/Excel/Misc/XLCallbackAction.cs deleted file mode 100644 index db031fc..0000000 --- a/ClosedXML/Excel/Misc/XLCallbackAction.cs +++ /dev/null @@ -1,17 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel.Misc -{ - internal class XLCallbackAction - { - public XLCallbackAction(Action action) - { - this.Action = action; - } - - public Action Action { get; set; } - } -} diff --git a/ClosedXML/Excel/Misc/XLReentrantEnumerableSet.cs b/ClosedXML/Excel/Misc/XLReentrantEnumerableSet.cs deleted file mode 100644 index 62337a0..0000000 --- a/ClosedXML/Excel/Misc/XLReentrantEnumerableSet.cs +++ /dev/null @@ -1,100 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using System.Collections; - -namespace ClosedXML.Excel.Misc -{ - /// - /// This is most definitely NOT thread-safe, but it is reentrant (e.g. you can insert and remove - /// items during enumeration) - /// - /// - internal class XLReentrantEnumerableSet : IEnumerable - { - private HashSet _hashSet; - private List _list; - - private int _activeEnumerators; - - public XLReentrantEnumerableSet() - { - _list = new List(); - _hashSet = new HashSet(); - } - - public void Add(T item) - { - if(!_hashSet.Contains(item)) - { - // Add item to end of list - _list.Add(item); - - // Store the item in the hashset too. - _hashSet.Add(item); - } - } - - public void Remove(T item) - { - _hashSet.Remove(item); - - fixup(); - } - - private void fixup() - { - // Only fixup the list if there are no active enumerators - if(_activeEnumerators > 0) - return; - - // Only fixup the list if there are more than a certain number of items to deal with - // This saves fixing up the list continually. - if(_list.Count < 1000 || (_list.Count < _hashSet.Count * 1.5)) - return; - - // Rebuild the list skipping out omitted items - _list = _list.Where(item => _hashSet.Contains(item)).ToList(); - } - - public IEnumerator GetEnumerator() - { - // Mark that we are enumerating - _activeEnumerators++; - try - { - int idx = 0; - - // Important, store count here, as more items may be added while we are enumerating - // and we only want to enumerate items that were already there. - int count = _list.Count; - while(idx < count) - { - var item = _list[idx]; - - // Skip over items in the list which aren't in the hashset; they could have been - // removed while we were enumerating or previously removed. - if(_hashSet.Contains(item)) - { - yield return item; - } - - idx++; - } - - } - finally - { - // Finished enumerating, can now fixup - _activeEnumerators--; - fixup(); - } - } - - IEnumerator IEnumerable.GetEnumerator() - { - return this.GetEnumerator(); - } - } -} diff --git a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 751f1a4..de1b017 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -10,7 +10,7 @@ /// /// The first address. /// - IXLAddress FirstAddress { get; set; } + IXLAddress FirstAddress { get; } /// /// Gets or sets a value indicating whether this range is valid. @@ -26,7 +26,7 @@ /// /// The last address. /// - IXLAddress LastAddress { get; set; } + IXLAddress LastAddress { get; } IXLWorksheet Worksheet { get; } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 0932cbf..0875a86 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -10,7 +10,7 @@ Worksheet } - public interface IXLRangeBase : IDisposable + public interface IXLRangeBase { IXLWorksheet Worksheet { get; } diff --git a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index 01395ab..c78e08d 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRangeColumns : IEnumerable, IDisposable + public interface IXLRangeColumns : IEnumerable { /// /// Adds a column range to this group. diff --git a/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 3934c12..07029e1 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRangeRows : IEnumerable, IDisposable + public interface IXLRangeRows : IEnumerable { /// /// Adds a row range to this group. diff --git a/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/Excel/Ranges/IXLRanges.cs index 66ccda8..afbb967 100644 --- a/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRanges : IEnumerable, IDisposable + public interface IXLRanges : IEnumerable { /// /// Adds the specified range to this group. diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 8e29344..6e8f90c 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -11,19 +11,14 @@ public XLRange(XLRangeParameters xlRangeParameters) : base(xlRangeParameters.RangeAddress, (xlRangeParameters.DefaultStyle as XLStyle).Value) { - RangeParameters = new XLRangeParameters(xlRangeParameters.RangeAddress, xlRangeParameters.DefaultStyle); - - if (!xlRangeParameters.IgnoreEvents) - { - SubscribeToShiftedRows((range, rowShifted) => this.WorksheetRangeShiftedRows(range, rowShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - //xlRangeParameters.IgnoreEvents = true; - } } #endregion Constructor - public XLRangeParameters RangeParameters { get; private set; } + public override XLRangeType RangeType + { + get { return XLRangeType.Range; } + } #region IXLRange Members @@ -51,8 +46,6 @@ var column = Column(c); if (predicate == null || predicate(column)) retVal.Add(column); - else - column.Dispose(); } return retVal; } @@ -166,8 +159,6 @@ var row = Row(r); if (predicate == null || predicate(row)) retVal.Add(Row(r)); - else - row.Dispose(); } return retVal; } @@ -218,11 +209,14 @@ MoveOrClearForTranspose(transposeOption, rowCount, columnCount); TransposeMerged(squareSide); TransposeRange(squareSide); - RangeAddress.LastAddress = new XLAddress(Worksheet, - firstCell.Address.RowNumber + columnCount - 1, - firstCell.Address.ColumnNumber + rowCount - 1, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn); + RangeAddress = new XLRangeAddress( + RangeAddress.FirstAddress, + new XLAddress(Worksheet, + firstCell.Address.RowNumber + columnCount - 1, + firstCell.Address.ColumnNumber + rowCount - 1, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); + if (rowCount > columnCount) { var rng = Worksheet.Range( @@ -258,12 +252,12 @@ public IXLTable AsTable() { - return new XLTable(this, false); + return Worksheet.Table(this, false); } public IXLTable AsTable(String name) { - return new XLTable(this, name, false); + return Worksheet.Table(this, name, false); } IXLTable IXLRange.CreateTable() @@ -273,7 +267,7 @@ public XLTable CreateTable() { - return new XLTable(this, true, true); + return (XLTable)Worksheet.Table(this, true, true); } IXLTable IXLRange.CreateTable(String name) @@ -283,12 +277,12 @@ public XLTable CreateTable(String name) { - return new XLTable(this, name, true, true); + return (XLTable)Worksheet.Table(this, name, true, true); } public IXLTable CreateTable(String name, Boolean setAutofilter) { - return new XLTable(this, name, true, setAutofilter); + return Worksheet.Table(this, name, true, setAutofilter); } public new IXLRange CopyTo(IXLCell target) @@ -353,14 +347,14 @@ #endregion IXLRange Members - internal void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - ShiftColumns(RangeAddress, range, columnsShifted); + RangeAddress = (XLRangeAddress)ShiftColumns(RangeAddress, range, columnsShifted); } - internal void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - ShiftRows(RangeAddress, range, rowsShifted); + RangeAddress = (XLRangeAddress)ShiftRows(RangeAddress, range, rowsShifted); } IXLRangeColumn IXLRange.FirstColumn(Func predicate) @@ -378,8 +372,6 @@ { var column = Column(c); if (predicate(column)) return column; - - column.Dispose(); } return null; @@ -400,8 +392,6 @@ { var column = Column(c); if (predicate(column)) return column; - - column.Dispose(); } return null; @@ -443,7 +433,6 @@ if (!column.IsEmpty(includeFormats) && predicate(column)) return column; - column.Dispose(); } return null; } @@ -484,7 +473,6 @@ if (!column.IsEmpty(includeFormats) && predicate(column)) return column; - column.Dispose(); } return null; } @@ -504,8 +492,6 @@ { var row = Row(ro); if (predicate(row)) return row; - - row.Dispose(); } return null; @@ -526,8 +512,6 @@ { var row = Row(ro); if (predicate(row)) return row; - - row.Dispose(); } return null; @@ -571,7 +555,6 @@ if (!row.IsEmpty(includeFormats) && predicate(row)) return row; - row.Dispose(); } return null; } @@ -612,7 +595,6 @@ if (!row.IsEmpty(includeFormats) && predicate(row)) return row; - row.Dispose(); } return null; } @@ -632,8 +614,6 @@ if (!row.IsEmpty(includeFormats) && (predicate == null || predicate(row))) rows.Add(row); - else - row.Dispose(); } return rows; } @@ -663,8 +643,6 @@ if (!column.IsEmpty(includeFormats) && (predicate == null || predicate(column))) columns.Add(column); - else - column.Dispose(); } return columns; } @@ -694,8 +672,7 @@ RangeAddress.LastAddress.ColumnNumber, false, false); - return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + return Worksheet.RangeRow(new XLRangeAddress(firstCellAddress, lastCellAddress)); } public virtual XLRangeColumn Column(Int32 columnNumber) @@ -713,8 +690,7 @@ RangeAddress.FirstAddress.ColumnNumber + columnNumber - 1, false, false); - return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + return Worksheet.RangeColumn(new XLRangeAddress(firstCellAddress, lastCellAddress)); } public virtual XLRangeColumn Column(String columnLetter) @@ -760,9 +736,11 @@ RangeAddress.FirstAddress.RowNumber + squareSide - 1, RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - foreach (IXLRange merge in Worksheet.Internals.MergedRanges.Where(Contains)) + foreach (var merge in Worksheet.Internals.MergedRanges.Where(Contains).Cast()) { - merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; + merge.RangeAddress = new XLRangeAddress( + merge.RangeAddress.FirstAddress, + rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address); } } @@ -829,8 +807,6 @@ var column = Column(c); if (predicate == null || predicate(column)) return column; - else - column.Dispose(); } return null; } @@ -843,8 +819,6 @@ var row = Row(r); if (predicate(row)) return row; - else - row.Dispose(); } return null; } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index f1afeb4..d774b6a 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -1,36 +1,41 @@ using ClosedXML.Extensions; using System; +using System.Collections.Generic; using System.Diagnostics; using System.Linq; namespace ClosedXML.Excel { - internal class XLRangeAddress : IXLRangeAddress + internal struct XLRangeAddress : IXLRangeAddress, IEquatable { - #region Private fields + #region Static members - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private XLAddress _firstAddress; + public static XLRangeAddress EntireColumn(XLWorksheet worksheet, int column) + { + return new XLRangeAddress( + new XLAddress(worksheet, 1, column, false, false), + new XLAddress(worksheet, XLHelper.MaxRowNumber, column, false, false)); + } - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private XLAddress _lastAddress; + public static XLRangeAddress EntireRow(XLWorksheet worksheet, int row) + { + return new XLRangeAddress( + new XLAddress(worksheet, row, 1, false, false), + new XLAddress(worksheet, row, XLHelper.MaxColumnNumber, false, false)); + } - #endregion Private fields + #endregion Static members #region Constructor - public XLRangeAddress(XLRangeAddress rangeAddress) : this(rangeAddress.FirstAddress, rangeAddress.LastAddress) - { - } - - public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) + public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) : this() { Worksheet = firstAddress.Worksheet; FirstAddress = firstAddress; LastAddress = lastAddress; } - public XLRangeAddress(XLWorksheet worksheet, String rangeAddress) + public XLRangeAddress(XLWorksheet worksheet, String rangeAddress) : this() { string addressToUse = rangeAddress.Contains("!") ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) @@ -78,31 +83,11 @@ #region Public properties - public XLWorksheet Worksheet { get; internal set; } + public XLWorksheet Worksheet { get; } - public XLAddress FirstAddress - { - get - { - if (!IsValid) - throw new InvalidOperationException("Range is invalid."); + public XLAddress FirstAddress { get; } - return _firstAddress; - } - set { _firstAddress = value; } - } - - public XLAddress LastAddress - { - get - { - if (!IsValid) - throw new InvalidOperationException("Range is an invalid state."); - - return _lastAddress; - } - set { _lastAddress = value; } - } + public XLAddress LastAddress { get; } IXLWorksheet IXLRangeAddress.Worksheet { @@ -113,34 +98,76 @@ { [DebuggerStepThrough] get { return FirstAddress; } - set - { - if (value is XLAddress) - FirstAddress = (XLAddress)value; - else - FirstAddress = new XLAddress(value.RowNumber, value.ColumnNumber, value.FixedRow, value.FixedColumn); - } } IXLAddress IXLRangeAddress.LastAddress { [DebuggerStepThrough] get { return LastAddress; } - set - { - if (value is XLAddress) - LastAddress = (XLAddress)value; - else - LastAddress = new XLAddress(value.RowNumber, value.ColumnNumber, value.FixedRow, value.FixedColumn); - } } - public bool IsValid { get; set; } = true; + public bool IsValid + { + get + { + return FirstAddress.IsValid && + LastAddress.IsValid; + } + } #endregion Public properties #region Public methods + /// + /// Lead a range address to a normal form - when points to the top-left address and + /// points to the bottom-right address. + /// + /// + public XLRangeAddress Normalize() + { + if (FirstAddress.RowNumber <= LastAddress.RowNumber && + FirstAddress.ColumnNumber <= LastAddress.ColumnNumber) + return this; + + int firstRow, firstColumn, lastRow, lastColumn; + bool firstRowFixed, firstColumnFixed, lastRowFixed, lastColumnFixed; + + if (FirstAddress.RowNumber <= LastAddress.RowNumber) + { + firstRow = FirstAddress.RowNumber; + firstRowFixed = FirstAddress.FixedRow; + lastRow = LastAddress.RowNumber; + lastRowFixed = LastAddress.FixedRow; + } + else + { + firstRow = LastAddress.RowNumber; + firstRowFixed = LastAddress.FixedRow; + lastRow = FirstAddress.RowNumber; + lastRowFixed = FirstAddress.FixedRow; + } + + if (FirstAddress.ColumnNumber <= LastAddress.ColumnNumber) + { + firstColumn = FirstAddress.ColumnNumber; + firstColumnFixed = FirstAddress.FixedColumn; + lastColumn = LastAddress.ColumnNumber; + lastColumnFixed = LastAddress.FixedColumn; + } + else + { + firstColumn = LastAddress.ColumnNumber; + firstColumnFixed = LastAddress.FixedColumn; + lastColumn = FirstAddress.ColumnNumber; + lastColumnFixed = FirstAddress.FixedColumn; + } + + return new XLRangeAddress( + new XLAddress(FirstAddress.Worksheet, firstRow, firstColumn, firstRowFixed, firstColumnFixed), + new XLAddress(LastAddress.Worksheet, lastRow, lastColumn, lastRowFixed, lastColumnFixed)); + } + public String ToStringRelative() { return ToStringRelative(false); @@ -157,14 +184,14 @@ return String.Concat( Worksheet.Name.EscapeSheetName(), '!', - _firstAddress.ToStringRelative(), + FirstAddress.ToStringRelative(), ':', - _lastAddress.ToStringRelative()); + LastAddress.ToStringRelative()); else return string.Concat( - _firstAddress.ToStringRelative(), + FirstAddress.ToStringRelative(), ":", - _lastAddress.ToStringRelative()); + LastAddress.ToStringRelative()); } public String ToStringFixed(XLReferenceStyle referenceStyle) @@ -177,15 +204,15 @@ if (includeSheet) return String.Format("{0}!{1}:{2}", Worksheet.Name.EscapeSheetName(), - _firstAddress.ToStringFixed(referenceStyle), - _lastAddress.ToStringFixed(referenceStyle)); + FirstAddress.ToStringFixed(referenceStyle), + LastAddress.ToStringFixed(referenceStyle)); - return _firstAddress.ToStringFixed(referenceStyle) + ":" + _lastAddress.ToStringFixed(referenceStyle); + return FirstAddress.ToStringFixed(referenceStyle) + ":" + LastAddress.ToStringFixed(referenceStyle); } public override string ToString() { - return String.Concat(_firstAddress, ':', _lastAddress); + return String.Concat(FirstAddress, ':', LastAddress); } public string ToString(XLReferenceStyle referenceStyle) @@ -203,22 +230,46 @@ public override bool Equals(object obj) { - var other = obj as XLRangeAddress; - if (other == null) + if (!(obj is XLRangeAddress)) + { return false; - return Worksheet.Equals(other.Worksheet) - && FirstAddress.Equals(other.FirstAddress) - && LastAddress.Equals(other.LastAddress); + } + + var address = (XLRangeAddress)obj; + return FirstAddress.Equals(address.FirstAddress) && + LastAddress.Equals(address.LastAddress) && + EqualityComparer.Default.Equals(Worksheet, address.Worksheet); } public override int GetHashCode() { - return - Worksheet.GetHashCode() - ^ FirstAddress.GetHashCode() - ^ LastAddress.GetHashCode(); + var hashCode = -778064135; + hashCode = hashCode * -1521134295 + FirstAddress.GetHashCode(); + hashCode = hashCode * -1521134295 + LastAddress.GetHashCode(); + hashCode = hashCode * -1521134295 + EqualityComparer.Default.GetHashCode(Worksheet); + return hashCode; + } + + public bool Equals(XLRangeAddress other) + { + return ReferenceEquals(Worksheet, other.Worksheet) && + FirstAddress == other.FirstAddress && + LastAddress == other.LastAddress; } #endregion Public methods + + #region Operators + + public static bool operator ==(XLRangeAddress left, XLRangeAddress right) + { + return left.Equals(right); + } + + public static bool operator !=(XLRangeAddress left, XLRangeAddress right) + { + return !(left == right); + } + #endregion } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index baebfeb..370b9b7 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1,4 +1,3 @@ -using ClosedXML.Excel.Misc; using ClosedXML.Extensions; using System; using System.Collections; @@ -33,31 +32,14 @@ { Id = ++IdCounter; - RangeAddress = new XLRangeAddress(rangeAddress); + _rangeAddress = rangeAddress; } #endregion Constructor - private XLCallbackAction _shiftedRowsAction; - - protected void SubscribeToShiftedRows(Action action) + protected virtual void OnRangeAddressChanged(XLRangeAddress oldAddress, XLRangeAddress newAddress) { - if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; - - _shiftedRowsAction = new XLCallbackAction(action); - - RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); - } - - private XLCallbackAction _shiftedColumnsAction; - - protected void SubscribeToShiftedColumns(Action action) - { - if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; - - _shiftedColumnsAction = new XLCallbackAction(action); - - RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); + Worksheet.RellocateRange(RangeType, oldAddress, newAddress); } #region Public properties @@ -67,7 +49,15 @@ public XLRangeAddress RangeAddress { get { return _rangeAddress; } - protected set { _rangeAddress = value; } + protected set + { + if (_rangeAddress != value) + { + var oldAddress = _rangeAddress; + _rangeAddress = value; + OnRangeAddressChanged(oldAddress, _rangeAddress); + } + } } public XLWorksheet Worksheet @@ -300,21 +290,18 @@ { if (checkIntersect) { - using (IXLRange range = Worksheet.Range(RangeAddress)) + var range = Worksheet.Range(RangeAddress); + foreach (var mergedRange in Worksheet.Internals.MergedRanges) { - foreach (var mergedRange in Worksheet.Internals.MergedRanges) + if (mergedRange.Intersects(range)) { - if (mergedRange.Intersects(range)) - { - Worksheet.Internals.MergedRanges.Remove(mergedRange); - } + Worksheet.Internals.MergedRanges.Remove(mergedRange); } } } var asRange = AsRange(); Worksheet.Internals.MergedRanges.Add(asRange); - return asRange; } @@ -456,8 +443,7 @@ public bool Intersects(string rangeAddress) { - using (var range = Worksheet.Range(rangeAddress)) - return Intersects(range); + return Intersects(Worksheet.Range(rangeAddress)); } public bool Intersects(IXLRangeBase range) @@ -872,6 +858,12 @@ return Range(rangeAddress); } + internal abstract void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted); + + internal abstract void WorksheetRangeShiftedRows(XLRange range, int rowsShifted); + + public abstract XLRangeType RangeType { get; } + public XLRange Range(IXLCell firstCell, IXLCell lastCell) { var newFirstCellAddress = (XLAddress)firstCell.Address; @@ -891,7 +883,7 @@ || newFirstCellAddress.ColumnNumber < RangeAddress.FirstAddress.ColumnNumber || newFirstCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber || newLastCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - ) + ) { throw new ArgumentOutOfRangeException(String.Format( "The cells {0} and {1} are outside the range '{2}'.", @@ -900,7 +892,12 @@ ToString())); } - return new XLRange(xlRangeParameters); + if (newFirstCellAddress.Worksheet != null) + return newFirstCellAddress.Worksheet.GetOrCreateRange(xlRangeParameters); + else if (Worksheet != null) + return Worksheet.GetOrCreateRange(xlRangeParameters); + else + return new XLRange(xlRangeParameters); } public XLRange Range(String firstCellAddress, String lastCellAddress) @@ -931,18 +928,12 @@ rangeAddress.FirstAddress.FixedRow, rangeAddress.FirstAddress.FixedColumn); - newFirstCellAddress.FixedRow = rangeAddress.FirstAddress.FixedRow; - newFirstCellAddress.FixedColumn = rangeAddress.FirstAddress.FixedColumn; - var newLastCellAddress = new XLAddress((XLWorksheet)rangeAddress.LastAddress.Worksheet, rangeAddress.LastAddress.RowNumber + RangeAddress.FirstAddress.RowNumber - 1, rangeAddress.LastAddress.ColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1, rangeAddress.LastAddress.FixedRow, rangeAddress.LastAddress.FixedColumn); - newLastCellAddress.FixedRow = rangeAddress.LastAddress.FixedRow; - newLastCellAddress.FixedColumn = rangeAddress.LastAddress.FixedColumn; - return GetRange(newFirstCellAddress, newLastCellAddress); } @@ -1091,8 +1082,7 @@ foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) - using (var asRange = AsRange()) - cell.ShiftFormulaColumns(asRange, numberOfColumns); + cell.ShiftFormulaColumns(AsRange(), numberOfColumns); } var cellsToInsert = new Dictionary(); @@ -1156,31 +1146,25 @@ Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; - using (var asRange = AsRange()) - Worksheet.NotifyRangeShiftedColumns(asRange, numberOfColumns); + Worksheet.NotifyRangeShiftedColumns(AsRange(), numberOfColumns); var rangeToReturn = Worksheet.Range(firstRowReturn, firstColumnReturn, lastRowReturn, lastColumnReturn); if (formatFromLeft && rangeToReturn.RangeAddress.FirstAddress.ColumnNumber > 1) { - using (var firstColumnUsed = rangeToReturn.FirstColumn()) - { - using (var model = firstColumnUsed.ColumnLeft()) - { + var firstColumnUsed = rangeToReturn.FirstColumn(); + var model = firstColumnUsed.ColumnLeft(); var modelFirstRow = model.FirstCellUsed(true); var modelLastRow = model.LastCellUsed(true); - if (modelLastRow != null) - { - Int32 firstRoReturned = modelFirstRow.Address.RowNumber - - model.RangeAddress.FirstAddress.RowNumber + 1; - Int32 lastRoReturned = modelLastRow.Address.RowNumber - - model.RangeAddress.FirstAddress.RowNumber + 1; - for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) - { - using (var row = rangeToReturn.Row(ro)) - row.Style = model.Cell(ro).Style; - } - } + if (modelLastRow != null) + { + Int32 firstRoReturned = modelFirstRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + Int32 lastRoReturned = modelLastRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) + { + rangeToReturn.Row(ro).Style = model.Cell(ro).Style; } } } @@ -1195,17 +1179,13 @@ var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(ro) ? Worksheet.Internals.RowsCollection[ro].Style : Worksheet.Style; - using (var row = rangeToReturn.Row(ro)) - row.Style = styleToUse; + rangeToReturn.Row(ro).Style = styleToUse; } } } if (nullReturn) - { - rangeToReturn.Dispose(); return null; - } return rangeToReturn.Columns(); } @@ -1303,12 +1283,12 @@ private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) { - using (var asRange = AsRange()) - foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) - { - foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) - cell.ShiftFormulaRows(asRange, numberOfRows); - } + var asRange = AsRange(); + foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) + { + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) + cell.ShiftFormulaRows(asRange, numberOfRows); + } var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); @@ -1374,31 +1354,25 @@ Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.LastAddress.ColumnNumber; - using (var asRange = AsRange()) - Worksheet.NotifyRangeShiftedRows(asRange, numberOfRows); + Worksheet.NotifyRangeShiftedRows(AsRange(), numberOfRows); var rangeToReturn = Worksheet.Range(firstRowReturn, firstColumnReturn, lastRowReturn, lastColumnReturn); if (formatFromAbove && rangeToReturn.RangeAddress.FirstAddress.RowNumber > 1) { - using (var fr = rangeToReturn.FirstRow()) + var fr = rangeToReturn.FirstRow(); + var model = fr.RowAbove(); + var modelFirstColumn = model.FirstCellUsed(true); + var modelLastColumn = model.LastCellUsed(true); + if (modelFirstColumn != null && modelLastColumn != null) { - using (var model = fr.RowAbove()) + Int32 firstCoReturned = modelFirstColumn.Address.ColumnNumber + - model.RangeAddress.FirstAddress.ColumnNumber + 1; + Int32 lastCoReturned = modelLastColumn.Address.ColumnNumber + - model.RangeAddress.FirstAddress.ColumnNumber + 1; + for (Int32 co = firstCoReturned; co <= lastCoReturned; co++) { - var modelFirstColumn = model.FirstCellUsed(true); - var modelLastColumn = model.LastCellUsed(true); - if (modelFirstColumn != null && modelLastColumn != null) - { - Int32 firstCoReturned = modelFirstColumn.Address.ColumnNumber - - model.RangeAddress.FirstAddress.ColumnNumber + 1; - Int32 lastCoReturned = modelLastColumn.Address.ColumnNumber - - model.RangeAddress.FirstAddress.ColumnNumber + 1; - for (Int32 co = firstCoReturned; co <= lastCoReturned; co++) - { - using (var column = rangeToReturn.Column(co)) - column.Style = model.Cell(co).Style; - } - } + rangeToReturn.Column(co).Style = model.Cell(co).Style; } } } @@ -1413,18 +1387,14 @@ var styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(co) ? Worksheet.Internals.ColumnsCollection[co].Style : Worksheet.Style; - using (var column = rangeToReturn.Column(co)) - column.Style = styleToUse; + rangeToReturn.Style = styleToUse; } } } // Skip calling .Rows() for performance reasons if required. if (nullReturn) - { - rangeToReturn.Dispose(); return null; - } return rangeToReturn.Rows(); } @@ -1457,6 +1427,9 @@ { int numberOfRows = RowCount(); int numberOfColumns = ColumnCount(); + + if (!RangeAddress.IsValid) return; + IXLRange shiftedRangeFormula = Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, @@ -1522,13 +1495,13 @@ var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - using (var shiftedRange = AsRange()) - { - if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); - else - Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); - } + var shiftedRange = AsRange(); + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); + else + Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); + + Worksheet.DeleteRange(RangeAddress); } public override string ToString() @@ -1541,15 +1514,15 @@ RangeAddress.LastAddress); } - protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) + protected IXLRangeAddress ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) { - if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return; + if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return thisRangeAddress; bool allRowsAreCovered = thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber; if (!allRowsAreCovered) - return; + return thisRangeAddress; bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) || (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); @@ -1571,36 +1544,42 @@ bool destroyedByShift = newRightBoundary < newLeftBoundary; + var firstAddress = (XLAddress)thisRangeAddress.FirstAddress; + var lastAddress = (XLAddress)thisRangeAddress.LastAddress; + if (destroyedByShift) { - (thisRangeAddress as XLRangeAddress).IsValid = false; - return; + firstAddress = Worksheet.InvalidAddress; + lastAddress = Worksheet.InvalidAddress; + Worksheet.DeleteRange(RangeAddress); } if (shiftLeftBoundary) - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber, - newLeftBoundary, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); + firstAddress = new XLAddress(Worksheet, + thisRangeAddress.FirstAddress.RowNumber, + newLeftBoundary, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); if (shiftRightBoundary) - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber, - newRightBoundary, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); + lastAddress = new XLAddress(Worksheet, + thisRangeAddress.LastAddress.RowNumber, + newRightBoundary, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); + + return new XLRangeAddress(firstAddress, lastAddress); } - protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) + protected IXLRangeAddress ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { - if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return; + if (!thisRangeAddress.IsValid || !shiftedRange.RangeAddress.IsValid) return thisRangeAddress; bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; if (!allColumnsAreCovered) - return; + return thisRangeAddress; bool shiftTopBoundary = (rowsShifted > 0 && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber) || (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber); @@ -1622,25 +1601,31 @@ bool destroyedByShift = newBottomBoundary < newTopBoundary; + var firstAddress = (XLAddress)thisRangeAddress.FirstAddress; + var lastAddress = (XLAddress)thisRangeAddress.LastAddress; + if (destroyedByShift) { - (thisRangeAddress as XLRangeAddress).IsValid = false; - return; + firstAddress = Worksheet.InvalidAddress; + lastAddress = Worksheet.InvalidAddress; + Worksheet.DeleteRange(RangeAddress); } if (shiftTopBoundary) - thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - newTopBoundary, - thisRangeAddress.FirstAddress.ColumnNumber, - thisRangeAddress.FirstAddress.FixedRow, - thisRangeAddress.FirstAddress.FixedColumn); + firstAddress = new XLAddress(Worksheet, + newTopBoundary, + thisRangeAddress.FirstAddress.ColumnNumber, + thisRangeAddress.FirstAddress.FixedRow, + thisRangeAddress.FirstAddress.FixedColumn); if (shiftBottomBoundary) - thisRangeAddress.LastAddress = new XLAddress(Worksheet, - newBottomBoundary, - thisRangeAddress.LastAddress.ColumnNumber, - thisRangeAddress.LastAddress.FixedRow, - thisRangeAddress.LastAddress.FixedColumn); + lastAddress = new XLAddress(Worksheet, + newBottomBoundary, + thisRangeAddress.LastAddress.ColumnNumber, + thisRangeAddress.LastAddress.FixedRow, + thisRangeAddress.LastAddress.FixedColumn); + + return new XLRangeAddress(firstAddress, lastAddress); } public IXLRange RangeUsed() @@ -1706,8 +1691,7 @@ public IXLAutoFilter SetAutoFilter(Boolean value) { if (value) - using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return Worksheet.AutoFilter.Set(this); else return Worksheet.AutoFilter.Clear(); } @@ -1955,8 +1939,7 @@ RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); - return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + return Worksheet.RangeColumn(new XLRangeAddress(firstCellAddress, lastCellAddress)); } public XLRangeRow RowQuick(Int32 row) @@ -1971,24 +1954,14 @@ RangeAddress.LastAddress.ColumnNumber, false, false); - return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + + return Worksheet.RangeRow(new XLRangeAddress(firstCellAddress, lastCellAddress)); } - public void Dispose() + /*public void Dispose() { - if (_shiftedRowsAction != null) - { - RangeAddress.Worksheet.RangeShiftedRows.Remove(_shiftedRowsAction); - _shiftedRowsAction = null; - } - - if (_shiftedColumnsAction != null) - { - RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); - _shiftedColumnsAction = null; - } - } + // Dispose does nothing but left for not breaking the existing code + }*/ public IXLDataValidation SetDataValidation() { @@ -2016,12 +1989,8 @@ if (thisStart > dvStart && thisEnd < dvEnd) { - var r1 = Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1); - r1.Dispose(); - dv.Ranges.Add(r1); - var r2 = Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd); - r2.Dispose(); - dv.Ranges.Add(r2); + dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1)); + dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd)); } else { @@ -2041,16 +2010,13 @@ if (coEnd >= dvStart) { - var r = Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd); - r.Dispose(); - dv.Ranges.Add(r); + dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd)); } } } } else { - column.Dispose(); dv.Ranges.Add(column); } } @@ -2073,23 +2039,17 @@ public IXLConditionalFormat AddConditionalFormat() { - using (var asRange = AsRange()) - { - var cf = new XLConditionalFormat(asRange); - Worksheet.ConditionalFormats.Add(cf); - return cf; - } + var cf = new XLConditionalFormat(AsRange()); + Worksheet.ConditionalFormats.Add(cf); + return cf; } internal IXLConditionalFormat AddConditionalFormat(IXLConditionalFormat source) { - using (var asRange = AsRange()) - { - var cf = new XLConditionalFormat(asRange); - cf.CopyFrom(source); - Worksheet.ConditionalFormats.Add(cf); - return cf; - } + var cf = new XLConditionalFormat(AsRange()); + cf.CopyFrom(source); + Worksheet.ConditionalFormats.Add(cf); + return cf; } public void Select() diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index c027859..9ab39d0 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -7,19 +7,12 @@ { #region Constructor - public XLRangeColumn(XLRangeParameters rangeParameters, bool quickLoad) + /// + /// The direct contructor should only be used in . + /// + public XLRangeColumn(XLRangeParameters rangeParameters) : base(rangeParameters.RangeAddress, (rangeParameters.DefaultStyle as XLStyle).Value) { - if (quickLoad) return; - - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - } - - public XLRangeColumn(XLRangeParameters rangeParameters, bool quickLoad, IXLTable table) - : this(rangeParameters, quickLoad) - { - this.Table = table; } #endregion Constructor @@ -187,19 +180,24 @@ #endregion IXLRangeColumn Members + public override XLRangeType RangeType + { + get { return XLRangeType.RangeColumn; } + } + public XLCell Cell(int row) { return Cell(row, 1); } - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - ShiftColumns(RangeAddress, range, columnsShifted); + RangeAddress = (XLRangeAddress)ShiftColumns(RangeAddress, range, columnsShifted); } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - ShiftRows(RangeAddress, range, rowsShifted); + RangeAddress = (XLRangeAddress)ShiftRows(RangeAddress, range, rowsShifted); } public XLRange Range(int firstRow, int lastRow) @@ -339,8 +337,7 @@ if (IsTableColumn()) throw new InvalidOperationException("This column is already part of a table."); - using (var asRange = AsRange()) - return asRange.AsTable(); + return AsRange().AsTable(); } public IXLTable AsTable(string name) @@ -348,8 +345,7 @@ if (IsTableColumn()) throw new InvalidOperationException("This column is already part of a table."); - using (var asRange = AsRange()) - return asRange.AsTable(name); + return AsRange().AsTable(name); } public IXLTable CreateTable() @@ -357,8 +353,7 @@ if (IsTableColumn()) throw new InvalidOperationException("This column is already part of a table."); - using (var asRange = AsRange()) - return asRange.CreateTable(); + return AsRange().CreateTable(); } public IXLTable CreateTable(string name) @@ -366,8 +361,7 @@ if (IsTableColumn()) throw new InvalidOperationException("This column is already part of a table."); - using (var asRange = AsRange()) - return asRange.CreateTable(name); + return AsRange().CreateTable(name); } public new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.All) diff --git a/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 170cd3f..e73de1c 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -119,12 +119,6 @@ #endregion IXLStylized Members - public void Dispose() - { - if (_ranges != null) - _ranges.ForEach(r => r.Dispose()); - } - public void Select() { foreach (var range in this) diff --git a/ClosedXML/Excel/Ranges/XLRangeFactory.cs b/ClosedXML/Excel/Ranges/XLRangeFactory.cs new file mode 100644 index 0000000..6c88d26 --- /dev/null +++ b/ClosedXML/Excel/Ranges/XLRangeFactory.cs @@ -0,0 +1,91 @@ +using System; + +namespace ClosedXML.Excel +{ + internal class XLRangeFactory + { + #region Properties + + public XLWorksheet Worksheet { get; private set; } + + #endregion Properties + + #region Constructors + + public XLRangeFactory(XLWorksheet worksheet) + { + if (worksheet == null) + throw new ArgumentNullException(nameof(worksheet)); + Worksheet = worksheet; + } + + #endregion Constructors + + #region Methods + + public XLRangeBase Create(XLRangeKey key) + { + switch (key.RangeType) + { + case XLRangeType.Range: + return CreateRange(key.RangeAddress); + + case XLRangeType.Column: + return CreateColumn(key.RangeAddress.FirstAddress.ColumnNumber); + + case XLRangeType.Row: + return CreateColumn(key.RangeAddress.FirstAddress.RowNumber); + + case XLRangeType.RangeColumn: + return CreateRangeColumn(key.RangeAddress); + + case XLRangeType.RangeRow: + return CreateRangeRow(key.RangeAddress); + + case XLRangeType.Table: + return CreateTable(key.RangeAddress); + + case XLRangeType.Worksheet: + //break; + default: + throw new NotImplementedException(key.RangeType.ToString()); + break; + } + } + + public XLRange CreateRange(XLRangeAddress rangeAddress) + { + var xlRangeParameters = new XLRangeParameters(rangeAddress, Worksheet.Style); + return new XLRange(xlRangeParameters); + } + + public XLColumn CreateColumn(int columnNumber) + { + return new XLColumn(Worksheet, columnNumber); + } + + public XLRow CreateRow(int rowNumber) + { + return new XLRow(Worksheet, rowNumber); + } + + public XLRangeColumn CreateRangeColumn(XLRangeAddress rangeAddress) + { + var xlRangeParameters = new XLRangeParameters(rangeAddress, Worksheet.Style); + return new XLRangeColumn(xlRangeParameters); + } + + public XLRangeRow CreateRangeRow(XLRangeAddress rangeAddress) + { + var xlRangeParameters = new XLRangeParameters(rangeAddress, Worksheet.Style); + return new XLRangeRow(xlRangeParameters); + } + + public XLTable CreateTable(XLRangeAddress rangeAddress) + { + return new XLTable(new XLRangeParameters(rangeAddress, Worksheet.Style)); + } + + #endregion Methods + } +} diff --git a/ClosedXML/Excel/Ranges/XLRangeKey.cs b/ClosedXML/Excel/Ranges/XLRangeKey.cs new file mode 100644 index 0000000..8fb4842 --- /dev/null +++ b/ClosedXML/Excel/Ranges/XLRangeKey.cs @@ -0,0 +1,60 @@ +using System; + +namespace ClosedXML.Excel +{ + [System.Diagnostics.DebuggerDisplay("{RangeType} {RangeAddress}")] + internal struct XLRangeKey : IEquatable + { + public XLRangeType RangeType { get; private set; } + + public XLRangeAddress RangeAddress { get; private set; } + + public XLRangeKey(XLRangeType rangeType, XLRangeAddress address) + { + RangeType = rangeType; + RangeAddress = address; + } + + #region Overrides + + /// Indicates whether the current object is equal to another object of the same type. + /// true if the current object is equal to the parameter; otherwise, false. + /// An object to compare with this object. + public bool Equals(XLRangeKey other) + { + return RangeType == other.RangeType && + RangeAddress.Equals(other.RangeAddress); + } + + /// Indicates whether this instance and a specified object are equal. + /// true if and this instance are the same type and represent the same value; otherwise, false. + /// Another object to compare to. + public override bool Equals(object obj) + { + if (ReferenceEquals(null, obj)) return false; + return obj is XLRangeKey && Equals((XLRangeKey)obj); + } + + /// Returns the hash code for this instance. + /// A 32-bit signed integer that is the hash code for this instance. + public override int GetHashCode() + { + unchecked + { + return ((int)RangeType * 397) ^ RangeAddress.GetHashCode(); + } + } + + public static bool operator ==(XLRangeKey left, XLRangeKey right) + { + return left.Equals(right); + } + + public static bool operator !=(XLRangeKey left, XLRangeKey right) + { + return !(left == right); + } + + #endregion Overrides + } +} diff --git a/ClosedXML/Excel/Ranges/XLRangeParameters.cs b/ClosedXML/Excel/Ranges/XLRangeParameters.cs index 97b19ab..2c93bab 100644 --- a/ClosedXML/Excel/Ranges/XLRangeParameters.cs +++ b/ClosedXML/Excel/Ranges/XLRangeParameters.cs @@ -7,7 +7,6 @@ public XLRangeParameters(XLRangeAddress rangeAddress, IXLStyle defaultStyle) { RangeAddress = rangeAddress; - _ignoreEvents = !rangeAddress.Worksheet.EventTrackingEnabled; DefaultStyle = defaultStyle; } @@ -18,16 +17,6 @@ public XLRangeAddress RangeAddress { get; private set; } public IXLStyle DefaultStyle { get; private set; } - private bool _ignoreEvents; - public bool IgnoreEvents - { - get { return _ignoreEvents; } - set - { - _ignoreEvents = value; - } - } - #endregion } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index bd0034c..83c46fe 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -7,22 +7,16 @@ { #region Constructor - public XLRangeRow(XLRangeParameters rangeParameters, bool quickLoad) + /// + /// The direct contructor should only be used in . + /// + public XLRangeRow(XLRangeParameters rangeParameters) : base(rangeParameters.RangeAddress, (rangeParameters.DefaultStyle as XLStyle).Value) { - RangeParameters = rangeParameters; - if (quickLoad) return; - if (!RangeParameters.IgnoreEvents) - { - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - } } #endregion Constructor - public XLRangeParameters RangeParameters { get; private set; } - #region IXLRangeRow Members public IXLCell Cell(int column) @@ -183,15 +177,19 @@ } #endregion IXLRangeRow Members - - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + public override XLRangeType RangeType { - ShiftColumns(RangeAddress, range, columnsShifted); + get { return XLRangeType.RangeRow; } } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - ShiftRows(RangeAddress, range, rowsShifted); + RangeAddress = (XLRangeAddress)ShiftColumns(RangeAddress, range, columnsShifted); + } + + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + { + RangeAddress = (XLRangeAddress)ShiftRows(RangeAddress, range, rowsShifted); } public IXLRange Range(int firstColumn, int lastColumn) @@ -295,10 +293,7 @@ rowNum, RangeAddress.LastAddress.ColumnNumber); - var result = range.FirstRow(); - range.Dispose(); - - return result; + return range.FirstRow(); } #region XLRangeRow Above diff --git a/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/Excel/Ranges/XLRangeRows.cs index 553e493..1ada22a 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -120,12 +120,6 @@ #endregion IXLStylized Members - public void Dispose() - { - if (_ranges != null) - _ranges.ForEach(r => r.Dispose()); - } - public void Select() { foreach (var range in this) diff --git a/ClosedXML/Excel/Ranges/XLRangeType.cs b/ClosedXML/Excel/Ranges/XLRangeType.cs new file mode 100644 index 0000000..1042d86 --- /dev/null +++ b/ClosedXML/Excel/Ranges/XLRangeType.cs @@ -0,0 +1,13 @@ +namespace ClosedXML.Excel +{ + internal enum XLRangeType : byte + { + Range = 0, + Column = 1, + Row = 2, + RangeColumn = 3, + RangeRow = 4, + Table = 5, + Worksheet = 6 + } +} diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index 3b58d3d..f6cbc2d 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -54,16 +54,7 @@ /// row/column shifting events. Until ranges are unsubscribed they cannot be collected by GC. public void RemoveAll(Predicate match = null, bool releaseEventHandlers = true) { - match = match ?? (_ => true); - - if (releaseEventHandlers) - { - _ranges - .Where(r => match(r)) - .ForEach(r => r.Dispose()); - } - - Count -= _ranges.RemoveAll(match); + Count -= _ranges.RemoveAll(match ?? (_ => true)); } public int Count { get; private set; } @@ -152,11 +143,6 @@ return this; } - public void Dispose() - { - _ranges.ForEach(r => r.Dispose()); - } - #endregion IXLRanges Members #region IXLStylized Members @@ -232,9 +218,7 @@ dv.Ranges.Remove(dvRange); foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString()))) { - var r = c.AsRange(); - r.Dispose(); - dv.Ranges.Add(r); + dv.Ranges.Add(c.AsRange()); } } } diff --git a/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/Excel/Rows/IXLRows.cs index 376a373..9c92118 100644 --- a/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/Excel/Rows/IXLRows.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRows : IEnumerable, IDisposable + public interface IXLRows : IEnumerable { /// /// Sets the height of all rows. diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index e52827d..36dbe9b 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -9,61 +9,36 @@ { #region Private fields - private Boolean _collapsed; private Double _height; - private Boolean _isHidden; private Int32 _outlineLevel; #endregion Private fields #region Constructor - public XLRow(Int32 row, XLRowParameters xlRowParameters) - : base(new XLRangeAddress(new XLAddress(xlRowParameters.Worksheet, row, 1, false, false), - new XLAddress(xlRowParameters.Worksheet, row, XLHelper.MaxColumnNumber, false, - false)), - xlRowParameters.IsReference ? xlRowParameters.Worksheet.Internals.RowsCollection[row].StyleValue - : (xlRowParameters.DefaultStyle as XLStyle).Value - ) + /// + /// The direct contructor should only be used in . + /// + public XLRow(XLWorksheet worksheet, Int32 row) + : base(XLRangeAddress.EntireRow(worksheet, row), worksheet.StyleValue) { SetRowNumber(row); - IsReference = xlRowParameters.IsReference; - if (IsReference) - SubscribeToShiftedRows((range, rowShifted) => this.WorksheetRangeShiftedRows(range, rowShifted)); - else - _height = xlRowParameters.Worksheet.RowHeight; - } - - public XLRow(XLRow row) - : base(new XLRangeAddress(new XLAddress(row.Worksheet, row.RowNumber(), 1, false, false), - new XLAddress(row.Worksheet, row.RowNumber(), XLHelper.MaxColumnNumber, false, - false)), - row.StyleValue) - { - _height = row._height; - IsReference = row.IsReference; - if (IsReference) - SubscribeToShiftedRows((range, rowShifted) => this.WorksheetRangeShiftedRows(range, rowShifted)); - - _collapsed = row._collapsed; - _isHidden = row._isHidden; - _outlineLevel = row._outlineLevel; - HeightChanged = row.HeightChanged; + _height = worksheet.RowHeight; } #endregion Constructor - public Boolean IsReference { get; private set; } + public override XLRangeType RangeType + { + get { return XLRangeType.Row; } + } public override IEnumerable Styles { get { - if (IsReference) - yield return Worksheet.Internals.RowsCollection[RowNumber()].Style; - else - yield return Style; + yield return Style; int row = RowNumber(); @@ -77,58 +52,30 @@ get { int row = RowNumber(); - if (IsReference) - yield return Worksheet.Internals.RowsCollection[row]; - else - { - foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) - yield return cell; - } + + foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) + yield return cell; + } } - public Boolean Collapsed - { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Collapsed : _collapsed; } - set - { - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].Collapsed = value; - else - _collapsed = value; - } - } + public Boolean Collapsed { get; set; } #region IXLRow Members - private Boolean _loading; - - public Boolean Loading - { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Loading : _loading; } - set - { - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].Loading = value; - else - _loading = value; - } - } + public Boolean Loading { get; set; } public Boolean HeightChanged { get; private set; } public Double Height { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Height : _height; } + get { return _height; } set { if (!Loading) HeightChanged = true; - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].Height = value; - else - _height = value; + _height = value; } } @@ -141,30 +88,17 @@ public void Delete() { int rowNumber = RowNumber(); - using (var asRange = AsRange()) - asRange.Delete(XLShiftDeletedCells.ShiftCellsUp); - - Worksheet.Internals.RowsCollection.Remove(rowNumber); - var rowsToMove = new List(); - rowsToMove.AddRange(Worksheet.Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); - foreach (int row in rowsToMove.OrderBy(r => r)) - { - Worksheet.Internals.RowsCollection.Add(row - 1, Worksheet.Internals.RowsCollection[row]); - Worksheet.Internals.RowsCollection.Remove(row); - } + AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); + Worksheet.DeleteRow(rowNumber); } public new IXLRows InsertRowsBelow(Int32 numberOfRows) { int rowNum = RowNumber(); Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); - using (var row = Worksheet.Row(rowNum)) - { - using (var asRange = row.AsRange()) - { - asRange.InsertRowsBelowVoid(true, numberOfRows); - } - } + var asRange = Worksheet.Row(rowNum).AsRange(); + asRange.InsertRowsBelowVoid(true, numberOfRows); + var newRows = Worksheet.Rows(rowNum + 1, rowNum + numberOfRows); CopyRows(newRows); @@ -179,8 +113,8 @@ var internalRow = Worksheet.Internals.RowsCollection[newRow.RowNumber()]; internalRow._height = Height; internalRow.InnerStyle = InnerStyle; - internalRow._collapsed = Collapsed; - internalRow._isHidden = IsHidden; + internalRow.Collapsed = Collapsed; + internalRow.IsHidden = IsHidden; internalRow._outlineLevel = OutlineLevel; } } @@ -190,20 +124,12 @@ int rowNum = RowNumber(); if (rowNum > 1) { - using (var row = Worksheet.Row(rowNum - 1)) - { - return row.InsertRowsBelow(numberOfRows); - } + return Worksheet.Row(rowNum - 1).InsertRowsBelow(numberOfRows); } Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); - using (var row = Worksheet.Row(rowNum)) - { - using (var asRange = row.AsRange()) - { - asRange.InsertRowsAboveVoid(true, numberOfRows); - } - } + var asRange = Worksheet.Row(rowNum).AsRange(); + asRange.InsertRowsAboveVoid(true, numberOfRows); return Worksheet.Rows(rowNum, rowNum + numberOfRows - 1); } @@ -376,35 +302,19 @@ return this; } - public Boolean IsHidden - { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].IsHidden : _isHidden; } - set - { - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].IsHidden = value; - else - _isHidden = value; - } - } - + public Boolean IsHidden { get; set; } public Int32 OutlineLevel { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].OutlineLevel : _outlineLevel; } + get { return _outlineLevel; } set { if (value < 0 || value > 8) throw new ArgumentOutOfRangeException("value", "Outline level must be between 0 and 8."); - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].OutlineLevel = value; - else - { - Worksheet.IncrementColumnOutline(value); - Worksheet.DecrementColumnOutline(_outlineLevel); - _outlineLevel = value; - } + Worksheet.IncrementColumnOutline(value); + Worksheet.DecrementColumnOutline(_outlineLevel); + _outlineLevel = value; } } @@ -482,16 +392,14 @@ IXLRangeRow IXLRow.CopyTo(IXLCell target) { - using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Row(1); + var copy = AsRange().CopyTo(target); + return copy.Row(1); } IXLRangeRow IXLRow.CopyTo(IXLRangeBase target) { - using (var asRange = AsRange()) - using (var copy = asRange.CopyTo(target)) - return copy.Row(1); + var copy = AsRange().CopyTo(target); + return copy.Row(1); } public IXLRow CopyTo(IXLRow row) @@ -501,8 +409,7 @@ newRow._height = _height; newRow.InnerStyle = GetStyle(); - using (var asRange = AsRange()) - asRange.CopyTo(row).Dispose(); + AsRange().CopyTo(row); return newRow; } @@ -522,8 +429,8 @@ var retVal = new XLRangeRows(); var rowPairs = rows.Split(','); foreach (string pair in rowPairs) - using (var asRange = AsRange()) - asRange.Rows(pair.Trim()).ForEach(retVal.Add); + AsRange().Rows(pair.Trim()).ForEach(retVal.Add); + return retVal; } @@ -551,29 +458,26 @@ return Range(1, 1, 1, XLHelper.MaxColumnNumber); } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - if (range.RangeAddress.IsValid && - RangeAddress.IsValid && - range.RangeAddress.FirstAddress.RowNumber <= RowNumber()) - SetRowNumber(RowNumber() + rowsShifted); + //do nothing } - private void SetRowNumber(Int32 row) + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - if (row <= 0) - RangeAddress.IsValid = false; - else - { - RangeAddress.IsValid = true; - RangeAddress.FirstAddress = new XLAddress(Worksheet, row, 1, RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn); - RangeAddress.LastAddress = new XLAddress(Worksheet, - row, - XLHelper.MaxColumnNumber, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn); - } + return; // rows are shifted by XLRowCollection + } + + internal void SetRowNumber(Int32 row) + { + RangeAddress = new XLRangeAddress( + new XLAddress(Worksheet, row, 1, RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + row, + XLHelper.MaxColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } public override XLRange Range(String rangeAddressStr) @@ -603,16 +507,11 @@ internal void SetStyleNoColumns(IXLStyle value) { - if (IsReference) - Worksheet.Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value); - else - { - InnerStyle = value; + InnerStyle = value; - int row = RowNumber(); - foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) - c.InnerStyle = value; - } + int row = RowNumber(); + foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) + c.InnerStyle = value; } private XLRow RowShift(Int32 rowsToShift) diff --git a/ClosedXML/Excel/Rows/XLRowCollection.cs b/ClosedXML/Excel/Rows/XLRowCollection.cs index 6aa2e49..a8bc397 100644 --- a/ClosedXML/Excel/Rows/XLRowCollection.cs +++ b/ClosedXML/Excel/Rows/XLRowCollection.cs @@ -6,7 +6,7 @@ { using System.Collections; - internal class XLRowsCollection : IDictionary, IDisposable + internal class XLRowsCollection : IDictionary { private readonly Dictionary _deleted = new Dictionary(); private readonly Dictionary _dictionary = new Dictionary(); @@ -122,28 +122,20 @@ return _dictionary.GetEnumerator(); } - #endregion + #endregion IDictionary Members public void ShiftRowsDown(Int32 startingRow, Int32 rowsToShift) { foreach (int ro in _dictionary.Keys.Where(k => k >= startingRow).OrderByDescending(k => k)) { var rowToMove = _dictionary[ro]; + _dictionary.Remove(ro); Int32 newRowNum = ro + rowsToShift; if (newRowNum <= XLHelper.MaxRowNumber) { - var newRow = new XLRow(rowToMove) - { - RangeAddress = - { - FirstAddress = new XLAddress(newRowNum, 1, false, false), - LastAddress = - new XLAddress(newRowNum, XLHelper.MaxColumnNumber, false, false) - } - }; - _dictionary.Add(newRowNum, newRow); + rowToMove.SetRowNumber(newRowNum); + _dictionary.Add(newRowNum, rowToMove); } - _dictionary.Remove(ro); } } @@ -156,10 +148,5 @@ _dictionary.RemoveAll(predicate); } - - public void Dispose() - { - _dictionary.Values.ForEach(r=>r.Dispose()); - } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Rows/XLRowParameters.cs b/ClosedXML/Excel/Rows/XLRowParameters.cs index db9394e..2b7424a 100644 --- a/ClosedXML/Excel/Rows/XLRowParameters.cs +++ b/ClosedXML/Excel/Rows/XLRowParameters.cs @@ -5,15 +5,13 @@ { internal class XLRowParameters { - public XLRowParameters(XLWorksheet worksheet, IXLStyle defaultStyle, Boolean isReference = true) + public XLRowParameters(XLWorksheet worksheet, IXLStyle defaultStyle) { Worksheet = worksheet; DefaultStyle = defaultStyle; - IsReference = isReference; } public IXLStyle DefaultStyle { get; private set; } public XLWorksheet Worksheet { get; private set; } - public Boolean IsReference { get; private set; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/Excel/Rows/XLRows.cs index 8494f8f..7a76463 100644 --- a/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/Excel/Rows/XLRows.cs @@ -245,12 +245,6 @@ return this; } - public void Dispose() - { - if (_rows != null) - _rows.ForEach(r => r.Dispose()); - } - public void Select() { foreach (var range in this) diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 9fb10ec..4c2fb00 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -16,7 +16,7 @@ Custom } - public interface IXLTableField : IDisposable + public interface IXLTableField { /// /// Gets the corresponding column for this table field. diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 380a326..b7a791c 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -10,7 +10,6 @@ internal class XLTable : XLRange, IXLTable { #region Private fields - private string _name; internal bool _showTotalsRow; internal HashSet _uniqueNames; @@ -19,38 +18,22 @@ #region Constructor - public XLTable(XLRange range, Boolean addToTables, Boolean setAutofilter = true) - : base(new XLRangeParameters(range.RangeAddress, range.Style)) + /// + /// The direct contructor should only be used in . + /// + public XLTable(XLRangeParameters xlRangeParameters) + : base(xlRangeParameters) { - CheckRangeNotInTable(range); - InitializeValues(setAutofilter); - - Int32 id = 1; - while (true) - { - string tableName = String.Concat("Table", id); - if (!Worksheet.Tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase))) - { - Name = tableName; - AddToTables(range, addToTables); - break; - } - id++; - } - } - - public XLTable(XLRange range, String name, Boolean addToTables, Boolean setAutofilter = true) - : base(new XLRangeParameters(range.RangeAddress, range.Style)) - { - CheckRangeNotInTable(range); - InitializeValues(setAutofilter); - - Name = name; - AddToTables(range, addToTables); + InitializeValues(false); } #endregion Constructor + public override XLRangeType RangeType + { + get { return XLRangeType.Table; } + } + private IXLRangeAddress _lastRangeAddress; private Dictionary _fieldNames = null; @@ -183,13 +166,10 @@ { get { - using (var asRange = ShowTotalsRow ? Range(1, 1, RowCount() - 1, ColumnCount()) : AsRange()) - { - if (_autoFilter == null) - _autoFilter = new XLAutoFilter(); + if (_autoFilter == null) + _autoFilter = new XLAutoFilter(); - _autoFilter.Range = asRange; - } + _autoFilter.Range = ShowTotalsRow ? Range(1, 1, RowCount() - 1, ColumnCount()) : AsRange(); return _autoFilter; } } @@ -199,6 +179,11 @@ return AutoFilter; } + protected override void OnRangeAddressChanged(XLRangeAddress oldAddress, XLRangeAddress newAddress) + { + //Do nothing for table + } + #region IXLTable Members public Boolean EmphasizeFirstColumn { get; set; } @@ -402,7 +387,7 @@ this.TotalsRow().AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); } - this.RangeAddress = range.RangeAddress as XLRangeAddress; + this.RangeAddress = (XLRangeAddress)range.RangeAddress; RescanFieldNames(); if (this.ShowTotalsRow) @@ -558,16 +543,6 @@ get { return AutoFilter; } } - public new void Dispose() - { - if (AutoFilter != null) - AutoFilter.Dispose(); - - Fields?.ForEach(field => field.Dispose()); - - base.Dispose(); - } - #endregion IXLTable Members private void InitializeValues(Boolean setAutofilter) @@ -589,20 +564,17 @@ ShowAutoFilter = true; } - private void AddToTables(XLRange range, Boolean addToTables) + internal void OnAddedToTables() { - if (!addToTables) return; - _uniqueNames = new HashSet(); Int32 co = 1; - foreach (IXLCell c in range.Row(1).Cells()) + foreach (IXLCell c in Row(1).Cells()) { if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; } - Worksheet.Tables.Add(this); } private String GetUniqueName(String originalName, Int32 initialOffset, Boolean enforceOffset) @@ -657,57 +629,53 @@ } headersRow.Clear(); - RangeAddress.FirstAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + 1, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn); + RangeAddress = new XLRangeAddress( + new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + RangeAddress.LastAddress); } else { - using (var asRange = Worksheet.Range( + var asRange = Worksheet.Range( RangeAddress.FirstAddress.RowNumber - 1, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber - )) - using (var firstRow = asRange.FirstRow()) + RangeAddress.LastAddress.ColumnNumber); + var firstRow = asRange.FirstRow(); + IXLRangeRow rangeRow; + if (firstRow.IsEmpty(true)) { - IXLRangeRow rangeRow; - if (firstRow.IsEmpty(true)) - { - rangeRow = firstRow; - RangeAddress.FirstAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber - 1, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn); - } - else - { - var fAddress = RangeAddress.FirstAddress; - var lAddress = RangeAddress.LastAddress; + rangeRow = firstRow; + RangeAddress = new XLRangeAddress( + new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber - 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + RangeAddress.LastAddress); + } + else + { + var fAddress = RangeAddress.FirstAddress; + //var lAddress = RangeAddress.LastAddress; - rangeRow = firstRow.InsertRowsBelow(1, false).First(); + rangeRow = firstRow.InsertRowsBelow(1, false).First(); - RangeAddress.FirstAddress = new XLAddress(Worksheet, fAddress.RowNumber, - fAddress.ColumnNumber, - fAddress.FixedRow, - fAddress.FixedColumn); + RangeAddress = new XLRangeAddress( + fAddress, + RangeAddress.LastAddress); + } - RangeAddress.LastAddress = new XLAddress(Worksheet, lAddress.RowNumber + 1, - lAddress.ColumnNumber, - lAddress.FixedRow, - lAddress.FixedColumn); - } - - Int32 co = 1; - foreach (var name in FieldNames.Values.Select(f => f.Name)) - { - rangeRow.Cell(co).SetValue(name); - co++; - } + Int32 co = 1; + foreach (var name in FieldNames.Values.Select(f => f.Name)) + { + rangeRow.Cell(co).SetValue(name); + co++; } } + _showHeaderRow = value; if (_showHeaderRow) @@ -728,10 +696,12 @@ public void ExpandTableRows(Int32 rows) { - RangeAddress.LastAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber + rows, - RangeAddress.LastAddress.ColumnNumber, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn); + RangeAddress = new XLRangeAddress( + RangeAddress.FirstAddress, + new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber + rows, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } public override XLRangeColumn Column(int columnNumber) @@ -861,12 +831,5 @@ return table; } - - private static void CheckRangeNotInTable(XLRange range) - { - var overlappingTables = range.Worksheet.Tables.Where(t => t.RangeUsed().Intersects(range)); - if (overlappingTables.Any()) - throw new ArgumentException(nameof(range), $"The range {range.RangeAddress.ToStringRelative(true)} is already part of table '{overlappingTables.First().Name}'"); - } } } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 32c5c17..9b6ee39 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -28,8 +28,7 @@ { if (_column == null) { - using (var range = this.table.AsRange()) - _column = range.Column(this.Index + 1); + _column = table.AsRange().Column(this.Index + 1); } return _column; } @@ -213,10 +212,5 @@ } } } - - public void Dispose() - { - _column.Dispose(); - } } } diff --git a/ClosedXML/Excel/Tables/XLTableRange.cs b/ClosedXML/Excel/Tables/XLTableRange.cs index 13e7ef3..24c30ab 100644 --- a/ClosedXML/Excel/Tables/XLTableRange.cs +++ b/ClosedXML/Excel/Tables/XLTableRange.cs @@ -8,7 +8,8 @@ { private readonly XLTable _table; private readonly XLRange _range; - public XLTableRange(XLRange range, XLTable table):base(range.RangeParameters) + public XLTableRange(XLRange range, XLTable table) + : base(new XLRangeParameters(range.RangeAddress, range.Style)) { _table = table; _range = range; @@ -29,8 +30,6 @@ { var row = new XLTableRow(this, (_range.Row(ro))); if (predicate(row)) return row; - - row.Dispose(); } return null; @@ -62,7 +61,6 @@ if (!row.IsEmpty(includeFormats) && predicate(row)) return row; - row.Dispose(); } return null; @@ -84,8 +82,6 @@ { var row = new XLTableRow(this, (_range.Row(ro))); if (predicate(row)) return row; - - row.Dispose(); } return null; } @@ -116,7 +112,6 @@ if (!row.IsEmpty(includeFormats) && predicate(row)) return row; - row.Dispose(); } return null; @@ -147,8 +142,6 @@ var row = Row(r); if (predicate == null || predicate(row)) retVal.Add(row); - else - row.Dispose(); } return retVal; } @@ -203,8 +196,6 @@ if (!row.IsEmpty(includeFormats) && (predicate == null || predicate(row))) rows.Add(row); - else - row.Dispose(); } return rows; } @@ -242,4 +233,4 @@ return Column(_table.GetFieldIndex(column) + 1); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/Excel/Tables/XLTableRow.cs index 5d96e00..3acff51 100644 --- a/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/Excel/Tables/XLTableRow.cs @@ -7,7 +7,7 @@ private readonly XLTableRange _tableRange; public XLTableRow(XLTableRange tableRange, XLRangeRow rangeRow) - : base(rangeRow.RangeParameters, false) + : base(new XLRangeParameters(rangeRow.RangeAddress, rangeRow.Style)) { _tableRange = tableRange; } diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs index 492b7dd..9ae8e7f 100644 --- a/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/Excel/Tables/XLTables.cs @@ -32,6 +32,7 @@ public void Add(IXLTable table) { _tables.Add(table.Name, table); + (table as XLTable)?.OnAddedToTables(); } public IXLTable Table(Int32 index) diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index f8b93a9..c521125 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -2090,23 +2090,20 @@ if (String.IsNullOrWhiteSpace(txt)) continue; foreach (var rangeAddress in txt.Split(' ')) { - using (var range = ws.Range(rangeAddress)) - { - var dvt = range.SetDataValidation(); - if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; - if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; - if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; - if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; - if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; - if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; - if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; - if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; - if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); - if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); - if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); - if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; - if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; - } + var dvt = ws.Range(rangeAddress).SetDataValidation(); + if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; + if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; + if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; + if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; + if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; + if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; + if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; + if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; + if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); + if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); + if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); + if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; + if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; } } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 8e391f6..4e4afad 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1,6 +1,6 @@ +using ClosedXML.Excel.Caching; using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.Drawings; -using ClosedXML.Excel.Misc; using System; using System.Collections.Generic; using System.Drawing; @@ -11,17 +11,12 @@ { internal class XLWorksheet : XLRangeBase, IXLWorksheet { - #region Events - - public XLReentrantEnumerableSet RangeShiftedRows; - public XLReentrantEnumerableSet RangeShiftedColumns; - - #endregion Events - #region Fields private readonly Dictionary _columnOutlineCount = new Dictionary(); private readonly Dictionary _rowOutlineCount = new Dictionary(); + private readonly XLRangeFactory _rangeFactory; + private readonly XLRangeRepository _rangeRepository; internal Int32 ZOrder = 1; private String _name; internal Int32 _position; @@ -30,6 +25,10 @@ private Boolean _tabActive; internal Boolean EventTrackingEnabled; + /// + /// Fake address to be used everywhere the invalid address is needed. + /// + internal readonly XLAddress InvalidAddress; #endregion Fields #region Constructor @@ -44,15 +43,15 @@ EventTrackingEnabled = workbook.EventTracking == XLEventTracking.Enabled; Workbook = workbook; + InvalidAddress = new XLAddress(this, 0, 0, false, false); - RangeShiftedRows = new XLReentrantEnumerableSet(); - RangeShiftedColumns = new XLReentrantEnumerableSet(); - - RangeAddress.Worksheet = this; - RangeAddress.FirstAddress = new XLAddress(this, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); - RangeAddress.LastAddress = new XLAddress(this, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, - RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); + var firstAddress = new XLAddress(this, RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); + var lastAddress = new XLAddress(this, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); + RangeAddress = new XLRangeAddress(firstAddress, lastAddress); + _rangeFactory = new XLRangeFactory(this); + _rangeRepository = new XLRangeRepository(workbook, _rangeFactory.Create); Pictures = new XLPictures(this); NamedRanges = new XLNamedRanges(this); @@ -72,8 +71,6 @@ _rowHeight = workbook.RowHeight; RowHeightChanged = Math.Abs(workbook.RowHeight - XLWorkbook.DefaultRowHeight) > XLHelper.Epsilon; Name = sheetName; - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); Charts = new XLCharts(); ShowFormulas = workbook.ShowFormulas; ShowGridLines = workbook.ShowGridLines; @@ -91,6 +88,11 @@ #endregion Constructor + public override XLRangeType RangeType + { + get { return XLRangeType.Worksheet; } + } + //private IXLStyle _style; private const String InvalidNameChars = @":\/?*[]"; @@ -99,6 +101,11 @@ private Double _columnWidth; public XLWorksheetInternals Internals { get; private set; } + public XLRangeFactory RangeFactory + { + get { return _rangeFactory; } + } + public override IEnumerable Styles { get @@ -371,9 +378,9 @@ firstRow = tPair; lastRow = tPair; } - using (var xlRows = Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - foreach (IXLRow row in xlRows) - retVal.Add((XLRow)row); + + Rows(Int32.Parse(firstRow), Int32.Parse(lastRow)) + .ForEach(row => retVal.Add((XLRow)row)); } return retVal; } @@ -555,8 +562,8 @@ public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position) { var targetSheet = (XLWorksheet)workbook.WorksheetsInternal.Add(newSheetName, position); - Internals.ColumnsCollection.ForEach(kp => targetSheet.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); - Internals.RowsCollection.ForEach(kp => targetSheet.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value))); + Internals.ColumnsCollection.ForEach(kp => kp.Value.CopyTo(targetSheet.Column(kp.Key))); + Internals.RowsCollection.ForEach(kp => kp.Value.CopyTo(targetSheet.Row(kp.Key))); Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c, false)); DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv))); targetSheet.Visibility = Visibility; @@ -570,8 +577,6 @@ (targetSheet.PageSetup.Footer as XLHeaderFooter).Changed = true; targetSheet.Outline = new XLOutline(Outline); targetSheet.SheetView = new XLSheetView(SheetView); - Internals.MergedRanges.ForEach( - kp => targetSheet.Internals.MergedRanges.Add(targetSheet.Range(kp.RangeAddress.ToString()))); foreach (var picture in Pictures) { @@ -587,15 +592,19 @@ break; case XLPicturePlacement.Move: - var newAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, picture.TopLeftCellAddress.ColumnNumber, false, false); + var newAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, + picture.TopLeftCellAddress.ColumnNumber, false, false); newPic.MoveTo(newAddress, picture.GetOffset(XLMarkerPosition.TopLeft)); break; case XLPicturePlacement.MoveAndSize: - var newFromAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, picture.TopLeftCellAddress.ColumnNumber, false, false); - var newToAddress = new XLAddress(targetSheet, picture.BottomRightCellAddress.RowNumber, picture.BottomRightCellAddress.ColumnNumber, false, false); + var newFromAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, + picture.TopLeftCellAddress.ColumnNumber, false, false); + var newToAddress = new XLAddress(targetSheet, picture.BottomRightCellAddress.RowNumber, + picture.BottomRightCellAddress.ColumnNumber, false, false); - newPic.MoveTo(newFromAddress, picture.GetOffset(XLMarkerPosition.TopLeft), newToAddress, picture.GetOffset(XLMarkerPosition.BottomRight)); + newPic.MoveTo(newFromAddress, picture.GetOffset(XLMarkerPosition.TopLeft), newToAddress, + picture.GetOffset(XLMarkerPosition.BottomRight)); break; } } @@ -607,19 +616,20 @@ { if (this == r.Worksheet) // Named ranges on the source worksheet have to point to the new destination sheet - ranges.Add(targetSheet.Range(r.RangeAddress.FirstAddress.RowNumber, r.RangeAddress.FirstAddress.ColumnNumber, r.RangeAddress.LastAddress.RowNumber, r.RangeAddress.LastAddress.ColumnNumber)); + ranges.Add(targetSheet.Range(r.RangeAddress.FirstAddress.RowNumber, + r.RangeAddress.FirstAddress.ColumnNumber, r.RangeAddress.LastAddress.RowNumber, + r.RangeAddress.LastAddress.ColumnNumber)); else ranges.Add(r); } + targetSheet.NamedRanges.Add(nr.Name, ranges); } foreach (var t in Tables.Cast()) { String tableName = t.Name; - var table = targetSheet.Tables.Any(tt => tt.Name == tableName) - ? new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true) - : new XLTable(targetSheet.Range(t.RangeAddress.ToString()), tableName, true); + var table = (XLTable)targetSheet.Table(targetSheet.Range(t.RangeAddress.ToString()), tableName, true); table.RelId = t.RelId; table.EmphasizeFirstColumn = t.EmphasizeFirstColumn; @@ -645,8 +655,14 @@ } if (AutoFilter.Enabled) - using (var range = targetSheet.Range(AutoFilter.Range.RangeAddress.FirstAddress.RowNumber, AutoFilter.Range.RangeAddress.FirstAddress.ColumnNumber, AutoFilter.Range.RangeAddress.LastAddress.RowNumber, AutoFilter.Range.RangeAddress.LastAddress.ColumnNumber)) - range.SetAutoFilter(); + { + var range = targetSheet.Range( + AutoFilter.Range.RangeAddress.FirstAddress.RowNumber, + AutoFilter.Range.RangeAddress.FirstAddress.ColumnNumber, + AutoFilter.Range.RangeAddress.LastAddress.RowNumber, + AutoFilter.Range.RangeAddress.LastAddress.ColumnNumber); + range.SetAutoFilter(); + } return targetSheet; } @@ -900,22 +916,18 @@ { if (XLHelper.IsValidRangeAddress(rangeAddressStr)) { - using (var range = Range(new XLRangeAddress(Worksheet, rangeAddressStr))) - retVal.Add(range); + retVal.Add(Range(new XLRangeAddress(Worksheet, rangeAddressStr))); } else if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0)) { - using (var xlRanges = NamedRange(rangeAddressStr).Ranges) - xlRanges.ForEach(retVal.Add); + NamedRange(rangeAddressStr).Ranges.ForEach(retVal.Add); } else { - using (var xlRanges = Workbook.NamedRanges.First(n => + Workbook.NamedRanges.First(n => String.Compare(n.Name, rangeAddressStr, true) == 0 - && n.Ranges.First().Worksheet == this).Ranges) - { - xlRanges.ForEach(retVal.Add); - } + && n.Ranges.First().Worksheet == this).Ranges + .ForEach(retVal.Add); } } return retVal; @@ -937,8 +949,6 @@ var row = Row(rowNum); if (!row.IsEmpty(includeFormats) && (predicate == null || predicate(row))) rows.Add(row); - else - row.Dispose(); } return rows; } @@ -959,8 +969,6 @@ var column = Column(columnNum); if (!column.IsEmpty(includeFormats) && (predicate == null || predicate(column))) columns.Add(column); - else - column.Dispose(); } return columns; } @@ -970,18 +978,11 @@ return ColumnsUsed(false, predicate); } - public new void Dispose() + public void Dispose() { - if (AutoFilter != null) - AutoFilter.Dispose(); - Internals.Dispose(); - - SelectedRanges?.Dispose(); - DataValidations?.Dispose(); - this.Pictures.ForEach(p => p.Dispose()); - - base.Dispose(); + Pictures.ForEach(p => p.Dispose()); + _rangeRepository.Clear(); } #endregion IXLWorksheet Members @@ -1046,9 +1047,8 @@ public XLRow FirstRowUsed(Boolean includeFormats) { - using (var asRange = AsRange()) - using (var rngRow = asRange.FirstRowUsed(includeFormats)) - return rngRow != null ? Row(rngRow.RangeAddress.FirstAddress.RowNumber) : null; + var rngRow = AsRange().FirstRowUsed(includeFormats); + return rngRow != null ? Row(rngRow.RangeAddress.FirstAddress.RowNumber) : null; } public XLRow LastRowUsed() @@ -1058,9 +1058,8 @@ public XLRow LastRowUsed(Boolean includeFormats) { - using (var asRange = AsRange()) - using (var rngRow = asRange.LastRowUsed(includeFormats)) - return rngRow != null ? Row(rngRow.RangeAddress.LastAddress.RowNumber) : null; + var rngRow = AsRange().LastRowUsed(includeFormats); + return rngRow != null ? Row(rngRow.RangeAddress.LastAddress.RowNumber) : null; } public XLColumn LastColumn() @@ -1090,9 +1089,8 @@ public XLColumn FirstColumnUsed(Boolean includeFormats) { - using (var asRange = AsRange()) - using (var rngColumn = asRange.FirstColumnUsed(includeFormats)) - return rngColumn != null ? Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber) : null; + var rngColumn = AsRange().FirstColumnUsed(includeFormats); + return rngColumn != null ? Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber) : null; } public XLColumn LastColumnUsed() @@ -1102,9 +1100,8 @@ public XLColumn LastColumnUsed(Boolean includeFormats) { - using (var asRange = AsRange()) - using (var rngColumn = asRange.LastColumnUsed(includeFormats)) - return rngColumn != null ? Column(rngColumn.RangeAddress.LastAddress.ColumnNumber) : null; + var rngColumn = AsRange().LastColumnUsed(includeFormats); + return rngColumn != null ? Column(rngColumn.RangeAddress.LastAddress.ColumnNumber) : null; } public XLRow Row(Int32 row) @@ -1112,23 +1109,26 @@ return Row(row, true); } - public XLColumn Column(Int32 column) + public XLColumn Column(Int32 columnNumber) { - if (column <= 0 || column > XLHelper.MaxColumnNumber) + if (columnNumber <= 0 || columnNumber > XLHelper.MaxColumnNumber) throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber)); - var thisStyle = Style; - if (!Internals.ColumnsCollection.ContainsKey(column)) + XLColumn column; + if (Internals.ColumnsCollection.TryGetValue(columnNumber, out column)) + return column; + else { - // This is a new row so we're going to reference all - // cells in this row to preserve their formatting - Internals.RowsCollection.Keys.ForEach(r => Cell(r, column)); - Internals.ColumnsCollection.Add(column, - new XLColumn(column, new XLColumnParameters(this, thisStyle, false))); + // This is a new column so we're going to reference all + // cells in this column to preserve their formatting + Internals.RowsCollection.Keys.ForEach(r => Cell(r, columnNumber)); + + column = RangeFactory.CreateColumn(columnNumber); + Internals.ColumnsCollection.Add(columnNumber, column); } - return new XLColumn(column, new XLColumnParameters(this, thisStyle, true)); + return column; } public IXLColumn Column(String column) @@ -1149,28 +1149,22 @@ Internals.RowsCollection.Clear(); } - private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { - var newMerge = new XLRanges(); - foreach (IXLRange rngMerged in Internals.MergedRanges) + if (!range.IsEntireColumn()) { - if (range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber - && rngMerged.RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber - && rngMerged.RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber) + var model = Worksheet.Range(range.RangeAddress.FirstAddress, + new XLAddress(range.RangeAddress.LastAddress.RowNumber, XLHelper.MaxColumnNumber, false, false)); + var rangesToSplit = Worksheet.MergedRanges + .Where(mr => mr.Intersects(model)) // in #803 this must be optimized too + .Where(r => r.RangeAddress.FirstAddress.RowNumber < range.RangeAddress.FirstAddress.RowNumber || + r.RangeAddress.LastAddress.RowNumber > range.RangeAddress.LastAddress.RowNumber) + .ToList(); + foreach (var rangeToSplit in rangesToSplit) { - var newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber, - rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, - rngMerged.RangeAddress.LastAddress.RowNumber, - rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); - newMerge.Add(newRng); + Worksheet.MergedRanges.Remove(rangeToSplit); } - else if ( - !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) - newMerge.Add(rngMerged); } - Internals.MergedRanges = newMerge; Workbook.Worksheets.ForEach(ws => MoveNamedRangesColumns(range, columnsShifted, ws.NamedRanges)); MoveNamedRangesColumns(range, columnsShifted, Workbook.NamedRanges); @@ -1234,31 +1228,24 @@ if (!cf.Ranges.Any()) ConditionalFormats.Remove(f => f == cf); } - - model.Dispose(); } - private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - var newMerge = new XLRanges(); - foreach (IXLRange rngMerged in Internals.MergedRanges) + if (!range.IsEntireRow()) { - if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber - && rngMerged.RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber - && rngMerged.RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber) + var model = Worksheet.Range(range.RangeAddress.FirstAddress, + new XLAddress(XLHelper.MaxRowNumber, range.RangeAddress.LastAddress.ColumnNumber, false, false)); + var rangesToSplit = Worksheet.MergedRanges + .Where(mr => mr.Intersects(model)) // in #803 this must be optimized too + .Where(r => r.RangeAddress.FirstAddress.ColumnNumber < range.RangeAddress.FirstAddress.ColumnNumber || + r.RangeAddress.LastAddress.ColumnNumber > range.RangeAddress.LastAddress.ColumnNumber) + .ToList(); + foreach (var rangeToSplit in rangesToSplit) { - var newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.FirstAddress.ColumnNumber, - rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.LastAddress.ColumnNumber); - newMerge.Add(newRng); + Worksheet.MergedRanges.Remove(rangeToSplit); } - else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber)) - newMerge.Add(rngMerged); } - Internals.MergedRanges = newMerge; Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges); @@ -1322,8 +1309,6 @@ if (!cf.Ranges.Any()) ConditionalFormats.Remove(f => f == cf); } - - model.Dispose(); } private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges) @@ -1350,35 +1335,56 @@ public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted) { - if (RangeShiftedRows != null) + try { - foreach (var item in RangeShiftedRows) + SuspendEvents(); + var rangesToShift = _rangeRepository.ToList(); + + WorksheetRangeShiftedRows(range, rowsShifted); + foreach (var storedRange in rangesToShift) { - item.Action(range, rowsShifted); + if (!ReferenceEquals(range, storedRange)) + storedRange.WorksheetRangeShiftedRows(range, rowsShifted); } + range.WorksheetRangeShiftedRows(range, rowsShifted); + } + finally + { + ResumeEvents(); } } public void NotifyRangeShiftedColumns(XLRange range, Int32 columnsShifted) { - if (RangeShiftedColumns != null) + try { - foreach (var item in RangeShiftedColumns) + SuspendEvents(); + var rangesToShift = _rangeRepository.ToList(); + + WorksheetRangeShiftedColumns(range, columnsShifted); + foreach (var storedRange in rangesToShift) { - item.Action(range, columnsShifted); + var addr = storedRange.RangeAddress; + if (!ReferenceEquals(range, storedRange)) + storedRange.WorksheetRangeShiftedColumns(range, columnsShifted); } + range.WorksheetRangeShiftedColumns(range, columnsShifted); + } + finally + { + ResumeEvents(); } } - public XLRow Row(Int32 row, Boolean pingCells) + public XLRow Row(Int32 rowNumber, Boolean pingCells) { - if (row <= 0 || row > XLHelper.MaxRowNumber) + if (rowNumber <= 0 || rowNumber > XLHelper.MaxRowNumber) throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber)); - IXLStyle style; - if (Internals.RowsCollection.TryGetValue(row, out XLRow rowToUse)) - style = rowToUse.Style; + XLRow row; + if (Internals.RowsCollection.TryGetValue(rowNumber, out row)) + return row; else { if (pingCells) @@ -1389,16 +1395,69 @@ var usedColumns = from c in Internals.ColumnsCollection join dc in Internals.CellsCollection.ColumnsUsed.Keys on c.Key equals dc - where !Internals.CellsCollection.Contains(row, dc) + where !Internals.CellsCollection.Contains(rowNumber, dc) select dc; - usedColumns.ForEach(c => Cell(row, c)); + usedColumns.ForEach(c => Cell(rowNumber, c)); } - style = Style; - Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, style, false))); + + row = RangeFactory.CreateRow(rowNumber); + Internals.RowsCollection.Add(rowNumber, row); } - return new XLRow(row, new XLRowParameters(this, style)); + return row; + } + + public IXLTable Table(XLRange range, Boolean addToTables, Boolean setAutofilter = true) + { + return Table(range, GetNewTableName("Table"), addToTables, setAutofilter); + } + + public IXLTable Table(XLRange range, String name, Boolean addToTables, Boolean setAutofilter = true) + { + CheckRangeNotInTable(range); + XLRangeAddress rangeAddress; + if (range.Rows().Count() == 1) + { + rangeAddress = new XLRangeAddress(range.FirstCell().Address, range.LastCell().CellBelow().Address); + range.InsertRowsBelow(1); + } + else + rangeAddress = range.RangeAddress; + + var table = (XLTable) _rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.Table, rangeAddress)); + + if (table.Name != name) + table.Name = name; + + if (addToTables && !Tables.Contains(table)) + { + Tables.Add(table); + } + + if (setAutofilter && !table.ShowAutoFilter) + table.InitializeAutoFilter(); + + return table; + } + private void CheckRangeNotInTable(XLRange range) + { + var overlappingTables = Tables.Where(t => t.RangeUsed().Intersects(range)); + if (overlappingTables.Any()) + throw new ArgumentException(nameof(range), $"The range {range.RangeAddress.ToStringRelative(true)} is already part of table '{overlappingTables.First().Name}'"); + } + + private string GetNewTableName(string baseName) + { + var i = 1; + string tableName; + do + { + tableName = baseName + i.ToString(); + i++; + } while (Tables.Any(t => t.Name == tableName)); + + return tableName; } private IXLRange GetRangeForSort() @@ -1438,10 +1497,8 @@ var namedRanges = Workbook.NamedRanges.FirstOrDefault(n => String.Compare(n.Name, cellAddressInRange, true) == 0 && n.Ranges.Count == 1); - if (namedRanges == null || !namedRanges.Ranges.Any()) return null; - using (var rs = namedRanges.Ranges) - return (XLCell)rs.First().FirstCell(); + return (XLCell)namedRanges?.Ranges?.FirstOrDefault()?.FirstCell(); } internal XLCell CellFast(String cellAddressInRange) @@ -1607,5 +1664,96 @@ return cell.Value; } + + public XLRange GetOrCreateRange(XLRangeParameters xlRangeParameters) + { + var range = _rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.Range, xlRangeParameters.RangeAddress)); + if (xlRangeParameters.DefaultStyle != null && range.StyleValue == StyleValue) + range.InnerStyle = xlRangeParameters.DefaultStyle; + + return range as XLRange; + } + + /// + /// Get a range row from the shared repository or create a new one. + /// + /// Address of range row. + /// Style to apply. If null the worksheet's style is applied. + /// Range row with the specified address. + public XLRangeRow RangeRow(XLRangeAddress address, IXLStyle defaultStyle = null) + { + var rangeRow = (XLRangeRow)_rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.RangeRow, address)); + + if (defaultStyle != null && rangeRow.StyleValue == StyleValue) + rangeRow.InnerStyle = defaultStyle; + + return rangeRow; + } + + /// + /// Get a range column from the shared repository or create a new one. + /// + /// Address of range column. + /// Style to apply. If null the worksheet's style is applied. + /// Range column with the specified address. + public XLRangeColumn RangeColumn(XLRangeAddress address, IXLStyle defaultStyle = null) + { + var rangeColumn = (XLRangeColumn)_rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.RangeColumn, address)); + + if (defaultStyle != null && rangeColumn.StyleValue == StyleValue) + rangeColumn.InnerStyle = defaultStyle; + + return rangeColumn; + } + + protected override void OnRangeAddressChanged(XLRangeAddress oldAddress, XLRangeAddress newAddress) + { + } + + public void RellocateRange(XLRangeType rangeType, XLRangeAddress oldAddress, XLRangeAddress newAddress) + { + if (_rangeRepository == null) + return; + + _rangeRepository.Replace(new XLRangeKey(rangeType, oldAddress), + new XLRangeKey(rangeType, newAddress)); + } + + internal void DeleteColumn(int columnNumber) + { + Internals.ColumnsCollection.Remove(columnNumber); + + var columnsToMove = new List(); + columnsToMove.AddRange( + Internals.ColumnsCollection.Where(c => c.Key > columnNumber).Select(c => c.Key)); + foreach (int column in columnsToMove.OrderBy(c => c)) + { + Internals.ColumnsCollection.Add(column - 1, Internals.ColumnsCollection[column]); + Internals.ColumnsCollection.Remove(column); + + Internals.ColumnsCollection[column - 1].SetColumnNumber(column - 1); + } + } + + internal void DeleteRow(int rowNumber) + { + Internals.RowsCollection.Remove(rowNumber); + + var rowsToMove = new List(); + rowsToMove.AddRange(Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); + foreach (int row in rowsToMove.OrderBy(r => r)) + { + Internals.RowsCollection.Add(row - 1, Worksheet.Internals.RowsCollection[row]); + Internals.RowsCollection.Remove(row); + + Internals.RowsCollection[row - 1].SetRowNumber(row - 1); + } + } + + internal void DeleteRange(XLRangeAddress rangeAddress) + { + _rangeRepository.Remove(new XLRangeKey(XLRangeType.Range, rangeAddress)); + } + } } diff --git a/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/Excel/XLWorksheetInternals.cs index 04a73f7..5906987 100644 --- a/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/Excel/XLWorksheetInternals.cs @@ -24,9 +24,9 @@ public void Dispose() { - ColumnsCollection.Dispose(); - RowsCollection.Dispose(); - MergedRanges.Dispose(); + ColumnsCollection.Clear(); + RowsCollection.Clear(); + MergedRanges.RemoveAll(); } } } diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index e1873c1..4e9d64a 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -650,15 +650,13 @@ var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); - using (var range = ws.Range("A1:A10")) - { - range.AddConditionalFormat().WhenEquals("3") - .Fill.SetBackgroundColor(XLColor.Blue); - range.AddConditionalFormat().WhenEquals("2") - .Fill.SetBackgroundColor(XLColor.Green); - range.AddConditionalFormat().WhenEquals("1") - .Fill.SetBackgroundColor(XLColor.Red); - } + var range = ws.Range("A1:A10"); + range.AddConditionalFormat().WhenEquals("3") + .Fill.SetBackgroundColor(XLColor.Blue); + range.AddConditionalFormat().WhenEquals("2") + .Fill.SetBackgroundColor(XLColor.Green); + range.AddConditionalFormat().WhenEquals("1") + .Fill.SetBackgroundColor(XLColor.Red); workbook.SaveAs(filePath); } @@ -695,24 +693,22 @@ { var ws = workbook.AddWorksheet("Sheet1"); - using (var range = ws.Range("A1:A10")) - { - range.AddConditionalFormat() - .WhenDateIs(XLTimePeriod.Tomorrow) - .Fill.SetBackgroundColor(XLColor.GrannySmithApple); + var range = ws.Range("A1:A10"); + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.Tomorrow) + .Fill.SetBackgroundColor(XLColor.GrannySmithApple); - range.AddConditionalFormat() - .WhenDateIs(XLTimePeriod.Yesterday) - .Fill.SetBackgroundColor(XLColor.Orange); + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.Yesterday) + .Fill.SetBackgroundColor(XLColor.Orange); - range.AddConditionalFormat() - .WhenDateIs(XLTimePeriod.InTheLast7Days) - .Fill.SetBackgroundColor(XLColor.Blue); + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.InTheLast7Days) + .Fill.SetBackgroundColor(XLColor.Blue); - range.AddConditionalFormat() - .WhenDateIs(XLTimePeriod.ThisMonth) - .Fill.SetBackgroundColor(XLColor.Red); - } + range.AddConditionalFormat() + .WhenDateIs(XLTimePeriod.ThisMonth) + .Fill.SetBackgroundColor(XLColor.Red); workbook.SaveAs(filePath); } diff --git a/ClosedXML_Tests/Excel/Caching/SampleRepositoryTests.cs b/ClosedXML_Tests/Excel/Caching/SampleRepositoryTests.cs index 2cbbb5f..e36a8ed 100644 --- a/ClosedXML_Tests/Excel/Caching/SampleRepositoryTests.cs +++ b/ClosedXML_Tests/Excel/Caching/SampleRepositoryTests.cs @@ -1,10 +1,10 @@ +using System; +using ClosedXML.Excel; using ClosedXML.Excel.Caching; using NUnit.Framework; -using System; using System.Linq; using System.Threading; using System.Threading.Tasks; -using System.Collections.Generic; namespace ClosedXML_Tests.Excel.Caching { @@ -121,13 +121,67 @@ Assert.NotNull(entities); // To protect them from GC } + [Test] + public void ReplaceKeyInRepository() + { + // Arrange + int key1 = 12345; + int key2 = 54321; + var entity = new SampleEntity(key1); + var sampleRepository = this.CreateSampleRepository(); + var storedEntity1 = sampleRepository.Store(key1, entity); + + // Act + sampleRepository.Replace(key1, key2); + bool containsOld = sampleRepository.ContainsKey(key1, out var _); + bool containsNew = sampleRepository.ContainsKey(key2, out var _); + var storedEntity2 = sampleRepository.GetOrCreate(key2); + + // Assert + Assert.IsFalse(containsOld); + Assert.IsTrue(containsNew); + Assert.AreSame(entity, storedEntity1); + Assert.AreSame(entity, storedEntity2); + } + + [Test] + public void ConcurrentReplaceKeyInRepository() + { + var sampleRepository = new EditableRepository(); + int[] keys = Enumerable.Range(0, 1000).ToArray(); + keys.ForEach(key => sampleRepository.GetOrCreate(key)); + + Parallel.ForEach(keys, key => + { + var val1 = sampleRepository.Replace(key, key + 2000); + val1.Key = key + 2000; + var val2 = sampleRepository.GetOrCreate(key + 2000); + Assert.AreSame(val1, val2); + }); + } + + [Test] + public void ReplaceNonExistingKeyInRepository() + { + int key1 = 100; + int key2 = 200; + int key3 = 300; + var entity = new SampleEntity(key1); + var sampleRepository = this.CreateSampleRepository(); + sampleRepository.Store(key1, entity); + + sampleRepository.Replace(key2, key3); + var all = sampleRepository.ToList(); + + Assert.AreEqual(1, all.Count); + Assert.AreSame(entity, all.First()); + } private SampleRepository CreateSampleRepository() { return new SampleRepository(); } - /// /// Class under testing /// @@ -147,6 +201,27 @@ Key = key; } } + + + /// + /// Class under testing + /// + internal class EditableRepository : XLRepositoryBase + { + public EditableRepository() : base(key => new EditableEntity(key)) + { + } + } + + public class EditableEntity + { + public int Key { get; set; } + + public EditableEntity(int key) + { + Key = key; + } + } } diff --git a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs index 35dc053..5e07723 100644 --- a/ClosedXML_Tests/Excel/Columns/ColumnTests.cs +++ b/ClosedXML_Tests/Excel/Columns/ColumnTests.cs @@ -241,7 +241,7 @@ { var ws = new XLWorkbook().AddWorksheet("Sheet1") as XLWorksheet; - var column = new XLColumn(-1, new XLColumnParameters(ws, XLStyle.Default, false)); + var column = new XLColumn(ws, -1); Assert.IsFalse(column.RangeAddress.IsValid); } diff --git a/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs index 41c128c..7621797 100644 --- a/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using NUnit.Framework; +using System.Linq; namespace ClosedXML_Tests { @@ -19,5 +20,155 @@ Assert.AreEqual("B2", first); Assert.AreEqual("D4", last); } + + [TestCase("A1:A2", "A1:A2")] + [TestCase("A2:B2", "A2:B2")] + [TestCase("A3:C3", "A3:E3")] + [TestCase("B4:B6", "B4:B6")] + [TestCase("C7:D7", "E7:F7")] + public void MergedRangesShiftedOnColumnInsert(string originalRange, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + var range = ws.Range(originalRange).Merge(); + + ws.Column(2).InsertColumnsAfter(2); + + var mr = ws.MergedRanges.ToArray(); + Assert.AreEqual(1, mr.Length); + Assert.AreSame(range, mr.Single()); + Assert.AreEqual(expectedRange, range.RangeAddress.ToString()); + } + } + + [TestCase("A1:B1", "A1:B1")] + [TestCase("B1:B2", "B1:B2")] + [TestCase("C1:C3", "C1:C5")] + [TestCase("D2:F2", "D2:F2")] + [TestCase("G4:G5", "G6:G7")] + public void MergedRangesShiftedOnRowInsert(string originalRange, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + var range = ws.Range(originalRange).Merge(); + + ws.Row(2).InsertRowsBelow(2); + + var mr = ws.MergedRanges.ToArray(); + Assert.AreEqual(1, mr.Length); + Assert.AreSame(range, mr.Single()); + Assert.AreEqual(expectedRange, range.RangeAddress.ToString()); + } + } + + [TestCase("A1:A2", true, "A1:A2")] + [TestCase("A2:B2", true, "A2:A2")] + [TestCase("A3:C3", true, "A3:B3")] + [TestCase("B4:B6", false, "")] + [TestCase("C7:D7", true, "B7:C7")] + public void MergedRangesShiftedOnColumnDelete(string originalRange, bool expectedExist, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + var range = ws.Range(originalRange).Merge(); + + ws.Column(2).Delete(); + + var mr = ws.MergedRanges.ToArray(); + if (expectedExist) + { + Assert.AreEqual(1, mr.Length); + Assert.AreSame(range, mr.Single()); + Assert.AreEqual(expectedRange, range.RangeAddress.ToString()); + } + else + { + Assert.AreEqual(0, mr.Length); + Assert.IsFalse(range.RangeAddress.IsValid); + } + } + } + + [TestCase("A1:B1", true, "A1:B1")] + [TestCase("B1:B2", true, "B1:B1")] + [TestCase("C1:C3", true, "C1:C2")] + [TestCase("D2:F2", false, "")] + [TestCase("G4:G5", true, "G3:G4")] + public void MergedRangesShiftedOnRowDelete(string originalRange, bool expectedExist, string expectedRange) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + var range = ws.Range(originalRange).Merge(); + + ws.Row(2).Delete(); + + var mr = ws.MergedRanges.ToArray(); + if (expectedExist) + { + Assert.AreEqual(1, mr.Length); + Assert.AreSame(range, mr.Single()); + Assert.AreEqual(expectedRange, range.RangeAddress.ToString()); + } + else + { + Assert.AreEqual(0, mr.Length); + Assert.IsFalse(range.RangeAddress.IsValid); + } + } + } + + [TestCase(-2)] + [TestCase(2)] + public void RangeShiftHorizontallyBreaksMerges(int columnShift) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + ws.Range("B2:C3").Merge(); + ws.Range("B4:C5").Merge(); + ws.Range("F2:G3").Merge(); // to be broken + ws.Range("F4:G5").Merge(); // to be broken + ws.Range("H1:I2").Merge(); + ws.Range("H5:I6").Merge(); + + ws.Range("D3:E4").InsertColumnsAfter(columnShift); + + var mr = ws.MergedRanges.ToArray(); + Assert.AreEqual(4, mr.Length); + Assert.AreEqual("B2:C3", mr[0].RangeAddress.ToString()); + Assert.AreEqual("B4:C5", mr[1].RangeAddress.ToString()); + Assert.AreEqual("H1:I2", mr[2].RangeAddress.ToString()); + Assert.AreEqual("H5:I6", mr[3].RangeAddress.ToString()); + } + } + + [TestCase(-2)] + [TestCase(2)] + public void RangeShiftVerticallyBreaksMerges(int rowShift) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("MRShift"); + ws.Range("B2:C3").Merge(); + ws.Range("D2:E3").Merge(); + ws.Range("B6:C7").Merge(); // to be broken + ws.Range("D6:E7").Merge(); // to be broken + ws.Range("A8:B9").Merge(); + ws.Range("E8:F9").Merge(); + + ws.Range("C4:D5").InsertRowsBelow(rowShift); + + var mr = ws.MergedRanges.ToArray(); + Assert.AreEqual(4, mr.Length); + Assert.AreEqual("B2:C3", mr[0].RangeAddress.ToString()); + Assert.AreEqual("D2:E3", mr[1].RangeAddress.ToString()); + Assert.AreEqual("A8:B9", mr[2].RangeAddress.ToString()); + Assert.AreEqual("E8:F9", mr[3].RangeAddress.ToString()); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index c103c6e..42077e6 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -47,5 +47,27 @@ Assert.AreEqual("'Sheet 1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true)); Assert.AreEqual("'Sheet 1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true)); } + + [TestCase("B2:E5", "B2:E5")] + [TestCase("E5:B2", "B2:E5")] + [TestCase("B5:E2", "B2:E5")] + [TestCase("B2:E$5", "B2:E$5")] + [TestCase("B2:$E$5", "B2:$E$5")] + [TestCase("B$2:$E$5", "B$2:$E$5")] + [TestCase("$B$2:$E$5", "$B$2:$E$5")] + [TestCase("B5:E$2", "B$2:E5")] + [TestCase("$B$5:E2", "$B2:E$5")] + [TestCase("$B$5:E$2", "$B$2:E$5")] + [TestCase("$B$5:$E$2", "$B$2:$E$5")] + public void RangeAddressNormalizeTest(string inputAddress, string expectedAddress) + { + XLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet 1") as XLWorksheet; + var rangeAddress = new XLRangeAddress(ws, inputAddress); + + var normalizedAddress = rangeAddress.Normalize(); + + Assert.AreSame(ws, rangeAddress.Worksheet); + Assert.AreEqual(expectedAddress, normalizedAddress.ToString()); + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs index 5214be4..67f1e8b 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs @@ -390,24 +390,6 @@ } [Test] - public void RangesRemoveAllWithDispose() - { - var ws = new XLWorkbook().Worksheets.Add("Sheet1"); - var ranges = new XLRanges(); - ranges.Add(ws.Range("A1:A2")); - ranges.Add(ws.Range("B1:B2")); - var rangesCopy = ranges.ToList(); - - ranges.RemoveAll(); - ws.FirstColumn().InsertColumnsBefore(1); - - Assert.AreEqual(0, ranges.Count); - // if ranges were disposed they addresses didn't change - Assert.AreEqual("A1:A2", rangesCopy.First().RangeAddress.ToString()); - Assert.AreEqual("B1:B2", rangesCopy.Last().RangeAddress.ToString()); - } - - [Test] public void RangesRemoveAllWithoutDispose() { var ws = new XLWorkbook().Worksheets.Add("Sheet1"); diff --git a/ClosedXML_Tests/Excel/Rows/RowTests.cs b/ClosedXML_Tests/Excel/Rows/RowTests.cs index 5314b54..14da694 100644 --- a/ClosedXML_Tests/Excel/Rows/RowTests.cs +++ b/ClosedXML_Tests/Excel/Rows/RowTests.cs @@ -259,7 +259,7 @@ { var ws = new XLWorkbook().AddWorksheet("Sheet1") as XLWorksheet; - var row = new XLRow(-1, new XLRowParameters(ws, XLStyle.Default, false)); + var row = new XLRow(ws, -1); Assert.IsFalse(row.RangeAddress.IsValid); } diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index f440bd3..37bd741 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -89,8 +89,8 @@ .CellBelow().SetValue("X"); ws.Range("A1").CreateTable(); - Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); - Assert.AreEqual(ws.Cell("A3").GetString(), "X"); + Assert.AreEqual(String.Empty, ws.Cell("A2").GetString()); + Assert.AreEqual("X", ws.Cell("A3").GetString()); } } diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx index adb5b86..64e85ed 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx index ffd35b3..4eee40a 100644 --- a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx +++ b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx Binary files differ