[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Collapse]Office File API
  Demo Application
  Redistribution and Deployment
 [Collapse]Spreadsheet Document API
   Product Structure
   Getting Started
  [Expand]Spreadsheet Document
   Supported Formats
  [Collapse]Cell Basics
    Cell Data Types
    Dates and Times in Cells
    Error Types
    Cell Referencing
    Formatting Cells
  [Expand]Formulas
   Defined Names
   Data Binding
  [Expand]Pivot Tables
  [Expand]Charts and Graphics
  [Expand]Mail Merge
  [Expand]Examples
 [Expand]Word Processing Document API
 [Expand]PDF Document API
 [Expand]Excel Export Library
 [Expand]Snap Report API
 [Expand]Zip Compression and Archive API
 [Expand]Barcode Generation API
 [Expand]Unit Conversion API
 [Expand]API Reference
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Formatting Cells

The proper formatting of worksheet cells improves document appearance and allows end-users to read, find and understand the data that it contains more easily. Cell formatting includes a variety of features such as multiple settings for font, font size, character style (bold, italics, underlined), text alignment, background and foreground colors, etc. This document explains such concepts as Cell Styles Direct Cell Formatting Style Flags and Rich Text Formatting and provides links to examples on how to format cells using the Spreadsheet Document API.

Expanded Cell Styles

A style is a named set of predefined cell format characteristics (font settings, number format, content alignment, cell borders, fill color, etc.). When applying a style, all format settings are applied to a cell or cell range in a single step.

A Workbook keeps all available styles in a StyleCollection collection, which is accessed via the Workbook.Styles property. By default, this collection contains a set of built-in cell styles similar to those found in Microsoft® Excel® (including the Normal style that is applied to all unformatted cells in the workbook by default). Identifiers of all built-in styles are listed by the BuiltInStyleId enumerator.

The image below shows the gallery of style in Microsoft® Excel®.

You can do the following to manage the workbook's collection of cell styles via the Spreadsheet Document API.

  • Modify an existing style by changing the properties of the corresponding Style object. Use the Formatting.BeginUpdate - Formatting.EndUpdate method pair to make multiple modifications to a style.
  • Create a new custom style by adding a new Style object to the IWorkbook.Styles collection (the StyleCollection.Add method). Note that new styles are created based on the Normal style by default.
  • Duplicate an existing style by creating a new style and copying all format settings from an existing style via the Style.CopyFrom method.

For examples, see the How to: Create or Modify a Style document.

Note

All custom styles of an Excel document that is loaded into the Workbook object are automatically added to the Workbook.Styles collection and can be accessed by their names.

To format a cell or cell range by applying a style, assign the required Style object to the Range.Style property. For details, see the How to: Apply a Style to a Cell or Range of Cells example.

Expanded Direct Cell Formatting

To change cell appearance, you can not only apply a style, but also set the required formatting characteristics for an individual cell or cell range directly. This is called direct cell formatting. In Microsoft® Excel®, direct cell formatting options are available via the Ribbon interface or in the Format Cells dialog.

To perform direct cell formatting via the Spreadsheet Document API, change the cell or cell range properties that are inherited from the Formatting interface (Formatting.Fill, Formatting.Font, Formatting.Alignment, Formatting.Borders and Formatting.NumberFormat). By default, these properties are set according to the style applied to a cell. Use the following approaches.

Thus, a Cell or Range object's properties inherited from the Formatting interface provide access to the actual formatting specified for a cell or range of cells (including the characteristics defined by an applied style and direct formatting attributes).

Expanded Style Flags

The actual appearance of a cell is a combination of settings specified by the applied style and the direct cell format settings. Each formatting type provides a set of flags (Formatting.Flags). Each flag corresponds to a specific group of format attributes. You can use these flags when formatting a cell, to control whether to use attributes specified in the applied style or attributes specified directly for the cell.

Group Attributes Flag
Alignment Horizontal and vertical alignment of cell content, indentation and text wrap. StyleFlags.Alignment
Borders Cell border line styles and colors. StyleFlags.Borders
Fill Cell background color. StyleFlags.Fill
Font Cell font settings (name, style, color and size). StyleFlags.Font
Number Format Cell number format. StyleFlags.Number
Protection Cell protection options (Locked and Hidden). StyleFlags.Protection

Expanded Rich Text Formatting

A cell in a worksheet can contain rich formatted text specified by the RichTextString object. Rich text is comprised of one or more text regions (or text runs), each with its own set of font characteristics. An individual text run is defined by the RichTextRun object and stored in the RichTextString.Runs collection.

You can apply rich formatting to cell text as follows:

Pass the RichTextString instance to the Range.SetRichText method to assign rich text to a cell.

Refer to the How to: Apply Rich Formatting to Cell Text document for examples on how to apply different fonts to desired regions of cell text.

Expanded Examples

Expanded See Also

Is this topic helpful?​​​​​​​