internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn) { ValidateInsertColumn(ws, columnFrom, columns); lock (ws) { InsertCellStores(ws, 0, columnFrom, 0, columns); FixFormulasInsertColumn(ws, columnFrom, columns); WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false); AdjustColumns(ws, columnFrom, columns); CopyStylesFromColumn(ws, columnFrom, columns, copyStylesFromColumn); //Adjust tables foreach (var tbl in ws.Tables) { if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) { InsertTableColumns(columnFrom, columns, tbl); } tbl.Address = tbl.Address.AddColumn(columnFrom, columns); } foreach (var ptbl in ws.PivotTables) { if (columnFrom <= ptbl.Address.End.Column) { ptbl.Address = ptbl.Address.AddColumn(columnFrom, columns); } if (columnFrom <= ptbl.CacheDefinition.SourceRange.End.Column) { if (ptbl.CacheDefinition.CacheSource == eSourceType.Worksheet) { ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddColumn(columnFrom, columns).Address; } } } var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1]; var effectedAddress = GetEffectedRange(range, eShiftTypeInsert.Right); InsertFilterAddress(range, effectedAddress, eShiftTypeInsert.Right); InsertSparkLinesAddress(range, eShiftTypeInsert.Right, effectedAddress); //Adjust DataValidation foreach (ExcelDataValidation dv in ws.DataValidations) { var addr = dv.Address; var newAddr = addr.AddColumn(columnFrom, columns).Address; if (addr.Address != newAddr) { dv.SetAddress(newAddr); } } //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns); } }
internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn) { ValidateInsertColumn(ws, columnFrom, columns); lock (ws) { InsertCellStores(ws, 0, columnFrom, 0, columns); foreach (var wsToUpdate in ws.Workbook.Worksheets) { FixFormulasInsertColumn(wsToUpdate, columnFrom, columns, ws.Name); } WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false); AdjustColumns(ws, columnFrom, columns); CopyStylesFromColumn(ws, columnFrom, columns, copyStylesFromColumn); //Adjust tables foreach (var tbl in ws.Tables) { if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) { InsertTableColumns(columnFrom, columns, tbl); } tbl.Address = tbl.Address.AddColumn(columnFrom, columns); } foreach (var ptbl in ws.PivotTables) { if (columnFrom <= ptbl.Address.End.Column) { ptbl.Address = ptbl.Address.AddColumn(columnFrom, columns); } if (columnFrom <= ptbl.CacheDefinition.SourceRange.End.Column) { if (ptbl.CacheDefinition.CacheSource == eSourceType.Worksheet) { ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddColumn(columnFrom, columns).Address; } } } //Adjust DataValidation foreach (ExcelDataValidation dv in ws.DataValidations) { var addr = dv.Address; var newAddr = addr.AddColumn(columnFrom, columns).Address; if (addr.Address != newAddr) { dv.SetAddress(newAddr); } } //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns); } }
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 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) { //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; } } } DeleteCellStores(ws, 0, columnFrom, 0, columns, true); AdjustFormulasColumn(ws, columnFrom, columns); 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); } } } //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns); } }
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); } }
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); } }
internal static void InsertColumn(ExcelWorksheet ws, int columnFrom, int columns, int copyStylesFromColumn) { ValidateInsertColumn(ws, columnFrom, columns); lock (ws) { InsertCellStores(ws, 0, columnFrom, 0, columns); InsertColumnFormulas(ws, columnFrom, columns); WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, false); var csec = new CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns); var lst = new List <ExcelColumn>(); foreach (var val in csec) { var col = val._value; if (col is ExcelColumn) { lst.Add((ExcelColumn)col); } } for (int i = lst.Count - 1; i >= 0; i--) { var c = lst[i]; if (c._columnMin >= columnFrom) { if (c._columnMin + columns <= ExcelPackage.MaxColumns) { c._columnMin += columns; } else { c._columnMin = ExcelPackage.MaxColumns; } if (c._columnMax + columns <= ExcelPackage.MaxColumns) { c._columnMax += columns; } else { c._columnMax = ExcelPackage.MaxColumns; } } else if (c._columnMax >= columnFrom) { var cc = c._columnMax - columnFrom; c._columnMax = columnFrom - 1; ws.CopyColumn(c, columnFrom + columns, columnFrom + columns + cc); } } //Copy style from another column? if (copyStylesFromColumn > 0) { if (copyStylesFromColumn >= columnFrom) { copyStylesFromColumn += columns; } //Get styles to a cached list, var l = new List <int[]>(); var sce = new CellStoreEnumerator <ExcelValue>(ws._values, 0, copyStylesFromColumn, ExcelPackage.MaxRows, copyStylesFromColumn); lock (sce) { while (sce.Next()) { if (sce.Value._styleId == 0) { continue; } l.Add(new int[] { sce.Row, sce.Value._styleId }); } } //Set the style id's from the list. foreach (var sc in l) { for (var c = 0; c < columns; c++) { if (sc[0] == 0) { var col = ws.Column(columnFrom + c); //Create the column col.StyleID = sc[1]; } else { ws.SetStyleInner(sc[0], columnFrom + c, sc[1]); } } } var newOutlineLevel = ws.Column(copyStylesFromColumn).OutlineLevel; for (var c = 0; c < columns; c++) { ws.Column(columnFrom + c).OutlineLevel = newOutlineLevel; } } //Adjust tables foreach (var tbl in ws.Tables) { if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) { InsertTableColumns(columnFrom, columns, tbl); } tbl.Address = tbl.Address.AddColumn(columnFrom, columns); } foreach (var ptbl in ws.PivotTables) { if (columnFrom <= ptbl.Address.End.Column) { ptbl.Address = ptbl.Address.AddColumn(columnFrom, columns); } if (columnFrom <= ptbl.CacheDefinition.SourceRange.End.Column) { if (ptbl.CacheDefinition.CacheSource == eSourceType.Worksheet) { ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.AddColumn(columnFrom, columns).Address; } } } //Adjust DataValidation foreach (ExcelDataValidation dv in ws.DataValidations) { var addr = dv.Address; var newAddr = addr.AddColumn(columnFrom, columns).Address; if (addr.Address != newAddr) { dv.SetAddress(newAddr); } } // Update cross-sheet references. foreach (var sheet in ws.Workbook.Worksheets.Where(otherSheet => otherSheet != ws)) { sheet.UpdateSheetNameInFormulas(ws.Name, 0, 0, columnFrom, columns); } //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, columns); } }