Insert and delete rows, columns and ranges

From version 5 EPPlus can insert and delete rows/columns in a worksheet. Formulas/references in the workbook affected by the change will be adjusted, EPPlus does this for formulas, styles, tables, pivot tables, data validations, filters, conditional formatting, sparklines and drawings.

Insert columns

We start by creating a workbook, and add a sheet with some numbers and formulas

using var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Sheet 1");
// FillNumber will add 1, 2, 3, etc in each cell of the range
sheet.Cells["A1:A5"].FillNumber(x => x.StartValue = 1);
// Add two more columns with shared formula that refers to eachother.
sheet.Cells["B1:B5"].Formula = "A$1:A$5 + 1";
sheet.Cells["C1:C5"].Formula = "B$1:B$5 + 1";
sheet.Cells["A1:C5"].Style.Fill.SetBackground(Color.LightYellow);

Now we can use the Insert method to insert a column.

sheet.Cells["B1"].Insert(eShiftTypeInsert.EntireColumn);

As you can see EPPlus has inserted a new column B and moved the former B and C columns to the right. The formula in cell D1 (that previously referred to column B1) has been shifted so it still refer to its neighboring column. The styling (lightyellow background) has also been extended to the right.

Insert multiple columns

sheet.Cells["A1:C1"].Insert(eShiftTypeInsert.EntireColumn);

Insert rows

Works as insert columns, but you use eShiftTypeInsert.EntireRow instead.

sheet.Cells["A2"].Insert(eShiftTypeInsert.EntireRow);

Delete columns

This is similar to the example in insert columns, but we add an extra column B that we will delete.

using var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Sheet 1");
sheet.Cells["A1:A5"].FillNumber(x => x.StartValue = 1);
sheet.Cells["B1:B5"].FillNumber(x => x.StartValue = 1);
sheet.Cells["C1:C5"].Formula = "A$1:A$5 + 1";
sheet.Cells["D1:D5"].Formula = "C$1:C$5 + 1";
sheet.Cells["A1:D5"].Style.Fill.SetBackground(Color.LightYellow);

Now let's remove column B.

sheet.Cells["B1"].Delete(eShiftTypeDelete.EntireColumn);

Column B has now been deleted from the worksheet and the former columns C and D has been moved left. The formulas in column C (previously column D) has been shifted so they still refer to its neighboring column.