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); }
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); }
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]); } }
/// <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); } } }
/// <summary> /// Inserts one or more columns before the specified position in the table. /// </summary> /// <param name="position">The position in the table where the column will be inserted. 0 will insert the column at the leftmost. Any value larger than the number of rows in the table will insert a row at the bottom of the table.</param> /// <param name="columns">Number of rows to insert.</param> /// <returns>The inserted range</returns> internal ExcelRangeBase InsertColumn(int position, int columns) { if (position < 0) { throw new ArgumentException("position", "position can't be negative"); } if (columns < 0) { throw new ArgumentException("columns", "columns can't be negative"); } if (position >= ExcelPackage.MaxColumns || position > _address._fromCol + position + columns - 1) { position = _address.Columns; } if (_address._fromCol + position + columns - 1 > ExcelPackage.MaxColumns) { throw new InvalidOperationException("Insert will exceed the maximum number of columns in the worksheet"); } var address = ExcelCellBase.GetAddress(_address._fromRow, _address._fromCol + position, _address._toRow, _address._fromCol + position + columns - 1); var range = new ExcelRangeBase(WorkSheet, address); WorksheetRangeInsertHelper.Insert(range, eShiftTypeInsert.Right, false); if (position == 0) { Address = new ExcelAddressBase(_address._fromRow, _address._fromCol - columns, _address._toRow, _address._toCol); } else if (range._toCol > _address._toCol) { Address = _address.AddColumn(_address._toCol, columns); } return(range); }