public void InsertDeleteTest() { var addr = new ExcelAddressBase("A1:B3"); Assert.AreEqual(addr.AddRow(2, 4).Address, "A1:B7"); Assert.AreEqual(addr.AddColumn(2, 4).Address, "A1:F3"); Assert.AreEqual(addr.DeleteColumn(2, 1).Address, "A1:A3"); Assert.AreEqual(addr.DeleteRow(2, 2).Address, "A1:B1"); Assert.AreEqual(addr.DeleteRow(1, 3), null); Assert.AreEqual(addr.DeleteColumn(1, 2), null); }
/// <summary> /// Shifts all comments based on their address and the location of inserted rows and columns. /// </summary> /// <param name="fromRow">The start row.</param> /// <param name="fromCol">The start column.</param> /// <param name="rows">The number of rows to insert.</param> /// <param name="columns">The number of columns to insert.</param> /// <param name="toRow">If the delete is in a range, this is the end row</param> /// <param name="toCol">If the delete is in a range, this the end column</param> internal void Delete(int fromRow, int fromCol, int rows, int columns, int toRow = ExcelPackage.MaxRows, int toCol = ExcelPackage.MaxColumns) { List <ExcelThreadedCommentThread> deletedComments = new List <ExcelThreadedCommentThread>(); foreach (var threadedComment in _threads.Where(x => x != null)) { var address = new ExcelAddressBase(threadedComment.CellAddress.Address); if (columns > 0 && address._fromCol >= fromCol && address._fromRow >= fromRow && address._toRow <= toRow) { address = address.DeleteColumn(fromCol, columns); } if (rows > 0 && address._fromRow >= fromRow && address._fromCol >= fromCol && address._toCol <= toCol) { address = address.DeleteRow(fromRow, rows); } if (address == null || address.Address == "#REF!") { deletedComments.Add(threadedComment); } else { threadedComment.CellAddress = new ExcelCellAddress(address.Address); } } foreach (var comment in deletedComments) { comment.Comments.TopNode.ParentNode.RemoveChild(comment.Comments.TopNode); var ix = _threads.IndexOf(comment); _threadsIndex.Remove(ix); _threads[ix] = null; } }
private static ExcelAddressBase ShiftAddress(ExcelAddressBase address, ExcelAddressBase range, eShiftTypeDelete shift) { if (shift == eShiftTypeDelete.Up) { return(address.DeleteRow(range._fromRow, range.Rows)); } else { return(address.DeleteColumn(range._fromCol, range.Columns)); } }
private static ExcelAddressBase DeleteSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift) { if (address.CollideFullRowOrColumn(range)) { if (range.CollideFullColumn(address._fromCol, address._toCol)) { return(address.DeleteColumn(range._fromCol, range.Columns)); } else { return(address.DeleteRow(range._fromRow, range.Rows)); } } else { var collide = effectedAddress.Collide(address); if (collide == ExcelAddressBase.eAddressCollition.Partly) { var addressToShift = effectedAddress.Intersect(address); var shiftedAddress = ShiftAddress(addressToShift, range, shift); var newAddress = ""; if (address._fromRow < addressToShift._fromRow) { newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ","; } if (address._fromCol < addressToShift._fromCol) { var fromRow = Math.Max(address._fromRow, addressToShift._fromRow); newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ","; } if (shiftedAddress != null) { newAddress += $"{shiftedAddress.Address},"; } if (address._toRow > addressToShift._toRow) { newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ","; } if (address._toCol > addressToShift._toCol) { newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ","; } return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } else if (collide != ExcelAddressBase.eAddressCollition.No) { return(ShiftAddress(address, range, shift)); } } return(address); }
internal static void FixMergedCellsColumn(ExcelWorksheet ws, int column, int columns, bool delete, int fromRow = 1, int toRow = ExcelPackage.MaxRows) { if (delete) { ws._mergedCells._cells.Delete(0, column, 0, columns); } else { ws._mergedCells._cells.Insert(0, column, 0, columns); } List <int> removeIndex = new List <int>(); for (int i = 0; i < ws._mergedCells.Count; i++) { if (!string.IsNullOrEmpty(ws._mergedCells[i])) { ExcelAddressBase addr = new ExcelAddressBase(ws._mergedCells[i]), newAddr; if (delete) { newAddr = addr.DeleteColumn(column, columns); if (newAddr == null) { removeIndex.Add(i); continue; } } else { newAddr = addr.AddColumn(column, columns); if (newAddr.Address != addr.Address) { ws._mergedCells.SetIndex(newAddr, i); } } if (newAddr.Address != addr.Address) { ws._mergedCells._list[i] = newAddr._address; } } } for (int i = removeIndex.Count - 1; i >= 0; i--) { ws._mergedCells._list.RemoveAt(removeIndex[i]); } }