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);
}
