diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 0dd21de..f622f4a 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1927,7 +1927,7 @@ return columnPart; } - internal void CopyValues(XLCell source) + internal void CopyValuesFrom(XLCell source) { _cellValue = source._cellValue; _dataType = source._dataType; @@ -1960,7 +1960,7 @@ { var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc //var source = castedOtherCell; - CopyValues(source); + CopyValuesFrom(source); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); diff --git a/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/Excel/Comments/XLComment.cs index 83f5846..87cae88 100644 --- a/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/Excel/Comments/XLComment.cs @@ -164,7 +164,7 @@ if (pRow > 1) { pRow--; - double prevHeight = cell.CellAbove().WorksheetRow().Height; + double prevHeight = cell.Worksheet.Row(pRow).Height; if (prevHeight > 7) pRowOffset = prevHeight - 7; } @@ -204,4 +204,4 @@ ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 60d12d4..8251bcd 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -58,25 +58,25 @@ #endregion - private XLCallbackAction _shiftedRowsAction; + private XLCallbackAction _shiftedRowsAction; protected void SubscribeToShiftedRows(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; - _shiftedRowsAction = new XLCallbackAction(action); + _shiftedRowsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); + RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); } private XLCallbackAction _shiftedColumnsAction; - protected void SubscribeToShiftedColumns(Action action) + protected void SubscribeToShiftedColumns(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; _shiftedColumnsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); + RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); } #region Public properties @@ -178,7 +178,7 @@ dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); - var newRanges = new XLRanges {AsRange()}; + var newRanges = new XLRanges { AsRange() }; var dataValidation = new XLDataValidation(newRanges); if (dataValidationToCopy != null) dataValidation.CopyFrom(dataValidationToCopy); @@ -262,7 +262,7 @@ { get { - var retVal = new XLRanges {AsRange()}; + var retVal = new XLRanges { AsRange() }; return retVal; } } @@ -394,7 +394,8 @@ return this; } - public void DeleteComments() { + public void DeleteComments() + { Cells().DeleteComments(); } @@ -427,7 +428,7 @@ public bool Intersects(string rangeAddress) { - using (var range = Worksheet.Range(rangeAddress)) + using (var range = Worksheet.Range(rangeAddress)) return Intersects(range); } @@ -948,7 +949,7 @@ public IXLCells CellsUsed(bool includeFormats) { - var cells = new XLCells(true, includeFormats) {RangeAddress}; + var cells = new XLCells(true, includeFormats) { RangeAddress }; return cells; } @@ -991,14 +992,14 @@ } public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + { + return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsAfterInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1045,15 +1046,15 @@ return retVal; } - public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsBeforeInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1088,7 +1089,7 @@ Worksheet.Cell(oldKey); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1106,12 +1107,12 @@ int newColumn = c.Address.ColumnNumber + numberOfColumns; var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1135,8 +1136,8 @@ //cellsDataValidations.ForEach(kp => Worksheet.Cell(kp.Key).CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; - Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber ; - Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber ; + Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber; + Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); @@ -1184,8 +1185,8 @@ } } - if(nullReturn) - return null; + if (nullReturn) + return null; return rangeToReturn.Columns(); } @@ -1216,18 +1217,18 @@ return retVal; } - public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); - } + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); + } - public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); - } + public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); + } - private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { int rowCount = RowCount(); int firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; if (firstRow > XLHelper.MaxRowNumber) @@ -1276,22 +1277,22 @@ public XLAddress SourceAddress; public XLDataValidation DataValidation; } - public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); - } - public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); - } + public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); + } + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); + } - private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { using (var asRange = AsRange()) foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) - cell.ShiftFormulaRows(asRange, numberOfRows); + cell.ShiftFormulaRows(asRange, numberOfRows); } var cellsToInsert = new Dictionary(); @@ -1319,7 +1320,7 @@ if (oldCell != null) { var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1339,12 +1340,12 @@ int newRow = c.Address.RowNumber + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1354,19 +1355,19 @@ } } - cellsDataValidations.ForEach(kp => - { - XLCell targetCell; - if(!cellsToInsert.TryGetValue(kp.Key, out targetCell)) - targetCell = Worksheet.Cell(kp.Key); + cellsDataValidations + .ForEach(kp => + { + XLCell targetCell; + if (!cellsToInsert.TryGetValue(kp.Key, out targetCell)) + targetCell = Worksheet.Cell(kp.Key); - targetCell.CopyDataValidation( - Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); - }); + targetCell.CopyDataValidation( + Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); + }); 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)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; @@ -1418,9 +1419,9 @@ } } - // Skip calling .Rows() for performance reasons if required. - if(nullReturn) - return null; + // Skip calling .Rows() for performance reasons if required. + if (nullReturn) + return null; return rangeToReturn.Rows(); } @@ -1498,7 +1499,7 @@ c.Address.ColumnNumber - columnModifier, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); newCell.FormulaA1 = c.FormulaA1; cellsToDelete.Add(c.Address); @@ -1709,7 +1710,7 @@ public IXLAutoFilter SetAutoFilter() { using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return Worksheet.AutoFilter.Set(asRange); } #region Sort @@ -1976,17 +1977,17 @@ public void Dispose() { - if(_shiftedRowsAction != null) + if (_shiftedRowsAction != null) { RangeAddress.Worksheet.RangeShiftedRows.Remove(_shiftedRowsAction); - _shiftedRowsAction = null; + _shiftedRowsAction = null; } - if(_shiftedColumnsAction != null) - { - RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); - _shiftedColumnsAction = null; - } + if (_shiftedColumnsAction != null) + { + RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); + _shiftedColumnsAction = null; + } } public IXLDataValidation SetDataValidation() diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index dcda221..a614b55 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -81,5 +81,33 @@ Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor); } + + [Test] + public void InsertingRowsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert Below"); + ws.Cell("A2").SetValue("Already existing cell"); + ws.Cell("A3").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Row(1).InsertRowsBelow(2); + Assert.AreEqual("Comment here", ws.Cell("A5").Comment.Text); + } + + [Test] + public void InsertingColumnsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert to the right"); + ws.Cell("B1").SetValue("Already existing cell"); + ws.Cell("C1").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Column(1).InsertColumnsAfter(2); + Assert.AreEqual("Comment here", ws.Cell("E1").Comment.Text); + } } -} \ No newline at end of file +}