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