internal static void FixMergedCells(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift)
{
if (shift == eShiftTypeDelete.Up)
{
FixMergedCellsRow(ws, range._fromRow, range.Rows, true, range._fromCol, range._toCol);
}
else
{
FixMergedCellsColumn(ws, range._fromCol, range.Columns, true, range._fromRow, range._toRow);
}
}
private static void AdjustDrawings(ExcelRangeBase range, eShiftTypeDelete shift)
{
if (shift == eShiftTypeDelete.Up)
{
WorksheetRangeHelper.AdjustDrawingsRow(range.Worksheet, range._fromRow, -range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(range.Worksheet, range._fromCol, -range.Columns, range._fromRow, range._toRow);
}
}
private static void ValidateDelete(ExcelRangeBase range, eShiftTypeDelete shift)
{
if (range == null || (range.Addresses != null && range.Addresses.Count > 1))
{
throw new ArgumentException("Can't delete range. ´range´ can't be null or have multiple addresses.", "range");
}
if (shift == eShiftTypeDelete.Left)
{
ValidateColumn(range._fromCol, range.Columns);
}
else
{
ValidateRow(range._fromRow, range.Rows);
}
}
private static ExcelAddressBase GetEffectedRange(ExcelRangeBase range, eShiftTypeDelete shift, int?start = null)
{
if (shift == eShiftTypeDelete.Up)
{
return(new ExcelAddressBase(start ?? range._fromRow, range._fromCol, ExcelPackage.MaxRows, range._toCol));
}
else if (shift == eShiftTypeDelete.Left)
{
return(new ExcelAddressBase(range._fromRow, start ?? range._fromCol, range._toRow, ExcelPackage.MaxColumns));
}
else if (shift == eShiftTypeDelete.EntireColumn)
{
return(new ExcelAddressBase(1, range._fromCol, ExcelPackage.MaxRows, ExcelPackage.MaxColumns));
}
else
{
return(new ExcelAddressBase(range._fromRow, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns));
}
}
private static void DeleteDataValidations(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress)
{
//Update data validation references
var deletedDV = new List <IExcelDataValidation>();
foreach (var dv in ws.DataValidations)
{
var address = DeleteSplitAddress(dv.Address, range, effectedAddress, shift);
if (address == null)
{
deletedDV.Add(dv);
}
else
{
((ExcelDataValidation)dv).SetAddress(address.Address);
}
}
deletedDV.ForEach(dv => ws.DataValidations.Remove(dv));
}
private static void DeleteConditionalFormatting(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress)
{
//Update Conditional formatting references
var deletedCF = new List <IExcelConditionalFormattingRule>();
foreach (var cf in ws.ConditionalFormatting)
{
var address = DeleteSplitAddress(cf.Address, range, effectedAddress, shift);
if (address == null)
{
deletedCF.Add(cf);
}
else
{
((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(address.Address);
}
}
deletedCF.ForEach(cf => ws.ConditionalFormatting.Remove(cf));
}
internal static void Delete(ExcelRangeBase range, eShiftTypeDelete shift)
{
ValidateDelete(range, shift);
var effectedAddress = GetEffectedRange(range, shift);
WorksheetRangeHelper.ValidateIfInsertDeleteIsPossible(range, effectedAddress, GetEffectedRange(range, shift, 1), false);
var ws = range.Worksheet;
lock (ws)
{
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, effectedAddress);
if (shift == eShiftTypeDelete.Up)
{
DeleteCellStores(ws, range._fromRow, range._fromCol, range.Rows, range.Columns, range._toCol);
}
else
{
DeleteCellStoresShiftLeft(ws, range);
}
FixFormulasDelete(range, effectedAddress, shift);
WorksheetRangeHelper.FixMergedCells(ws, range, shift);
DeleteFilterAddress(range, effectedAddress, shift);
DeleteTableAddresses(ws, range, shift, effectedAddress);
DeletePivottableAddresses(ws, range, shift, effectedAddress);
//Adjust/delete data validations and conditional formatting
DeleteDataValidations(range, shift, ws, effectedAddress);
DeleteConditionalFormatting(range, shift, ws, effectedAddress);
DeleteSparkLinesAddress(range, shift, effectedAddress);
AdjustDrawings(range, shift);
}
}
private static void FixFormulasDelete(ExcelRangeBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift)
{
foreach (var ws in range.Worksheet.Workbook.Worksheets)
{
var workSheetName = range.WorkSheetName;
var rowFrom = range._fromRow;
var rows = range.Rows;
var delSF = new List <int>();
foreach (var sf in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
if (effectedRange.Collide(new ExcelAddressBase(sf.Address)) != ExcelAddressBase.eAddressCollition.No)
{
ExcelAddressBase a;
if (shift == eShiftTypeDelete.Up)
{
a = new ExcelAddress(sf.Address).DeleteRow(range._fromRow, rows);
}
else
{
a = new ExcelAddress(sf.Address).DeleteColumn(range._fromRow, rows);
}
if (a == null)
{
delSF.Add(sf.Index);
}
else
{
sf.Address = a.Address;
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, range, effectedRange, shift, ws.Name, workSheetName);
if (sf.StartRow >= rowFrom)
{
var r = Math.Max(rowFrom, sf.StartRow - rows);
sf.StartRow = r;
}
}
}
}
else if (sf.Formula.Contains(workSheetName))
{
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -rows, 0, rowFrom, 0, ws.Name, workSheetName);
}
}
foreach (var ix in delSF)
{
ws._sharedFormulas.Remove(ix);
}
var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name || v.IndexOf(workSheetName, StringComparison.CurrentCultureIgnoreCase) >= 0)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedRange, shift, ws.Name, workSheetName);
}
}
}
}
}
private static void DeleteTableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
{
var deletedTbl = new List <ExcelTable>();
foreach (var tbl in ws.Tables)
{
if (shift == eShiftTypeDelete.Up)
{
if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol)
{
tbl.Address = tbl.Address.DeleteRow(range._fromRow, range.Rows);
}
}
else
{
if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow)
{
tbl.Address = tbl.Address.DeleteColumn(range._fromCol, range.Columns);
}
}
if (tbl.Address == null)
{
deletedTbl.Add(tbl);
}
}
deletedTbl.ForEach(x => ws.Tables.Delete(x));
}
private static void DeletePivottableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
{
var deletedPt = new List <ExcelPivotTable>();
foreach (var ptbl in ws.PivotTables)
{
if (shift == eShiftTypeDelete.Up)
{
if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol)
{
ptbl.Address = ptbl.Address.DeleteRow(range._fromRow, range.Rows);
}
}
else
{
if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow)
{
ptbl.Address = ptbl.Address.DeleteColumn(range._fromCol, range.Columns);
}
}
if (ptbl.Address == null)
{
deletedPt.Add(ptbl);
}
else
{
foreach (var wsSource in ws.Workbook.Worksheets)
{
if (ptbl.CacheDefinition.SourceRange.Worksheet == wsSource)
{
var address = ptbl.CacheDefinition.SourceRange;
if (shift == eShiftTypeDelete.Up)
{
if (address._fromCol >= range._fromCol && address._toCol <= range._toCol)
{
var deletedRange = ws.Cells[address.DeleteRow(range._fromRow, range.Rows).Address];
if (deletedRange != null)
{
ptbl.CacheDefinition.SourceRange = deletedRange;
}
}
}
else
{
if (address._fromRow >= range._fromRow && address._toRow <= range._toRow)
{
var deletedRange = ws.Cells[address.DeleteColumn(range._fromCol, range.Columns).Address];
if (deletedRange != null)
{
ptbl.CacheDefinition.SourceRange = deletedRange;
}
}
}
}
}
}
}
deletedPt.ForEach(x => ws.PivotTables.Delete(x));
}
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);
}
private static ExcelAddressBase DeleteSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift)
{
if (address.Addresses == null)
{
return(DeleteSplitIndividualAddress(address, range, effectedAddress, shift));
}
else
{
var newAddress = "";
foreach (var a in address.Addresses)
{
var na = DeleteSplitIndividualAddress(a, range, effectedAddress, shift);
if (na != null)
{
newAddress += na.Address + ",";
}
}
if (string.IsNullOrEmpty(newAddress))
{
return(null);
}
else
{
return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
}
}
}
private static void DeleteFilterAddress(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift)
{
var ws = range.Worksheet;
if (ws.AutoFilterAddress != null && effectedAddress.Collide(ws.AutoFilterAddress) != ExcelAddressBase.eAddressCollition.No)
{
var firstRow = new ExcelAddress(ws.AutoFilterAddress._fromRow, ws.AutoFilterAddress._fromCol, ws.AutoFilterAddress._fromRow, ws.AutoFilterAddress._toCol);
if (range.Collide(firstRow, true) >= ExcelAddressBase.eAddressCollition.Inside)
{
ws.AutoFilterAddress = null;
}
else if (shift == eShiftTypeDelete.Up)
{
ws.AutoFilterAddress = ws.AutoFilterAddress.DeleteRow(range._fromRow, range.Rows);
}
else
{
ws.AutoFilterAddress = ws.AutoFilterAddress.DeleteColumn(range._fromCol, range.Columns);
}
}
}
private static void DeleteSparkLinesAddress(ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
{
var delSparklineGroups = new List <ExcelSparklineGroup>();
foreach (var slg in range.Worksheet.SparklineGroups)
{
if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside)
{
string address;
if (shift == eShiftTypeDelete.Up)
{
address = slg.DateAxisRange.DeleteRow(range._fromRow, range.Rows).Address;
}
else
{
address = slg.DateAxisRange.DeleteColumn(range._fromCol, range.Columns).Address;
}
slg.DateAxisRange = address == null? null : range.Worksheet.Cells[address];
}
var delSparklines = new List <ExcelSparkline>();
foreach (var sl in slg.Sparklines)
{
if (range.Collide(sl.Cell.Row, sl.Cell.Column) >= ExcelAddressBase.eAddressCollition.Inside)
{
delSparklines.Add(sl);
}
else if (shift == eShiftTypeDelete.Up)
{
if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside ||
range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow))
{
sl.RangeAddress = sl.RangeAddress.DeleteRow(range._fromRow, range.Rows);
}
if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol)
{
sl.Cell = new ExcelCellAddress(sl.Cell.Row - range.Rows, sl.Cell.Column);
}
}
else
{
if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside
||
range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol))
{
sl.RangeAddress = sl.RangeAddress.DeleteColumn(range._fromCol, range.Columns);
}
if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow)
{
sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column - range.Columns);
}
}
}
delSparklines.ForEach(x => slg.Sparklines.Remove(x));
if (slg.Sparklines.Count == 0)
{
delSparklineGroups.Add(slg);
}
}
delSparklineGroups.ForEach(x => range.Worksheet.SparklineGroups.Remove(x));
}
internal static string UpdateFormulaReferences(string formula, ExcelAddressBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift, string currentSheet, string modifiedSheet, bool setFixed = false)
{
int rowIncrement;
int colIncrement;
if (shift == eShiftTypeDelete.Up || shift == eShiftTypeDelete.EntireRow)
{
rowIncrement = -range.Rows;
colIncrement = 0;
}
else
{
colIncrement = -range.Columns;
rowIncrement = 0;
}
return(UpdateFormulaReferncesPrivate(formula, range, effectedRange, currentSheet, modifiedSheet, setFixed, rowIncrement, colIncrement));
}