In order to get some data into our Spreadsheet control, we will need a class to represent each cell in the spreadsheet. Let's take a look at a basic Cell class now:
namespace CompanyName.ApplicationName.DataModels
{
public class Cell : BaseDataModel
{
private string address = string.Empty, content = string.Empty;
private double width = 0;
public Cell(string address, string content, double width)
{
Address = address;
Content = content;
Width = width;
}
public string Address
{
get { return address; }
set { if (address != value) { address = value;
NotifyPropertyChanged(); } }
}
public string Content
{
get { return content; }
set { if (content != value) { content = value;
NotifyPropertyChanged(); } }
}
public double Width
{
get { return width; }
set { if (width != value) { width = value; NotifyPropertyChanged(); } }
}
public override string ToString()
{
return $"{Address}: {Content}";
}
}
}
This is a very straight forward class, with just three properties, a constructor to populate those properties, and an overridden ToString method. As usual, we extend our BaseDataModel class to provide us with access to the INotifyPropertyChanged interface. Note that in a real spreadsheet-based application, we would have many more properties in this class, to enable us to style and format the content appropriately.
Let's now move on, to create our SpreadsheetViewModel and SpreadsheetView classes. In the SpreadsheetViewModel class, we populate a DataTable with some basic example data and we data bind that to our new Spreadsheet control in the SpreadsheetView class:
using CompanyName.ApplicationName.DataModels;
using System.Data;
namespace CompanyName.ApplicationName.ViewModels
{
public class SpreadsheetViewModel : BaseViewModel
{
private DataRowCollection dataRowCollection = null;
public SpreadsheetViewModel()
{
Cell[] Cells = new Cell[9];
Cells[0] = new Cell("A1", "", 64);
Cells[1] = new Cell("B1", "", 96);
Cells[2] = new Cell("C1", "", 64);
Cells[3] = new Cell("A2", "", 64);
Cells[4] = new Cell("B2", "Hello World", 96);
Cells[5] = new Cell("C2", "", 64);
Cells[6] = new Cell("A3", "", 64);
Cells[7] = new Cell("B3", "", 96);
Cells[8] = new Cell("C3", "", 64);
DataTable table = new DataTable();
table.Columns.Add("A", typeof(Cell));
table.Columns.Add("B", typeof(Cell));
table.Columns.Add("C", typeof(Cell));
table.Rows.Add(Cells[0], Cells[1], Cells[2]);
table.Rows.Add(Cells[3], Cells[4], Cells[5]);
table.Rows.Add(Cells[6], Cells[7], Cells[8]);
Rows = table.Rows;
}
public DataRowCollection Rows
{
get { return dataRowCollection; }
set { if (dataRowCollection != value) { dataRowCollection = value;
NotifyPropertyChanged(); } }
}
}
}
In this very simple View Model, we declare a single property of type DataRowCollection, to contain our spreadsheet data. Using this type enables us to easily populate our spreadsheet from a DataTable object, which we may have loaded from a database, or generated from an XML file, for example.
In the constructor, we programmatically initialize and populate a DataTable with example Cell objects and set its Rows property value to our Rows property. Let's see how this Rows property is data-bound to our Spreadsheet control in the SpreadsheetView class now:
<UserControl x:Class="CompanyName.ApplicationName.Views.SpreadsheetView"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:Controls="clr-namespace:CompanyName.ApplicationName.Views.Controls">
<Controls:Spreadsheet ItemsSource="{Binding Rows}" Margin="50" />
</UserControl>
Once again, this is a very simple class, with nothing other than a XAML Namespace declaration for our Controls project and one of our Spreadsheet controls, with its ItemsSource property data bound to the Rows property of our View Model. The code behind is even more bare, with no custom code in it at all. Also, remember to link our View and View Model together, using whichever method you prefer.
Before we can see any data in our Spreadsheet control, however, we will need to declare a DataTemplate to define how each cell should be rendered and programmatically set up our columns, in relation to the data-bound items. Let's declare the required XAML Namespace in the XAML file and add the DataTemplate into the Resources section of our Spreadsheet control first:
xmlns:DataModels="clr-namespace:CompanyName.ApplicationName.DataModels;
assembly=CompanyName.ApplicationName.DataModels" ... <DataTemplate x:Key="CellTemplate" DataType="{x:Type DataModels:Cell}"> <TextBlock Text="{Binding Content}" HorizontalAlignment="Center"
VerticalAlignment="Center" /> </DataTemplate>
Here, we have a horizontally centered TextBlock control, to output the contents of each cell. In a real-world application, we'd surround it with a Border element, to color the background of each cell and data bind to many more properties, to enable us to set different style and formatting settings for each cell. For this example, however, we'll keep it simple.
Returning to the subject of column generation now, remember that we do not know how many columns there will be in the incoming data, so we need to find a place to set them up programmatically. For this, we return to the protected base class methods.
Looking through the protected methods of the DataGrid class, we see a good candidate: the OnItemsSourceChanged method. This method will be called each time the ItemsSource value changes, so it's an ideal place to initialize our spreadsheet columns when the data source changes.
But our items are DataRow objects, with each Cell object being in a different location in its ItemArray collection. We need a way to use the array syntax to data bind each Cell, but the built-in column types don't have this functionality. As such, we will need to create a custom one and the DataGridTemplateColumn class is the best place to start.
We can override this class to add a property named Binding of type Binding and use it to set the binding on the UI element that is generated for each cell. Looking through the protected methods in the DataGridTemplateColumn class, we find the GenerateElement method, which generates these UI elements. Let's see this new DataGridBoundTemplateColumn class now:
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
namespace CompanyName.ApplicationName.Views.Controls
{
public class DataGridBoundTemplateColumn : DataGridTemplateColumn
{
public Binding Binding { get; set; }
protected override FrameworkElement GenerateElement(DataGridCell cell,
object dataItem)
{
FrameworkElement element = base.GenerateElement(cell, dataItem);
if (Binding != null)
element.SetBinding(ContentPresenter.ContentProperty, Binding);
return element;
}
}
}
This is another simple class and, we start by extending the DataGridTemplateColumn class and declaring the aforementioned Binding property. We then override the GenerateElement method and in it, first call the base class implementation to generate the FrameworkElement object that relates to the current cell, passing the input parameters through unchanged.
If the Binding property is not null, we then call the SetBinding method on the element, specifying the ContentPresenter.ContentProperty Dependency Property as the binding target and passing the Binding object from the Binding property through to connect with it. We end by simply returning the generated element.
Now, let's return to the code behind of our Spreadsheet class, where we need to use our new DataGridBoundTemplateColumn class:
using CompanyName.ApplicationName.DataModels; using System.Collections; using System.Data; using System.Linq; using System.Windows; using System.Windows.Controls; using System.Windows.Data; ... protected override void OnItemsSourceChanged(IEnumerable oldValue,
IEnumerable newValue) { if (!(newValue is DataRowCollection rows) || rows.Count == 0) return; Cell[] cells = rows[0].ItemArray.Cast<Cell>().ToArray(); Columns.Clear(); DataTemplate cellTemplate = (DataTemplate)FindResource("CellTemplate"); for (int i = 0; i < cells.Length; i++) { DataGridBoundTemplateColumn column = new DataGridBoundTemplateColumn { Header = GetColumnName(i + 1), CellTemplate = cellTemplate, Binding = new Binding($"[{i}]"), Width = cells[i].Width }; Columns.Add(column); } } private string GetColumnName(int index) { if (index <= 26) return ((char)(index + 64)).ToString(); if (index % 26 == 0)
return string.Concat(GetColumnName(index / 26 - 1), "Z"); return string.Concat(GetColumnName(index / 26),
GetColumnName(index % 26)); }
As mentioned previously, we override the OnItemsSourceChanged method to initialize our spreadsheet columns each time the data source changes. In it, we use C# 6.0 Pattern Matching to verify that the newValue input parameter is not null and is of type DataRowCollection, before also checking that the collection has one or more rows in it.
If the DataRowCollection object is valid, then we cast the items in the ItemArray collection of its first row to an array of our custom type Cell. We only need to use the first row, because here, we are just setting up the columns, not the data. We then clear the columns of our spreadsheet control and find the DataTemplate named CellTemplate from the control's Resources section.
Next, we iterate through the Cell objects in the array, adding a new DataGridBoundTemplateColumn element to the spreadsheet's Columns collection for each one. Each column element is initialized with a Header, taken from the GetColumnName method, the CellTemplate DataTemplate, the Width from the Cell object, and a Binding object.
Note that the Binding path is set to $"[{i}]", which would translate to "[0]" for the first item for example, and represents the standard indexing notation. This would result in the binding path being set to the first item in each row of the data-bound collection, or put another way, each cell in the first column of our data source.
If the input value in the GetColumnName method is between 1 and 26, we add 64 to it, before casting it to a char and then calling the ToString method on the result. The capital A character has the integer value of 65 in the ASCII table and so, this code has the effect of turning the index of the first 26 columns into the letters A to Z.
If the input value is more than 26 and is also an exact multiple of 26, then we return the string concatenation of a recursive call to the GetColumnName method, passing in the factor of the input value when it is divided by 26, with 1 subtracted from it, and the letter Z.
If none of the if conditions are met, we return the result of two more recursive calls: the first passed value represents the factor of the input value when it is divided by 26 and the second represents the remainder of the input value when it is divided by 26.
In plain English, the first line outputs letters A to Z, while the second handles column identities that contain more than a single letter and end in the letter Z, and the third line handles all of the rest. Let's see what we have when running the application so far:
