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