diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 5b273ff..3f57037 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -119,8 +119,9 @@ - + + @@ -131,6 +132,8 @@ + + @@ -192,7 +195,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 8571774..c90afad 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -180,5 +180,7 @@ Boolean HasRichText { get; } Boolean IsMerged(); + Boolean IsUsed(); + Boolean IsUsed(Boolean includeFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 514d5ad..13258c8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -1842,5 +1842,14 @@ { return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); } + + public Boolean IsUsed() + { + return IsUsed(false); + } + public Boolean IsUsed(Boolean includeFormats) + { + return !StringExtensions.IsNullOrWhiteSpace(InnerText) || (includeFormats && (!Style.Equals(Worksheet.Style) || IsMerged())); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs deleted file mode 100644 index 8943083..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollection.cs +++ /dev/null @@ -1,144 +0,0 @@ -using System; -using System.Collections; -using System.Collections.Generic; -using System.Linq; - -namespace ClosedXML.Excel -{ - internal class XLCellCollection : IDictionary - { - #region Private fields - private readonly Dictionary m_dictionary = new Dictionary(); - private readonly Dictionary m_deleted = new Dictionary(); - #endregion - public Dictionary Deleted - { - get { return m_deleted; } - } - - public void Add(IXLAddress key, XLCell value) - { - if (m_deleted.ContainsKey(key)) - { - m_deleted.Remove(key); - } - - m_dictionary.Add(key, value); - } - - public bool ContainsKey(IXLAddress key) - { - return m_dictionary.ContainsKey(key); - } - - public ICollection Keys - { - get { return m_dictionary.Keys; } - } - - public bool Remove(IXLAddress key) - { - if (!m_deleted.ContainsKey(key)) - { - m_deleted.Add(key, m_dictionary[key]); - } - - return m_dictionary.Remove(key); - } - - public bool TryGetValue(IXLAddress key, out XLCell value) - { - return m_dictionary.TryGetValue(key, out value); - } - - public ICollection Values - { - get { return m_dictionary.Values; } - } - - public XLCell this[IXLAddress key] - { - get { return m_dictionary[key]; } - set { m_dictionary[key] = value; } - } - - public void Add(KeyValuePair item) - { - if (m_deleted.ContainsKey(item.Key)) - { - m_deleted.Remove(item.Key); - } - m_dictionary.Add(item.Key, item.Value); - } - - public void Clear() - { - foreach (var kp in m_dictionary) - { - if (!m_deleted.ContainsKey(kp.Key)) - { - m_deleted.Add(kp.Key, kp.Value); - } - } - m_dictionary.Clear(); - } - - public bool Contains(KeyValuePair item) - { - return m_dictionary.Contains(item); - } - - public void CopyTo(KeyValuePair[] array, int arrayIndex) - { - throw new NotImplementedException(); - } - - public int Count - { - get { return m_dictionary.Count; } - } - - public bool IsReadOnly - { - get { return false; } - } - - public bool Remove(KeyValuePair item) - { - if (!m_deleted.ContainsKey(item.Key)) - { - m_deleted.Add(item.Key, m_dictionary[item.Key]); - } - - return m_dictionary.Remove(item.Key); - } - - public IEnumerator> GetEnumerator() - { - return m_dictionary.GetEnumerator(); - } - - IEnumerator IEnumerable.GetEnumerator() - { - return m_dictionary.GetEnumerator(); - } - - public void RemoveAll() - { - RemoveAll(c => true); - } - - public void RemoveAll(Func predicate) - { - foreach (var kp in m_dictionary.Values.Where(predicate).Select(c => c)) - { - if (!m_deleted.ContainsKey(kp.Address)) - { - m_deleted.Add(kp.Address, kp); - } - } - - m_dictionary.RemoveAll(predicate); - } - } -} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs new file mode 100644 index 0000000..53eec57 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellCollectionOLD.cs @@ -0,0 +1,144 @@ +using System; +using System.Collections; +using System.Collections.Generic; +using System.Linq; + +namespace ClosedXML.Excel +{ + internal class XLCellCollectionOLD : IDictionary + { + #region Private fields + private readonly Dictionary m_dictionary = new Dictionary(); + private readonly Dictionary m_deleted = new Dictionary(); + #endregion + public Dictionary Deleted + { + get { return m_deleted; } + } + + public void Add(IXLAddress key, XLCell value) + { + if (m_deleted.ContainsKey(key)) + { + m_deleted.Remove(key); + } + + m_dictionary.Add(key, value); + } + + public bool ContainsKey(IXLAddress key) + { + return m_dictionary.ContainsKey(key); + } + + public ICollection Keys + { + get { return m_dictionary.Keys; } + } + + public bool Remove(IXLAddress key) + { + if (!m_deleted.ContainsKey(key)) + { + m_deleted.Add(key, m_dictionary[key]); + } + + return m_dictionary.Remove(key); + } + + public bool TryGetValue(IXLAddress key, out XLCell value) + { + return m_dictionary.TryGetValue(key, out value); + } + + public ICollection Values + { + get { return m_dictionary.Values; } + } + + public XLCell this[IXLAddress key] + { + get { return m_dictionary[key]; } + set { m_dictionary[key] = value; } + } + + public void Add(KeyValuePair item) + { + if (m_deleted.ContainsKey(item.Key)) + { + m_deleted.Remove(item.Key); + } + m_dictionary.Add(item.Key, item.Value); + } + + public void Clear() + { + foreach (var kp in m_dictionary) + { + if (!m_deleted.ContainsKey(kp.Key)) + { + m_deleted.Add(kp.Key, kp.Value); + } + } + m_dictionary.Clear(); + } + + public bool Contains(KeyValuePair item) + { + return m_dictionary.Contains(item); + } + + public void CopyTo(KeyValuePair[] array, int arrayIndex) + { + throw new NotImplementedException(); + } + + public int Count + { + get { return m_dictionary.Count; } + } + + public bool IsReadOnly + { + get { return false; } + } + + public bool Remove(KeyValuePair item) + { + if (!m_deleted.ContainsKey(item.Key)) + { + m_deleted.Add(item.Key, m_dictionary[item.Key]); + } + + return m_dictionary.Remove(item.Key); + } + + public IEnumerator> GetEnumerator() + { + return m_dictionary.GetEnumerator(); + } + + IEnumerator IEnumerable.GetEnumerator() + { + return m_dictionary.GetEnumerator(); + } + + public void RemoveAll() + { + RemoveAll(c => true); + } + + public void RemoveAll(Func predicate) + { + foreach (var kp in m_dictionary.Values.Where(predicate).Select(c => c)) + { + if (!m_deleted.ContainsKey(kp.Address)) + { + m_deleted.Add(kp.Address, kp); + } + } + + m_dictionary.RemoveAll(predicate); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 2dafc2f..db27ad7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -1,7 +1,6 @@ using System; using System.Collections; using System.Collections.Generic; -using System.Linq; namespace ClosedXML.Excel { @@ -31,28 +30,28 @@ public IEnumerator GetEnumerator() { - var cellsInRanges = new Dictionary>(); + var cellsInRanges = new Dictionary>(); foreach (XLRangeAddress range in _rangeAddresses) { - HashSet hash; + HashSet hash; if (cellsInRanges.ContainsKey(range.Worksheet)) hash = cellsInRanges[range.Worksheet]; else { - hash = new HashSet(); + hash = new HashSet(); cellsInRanges.Add(range.Worksheet, hash); } if (_usedCellsOnly) { var tmpRange = range; - var addressList = range.Worksheet.Internals.CellsCollection.Keys - .Where(a => a.RowNumber >= tmpRange.FirstAddress.RowNumber && - a.RowNumber <= tmpRange.LastAddress.RowNumber && - a.ColumnNumber >= tmpRange.FirstAddress.ColumnNumber && - a.ColumnNumber <= tmpRange.LastAddress.ColumnNumber); + var addressList = range.Worksheet.Internals.CellsCollection.GetSheetPoints( + tmpRange.FirstAddress.RowNumber, + tmpRange.FirstAddress.ColumnNumber, + tmpRange.LastAddress.RowNumber, + tmpRange.LastAddress.ColumnNumber); - foreach (IXLAddress a in addressList) + foreach (XLSheetPoint a in addressList) { if (!hash.Contains(a)) hash.Add(a); @@ -73,7 +72,7 @@ { for (Int32 co = mm.MinColumn; co <= mm.MaxColumn; co++) { - var address = new XLAddress(range.Worksheet, ro, co, false, false); + var address = new XLSheetPoint(ro, co); if (!hash.Contains(address)) hash.Add(address); } @@ -84,27 +83,22 @@ if (_usedCellsOnly) { - foreach (KeyValuePair> cir in cellsInRanges) + foreach (var cir in cellsInRanges) { - var cellsCollection = cir.Key.Internals.CellsCollection; - foreach (IXLAddress a in cir.Value) + foreach (XLSheetPoint a in cir.Value) { - if (cellsCollection.ContainsKey(a)) - { - var cell = cellsCollection[a]; - if (!StringExtensions.IsNullOrWhiteSpace((cell).InnerText) - || (_includeFormats && (!cell.Style.Equals(cir.Key.Style) || cell.IsMerged()))) - yield return cell; - } + var cell = cir.Key.Internals.CellsCollection.GetCell(a); + if (cell != null && cell.IsUsed(_includeFormats)) + yield return cell; } } } else { - foreach (KeyValuePair> cir in cellsInRanges) + foreach (var cir in cellsInRanges) { - foreach (IXLAddress address in cir.Value) - yield return cir.Key.Cell(address); + foreach (XLSheetPoint a in cir.Value) + yield return cir.Key.Cell(a.Row, a.Column); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs new file mode 100644 index 0000000..7256745 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs @@ -0,0 +1,304 @@ +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel +{ + internal class XLCellsCollection + { + private const Int32 InitialRowCapacity = 10000; + private const Int32 InitialColumnCapacity = 50; + private XLCell[,] _cells; + private Int32 _columnCapacity = InitialColumnCapacity; + private Int32 _rowCapacity = InitialRowCapacity; + + public Int32 MaxColumnUsed; + public Int32 MaxRowUsed; + public Int32 Count { get; private set; } + public HashSet Deleted = new HashSet(); + public Dictionary RowsUsed = new Dictionary(); + public Dictionary ColumnsUsed = new Dictionary(); + + public XLCellsCollection() + { + Clear(); + } + + private void ResizeIfNecessary(Int32 row, Int32 column) + { + if (row >= _rowCapacity || column >= _columnCapacity) + { + if (row >= _rowCapacity) + { + _rowCapacity = (Int32)((Double)_rowCapacity * 2); + + if (_rowCapacity < row) + _rowCapacity = (Int32)((Double)row * 1.5); + + if (_rowCapacity > ExcelHelper.MaxRowNumber) + _rowCapacity = ExcelHelper.MaxRowNumber; + } + + if (column >= _columnCapacity) + { + _columnCapacity = (Int32)((Double)_columnCapacity * 2); + + if (_columnCapacity < column) + _columnCapacity = (Int32)((Double)column * 1.5); + + if (_columnCapacity > ExcelHelper.MaxColumnNumber) + _columnCapacity = ExcelHelper.MaxColumnNumber; + } + + _cells = ExcelHelper.ResizeArray(_cells, _rowCapacity + 1, _columnCapacity + 1); + } + } + + public void Add(XLSheetPoint sheetPoint, XLCell cell) + { + Add(sheetPoint.Row, sheetPoint.Column, cell); + } + public void Add(Int32 row, Int32 column, XLCell cell) + { + Count++; + + IncrementUsage(RowsUsed, row); + IncrementUsage(ColumnsUsed, column); + + ResizeIfNecessary(row, column); + _cells[row, column] = cell; + if (row > MaxRowUsed) MaxRowUsed = row; + if (column > MaxColumnUsed) MaxColumnUsed = column; + var sp = new XLSheetPoint(row, column); + if (Deleted.Contains(sp)) + Deleted.Remove(sp); + } + + private static void IncrementUsage(Dictionary dictionary, Int32 key) + { + if (dictionary.ContainsKey(key)) + dictionary[key]++; + else + dictionary.Add(key, 1); + } + + private static void DecrementUsage(Dictionary dictionary, Int32 key) + { + Int32 count; + if (dictionary.TryGetValue(key, out count)) + { + if (count > 0) + dictionary[key]--; + else + dictionary.Remove(key); + } + } + + public void Clear() + { + Count = 0; + RowsUsed.Clear(); + ColumnsUsed.Clear(); + + for (int ro = 1; ro <= MaxRowUsed; ro++) + { + for (int co = 1; co <= MaxColumnUsed; co++) + { + if (_cells[ro, co] != null) + { + var sp = new XLSheetPoint(ro, co); + if (!Deleted.Contains(sp)) + Deleted.Add(sp); + } + } + } + _rowCapacity = InitialRowCapacity; + _columnCapacity = InitialColumnCapacity; + MaxRowUsed = 0; + MaxColumnUsed = 0; + _cells = new XLCell[_rowCapacity,_columnCapacity]; + } + + public void Remove(XLSheetPoint sheetPoint) + { + Remove(sheetPoint.Row,sheetPoint.Column); + } + public void Remove(Int32 row, Int32 column) + { + Count--; + DecrementUsage(RowsUsed, row); + DecrementUsage(ColumnsUsed, row); + var sp = new XLSheetPoint(row, column); + Deleted.Add(sp); + _cells[row, column] = null; + } + + public IEnumerable GetCells(Int32 rowStart, Int32 columnStart, + Int32 rowEnd, Int32 columnEnd) + { + int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; + int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; + for (int ro = rowStart; ro <= finalRow; ro++) + { + for (int co = columnStart; co <= finalColumn; co++) + { + var cell = _cells[ro, co]; + if (cell != null) + { + yield return cell; + } + } + } + } + + public void RemoveAll(Int32 rowStart, Int32 columnStart, + Int32 rowEnd, Int32 columnEnd) + { + int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; + int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; + for (int ro = rowStart; ro <= finalRow; ro++) + { + for (int co = columnStart; co <= finalColumn; co++) + { + var cell = _cells[ro, co]; + if (cell != null) + { + Remove(ro,co); + } + } + } + } + + public IEnumerable GetSheetPoints(Int32 rowStart, Int32 columnStart, + Int32 rowEnd, Int32 columnEnd) + { + int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; + int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; + + for (int ro = rowStart; ro <= finalRow; ro++) + { + for (int co = columnStart; co <= finalColumn; co++) + { + var cell = _cells[ro, co]; + if (cell != null) + { + yield return new XLSheetPoint(ro,co); + } + } + } + } + + public XLCell GetCell(Int32 row, Int32 column) + { + if (row > MaxRowUsed || column > MaxColumnUsed) + return null; + + return _cells[row, column]; + } + + public XLCell GetCell(XLSheetPoint sheetPoint) + { + return _cells[sheetPoint.Row, sheetPoint.Column]; + } + + internal void SwapRanges(XLSheetRange sheetRange1, XLSheetRange sheetRange2) + { + Int32 rowCount = sheetRange1.LastPoint.Row - sheetRange1.FirstPoint.Row + 1; + Int32 columnCount = sheetRange1.LastPoint.Column - sheetRange1.FirstPoint.Column + 1; + for (int row = 0; row < rowCount; row++) + { + for (int column = 0; column < columnCount; column++) + { + var cell1 = _cells[sheetRange1.FirstPoint.Row + row, sheetRange1.FirstPoint.Column + column]; + var cell2 = _cells[sheetRange2.FirstPoint.Row + row, sheetRange2.FirstPoint.Column + column]; + + var cell1Address = cell1.Address; + + cell1.Address = cell2.Address; + cell2.Address = cell1Address; + + _cells[sheetRange1.FirstPoint.Row + row, sheetRange1.FirstPoint.Column + column] = cell2; + _cells[sheetRange2.FirstPoint.Row + row, sheetRange2.FirstPoint.Column + column] = cell1; + } + } + } + + internal IEnumerable GetCells() + { + return GetCells(1, 1, MaxRowUsed, MaxColumnUsed); + } + + internal IEnumerable GetCells(Func predicate) + { + for (int ro = 1; ro <= MaxRowUsed; ro++) + { + for (int co = 1; co <= MaxColumnUsed; co++) + { + var cell = _cells[ro, co]; + if (cell != null && predicate(cell)) + { + yield return cell; + } + } + } + } + + public Boolean Contains(Int32 row, Int32 column) + { + return _cells[row, column] != null; + } + + public Int32 MinRowInColumn(Int32 column) + { + for (int row = 1; row <= MaxRowUsed; row++) + { + if (_cells[row, column] != null) + return row; + } + + return 0; + } + + public Int32 MaxRowInColumn(Int32 column) + { + for (int row = MaxRowUsed; row >= 1; row--) + { + if (_cells[row, column] != null) + return row; + } + + return 0; + } + + public Int32 MinColumnInRow(Int32 row) + { + for (int column = 1; column <= MaxColumnUsed; column++) + { + if (_cells[row, column] != null) + return column; + } + + return 0; + } + + public Int32 MaxColumnInRow(Int32 row) + { + for (int column = MaxColumnUsed; column >= 1; column--) + { + if (_cells[row, column] != null) + return column; + } + + return 0; + } + + public IEnumerable GetCellsInColumn(Int32 column) + { + return GetCells(1, column, MaxRowUsed, column); + } + + public IEnumerable GetCellsInRow(Int32 row) + { + return GetCells(row, 1, row, MaxColumnUsed); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index a813416..e7807ea 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -173,14 +173,10 @@ Int32 minRow = 1; Int32 maxRow = 0; var column = ColumnNumber(); - if ((Worksheet).Internals.CellsCollection.Values.Any(c => c.Address.ColumnNumber == column)) + if (Worksheet.Internals.CellsCollection.ColumnsUsed.ContainsKey(column)) { - minRow = (Worksheet).Internals.CellsCollection.Values - .Where(c => c.Address.ColumnNumber == column) - .Min(c => c.Address.RowNumber); - maxRow = (Worksheet).Internals.CellsCollection.Values - .Where(c => c.Address.ColumnNumber == column) - .Max(c => c.Address.RowNumber); + minRow = Worksheet.Internals.CellsCollection.MinRowInColumn(column); + maxRow = Worksheet.Internals.CellsCollection.MaxRowInColumn(column); } if ((Worksheet).Internals.RowsCollection.Count > 0) @@ -216,9 +212,9 @@ var column = ColumnNumber(); Int32 minRow = 1; Int32 maxRow = 0; - if ((Worksheet).Internals.CellsCollection.Values.Any(c => c.Address.ColumnNumber == column)) + if (Worksheet.Internals.CellsCollection.ColumnsUsed.ContainsKey(column)) { - maxRow = (Worksheet).Internals.CellsCollection.Values.Where(c => c.Address.ColumnNumber == column).Max(c => c.Address.RowNumber); + maxRow = Worksheet.Internals.CellsCollection.MaxRowInColumn(column); } if ((Worksheet).Internals.RowsCollection.Count > 0) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnParameters.cs index 9e6106e..84053aa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnParameters.cs @@ -5,7 +5,7 @@ { internal class XLColumnParameters { - public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle, Boolean isReference = true) + public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle, Boolean isReference) { Worksheet = worksheet; DefaultStyle = defaultStyle; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 6177765..e0371c1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -4,94 +4,58 @@ namespace ClosedXML.Excel { + using System.Collections; + internal class XLColumns : IXLColumns, IXLStylized { - private XLWorksheet worksheet; + private readonly List columns = new List(); + private readonly XLWorksheet worksheet; + internal IXLStyle style; + public XLColumns(XLWorksheet worksheet) { this.worksheet = worksheet; style = new XLStyle(this, XLWorkbook.DefaultStyle); } - List columns = new List(); + #region IXLColumns Members + public IEnumerator GetEnumerator() { var retList = new List(); - columns.ForEach(c => retList.Add(c)); + columns.ForEach(retList.Add); return retList.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #region IXLStylized Members - - internal IXLStyle style; public IXLStyle Style { - get - { - return style; - } + get { return style; } set { style = new XLStyle(this, value); if (worksheet != null) - { worksheet.Style = value; - } else { - foreach (var column in columns) - { + foreach (XLColumn column in columns) column.Style = value; - } } } } - public IEnumerable Styles - { - get - { - UpdatingStyle = true; - yield return style; - if (worksheet != null) - { - yield return worksheet.Style; - } - else - { - foreach (var col in columns) - { - foreach (var s in col.Styles) - yield return s; - } - } - UpdatingStyle = false; - } - } - - public Boolean UpdatingStyle { get; set; } - - public IXLStyle InnerStyle - { - get { return style; } - set { style = new XLStyle(this, value); } - } - - #endregion - public Double Width { set { columns.ForEach(c => c.Width = value); - if (worksheet !=null) + if (worksheet != null) { worksheet.ColumnWidth = value; worksheet.Internals.ColumnsCollection.ForEach(c => c.Value.Width = value); @@ -109,7 +73,7 @@ else { var toDelete = new Dictionary>(); - foreach (var c in columns) + foreach (XLColumn c in columns) { if (!toDelete.ContainsKey(c.Worksheet)) toDelete.Add(c.Worksheet, new List()); @@ -117,17 +81,12 @@ toDelete[c.Worksheet].Add(c.ColumnNumber()); } - foreach (var kp in toDelete) - { - foreach(var c in kp.Value.OrderByDescending(c=>c)) + foreach (KeyValuePair> kp in toDelete) + { + foreach (int c in kp.Value.OrderByDescending(c => c)) kp.Key.Column(c).Delete(); } - } - } - - public void Add(XLColumn column) - { - columns.Add(column); + } } public IXLColumns AdjustToContents() @@ -135,11 +94,13 @@ columns.ForEach(c => c.AdjustToContents()); return this; } + public IXLColumns AdjustToContents(Int32 startRow) { columns.ForEach(c => c.AdjustToContents(startRow)); return this; } + public IXLColumns AdjustToContents(Int32 startRow, Int32 endRow) { columns.ForEach(c => c.AdjustToContents(startRow, endRow)); @@ -151,11 +112,13 @@ columns.ForEach(c => c.AdjustToContents(minWidth, maxWidth)); return this; } + public IXLColumns AdjustToContents(Int32 startRow, Double minWidth, Double maxWidth) { columns.ForEach(c => c.AdjustToContents(startRow, minWidth, maxWidth)); return this; } + public IXLColumns AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth) { columns.ForEach(c => c.AdjustToContents(startRow, endRow, minWidth, maxWidth)); @@ -166,42 +129,47 @@ { columns.ForEach(c => c.Hide()); } + public void Unhide() { columns.ForEach(c => c.Unhide()); } + public void Group() { Group(false); } + public void Group(Int32 outlineLevel) { Group(outlineLevel, false); } + public void Ungroup() { Ungroup(false); } + public void Group(Boolean collapse) { columns.ForEach(c => c.Group(collapse)); } + public void Group(Int32 outlineLevel, Boolean collapse) { columns.ForEach(c => c.Group(outlineLevel, collapse)); } + public void Ungroup(Boolean ungroupFromAll) { columns.ForEach(c => c.Ungroup(ungroupFromAll)); } + public void Collapse() { columns.ForEach(c => c.Collapse()); } - public void CollapseOnly() - { - columns.ForEach(c => c.Collapsed = true ); - } + public void Expand() { columns.ForEach(c => c.Expand()); @@ -209,32 +177,74 @@ public IXLCells Cells() { - var cells = new XLCells( false, false); - foreach (var container in columns) - { + var cells = new XLCells(false, false); + foreach (XLColumn container in columns) cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + return cells; } public IXLCells CellsUsed() { - var cells = new XLCells( true, false); - foreach (var container in columns) - { + var cells = new XLCells(true, false); + foreach (XLColumn container in columns) cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + return cells; } public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells( true, includeStyles); - foreach (var container in columns) - { + var cells = new XLCells(true, includeStyles); + foreach (XLColumn container in columns) cells.Add(container.RangeAddress); + return cells; + } + + /// + /// Adds a vertical page break after this column. + /// + public IXLColumns AddVerticalPageBreaks() + { + foreach (XLColumn col in columns) + col.Worksheet.PageSetup.AddVerticalPageBreak(col.ColumnNumber()); + return this; + } + + public IXLColumns SetDataType(XLCellValues dataType) + { + columns.ForEach(c => c.DataType = dataType); + return this; + } + + #endregion + + #region IXLStylized Members + + public IEnumerable Styles + { + get + { + UpdatingStyle = true; + yield return style; + if (worksheet != null) + yield return worksheet.Style; + else + { + foreach (XLColumn col in columns) + { + foreach (IXLStyle s in col.Styles) + yield return s; + } + } + UpdatingStyle = false; } - return (IXLCells)cells; + } + + public Boolean UpdatingStyle { get; set; } + + public IXLStyle InnerStyle + { + get { return style; } + set { style = new XLStyle(this, value); } } public IXLRanges RangesUsed @@ -247,20 +257,16 @@ } } - /// - /// Adds a vertical page break after this column. - /// - public IXLColumns AddVerticalPageBreaks() + #endregion + + public void Add(XLColumn column) { - foreach (var col in columns) - col.Worksheet.PageSetup.AddVerticalPageBreak(col.ColumnNumber()); - return this; + columns.Add(column); } - public IXLColumns SetDataType(XLCellValues dataType) + public void CollapseOnly() { - columns.ForEach(c => c.DataType = dataType); - return this; + columns.ForEach(c => c.Collapsed = true); } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs new file mode 100644 index 0000000..a66ae8f --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetPoint.cs @@ -0,0 +1,29 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal struct XLSheetPoint:IEquatable + { + public XLSheetPoint(Int32 row, Int32 column) + { + Row = row; + Column = column; + } + + public Int32 Row; + public Int32 Column; + + public bool Equals(XLSheetPoint other) + { + return Row == other.Row && Column == other.Column; + } + + public override int GetHashCode() + { + return (Row * -1) ^ Column; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs new file mode 100644 index 0000000..23ccd25 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLSheetRange.cs @@ -0,0 +1,29 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal struct XLSheetRange:IEquatable + { + public XLSheetRange(XLSheetPoint firstPoint, XLSheetPoint lastPoint) + { + FirstPoint = firstPoint; + LastPoint = lastPoint; + } + + public XLSheetPoint FirstPoint; + public XLSheetPoint LastPoint; + + public bool Equals(XLSheetRange other) + { + return FirstPoint.Equals(other.FirstPoint) && LastPoint.Equals(other.LastPoint); + } + + public override int GetHashCode() + { + return FirstPoint.GetHashCode() ^ LastPoint.GetHashCode(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index cc5b137..259fe75 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -44,6 +44,7 @@ /// Gets the first row of the worksheet that contains a cell with a value. /// IXLRow FirstRowUsed(); + IXLRow FirstRowUsed(Boolean includeFormats); /// /// Gets the last row of the worksheet. /// @@ -52,6 +53,7 @@ /// Gets the last row of the worksheet that contains a cell with a value. /// IXLRow LastRowUsed(); + IXLRow LastRowUsed(Boolean includeFormats); /// /// Gets the first column of the worksheet. /// @@ -60,6 +62,7 @@ /// Gets the first column of the worksheet that contains a cell with a value. /// IXLColumn FirstColumnUsed(); + IXLColumn FirstColumnUsed(Boolean includeFormats); /// /// Gets the last column of the worksheet. /// @@ -68,6 +71,7 @@ /// Gets the last column of the worksheet that contains a cell with a value. /// IXLColumn LastColumnUsed(); + IXLColumn LastColumnUsed(Boolean includeFormats); /// /// Gets a collection of all columns in this worksheet. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs deleted file mode 100644 index 82a3e60..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs +++ /dev/null @@ -1,12 +0,0 @@ - -namespace ClosedXML.Excel -{ - internal interface IXLWorksheetInternals - { - XLCellCollection CellsCollection { get; } - XLColumnsCollection ColumnsCollection { get; } - XLRowsCollection RowsCollection { get; } - XLRanges MergedRanges { get; } - XLWorkbook Workbook { get; } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 89f8052..bbad09d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -51,6 +51,7 @@ /// Gets the first column of the range that contains a cell with a value. /// IXLRangeColumn FirstColumnUsed(); + IXLRangeColumn FirstColumnUsed(Boolean includeFormats); /// /// Gets the last column of the range. /// @@ -59,6 +60,7 @@ /// Gets the last column of the range that contains a cell with a value. /// IXLRangeColumn LastColumnUsed(); + IXLRangeColumn LastColumnUsed(Boolean includeFormats); /// /// Gets a collection of all columns in this range. /// @@ -90,6 +92,7 @@ /// Gets the first row of the range that contains a cell with a value. /// IXLRangeRow FirstRowUsed(); + IXLRangeRow FirstRowUsed(Boolean includeFormats); /// /// Gets the last row of the range. /// @@ -98,6 +101,7 @@ /// Gets the last row of the range that contains a cell with a value. /// IXLRangeRow LastRowUsed(); + IXLRangeRow LastRowUsed(Boolean includeFormats); /// /// Gets the specified row of the range. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index ceecd48..6578432 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -7,274 +7,146 @@ internal class XLRange : XLRangeBase, IXLRange { #region Fields - private IXLSortElements m_sortRows; - private IXLSortElements m_sortColumns; + + private IXLSortElements _mSortColumns; + private IXLSortElements _mSortRows; + #endregion + #region Constructor + public XLRange(XLRangeParameters xlRangeParameters) - : base(xlRangeParameters.RangeAddress) + : base(xlRangeParameters.RangeAddress) { RangeParameters = xlRangeParameters; if (!xlRangeParameters.IgnoreEvents) { - (Worksheet).RangeShiftedRows += Worksheet_RangeShiftedRows; - (Worksheet).RangeShiftedColumns += Worksheet_RangeShiftedColumns; + (Worksheet).RangeShiftedRows += WorksheetRangeShiftedRows; + (Worksheet).RangeShiftedColumns += WorksheetRangeShiftedColumns; xlRangeParameters.IgnoreEvents = true; } DefaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } + #endregion + public XLRangeParameters RangeParameters { get; private set; } - private void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) - { - ShiftColumns(RangeAddress, range, columnsShifted); - } - - private void Worksheet_RangeShiftedRows(XLRange range, int rowsShifted) - { - ShiftRows(RangeAddress, range, rowsShifted); - } #region IXLRange Members - public IXLRangeColumn FirstColumn() + + IXLRangeColumn IXLRange.FirstColumn() { - return Column(1); - } - public IXLRangeColumn LastColumn() - { - return Column(ColumnCount()); - } - public IXLRangeColumn FirstColumnUsed() - { - var firstColumn = RangeAddress.FirstAddress.ColumnNumber; - var columnCount = ColumnCount(); - Int32 minColumnUsed = Int32.MaxValue; - Int32 minColumnInCells = Int32.MaxValue; - if ((Worksheet).Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) - { - minColumnInCells = (Worksheet).Internals.CellsCollection - .Where(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount).Select(c => c.Key.ColumnNumber).Min(); - } - - Int32 minCoInColumns = Int32.MaxValue; - if ((Worksheet).Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) - { - minCoInColumns = (Worksheet).Internals.ColumnsCollection - .Where(c => c.Key >= firstColumn && c.Key <= columnCount).Select(c => c.Key).Min(); - } - - minColumnUsed = minColumnInCells < minCoInColumns ? minColumnInCells : minCoInColumns; - - if (minColumnUsed == Int32.MaxValue) - { - return null; - } - else - { - return Column(minColumnUsed); - } - } - public IXLRangeColumn LastColumnUsed() - { - var firstColumn = RangeAddress.FirstAddress.ColumnNumber; - var columnCount = ColumnCount(); - Int32 maxColumnUsed = 0; - Int32 maxColumnInCells = 0; - if ((Worksheet).Internals.CellsCollection.Any(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount)) - { - maxColumnInCells = (Worksheet).Internals.CellsCollection - .Where(c => c.Key.ColumnNumber >= firstColumn && c.Key.ColumnNumber <= columnCount).Select(c => c.Key.ColumnNumber).Max(); - } - - Int32 maxCoInColumns = 0; - if ((Worksheet).Internals.ColumnsCollection.Any(c => c.Key >= firstColumn && c.Key <= columnCount)) - { - maxCoInColumns = (Worksheet).Internals.ColumnsCollection - .Where(c => c.Key >= firstColumn && c.Key <= columnCount).Select(c => c.Key).Max(); - } - - maxColumnUsed = maxColumnInCells > maxCoInColumns ? maxColumnInCells : maxCoInColumns; - - if (maxColumnUsed == 0) - { - return null; - } - else - { - return Column(maxColumnUsed); - } + return FirstColumn(); } - public IXLRangeRow FirstRow() + IXLRangeColumn IXLRange.LastColumn() { - return Row(1); - } - public IXLRangeRow LastRow() - { - return Row(RowCount()); - } - public IXLRangeRow FirstRowUsed() - { - var firstRow = RangeAddress.FirstAddress.RowNumber; - var rowCount = RowCount(); - Int32 minRowUsed = Int32.MaxValue; - Int32 minRowInCells = Int32.MaxValue; - if ((Worksheet).Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) - { - minRowInCells = (Worksheet).Internals.CellsCollection - .Where(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount).Select(c => c.Key.RowNumber).Min(); - } - - Int32 minRoInRows = Int32.MaxValue; - if ((Worksheet).Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) - { - minRoInRows = (Worksheet).Internals.RowsCollection - .Where(r => r.Key >= firstRow && r.Key <= rowCount).Select(r => r.Key).Min(); - } - - minRowUsed = minRowInCells < minRoInRows ? minRowInCells : minRoInRows; - - if (minRowUsed == Int32.MaxValue) - { - return null; - } - return Row(minRowUsed); - } - public IXLRangeRow LastRowUsed() - { - var firstRow = RangeAddress.FirstAddress.RowNumber; - var rowCount = RowCount(); - Int32 maxRowUsed = 0; - Int32 maxRowInCells = 0; - if ((Worksheet).Internals.CellsCollection.Any(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount)) - { - maxRowInCells = (Worksheet).Internals.CellsCollection - .Where(c => c.Key.RowNumber >= firstRow && c.Key.RowNumber <= rowCount).Select(c => c.Key.RowNumber).Max(); - } - - Int32 maxRoInRows = 0; - if ((Worksheet).Internals.RowsCollection.Any(r => r.Key >= firstRow && r.Key <= rowCount)) - { - maxRoInRows = (Worksheet).Internals.RowsCollection - .Where(r => r.Key >= firstRow && r.Key <= rowCount).Select(r => r.Key).Max(); - } - - maxRowUsed = maxRowInCells > maxRoInRows ? maxRowInCells : maxRoInRows; - - if (maxRowUsed == 0) - { - return null; - } - else - { - return Row(maxRowUsed); - } + return LastColumn(); } - public IXLRangeRow Row(Int32 row) + IXLRangeColumn IXLRange.FirstColumnUsed() { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.FirstAddress.ColumnNumber, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.LastAddress.ColumnNumber, - false, - false); - return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + return FirstColumnUsed(); } - public XLRangeRow RowQuick(Int32 row) + + IXLRangeColumn IXLRange.FirstColumnUsed(bool includeFormats) { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.FirstAddress.ColumnNumber, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.LastAddress.ColumnNumber, - false, - false); - return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + return FirstColumnUsed(includeFormats); } - public IXLRangeColumn Column(Int32 column) + + IXLRangeColumn IXLRange.LastColumnUsed() { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + return LastColumnUsed(); } - public IXLRangeColumn Column(String column) + + IXLRangeColumn IXLRange.LastColumnUsed(bool includeFormats) { - return Column(ExcelHelper.GetColumnNumberFromLetter(column)); + return LastColumnUsed(includeFormats); } - public XLRangeColumn ColumnQuick(Int32 column) + + IXLRangeRow IXLRange.FirstRow() { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + return FirstRow(); + } + + IXLRangeRow IXLRange.LastRow() + { + return LastRow(); + } + + IXLRangeRow IXLRange.LastRowUsed() + { + return LastRowUsed(); + } + + IXLRangeRow IXLRange.LastRowUsed(bool includeFormats) + { + return LastRowUsed(includeFormats); + } + + IXLRangeRow IXLRange.FirstRowUsed() + { + return FirstRowUsed(); + } + + IXLRangeRow IXLRange.FirstRowUsed(bool includeFormats) + { + return FirstRowUsed(includeFormats); + } + + IXLRangeRow IXLRange.Row(Int32 row) + { + return Row(row); + } + + IXLRangeColumn IXLRange.Column(Int32 column) + { + return Column(column); + } + + IXLRangeColumn IXLRange.Column(String column) + { + return Column(column); } public IXLRangeColumns Columns() { var retVal = new XLRangeColumns(); - foreach (var c in Enumerable.Range(1, ColumnCount())) - { + Int32 columnCount = ColumnCount(); + for (Int32 c = 1; c <= columnCount; c++ ) retVal.Add(Column(c)); - } return retVal; } + public virtual IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) { var retVal = new XLRangeColumns(); - for (var co = firstColumn; co <= lastColumn; co++) - { + for (int co = firstColumn; co <= lastColumn; co++) retVal.Add(Column(co)); - } return retVal; } + public IXLRangeColumns Columns(String firstColumn, String lastColumn) { - return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), ExcelHelper.GetColumnNumberFromLetter(lastColumn)); + return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), + ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } + public IXLRangeColumns Columns(String columns) { var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); - foreach (var pair in columnPairs) + foreach (string pair in columnPairs) { - var tPair = pair.Trim(); + string tPair = pair.Trim(); String firstColumn; String lastColumn; if (tPair.Contains(':') || tPair.Contains('-')) { if (tPair.Contains('-')) - { tPair = tPair.Replace('-', ':'); - } var columnRange = tPair.Split(':'); firstColumn = columnRange[0]; @@ -289,17 +161,13 @@ Int32 tmp; if (Int32.TryParse(firstColumn, out tmp)) { - foreach (var col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))) - { + foreach (IXLRangeColumn col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))) retVal.Add(col); - } } else { - foreach (var col in Columns(firstColumn, lastColumn)) - { + foreach (IXLRangeColumn col in Columns(firstColumn, lastColumn)) retVal.Add(col); - } } } return retVal; @@ -309,14 +177,17 @@ { return Cell(row, column); } + IXLCell IXLRange.Cell(string cellAddressInRange) { return Cell(cellAddressInRange); } + IXLCell IXLRange.Cell(int row, string column) { return Cell(row, column); } + IXLCell IXLRange.Cell(IXLAddress cellAddressInRange) { return Cell(cellAddressInRange); @@ -326,22 +197,27 @@ { return Range(rangeAddress); } + IXLRange IXLRange.Range(string rangeAddress) { return Range(rangeAddress); } + IXLRange IXLRange.Range(IXLCell firstCell, IXLCell lastCell) { return Range(firstCell, lastCell); } + IXLRange IXLRange.Range(string firstCellAddress, string lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLRange.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLRange.Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) { return Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); @@ -350,37 +226,34 @@ public IXLRangeRows Rows() { var retVal = new XLRangeRows(); - foreach (var r in Enumerable.Range(1, RowCount())) - { + Int32 rowCount = RowCount(); + for (Int32 r = 1; r <= rowCount; r++ ) retVal.Add(Row(r)); - } return retVal; } + public IXLRangeRows Rows(Int32 firstRow, Int32 lastRow) { var retVal = new XLRangeRows(); - for (var ro = firstRow; ro <= lastRow; ro++) - { + for (int ro = firstRow; ro <= lastRow; ro++) retVal.Add(Row(ro)); - } return retVal; } + public IXLRangeRows Rows(String rows) { var retVal = new XLRangeRows(); var rowPairs = rows.Split(','); - foreach (var pair in rowPairs) + foreach (string pair in rowPairs) { - var tPair = pair.Trim(); + string tPair = pair.Trim(); String firstRow; String lastRow; if (tPair.Contains(':') || tPair.Contains('-')) { if (tPair.Contains('-')) - { tPair = tPair.Replace('-', ':'); - } var rowRange = tPair.Split(':'); firstRow = rowRange[0]; @@ -391,19 +264,17 @@ firstRow = tPair; lastRow = tPair; } - foreach (var row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - { + foreach (IXLRangeRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) retVal.Add(row); - } } return retVal; } public void Transpose(XLTransposeOptions transposeOption) { - var rowCount = RowCount(); - var columnCount = ColumnCount(); - var squareSide = rowCount > columnCount ? rowCount : columnCount; + int rowCount = RowCount(); + int columnCount = ColumnCount(); + int squareSide = rowCount > columnCount ? rowCount : columnCount; var firstCell = FirstCell(); @@ -418,23 +289,23 @@ if (rowCount > columnCount) { var rng = Worksheet.Range( - RangeAddress.LastAddress.RowNumber + 1, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber + (rowCount - columnCount), - RangeAddress.LastAddress.ColumnNumber); + RangeAddress.LastAddress.RowNumber + 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber + (rowCount - columnCount), + RangeAddress.LastAddress.ColumnNumber); rng.Delete(XLShiftDeletedCells.ShiftCellsUp); } else if (columnCount > rowCount) { var rng = Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber + 1, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber + (columnCount - rowCount)); + RangeAddress.FirstAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + 1, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + (columnCount - rowCount)); rng.Delete(XLShiftDeletedCells.ShiftCellsLeft); } - foreach (var c in Range(1, 1, columnCount, rowCount).Cells()) + foreach (IXLCell c in Range(1, 1, columnCount, rowCount).Cells()) { var border = new XLBorder(this, c.Style.Border); c.Style.Border.TopBorder = border.LeftBorder; @@ -448,100 +319,6 @@ } } - private void TransposeRange(int squareSide) - { - var cellsToInsert = new Dictionary(); - var cellsToDelete = new List(); - XLRange rngToTranspose = (XLRange) Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.RowNumber + squareSide - 1, - RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - - Int32 roCount = rngToTranspose.RowCount(); - Int32 coCount = rngToTranspose.ColumnCount(); - for (Int32 ro = 1; ro <= roCount; ro++) - { - for (Int32 co = 1; co <= coCount; co++) - { - var oldCell = rngToTranspose.Cell(ro, co); - var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); - var newCell = new XLCell(Worksheet, newKey, oldCell.Style); - newCell.CopyFrom(oldCell); - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldCell.Address); - } - } - //Int32 roInitial = rngToTranspose.RangeAddress.FirstAddress.RowNumber; - //Int32 coInitial = rngToTranspose.RangeAddress.FirstAddress.ColumnNumber; - //foreach (var c in rngToTranspose.Cells()) - //{ - // var newKey = new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); - // var newCell = new XLCell(newKey, c.Style, Worksheet); - // newCell.Value = c.Value; - // newCell.DataType = c.DataType; - // cellsToInsert.Add(newKey, newCell); - // cellsToDelete.Add(c.Address); - //} - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); - } - - private void TransposeMerged(Int32 squareSide) - { - XLRange rngToTranspose = (XLRange)Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.RowNumber + squareSide - 1, - RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - - foreach (var merge in Worksheet.Internals.MergedRanges) - { - if (Contains(merge)) - { - merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; - } - } - } - - private void MoveOrClearForTranspose(XLTransposeOptions transposeOption, int rowCount, int columnCount) - { - if (transposeOption == XLTransposeOptions.MoveCells) - { - if (rowCount > columnCount) - { - InsertColumnsAfter(rowCount - columnCount, false); - } - else if (columnCount > rowCount) - { - InsertRowsBelow(columnCount - rowCount, false); - } - } - else - { - if (rowCount > columnCount) - { - var toMove = rowCount - columnCount; - var rngToClear = Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber + 1, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber + toMove); - rngToClear.Clear(); - } - else if (columnCount > rowCount) - { - var toMove = columnCount - rowCount; - var rngToClear = Worksheet.Range( - RangeAddress.LastAddress.RowNumber + 1, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber + toMove, - RangeAddress.LastAddress.ColumnNumber); - rngToClear.Clear(); - } - } - } - public IXLTable AsTable() { return new XLTable(this, false); @@ -561,86 +338,67 @@ { return new XLTable(this, name, true); } - #endregion - public override bool Equals(object obj) - { - var other = (XLRange) obj; - return RangeAddress.Equals(other.RangeAddress) - && Worksheet.Equals(other.Worksheet); - } - - public override int GetHashCode() - { - return RangeAddress.GetHashCode() - ^ Worksheet.GetHashCode(); - } public IXLSortElements SortRows { - get { return m_sortRows ?? (m_sortRows = new XLSortElements()); } + get { return _mSortRows ?? (_mSortRows = new XLSortElements()); } } public IXLSortElements SortColumns { - get { return m_sortColumns ?? (m_sortColumns = new XLSortElements()); } + get { return _mSortColumns ?? (_mSortColumns = new XLSortElements()); } } public IXLRange Sort() { if (SortColumns.Count() == 0) - { return Sort(XLSortOrder.Ascending); - } SortRangeRows(); return this; } + public IXLRange Sort(Boolean matchCase) { if (SortColumns.Count() == 0) - { return Sort(XLSortOrder.Ascending, false); - } SortRangeRows(); return this; } + public IXLRange Sort(XLSortOrder sortOrder) { if (SortColumns.Count() == 0) { Int32 columnCount = ColumnCount(); for (Int32 co = 1; co <= columnCount; co++) - { SortColumns.Add(co, sortOrder); - } } else - { SortColumns.ForEach(sc => sc.SortOrder = sortOrder); - } SortRangeRows(); return this; } + public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) { if (SortColumns.Count() == 0) { Int32 columnCount = ColumnCount(); for (Int32 co = 1; co <= columnCount; co++) - { SortColumns.Add(co, sortOrder, true, matchCase); - } } else { SortColumns.ForEach(sc => - { - sc.SortOrder = sortOrder; - sc.MatchCase = matchCase; - }); + { + sc.SortOrder = sortOrder; + sc.MatchCase = matchCase; + }); } SortRangeRows(); return this; } + public IXLRange Sort(String columnsToSortBy) { SortColumns.Clear(); @@ -663,23 +421,15 @@ Int32 co; if (!Int32.TryParse(coString, out co)) - { co = ExcelHelper.GetColumnNumberFromLetter(coString); - } - if (order.ToUpper().Equals("ASC")) - { - SortColumns.Add(co, XLSortOrder.Ascending); - } - else - { - SortColumns.Add(co, XLSortOrder.Descending); - } + SortColumns.Add(co, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending); } SortRangeRows(); return this; } + public IXLRange Sort(String columnsToSortBy, Boolean matchCase) { SortColumns.Clear(); @@ -702,18 +452,10 @@ Int32 co; if (!Int32.TryParse(coString, out co)) - { co = ExcelHelper.GetColumnNumberFromLetter(coString); - } - if (order.ToUpper().Equals("ASC")) - { - SortColumns.Add(co, XLSortOrder.Ascending, true, matchCase); - } - else - { - SortColumns.Add(co, XLSortOrder.Descending, true, matchCase); - } + SortColumns.Add(co, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending, true, + matchCase); } SortRangeRows(); @@ -723,81 +465,65 @@ public IXLRange Sort(XLSortOrientation sortOrientation) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(); - } if (SortRows.Count() == 0) - { return Sort(sortOrientation, XLSortOrder.Ascending); - } SortRangeColumns(); return this; } + public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(matchCase); - } if (SortRows.Count() == 0) - { return Sort(sortOrientation, XLSortOrder.Ascending, matchCase); - } SortRangeColumns(); return this; } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(sortOrder); - } if (SortRows.Count() == 0) { Int32 rowCount = RowCount(); for (Int32 co = 1; co <= rowCount; co++) - { SortRows.Add(co, sortOrder); - } } else - { SortRows.ForEach(sc => sc.SortOrder = sortOrder); - } SortRangeColumns(); return this; } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(sortOrder, matchCase); - } if (SortRows.Count() == 0) { Int32 rowCount = RowCount(); for (Int32 co = 1; co <= rowCount; co++) - { SortRows.Add(co, sortOrder, matchCase); - } } else { SortRows.ForEach(sc => - { - sc.SortOrder = sortOrder; - sc.MatchCase = matchCase; - }); + { + sc.SortOrder = sortOrder; + sc.MatchCase = matchCase; + }); } SortRangeColumns(); return this; } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(elementsToSortBy); - } SortRows.Clear(); foreach (String roPair in elementsToSortBy.Split(',')) { @@ -818,25 +544,17 @@ Int32 ro = Int32.Parse(roString); - if (order.ToUpper().Equals("ASC")) - { - SortRows.Add(ro, XLSortOrder.Ascending); - } - else - { - SortRows.Add(ro, XLSortOrder.Descending); - } + SortRows.Add(ro, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending); } SortRangeColumns(); return this; } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) { if (sortOrientation == XLSortOrientation.TopToBottom) - { return Sort(elementsToSortBy, matchCase); - } SortRows.Clear(); foreach (String roPair in elementsToSortBy.Split(',')) { @@ -857,199 +575,41 @@ Int32 ro = Int32.Parse(roString); - if (order.ToUpper().Equals("ASC")) - { - SortRows.Add(ro, XLSortOrder.Ascending, true, matchCase); - } - else - { - SortRows.Add(ro, XLSortOrder.Descending, true, matchCase); - } + SortRows.Add(ro, order.ToUpper().Equals("ASC") ? XLSortOrder.Ascending : XLSortOrder.Descending, true, + matchCase); } SortRangeColumns(); return this; } - #region Sort Rows - private void SortRangeRows() - { - SortingRangeRows(1, RowCount()); - } - private void SwapRows(Int32 row1, Int32 row2) - { - Int32 cellCount = ColumnCount(); - for (Int32 co = 1; co <= cellCount; co++) - { - var cell1 = (XLCell) RowQuick(row1).Cell(co); - var cell1Address = cell1.Address; - var cell2 = (XLCell) RowQuick(row2).Cell(co); - - cell1.Address = cell2.Address; - cell2.Address = cell1Address; - - (Worksheet).Internals.CellsCollection[cell1.Address] = cell1; - (Worksheet).Internals.CellsCollection[cell2.Address] = cell2; - } - } - private int SortRangeRows(int begPoint, int endPoint) - { - int pivot = begPoint; - int m = begPoint + 1; - int n = endPoint; - while ((m < endPoint) && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) - { - m++; - } - - while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) - { - n--; - } - while (m < n) - { - SwapRows(m, n); - - while (m < endPoint && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) - { - m++; - } - - while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) - { - n--; - } - } - if (pivot != n) - { - SwapRows(n, pivot); - } - return n; - } - private void SortingRangeRows(int beg, int end) - { - if (end == beg) - { - return; - } - int pivot = SortRangeRows(beg, end); - if (pivot > beg) - { - SortingRangeRows(beg, pivot - 1); - } - if (pivot < end) - { - SortingRangeRows(pivot + 1, end); - } - } - #endregion - #region Sort Columns - private void SortRangeColumns() - { - SortingRangeColumns(1, ColumnCount()); - } - private void SwapColumns(Int32 column1, Int32 column2) - { - Int32 cellCount = ColumnCount(); - - for (Int32 co = 1; co <= cellCount; co++) - { - var cell1 = (XLCell) ColumnQuick(column1).Cell(co); - var cell1Address = cell1.Address; - var cell2 = (XLCell) ColumnQuick(column2).Cell(co); - - cell1.Address = cell2.Address; - cell2.Address = cell1Address; - - (Worksheet).Internals.CellsCollection[cell1.Address] = cell1; - (Worksheet).Internals.CellsCollection[cell2.Address] = cell2; - } - } - private int SortRangeColumns(int begPoint, int endPoint) - { - int pivot = begPoint; - int m = begPoint + 1; - int n = endPoint; - while ((m < endPoint) && ColumnQuick(pivot).CompareTo((ColumnQuick(m)), SortRows) >= 0) - { - m++; - } - - while ((n > begPoint) && ((ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0)) - { - n--; - } - while (m < n) - { - SwapColumns(m, n); - - while ((m < endPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(m)), SortRows) >= 0) - { - m++; - } - - while ((n > begPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0) - { - n--; - } - } - if (pivot != n) - { - SwapColumns(n, pivot); - } - return n; - } - private void SortingRangeColumns(int beg, int end) - { - if (end == beg) - { - return; - } - int pivot = SortRangeColumns(beg, end); - if (pivot > beg) - { - SortingRangeColumns(beg, pivot - 1); - } - if (pivot < end) - { - SortingRangeColumns(pivot + 1, end); - } - } - #endregion public new IXLRange CopyTo(IXLCell target) { base.CopyTo(target); Int32 lastRowNumber = target.Address.RowNumber + RowCount() - 1; if (lastRowNumber > ExcelHelper.MaxRowNumber) - { lastRowNumber = ExcelHelper.MaxRowNumber; - } Int32 lastColumnNumber = target.Address.ColumnNumber + ColumnCount() - 1; if (lastColumnNumber > ExcelHelper.MaxColumnNumber) - { lastColumnNumber = ExcelHelper.MaxColumnNumber; - } return target.Worksheet.Range(target.Address.RowNumber, target.Address.ColumnNumber, lastRowNumber, lastColumnNumber); } + public new IXLRange CopyTo(IXLRangeBase target) { base.CopyTo(target); Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + RowCount() - 1; if (lastRowNumber > ExcelHelper.MaxRowNumber) - { lastRowNumber = ExcelHelper.MaxRowNumber; - } Int32 lastColumnNumber = target.RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1; if (lastColumnNumber > ExcelHelper.MaxColumnNumber) - { lastColumnNumber = ExcelHelper.MaxColumnNumber; - } return target.Worksheet.Range(target.RangeAddress.FirstAddress.RowNumber, target.RangeAddress.FirstAddress.ColumnNumber, @@ -1062,5 +622,378 @@ DataType = dataType; return this; } + + #endregion + + #region Sort Rows + + private void SortRangeRows() + { + SortingRangeRows(1, RowCount()); + } + + private void SwapRows(Int32 row1, Int32 row2) + { + int row1InWs = RangeAddress.FirstAddress.RowNumber + row1 - 1; + int row2InWs = RangeAddress.FirstAddress.RowNumber + row2 - 1; + + Int32 firstColumn = RangeAddress.FirstAddress.ColumnNumber; + Int32 lastColumn = RangeAddress.LastAddress.ColumnNumber; + + var range1Sp1 = new XLSheetPoint(row1InWs, firstColumn); + var range1Sp2 = new XLSheetPoint(row1InWs, lastColumn); + var range2Sp1 = new XLSheetPoint(row2InWs, firstColumn); + var range2Sp2 = new XLSheetPoint(row2InWs, lastColumn); + + Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), + new XLSheetRange(range2Sp1, range2Sp2)); + } + + private int SortRangeRows(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) + m++; + + while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) + n--; + while (m < n) + { + SwapRows(m, n); + + while (m < endPoint && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) + m++; + + while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) + n--; + } + if (pivot != n) + SwapRows(n, pivot); + return n; + } + + private void SortingRangeRows(int beg, int end) + { + if (end == beg) + return; + int pivot = SortRangeRows(beg, end); + if (pivot > beg) + SortingRangeRows(beg, pivot - 1); + if (pivot < end) + SortingRangeRows(pivot + 1, end); + } + + #endregion + + #region Sort Columns + + private void SortRangeColumns() + { + SortingRangeColumns(1, ColumnCount()); + } + + private void SwapColumns(Int32 column1, Int32 column2) + { + int col1InWs = RangeAddress.FirstAddress.ColumnNumber + column1 - 1; + int col2InWs = RangeAddress.FirstAddress.ColumnNumber + column2 - 1; + + Int32 firstRow = RangeAddress.FirstAddress.RowNumber; + Int32 lastRow = RangeAddress.LastAddress.RowNumber; + + var range1Sp1 = new XLSheetPoint(firstRow, col1InWs); + var range1Sp2 = new XLSheetPoint(lastRow, col1InWs); + var range2Sp1 = new XLSheetPoint(firstRow, col2InWs); + var range2Sp2 = new XLSheetPoint(lastRow, col2InWs); + + Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), + new XLSheetRange(range2Sp1, range2Sp2)); + } + + private int SortRangeColumns(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && ColumnQuick(pivot).CompareTo((ColumnQuick(m)), SortRows) >= 0) + m++; + + while ((n > begPoint) && ((ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0)) + n--; + while (m < n) + { + SwapColumns(m, n); + + while ((m < endPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(m)), SortRows) >= 0) + m++; + + while ((n > begPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0) + n--; + } + if (pivot != n) + SwapColumns(n, pivot); + return n; + } + + private void SortingRangeColumns(int beg, int end) + { + if (end == beg) + return; + int pivot = SortRangeColumns(beg, end); + if (pivot > beg) + SortingRangeColumns(beg, pivot - 1); + if (pivot < end) + SortingRangeColumns(pivot + 1, end); + } + + #endregion + + private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + { + ShiftColumns(RangeAddress, range, columnsShifted); + } + + private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) + { + ShiftRows(RangeAddress, range, rowsShifted); + } + + public XLRangeColumn FirstColumn() + { + return Column(1); + } + + public IXLRangeColumn LastColumn() + { + return Column(ColumnCount()); + } + + public XLRangeColumn FirstColumnUsed() + { + return FirstColumnUsed(false); + } + + public XLRangeColumn FirstColumnUsed(bool includeFormats) + { + var firstCellUsed = FirstCellUsed(includeFormats); + if (firstCellUsed == null) + return null; + + return Column(firstCellUsed.Address.ColumnNumber); + } + + public XLRangeColumn LastColumnUsed() + { + return LastColumnUsed(false); + } + + public XLRangeColumn LastColumnUsed(bool includeFormats) + { + var lastCellUsed = LastCellUsed(includeFormats); + if (lastCellUsed == null) + return null; + + return Column(lastCellUsed.Address.ColumnNumber); + } + + public XLRangeRow FirstRow() + { + return Row(1); + } + + public IXLRangeRow LastRow() + { + return Row(RowCount()); + } + + public XLRangeRow LastRowUsed() + { + return LastRowUsed(false); + } + + public XLRangeRow LastRowUsed(bool includeFormats) + { + var lastCellUsed = LastCellUsed(includeFormats); + if (lastCellUsed == null) + return null; + + return Row(lastCellUsed.Address.RowNumber); + } + + public XLRangeRow FirstRowUsed() + { + return FirstRowUsed(false); + } + + public XLRangeRow FirstRowUsed(bool includeFormats) + { + var firstCellUsed = FirstCellUsed(includeFormats); + if (firstCellUsed == null) + return null; + + return Row(firstCellUsed.Address.RowNumber); + } + + public XLRangeRow Row(Int32 row) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.FirstAddress.ColumnNumber, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.LastAddress.ColumnNumber, + false, + false); + return new XLRangeRow( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + } + + public XLRangeRow RowQuick(Int32 row) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.FirstAddress.ColumnNumber, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.LastAddress.ColumnNumber, + false, + false); + return new XLRangeRow( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + } + + public XLRangeColumn Column(Int32 column) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + return new XLRangeColumn( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); + } + + public XLRangeColumn Column(String column) + { + return Column(ExcelHelper.GetColumnNumberFromLetter(column)); + } + + public XLRangeColumn ColumnQuick(Int32 column) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + return new XLRangeColumn( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + } + + private void TransposeRange(int squareSide) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var rngToTranspose = Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.RowNumber + squareSide - 1, + RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); + + Int32 roCount = rngToTranspose.RowCount(); + Int32 coCount = rngToTranspose.ColumnCount(); + for (Int32 ro = 1; ro <= roCount; ro++) + { + for (Int32 co = 1; co <= coCount; co++) + { + var oldCell = rngToTranspose.Cell(ro, co); + var newKey = rngToTranspose.Cell(co, ro).Address; + // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); + var newCell = new XLCell(Worksheet, newKey, oldCell.Style); + newCell.CopyFrom(oldCell); + cellsToInsert.Add(new XLSheetPoint(newKey.RowNumber, newKey.ColumnNumber), newCell); + cellsToDelete.Add(new XLSheetPoint(oldCell.Address.RowNumber, oldCell.Address.ColumnNumber)); + } + } + + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + } + + private void TransposeMerged(Int32 squareSide) + { + var rngToTranspose = Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.RowNumber + squareSide - 1, + RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); + + foreach (IXLRange merge in Worksheet.Internals.MergedRanges) + { + if (Contains(merge)) + merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; + } + } + + private void MoveOrClearForTranspose(XLTransposeOptions transposeOption, int rowCount, int columnCount) + { + if (transposeOption == XLTransposeOptions.MoveCells) + { + if (rowCount > columnCount) + InsertColumnsAfter(rowCount - columnCount, false); + else if (columnCount > rowCount) + InsertRowsBelow(columnCount - rowCount, false); + } + else + { + if (rowCount > columnCount) + { + int toMove = rowCount - columnCount; + var rngToClear = Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + 1, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + toMove); + rngToClear.Clear(); + } + else if (columnCount > rowCount) + { + int toMove = columnCount - rowCount; + var rngToClear = Worksheet.Range( + RangeAddress.LastAddress.RowNumber + 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber + toMove, + RangeAddress.LastAddress.ColumnNumber); + rngToClear.Clear(); + } + } + } + + public override bool Equals(object obj) + { + var other = (XLRange)obj; + return RangeAddress.Equals(other.RangeAddress) + && Worksheet.Equals(other.Worksheet); + } + + public override int GetHashCode() + { + return RangeAddress.GetHashCode() + ^ Worksheet.GetHashCode(); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index e78ab6f..34321f0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -194,9 +194,14 @@ Worksheet.Internals.MergedRanges.Select(m => m.RangeAddress.ToString()).Any( mAddress => mAddress == tAddress); + var asRange = AsRange(); if (!foundOne) - Worksheet.Internals.MergedRanges.Add(AsRange()); - return AsRange(); + Worksheet.Internals.MergedRanges.Add(asRange); + + // Call every cell in the merge to make sure they exist + asRange.Cells().ForEach(c=> { }); + + return asRange; } public IXLRange Unmerge() @@ -213,15 +218,11 @@ public void Clear() { // Remove cells inside range - Worksheet.Internals.CellsCollection.RemoveAll(c => - c.Address.ColumnNumber >= - RangeAddress.FirstAddress.ColumnNumber - && - c.Address.ColumnNumber <= - RangeAddress.LastAddress.ColumnNumber - && - c.Address.RowNumber >= RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= RangeAddress.LastAddress.RowNumber + Worksheet.Internals.CellsCollection.RemoveAll( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber ); ClearMerged(); @@ -433,8 +434,12 @@ public XLCell Cell(XLAddress cellAddressInRange) { var absoluteAddress = cellAddressInRange + RangeAddress.FirstAddress - 1; - if (Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) - return Worksheet.Internals.CellsCollection[absoluteAddress]; + + var cell = Worksheet.Internals.CellsCollection.GetCell(absoluteAddress.RowNumber, + absoluteAddress.ColumnNumber); + if(cell!=null) + return cell; + var style = Style; if (Style != null && Style.Equals(Worksheet.Style)) { @@ -448,7 +453,7 @@ style = Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; } var newCell = new XLCell(Worksheet, absoluteAddress, style); - Worksheet.Internals.CellsCollection.Add(absoluteAddress, newCell); + Worksheet.Internals.CellsCollection.Add(absoluteAddress.RowNumber, absoluteAddress.ColumnNumber, newCell); return newCell; } @@ -655,7 +660,7 @@ { foreach ( XLCell cell in - ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) + ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) ) cell.ShiftFormulaColumns((XLRange)AsRange(), numberOfColumns); } @@ -669,48 +674,46 @@ if (!onlyUsedCells) { - int lastColumn = Worksheet.LastColumnUsed().ColumnNumber(); - - for (int co = lastColumn; co >= firstColumn; co--) + int lastColumn = Worksheet.Internals.CellsCollection.MaxColumnUsed; + if (lastColumn > 0) { - for (int ro = lastRow; ro >= firstRow; ro--) + for (int co = lastColumn; co >= firstColumn; co--) { - var oldKey = new XLAddress(Worksheet, ro, co, false, false); - int newColumn = co + numberOfColumns; - var newKey = new XLAddress(Worksheet, ro, newColumn, false, false); - IXLCell oldCell = Worksheet.Internals.CellsCollection.ContainsKey(oldKey) - ? Worksheet.Internals.CellsCollection[oldKey] - : Worksheet.Cell(oldKey); - var newCell = new XLCell(Worksheet, newKey, oldCell.Style); - newCell.CopyValues((XLCell)oldCell); - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); - if (oldKey.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(oldKey); + for (int ro = lastRow; ro >= firstRow; ro--) + { + var oldKey = new XLAddress(Worksheet, ro, co, false, false); + int newColumn = co + numberOfColumns; + var newKey = new XLAddress(Worksheet, ro, newColumn, false, false); + XLCell oldCell = Worksheet.Internals.CellsCollection.GetCell(ro, co); + if (oldCell == null) + oldCell = Worksheet.Cell(oldKey); + + var newCell = new XLCell(Worksheet, newKey, oldCell.Style); + newCell.CopyValues(oldCell); + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(oldKey); + if (oldKey.ColumnNumber < firstColumn + numberOfColumns) + cellsToBlank.Add(oldKey); + } } } } else { - foreach (KeyValuePair c in Worksheet.Internals.CellsCollection - .Where(c => - c.Key.ColumnNumber >= firstColumn - && c.Key.RowNumber >= firstRow - && c.Key.RowNumber <= lastRow - )) + foreach (var c in Worksheet.Internals.CellsCollection.GetCells(firstRow,firstColumn,lastRow,Worksheet.Internals.CellsCollection.MaxColumnUsed)) { - int newColumn = c.Key.ColumnNumber + numberOfColumns; - var newKey = new XLAddress(Worksheet, c.Key.RowNumber, newColumn, false, false); - var newCell = new XLCell(Worksheet, newKey, c.Value.Style); - newCell.CopyValues(c.Value); + int newColumn = c.Address.ColumnNumber + numberOfColumns; + var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false); + var newCell = new XLCell(Worksheet, newKey, c.Style); + newCell.CopyValues(c); cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(c.Key); - if (c.Key.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(c.Key); + cellsToDelete.Add(c.Address); + if (c.Address.ColumnNumber < firstColumn + numberOfColumns) + cellsToBlank.Add(c.Address); } } - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber,c.ColumnNumber)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); foreach (IXLAddress c in cellsToBlank) { var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber) @@ -805,7 +808,7 @@ { foreach ( XLCell cell in - ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) + ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) ) cell.ShiftFormulaRows((XLRange)AsRange(), numberOfRows); } @@ -819,50 +822,46 @@ if (!onlyUsedCells) { - int lastRow = Worksheet.LastRowUsed().RowNumber(); - - for (int ro = lastRow; ro >= firstRow; ro--) + int lastRow = Worksheet.Internals.CellsCollection.MaxRowUsed; + if (lastRow > 0) { - for (int co = lastColumn; co >= firstColumn; co--) + for (int ro = lastRow; ro >= firstRow; ro--) { - var oldKey = new XLAddress(Worksheet, ro, co, false, false); - int newRow = ro + numberOfRows; - var newKey = new XLAddress(Worksheet, newRow, co, false, false); - XLCell oldCell; - if (Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) - oldCell = Worksheet.Internals.CellsCollection[oldKey]; - else - oldCell = Worksheet.Cell(oldKey); - var newCell = new XLCell(Worksheet, newKey, oldCell.Style); - newCell.CopyFrom(oldCell); - cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); - if (oldKey.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(oldKey); + for (int co = lastColumn; co >= firstColumn; co--) + { + var oldKey = new XLAddress(Worksheet, ro, co, false, false); + int newRow = ro + numberOfRows; + var newKey = new XLAddress(Worksheet, newRow, co, false, false); + XLCell oldCell = Worksheet.Internals.CellsCollection.GetCell(ro, co); + if (oldCell == null) + oldCell = Worksheet.Cell(oldKey); + + var newCell = new XLCell(Worksheet, newKey, oldCell.Style); + newCell.CopyFrom(oldCell); + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(oldKey); + if (oldKey.RowNumber < firstRow + numberOfRows) + cellsToBlank.Add(oldKey); + } } } } else { - foreach (KeyValuePair c in Worksheet.Internals.CellsCollection - .Where(c => - c.Key.RowNumber >= firstRow - && c.Key.ColumnNumber >= firstColumn - && c.Key.ColumnNumber <= lastColumn - )) + foreach (var c in Worksheet.Internals.CellsCollection.GetCells(firstRow, firstColumn, Worksheet.Internals.CellsCollection.MaxRowUsed, lastColumn)) { - int newRow = c.Key.RowNumber + numberOfRows; - var newKey = new XLAddress(Worksheet, newRow, c.Key.ColumnNumber, false, false); - var newCell = new XLCell(Worksheet, newKey, c.Value.Style); - newCell.CopyFrom(c.Value); + int newRow = c.Address.RowNumber + numberOfRows; + var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false); + var newCell = new XLCell(Worksheet, newKey, c.Style); + newCell.CopyFrom(c); cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(c.Key); - if (c.Key.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(c.Key); + cellsToDelete.Add(c.Address); + if (c.Address.RowNumber < firstRow + numberOfRows) + cellsToBlank.Add(c.Address); } } - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); foreach (IXLAddress c in cellsToBlank) { IXLStyle styleToUse; @@ -909,30 +908,34 @@ { int numberOfRows = RowCount(); int numberOfColumns = ColumnCount(); - IXLRange shiftedRangeFormula; - if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - { - var lastCell = Worksheet.Cell(ExcelHelper.MaxRowNumber, RangeAddress.LastAddress.ColumnNumber); - shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); - if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && - StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - Worksheet.Internals.CellsCollection.Remove(lastCell.Address); - } - else - { - var lastCell = Worksheet.Cell(RangeAddress.LastAddress.RowNumber, ExcelHelper.MaxColumnNumber); - shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); - if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && - StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - Worksheet.Internals.CellsCollection.Remove(lastCell.Address); - } + IXLRange shiftedRangeFormula = Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber); + //if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + //{ + // var lastCell = Worksheet.Cell(ExcelHelper.MaxRowNumber, RangeAddress.LastAddress.ColumnNumber); + // shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); + // if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && + // StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + // Worksheet.Internals.CellsCollection.Remove(lastCell.Address.RowNumber, lastCell.Address.ColumnNumber); + //} + //else + //{ + // var lastCell = Worksheet.Cell(RangeAddress.LastAddress.RowNumber, ExcelHelper.MaxColumnNumber); + // shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); + // if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && + // StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + // Worksheet.Internals.CellsCollection.Remove(lastCell.Address.RowNumber, lastCell.Address.ColumnNumber); + //} foreach (IXLWorksheet ws in Worksheet.Internals.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; foreach ( XLCell cell in - (xlWorksheet).Internals.CellsCollection.Values.Where( + (xlWorksheet).Internals.CellsCollection.GetCells( c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) @@ -945,39 +948,40 @@ // Range to shift... var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var shiftLeftQuery = Worksheet.Internals.CellsCollection - .Where(c => - c.Key.RowNumber >= RangeAddress.FirstAddress.RowNumber - && c.Key.RowNumber <= RangeAddress.LastAddress.RowNumber - && c.Key.ColumnNumber >= RangeAddress.FirstAddress.ColumnNumber); + var shiftLeftQuery = Worksheet.Internals.CellsCollection.GetCells( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber, + Worksheet.Internals.CellsCollection.MaxColumnUsed); - var shiftUpQuery = Worksheet.Internals.CellsCollection - .Where(c => - c.Key.ColumnNumber >= RangeAddress.FirstAddress.ColumnNumber - && c.Key.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber - && c.Key.RowNumber >= RangeAddress.FirstAddress.RowNumber); + var shiftUpQuery = Worksheet.Internals.CellsCollection.GetCells( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + Worksheet.Internals.CellsCollection.MaxRowUsed, + RangeAddress.LastAddress.ColumnNumber); + int columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? ColumnCount() : 0; int rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? RowCount() : 0; var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; - foreach (KeyValuePair c in cellsQuery) + foreach (var c in cellsQuery) { - var newKey = new XLAddress(Worksheet, c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier, + var newKey = new XLAddress(Worksheet, c.Address.RowNumber - rowModifier, c.Address.ColumnNumber - columnModifier, false, false); - var newCell = new XLCell(Worksheet, newKey, c.Value.Style); - newCell.CopyValues(c.Value); + var newCell = new XLCell(Worksheet, newKey, c.Style); + newCell.CopyValues(c); //newCell.ShiftFormula(rowModifier * -1, columnModifier * -1); - cellsToDelete.Add(c.Key); + cellsToDelete.Add(c.Address); bool canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft - ? c.Key.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - : c.Key.RowNumber > RangeAddress.LastAddress.RowNumber; + ? c.Address.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + : c.Address.RowNumber > RangeAddress.LastAddress.RowNumber; if (canInsert) cellsToInsert.Add(newKey, newCell); } - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); var mergesToRemove = new List(); foreach (IXLRange merge in Worksheet.Internals.MergedRanges) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 92f8ac6..3dc6bc3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -22,11 +22,16 @@ #region IXLRangeColumn Members - public IXLCell Cell(int row) + public XLCell Cell(int row) { return Cell(row, 1); } + IXLCell IXLRangeColumn.Cell(int row) + { + return Cell(row); + } + public new IXLCells Cells(string cellsInColumn) { var retVal = new XLCells( false, false); @@ -215,11 +220,11 @@ { foreach (IXLSortElement e in rowsToSort) { - var thisCell = (XLCell)Cell(e.ElementNumber); - var otherCell = (XLCell)otherColumn.Cell(e.ElementNumber); + var thisCell = Cell(e.ElementNumber); + var otherCell = otherColumn.Cell(e.ElementNumber); int comparison; - bool thisCellIsBlank = StringExtensions.IsNullOrWhiteSpace(thisCell.InnerText); - bool otherCellIsBlank = StringExtensions.IsNullOrWhiteSpace(otherCell.InnerText); + bool thisCellIsBlank = !thisCell.IsUsed(); + bool otherCellIsBlank = !otherCell.IsUsed(); if (e.IgnoreBlanks && (thisCellIsBlank || otherCellIsBlank)) { if (thisCellIsBlank && otherCellIsBlank) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index c6a3c9b..f8cd33e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -4,77 +4,107 @@ namespace ClosedXML.Excel { + using System.Collections; + internal class XLRangeColumns : IXLRangeColumns, IXLStylized { + private readonly List _ranges = new List(); + private IXLStyle _style; + public XLRangeColumns() { - style = new XLStyle(this, XLWorkbook.DefaultStyle); + _style = new XLStyle(this, XLWorkbook.DefaultStyle); } - List ranges = new List(); + #region IXLRangeColumns Members public void Clear() { - ranges.ForEach(r => r.Clear()); + _ranges.ForEach(r => r.Clear()); } public void Delete() { - ranges.OrderByDescending(c => c.ColumnNumber()).ForEach(r => r.Delete()); - ranges.Clear(); + _ranges.OrderByDescending(c => c.ColumnNumber()).ForEach(r => r.Delete()); + _ranges.Clear(); } public void Add(IXLRangeColumn range) { - ranges.Add((XLRangeColumn)range); + _ranges.Add((XLRangeColumn)range); } public IEnumerator GetEnumerator() { var retList = new List(); - ranges.ForEach(c => retList.Add(c)); + _ranges.ForEach(retList.Add); return retList.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #region IXLStylized Members - - private IXLStyle style; public IXLStyle Style { - get - { - return style; - } + get { return _style; } set { - style = new XLStyle(this, value); - ranges.ForEach(r => r.Style = value); + _style = new XLStyle(this, value); + _ranges.ForEach(r => r.Style = value); } } + public IXLCells Cells() + { + var cells = new XLCells(false, false); + foreach (XLRangeColumn container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed() + { + var cells = new XLCells(true, false); + foreach (XLRangeColumn container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed(Boolean includeStyles) + { + var cells = new XLCells(true, includeStyles); + foreach (XLRangeColumn container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLRangeColumns SetDataType(XLCellValues dataType) + { + _ranges.ForEach(c => c.DataType = dataType); + return this; + } + + #endregion + + #region IXLStylized Members + public IEnumerable Styles { get { UpdatingStyle = true; - yield return style; - foreach (var rng in ranges) + yield return _style; + foreach (XLRangeColumn rng in _ranges) { yield return rng.Style; - foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber - && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber - && c.Address.ColumnNumber <= rng.RangeAddress.LastAddress.ColumnNumber - )) - { + foreach (XLCell r in rng.Worksheet.Internals.CellsCollection.GetCells( + rng.RangeAddress.FirstAddress.RowNumber, + rng.RangeAddress.FirstAddress.ColumnNumber, + rng.RangeAddress.LastAddress.RowNumber, + rng.RangeAddress.LastAddress.ColumnNumber)) yield return r.Style; - } } UpdatingStyle = false; } @@ -84,41 +114,8 @@ public IXLStyle InnerStyle { - get { return style; } - set { style = new XLStyle(this, value); } - } - - #endregion - - - public IXLCells Cells() - { - var cells = new XLCells( false, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed() - { - var cells = new XLCells( true, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed(Boolean includeStyles) - { - var cells = new XLCells( true, includeStyles); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + get { return _style; } + set { _style = new XLStyle(this, value); } } public IXLRanges RangesUsed @@ -131,11 +128,6 @@ } } - public IXLRangeColumns SetDataType(XLCellValues dataType) - { - ranges.ForEach(c => c.DataType = dataType); - return this; - } - + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 572b8d6..24af38b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -4,77 +4,107 @@ namespace ClosedXML.Excel { + using System.Collections; + internal class XLRangeRows : IXLRangeRows, IXLStylized { + private readonly List _ranges = new List(); + private IXLStyle _style; + public XLRangeRows() { - style = new XLStyle(this, XLWorkbook.DefaultStyle); + _style = new XLStyle(this, XLWorkbook.DefaultStyle); } - List ranges = new List(); + #region IXLRangeRows Members public void Clear() { - ranges.ForEach(r => r.Clear()); + _ranges.ForEach(r => r.Clear()); } public void Delete() { - ranges.OrderByDescending(r => r.RowNumber()).ForEach(r => r.Delete()); - ranges.Clear(); + _ranges.OrderByDescending(r => r.RowNumber()).ForEach(r => r.Delete()); + _ranges.Clear(); } public void Add(IXLRangeRow range) { - ranges.Add((XLRangeRow)range); + _ranges.Add((XLRangeRow)range); } public IEnumerator GetEnumerator() { var retList = new List(); - ranges.ForEach(c => retList.Add(c)); + _ranges.ForEach(retList.Add); return retList.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #region IXLStylized Members - - private IXLStyle style; public IXLStyle Style { - get - { - return style; - } + get { return _style; } set { - style = new XLStyle(this, value); - ranges.ForEach(r => r.Style = value); + _style = new XLStyle(this, value); + _ranges.ForEach(r => r.Style = value); } } + public IXLCells Cells() + { + var cells = new XLCells(false, false); + foreach (XLRangeRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed() + { + var cells = new XLCells(true, false); + foreach (XLRangeRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed(Boolean includeStyles) + { + var cells = new XLCells(true, includeStyles); + foreach (XLRangeRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLRangeRows SetDataType(XLCellValues dataType) + { + _ranges.ForEach(c => c.DataType = dataType); + return this; + } + + #endregion + + #region IXLStylized Members + public IEnumerable Styles { get { UpdatingStyle = true; - yield return style; - foreach (var rng in ranges) + yield return _style; + foreach (XLRangeRow rng in _ranges) { yield return rng.Style; - foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber - && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber - && c.Address.ColumnNumber <= rng.RangeAddress.LastAddress.ColumnNumber - )) - { + foreach (XLCell r in rng.Worksheet.Internals.CellsCollection.GetCells( + rng.RangeAddress.FirstAddress.RowNumber, + rng.RangeAddress.FirstAddress.ColumnNumber, + rng.RangeAddress.LastAddress.RowNumber, + rng.RangeAddress.LastAddress.ColumnNumber)) yield return r.Style; - } } UpdatingStyle = false; } @@ -84,40 +114,8 @@ public IXLStyle InnerStyle { - get { return style; } - set { style = new XLStyle(this, value); } - } - - #endregion - - public IXLCells Cells() - { - var cells = new XLCells( false, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed() - { - var cells = new XLCells( true, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed(Boolean includeStyles) - { - var cells = new XLCells( true, includeStyles); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + get { return _style; } + set { _style = new XLStyle(this, value); } } public IXLRanges RangesUsed @@ -130,10 +128,6 @@ } } - public IXLRangeRows SetDataType(XLCellValues dataType) - { - ranges.ForEach(c => c.DataType = dataType); - return this; - } + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index a2ec270..fdbdadc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -4,25 +4,32 @@ namespace ClosedXML.Excel { + using System.Collections; + internal class XLRanges : IXLRanges, IXLStylized { + private readonly List _ranges = new List(); + private Int32 _count; + private IXLStyle _style; + public XLRanges() { - this.style = new XLStyle(this, XLWorkbook.DefaultStyle); + _style = new XLStyle(this, XLWorkbook.DefaultStyle); } - List ranges = new List(); + #region IXLRanges Members public void Clear() { - ranges.ForEach(r => r.Clear()); + _ranges.ForEach(r => r.Clear()); } public void Add(IXLRange range) { - count++; - ranges.Add((XLRange)range); + _count++; + _ranges.Add((XLRange)range); } + //public void Add(String rangeAddress) //{ // count++; @@ -34,65 +41,148 @@ //} public void Remove(IXLRange range) { - count--; - ranges.RemoveAll(r => r.ToString() == range.ToString()); + _count--; + _ranges.RemoveAll(r => r.ToString() == range.ToString()); } - private Int32 count; - public Int32 Count { - get { return count; } - private set { count = value; } + public Int32 Count + { + get { return _count; } } public IEnumerator GetEnumerator() { var retList = new List(); - ranges.ForEach(c => retList.Add(c)); + _ranges.ForEach(retList.Add); return retList.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #region IXLStylized Members - - private IXLStyle style; public IXLStyle Style { + get { return _style; } + set + { + _style = new XLStyle(this, value); + foreach (XLRange rng in _ranges) + rng.Style = value; + } + } + + public Boolean Contains(IXLRange range) + { + return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Equals(range)); + } + + public IXLDataValidation DataValidation + { get { - return style; - } - set - { - style = new XLStyle(this, value); - foreach (var rng in ranges) + foreach (XLRange range in _ranges) { - rng.Style = value; + foreach (IXLDataValidation dv in range.Worksheet.DataValidations) + { + foreach (IXLRange dvRange in dv.Ranges) + { + if (dvRange.Intersects(range)) + { + dv.Ranges.Remove(dvRange); + foreach (IXLCell c in dvRange.Cells()) + { + if (!range.Contains(c.Address.ToString())) + dv.Ranges.Add(c.AsRange()); + } + } + } + } } + var dataValidation = new XLDataValidation(this, _ranges.First().Worksheet); + + _ranges.First().Worksheet.DataValidations.Add(dataValidation); + return dataValidation; } } + public IXLRanges AddToNamed(String rangeName) + { + return AddToNamed(rangeName, XLScope.Workbook); + } + + public IXLRanges AddToNamed(String rangeName, XLScope scope) + { + return AddToNamed(rangeName, XLScope.Workbook, null); + } + + public IXLRanges AddToNamed(String rangeName, XLScope scope, String comment) + { + _ranges.ForEach(r => r.AddToNamed(rangeName, scope, comment)); + return this; + } + + public Object Value + { + set { _ranges.ForEach(r => r.Value = value); } + } + + public IXLRanges SetValue(T value) + { + _ranges.ForEach(r => r.SetValue(value)); + return this; + } + + public IXLCells Cells() + { + var cells = new XLCells(false, false); + foreach (XLRange container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed() + { + var cells = new XLCells(true, false); + foreach (XLRange container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed(Boolean includeStyles) + { + var cells = new XLCells(true, includeStyles); + foreach (XLRange container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLRanges SetDataType(XLCellValues dataType) + { + _ranges.ForEach(c => c.DataType = dataType); + return this; + } + + #endregion + + #region IXLStylized Members + public IEnumerable Styles { get { UpdatingStyle = true; - yield return style; - foreach (var rng in ranges) + yield return _style; + foreach (XLRange rng in _ranges) { yield return rng.Style; - foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber - && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber - && c.Address.ColumnNumber <= rng.RangeAddress.LastAddress.ColumnNumber - )) - { + foreach (XLCell r in rng.Worksheet.Internals.CellsCollection.GetCells( + rng.RangeAddress.FirstAddress.RowNumber, + rng.RangeAddress.FirstAddress.ColumnNumber, + rng.RangeAddress.LastAddress.RowNumber, + rng.RangeAddress.LastAddress.ColumnNumber)) yield return r.Style; - } } UpdatingStyle = false; } @@ -102,15 +192,20 @@ public IXLStyle InnerStyle { - get { return style; } - set { style = new XLStyle(this, value); } + get { return _style; } + set { _style = new XLStyle(this, value); } + } + + public IXLRanges RangesUsed + { + get { return this; } } #endregion public override string ToString() { - String retVal = ranges.Aggregate(String.Empty, (agg, r)=> agg += r.ToString() + ","); + String retVal = _ranges.Aggregate(String.Empty, (agg, r) => agg + (r.ToString() + ",")); if (retVal.Length > 0) retVal = retVal.Substring(0, retVal.Length - 1); return retVal; } @@ -119,151 +214,15 @@ { var other = (XLRanges)obj; - if (this.ranges.Count != other.ranges.Count) - { + if (_ranges.Count != other._ranges.Count) return false; - } - else - { - foreach (var thisRange in this.ranges) - { - Boolean foundOne = false; - foreach (var otherRange in other.ranges) - { - if (thisRange.Equals(otherRange)) - { - foundOne = true; - break; - } - } - if (!foundOne) - return false; - } - - return true; - } - } - - public Boolean Contains(IXLRange range) - { - foreach (var r in this.ranges) - { - if (!r.RangeAddress.IsInvalid && r.Equals(range)) return true; - } - return false; + return _ranges.Select(thisRange => Enumerable.Contains(other._ranges, thisRange)).All(foundOne => foundOne); } public override int GetHashCode() { - Int32 hash = 0; - foreach (var r in this.ranges) - { - hash ^= r.GetHashCode(); - } - return hash; - } - - public IXLDataValidation DataValidation - { - get - { - foreach (var range in ranges) - { - String address = range.RangeAddress.ToString(); - foreach (var dv in range.Worksheet.DataValidations) - { - foreach (var dvRange in dv.Ranges) - { - if (dvRange.Intersects(range)) - { - dv.Ranges.Remove(dvRange); - foreach (var c in dvRange.Cells()) - { - if (!range.Contains(c.Address.ToString())) - dv.Ranges.Add(c.AsRange()); - } - } - } - } - } - var dataValidation = new XLDataValidation(this, ranges.First().Worksheet as XLWorksheet); - - ranges.First().Worksheet.DataValidations.Add(dataValidation); - return dataValidation; - } - } - - public IXLRanges AddToNamed(String rangeName) - { - return AddToNamed(rangeName, XLScope.Workbook); - } - public IXLRanges AddToNamed(String rangeName, XLScope scope) - { - return AddToNamed(rangeName, XLScope.Workbook, null); - } - public IXLRanges AddToNamed(String rangeName, XLScope scope, String comment) - { - ranges.ForEach(r => r.AddToNamed(rangeName, scope, comment)); - return this; - } - - public Object Value - { - set - { - ranges.ForEach(r => r.Value = value); - } - } - - public IXLRanges SetValue(T value) - { - ranges.ForEach(r => r.SetValue(value)); - return this; - } - - public IXLRanges RangesUsed - { - get - { - return this; - } - } - - public IXLCells Cells() - { - var cells = new XLCells( false, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed() - { - var cells = new XLCells( true, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed(Boolean includeStyles) - { - var cells = new XLCells( true, includeStyles); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLRanges SetDataType(XLCellValues dataType) - { - ranges.ForEach(c => c.DataType = dataType); - return this; + return _ranges.Aggregate(0, (current, r) => current ^ r.GetHashCode()); } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index cea522f..c8bd684 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -361,7 +361,7 @@ style = new XLStyle(this, value); var row = RowNumber(); - foreach (var c in (Worksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row)) + foreach (var c in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) { c.Style = value; } @@ -394,14 +394,10 @@ Int32 minColumn = 1; Int32 maxColumn = 0; var row = RowNumber(); - if ((Worksheet).Internals.CellsCollection.Values.Any(c => c.Address.RowNumber == row)) + if (Worksheet.Internals.CellsCollection.RowsUsed.ContainsKey(row)) { - minColumn = (Worksheet).Internals.CellsCollection.Values - .Where(c => c.Address.RowNumber == row) - .Min(c => c.Address.ColumnNumber); - maxColumn = (Worksheet).Internals.CellsCollection.Values - .Where(c => c.Address.RowNumber == row) - .Max(c => c.Address.ColumnNumber); + minColumn = Worksheet.Internals.CellsCollection.MinColumnInRow(row); + maxColumn = Worksheet.Internals.CellsCollection.MaxColumnInRow(row); } if ((Worksheet).Internals.ColumnsCollection.Count > 0) @@ -437,11 +433,9 @@ var row = RowNumber(); Int32 minColumn = 1; Int32 maxColumn = 0; - if ((Worksheet).Internals.CellsCollection.Values.Any(c => c.Address.RowNumber == row)) + if ((Worksheet).Internals.CellsCollection.RowsUsed.ContainsKey(row)) { - maxColumn = - (Worksheet).Internals.CellsCollection.Values.Where(c => c.Address.RowNumber == row).Max( - c => c.Address.ColumnNumber); + maxColumn = Worksheet.Internals.CellsCollection.MaxColumnInRow(row); } if ((Worksheet).Internals.ColumnsCollection.Count > 0) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 434e67c..cc8c2aa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -4,89 +4,51 @@ namespace ClosedXML.Excel { + using System.Collections; + internal class XLRows : IXLRows, IXLStylized { - private XLWorksheet worksheet; + private readonly List rows = new List(); + private readonly XLWorksheet worksheet; + internal IXLStyle style; + public XLRows(XLWorksheet worksheet) { this.worksheet = worksheet; style = new XLStyle(this, XLWorkbook.DefaultStyle); } - List rows = new List(); + #region IXLRows Members public IEnumerator GetEnumerator() { var retList = new List(); - rows.ForEach(c => retList.Add(c)); + rows.ForEach(retList.Add); return retList.GetEnumerator(); } - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #region IXLStylized Members - - internal IXLStyle style; public IXLStyle Style { - get - { - return style; - } + get { return style; } set { style = new XLStyle(this, value); if (worksheet != null) - { worksheet.Style = value; - } else { - foreach (var row in rows) - { + foreach (XLRow row in rows) row.Style = value; - } } - } } - public IEnumerable Styles - { - get - { - UpdatingStyle = true; - yield return style; - if (worksheet != null) - { - yield return worksheet.Style; - } - else - { - foreach (var row in rows) - { - foreach (var s in row.Styles) - yield return s; - } - } - UpdatingStyle = false; - } - } - - public Boolean UpdatingStyle { get; set; } - - public IXLStyle InnerStyle - { - get { return style; } - set { style = new XLStyle(this, value); } - } - - #endregion - public double Height { set @@ -110,7 +72,7 @@ else { var toDelete = new Dictionary>(); - foreach (var r in rows) + foreach (XLRow r in rows) { if (!toDelete.ContainsKey(r.Worksheet)) toDelete.Add(r.Worksheet, new List()); @@ -118,29 +80,26 @@ toDelete[r.Worksheet].Add(r.RowNumber()); } - foreach (var kp in toDelete) + foreach (KeyValuePair> kp in toDelete) { - foreach (var r in kp.Value.OrderByDescending(r => r)) + foreach (int r in kp.Value.OrderByDescending(r => r)) kp.Key.Row(r).Delete(); } } } - public void Add(XLRow row) - { - rows.Add(row); - } - public IXLRows AdjustToContents() { rows.ForEach(r => r.AdjustToContents()); return this; } + public IXLRows AdjustToContents(Int32 startColumn) { rows.ForEach(r => r.AdjustToContents(startColumn)); return this; } + public IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn) { rows.ForEach(r => r.AdjustToContents(startColumn, endColumn)); @@ -152,11 +111,13 @@ rows.ForEach(r => r.AdjustToContents(minHeight, maxHeight)); return this; } + public IXLRows AdjustToContents(Int32 startColumn, Double minHeight, Double maxHeight) { rows.ForEach(r => r.AdjustToContents(startColumn, minHeight, maxHeight)); return this; } + public IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn, Double minHeight, Double maxHeight) { rows.ForEach(r => r.AdjustToContents(startColumn, endColumn, minHeight, maxHeight)); @@ -168,6 +129,7 @@ { rows.ForEach(r => r.Hide()); } + public void Unhide() { rows.ForEach(r => r.Unhide()); @@ -177,30 +139,37 @@ { Group(false); } + public void Group(Int32 outlineLevel) { Group(outlineLevel, false); } + public void Ungroup() { Ungroup(false); } + public void Group(Boolean collapse) { rows.ForEach(r => r.Group(collapse)); } + public void Group(Int32 outlineLevel, Boolean collapse) { rows.ForEach(r => r.Group(outlineLevel, collapse)); } + public void Ungroup(Boolean ungroupFromAll) { rows.ForEach(r => r.Ungroup(ungroupFromAll)); } + public void Collapse() { rows.ForEach(r => r.Collapse()); } + public void Expand() { rows.ForEach(r => r.Expand()); @@ -208,32 +177,71 @@ public IXLCells Cells() { - var cells = new XLCells( false, false); - foreach (var container in rows) - { + var cells = new XLCells(false, false); + foreach (XLRow container in rows) cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + return cells; } public IXLCells CellsUsed() { - var cells = new XLCells( true, false); - foreach (var container in rows) - { + var cells = new XLCells(true, false); + foreach (XLRow container in rows) cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + return cells; } public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells( true, includeStyles); - foreach (var container in rows) - { + var cells = new XLCells(true, includeStyles); + foreach (XLRow container in rows) cells.Add(container.RangeAddress); + return cells; + } + + public IXLRows AddHorizontalPageBreaks() + { + foreach (XLRow row in rows) + row.Worksheet.PageSetup.AddHorizontalPageBreak(row.RowNumber()); + return this; + } + + public IXLRows SetDataType(XLCellValues dataType) + { + rows.ForEach(c => c.DataType = dataType); + return this; + } + + #endregion + + #region IXLStylized Members + + public IEnumerable Styles + { + get + { + UpdatingStyle = true; + yield return style; + if (worksheet != null) + yield return worksheet.Style; + else + { + foreach (XLRow row in rows) + { + foreach (IXLStyle s in row.Styles) + yield return s; + } + } + UpdatingStyle = false; } - return (IXLCells)cells; + } + + public Boolean UpdatingStyle { get; set; } + + public IXLStyle InnerStyle + { + get { return style; } + set { style = new XLStyle(this, value); } } public IXLRanges RangesUsed @@ -246,19 +254,11 @@ } } - public IXLRows AddHorizontalPageBreaks() - { - foreach (var row in rows) - { - row.Worksheet.PageSetup.AddHorizontalPageBreak(row.RowNumber()); - } - return this; - } + #endregion - public IXLRows SetDataType(XLCellValues dataType) + public void Add(XLRow row) { - rows.ForEach(c => c.DataType = dataType); - return this; + rows.Add(row); } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index d2f0e4e..31c811b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -192,7 +192,7 @@ public new IXLTableRows Rows() { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet.Style); foreach (var r in Enumerable.Range(1, DataRange.RowCount())) { retVal.Add(Row(r)); @@ -202,7 +202,7 @@ public new IXLTableRows Rows(int firstRow, int lastRow) { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet.Style); for (var ro = firstRow; ro <= lastRow; ro++) { @@ -213,7 +213,7 @@ public new IXLTableRows Rows(string rows) { - var retVal = new XLTableRows(Worksheet); + var retVal = new XLTableRows(Worksheet.Style); var rowPairs = rows.Split(','); foreach (var pair in rowPairs) { @@ -424,5 +424,21 @@ public IXLTable SetShowTotalsRow() { ShowTotalsRow = true; return this; } public IXLTable SetShowTotalsRow(Boolean value) { ShowTotalsRow = value; return this; } public IXLTable SetShowAutoFilter() { ShowAutoFilter = true; return this; } public IXLTable SetShowAutoFilter(Boolean value) { ShowAutoFilter = value; return this; } + + + IXLRangeColumn IXLTable.FirstColumn() + { + return FirstColumn(); + } + + IXLRangeColumn IXLTable.FirstColumnUsed() + { + return FirstColumnUsed(); + } + + IXLRangeColumn IXLTable.LastColumnUsed() + { + return LastColumnUsed(); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index ea5fc50..c4771e2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -1,76 +1,38 @@ using System; using System.Collections.Generic; -using System.Linq; namespace ClosedXML.Excel { + using System.Collections; + internal class XLTableRows : IXLTableRows, IXLStylized { - XLWorksheet worksheet; - public XLTableRows(XLWorksheet worksheet) - { - style = new XLStyle(this, worksheet.Style); - this.worksheet = worksheet; - } + private readonly List _ranges = new List(); + private IXLStyle _style; + - List ranges = new List(); - - public void Clear() + public XLTableRows(IXLStyle defaultStyle) { - ranges.ForEach(r => r.Clear()); - } - - public void Add(IXLTableRow range) - { - ranges.Add((XLTableRow)range); - } - - public IEnumerator GetEnumerator() - { - var retList = new List(); - ranges.ForEach(c => retList.Add(c)); - return retList.GetEnumerator(); - } - - System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() - { - return GetEnumerator(); + _style = new XLStyle(this, defaultStyle); } #region IXLStylized Members - private IXLStyle style; - public IXLStyle Style - { - get - { - return style; - } - set - { - style = new XLStyle(this, value); - ranges.ForEach(r => r.Style = value); - } - } - public IEnumerable Styles { get { UpdatingStyle = true; - yield return style; - foreach (var rng in ranges) + yield return _style; + foreach (XLTableRow rng in _ranges) { yield return rng.Style; - foreach (var r in (rng.Worksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => - c.Address.RowNumber >= rng.RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= rng.RangeAddress.LastAddress.RowNumber - && c.Address.ColumnNumber >= rng.RangeAddress.FirstAddress.ColumnNumber - && c.Address.ColumnNumber <= rng.RangeAddress.LastAddress.ColumnNumber - )) - { + foreach (XLCell r in rng.Worksheet.Internals.CellsCollection.GetCells( + rng.RangeAddress.FirstAddress.RowNumber, + rng.RangeAddress.FirstAddress.ColumnNumber, + rng.RangeAddress.LastAddress.RowNumber, + rng.RangeAddress.LastAddress.ColumnNumber)) yield return r.Style; - } } UpdatingStyle = false; } @@ -80,40 +42,8 @@ public IXLStyle InnerStyle { - get { return style; } - set { style = new XLStyle(this, value); } - } - - #endregion - - public IXLCells Cells() - { - var cells = new XLCells( false, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed() - { - var cells = new XLCells( true, false); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; - } - - public IXLCells CellsUsed(Boolean includeStyles) - { - var cells = new XLCells( false, includeStyles); - foreach (var container in ranges) - { - cells.Add(container.RangeAddress); - } - return (IXLCells)cells; + get { return _style; } + set { _style = new XLStyle(this, value); } } public IXLRanges RangesUsed @@ -125,6 +55,67 @@ return retVal; } } - + + #endregion + + #region IXLTableRows Members + + public void Clear() + { + _ranges.ForEach(r => r.Clear()); + } + + public void Add(IXLTableRow range) + { + _ranges.Add((XLTableRow)range); + } + + public IEnumerator GetEnumerator() + { + var retList = new List(); + _ranges.ForEach(retList.Add); + return retList.GetEnumerator(); + } + + IEnumerator IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + public IXLStyle Style + { + get { return _style; } + set + { + _style = new XLStyle(this, value); + _ranges.ForEach(r => r.Style = value); + } + } + + public IXLCells Cells() + { + var cells = new XLCells(false, false); + foreach (XLTableRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed() + { + var cells = new XLCells(true, false); + foreach (XLTableRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + public IXLCells CellsUsed(Boolean includeStyles) + { + var cells = new XLCells(false, includeStyles); + foreach (XLTableRow container in _ranges) + cells.Add(container.RangeAddress); + return cells; + } + + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 32e46a4..e86b8df 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -600,7 +600,7 @@ var newRichStrings = new Dictionary(); foreach (XLWorksheet w in Worksheets.Cast()) { - foreach (XLCell c in w.Internals.CellsCollection.Values) + foreach (XLCell c in w.Internals.CellsCollection.GetCells()) { if ( c.DataType == XLCellValues.Text @@ -752,7 +752,7 @@ foreach (XLWorksheet worksheet in WorksheetsInternal) { var cellsWithoutFormulas = new HashSet(); - foreach (XLCell c in worksheet.Internals.CellsCollection.Values) + foreach (XLCell c in worksheet.Internals.CellsCollection.GetCells()) { if (StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) cellsWithoutFormulas.Add(c.Address.ToStringRelative()); @@ -2380,8 +2380,8 @@ String sheetDimensionReference = "A1"; if (xlWorksheet.Internals.CellsCollection.Count > 0) { - maxColumn = xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); - Int32 maxRow = xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); + maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed; + Int32 maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed; sheetDimensionReference = "A1:" + ExcelHelper.GetColumnLetterFromNumber(maxColumn) + maxRow.ToStringLookup(); } @@ -2642,7 +2642,7 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var cellsByRow = new Dictionary>(); - foreach (XLCell c in xlWorksheet.Internals.CellsCollection.Values) + foreach (XLCell c in xlWorksheet.Internals.CellsCollection.GetCells()) { Int32 rowNum = c.Address.RowNumber; if (!cellsByRow.ContainsKey(rowNum)) @@ -2722,10 +2722,11 @@ var cellsByReference = row.Elements().ToDictionary(c => c.CellReference.Value, c => c); - foreach (KeyValuePair c in xlWorksheet.Internals.CellsCollection.Deleted) + foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted) { - if (cellsByReference.ContainsKey(c.Key.ToStringRelative())) - row.RemoveChild(cellsByReference[c.Key.ToStringRelative()]); + String key = ExcelHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup(); + if (cellsByReference.ContainsKey(key)) + row.RemoveChild(cellsByReference[key]); } if (cellsByRow.ContainsKey(distinctRow)) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index fdbc0ee..86e96c0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -11,29 +11,37 @@ internal class XLWorksheet : XLRangeBase, IXLWorksheet { #region Constants - + #endregion + #region Events + public event RangeShiftedRowsDelegate RangeShiftedRows; public event RangeShiftedColumnsDelegate RangeShiftedColumns; + #endregion + #region Fields - internal Int32 m_position; - private Double m_rowHeight; - private String m_name; + private readonly Dictionary _columnOutlineCount = new Dictionary(); + private readonly Dictionary _rowOutlineCount = new Dictionary(); + private String _name; + internal Int32 _position; - private IXLSortElements m_sortRows; - private IXLSortElements m_sortColumns; - private Boolean m_tabActive; + private Double _rowHeight; + private IXLSortElements _sortColumns; + private IXLSortElements _sortRows; + private Boolean _tabActive; - private readonly Dictionary m_columnOutlineCount = new Dictionary(); - private readonly Dictionary m_rowOutlineCount = new Dictionary(); #endregion + #region Constructor + public XLWorksheet(String sheetName, XLWorkbook workbook) - : base(new XLRangeAddress(new XLAddress(null, ExcelHelper.MinRowNumber, ExcelHelper.MinColumnNumber, false, false), - new XLAddress(null, ExcelHelper.MaxRowNumber, ExcelHelper.MaxColumnNumber, false, false))) + : base( + new XLRangeAddress( + new XLAddress(null, ExcelHelper.MinRowNumber, ExcelHelper.MinColumnNumber, false, false), + new XLAddress(null, ExcelHelper.MaxRowNumber, ExcelHelper.MaxColumnNumber, false, false))) { RangeAddress.Worksheet = this; RangeAddress.FirstAddress.Worksheet = this; @@ -46,12 +54,13 @@ DataValidations = new XLDataValidations(); Protection = new XLSheetProtection(); Workbook = workbook; - style = new XLStyle(this, workbook.Style); - Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(), workbook); + _style = new XLStyle(this, workbook.Style); + Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(), + new XLRowsCollection(), new XLRanges(), workbook); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; - m_rowHeight = workbook.RowHeight; + _rowHeight = workbook.RowHeight; RowHeightChanged = workbook.RowHeight != XLWorkbook.DefaultRowHeight; Name = sheetName; RangeShiftedRows += XLWorksheet_RangeShiftedRows; @@ -66,102 +75,20 @@ ShowZeros = workbook.ShowZeros; TabColor = new XLColor(); } + #endregion - public XLWorkbook Workbook { get; private set; } - void XLWorksheet_RangeShiftedColumns(XLRange range, int columnsShifted) - { - var newMerge = new XLRanges(); - foreach (var rngMerged in Internals.MergedRanges) - { - 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 newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber, - rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, - rngMerged.RangeAddress.LastAddress.RowNumber, - rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); - newMerge.Add(newRng); - } - else if ( - !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) - { - newMerge.Add(rngMerged); - } - } - Internals.MergedRanges = newMerge; - } - - void XLWorksheet_RangeShiftedRows(XLRange range, int rowsShifted) - { - var newMerge = new XLRanges(); - foreach (var rngMerged in Internals.MergedRanges) - { - 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 newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.FirstAddress.ColumnNumber, - rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.LastAddress.ColumnNumber); - newMerge.Add(newRng); - } - else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber)) - { - newMerge.Add(rngMerged); - } - } - Internals.MergedRanges = newMerge; - } - - public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted) - { - if (RangeShiftedRows != null) - { - RangeShiftedRows(range, rowsShifted); - } - } - - public void NotifyRangeShiftedColumns(XLRange range, Int32 columnsShifted) - { - if (RangeShiftedColumns != null) - { - RangeShiftedColumns(range, columnsShifted); - } - } - + private IXLStyle _style; public XLWorksheetInternals Internals { get; private set; } - #region IXLStylized Members - private IXLStyle style; - public override IXLStyle Style - { - get { return style; } - set - { - style = new XLStyle(this, value); - foreach (var cell in Internals.CellsCollection.Values) - { - cell.Style = style; - } - } - } public override IEnumerable Styles { get { UpdatingStyle = true; - yield return style; - foreach (var c in Internals.CellsCollection.Values) - { + yield return _style; + foreach (XLCell c in Internals.CellsCollection.GetCells()) yield return c.Style; - } UpdatingStyle = false; } } @@ -170,122 +97,203 @@ public override IXLStyle InnerStyle { - get { return new XLStyle(new XLStylizedContainer(style, this), style); } - set { style = new XLStyle(this, value); } + get { return new XLStyle(new XLStylizedContainer(_style, this), _style); } + set { _style = new XLStyle(this, value); } } - #endregion - public Double ColumnWidth { get; set; } + internal Boolean RowHeightChanged { get; set; } + public Int32 SheetId { get; set; } + public String RelId { get; set; } + public XLDataValidations DataValidations { get; private set; } + public IXLCharts Charts { get; private set; } + + #region IXLWorksheet Members + + public XLWorkbook Workbook { get; private set; } + + public override IXLStyle Style + { + get { return _style; } + set + { + _style = new XLStyle(this, value); + foreach (XLCell cell in Internals.CellsCollection.GetCells()) + cell.Style = _style; + } + } + + public Double ColumnWidth { get; set; } + public Double RowHeight { - get { return m_rowHeight; } + get { return _rowHeight; } set { RowHeightChanged = true; - m_rowHeight = value; + _rowHeight = value; } } public String Name { - get { return m_name; } + get { return _name; } set { - (Workbook.WorksheetsInternal).Rename(m_name, value); - m_name = value; + (Workbook.WorksheetsInternal).Rename(_name, value); + _name = value; } } - public Int32 SheetId { get; set; } - public String RelId { get; set; } - public Int32 Position { - get { return m_position; } + get { return _position; } set { if (value > Workbook.WorksheetsInternal.Count + 1) - { throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets + 1."); - } - if (value < m_position) + if (value < _position) { Workbook.WorksheetsInternal - .Where(w => w.Position >= value && w.Position < m_position) - .ForEach(w => w.m_position += 1); + .Where(w => w.Position >= value && w.Position < _position) + .ForEach(w => w._position += 1); } - if (value > m_position) + if (value > _position) { Workbook.WorksheetsInternal - .Where(w => w.Position <= value && w.Position > m_position) - .ForEach(w => (w).m_position -= 1); + .Where(w => w.Position <= value && w.Position > _position) + .ForEach(w => (w)._position -= 1); } - m_position = value; + _position = value; } } public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } - public IXLRow FirstRowUsed() + public XLRow FirstRowUsed() { - var rngRow = AsRange().FirstRowUsed(); - if (rngRow != null) - { - return Row(rngRow.RangeAddress.FirstAddress.RowNumber); - } - return null; - } - public IXLRow LastRowUsed() - { - var rngRow = AsRange().LastRowUsed(); - if (rngRow != null) - { - return Row(rngRow.RangeAddress.LastAddress.RowNumber); - } - else - { - return null; - } + return FirstRowUsed(false); } - public IXLColumn LastColumn() + IXLRow IXLWorksheet.FirstRowUsed() + { + return FirstRowUsed(); + } + + public XLRow FirstRowUsed(Boolean includeFormats) + { + var rngRow = AsRange().FirstRowUsed(includeFormats); + if (rngRow != null) + return Row(rngRow.RangeAddress.FirstAddress.RowNumber); + return null; + } + + IXLRow IXLWorksheet.FirstRowUsed(Boolean includeFormats) + { + return FirstRowUsed(includeFormats); + } + + public XLRow LastRowUsed() + { + return LastRowUsed(false); + } + IXLRow IXLWorksheet.LastRowUsed() + { + return LastRowUsed(); + } + public XLRow LastRowUsed(Boolean includeFormats) + { + var rngRow = AsRange().LastRowUsed(includeFormats); + if (rngRow != null) + return Row(rngRow.RangeAddress.LastAddress.RowNumber); + + return null; + } + IXLRow IXLWorksheet.LastRowUsed(Boolean includeFormats) + { + return LastRowUsed(includeFormats); + } + + public XLColumn LastColumn() { return Column(ExcelHelper.MaxColumnNumber); } - public IXLColumn FirstColumn() + IXLColumn IXLWorksheet.LastColumn() + { + return LastColumn(); + } + + public XLColumn FirstColumn() { return Column(1); } - public IXLRow FirstRow() + IXLColumn IXLWorksheet.FirstColumn() + { + return FirstColumn(); + } + + public XLRow FirstRow() { return Row(1); } - public IXLRow LastRow() + IXLRow IXLWorksheet.FirstRow() + { + return FirstRow(); + } + + public XLRow LastRow() { return Row(ExcelHelper.MaxRowNumber); } - public IXLColumn FirstColumnUsed() + IXLRow IXLWorksheet.LastRow() { - var rngColumn = AsRange().FirstColumnUsed(); + return LastRow(); + } + + public XLColumn FirstColumnUsed() + { + return FirstColumnUsed(false); + } + IXLColumn IXLWorksheet.FirstColumnUsed() + { + return FirstColumnUsed(); + } + + public XLColumn FirstColumnUsed(Boolean includeFormats) + { + var rngColumn = AsRange().FirstColumnUsed(includeFormats); return rngColumn != null ? Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber) : null; } - public IXLColumn LastColumnUsed() + IXLColumn IXLWorksheet.FirstColumnUsed(Boolean includeFormats) { - var rngColumn = AsRange().LastColumnUsed(); - if (rngColumn != null) - { - return Column(rngColumn.RangeAddress.LastAddress.ColumnNumber); - } - else - { - return null; - } + return FirstColumnUsed(includeFormats); } + public XLColumn LastColumnUsed() + { + return LastColumnUsed(false); + } + IXLColumn IXLWorksheet.LastColumnUsed() + { + return LastColumnUsed(); + } + + public XLColumn LastColumnUsed(Boolean includeFormats) + { + var rngColumn = AsRange().LastColumnUsed(includeFormats); + if (rngColumn != null) + return Column(rngColumn.RangeAddress.LastAddress.ColumnNumber); + + return null; + } + IXLColumn IXLWorksheet.LastColumnUsed(Boolean includeFormats) + { + return LastColumnUsed(includeFormats); + } + public IXLColumns Columns() { @@ -293,37 +301,30 @@ var columnList = new List(); if (Internals.CellsCollection.Count > 0) - { - columnList.AddRange(Internals.CellsCollection.Keys.Select(k => k.ColumnNumber).Distinct()); - } + columnList.AddRange(Internals.CellsCollection.ColumnsUsed.Keys); if (Internals.ColumnsCollection.Count > 0) - { columnList.AddRange(Internals.ColumnsCollection.Keys.Where(c => !columnList.Contains(c))); - } - foreach (var c in columnList) - { - retVal.Add((XLColumn) Column(c)); - } + foreach (int c in columnList) + retVal.Add((XLColumn)Column(c)); return retVal; } + public IXLColumns Columns(String columns) { var retVal = new XLColumns(null); var columnPairs = columns.Split(','); - foreach (var pair in columnPairs) + foreach (string pair in columnPairs) { - var tPair = pair.Trim(); + string tPair = pair.Trim(); String firstColumn; String lastColumn; if (tPair.Contains(':') || tPair.Contains('-')) { if (tPair.Contains('-')) - { tPair = tPair.Replace('-', ':'); - } var columnRange = tPair.Split(':'); firstColumn = columnRange[0]; @@ -338,33 +339,30 @@ Int32 tmp; if (Int32.TryParse(firstColumn, out tmp)) { - foreach (var col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))) - { - retVal.Add((XLColumn) col); - } + foreach (IXLColumn col in Columns(Int32.Parse(firstColumn), Int32.Parse(lastColumn))) + retVal.Add((XLColumn)col); } else { - foreach (var col in Columns(firstColumn, lastColumn)) - { - retVal.Add((XLColumn) col); - } + foreach (IXLColumn col in Columns(firstColumn, lastColumn)) + retVal.Add((XLColumn)col); } } return retVal; } + public IXLColumns Columns(String firstColumn, String lastColumn) { - return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), ExcelHelper.GetColumnNumberFromLetter(lastColumn)); + return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), + ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } + public IXLColumns Columns(Int32 firstColumn, Int32 lastColumn) { var retVal = new XLColumns(null); - for (var co = firstColumn; co <= lastColumn; co++) - { - retVal.Add((XLColumn) Column(co)); - } + for (int co = firstColumn; co <= lastColumn; co++) + retVal.Add((XLColumn)Column(co)); return retVal; } @@ -374,37 +372,30 @@ var rowList = new List(); if (Internals.CellsCollection.Count > 0) - { - rowList.AddRange(Internals.CellsCollection.Keys.Select(k => k.RowNumber).Distinct()); - } + rowList.AddRange(Internals.CellsCollection.RowsUsed.Keys); if (Internals.RowsCollection.Count > 0) - { rowList.AddRange(Internals.RowsCollection.Keys.Where(r => !rowList.Contains(r))); - } - foreach (var r in rowList) - { - retVal.Add((XLRow) Row(r)); - } + foreach (int r in rowList) + retVal.Add((XLRow)Row(r)); return retVal; } + public IXLRows Rows(String rows) { var retVal = new XLRows(null); var rowPairs = rows.Split(','); - foreach (var pair in rowPairs) + foreach (string pair in rowPairs) { - var tPair = pair.Trim(); + string tPair = pair.Trim(); String firstRow; String lastRow; if (tPair.Contains(':') || tPair.Contains('-')) { if (tPair.Contains('-')) - { tPair = tPair.Replace('-', ':'); - } var rowRange = tPair.Split(':'); firstRow = rowRange[0]; @@ -415,99 +406,73 @@ firstRow = tPair; lastRow = tPair; } - foreach (var row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - { - retVal.Add((XLRow) row); - } + foreach (IXLRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) + retVal.Add((XLRow)row); } return retVal; } + public IXLRows Rows(Int32 firstRow, Int32 lastRow) { var retVal = new XLRows(null); - for (var ro = firstRow; ro <= lastRow; ro++) - { - retVal.Add((XLRow) Row(ro)); - } + for (int ro = firstRow; ro <= lastRow; ro++) + retVal.Add((XLRow)Row(ro)); return retVal; } - public IXLRow Row(Int32 row) + public XLRow Row(Int32 row) { return Row(row, true); } - public IXLRow Row(Int32 row, Boolean pingCells) + + IXLRow IXLWorksheet.Row(Int32 row) { - IXLStyle styleToUse; - if (Internals.RowsCollection.ContainsKey(row)) - { - styleToUse = Internals.RowsCollection[row].Style; - } - else - { - if (pingCells) - { - // This is a new row so we're going to reference all - // cells in columns of this row to preserve their formatting - var distinctColumns = new HashSet(); - foreach (var k in Internals.CellsCollection.Keys) - { - if (!distinctColumns.Contains(k.ColumnNumber)) - { - distinctColumns.Add(k.ColumnNumber); - } - } - - var usedColumns = from c in Internals.ColumnsCollection - join dc in distinctColumns - on c.Key equals dc - where !Internals.CellsCollection.ContainsKey(new XLAddress(Worksheet, row, c.Key, false, false)) - select c.Key; - - usedColumns.ForEach(c => Cell(row, c)); - } - styleToUse = Style; - Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, styleToUse, false))); - } - - return new XLRow(row, new XLRowParameters(this, styleToUse, true)); + return Row(row); } - public IXLColumn Column(Int32 column) + + public XLColumn Column(Int32 column) { - IXLStyle styleToUse; - if (Internals.ColumnsCollection.ContainsKey(column)) - { - styleToUse = Internals.ColumnsCollection[column].Style; - } - else + if (!Internals.ColumnsCollection.ContainsKey(column)) { // 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)); - styleToUse = Style; Internals.ColumnsCollection.Add(column, new XLColumn(column, new XLColumnParameters(this, Style, false))); } return new XLColumn(column, new XLColumnParameters(this, Style, true)); } + + IXLColumn IXLWorksheet.Column(Int32 column) + { + return Column(column); + } + public IXLColumn Column(String column) { return Column(ExcelHelper.GetColumnNumberFromLetter(column)); } + IXLColumn IXLWorksheet.Column(String column) + { + return Column(column); + } IXLCell IXLWorksheet.Cell(int row, int column) { return Cell(row, column); } + IXLCell IXLWorksheet.Cell(string cellAddressInRange) { return Cell(cellAddressInRange); } + IXLCell IXLWorksheet.Cell(int row, string column) { return Cell(row, column); } + IXLCell IXLWorksheet.Cell(IXLAddress cellAddressInRange) { return Cell(cellAddressInRange); @@ -517,22 +482,27 @@ { return Range(rangeAddress); } + IXLRange IXLWorksheet.Range(string rangeAddress) { return Range(rangeAddress); } + IXLRange IXLWorksheet.Range(IXLCell firstCell, IXLCell lastCell) { return Range(firstCell, lastCell); } + IXLRange IXLWorksheet.Range(string firstCellAddress, string lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLWorksheet.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) { return Range(firstCellAddress, lastCellAddress); } + IXLRange IXLWorksheet.Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) { return Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); @@ -548,16 +518,19 @@ Enumerable.Range(1, 8).ForEach(i => CollapseRows(i)); return this; } + public IXLWorksheet CollapseColumns() { Enumerable.Range(1, 8).ForEach(i => CollapseColumns(i)); return this; } + public IXLWorksheet ExpandRows() { Enumerable.Range(1, 8).ForEach(i => ExpandRows(i)); return this; } + public IXLWorksheet ExpandColumns() { Enumerable.Range(1, 8).ForEach(i => ExpandRows(i)); @@ -567,39 +540,34 @@ public IXLWorksheet CollapseRows(Int32 outlineLevel) { if (outlineLevel < 1 || outlineLevel > 8) - { throw new ArgumentOutOfRangeException("Outline level must be between 1 and 8."); - } Internals.RowsCollection.Values.Where(r => r.OutlineLevel == outlineLevel).ForEach(r => r.Collapse()); return this; } + public IXLWorksheet CollapseColumns(Int32 outlineLevel) { if (outlineLevel < 1 || outlineLevel > 8) - { throw new ArgumentOutOfRangeException("outlineLevel", "Outline level must be between 1 and 8."); - } Internals.ColumnsCollection.Values.Where(c => c.OutlineLevel == outlineLevel).ForEach(c => c.Collapse()); return this; } + public IXLWorksheet ExpandRows(Int32 outlineLevel) { if (outlineLevel < 1 || outlineLevel > 8) - { throw new ArgumentOutOfRangeException("outlineLevel", "Outline level must be between 1 and 8."); - } Internals.RowsCollection.Values.Where(r => r.OutlineLevel == outlineLevel).ForEach(r => r.Expand()); return this; } + public IXLWorksheet ExpandColumns(Int32 outlineLevel) { if (outlineLevel < 1 || outlineLevel > 8) - { throw new ArgumentOutOfRangeException("outlineLevel", "Outline level must be between 1 and 8."); - } Internals.ColumnsCollection.Values.Where(c => c.OutlineLevel == outlineLevel).ForEach(c => c.Expand()); return this; @@ -609,6 +577,7 @@ { Workbook.WorksheetsInternal.Delete(Name); } + public new void Clear() { Internals.CellsCollection.Clear(); @@ -616,13 +585,17 @@ Internals.MergedRanges.Clear(); Internals.RowsCollection.Clear(); } + public IXLNamedRanges NamedRanges { get; private set; } + public IXLNamedRange NamedRange(String rangeName) { return NamedRanges.NamedRange(rangeName); } + public IXLSheetView SheetView { get; private set; } public IXLTables Tables { get; private set; } + public IXLTable Table(String name) { return Tables.Table(name); @@ -645,39 +618,38 @@ public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position) { - var targetSheet = (XLWorksheet) workbook.WorksheetsInternal.Add(newSheetName, position); + var targetSheet = (XLWorksheet)workbook.WorksheetsInternal.Add(newSheetName, position); - Internals.CellsCollection.ForEach(kp => targetSheet.Cell(kp.Value.Address.RowNumber, kp.Value.Address.ColumnNumber).CopyFrom(kp.Value)); + Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c)); DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv, targetSheet))); - Internals.ColumnsCollection.ForEach(kp => targetSheet.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); + 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))); targetSheet.Visibility = Visibility; targetSheet.ColumnWidth = ColumnWidth; targetSheet.RowHeight = RowHeight; - targetSheet.style = new XLStyle(targetSheet, style); + targetSheet._style = new XLStyle(targetSheet, _style); targetSheet.PageSetup = new XLPageSetup(PageSetup, targetSheet); targetSheet.Outline = new XLOutline(Outline); targetSheet.SheetView = new XLSheetView(SheetView); - this.Internals.MergedRanges.ForEach(kp => targetSheet.Internals.MergedRanges.Add(targetSheet.Range(kp.RangeAddress.ToString()))); + Internals.MergedRanges.ForEach( + kp => targetSheet.Internals.MergedRanges.Add(targetSheet.Range(kp.RangeAddress.ToString()))); - foreach (var r in NamedRanges) + foreach (IXLNamedRange r in NamedRanges) { var ranges = new XLRanges(); r.Ranges.ForEach(ranges.Add); targetSheet.NamedRanges.Add(r.Name, ranges); } - foreach (var t in Tables.Cast()) + foreach (XLTable t in Tables.Cast()) { XLTable table; - if (targetSheet.Tables.Any(tt => tt.Name == t.Name)) - { + String tableName = t.Name; + if (targetSheet.Tables.Any(tt => tt.Name == tableName)) table = new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true); - } else - { - table = new XLTable(targetSheet.Range(t.RangeAddress.ToString()), t.Name, true); - } + table = new XLTable(targetSheet.Range(t.RangeAddress.ToString()), tableName, true); table.RelId = t.RelId; table.EmphasizeFirstColumn = t.EmphasizeFirstColumn; @@ -701,98 +673,26 @@ } if (AutoFilterRange != null) - { targetSheet.Range(AutoFilterRange.RangeAddress).SetAutoFilter(); - } return targetSheet; } - #region Outlines - public void IncrementColumnOutline(Int32 level) - { - if (level > 0) - { - if (!m_columnOutlineCount.ContainsKey(level)) - { - m_columnOutlineCount.Add(level, 0); - } - m_columnOutlineCount[level]++; - } - } - public void DecrementColumnOutline(Int32 level) - { - if (level > 0) - { - if (!m_columnOutlineCount.ContainsKey(level)) - { - m_columnOutlineCount.Add(level, 0); - } - - if (m_columnOutlineCount[level] > 0) - { - m_columnOutlineCount[level]--; - } - } - } - public Int32 GetMaxColumnOutline() - { - if (m_columnOutlineCount.Count == 0) - { - return 0; - } - return m_columnOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); - } - - public void IncrementRowOutline(Int32 level) - { - if (level > 0) - { - if (!m_rowOutlineCount.ContainsKey(level)) - { - m_rowOutlineCount.Add(level, 0); - } - - m_rowOutlineCount[level]++; - } - } - public void DecrementRowOutline(Int32 level) - { - if (level > 0) - { - if (!m_rowOutlineCount.ContainsKey(level)) - { - m_rowOutlineCount.Add(level, 0); - } - - if (m_rowOutlineCount[level] > 0) - { - m_rowOutlineCount[level]--; - } - } - } - public Int32 GetMaxRowOutline() - { - if (m_rowOutlineCount.Count == 0) - { - return 0; - } - return m_rowOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); - } - #endregion public new IXLHyperlinks Hyperlinks { get; private set; } - public XLDataValidations DataValidations { get; private set; } + IXLDataValidations IXLWorksheet.DataValidations { get { return DataValidations; } } public XLWorksheetVisibility Visibility { get; set; } + public IXLWorksheet Hide() { Visibility = XLWorksheetVisibility.Hidden; return this; } + public IXLWorksheet Unhide() { Visibility = XLWorksheetVisibility.Visible; @@ -800,18 +700,22 @@ } public IXLSheetProtection Protection { get; private set; } + public IXLSheetProtection Protect() { return Protection.Protect(); } + public IXLSheetProtection Protect(String password) { return Protection.Protect(password); } + public IXLSheetProtection Unprotect() { return Protection.Unprotect(); } + public IXLSheetProtection Unprotect(String password) { return Protection.Unprotect(password); @@ -821,12 +725,12 @@ public IXLSortElements SortRows { - get { return m_sortRows ?? (m_sortRows = new XLSortElements()); } + get { return _sortRows ?? (_sortRows = new XLSortElements()); } } public IXLSortElements SortColumns { - get { return m_sortColumns ?? (m_sortColumns = new XLSortElements()); } + get { return _sortColumns ?? (_sortColumns = new XLSortElements()); } } public IXLRange Sort() @@ -834,72 +738,73 @@ var range = GetRangeForSort(); return range.Sort(); } + public IXLRange Sort(Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(matchCase); } + public IXLRange Sort(XLSortOrder sortOrder) { var range = GetRangeForSort(); return range.Sort(sortOrder); } + public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(sortOrder, matchCase); } + public IXLRange Sort(String columnsToSortBy) { var range = GetRangeForSort(); return range.Sort(columnsToSortBy); } + public IXLRange Sort(String columnsToSortBy, Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(columnsToSortBy, matchCase); } + public IXLRange Sort(XLSortOrientation sortOrientation) { var range = GetRangeForSort(); return range.Sort(sortOrientation); } + public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(sortOrientation, matchCase); } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) { var range = GetRangeForSort(); return range.Sort(sortOrientation, sortOrder); } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(sortOrientation, sortOrder, matchCase); } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) { var range = GetRangeForSort(); return range.Sort(sortOrientation, elementsToSortBy); } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) { var range = GetRangeForSort(); return range.Sort(sortOrientation, elementsToSortBy, matchCase); } - private IXLRange GetRangeForSort() - { - var range = RangeUsed(); - SortColumns.ForEach(e => range.SortColumns.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); - SortRows.ForEach(e => range.SortRows.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); - return range; - } - - public IXLCharts Charts { get; private set; } - public Boolean ShowFormulas { get; set; } public Boolean ShowGridLines { get; set; } public Boolean ShowOutlineSymbols { get; set; } @@ -913,66 +818,79 @@ ShowFormulas = true; return this; } + public IXLWorksheet SetShowFormulas(Boolean value) { ShowFormulas = value; return this; } + public IXLWorksheet SetShowGridLines() { ShowGridLines = true; return this; } + public IXLWorksheet SetShowGridLines(Boolean value) { ShowGridLines = value; return this; } + public IXLWorksheet SetShowOutlineSymbols() { ShowOutlineSymbols = true; return this; } + public IXLWorksheet SetShowOutlineSymbols(Boolean value) { ShowOutlineSymbols = value; return this; } + public IXLWorksheet SetShowRowColHeaders() { ShowRowColHeaders = true; return this; } + public IXLWorksheet SetShowRowColHeaders(Boolean value) { ShowRowColHeaders = value; return this; } + public IXLWorksheet SetShowRuler() { ShowRuler = true; return this; } + public IXLWorksheet SetShowRuler(Boolean value) { ShowRuler = value; return this; } + public IXLWorksheet SetShowWhiteSpace() { ShowWhiteSpace = true; return this; } + public IXLWorksheet SetShowWhiteSpace(Boolean value) { ShowWhiteSpace = value; return this; } + public IXLWorksheet SetShowZeros() { ShowZeros = true; return this; } + public IXLWorksheet SetShowZeros(Boolean value) { ShowZeros = value; @@ -980,6 +898,7 @@ } public IXLColor TabColor { get; set; } + public IXLWorksheet SetTabColor(IXLColor color) { TabColor = color; @@ -987,40 +906,205 @@ } public Boolean TabSelected { get; set; } + public Boolean TabActive { - get { return m_tabActive; } + get { return _tabActive; } set { - if (value && !m_tabActive) + if (value && !_tabActive) { - foreach (var ws in Worksheet.Workbook.WorksheetsInternal) - { - ws.m_tabActive = false; - } + foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) + ws._tabActive = false; } - m_tabActive = value; + _tabActive = value; } } + public IXLWorksheet SetTabSelected() { TabSelected = true; return this; } + public IXLWorksheet SetTabSelected(Boolean value) { TabSelected = value; return this; } + public IXLWorksheet SetTabActive() { TabActive = true; return this; } + public IXLWorksheet SetTabActive(Boolean value) { TabActive = value; return this; } + + #endregion + + #region Outlines + + public void IncrementColumnOutline(Int32 level) + { + if (level > 0) + { + if (!_columnOutlineCount.ContainsKey(level)) + _columnOutlineCount.Add(level, 0); + + _columnOutlineCount[level]++; + } + } + + public void DecrementColumnOutline(Int32 level) + { + if (level > 0) + { + if (!_columnOutlineCount.ContainsKey(level)) + _columnOutlineCount.Add(level, 0); + + if (_columnOutlineCount[level] > 0) + _columnOutlineCount[level]--; + } + } + + public Int32 GetMaxColumnOutline() + { + if (_columnOutlineCount.Count == 0) + return 0; + return _columnOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); + } + + public void IncrementRowOutline(Int32 level) + { + if (level > 0) + { + if (!_rowOutlineCount.ContainsKey(level)) + _rowOutlineCount.Add(level, 0); + + _rowOutlineCount[level]++; + } + } + + public void DecrementRowOutline(Int32 level) + { + if (level > 0) + { + if (!_rowOutlineCount.ContainsKey(level)) + _rowOutlineCount.Add(level, 0); + + if (_rowOutlineCount[level] > 0) + _rowOutlineCount[level]--; + } + } + + public Int32 GetMaxRowOutline() + { + if (_rowOutlineCount.Count == 0) + return 0; + return _rowOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); + } + + #endregion + + private void XLWorksheet_RangeShiftedColumns(XLRange range, int columnsShifted) + { + var newMerge = new XLRanges(); + foreach (IXLRange rngMerged in Internals.MergedRanges) + { + 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 newRng = Range( + rngMerged.RangeAddress.FirstAddress.RowNumber, + rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + rngMerged.RangeAddress.LastAddress.RowNumber, + rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); + newMerge.Add(newRng); + } + else if ( + !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber + && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) + newMerge.Add(rngMerged); + } + Internals.MergedRanges = newMerge; + } + + private void XLWorksheet_RangeShiftedRows(XLRange range, int rowsShifted) + { + var newMerge = new XLRanges(); + foreach (IXLRange rngMerged in Internals.MergedRanges) + { + 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 newRng = Range( + rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted, + rngMerged.RangeAddress.FirstAddress.ColumnNumber, + rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, + rngMerged.RangeAddress.LastAddress.ColumnNumber); + newMerge.Add(newRng); + } + else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber + && + range.RangeAddress.FirstAddress.ColumnNumber <= + rngMerged.RangeAddress.LastAddress.ColumnNumber)) + newMerge.Add(rngMerged); + } + Internals.MergedRanges = newMerge; + } + + public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted) + { + if (RangeShiftedRows != null) + RangeShiftedRows(range, rowsShifted); + } + + public void NotifyRangeShiftedColumns(XLRange range, Int32 columnsShifted) + { + if (RangeShiftedColumns != null) + RangeShiftedColumns(range, columnsShifted); + } + + public XLRow Row(Int32 row, Boolean pingCells) + { + IXLStyle styleToUse; + if (Internals.RowsCollection.ContainsKey(row)) + styleToUse = Internals.RowsCollection[row].Style; + else + { + if (pingCells) + { + // This is a new row so we're going to reference all + // cells in columns of this row to preserve their formatting + + 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) + select dc; + + usedColumns.ForEach(c => Cell(row, c)); + } + styleToUse = Style; + Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, styleToUse, false))); + } + + return new XLRow(row, new XLRowParameters(this, styleToUse, true)); + } + + private IXLRange GetRangeForSort() + { + var range = RangeUsed(); + SortColumns.ForEach(e => range.SortColumns.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); + SortRows.ForEach(e => range.SortRows.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); + return range; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index c52bf84..a55c68d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -1,9 +1,9 @@ namespace ClosedXML.Excel { - internal class XLWorksheetInternals: IXLWorksheetInternals + internal class XLWorksheetInternals { public XLWorksheetInternals( - XLCellCollection cellsCollection, + XLCellsCollection cellsCollection, XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, XLRanges mergedRanges, @@ -17,7 +17,7 @@ Workbook = workbook; } - public XLCellCollection CellsCollection { get; private set; } + public XLCellsCollection CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public XLRanges MergedRanges { get; internal set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 7bdbdc2..34094f3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -74,7 +74,7 @@ { var sheet = new XLWorksheet(sheetName, m_workbook); m_worksheets.Add(sheetName, sheet); - sheet.m_position = m_worksheets.Count; + sheet._position = m_worksheets.Count; return sheet; } @@ -110,7 +110,7 @@ } m_worksheets.RemoveAll(w => w.Position == position); - m_worksheets.Values.Where(w => w.Position > position).ForEach(w => (w).m_position -= 1); + m_worksheets.Values.Where(w => w.Position > position).ForEach(w => (w)._position -= 1); } #endregion #region IEnumerable Members diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index a226bbd..5a5d255 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -182,5 +182,12 @@ } return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); } + + public static T[,] ResizeArray(T[,] original, int rows, int cols) + { + var newArray = new T[rows, cols]; + Array.Copy(original, newArray, original.Length); + return newArray; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 639e3df..1a3b8a1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -76,6 +76,7 @@ new AddingDataSet().Create(@"C:\Excel Files\Created\AddingDataSet.xlsx"); new AddingDataTableAsWorksheet().Create(@"C:\Excel Files\Created\AddingDataTableAsWorksheet.xlsx"); new TabColors().Create(@"C:\Excel Files\Created\TabColors.xlsx"); + new ShiftingFormulas().Create(@"C:\Excel Files\Created\ShiftingFormulas.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 0465e56..f876d13 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -127,12 +127,15 @@ Excel\Cells\XLCell.cs - - Excel\Cells\XLCellCollection.cs + + Excel\Cells\XLCellCollectionOLD.cs Excel\Cells\XLCells.cs + + Excel\Cells\XLCellsCollection.cs + Excel\Charts\IXLChart.cs @@ -175,6 +178,12 @@ Excel\Coordinate\XLAddress.cs + + Excel\Coordinate\XLSheetPoint.cs + + + Excel\Coordinate\XLSheetRange.cs + Excel\CustomProperties\IXLCustomProperties.cs @@ -250,9 +259,6 @@ Excel\IXLWorksheet.cs - - Excel\IXLWorksheetInternals.cs - Excel\IXLWorksheets.cs