DataGridView Custom Formatting

From IronPython Cookbook

Utility of this Article

The main lessons I gleaned from this excercise:

1) It is possible to customize the appearance of the DataGridView control (colors, fonts); the code in this article can be used to accomplish that.

2) It is also possible to customize selection of cells in the DataGridView control, and to paste them automatically to the clipboard.

3) The greater the degree of overriding of the default behavior of the DataGridView control, the more brittle and "abstraction leaky" [1] the DataGridView becomes - I have highlighted some of these omissions and mistakes with triple X's in the code.

Given point 3), it would be wise not to apply the code below in its current state to an enterprise application or commercial product. The code does, however, demonstrate methods for customizing the control that could be employed with more testing and "bulletproofing" of the application.

The Problem

We have a data acquisition scenario that requires manual selection of the correct data. A set of eyballs connected to a brain are necessary. The correct data will reside in one row, and one row only, of the DataGridView control.

The tabular representation of the data spans 10 columns and about 1000 rows. It is easy to lose your place when looking at that much data on screen. I wanted a means of tracking where the mouse was at a glance.

The default Windows selection color scheme (white on deep blue) doesn't lend itself to easy viewing. Also, the default behavior of the DataGridView is to allow boxed multiple cell collections. I want only one row or one cell selected at a time.

Ideally, after a little training, the user would be able to work faster and make fewer mistakes with the customized control.

Result of Attempt to Modify the Default Control Behavior

Several screen shots below show the modified behavior of the form.

The current mouse position column and row are highlighted along with the mouse-over'd cell. (Mock) Selections are made in a color scheme other than the Windows default. Selection restrictions of either one row or one cell are enforced.

Image:selectioncleared.jpg

Image:selection.jpg

Image:mouseover.jpg

Image:mouseoverandselected.jpg

Code and Performance

While the example shown above performed well, in practice my implementation of row and column highlighting was too slow, and would have worked against the goals of speed and accuracy for the user.

The full code is presented below. A discussion follows.

# cellfmt.py

"""
Test of IronPython's ability to change
individual cell formats in a dot Net
DataGridView control.
Intended behavior of DataGridViewControl:
    1) highlights row and column of mouse
       position simultaneously
    2) mock selection in color scheme
       other than Windows default
    3) selection of individual cells
       and individual rows allowed
       (no multiple selection)
    4) auto copy selections to clipboard
       in a manner that allows direct
       pasting into Excel
Geared toward use with ~1000 rows and
~10 columns.
Intended to make tracking of active row
and data easier.
"""
# XXX - row/column tracking is slow with
# XXX       more than 500 rows
# XXX   could try to optimize by tracking cell indices

# XXX - PageDown, PagUp, Home, and Arrow Keys will
# XXX       still highlight and select cells in the 
# XXX          Windows default fashion.

# XXX - could write more code for column header width
# XXX       adjustment and row width adjustment
# XXX       - columnwidth adjustment clears mock selection
# XXX       - rowwidth adjustment selects row above row
# XXX             boundary

import clr

clr.AddReference('System.Windows.Forms')
clr.AddReference('System.Drawing')

from System.Windows.Forms import Form
from System.Windows.Forms import DataGridView
from System.Windows.Forms import DataGridViewContentAlignment
from System.Windows.Forms import Application
from System.Windows.Forms import Control
from System.Windows.Forms import Clipboard
from System.Windows.Forms import DataFormats
from System.Windows.Forms import DataObject

from System.Drawing import Point
from System.Drawing import Size
from System.Drawing import Font
from System.Drawing import FontStyle
from System.Drawing import Color

from System import Text

from System.IO import MemoryStream

# formatting constants
MDDLCNTR = DataGridViewContentAlignment.MiddleCenter
BOLD = Font(Control.DefaultFont, FontStyle.Bold)
REGL = Font(Control.DefaultFont, FontStyle.Regular)
SELECTCOLOR = Color.LightSkyBlue
ROWCOLOR = Color.Yellow
COLUMNCOLOR = Color.Cyan
MOUSEOVERCOLOR = Color.GreenYellow
REGULARCOLOR = Color.White
ROWHDRWDTH = 65

CSV = DataFormats.CommaSeparatedValue

# hack for identifying mouseovered cell
#    mousing over one of the header cells yields
#        an event.RowIndex value of -1       
INDEXERROR = -1

NUMCOLS = 3
HEADERS = ['positive', 'negative', 'flat']
TESTDATA = (['happy', 'sad', 'indifferent'],
            ['ebullient', 'despondent', 'phlegmatic'],
            ['elated', 'depressed', 'apathetic'],
            ['fired up', 'bummed out', "doesn't care"],
            ['psyched', 'uninspired', 'blah'])
NUMROWS = len(TESTDATA)

def getcellidxs(event):
    """
    From a mouse event on the DataGridView,
    returns the row and column indices of 
    the cell as a 2 tuple of integers.
    """
    # this is redundant with DataGridForm.getcell
    #    class methods were not handling unpacking
    #        of tuple with cell in it
    #    trying a separate function
    print event.RowIndex, event.ColumnIndex
    return event.RowIndex, event.ColumnIndex

def resetcellfmts(gridcontrol, numrows):
    """
    Initialize formatting of all data
    cells in the grid control.
    """
    # need to cycle through all cells individually
    #    to reset them
    for num in xrange(numrows):
        row = gridcontrol.Rows[num]
        for cell in row.Cells:
            # skip over selected cell(s)
            if cell.Style.BackColor != SELECTCOLOR:
                cell.Style.Font = REGL
                cell.Style.BackColor = REGULARCOLOR 

def resetheaderfmts(gridcontrol, rowidx, colidx):
    """
    Reset BackColor on "Header" cells for 
    rows and columns.
    """
    col = gridcontrol.Columns[colidx]
    col.HeaderCell.Style.BackColor = Color.Empty
    # for row header formats, don't clear selected
    row = gridcontrol.Rows[rowidx]
    if row.HeaderCell.Style.BackColor != SELECTCOLOR:
        row.HeaderCell.Style.BackColor = Color.Empty

def clearselection(gridcontrol):
    """
    This works as a separate function,
    but not as part of the form class.
    Clears gridview selection.
    """
    # clear selection
    gridcontrol.ClearSelection()

def mockclearselection(gridcontrol):
    """
    Clears mock selection on custom 
    color scheme for DataGridView.
    """
    # have to cycle through all cells
    rows = gridcontrol.Rows
    for row in rows:
        # deal with selected header, if any
        row.HeaderCell.Style.BackColor = Color.Empty
        cells = row.Cells
        for cell in cells:
            if cell.Style.BackColor == SELECTCOLOR:
                cell.Style.BackColor = REGULARCOLOR
                cell.Style.Font = REGL

def copytoclipboard(args):
    """
    Put data on Windows clipboard 
    in csv format.
    """
    csvx = ""
    if len(args) == 0:
        # clear clipboard
        print 'clearing clipboard'
    elif len(args) == 1:
        csvx = args[0]
    else:
        csvx = ""
        csvx = ','.join(args)
    dobj = DataObject()
    # hack from MSDN PostID 238181
    # this is a bit bizarre,
    #    but it works for getting csv data into Excel
    txt = Text.Encoding.Default.GetBytes(csvx)
    memstr = MemoryStream(txt)
    dobj.SetData('CSV', memstr)
    dobj.SetData(CSV, memstr)
    Clipboard.SetDataObject(dobj)

class DataGridForm(Form):
    """
    Container for the DataGridView control
    I'm trying to test.
    DataGridView is customized to have row
    and column of mouse-over'd cell highlighted.
    Also, there is a customized selection 
    color and selection limitations (one cell
    or one row at a time).
    """
    def __init__(self, numcols, numrows):
        """
        numcols is the number of columns
        in the grid.
        """
        self.Text = 'DataGridView Cell Format Test'
        self.ClientSize = Size(400, 175)
        self.MinimumSize = Size(400, 175)
        self.dgv = DataGridView()
        self.numcols = numcols
        self.numrows = numrows
        self.setupdatagridview()
        self.adddata()
        self.formatheaders()
        # clears Windows default selection on load
        clearselection(self.dgv)
    def setupdatagridview(self):
        """
        General construction of DataGridView control.
        Bind mouse events as appropriate.
        """
        self.dgv.Location = Point(0, 0)
        self.dgv.Size = Size(375, 150)
        self.Controls.Add(self.dgv)
        # have to have columns defined before inserting rows
        self.dgv.ColumnCount = self.numcols
        # center all text in all data cells by default
        self.dgv.DefaultCellStyle.Alignment = MDDLCNTR
        # use Mouse events for contingency that actual
        #    position is required
        #    otherwise, can use events without "Mouse"
        #       in them
        # CellMouseEnter event for formatting
        self.dgv.CellMouseEnter += self.onmouseovercell
        # CellMouseLeave event for formatting
        self.dgv.CellMouseLeave += self.onmouseleavingcell
        # another try at MouseClick (avoiding default select color)
        self.dgv.CellMouseUp += self.onmouseclickcell
        # add empty rows first
        for num in xrange(self.numrows):
            self.dgv.Rows.Add()
        # format empty cells
        resetcellfmts(self.dgv, self.numrows)
        # lock control so user cannot do anything to it datawise
        self.dgv.AllowUserToAddRows = False
        self.dgv.AllowUserToDeleteRows = False
        self.dgv.ReadOnly = True
        self.dgv.ClearSelection()
    def formatheaders(self):
        """
        Get header row and left side column 
        populated and formatted.
        """
        # give names to columns
        # if the name is the same as the desired header caption,
        #     the Name attribute will take care of the caption
        for num in xrange(self.numcols):
            # need to center text in header row 
            #    separate from data rows
            col = self.dgv.Columns[num]
            col.Name = HEADERS[num]
            # slightly left of center on headers
            col.HeaderCell.Style.Alignment = MDDLCNTR
            # sets font and font style
            col.HeaderCell.Style.Font = BOLD 
            col.HeaderCell.Style.ForeColor = Color.MidnightBlue
        # put numbers on rows
        for num in xrange(self.numrows):
            row = self.dgv.Rows[num]
            # get sequential numeric label on side of row
            row.HeaderCell.Value = str(num + 1)
            # sets font and font style
            row.HeaderCell.Style.Font = BOLD 
            row.HeaderCell.Style.ForeColor = Color.Blue
        # XXX - clear button is implicit, not explicit
        self.dgv.TopLeftHeaderCell.Value = 'CLEAR'
        self.dgv.TopLeftHeaderCell.Style.Font = BOLD
        self.dgv.TopLeftHeaderCell.Style.ForeColor = Color.Blue
        self.dgv.RowHeadersWidth = ROWHDRWDTH
    def adddata(self):
        """
        Put data into the grid, 
        row by row, column by column.
        """
        # go off indices of rows for placing data in cells
        for num in xrange(self.numrows):
            row = self.dgv.Rows[num]
            # iterator for data - places in correct column
            dat = (datax for datax in TESTDATA[num]) 
            for cell in row.Cells:
                cell.Value = dat.next()
    def getcell(self, event):
        """
        Gets DataGridViewCell that is responding
        to an event.
        Attempt to minimize code duplication by 
        applying method to multiple events.
        """
        colidx = event.ColumnIndex
        rowidx = event.RowIndex
        if rowidx > INDEXERROR and colidx > INDEXERROR:
            # to get a specific cell, need to first
            #    get row the cell is in,
            #        then get cell indexed by column
            row = self.dgv.Rows[rowidx]
            cell = row.Cells[colidx]
            return cell
        else:
            return None
    def onmouseovercell(self, sender, event):
        """
        Change format of data cells
        when mouse passes over them.
        """
        # had to separate these into two functions
        #    problems with tuple unpacking
        cell = self.getcell(event)
        rowidx, colidx = getcellidxs(event)
        if cell:
            # 1) take care of row and column header formatting
            col = self.dgv.Columns[colidx]
            col.HeaderCell.Style.BackColor = COLUMNCOLOR
            row = self.dgv.Rows[rowidx]
            # only change if the row header is not selected
            if row.HeaderCell.Style.BackColor != SELECTCOLOR:
                row.HeaderCell.Style.BackColor = ROWCOLOR
            # 2) bold individual cell
            cell.Style.Font = BOLD 
            # 3) color individual cell green
            # but skip if it is a selected cell
            if cell.Style.BackColor != SELECTCOLOR:
                cell.Style.BackColor = MOUSEOVERCOLOR
            row = self.dgv.Rows[rowidx]
            for cellx in row.Cells:
                # 4) color each cell in row except 
                #    green cell yellow
                if cellx.ColumnIndex != colidx:
                    # skip selected cell
                    if cellx.Style.BackColor != SELECTCOLOR:
                        cellx.Style.BackColor = ROWCOLOR
                        # 5) add bold to row
                        cellx.Style.Font = BOLD
            # highlighting a column is harder
            #    have to cycle through all cells
            for num in xrange(self.numrows):
                for num2 in xrange(self.numcols):
                    # want to skip single highlighted cell
                    if num != rowidx:
                        if num2 == colidx:
                            row = self.dgv.Rows[num]
                            cell = row.Cells[num2]
                            # 6) color all other cells in column cyan
                            # skip selected cells
                            if cell.Style.BackColor != SELECTCOLOR:
                                cell.Style.BackColor = COLUMNCOLOR
    def onmouseleavingcell(self, sender, event):
        """
        Change format of data cells
        back to "normal" when mouse passes 
        out of the cell.
        """
        cell = self.getcell(event)
        rowidx, colidx = getcellidxs(event)
        if cell:
            # need to cycle through all cells individually
            #    to reset them
            resetcellfmts(self.dgv, self.numrows)
            resetheaderfmts(self.dgv, rowidx, colidx)
            clearselection(self.dgv)
    def onmouseclickcell(self, sender, event):
        """
        Attempt to override selection.
        """
        # get selected cells' data onto clipboard
        selected = []
        mockclearselection(self.dgv)
        # had to separate these into two functions
        #    problems with tuple unpacking
        cell = self.getcell(event)
        rowidx, colidx = getcellidxs(event)
        # overrides Windows selection color
        #   sometimes flashes blue for a split second
        clearselection(self.dgv)
        # if dealing with one valid data cell (not header)
        if cell:
            cell.Style.Font = BOLD 
            cell.Style.BackColor = SELECTCOLOR
            selected.append(cell.Value)
        # if a row header is clicked, select row
        if colidx == INDEXERROR:
            # need to make sure that upper left header is not clicked
            if rowidx != INDEXERROR:
                row = self.dgv.Rows[rowidx]
                cells = row.Cells
                # highlight all the cells in the row
                for cell in cells:
                    cell.Style.Font = BOLD
                    cell.Style.BackColor = SELECTCOLOR
                    selected.append(cell.Value)
                # highlight the row header
                row.HeaderCell.Style.BackColor = SELECTCOLOR
        # get mouseover coloration reset
        self.onmouseovercell(sender, event)
        copytoclipboard(selected)
            
DGF = DataGridForm(NUMCOLS, NUMROWS)
Application.Run(DGF)

Discussion

- Loading of DataGridView from an IronPython datastructure

This allows for a bit more formatting flexibility than loading data from a dot Net DataTable object. Typically I end up doing some work on the data within the script anyway (translating timestamps to human readable times, for instance), so this is the approach is the one I prefer. In the example code, I've used a nested list of words that serve as mood adjectives.

- Why is the mouseover event too slow in responding? - 10,000 cells isn't that many

A lot of cells have to be accessed to highlight the columns and rows, particularly the columns. Cells are part of the Rows collection of the DataGridView object. To access a single cell within a column, the cell's parent row must first be retrieved.

Had I included attributes for the currently mouse-over'd column and row, accessing may have been faster and bought me more cells to work with. The implementation was getting complicated and I didn't see the functionality as being worth the further trouble.

- There are already events in the DataGridView object model that deal with the row and column headers - why do I have this piece of code and use indices for them?

# hack for identifying mouseovered cell
#    mousing over one of the header cells yields
#        an event.RowIndex value of -1       
INDEXERROR = -1

This -1 index for the headers raises an error inside the code bound to the mouseover event (and other mouse events). I tried using the header cell events, but the cell events continued to respond to the mouse when it was over the headers (and give the associated error).

The -1 index for headers is also tied to this omission in the code:

        # XXX - clear button is implicit, not explicit
        self.dgv.TopLeftHeaderCell.Value = 'CLEAR'

Even though the upper left header indicates that it has a function, it is what it is NOT doing (selecting a cell within the data part of the grid) that clears the color off of the grid.

For the sake of being explicit, it would probably have been better to include the header cell events and catch whatever errors they caused in the cell events with conditionals in the methods associated with the cell events. For the purposes of this investigation, I had discovered what I needed to and did not further edit the code.

For selections, why do I use the CellMouseUp event instead of the CellMouseClick event?

Two reasons:

1) I'm abstracting my version of a selection, as opposed to using Windows' (what I've done is not a real selection, but something that looks like one to the user).

2) It was the only way I could find to override the default selection behavior, which I didn't want.

MouseUp is the last physical action of a mouse click. The timing is right for overriding any click events.

- Relative to the user data input problem described at the top of the article, did this accomplish anything?

Yes, it's actually a big improvement. Just the mock selection capability alone helps row identification and eliminates potential error in selection.

One nugget was the MSDN thread dealing with pasting to the Clipboard with Excel ready input (MSDN PostID 238181). Selection of a row with the mouse, custom highligting of the row, and copying the selected row to the clipboard is done in one mouse click. This simplifies the user process a bit and leaves less room for error. It also opens up possibilites for customized filtering and reordering of the row contents before they are pasted into another application (Excel, Access).

Notes:

1) I hope to find an opportunity to test the non-Windows part of this under Mono on Linux or FreeBSD. If anyone does get to this before I do, please share your results.

2) The basic form design and addition of the DataGridView to the form was taken from Michael Foord's DataGridView recipe earlier in the Windows Form section of this site.

3) The code and demo were done on Windows XP with dot Net 2.0 and IronPython-1.1.1

Links

1) Excel paste ready csv MSDN thread (kregger): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=238181&SiteID=1

2) CSV text to Datagridview tip (Dharmit): http://www.codeproject.com/KB/vb/DataGridView_and_CSV.aspx

3) DataGridView Class Members (MSDN): http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview_members.aspx

4) Color Members (MSDN): http://msdn2.microsoft.com/en-us/library/system.drawing.color_members.aspx

5) FontStyle enumeration (MSDN): http://msdn2.microsoft.com/en-us/library/system.drawing.fontstyle.aspx


Back to Contents.

TOOLBOX
LANGUAGES