/// <summary> /// Inserts one or more rows before the specified position in the table. /// </summary> /// <param name="position">The position in the table where the row will be inserted. Default is in the end of the table. 0 will insert the row at the top. Any value larger than the number of rows in the table will insert a row at the bottom of the table.</param> /// <param name="rows">Number of rows to insert.</param> /// <returns>The inserted range</returns> public ExcelRangeBase InsertRow(int position, int rows = 1) { if (position < 0) { throw new ArgumentException("position", "position can't be negative"); } if (rows < 0) { throw new ArgumentException("position", "rows can't be negative"); } var subtact = ShowTotal ? 2 : 1; if (position >= ExcelPackage.MaxRows || position > _address._fromRow + position + rows - subtact) { position = _address.Rows - subtact; } if (_address._fromRow + position + rows > ExcelPackage.MaxRows) { throw new InvalidOperationException("Insert will exceed the maximum number of rows in the worksheet"); } position++; var address = ExcelCellBase.GetAddress(_address._fromRow + position, _address._fromCol, _address._fromRow + position + rows - 1, _address._toCol); var range = new ExcelRangeBase(WorkSheet, address); WorksheetRangeInsertHelper.Insert(range, eShiftTypeInsert.Down, false); if (range._toRow > _address._toRow) { Address = _address.AddRow(_address._toRow, rows); } return(range); }
private static ExcelAddressBase ShiftAddress(ExcelAddressBase address, ExcelAddressBase range, eShiftTypeInsert shift) { if (shift == eShiftTypeInsert.Down) { return(address.AddRow(range._fromRow, range.Rows)); } else { return(address.AddColumn(range._fromCol, range.Columns)); } }
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); }
internal static void FixMergedCellsRow(ExcelWorksheet ws, int row, int rows, bool delete, int fromCol = 1, int toCol = ExcelPackage.MaxColumns) { if (delete) { ws._mergedCells._cells.Delete(row, 0, rows, ExcelPackage.MaxColumns + 1); } else { ws._mergedCells._cells.Insert(row, 0, rows, ExcelPackage.MaxColumns + 1); } 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 (addr._fromCol >= fromCol && addr._toCol <= toCol) { if (delete) { newAddr = addr.DeleteRow(row, rows); if (newAddr == null) { removeIndex.Add(i); continue; } } else { newAddr = addr.AddRow(row, rows); 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]); } }
private static ExcelAddressBase InsertSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift) { if (address.CollideFullRowOrColumn(range)) { if (range.CollideFullColumn(address._fromCol, address._toCol)) { return(address.AddColumn(range._fromCol, range.Columns)); } else { return(address.AddRow(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) + ","; } newAddress += $"{shiftedAddress},"; 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); }
/// <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 insert is in a range, this is the end row</param> /// <param name="toCol">If the insert is in a range, this the end column</param> internal void Insert(int fromRow, int fromCol, int rows, int columns, int toRow = ExcelPackage.MaxRows, int toCol = ExcelPackage.MaxColumns) { foreach (var comment in _threads.Where(x => x != null)) { var address = new ExcelAddressBase(comment.CellAddress.Address); if (rows > 0 && address._fromRow >= fromRow && address._fromCol >= fromCol && address._toCol <= toCol) { comment.CellAddress = new ExcelCellAddress(address.AddRow(fromRow, rows).Address); } if (columns > 0 && address._fromCol >= fromCol && address._fromRow >= fromRow && address._toRow <= toRow) { comment.CellAddress = new ExcelCellAddress(address.AddColumn(fromCol, columns).Address); } } }