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