CellRange
public sealed class CellRange
Namespace SharpCell · package SharpCell
A rectangle of cells on one sheet, such as A1:C10. Like a Cell, it is a handle: creating one allocates no cells.
Properties
Address
public string Address { get; }
The A1 address without the sheet name: A1:C10, or B2 for one cell.
Cells
public IEnumerable<Cell> Cells { get; }
The cells of the range that hold something (a constant, a formula or a value spilled into them), row by row and left to right. Only existing cells are visited, so A:A is cheap. Values and formulas of the cells may be changed while enumerating; adding or removing a cell of the sheet makes the next step throw InvalidOperationException.
FirstColumn
public int FirstColumn { get; }
The first column, from 1 (A is 1).
FirstRow
public int FirstRow { get; }
The first row, from 1.
LastColumn
public int LastColumn { get; }
The last column, from 1.
LastRow
public int LastRow { get; }
The last row, from 1.
Worksheet
public Worksheet Worksheet { get; }
The sheet the range is on.
Methods
CopyTo(CellRange)
public CellRange CopyTo(CellRange destination)
Copies the range's cells to another range, as copying and pasting them in Excel does: values, formulas, number formats, styles, hyperlinks, notes, data validation and conditional formats. A formula's relative references keep their distance from its cell, so =A1*2 in B1 becomes =A2*2 in B2, and a reference that would point past the sheet's edge becomes #REF!. Empty cells of the range clear the cells they are copied to. When the destination is a whole number of times as tall and as wide as the range, the range repeats to fill it; otherwise it is copied once, with its top-left cell at the destination's. The cells are read before any is written, so the ranges may overlap. The destination can be in another workbook: formulas then refer to that workbook's sheets and names. A cell filled by a formula's spill is copied as its value, unless that formula is copied too and spills again. Data validation formulas keep their distance from each cell as cell formulas do. Merged cells, column widths and row heights are not copied. Every formula reading the cells written is calculated again at the next Workbook.Recalculate.
Parameters
destination: Where to copy the cells to.
Returns: The cells written to.
Exceptions
ArgumentException: The copy would not fit on the sheet.InvalidOperationException: A cell written to is part of an array formula other than its top-left cell.NotSupportedException: The range holds part of an array formula (Ctrl+Shift+Enter), or a formula SharpCell cannot read (such as a link to another workbook), in a cell or a data validation rule, whose references could not be moved.
Delete(DeleteShift)
public void Delete(DeleteShift shift)
Deletes the range's cells, as Excel's Delete with "Shift cells up" or "Shift cells left" does: the cells below (or to the right) in the range's columns (or rows) move into their place. A reference to a deleted cell becomes #REF!; otherwise references follow as with CellRange.Insert. A range of whole rows (or columns) deletes them.
Parameters
shift: Which cells take the place of the deleted ones.
Exceptions
ArgumentException:shiftis not a defined value.InvalidOperationException: The cells that move would split a merged area, a table or an array formula, or a table would lose its header, totals or data rows.NotSupportedException: A formula on the sheet, or one naming it, cannot be read by SharpCell; or some of a table's columns would be deleted.
Equals(CellRange)
public bool Equals(CellRange? other)
Whether the other range is on the same sheet with the same corners.
Equals(object)
public override bool Equals(object? obj)
Whether obj is a range on the same sheet with the same corners.
GetHashCode()
public override int GetHashCode()
A hash code consistent with CellRange.Equals.
GetValues()
public CellValue[,] GetValues()
The value of every cell, empty ones included, indexed [row, column] from the range's top-left cell. A whole column allocates an element for each of its 1,048,576 cells; use Worksheet.UsedRange or CellRange.Cells for sparse data.
Exceptions
InvalidOperationException: The range has more cells than an array can hold.
Insert(InsertShift)
public void Insert(InsertShift shift)
Inserts empty cells in place of the range, as Excel's Insert with "Shift cells down" or "Shift cells right" does: only the cells in the range's columns (or rows) move, along with their styles, number formats, hyperlinks, notes, data validation, merged areas and tables, and every reference to them follows. A reference to a range that only some of the moved cells are in stays as it was, as in Excel; data validation over such a range is split. A range of whole rows (or whole columns) inserts rows (or columns), as Worksheet.InsertRows does. Every formula is calculated again at the next Workbook.Recalculate.
Parameters
shift: Where the cells beside the range go.
Exceptions
ArgumentException:shiftis not a defined value.InvalidOperationException: Content would be pushed off the sheet, or the cells that move would split a merged area, a table or an array formula.NotSupportedException: A formula on the sheet, or one naming it, cannot be read by SharpCell, so its references could not follow; or cells would be inserted inside a table's columns.
SetValues(CellValue[,])
public void SetValues(CellValue[,] values)
Sets the value of every cell, as assigning Cell.Value would: formulas are replaced and CellValue.Empty clears a cell. Everything is checked before the first cell is written, so an exception leaves the sheet unchanged; a cell of an array formula other than its top-left cell is refused even when the range covers the whole array.
Parameters
values: Values indexed[row, column], exactly as large as the range.
Exceptions
ArgumentException: The array's size differs from the range's, or it holds a Missing, array or lambda value.InvalidOperationException: A cell is part of an array formula other than its top-left cell.
ToString()
public override string ToString()
The address with the sheet name as a formula writes it, such as Sheet1!A1:C10 or 'My Sheet'!B2.