Insert and Remove Cells
Worksheets in the document model consist of cells organized in rows and columns. Each worksheet allows you to insert and remove cells through shifting the contents of the surrounding cells in a specified direction. This article demonstrates how to insert and remove cells.
Insert Cells
In order to insert cells, you need to create a CellSelection instance that indicates where the new cells are to be inserted in the worksheet. Also, you have to specify the direction of the shift.
Whenever cells insertion is performed, all values that appear to the right or below the CellSelection region including the selected region are shifted. There are two shift directions: right and down. When you choose to shift cells to the right, all cells that appear to the right of the selected region including the selected region are shifted, thus, causing no loss of data. Similarly, choosing to shift the values down moves all values in the selected region columns downwards and expands the UsedCellRange.
The CellSelection class exposes an Insert() method that takes one argument which indicates the direction of the shift. Also, the class offers a CanInsertOrRemove() method that determines if the insertion is possible. Example 1 shows how to insert cells using methods mentioned above.
Example 1: Insert cells
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellRange range = new CellRange(1, 1, 10, 10);
CellSelection selection = worksheet.Cells[range];
if (selection.CanInsertOrRemove(range, ShiftType.Right))
{
selection.Insert(InsertShiftType.Right);
}
Remove Cells
In order to remove cells, you need to create a CellSelection object that indicates the region of cells you would like to remove. Also, you have to specify the direction of the shift.
Whenever you remove cells, all values that appear to the right or below the CellSelection region are shifted. There are two shift directions: left and up. When you choose to shift cells to the left, all cells that appear to the right of the selected region аре shifted to the left. Similarly, choosing to shift the values up moves all values in the selected region columns upwards.
The CellSelection class exposes a Remove() method that takes one argument which indicates the direction of the shift. Тhe class also offers a CanInsertOrRemove() method that determines if the removal is possible. Example 2 shows how to remove cells using methods mentioned above.
Example 2: Remove cells
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellRange range = new CellRange(1, 1, 10, 10);
CellSelection selection = worksheet.Cells[range];
if (selection.CanInsertOrRemove(range, ShiftType.Up))
{
selection.Remove(RemoveShiftType.Up);
}