diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 1338dad..01a9538 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1958,14 +1958,15 @@ } - public IXLCell CopyFrom(XLCell otherCell, Boolean copyDataValidations) + public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations) { - var source = otherCell; - CopyValues(otherCell); + var castedOtherCell = otherCell as XLCell; // To expose GetFormulaR1C1, etc + var source = castedOtherCell; + CopyValues(castedOtherCell); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); - var conditionalFormats = otherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(otherCell)).ToList(); + var conditionalFormats = castedOtherCell.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(castedOtherCell)).ToList(); foreach (var cf in conditionalFormats) { var c = new XLConditionalFormat(cf as XLConditionalFormat) {Range = AsRange()}; @@ -1976,7 +1977,7 @@ var f = v.Value; if (v.IsFormula) { - var r1c1 = otherCell.GetFormulaR1C1(f); + var r1c1 = castedOtherCell.GetFormulaR1C1(f); f = GetFormulaA1(r1c1); } @@ -1991,8 +1992,8 @@ { var eventTracking = Worksheet.EventTrackingEnabled; Worksheet.EventTrackingEnabled = false; - if (otherCell.HasDataValidation) - CopyDataValidation(otherCell, otherCell.DataValidation); + if (castedOtherCell.HasDataValidation) + CopyDataValidation(castedOtherCell, castedOtherCell.DataValidation); else if (HasDataValidation) { using (var asRange = AsRange()) diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index c713895..0e68171 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -127,7 +127,7 @@ { asRange.Delete(XLShiftDeletedCells.ShiftCellsLeft); } - + Worksheet.Internals.ColumnsCollection.Remove(columnNumber); var columnsToMove = new List(); columnsToMove.AddRange( @@ -161,7 +161,15 @@ public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Cells(FirstCellUsed().Address.RowNumber, LastCellUsed().Address.RowNumber); } public IXLCells Cells(Int32 firstRow, Int32 lastRow) @@ -763,4 +771,4 @@ return base.IsEmpty(includeFormats); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index d86698a..de5fa11 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -6,7 +6,7 @@ { Workbook, Worksheet - } + } public interface IXLRangeBase: IDisposable { @@ -70,6 +70,10 @@ /// IXLCells Cells(); + IXLCells Cells(Boolean usedCellsOnly); + + IXLCells Cells(Boolean usedCellsOnly, Boolean includeFormats); + IXLCells Cells(String cells); IXLCells Cells(Func predicate); @@ -189,7 +193,7 @@ IXLRange Merge(Boolean checkIntersect); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// The default scope for the named range is Workbook. /// @@ -197,7 +201,7 @@ IXLRange AddToNamed(String rangeName); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. @@ -205,7 +209,7 @@ IXLRange AddToNamed(String rangeName, XLScope scope); /// - /// Creates a named range out of this range. + /// Creates a named range out of this range. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. @@ -218,7 +222,7 @@ /// /// Specify what you want to clear. IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); - + /// /// Deletes the cell comments from this range. /// @@ -251,4 +255,4 @@ void Select(); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index ddb94bf..3484ed6 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -101,7 +101,7 @@ { var newRanges = new XLRanges { AsRange() }; var dataValidation = new XLDataValidation(newRanges); - + Worksheet.DataValidations.Add(dataValidation); return dataValidation; } @@ -305,7 +305,17 @@ public IXLCells Cells() { - var cells = new XLCells(false, false) {RangeAddress}; + return Cells(false); + } + + public IXLCells Cells(Boolean usedCellsOnly) + { + return Cells(usedCellsOnly, false); + } + + public IXLCells Cells(Boolean usedCellsOnly, Boolean includeFormats) + { + var cells = new XLCells(usedCellsOnly, includeFormats) { RangeAddress }; return cells; } @@ -322,13 +332,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(true, false) {RangeAddress}; - return cells; - } - - IXLCells IXLRangeBase.CellsUsed(Boolean includeFormats) - { - return CellsUsed(includeFormats); + return Cells(true); } public IXLRange Merge() @@ -370,7 +374,7 @@ clearOptions == XLClearOptions.ContentsAndFormats; foreach (var cell in CellsUsed(includeFormats)) { - cell.Clear(clearOptions, true); + (cell as XLCell).Clear(clearOptions, true); } if (includeFormats) @@ -456,7 +460,7 @@ return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); } - + public IXLRange AddToNamed(String rangeName) { @@ -502,8 +506,8 @@ public Boolean IsEmpty(Boolean includeFormats) { - return !CellsUsed(includeFormats).Any() || - CellsUsed(includeFormats).Any(c => c.IsEmpty(includeFormats)); + return !CellsUsed(includeFormats).Cast().Any() || + CellsUsed(includeFormats).Cast().Any(c => c.IsEmpty(includeFormats)); } #endregion @@ -632,8 +636,8 @@ } } - - if (sp.Row > 0) + + if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); return null; @@ -724,7 +728,7 @@ } } - + if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); @@ -838,7 +842,7 @@ { var newFirstCellAddress = firstCell.Address as XLAddress; var newLastCellAddress = lastCell.Address as XLAddress; - + return GetRange(newFirstCellAddress, newLastCellAddress); } @@ -942,7 +946,7 @@ return address; } - public XLCells CellsUsed(bool includeFormats) + public IXLCells CellsUsed(bool includeFormats) { var cells = new XLCells(true, includeFormats) {RangeAddress}; return cells; @@ -1176,13 +1180,13 @@ : Worksheet.Style; rangeToReturn.Row(ro).Style = styleToUse; } - + } } if(nullReturn) return null; - + return rangeToReturn.Columns(); } @@ -1318,7 +1322,7 @@ newCell.CopyValues(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); - cellsToDelete.Add(oldKey); + cellsToDelete.Add(oldKey); } } } @@ -1346,7 +1350,7 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - + } } @@ -1363,7 +1367,7 @@ 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)); - + Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; Int32 lastRowReturn = RangeAddress.FirstAddress.RowNumber + numberOfRows - 1; @@ -1702,7 +1706,7 @@ return (XLPivotTable)Worksheet.PivotTables.AddNew(name, targetCell, AsRange()); } - public IXLAutoFilter SetAutoFilter() + public IXLAutoFilter SetAutoFilter() { using (var asRange = AsRange()) return Worksheet.AutoFilter.Set(asRange); diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs index c431481..b5447d5 100644 --- a/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/Excel/Rows/XLRow.cs @@ -224,7 +224,15 @@ public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Cells(FirstCellUsed().Address.ColumnNumber, LastCellUsed().Address.ColumnNumber); } public new IXLCells Cells(String cellsInRow) @@ -707,6 +715,6 @@ return base.IsEmpty(includeFormats); } - + } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index b8018fa..c622c30 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -17,8 +17,8 @@ #region Events - public XLReentrantEnumerableSet RangeShiftedRows; - public XLReentrantEnumerableSet RangeShiftedColumns; + public XLReentrantEnumerableSet RangeShiftedRows; + public XLReentrantEnumerableSet RangeShiftedColumns; #endregion @@ -45,8 +45,8 @@ { EventTrackingEnabled = workbook.EventTracking == XLEventTracking.Enabled; - RangeShiftedRows = new XLReentrantEnumerableSet(); - RangeShiftedColumns = new XLReentrantEnumerableSet(); + RangeShiftedRows = new XLReentrantEnumerableSet(); + RangeShiftedColumns = new XLReentrantEnumerableSet(); RangeAddress.Worksheet = this; RangeAddress.FirstAddress.Worksheet = this; @@ -70,9 +70,9 @@ _columnWidth = workbook.ColumnWidth; _rowHeight = workbook.RowHeight; RowHeightChanged = Math.Abs(workbook.RowHeight - XLWorkbook.DefaultRowHeight) > XLHelper.Epsilon; - Name = sheetName; - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); + Name = sheetName; + SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); Charts = new XLCharts(); ShowFormulas = workbook.ShowFormulas; ShowGridLines = workbook.ShowGridLines; @@ -1111,7 +1111,7 @@ Int32 thisStyleId = GetStyleId(); if (!Internals.ColumnsCollection.ContainsKey(column)) { - // This is a new row so we're going to reference all + // This is a new row so we're going to reference all // cells in this row to preserve their formatting Internals.RowsCollection.Keys.ForEach(r => Cell(r, column)); Internals.ColumnsCollection.Add(column, @@ -1348,7 +1348,7 @@ { if (pingCells) { - // This is a new row so we're going to reference all + // 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 @@ -1379,9 +1379,17 @@ return (XLPivotTable)PivotTables.PivotTable(name); } - public new XLCells Cells() + public new IXLCells Cells() { - return CellsUsed(true); + return Cells(true, true); + } + + public new IXLCells Cells(Boolean usedCellsOnly) + { + if (usedCellsOnly) + return Cells(true, true); + else + return Range(FirstCellUsed(), LastCellUsed()).Cells(false, true); } public new XLCell Cell(String cellAddressInRange) @@ -1396,11 +1404,11 @@ String.Compare(n.Name, cellAddressInRange, true) == 0 && n.Ranges.Count == 1); if (namedRanges == null || !namedRanges.Ranges.Any()) return null; - + return (XLCell)namedRanges.Ranges.First().FirstCell(); } - public XLCell CellFast(String cellAddressInRange) + internal XLCell CellFast(String cellAddressInRange) { return Cell(XLAddress.Create(this, cellAddressInRange)); } @@ -1438,7 +1446,7 @@ { EventTrackingEnabled = _eventTracking; } - + public IXLRanges SelectedRanges { get; internal set; } public IXLCell ActiveCell { get; set; } @@ -1456,4 +1464,4 @@ public String Author { get; set; } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index cd9b3c0..9e4e024 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -77,6 +77,7 @@ + diff --git a/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs new file mode 100644 index 0000000..a096c33 --- /dev/null +++ b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs @@ -0,0 +1,133 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML_Tests.Excel.Ranges +{ + [TestFixture] + public class UsedAndUnusedCellsTests + { + private XLWorkbook workbook; + + [OneTimeSetUp] + public void SetupWorkbook() + { + workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = "A1"; + ws.Cell(1, 3).Value = "C1"; + ws.Cell(2, 2).Value = "B2"; + ws.Cell(4, 1).Value = "A4"; + ws.Cell(5, 2).Value = "B5"; + } + + [Test] + public void CountUsedCellsInRow() + { + int i = 0; + var row = workbook.Worksheets.First().FirstRow(); + foreach (var cell in row.Cells()) // Cells() returns UnUsed cells by default + { + i++; + } + Assert.AreEqual(2, i); + + i = 0; + row = workbook.Worksheets.First().FirstRow().RowBelow(); + foreach (var cell in row.Cells()) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountAllCellsInRow() + { + int i = 0; + var row = workbook.Worksheets.First().FirstRow(); + foreach (var cell in row.Cells(false)) // All cells in range between first and last cells used + { + i++; + } + Assert.AreEqual(3, i); + + i = 0; + row = workbook.Worksheets.First().FirstRow().RowBelow(); //This row has no empty cells BETWEEN used cells + foreach (var cell in row.Cells(false)) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountUsedCellsInColumn() + { + int i = 0; + var column = workbook.Worksheets.First().FirstColumn(); + foreach (var cell in column.Cells()) // Cells() returns UnUsed cells by default + { + i++; + } + Assert.AreEqual(2, i); + + i = 0; + column = workbook.Worksheets.First().FirstColumn().ColumnRight().ColumnRight(); + foreach (var cell in column.Cells()) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountAllCellsInColumn() + { + int i = 0; + var column = workbook.Worksheets.First().FirstColumn(); + foreach (var cell in column.Cells(false)) // All cells in range between first and last cells used + { + i++; + } + Assert.AreEqual(4, i); + + i = 0; + column = workbook.Worksheets.First().FirstColumn().ColumnRight().ColumnRight(); //This column has no empty cells BETWEEN used cells + foreach (var cell in column.Cells(false)) + { + i++; + } + Assert.AreEqual(1, i); + } + + [Test] + public void CountUsedCellsInWorksheet() + { + var ws = workbook.Worksheets.First(); + int i = 0; + + foreach (var cell in ws.Cells()) // Only used cells in worksheet + { + i++; + } + Assert.AreEqual(5, i); + } + + [Test] + public void CountAllCellsInWorksheet() + { + var ws = workbook.Worksheets.First(); + int i = 0; + + foreach (var cell in ws.Cells(false)) // All cells in range between first and last cells used (cartesian product of range) + { + i++; + } + Assert.AreEqual(15, i); + } + } +}