Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

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.

[C#] 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. The class exposes two properties that indicate the value of the property for the cell range:

  • 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 IsIndetermine 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

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.

[C#] 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.

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. The Value property of the RangePropertyValue instance returns the actual value of the selected region.

Example 3 illustrates who to retrieve the value of cell B2.

[C#] 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.

[C#] 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.

[C#] 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

Rad Spread Processing Working With Cells Get Set Clear Properties 01

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.

[C#] 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

Rad Spread Processing Working With Cells Get Set Clear Properties 02

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.

[C#] 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

Rad Spread Processing Working With Cells Get Set Clear Properties 03

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.

[C#] 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();

See Also