WPF C# Excel-like grid

Question

Simple question: how would I go about creating a blank grid (similar to excel) where I can edit boxes and then have the ability to export to CSV? What I'm mainly interested in is creating a grid that is blank.

At first, I tried dynamically creating a Grid and adding TextBox manually to each grid cell. However, this proved to be quite tedious and bulky. I then found that you could use a DataGrid, which is a lot easier to use.

My problem with the DataGrid is that I can't figure out a way to create a bunch of blank rows.

How can I create a blank grid that is excel-like, which allows the user to input values (with the then possibility of exporting to CSV)?

Thanks in advance.

Answer 1

I did not find any complete examples on how to actually do this, and all answers to similar question were unsatisfying or incomplete. It took a while to get it working with the help from several StackOverflow and MSDN answers, which are referenced at the appropriate places.

Solution

Preface

Solution uses MVVM pattern.

It is intended to allow creating a fixed-size, non jagged 2D DataGrid from a fixed-size, non jagged List of List of objects or types IList<IList<T>> with data binding to a shared property on those objects. Support for extendable or jagged arrays could be added by implementing INotifyCollectionChanged on the bound List of Lists

T<T<object>>
    where T: class, IList, INotifyCollectionChanged

Explanations are given in the form of Code-comments. Without these comments, the code is surprisingly short

Model

using System.ComponentModel;

namespace WpfApp
{
    /// <summary>
    /// INotifyPropertyChanged and a Property to bind to is all that is needed. 
    /// The final Grid does not require every Cell to be the same type, 
    /// therefore this could also be an abstract base-class instead. 
    /// Then, different CellTypes could be implemented expecting different values, 
    /// like Dates/Numbers/..., and assigned to the Grid
    /// </summary>
    public class ExcelLikeCell : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler? PropertyChanged;

        private void OnPropertyChanged(string propertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }

        private object? _value = null;
        public object? Value
        {
            get { return _value; }
            set
            {
                if (_value != value)
                {
                    _value = value;
                    OnPropertyChanged(nameof(Value));
                }
            }
        }
    }
}

ViewModel

using System.Collections.Generic;
using System.ComponentModel;

namespace WpfApp
{
    public class MainWindowViewModel : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler? PropertyChanged;

        private void OnPropertyChanged(string propertyValue)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyValue));
        }

        private BindingList<List<ExcelLikeCell>> _cellData = new()
        {
            // Just some example cells,
            // normally this would be populated by a Model from a Database or something
            new List <ExcelLikeCell>
            {
                new ExcelLikeCell(),
                new ExcelLikeCell(),
                new ExcelLikeCell(),
            },
            new List <ExcelLikeCell>
            {
                new ExcelLikeCell(),
                new ExcelLikeCell(),
                new ExcelLikeCell(),
            },
        };

        public BindingList<List<ExcelLikeCell>> CellData
        {
            get { return _cellData; }
            set
            {
                if (value != _cellData)
                {
                    _cellData = value;
                    OnPropertyChanged(nameof(CellData));
                }
            }
        }
    }
}

View

XAML

<Window x:Class="WpfApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WpfApp"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
    <Window.DataContext>
        <local:MainWindowViewModel x:Name="ViewModel"/>
    </Window.DataContext>
    <Grid>
        <DockPanel Grid.Row="0" Grid.Column="0">
            <!-- User should not be able to change the shape of the grid -->
            <!-- Virtualization is optional -->
            <!-- Selection unit and mode allow for editing of multiple cells at once, where multi-select is possible by:
            1. Mouse dragging
            2. Clicking a Cell and Shift-Clicking a different cell to select a contiguous range
            3. Clicking a Cell and Ctrl-Clicking other cells to sparsely select cells-->
            <DataGrid x:Name="DataGridWorkloads" AutoGenerateColumns="False"
                      SelectionUnit="Cell" SelectionMode="Extended" 
                      CanUserAddRows="False" CanUserSortColumns="False" CanUserDeleteRows="False" CanUserReorderColumns="False" 
                      PreviewKeyDown="DataGridWorkloads_PreviewKeyDown"
                      EnableColumnVirtualization="True" EnableRowVirtualization="True">
            </DataGrid>
        </DockPanel>
    </Grid>
</Window>

Code-behind

using System;
using System.Data;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;

namespace WpfApp
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            DataGridWorkloads.Columns.Clear();

            // In this case, ViewModel is instantiated in the .xaml file as a window resource
            // But instantiation in code-behind is also possible

            // Assumption: CellData is a "rectangular" 2D array/list/...
            // No support for jagged row lengths
            var col_count = ViewModel.CellData.Select(ws => ws.Count).Max();

            for (var col = 0; col < col_count; col++)
            {
                var dataGridColumn = new DataGridTextColumn()
                {
                    // Binding string from https://social.msdn.microsoft.com/Forums/vstudio/en-US/da5f36df-91e1-4a1a-9265-25c9a2b56414/binding-list-of-lists-to-datagrid?forum=wpf
                    Binding = new Binding(String.Format("[{0}].Value", col)) { Mode=BindingMode.TwoWay },
                    Header = "Column" + (col + 1).ToString(),
                };
                DataGridWorkloads.Columns.Add(dataGridColumn);
            }

            DataGridWorkloads.ItemsSource = ViewModel.CellData;
        }

        private void DataGridWorkloads_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control)
            {
                // When multiple cells are selected
                //  <Enter> only changes the currently editied cell
                //  <CTRL + Enter> changes all selected cells
                if (e.Key == Key.Enter)
                {
                    if (DataGridWorkloads.SelectedCells.Count > 1)
                    {
                        var source = e.OriginalSource;
                        if (source != null && source is TextBox txtbox_source)
                        {
                            MultiCellEdit(txtbox_source);
                            e.Handled = false; // Do not handle here, let default Enter Key Behaviour "commit" the changes
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Updates all other selected cells to have the same value as the edited <paramref name="originalSource"/>
        /// </summary>
        /// <param name="originalSource">The TextBox whose contents were actually edited</param>
        private void MultiCellEdit(TextBox originalSource)
        {
            var entered_value = originalSource.Text;
            var cell_infos = DataGridWorkloads.SelectedCells;
            foreach (var info in cell_infos)
            {
                if (!info.IsValid)
                    continue;

                // Solution adapted from https://stackoverflow.com/a/19231765/21656349
                // By default, DataGrid.Column.GetCellContent() returns a
                //      TextBox for cell you currently edit
                //      TextBlock for all other cells
                // The other highlighted cells of your selection will therefore return a TextBlock
                // and only those actually need to be updated as well, since the edited cell is already edited
                if (info.Column.GetCellContent(info.Item) is TextBlock txt_block)
                {
                    var binding = txt_block.GetBindingExpression(TextBlock.TextProperty);
                    // In this example we expect to be bound to ExcelLikeCell (first if-case).
                    //
                    // The else-case showcases how you could also use Reflecton to find the
                    // correct bound property, instead of hardcoding every possible CellType
                    // you might implement, e.g. Cells only accepting Dates or Numbers.
                    // (useful if you want to create an ExcelLikeGrid-Class and put it in a library)
                    // (Reflection might be a performance hit, didn't test it yet)
                    if (binding.ResolvedSource is ExcelLikeCell source_excel_like_cell)
                    {
                        source_excel_like_cell.Value = entered_value;
                    }
                    else
                    {
                        var source = binding.ResolvedSource;
                        var source_type = source.GetType();
                        var source_property = source_type.GetProperty(binding.ResolvedSourcePropertyName);
                        // Whether or not to check for null here, is debatable
                        //
                        // If source_property is null, null-checking silently ignores the changes made by your user,
                        // who will be very confused as to why nothing is happening.
                        //
                        // Your application should never be null here, unless you have a binding error,
                        // which should be fixed before shipping anyway,
                        // so a crash might actually help catching such binding error
                        source_property?.SetValue(source, entered_value, null);
                    }
                }
            }
        }
    }
}

Pasting Clipboard data to the grid

Preface

Pasting is not automatically available for multi-cell editing and we have to implement it ourselves. For that we need a way to interpret and parse the clipboard data and then extend our View to accept this data.

As an example, a way to paste Excel-Data into the grid to multiple cells is shown here.

Support for other clipboard sources like csv files or Word-tables have to be detected and implemented separately. It is also important to note, that the Clipboard does apparently not provide any culture information on the data it holds, therefore decimal signs . and ,, separators , and ;, etc. have to detected in such parsing functions as well.

Parsing Clipboard Data

The functions to parse clipboard excel data were not developed by me, and their source is referenced in the function documentations.

using System;
using System.Collections.Generic;
using System.Data;

namespace WpfApp
{
    internal class ClipboardFunctions
    {
        /// <summary>
        /// By Kikemann, Sep. 20th 2009, <see href="https://social.msdn.microsoft.com/Forums/vstudio/en-US/0db522eb-1d84-4804-b311-e0cd723349d4/import-clipboard-from-excel-file?forum=csharpgeneral"/>
        /// </summary>
        /// <param name="StrRtfData"></param>
        /// <returns></returns>
        internal static DataTable GetTableFromExcelRtfString(string StrRtfData, bool first_row_is_headers = true)
        {
            int idxRowEnd = 0;
            int idxRowStart = 0;
            DataTable DtTbl = new();
            bool FirstRow = true;

            do
            {
                idxRowEnd = StrRtfData.IndexOf(@"\row", idxRowEnd, StringComparison.OrdinalIgnoreCase);
                if (idxRowEnd < 0) break;
                else if (StrRtfData[idxRowEnd - 1] == '\\') { idxRowEnd++; continue; }
                idxRowStart = StrRtfData.LastIndexOf(@"\trowd", idxRowEnd, StringComparison.OrdinalIgnoreCase);
                if (idxRowStart < 0) break;
                else if (StrRtfData[idxRowStart - 1] == '\\') { idxRowEnd++; continue; }

                string RowStr = StrRtfData.Substring(idxRowStart, idxRowEnd - idxRowStart);
                idxRowEnd++;

                int idxCell = 0;
                int idxCellMem = 0;
                List<string> MyDataRow = new();
                do
                {
                    idxCell = RowStr.IndexOf(@"\Cell ", idxCell, StringComparison.OrdinalIgnoreCase);
                    if (idxCell < 0) break;
                    else if (RowStr[idxCell - 1] == '\\') { idxCell++; continue; }

                    MyDataRow.Add(PurgeExcelRtfCmds(RowStr.Substring(idxCellMem, idxCell - idxCellMem)));
                    idxCellMem = idxCell;
                    idxCell++;
                }
                while (idxCellMem > 0);

                if (FirstRow)
                {
                    FirstRow = false;
                    if(first_row_is_headers)
                        foreach (string ColName in MyDataRow)
                            DtTbl.Columns.Add(ColName);
                    else
                    {
                        for(var i = 1; i <= MyDataRow.Count; i++)
                            DtTbl.Columns.Add(i.ToString());
                        DtTbl.Rows.Add(MyDataRow.ToArray());
                    }
                }
                else
                    DtTbl.Rows.Add(MyDataRow.ToArray());
            }
            while ((idxRowStart > 0) && (idxRowEnd > 0));

            return DtTbl;
        }

        /// <summary>
        /// By Kikemann, Sep. 20th 2009, <see href="https://social.msdn.microsoft.com/Forums/vstudio/en-US/0db522eb-1d84-4804-b311-e0cd723349d4/import-clipboard-from-excel-file?forum=csharpgeneral"/>
        /// </summary>
        /// <param name="StrRtfData"></param>
        /// <returns></returns>
        internal static string PurgeExcelRtfCmds(string StrRtf)
        {
            int idxRtfStart = 0;
            int idxRtfEnd;

            while (idxRtfStart < StrRtf.Length)
            {
                idxRtfStart = StrRtf.IndexOf('\\', idxRtfStart);
                if (idxRtfStart < 0) break;
                if (StrRtf[idxRtfStart + 1] == '\\')
                {
                    StrRtf = StrRtf.Remove(idxRtfStart, 1);   //1 offset to erase space
                    idxRtfStart++; //sckip "\"
                }
                else
                {
                    idxRtfEnd = StrRtf.IndexOf(' ', idxRtfStart);
                    if (idxRtfEnd < 0)
                        if (StrRtf.Length > 0)
                            idxRtfEnd = StrRtf.Length - 1;
                        else
                            break;
                    StrRtf = StrRtf.Remove(idxRtfStart, idxRtfEnd - idxRtfStart + 1);   //1 offset to erase space
                }

            }

            //Erase spaces at the end of the cell info.
            if (StrRtf.Length > 0)
                while (StrRtf[StrRtf.Length - 1] == ' ')
                    StrRtf = StrRtf.Remove(StrRtf.Length - 1);

            //Erase spaces at the beginning of the cell info.
            if (StrRtf.Length > 0)
                while (StrRtf[0] == ' ')
                    StrRtf = StrRtf.Substring(1, StrRtf.Length - 1);

            return StrRtf;
        }
    }
}

Pasting a DataTable into the 2D DataGrid

I consider this to be a part of the view, since the viewmodel knows nothing about the user pressing Ctrl+V. Therefore the required methods are implemented in the View code-behind.

View

Code-behind

To paste data into the grid, we first have to detect the pasting operation and intercept it in the PreviewKeyDown-Event

public partial class MainWindow : Window
    {
        /* previous stuff, see above */

        private void DataGridWorkloads_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control)
            {
                if (e.Key == Key.Enter)
                {
                    /* unchanged */
                }
                else if (e.Key == Key.V)
                {
                    if (DataGridWorkloads.SelectedCells.Count > 0)
                    {
                        if (e.OriginalSource is DataGridCell cell)
                        {
                            var row = GetRowOfCell(cell);
                            var col = GetColumnOfCell(cell);
                            PasteClipboard(row, col);
                            e.Handled = true; // don't want the letter V to be written into the cell
                        }
                    }
                }
            }
        }
    }

The new methods are as follows:

public partial class MainWindow : Window
    {
        /* previous stuff, see above */

        private void PasteClipboard(int start_row, int start_col)
        {
            DataTable? dt = null;
            var clipboard_html = (string)Clipboard.GetText(TextDataFormat.Html);
            if (clipboard_html.Contains("urn:schemas-microsoft-com:office:excel"))
            {
                // Source is Excel-Data
                var clipboard_data = (string)Clipboard.GetData(DataFormats.Rtf);
                dt = ClipboardFunctions.GetTableFromExcelRtfString(clipboard_data, false);
            }
            else
            {
                var msg = "Currently only pasting Excel-Data is supported";
                var caption = "Paste";
                MessageBox.Show(msg, caption, MessageBoxButton.OK, MessageBoxImage.Exclamation);
            }
            if (dt is null)
                return;

            if (dt.Rows.Count <= 0 || dt.Columns.Count <= 0)
                return;

            var row_count_downwards = ViewModel.CellData.Count - start_row;
            var col_count_rightwards = ViewModel.CellData.Select(ws => ws.Count).Max() - start_col;

            // store the previous selected cells to restore them later
            var prev_selected_cells = DataGridWorkloads.SelectedCells.ToList();
            DataGridWorkloads.SelectAllCells();

            // I couldn't find a better way to get all cells in the DataGrid
            var all_cells = DataGridWorkloads.SelectedCells.ToList();

            var cell_col_count = DataGridWorkloads.Columns.Count;
            for (int row_index = 0;
                row_index < row_count_downwards && row_index < dt.Rows.Count;
                row_index++)
            {
                var dt_row = dt.Rows[row_index];

                for (int col_index = 0;
                    col_index < col_count_rightwards && col_index < dt.Columns.Count;
                    col_index++)
                {
                    var dt_col = dt.Columns[col_index];
                    var value = dt_row[dt_col];

                    var cell_row = row_index + start_row;
                    var cell_col = col_index + start_col;
                    var cell_index = cell_row * cell_col_count + cell_col;
                    var cell = all_cells[cell_index];
                    UpdateGridCell(cell, value);
                }
            }

            // Restore the previous cell-selection for the user
            DataGridWorkloads.UnselectAllCells();
            foreach (var prev_selected_cell in prev_selected_cells)
                DataGridWorkloads.SelectedCells.Add(prev_selected_cell);
        }

        private static int GetRowOfCell(DataGridCell cell)
        {
            var row = DataGridRow.GetRowContainingElement(cell);
            return row is not null ? row.GetIndex() : -1;
        }

        private static int GetColumnOfCell(DataGridCell cell)
        {
            return cell.Column.DisplayIndex;
        }

        // I have not profiled the performance of this function, but it uses a lot of reflection
        private static void UpdateGridCell(DataGridCellInfo cell, object value)
        {
            var frameworkelement = cell.Column.GetCellContent(cell.Item);

            BindingExpression? binding = null;
            if (frameworkelement is TextBox textbox)
                binding = textbox.GetBindingExpression(TextBox.TextProperty);
            else if (frameworkelement is TextBlock textblock)
                binding = textblock.GetBindingExpression(TextBlock.TextProperty);

            if (binding is null)
                return;

            var prop = binding.TargetProperty;
            if (prop is null)
                return;

            frameworkelement.SetCurrentValue(prop, value);

            var valid = binding.ValidateWithoutUpdate();
            if (valid)
                binding.UpdateSource();
        }
    }

Answer 2

Here is described how to add rows and columns. Maybe you have to reload/update the control after adding the elements.