-
Notifications
You must be signed in to change notification settings - Fork 275
Insert and delete rows, columns and ranges
From version 5 EPPlus can insert and delete rows, columns and ranges 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.
If you need to refresh the cell values after the insert/delete operation you can call the Calculate()
method.
- Insert entire columns
- Insert entire rows
- Delete entire columns
- Delete entire rows
- Insert ranges/cells
- Delete ranges/cells
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.
sheet.Cells["A1:C1"].Insert(eShiftTypeInsert.EntireColumn);
Works as insert columns, but you use eShiftTypeInsert.EntireRow
instead.
sheet.Cells["A2"].Insert(eShiftTypeInsert.EntireRow);
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.
Works as delete columns, but you use eShiftTypeInsert.EntireRow
instead.
sheet.Cells["A2"].Delete(eShiftTypeDelete.EntireRow);
To insert specfic cell/range you use the Insert
function, but use eShiftTypeInsert.Down
or eShiftTypeDelete.Right
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);
sheet.Cells["B2:C2"].Insert(eShiftTypeInsert.Down);
Since the two rows affected by the insertion have been shifted a row "down" the formula in C6 now refers to A$1:A$5 via implicit intersection, which generates a #VALUE! error
To delete a specfic cell/range you use the Delete
function, but use eShiftTypeDelete.Up
or eShiftTypeDelete.Left
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);
sheet.Cells["B2:C2"].Delete(eShiftTypeDelete.Up);
Since the row affected by the deletion has been shifted a row "up" the formula in D5 now refers to C$1:C$4 via implicit intersection, which generates a #VALUE! error
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles