Get, Set and Clear Cell Properties
Cells are the atomic parts of a worksheet and its basic data units. Each cell can be assigned a value, borders, fill, format, style and much more. This article aims to describe the properties offered by cells and demonstrate how to retrieve and change them. It contains the following sections:
Get, Set and Clear Methods
In order to access cell properties, you have to create a CellSelection object that contains the region of cells you would like to change. More information about retrieving CellSelection instances is available in the Accessing Cells of a Worksheet article.
Example 1 creates a selection for cells in the range A1:F6.
Example 1: Create CellSelection
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[0, 0, 5, 5];
Once you have a CellSelection instance, you can easily set and retrieve the properties of its cells. Each property is manipulated through three methods that get, set and clear the value of the property, respectively. Typically, the set methods take a single argument, which indicates the value to be set. Similarly, the clear methods have no parameters and reset the properties to their default values. The get methods, however, require more attention.
With one minor exception, the get methods of all cell properties return an object of type RangePropertyValue
IsIndeterminate: Indicates whether the value of the retrieved property is consistent among all cells in the specified CellSelection. If the property has one and the same value for all cells, IsIndeterminate is set to false. However, if the value of the retrieved property varies throughout the cells in the CellSelection, the IsIndeterminate property is set to true and the Value property of the RangePropertyValue
class is set to its default value. Value: Contains the value of the retrieved property. If the IsIndeterminate property is set to false, Value contains the value of the retrieved property for the whole CellSelection region. If the IsIndeterminate property is set to true, the Value property is set to its default value.
Cell Properties
Cells in RadSpreadProcessing offer a number of properties that allow you to change their content and appearance. The following list outlines all cell properties:
Value
Border
Fill
FontFamily
FontSize
ForeColor
Format
HorizontalAlignment
Indent
IsBold
IsItalic
IsWrapped
StyleName
Underline
VerticalAlignment
IsLocked
TextRotation
As already mentioned, the CellSelection class exposes methods that get, set and clear methods for each of the above properties. The names of the methods are constructed through the concatenation of the action the method executes (Get, Set, Clear) and the name of the property. For example, the methods that get, set and clear the IsBold property are respectively, GetIsBold(), SetIsBold() and ClearIsBold().
Example 2 illustrates how to use these methods on the region A1:F6.
Example 2: Use GetIsBold(), SetIsBold() and ClearIsBold() methods
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[0, 0, 5, 5];
selection.SetIsBold(true);
bool isBold = selection.GetIsBold().Value;
selection.ClearIsBold();
Using the above approach you can set the value of almost all cell properties. There are a few exceptions to the general get, set and clear rule, though, and each of them is described into one of the following sections.
When using GetFontSize() and SetFontSize() methods you have to keep in mind that measurement units used in RadSpreadProcessing are Device Independent Pixels (DIPs). For more information go to Measurement Units help topic.
Value Property
The Value property uses an instance of ICellValue to retrieve and change its value. The property has support for the following types of cell values, all of which conform to the ICellValue interface: EmptyCellValue, NumberCellValue, BooleanCellValue, TextCellValue, FormulaCellValue. Similarly to the other properties, Value has three methods that control the property: GetValue(), SetValue() and ClearValue(). More information about different value types is available in the Cell Value Types article.
The GetValue() method retrieves the value of the property and returns an instance of RangePropertyValue
Example 3 illustrates who to retrieve the value of cell B2.
Example 3: Retrieve value of cell
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[1, 1];
ICellValue cellValue = selection.GetValue().Value;
As the document model supports different types of cell values, the CellSelection class offers multiple overloads of the SetValue() method that allow you to produce different types of values. For example, if you choose the method that accepts a double instance, the Value of the cell will be an instance of NumberCellValue. The SetValue() method has three more overloads that take DateTime, string and ICellValue, respectively.
Example 4 demonstrates how to set the value of a given selection.
Example 4: Set value of CellSelection
// set DateTime value
selection.SetValue(DateTime.Now);
// set double value
selection.SetValue(51.345);
// set ICellValue
ICellValue value = worksheet.Cells[5, 5].GetValue().Value;
selection.SetValue(value);
// set string value
selection.SetValue("Total");
// set formula value
selection.SetValue("=C1+C10");
Borders Property
The Borders property uses a CellBorders object for getting and setting its property value. The CellBorders class contains eight instances of type CellBorder that describe respectively the left, top, right, bottom, inside horizontal, inside vertical, diagonal up, and diagonal down borders. In turn, the CellBorder object holds information about the style and color of the border. The GetBorders() method returns an instance of RangePropertyValue
Example 5 demonstrates how to set the value of the Borders of the regions B2:C4 and E2:F4.
Example 5: Set value of Borders
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ThemableColor purple = new ThemableColor(Color.FromArgb(255, 155, 89, 182));
ThemableColor darkBlue = new ThemableColor(Color.FromArgb(255, 44, 62, 80));
CellBorders purpleBorders = new CellBorders(new CellBorder(CellBorderStyle.Dotted, purple));
worksheet.Cells[1, 1, 2, 2].SetBorders(purpleBorders);
CellBorders darkBlueBorders = new CellBorders(
new CellBorder(CellBorderStyle.Medium, darkBlue), // Left border
new CellBorder(CellBorderStyle.Medium, darkBlue), // Top border
new CellBorder(CellBorderStyle.Medium, darkBlue), // Right border
new CellBorder(CellBorderStyle.Medium, darkBlue), // Bottom border
new CellBorder(CellBorderStyle.Thin, purple), // Inside horizontal border
new CellBorder(CellBorderStyle.Thin, purple), // Inside vertical border
new CellBorder(CellBorderStyle.None, darkBlue), // Diagonal up border
new CellBorder(CellBorderStyle.None, darkBlue)); // Diagonal down border
worksheet.Cells[1, 4, 2, 5].SetBorders(darkBlueBorders);
The result of Example 5 is demonstrated in Figure 1.
Figure 1: Resulting Borders
Fill Property
The Fill property uses an IFill object for getting and setting its property value. The document model supports two types of fills that are represented through the PatternFill and GradientFill classes, both of which conform to the IFill interface.
As its name suggests, the PatternFill object is used to fill the background of a region of cells using a repeated pattern of shapes. To create a PatternFill instance, you need to specify the type of the pattern, the background color and pattern color of the fill. You can choose between eighteen types of patterns, such as HorizontalStripe, DiagonalCrossHatch, Gray75Percent and many more. The PatternFill object also allows you to set the background of a cell to a solid color.
Example 6 creates two PatternFill objects with a DiagonalStripe and Solid PatternType respectively.
Example 6: Create and set PatternFill
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
PatternFill diagonalStripePatternFill = new PatternFill(PatternType.DiagonalStripe, Color.FromArgb(255, 231, 76, 60), Color.FromArgb(255, 241, 196, 15));
worksheet.Cells[0, 0, 0, 5].SetFill(diagonalStripePatternFill);
PatternFill solidPatternFill = new PatternFill(PatternType.Solid, Color.FromArgb(255, 46, 204, 113), Colors.Transparent);
worksheet.Cells[1, 0, 5, 5].SetFill(solidPatternFill);
The result of Example 6 is illustrated in Figure 2.
Figure 2: Applied PatternFill
The GradientFill is used to set the background of a region of cells to a gradual blending of two colors. To create a GradientFill, you need to specify a GradientType and the two colors that will blend.
Example 7 assigns the region A1:F1 a smooth horizontal green gradient.
Example 7: Create and set GradientFill
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Rows[0].SetHeight(new RowHeight(50, true));
GradientFill greenGradientFill = new GradientFill(GradientType.Horizontal, Color.FromArgb(255, 46, 204, 113), Color.FromArgb(255, 0, 134, 56));
worksheet.Cells[0, 0, 0, 5].SetFill(greenGradientFill);
The result of Example 7 is illustrated in Figure 3.
Figure 3: Applied GradientFill
Indent Property
In addition to the GetIndent(), SetIndent() and ClearIndent() methods, CellSelection offers two more methods that are used to increase and decrease the value of the Indent property. Those methods are IncreaseIndent() and DecreaseIndent() and neither of them takes arguments. Example 8 snippet shows how to use the methods.
Example 8: Increase and decrease indent
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellSelection selection = worksheet.Cells[0, 0, 5, 5];
selection.IncreaseIndent();
selection.DecreaseIndent();