Introduction

This sample demonstrates how to read and process an excel spreadsheet, follwoing a question on MSDN forums.

Building the Sample

Just download, unblock, unzip, open and run!

Description

The problem with trying to bind data directly to WPF controls is that you lose the ability to extend the data item, to provide other application/session variables like "processing state" or "job status".

This method converts the data into a proper classes, in an ObservableCollection. This means we can extend the class to show session data like processing status.

Below is the class we use in this example, with most properties removed except JobStatus. These properties use INotifyPropertyChanged, which means changes in the data will be reflected back in the UI.

Edit|Remove

using System.ComponentModel;

namespace ExcelSpreadsheets.Model
{
    public class MyClass : INotifyPropertyChanged
    {
    
    ...removed for readability

        JobStatuses _JobStatus;
        public JobStatuses JobStatus
        {
            get
            {
                return _JobStatus;
            }
            set
            {
                if (_JobStatus != value)
                {
                    _JobStatus = value;
                    RaisePropertyChanged("JobStatus");
                }
            }
        }

        void RaisePropertyChanged(string prop)
        {
            if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(prop)); }
        }
        public event PropertyChangedEventHandler PropertyChanged;

    }
}

using System.ComponentModel; 
 
namespace ExcelSpreadsheets.Model 
{ 
    public class MyClass : INotifyPropertyChanged 
    { 
     
    ...removed for readability 
 
        JobStatuses _JobStatus; 
        public JobStatuses JobStatus 
        { 
            get 
            { 
                return _JobStatus; 
            } 
            set 
            { 
                if (_JobStatus != value) 
                { 
                    _JobStatus = value; 
                    RaisePropertyChanged("JobStatus"); 
                } 
            } 
        } 
 
        void RaisePropertyChanged(string prop) 
        { 
            if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(prop)); } 
        } 
        public event PropertyChangedEventHandler PropertyChanged; 
 
    } 
}

This class is used in an ObservableCollection bound to a DataGrid:

XAML

Edit|Remove

<Window x:Class="ExcelSpreadsheets.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525" 
        xmlns:helpers="clr-namespace:ExcelSpreadsheets.Helpers">
    <Window.Resources>
        <helpers:JobStatusConverter x:Key="JobStatusConverter"/>
    </Window.Resources>
    <Grid>
        <DataGrid ItemsSource="{Binding MyData}" AutoGenerateColumns="False" >
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding UserID}"/>
                <DataGridTextColumn Binding="{Binding Name}"/>
                <DataGridTextColumn Binding="{Binding Type}"/>
                <DataGridTemplateColumn>
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Image Height="20" Width="20" Source="{Binding JobStatus, Converter={StaticResource JobStatusConverter}}"/>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>
</Window>

<Window x:Class="ExcelSpreadsheets.MainWindow" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
        Title="MainWindow" Height="350" Width="525"  
        xmlns:helpers="clr-namespace:ExcelSpreadsheets.Helpers"> 
    <Window.Resources> 
        <helpers:JobStatusConverter x:Key="JobStatusConverter"/> 
    </Window.Resources> 
    <Grid> 
        <DataGrid ItemsSource="{Binding MyData}" AutoGenerateColumns="False" > 
            <DataGrid.Columns> 
                <DataGridTextColumn Binding="{Binding UserID}"/> 
                <DataGridTextColumn Binding="{Binding Name}"/> 
                <DataGridTextColumn Binding="{Binding Type}"/> 
                <DataGridTemplateColumn> 
                    <DataGridTemplateColumn.CellTemplate> 
                        <DataTemplate> 
                            <Image Height="20" Width="20" Source="{Binding JobStatus, Converter={StaticResource JobStatusConverter}}"/> 
                        </DataTemplate> 
                    </DataGridTemplateColumn.CellTemplate> 
                </DataGridTemplateColumn> 
            </DataGrid.Columns> 
        </DataGrid> 
    </Grid> 
</Window>

The JobStatus column is an image, created using the JobStatusConverter. When JobStatus changes, the PropertyChanged event is triggered, and the binding updates, causing the converter to return the matching image for the new status.

Source Code Files

MainWindow.xaml - statup window
MainWindow.xaml.cs - Loads the spreadsheet and runs a background thread to process the rows
Converters.cs - An image converter
Book1.xls - dummy data
JobStatuses.cs - enum
UserTypes.cs - enum
MyClass.cs - example class used for data from spreadsheet