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 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);
}
}
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 InsertRow(ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow)
{
ValidateInsertRow(ws, rowFrom, rows);
lock (ws)
{
InsertCellStores(ws, rowFrom, 0, rows, 0);
FixFormulasInsertRow(ws, rowFrom, rows);
WorksheetRangeHelper.FixMergedCellsRow(ws, rowFrom, rows, false);
if (copyStylesFromRow > 0)
{
CopyFromStyleRow(ws, rowFrom, rows, copyStylesFromRow);
}
foreach (var tbl in ws.Tables)
{
tbl.Address = tbl.Address.AddRow(rowFrom, rows);
}
foreach (var ptbl in ws.PivotTables)
{
ptbl.Address = ptbl.Address.AddRow(rowFrom, rows);
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddRow(rowFrom, rows).Address;
}
var range = ws.Cells[rowFrom, 1, rowFrom + rows - 1, ExcelPackage.MaxColumns];
var effectedAddress = GetEffectedRange(range, eShiftTypeInsert.Down);
InsertFilterAddress(range, effectedAddress, eShiftTypeInsert.Down);
InsertSparkLinesAddress(range, eShiftTypeInsert.Down, effectedAddress);
//Update data validation references
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
var newAddr = addr.AddRow(rowFrom, rows).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, rows);
}
}
internal static void InsertRow(ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow)
{
ValidateInsertRow(ws, rowFrom, rows);
lock (ws)
{
InsertCellStores(ws, rowFrom, 0, rows, 0);
//Adjust formulas
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasInsertRow(wsToUpdate, rowFrom, rows, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsRow(ws, rowFrom, rows, false);
if (copyStylesFromRow > 0)
{
CopyFromStyleRow(ws, rowFrom, rows, copyStylesFromRow);
}
foreach (var tbl in ws.Tables)
{
tbl.Address = tbl.Address.AddRow(rowFrom, rows);
}
foreach (var ptbl in ws.PivotTables)
{
ptbl.Address = ptbl.Address.AddRow(rowFrom, rows);
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddRow(rowFrom, rows).Address;
}
//Update data validation references
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
var newAddr = addr.AddRow(rowFrom, rows).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, rows);
}
}
internal static void DeleteRow(ExcelWorksheet ws, int rowFrom, int rows)
{
ws.CheckSheetType();
ValidateRow(rowFrom, rows);
lock (ws)
{
DeleteCellStores(ws, rowFrom, 0, rows, ExcelPackage.MaxColumns + 1, true);
AdjustFormulasRow(ws, rowFrom, rows);
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);
}
}
}
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, -rows);
}
}
internal static void InsertRow(ExcelWorksheet ws, int rowFrom, int rows, int copyStylesFromRow)
{
ValidateInsertRow(ws, rowFrom, rows);
lock (ws)
{
InsertCellStores(ws, rowFrom, 0, rows, 0);
foreach (var f in ws._sharedFormulas.Values)
{
if (f.StartRow >= rowFrom)
{
f.StartRow += rows;
}
var a = new ExcelAddressBase(f.Address);
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, rows, 0, rowFrom, 0, ws.Name, ws.Name);
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), rows, 0, rowFrom, 0, ws.Name, ws.Name);
}
}
WorksheetRangeHelper.FixMergedCellsRow(ws, rowFrom, rows, false);
if (copyStylesFromRow > 0)
{
CopyFromStyleRow(ws, rowFrom, rows, copyStylesFromRow);
}
foreach (var tbl in ws.Tables)
{
tbl.Address = tbl.Address.AddRow(rowFrom, rows);
}
foreach (var ptbl in ws.PivotTables)
{
ptbl.Address = ptbl.Address.AddRow(rowFrom, rows);
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddRow(rowFrom, rows).Address;
}
//Update data validation references
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
var newAddr = addr.AddRow(rowFrom, rows).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
// Update sheetname in cross reference formulas
foreach (var sheet in ws.Workbook.Worksheets.Where(otherWs => otherWs != ws))
{
sheet.UpdateSheetNameInFormulas(ws.Name, rowFrom, rows, 0, 0);
}
WorksheetRangeHelper.AdjustDrawingsRow(ws, rowFrom, rows);
}
}