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