How to: Customize Cell Appearance Based on a Condition

• Jun 07, 2019
• 3 minutes to read

The cell template in the WPF Spreadsheet allows you to change the visual presentation of cells. To assign a cell template, use the SpreadsheetControl.CellTemplate property of the SpreadsheetControl.

The cell template is applied to all existing cells in the worksheet. However, you can have more than one template and implement custom logic to choose the desired template. This allows you to provide a different visual appearance for individual cells. To choose templates based on custom logic, do the following.

1. Create a template selector class that is a descendant of the DataTemplateSelector class and override the SelectTemplate method, to return a template that meets the required condition.
2. Assign its instance to the SpreadsheetControl.CellTemplateSelector property.

This example uses a template selector to display formulas above certain cells. If a cell does not contain a formula, it displays a ‘Missing Formula’ warning.

The result is shown below.

MainWindow.xaml

<Window
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:dxsps="http://schemas.devexpress.com/winfx/2008/xaml/spreadsheet" 
    xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
    xmlns:local="clr-namespace:CellTemplateSelectorExample"
    x:Class="CellTemplateSelectorExample.MainWindow"
    dx:ThemeManager.ThemeName="Office2013"
    Title="MainWindow" Height="600" Width="800">
    <Window.Resources>
        <DataTemplate x:Key="FormulaTemplate" DataType="{x:Type dxsps:CellData}">
            <Grid>
                <Canvas HorizontalAlignment="Right">
                    <Grid Canvas.Left="0" Canvas.Top="-20" Height="26">
                        <Border  Background="Lavender" Height="14" VerticalAlignment="Top">
                            <TextBlock Margin="10,0" Text="{Binding Path=Cell.Formula}" 
                                       FontWeight="Bold" Foreground="Brown" VerticalAlignment="Center"/>
                        </Border>
                        <Path VerticalAlignment="Top" Margin="0,14,0,0" HorizontalAlignment="Left" 
                              Data="M 0,0 0,10 7,0"  Fill="Lavender" />
                    </Grid>
                </Canvas>
                <TextBlock Text="{Binding TextSettings.Text}" TextWrapping="{Binding TextSettings.TextWrapping}" 
                           FontFamily="{Binding TextSettings.FontFamily}"
                           FontStyle="{Binding TextSettings.FontStyle}" FontSize="{Binding TextSettings.FontSize}" 
                           FontWeight="{Binding TextSettings.FontWeight}" 
                           TextAlignment="{Binding TextSettings.TextAlignment}" 
                           Foreground="Black" Margin="0,0,0,2" Clip="{Binding Clip}"/>
            </Grid>
        </DataTemplate>
        <DataTemplate x:Key="EmptyTemplate" DataType="{x:Type dxsps:CellData}">
            <Grid>
                <Canvas HorizontalAlignment="Right">
                    <Grid Canvas.Left="0" Canvas.Top="-20" Height="26">
                        <Border Background="LightPink" Height="14" VerticalAlignment="Top">
                            <TextBlock Margin="5,0" Text="Missing Formula" FontWeight="Bold" VerticalAlignment="Center"/>
                        </Border>
                        <Path VerticalAlignment="Top" Margin="0,14,0,0" HorizontalAlignment="Left" Data="M 0,0 0,10 7,0" Fill="LightPink"/>
                    </Grid>
                </Canvas>
                <TextBlock Text="{Binding TextSettings.Text}" TextWrapping="{Binding TextSettings.TextWrapping}" 
                           FontFamily="{Binding TextSettings.FontFamily}"
                           FontStyle="{Binding TextSettings.FontStyle}" FontSize="{Binding TextSettings.FontSize}" 
                           FontWeight="{Binding TextSettings.FontWeight}" TextAlignment="{Binding TextSettings.TextAlignment}" 
                           Foreground="Red" Margin="0,0,0,2" Clip="{Binding Clip}"/>
                <Border Background="Red" VerticalAlignment="Bottom" Height="2" Clip="{Binding Clip}"/>
            </Grid>
        </DataTemplate>
        <local:CellTemplateSelector x:Key="CellTemplateSelector"/>
    </Window.Resources>
    <Grid>
        <dxsps:SpreadsheetControl x:Name="spreadsheetControl1"/>
    </Grid>
</Window>

Application.xaml

<Application x:Class="CellTemplateSelectorExample.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="MainWindow.xaml">
    <Application.Resources>

    </Application.Resources>
</Application>

MainWindow.xaml.vb

Imports Microsoft.VisualBasic
Imports DevExpress.Xpf.Spreadsheet
Imports System.Collections.Generic
Imports System.Windows
Imports System.Windows.Controls

Namespace CellTemplateSelectorExample
    Partial Public Class MainWindow
        Inherits Window
        Public Sub New()
            InitializeComponent()
            spreadsheetControl1.LoadDocument("vlookup.xlsx")
'            #Region "#celltemplate_code"
            spreadsheetControl1.CellTemplateSelector = TryCast(spreadsheetControl1.TryFindResource("CellTemplateSelector"), DataTemplateSelector)
'            #End Region ' #celltemplate_code
        End Sub
    End Class

    Public Class CellTemplateSelector
        Inherits DataTemplateSelector
        Public Overrides Function SelectTemplate(ByVal item As Object, ByVal container As DependencyObject) As DataTemplate
            Dim data As CellData = TryCast(item, CellData)
            Return If(CanApplyCustomTemplate(data.Cell.GetReferenceA1()), GetCellTemplate(data.Cell, CType(container, SpreadsheetControl)), MyBase.SelectTemplate(item, container))
        End Function
        Private customTemplateCells As New List(Of String) (New String() {"A2", "A3", "A4", "A5", "A6"})
        Private Function CanApplyCustomTemplate(ByVal cellPosition As String) As Boolean
            Return customTemplateCells.Contains(cellPosition)
        End Function
        Private Function GetCellTemplate(ByVal cell As DevExpress.Spreadsheet.Cell, ByVal control As SpreadsheetControl) As DataTemplate
            Dim templateName As String = If(cell.HasFormula, "FormulaTemplate", "EmptyTemplate")
            Return TryCast(control.TryFindResource(templateName), DataTemplate)
        End Function
    End Class
End Namespace

Application.xaml.vb

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Linq
Imports System.Threading.Tasks
Imports System.Windows

Namespace CellTemplateSelectorExample
    ''' <summary>
    ''' Interaction logic for App.xaml
    ''' </summary>
    Partial Public Class App
        Inherits Application
    End Class
End Namespace

MainWindow.xaml.cs

using DevExpress.Xpf.Spreadsheet;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;

namespace CellTemplateSelectorExample
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            spreadsheetControl1.LoadDocument("vlookup.xlsx");
            #region #celltemplate_code
            spreadsheetControl1.CellTemplateSelector = spreadsheetControl1.TryFindResource("CellTemplateSelector") as DataTemplateSelector;
            #endregion #celltemplate_code
        }
    }

    public class CellTemplateSelector : DataTemplateSelector
    {
        public override DataTemplate SelectTemplate(object item, DependencyObject container)
        {
            CellData data = item as CellData;
            return CanApplyCustomTemplate(data.Cell.GetReferenceA1()) ? GetCellTemplate(data.Cell, (SpreadsheetControl)container) : base.SelectTemplate(item, container);
        }
        List<string> customTemplateCells = new List<string>() { "A2", "A3", "A4", "A5", "A6" };
        private bool CanApplyCustomTemplate(string cellPosition)
        {
            return customTemplateCells.Contains(cellPosition);
        }
        private DataTemplate GetCellTemplate(DevExpress.Spreadsheet.Cell cell, SpreadsheetControl control)
        {
            string templateName = cell.HasFormula ? "FormulaTemplate" : "EmptyTemplate";
            return control.TryFindResource(templateName) as DataTemplate;
        }
    }
}