Iterating Used Cells
This topic shows how you can access only the cells that are used in a worksheet and iterate them.
Working With The Whole Range of Used Cells
The Worksheet class enables you to obtain all the cells that are used. A cell is considered used when it has any property applied to it - not matter whether it will be a value or a foreground. The UsedCellRange property of the Worksheet class returns a cell range that starts from cell A1 and holds all cells containing data or formatting. Example 1 shows how to obtain this range of cells and iterate it.
Example 1: Iterate UsedCellRange
CellRange usedCellRange = worksheet.UsedCellRange;
for (int rowIndex = usedCellRange.FromIndex.RowIndex; rowIndex <= usedCellRange.ToIndex.RowIndex; rowIndex++)
{
for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
{
CellSelection cell = worksheet.Cells[rowIndex, columnIndex];
}
}Working With a Filtered Range
This section describes how you can obtain only the cells that have particular property applied to them and ignore the others. Often, the property that we are interested in is the value of a cell. With the GetUsedCellRange() method of Worksheet you can pass an IEnumerable<IPropertyDefinition> object to get the used cell range, holding only the cells with specific property definitions. Example 2 demonstrates how to get the used cell range of cells with value and iterate it to process each value.
Example 2: Obtain and iterate a filtered UsedCellRange
CellRange usedCellRangeWithValues = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
for (int row = usedCellRangeWithValues.FromIndex.RowIndex; row <= usedCellRangeWithValues.ToIndex.RowIndex; row++)
{
for (int column = usedCellRangeWithValues.FromIndex.ColumnIndex; column <= usedCellRangeWithValues.ToIndex.ColumnIndex; column++)
{
ICellValue value = worksheet.Cells[row, column].GetValue().Value;
}
}For more information on working with the values of the cells, check the Get, Set and Clear Cell Properties and Cell Value Types topics.
The following table lists the property definitions defined by the CellPropertyDefinitions class, which you can use to filter the used cell range:
| Property | Description |
|---|---|
AllPropertyDefinitions | Gets all property definitions. Same behavior as UsedCellRange — no filtering is applied. |
BottomBorderProperty | Gets all cells that have a defined bottom border. |
TopBorderProperty | Gets all cells that have a defined top border. |
LeftBorderProperty | Gets all cells that have a defined left border. |
RightBorderProperty | Gets all cells that have a defined right border. |
DiagonalDownBorderProperty | Gets all cells that have a defined diagonal down border. |
DiagonalUpBorderProperty | Gets all cells that have a defined diagonal up border. |
DataValidationRuleProperty | Gets all cells that have a data validation rule applied. |
FillProperty | Gets all cells that have a defined fill color. |
FontFamilyProperty | Gets all cells that have a font family applied. |
FontSizeProperty | Gets all cells that have a font size applied. |
ForeColorProperty | Gets all cells that have a fore color applied. |
FormatProperty | Gets all cells that have a specific number format defined. |
HorizontalAlignmentProperty | Gets all cells that have horizontal alignment applied. |
VerticalAlignmentProperty | Gets all cells that have vertical alignment applied. |
IndentProperty | Gets all cells that have indentation applied. |
IsBoldProperty | Gets all cells that are bold. |
IsItalicProperty | Gets all cells that have italic font style applied. |
IsLockedProperty | Gets all cells that are locked and will be protected when protection is applied. |
IsWrappedProperty | Gets all cells that have text wrapping applied. |
StyleNameProperty | Gets all cells that have a style applied. |
UnderlineProperty | Gets all cells that have underlining applied. |
ValueProperty | Gets all cells that have a value. |