DataGridView Custom Formatting

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" 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.









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.

""" 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. """ 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 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 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)
 * 1) cellfmt.py
 * 1) XXX - row/column tracking is slow with
 * 2) XXX       more than 500 rows
 * 3) XXX   could try to optimize by tracking cell indices
 * 1) XXX - PageDown, PagUp, Home, and Arrow Keys will
 * 2) XXX       still highlight and select cells in the
 * 3) XXX          Windows default fashion.
 * 1) XXX - could write more code for column header width
 * 2) XXX       adjustment and row width adjustment
 * 3) XXX       - columnwidth adjustment clears mock selection
 * 4) XXX       - rowwidth adjustment selects row above row
 * 5) XXX             boundary
 * 1) formatting constants
 * 1) hack for identifying mouseovered cell
 * 2)    mousing over one of the header cells yields
 * 3)        an event.RowIndex value of -1

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?

INDEXERROR = -1
 * 1) hack for identifying mouseovered cell
 * 2)    mousing over one of the header cells yields
 * 3)        an event.RowIndex value of -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.