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 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);
}
}