An attempt to make the Silverlight DataGrid similar to Excel

• Download source code - 1.52 MB

Introduction

For many simple editing operations, giving the users only a grid and allowing them to edit in place is a good solution. Users who are used to Excel (and in fact, demand similar functionality) will be able to instantly discover how your app works if it behaves somewhat similar to Excel.

Background

For the purposes of this article, I'll define "Excel like behavior" as a simple, easily discoverable navigation between grid cells, and not as formulas, the ability to add charts, etc. We're just looking for the ability of users to be able to perform CRUD operations simply without having to read an instruction document or call anybody.

The default navigation of a Silverlight DataGrid is as follows: Edit mode is entered when you double click on a cell or hit F2 in a cell - users will probably never discover the F2 part and be annoyed that typing doesn't work.

Default Excel navigation is as follows: Edit mode is entered when you double click or type in a cell.

In other words, Excel users expect to type in a cell and have it receive the text, and they are not going to reach for the mouse unless they have spilled and/or exploded something on the keyboard.

As an extra bonus and as our last fallback when debating with users, we will also provide reasonable copy and paste functionality so that users can paste in an arbitrary number of rows containing either all columns (we will ignore read only ones for pasting into the grid) or just the writeable ones. That way, users will be able to easily get data out of our grids just the way they do in Excel, and also get data in easily without going through any wizards or extra steps they aren't used to.

And if all else fails, the users can copy data out of our grid, edit it to their heart's content in their favorite editor, then copy from there and paste it back into the Silverlight DataGrid.

Copy and paste

Let's first take care of the fallback plan: copy and paste. Excel will understand copy and pasted data if rows are separated by newlines and columns are separated by tabs. That doesn't seem very difficult to accomplish, but we have two problems to deal with in the DataGrid first:

• DataGrids do not have grid data and bound data, they only have bound data.

So it is somewhat difficult to ask for the content in Row 1, Column 3 unless you happen to know that you bound property X to column 3 in your grid. What we are looking for here is a more general solution that will work with most grids without having to know the underlying binding details.

Fortunately, that can be achieved with the following, assuming there is a TextBlock in the cell and you already have a reference to the item that is bound for that row:

(column.GetCellContent(item) as TextBlock).Text

To get a reference to your bound objects, you can use dataGrid.SelectedItems.

Depending on how complex your display data is, though, you may have a more complicated structure in your grid. The following should handle most cases and be understandable enough to modify if you have something really crazy in your grid:

private static TextBlock GetCellTextBlock(object item, DataGridColumn column)
{
    var cellData = (column.GetCellContent(item) as TextBlock);
    if (cellData == null)
    {
        //for our custom columns
        var gridData = (column.GetCellContent(item) as Panel);
        if (gridData != null)
        {
            cellData = (gridData.Children.Where(x => x.GetType() == 
                         typeof(TextBlock)).FirstOrDefault() as TextBlock);
        }
    }
    return cellData;
}

• The second problem we must deal with is that DataGrids do not render content until it is actually visible.

This makes sense from a performance point of view, so it isn't rendering the parts of the grid you can't even see. But it does cause some difficulties if you want to access data in the order it is displayed in the grid. One way to solve this would be to use Reflection. Another way to solve it would be to manually scroll the grid into view as you copy the data! One of those is much simpler than the other, and isn't terribly visible to the user until the end where the grid has scrolled to the end of the data. If that bothers you, you can always scroll back to the beginning selection, but for now, I am leaving that as is. But the command to scroll the current cell into view is quite simple:

dataGrid.ScrollIntoView(item, column);

Once we have solved those two problems, it is fairly simple to handle the copy and paste by joining or splitting the data with the tab and newline delimiters. See the attached source for more details there.

If you look at the source, you will notice that when the data is copied out, the headers are added (so the users know which columns they have, important when there are lots of columns) and that the user can paste in an arbitrary amount of data, even when there are read-only columns. Here is a video to see this in action without downloading the source: http://www.blip.tv/file/3055352.

Now, how about making the edit behavior work like in Excel?

The one-liner, kinda-works way

private void dataGrid_CurrentCellChanged(object sender, EventArgs e)
{
   dataGrid.BeginEdit();
}

Using this, as soon as a cell gets focus, it goes into edit mode. This means that the user can type directly into a cell, and up/down arrow keys work, but left and right arrows will arrow through the cells until they get to the end and then go to the next cell. So, if the user is OK with that or is used to tabbing instead of using the arrow keys, stop, you are done. At any rate, this solution is better than the default if you expect the user to be doing lots of inline editing and aren't worried about accidentally changing data.

The complicated, multi-liner, actually works way

If we trap the KeyUp event on the grid and can detect if we have just entered edit mode, then we can just manually set the text of the textbox to the first key that was hit and move the cursor to the end of that text. Easy, right?

Not really. As far as I can tell, there isn't a way to know if a grid is in edit mode or not, so I'm using the tag property of the object itself to store that, and only do that if a key has been pressed, it isn't a navigation key, and the column isn't read only:

//don't start editing for nav keys so a left arrow, etc. doesn't put text in a box
if (dataGrid.Tag == null && !IsNavigationKey(e) && !dataGrid.CurrentColumn.IsReadOnly)
{
    bool isShifty = ((Keyboard.Modifiers & ModifierKeys.Shift) == ModifierKeys.Shift);
    string letter = ((char)e.PlatformKeyCode).ToString();
    letter = (isShifty ? letter.ToUpper() : letter.ToLower());
    dataGrid.Tag = letter;

    //beginedit will fire the focus event
    //if we try to access the textbox here its text will not be set
    dataGrid.BeginEdit();
}

OK, that's a little kludgey, but now, we can just set the text, right? No, the textbox isn't initialized yet in the KeyDown or KeyUp event, so we have to trap the GotFocus event and use that same Tag property to know what to put in it:

private void dataGrid_GotFocus(object sender, RoutedEventArgs e)
{
    if (dataGrid.Tag != null)
    {
        var box = CopyPasteSupport.GetCellItem<TextBox>(
                       dataGrid.SelectedItem, dataGrid.CurrentColumn);
        if (box != null)
        {
            box.Text = dataGrid.Tag.ToString();
            box.SelectionStart = 1; //move editing cursor to end of text
        }
    }
}

And then, reset of our kludgey state mechanism on the CellEditEnded event:

void dataGrid_CellEditEnded(object sender, DataGridCellEditEndedEventArgs e)
{
    dataGrid.Tag = null;
}

Now the user can just start typing in a cell and it doesn't lose the first key press while entering edit mode. The users will, in all likelihood, never know you did this, and just use it without asking any questions or making any phone calls.

To enable all this functionality, you just need to reference the ExcelBehavior project and put this line in your grid Load event:

ExcelBehavior.EnableForGrid(dataGrid);

History

• 9-Jan-2010 - Initial version.