private static void InsertSparkLinesAddress(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
{
foreach (var slg in range.Worksheet.SparklineGroups)
{
if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside)
{
string address;
if (shift == eShiftTypeInsert.Down)
{
address = slg.DateAxisRange.AddRow(range._fromRow, range.Rows).Address;
}
else
{
address = slg.DateAxisRange.AddColumn(range._fromCol, range.Columns).Address;
}
slg.DateAxisRange = range.Worksheet.Cells[address];
}
foreach (var sl in slg.Sparklines)
{
if (shift == eShiftTypeInsert.Down)
{
if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside ||
range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow))
{
sl.RangeAddress = sl.RangeAddress.AddRow(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.AddColumn(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);
}
}
}
}
}
internal static void ValidateIfInsertDeleteIsPossible(ExcelRangeBase range, ExcelAddressBase effectedAddress, ExcelAddressBase effectedAddressTable, bool insert)
{
//Validate autofilter
if (range.Worksheet.AutoFilterAddress != null &&
effectedAddress.Collide(range.Worksheet.AutoFilterAddress) == ExcelAddressBase.eAddressCollition.Partly
&&
range.Worksheet.AutoFilterAddress.CollideFullRowOrColumn(range) == false)
{
throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with the worksheets autofilter.");
}
//Validate merged Cells
foreach (var a in range.Worksheet.MergedCells)
{
var mc = new ExcelAddressBase(a);
if (effectedAddress.Collide(mc) == ExcelAddressBase.eAddressCollition.Partly)
{
throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with merged range {a}");
}
}
//Validate pivot tables Cells
foreach (var pt in range.Worksheet.PivotTables)
{
if (effectedAddress.Collide(pt.Address) == ExcelAddressBase.eAddressCollition.Partly)
{
throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with pivot table {pt.Name}");
}
}
//Validate tables Cells
foreach (var t in range.Worksheet.Tables)
{
if (effectedAddressTable.Collide(t.Address) == ExcelAddressBase.eAddressCollition.Partly
&&
t.Address.CollideFullRowOrColumn(range) == false)
{
throw new InvalidOperationException($"Can't {(insert ? "insert into" : "delete from")} the range. Cells collide with table {t.Name}");
}
}
}
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 void InsertFilterAddress(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
{
var ws = range.Worksheet;
if (ws.AutoFilterAddress != null && effectedAddress.Collide(ws.AutoFilterAddress) != ExcelAddressBase.eAddressCollition.No)
{
if (shift == eShiftTypeInsert.Down)
{
ws.AutoFilterAddress = ws.AutoFilterAddress.AddRow(range._fromRow, range.Rows);
}
else
{
ws.AutoFilterAddress = ws.AutoFilterAddress.AddColumn(range._fromCol, range.Columns);
}
}
}
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 bool ConvertEffectedSharedFormulaIfReferenceWithinRange(ExcelWorksheet ws, ExcelAddressBase delRange, ExcelWorksheet.Formulas sf, string wsName)
{
bool doConvertSF = false;
var sfAddress = new ExcelAddressBase(sf.Address);
sf.SetTokens(ws.Name);
foreach (var token in sf.Tokens)
{
if (token.TokenTypeIsSet(TokenType.ExcelAddress))
{
//Check if the address for the entire shared formula collides with the deleted address.
var tokenAddress = new ExcelAddressBase(token.Value);
if ((ws.Name.Equals(wsName, StringComparison.CurrentCultureIgnoreCase) && string.IsNullOrEmpty(tokenAddress.WorkSheetName)) ||
(!string.IsNullOrEmpty(tokenAddress.WorkSheetName) && tokenAddress.WorkSheetName.Equals(wsName, StringComparison.CurrentCultureIgnoreCase)))
{
if (tokenAddress._toRowFixed == false)
{
tokenAddress._toRow += (sfAddress.Rows - 1);
}
if (tokenAddress._toColFixed == false)
{
tokenAddress._toCol += (sfAddress.Columns - 1);
}
if (tokenAddress.Collide(delRange, true) != ExcelAddressBase.eAddressCollition.No) //Shared Formula address is effected.
{
doConvertSF = true;
break;
}
}
}
}
if (doConvertSF)
{
ConvertSharedFormulaToCellFormula(ws, sf, sfAddress);
}
return(doConvertSF);
}
private static ExcelAddressBase InsertSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
{
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);
}
private static void AdjustFormulasInsert(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
{
//Adjust formulas
foreach (var ws in range._workbook.Worksheets)
{
var workSheetName = range.Worksheet.Name;
var rowFrom = range._fromRow;
var columnFrom = range._fromCol;
var rows = range.Rows;
foreach (var f in ws._sharedFormulas.Values)
{
if (workSheetName == ws.Name)
{
var a = new ExcelAddressBase(f.Address);
var c = effectedAddress.Collide(a);
if (c == ExcelAddressBase.eAddressCollition.Partly)
{
throw new Exception("Invalid shared formula"); //This should never happend!
}
if (f.StartCol >= columnFrom && c != ExcelAddressBase.eAddressCollition.No)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
if (a._fromRow >= rowFrom)
{
a._fromRow += rows;
a._toRow += rows;
}
else if (a._toRow >= rowFrom)
{
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
else if (f.Formula.Contains(workSheetName))
{
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string v)
{
if (workSheetName == ws.Name)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
}
else if (v.Contains(workSheetName))
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
}
}
}
}
}
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 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));
}
