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);
}
}
}
internal static void DeleteRow(ExcelWorksheet ws, int rowFrom, int rows)
{
ws.CheckSheetType();
ValidateRow(rowFrom, rows);
lock (ws)
{
var delRange = new ExcelAddressBase(rowFrom, 1, rowFrom + rows - 1, ExcelPackage.MaxColumns);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, rowFrom, 0, rows, ExcelPackage.MaxColumns + 1);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteRow(wsToUpdate, rowFrom, rows, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsRow(ws, rowFrom, rows, true);
foreach (var tbl in ws.Tables)
{
tbl.Address = tbl.Address.DeleteRow(rowFrom, rows);
}
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Row > rowFrom + rows)
{
ptbl.Address = ptbl.Address.DeleteRow(rowFrom, rows);
}
}
//Issue 15573
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
if (addr.Start.Row > rowFrom + rows)
{
var newAddr = addr.DeleteRow(rowFrom, rows).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
}
var range = ws.Cells[rowFrom, 1, rowFrom + rows - 1, ExcelPackage.MaxColumns];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Up);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Up);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Up, effectedAddress);
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, -rows);
}
}
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);
}
}
}
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);
}
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
AdjustColumnMinMaxDelete(ws, columnFrom, columns);
var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
}
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column >= columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
DeleteDataValidations(range, eShiftTypeDelete.Left, ws, effectedAddress);
DeleteConditionalFormatting(range, eShiftTypeDelete.Left, ws, effectedAddress);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
//Set previous column Max to Row before if it spans the deleted column range.
ExcelColumn col = ws.GetValueInner(0, columnFrom) as ExcelColumn;
if (col == null)
{
var r = 0;
var c = columnFrom;
if (ws._values.PrevCell(ref r, ref c))
{
col = ws.GetValueInner(0, c) as ExcelColumn;
if (col._columnMax >= columnFrom)
{
col.ColumnMax = columnFrom - 1;
}
}
}
var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column > columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
}
//Adjust DataValidation
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
if (addr.Start.Column > columnFrom + columns)
{
var newAddr = addr.DeleteColumn(columnFrom, columns).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}