private static void ValidateInsert(ExcelRangeBase range, eShiftTypeInsert shift)
{
if (range == null || (range.Addresses != null && range.Addresses.Count > 1))
{
throw new ArgumentException("Can't insert into range. ´range´ can't be null or have multiple addresses.", "range");
}
}
private static List <int> GetStylesForRange(ExcelRangeBase range, eShiftTypeInsert shift)
{
var list = new List <int>();
if (shift == eShiftTypeInsert.Down)
{
for (int i = 0; i < range.Columns; i++)
{
if (range._fromRow == 1)
{
list.Add(0);
}
else
{
list.Add(range.Offset(-1, i).StyleID);
}
}
}
else
{
for (int i = 0; i < range.Rows; i++)
{
if (range._fromCol == 1)
{
list.Add(0);
}
else
{
list.Add(range.Offset(i, -1).StyleID);
}
}
}
return(list);
}
internal static void Insert(ExcelRangeBase range, eShiftTypeInsert shift, bool styleCopy)
{
ValidateInsert(range, shift);
var effectedAddress = GetEffectedRange(range, shift);
WorksheetRangeHelper.ValidateIfInsertDeleteIsPossible(range, effectedAddress, GetEffectedRange(range, shift, 1), true);
var ws = range.Worksheet;
lock (ws)
{
var styleList = GetStylesForRange(range, shift);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
InsertCellStores(range._worksheet, range._fromRow, range._fromCol, range.Rows, range.Columns, range._toCol);
}
else
{
InsertCellStoreShiftRight(range._worksheet, range);
}
AdjustFormulasInsert(range, effectedAddress, shift);
InsertFilterAddress(range, effectedAddress, shift);
WorksheetRangeHelper.FixMergedCells(ws, range, shift);
if (styleCopy)
{
SetStylesForRange(range, shift, styleList);
}
InsertTableAddress(ws, range, shift, effectedAddress);
InsertPivottableAddress(ws, range, shift, effectedAddress);
//Update data validation references
foreach (var dv in ws.DataValidations)
{
((ExcelDataValidation)dv).SetAddress(InsertSplitAddress(dv.Address, range, effectedAddress, shift).Address);
}
//Update Conditional formatting references
foreach (var cf in ws.ConditionalFormatting)
{
((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(InsertSplitAddress(cf.Address, range, effectedAddress, shift).Address);
}
InsertSparkLinesAddress(range, shift, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
WorksheetRangeHelper.AdjustDrawingsRow(ws, range._fromRow, range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(ws, range._fromCol, range.Columns, range._fromRow, range._toRow);
}
}
}
private static void InsertDataValidation(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws)
{
//Update data validation references
foreach (var dv in ws.DataValidations)
{
((ExcelDataValidation)dv).SetAddress(InsertSplitAddress(dv.Address, range, effectedAddress, shift).Address);
}
}
private static void InsertConditionalFormatting(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws)
{
//Update Conditional formatting references
foreach (var cf in ws.ConditionalFormatting)
{
((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(InsertSplitAddress(cf.Address, range, effectedAddress, shift).Address);
}
}
internal static void FixMergedCells(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift)
{
if (shift == eShiftTypeInsert.Down)
{
FixMergedCellsRow(ws, range._fromRow, range.Rows, false, range._fromCol, range._toCol);
}
else
{
FixMergedCellsColumn(ws, range._fromCol, range.Columns, false, range._fromRow, range._toRow);
}
}
internal static void Insert(ExcelRangeBase range, eShiftTypeInsert shift, bool styleCopy)
{
ValidateInsert(range, shift);
var effectedAddress = GetEffectedRange(range, shift);
WorksheetRangeHelper.ValidateIfInsertDeleteIsPossible(range, effectedAddress, GetEffectedRange(range, shift, 1), true);
var ws = range.Worksheet;
lock (ws)
{
var styleList = GetStylesForRange(range, shift);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
InsertCellStores(range._worksheet, range._fromRow, range._fromCol, range.Rows, range.Columns, range._toCol);
}
else
{
InsertCellStoreShiftRight(range._worksheet, range);
}
AdjustFormulasInsert(range, effectedAddress, shift);
InsertFilterAddress(range, effectedAddress, shift);
WorksheetRangeHelper.FixMergedCells(ws, range, shift);
if (styleCopy)
{
SetStylesForRange(range, shift, styleList);
}
InsertTableAddress(ws, range, shift, effectedAddress);
InsertPivottableAddress(ws, range, shift, effectedAddress);
InsertDataValidation(range, shift, effectedAddress, ws);
InsertConditionalFormatting(range, shift, effectedAddress, ws);
InsertSparkLinesAddress(range, shift, effectedAddress);
if (shift == eShiftTypeInsert.Down)
{
WorksheetRangeHelper.AdjustDrawingsRow(ws, range._fromRow, range.Rows, range._fromCol, range._toCol);
}
else
{
WorksheetRangeHelper.AdjustDrawingsColumn(ws, range._fromCol, range.Columns, range._fromRow, range._toRow);
}
}
}
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);
}
}
}
}
}
private static void SetStylesForRange(ExcelRangeBase range, eShiftTypeInsert shift, List <int> list)
{
if (shift == eShiftTypeInsert.Down)
{
for (int i = 0; i < range.Columns; i++)
{
range.Offset(0, i, range.Rows, 1).StyleID = list[i];
}
}
else
{
for (int i = 0; i < range.Rows; i++)
{
range.Offset(i, 0, 1, range.Columns).StyleID = list[i];
}
}
}
private static ExcelAddressBase GetEffectedRange(ExcelRangeBase range, eShiftTypeInsert shift, int?start = null)
{
if (shift == eShiftTypeInsert.Down)
{
return(new ExcelAddressBase(start ?? range._fromRow, range._fromCol, ExcelPackage.MaxRows, range._toCol));
}
else if (shift == eShiftTypeInsert.Right)
{
return(new ExcelAddressBase(range._fromRow, start ?? range._fromCol, range._toRow, ExcelPackage.MaxColumns));
}
else if (shift == eShiftTypeInsert.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 InsertTableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
{
foreach (var tbl in ws.Tables)
{
if (shift == eShiftTypeInsert.Down)
{
if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol)
{
tbl.Address = tbl.Address.AddRow(range._fromRow, range.Rows);
}
}
else
{
if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow)
{
tbl.Address = tbl.Address.AddColumn(range._fromCol, range.Columns);
}
}
}
}
private static void InsertPivottableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
{
foreach (var ptbl in ws.PivotTables)
{
if (shift == eShiftTypeInsert.Down)
{
if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol)
{
ptbl.Address = ptbl.Address.AddRow(range._fromRow, range.Rows);
}
}
else
{
if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow)
{
ptbl.Address = ptbl.Address.AddColumn(range._fromCol, range.Columns);
}
}
if (ptbl.CacheDefinition.SourceRange.Worksheet == ws)
{
var address = ptbl.CacheDefinition.SourceRange;
if (shift == eShiftTypeInsert.Down)
{
if (address._fromCol >= range._fromCol && address._toCol <= range._toCol)
{
ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddRow(range._fromRow, range.Rows).Address];
}
}
else
{
if (address._fromRow >= range._fromRow && address._toRow <= range._toRow)
{
ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddColumn(range._fromCol, range.Columns).Address];
}
}
}
}
}
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));
}
}
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 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 ExcelAddressBase InsertSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
{
if (address.Addresses == null)
{
return(InsertSplitIndividualAddress(address, range, effectedAddress, shift));
}
else
{
var newAddress = "";
foreach (var a in address.Addresses)
{
newAddress += InsertSplitIndividualAddress(a, range, effectedAddress, shift) + ",";
}
return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
}
}
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);
}
}
}
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="formula">The Excel formula</param>
/// <param name="range">The range that is inserted</param>
/// <param name="effectedRange">The range effected by the insert</param>
/// <param name="shift">Shift operation</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <returns>The updated version of the <paramref name="formula"/>.</returns>
internal static string UpdateFormulaReferences(string formula, ExcelAddressBase range, ExcelAddressBase effectedRange, eShiftTypeInsert shift, string currentSheet, string modifiedSheet, bool setFixed = false)
{
int rowIncrement;
int colIncrement;
if (shift == eShiftTypeInsert.Down || shift == eShiftTypeInsert.EntireRow)
{
rowIncrement = range.Rows;
colIncrement = 0;
}
else
{
colIncrement = range.Columns;
rowIncrement = 0;
}
return(UpdateFormulaReferncesPrivate(formula, range, effectedRange, currentSheet, modifiedSheet, setFixed, rowIncrement, colIncrement));
}
