Multiple Cell Edit Custom DataGridView

uini

Rate me:

4.85/5 (21 votes)

22 Jun 2009CPOL2 min read

149.7K

10.7K

112

Custom DataGridView with multi-cell copy/paste and Excel autofill drag and drop.

Introduction

This is a custom DataGridView that has some of Excel's useful behaviours. It features auto-fill, multiple cell copy and paste, and find and replace.

This custom DataGridView has been used in my applications that have both DataTable or BindingList as data binding sources, or with no data binding.

Using the code

MultiEditDataGridViewPanel

MultiEditDataGridViewPanel is a panel that contains a MultiEditDataGridView. It has a text bar on the top of the DataGridView for a cell editing bar similar to Excel's formula bar, as shown below:

The code snippet for the cell editing bar is shown below:

/// <summary>
/// MultiEditDataGridViewPanel is a panel that contains a MultiEditDataGridView.
/// It has a text bar on the top of the data grid view,
/// for a cell editing bar similar to excel's formula bar,
/// </summary>
public partial class MultiEditDataGridViewPanel : UserControl
{
    public MultiEditDataGridViewPanel()
    {
        InitializeComponent();
    }

    /// <summary>
    /// When the current cell change, bind the current cell's value
    /// to CellTextBox (the cell editing bar) 
    /// so that updates to the CellTextBox will update the current cell values.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void DataGridView_CurrentCellChanged(object sender, System.EventArgs e)
    {
        CellTextBox.DataBindings.Clear();
        if (DataGridView.CurrentCell != null)
        {                
            CellTextBox.DataBindings.Add("Text", 
              DataGridView.CurrentCell, "Value", true, 
              DataSourceUpdateMode.OnPropertyChanged);
            CellTextBox.ReadOnly = DataGridView.CurrentCell.ReadOnly;            
        }
    }

    /// <summary>
    /// When the editing control is showing, add a listener
    /// to the control's text changed event,
    /// to update the CellTextBox when the current cell changes.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void DataGridView_EditingControlShowing(object sender, 
         System.Windows.Forms.DataGridViewEditingControlShowingEventArgs e)
    {
        if ( DataGridView.CurrentCell != null )
        {
            e.Control.TextChanged += 
               new EventHandler(CurrentCell_TextChanged);
        }
    }

    /// <summary>
    /// Triggered when the current cell changes through the editing control
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void CurrentCell_TextChanged(object sender, EventArgs e)
    {
        TextBox tb = sender as TextBox;
        CellTextBox.Text = tb.Text;
    }
}

MultiEditDataGridView

MultiEditDataGridView is the custom DataGridView with multiple cell copy/pasting and auto-filling functionality. It is optional to use MultiEditDataGridView by itself or embedded in MultiEditDataGridViewPanel.

Multiple Cell Copy/Paste and Clear

Multiple cells copy and paste across applications or within the DataGridView by simply using Ctrl-C and Ctrl-V; hitting Delete on selected cells will restore the cell to its default cell value. If appropriate, it will copy and paste data from the clipboard with "HTML format" so that the rows are intact (no extra rows are pasted resulting from new lines in multi-line cells). A listener to the DataGridView's PreviewKeyDown event is used to do this:

/// <summary>
/// Capture Ctrl+C, Ctrl+V, and delete for multiple cell copy and paste
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void MultiEditDataGridView_PreviewKeyDown(object sender, PreviewKeyDownEventArgs e)
{
    // Delete, restore cell's default row value
    // If you have a custom cell, override object DefaultNewRowValue { get; }
    if (e.KeyCode == Keys.Delete)
    {
        if (SelectedCells.Count > 0)
        {
            foreach (DataGridViewCell cell in SelectedCells)
            {
                cell.Value = cell.DefaultNewRowValue;
            }
        }
    }
    // Copy
    if (e.Control && e.KeyCode == Keys.C)
    {
        DataObject d = this.GetClipboardContent();                
        Clipboard.SetDataObject(d);                
    }
    // Paste
    else if (e.Control && e.KeyCode == Keys.V)
    {
        // Try to process as html format (data from excel) since
        // it keeps the row information intact, instead of assuming
        // a new row for every new line if we just process it as text
        String HtmlFormat = Clipboard.GetData("HTML Format") as String;
        List<List<string>> rowContents = new List<List<string>>();
        if (HtmlFormat != null)
        {
            // Remove html tags to just extract row information
            // and store it in rowContents
            System.Text.RegularExpressions.Regex TRregex = 
              new System.Text.RegularExpressions.Regex(@"<( )*tr([^>])*>", 
              System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            System.Text.RegularExpressions.Regex TDregex = 
              new System.Text.RegularExpressions.Regex(@"<( )*td([^>])*>", 
              System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            System.Text.RegularExpressions.Match trMatch = TRregex.Match(HtmlFormat);
            while (!String.IsNullOrEmpty(trMatch.Value))
            {
                int rowStart = trMatch.Index + trMatch.Length;
                int rowEnd = HtmlFormat.IndexOf("</tr>", rowStart, 
                             StringComparison.InvariantCultureIgnoreCase);
                System.Text.RegularExpressions.Match tdMatch = 
                       TDregex.Match(HtmlFormat, rowStart, rowEnd - rowStart );
                List<string> rowContent = new List<string>();
                while ( !String.IsNullOrEmpty(tdMatch.Value) )
                {
                    int cellStart = tdMatch.Index + tdMatch.Length;
                    int cellEnd = HtmlFormat.IndexOf("</td>", 
                        cellStart, StringComparison.InvariantCultureIgnoreCase);
                    String cellContent = 
                        HtmlFormat.Substring(cellStart, cellEnd - cellStart);
                    cellContent = System.Text.RegularExpressions.Regex.Replace(cellContent, 
                        @"<( )*br( )*>", "\r\n", 
                        System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    cellContent = System.Text.RegularExpressions.Regex.Replace(cellContent, 
                        @"<( )*li( )*>", "\r\n", 
                        System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    cellContent = System.Text.RegularExpressions.Regex.Replace(cellContent, 
                        @"<( )*div([^>])*>", "\r\n\r\n", 
                        System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    cellContent = System.Text.RegularExpressions.Regex.Replace(cellContent, 
                        @"<( )*p([^>])*>", "\r\n\r\n", 
                        System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    cellContent = cellContent.Replace(" "," ");
                    rowContent.Add(cellContent);
                    tdMatch = tdMatch.NextMatch();
                }
                if (rowContent.Count > 0)
                {
                    rowContents.Add(rowContent);
                }
                trMatch = trMatch.NextMatch();
            }
        }
        else
        {
            // Clipboard is not in html format, read as text
            String CopiedText = Clipboard.GetText();
            String[] lines = CopiedText.Split('\n');                   
            foreach (string line in lines)
            {                        
                List<string> rowContent = 
                      new List<string>(line.Split('\t'));
                if (rowContent.Count > 0)
                {
                    rowContents.Add(rowContent);
                }
            }
        }
        int iRow = this.CurrentCell.RowIndex;
        // DataGridView's rowCount has one extra row (the temporary new row)
        if (iRow + rowContents.Count > this.Rows.Count - 1)
        {
            int iNumNewRows = iRow + rowContents.Count - this.Rows.Count + 1;
            // Simply add to a new row to the datagridview
            // if it is not binded to a datasource
            if (this.DataSource == null)
            {
                this.Rows.Add(iNumNewRows);
            }
            // Otherwise, add rows to binded data source
            else
            {
                try
                {                            
                    BindingSource bindingSource = this.DataSource as BindingSource;
                    if (bindingSource != null)
                    {
                        // This is important!!  
                        // Cancel Edit before adding new entries into bindingsource
                        // If the UI is currently adding a new line
                        // (you have your cursor on the last time)
                        // You will System.InvalidOperationException
                        bindingSource.CancelEdit();
                        for (int i = 0; i < iNumNewRows; i++)
                        {
                            Object obj = bindingSource.AddNew();
                        }
                    }
                }
                catch
                {
                    // failed adding row to binding data source
                    // It was okay for my application to ignore the error
                }
            }
        }
        // paste the data starting at the current cell
        foreach ( List<String> rowContent in rowContents)
        {
            int iCol = this.CurrentCell.ColumnIndex;
            foreach (String cellContent in rowContent)
            {
                try
                {
                    if (iCol < this.Columns.Count)
                    {
                        DataGridViewCell cell = this[iCol, iRow];
                        if ( !cell.ReadOnly )
                        {
                            cell.Value = Convert.ChangeType(cellContent, cell.ValueType);
                        }
                    }
                }
                catch
                {

                }
                iCol++;
            }
            iRow++;
            if (iRow >= this.Rows.Count)
            {
                break;
            }
        }
    }
}

Auto-Filling

The image below shows auto-filling a selected cell across the columns. This can be done by right-clicking on the selected cells, then drag and drop.

The image below shows auto-filling the selected cells across the columns. This can be done by right-clicking on the selected cells, then drag and drop.

The implementation of auto filling is fairly simple. The starting row and column indexes is stored on OnMouseDown and the drag and drop begins. The ending row and column indexes are updated OnDragOver. In OnDragDrop, I loop through the cells within the red box and copy the template cells' values to them. OnCellPainting is overridden to display the red and blue boxes during the drag and drop operation.

FindAndReplaceForm

FindAndReplaceForm is a form that contains find and replace functionality that works with any DataGridView. The form is shown in the image below:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

uini

Canada

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Private m_AutoFillRowStartIndex As Integer Private m_AutoFillColumnStartIndex As Integer Private m_AutoFillRowEndIndex As Integer Private m_AutoFillColumnEndIndex As Integer Private m_AutoFillMultipleCells As Boolean Private m_AutoFillRowStartIndex2 As Integer Private m_AutoFillColumnStartIndex2 As Integer Private Sub dgMain_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgMain.MouseDown Dim hitTest As DataGridView.HitTestInfo = dgMain.HitTest(e.X, e.Y) If hitTest.Type = DataGridViewHitTestType.Cell And e.Button = MouseButtons.Right Then Dim DragSize As Size = SystemInformation.DragSize If dgMain.SelectedCells.Count > 1 Then m_AutoFillMultipleCells = True Dim pCell As DataGridViewCell For Each pCell In dgMain.SelectedCells If m_AutoFillRowStartIndex = -1 Or pCell.RowIndex <= m_AutoFillRowStartIndex Then m_AutoFillRowStartIndex = pCell.RowIndex End If If m_AutoFillRowStartIndex2 = -1 Or pCell.RowIndex >= m_AutoFillRowStartIndex Then m_AutoFillRowStartIndex2 = pCell.RowIndex End If If m_AutoFillColumnStartIndex = -1 Or pCell.ColumnIndex <= m_AutoFillColumnStartIndex Then m_AutoFillColumnStartIndex = pCell.ColumnIndex End If If m_AutoFillColumnStartIndex2 = -1 Or pCell.ColumnIndex >= m_AutoFillColumnStartIndex2 Then m_AutoFillColumnStartIndex2 = pCell.ColumnIndex End If m_AutoFillRowEndIndex = m_AutoFillRowStartIndex2 m_AutoFillColumnEndIndex = m_AutoFillColumnStartIndex2 Next Else m_AutoFillMultipleCells = False m_AutoFillRowStartIndex = hitTest.RowIndex m_AutoFillColumnStartIndex = hitTest.ColumnIndex m_AutoFillRowEndIndex = hitTest.RowIndex m_AutoFillColumnEndIndex = hitTest.ColumnIndex End If dgMain.DoDragDrop(dgMain.Rows(hitTest.RowIndex).Cells(hitTest.ColumnIndex), DragDropEffects.Copy) Else m_AutoFillMultipleCells = False m_AutoFillRowStartIndex = -1 m_AutoFillColumnStartIndex = -1 m_AutoFillRowEndIndex = -1 m_AutoFillColumnEndIndex = -1 m_AutoFillColumnStartIndex2 = -1 m_AutoFillRowStartIndex2 = -1 End If Me.OnMouseDown(e) End Sub Private Sub dgMain_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles dgMain.DragOver If Not m_AutoFillColumnStartIndex = -1 And Not m_AutoFillRowStartIndex = -1 Then e.Effect = DragDropEffects.Copy Dim MousePos As Point = dgMain.PointToClient(New Point(e.X, e.Y)) Dim Column As Integer = dgMain.HitTest(MousePos.X, MousePos.Y).ColumnIndex Dim Row As Integer = dgMain.HitTest(MousePos.X, MousePos.Y).RowIndex If Not Column = -1 And Not Row = -1 Then If Row = dgMain.RowCount - 1 Then Row = Row - 1 End If If Not m_AutoFillMultipleCells Then m_AutoFillColumnEndIndex = Column m_AutoFillRowEndIndex = Row End If Else m_AutoFillColumnEndIndex = Column m_AutoFillRowEndIndex = Row If Column > m_AutoFillColumnStartIndex2 Or Column < m_AutoFillColumnStartIndex Then m_AutoFillRowEndIndex = m_AutoFillRowStartIndex2 ElseIf Row > m_AutoFillRowStartIndex2 Or Column < m_AutoFillRowStartIndex Then m_AutoFillColumnEndIndex = m_AutoFillColumnStartIndex2 End If If Row >= m_AutoFillRowStartIndex And Row <= m_AutoFillRowStartIndex2 Then m_AutoFillRowEndIndex = m_AutoFillRowStartIndex2 End If If Column >= m_AutoFillColumnStartIndex And Column <= m_AutoFillColumnStartIndex2 Then m_AutoFillColumnEndIndex = m_AutoFillColumnStartIndex2 End If End If dgMain.Invalidate() End If Me.OnDragOver(e) End Sub Private Sub dgMain_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles dgMain.DragDrop If Not m_AutoFillMultipleCells Then If Not m_AutoFillColumnStartIndex = -1 And Not m_AutoFillRowStartIndex = -1 And Not m_AutoFillColumnEndIndex = -1 And Not m_AutoFillRowEndIndex = -1 Then Dim StartRow, EndRow, StartColumn, EndColumn As Integer Dim SourceCellValue As Object = dgMain.Rows(m_AutoFillRowStartIndex).Cells(m_AutoFillColumnStartIndex).Value If m_AutoFillColumnStartIndex > m_AutoFillColumnEndIndex Then StartColumn = m_AutoFillColumnEndIndex EndColumn = m_AutoFillColumnStartIndex Else StartColumn = m_AutoFillColumnStartIndex EndColumn = m_AutoFillColumnEndIndex End If If m_AutoFillRowStartIndex > m_AutoFillRowEndIndex Then StartRow = m_AutoFillRowEndIndex EndRow = m_AutoFillRowStartIndex Else StartRow = m_AutoFillRowStartIndex EndRow = m_AutoFillRowEndIndex End If Dim i As Integer = 0 Do Until i = EndRow - StartRow + 1 Dim j As Integer = 0 Do Until j = EndColumn - StartColumn + 1 Try If Not dgMain.Rows(StartRow + i).Cells(StartColumn + j).ReadOnly Then dgMain.Rows(StartRow + i).Cells(StartColumn + j).Value = Convert.ChangeType(SourceCellValue, dgMain.Rows(StartRow + i).Cells(StartColumn + j).ValueType) End If Catch End Try j = j + 1 Loop i = i + 1 Loop Else If Not m_AutoFillColumnStartIndex = -1 And Not m_AutoFillRowStartIndex = -1 And Not m_AutoFillColumnEndIndex = -1 And Not m_AutoFillRowEndIndex = -1 Then If m_AutoFillRowEndIndex > m_AutoFillRowStartIndex2 Then Dim iNumRows As Integer = m_AutoFillRowStartIndex2 - m_AutoFillRowStartIndex + 1 Dim iRows As Integer = 0 For i As Integer = m_AutoFillRowStartIndex2 + 1 To i <= m_AutoFillRowEndIndex For j As Integer = m_AutoFillColumnStartIndex To j <= m_AutoFillColumnStartIndex2 Dim SourceCellValue As Object = dgMain.Rows(m_AutoFillRowStartIndex + iRows).Cells(j).Value Try If Not dgMain.Rows(i).Cells(j).ReadOnly Then dgMain.Rows(i).Cells(j).Value = Convert.ChangeType(SourceCellValue, dgMain.Rows(i).Cells(j).ValueType) End If Catch End Try j = j + 1 Next If iRows >= iNumRows Then iRows = 0 End If iRows = iRows + 1 i = i + 1 Next ElseIf m_AutoFillRowEndIndex < m_AutoFillRowStartIndex Then Dim iNumRows As Integer = m_AutoFillRowStartIndex2 - m_AutoFillRowStartIndex + 1 Dim iRows As Integer = 0 For i As Integer = m_AutoFillRowStartIndex - 1 To i >= m_AutoFillRowEndIndex For j As Integer = m_AutoFillColumnStartIndex To j <= m_AutoFillColumnStartIndex2 Dim SourceCellValue As Object = dgMain.Rows(m_AutoFillRowStartIndex2 - iRows).Cells(j).Value Try If Not dgMain.Rows(i).Cells(j).ReadOnly Then dgMain.Rows(i).Cells(j).Value = Convert.ChangeType(SourceCellValue, dgMain.Rows(i).Cells(j).ValueType) End If Catch End Try j = j + 1 Next If iRows >= iNumRows Then iRows = 0 End If iRows = iRows + 1 i = i - 1 Next ElseIf m_AutoFillColumnEndIndex > m_AutoFillColumnStartIndex2 Then Dim iNumColumns As Integer = m_AutoFillColumnStartIndex2 - m_AutoFillColumnStartIndex + 1 Dim iColumns As Integer = 0 For i As Integer = m_AutoFillColumnStartIndex2 + 1 To i <= m_AutoFillColumnEndIndex For j As Integer = m_AutoFillRowStartIndex To j <= m_AutoFillRowStartIndex2 Dim SourceCellValue As Object = dgMain.Rows(m_AutoFillColumnStartIndex + iColumns).Cells(j).Value Try If Not dgMain.Rows(i).Cells(j).ReadOnly Then dgMain.Rows(i).Cells(j).Value = Convert.ChangeType(SourceCellValue, dgMain.Rows(i).Cells(j).ValueType) End If Catch End Try j = j + 1 Next If iColumns >= iNumColumns Then iColumns = 0 End If iColumns = iColumns + 1 i = i + 1 Next ElseIf m_AutoFillColumnEndIndex < m_AutoFillColumnStartIndex Then Dim iNumColumns As Integer = m_AutoFillColumnStartIndex2 - m_AutoFillColumnStartIndex + 1 Dim iColumns As Integer = 0 For i As Integer = m_AutoFillColumnStartIndex - 1 To i >= m_AutoFillColumnEndIndex For j As Integer = m_AutoFillRowStartIndex To j <= m_AutoFillRowStartIndex2 Dim SourceCellValue As Object = dgMain.Rows(m_AutoFillColumnStartIndex2 - iColumns).Cells(j).Value Try If Not dgMain.Rows(i).Cells(j).ReadOnly Then dgMain.Rows(i).Cells(j).Value = Convert.ChangeType(SourceCellValue, dgMain.Rows(i).Cells(j).ValueType) End If Catch End Try j = j + 1 Next If iColumns >= iNumColumns Then iColumns = 0 End If iColumns = iColumns + 1 i = i - 1 Next End If End If End If End If m_AutoFillMultipleCells = False m_AutoFillRowStartIndex = -1 m_AutoFillColumnStartIndex = -1 m_AutoFillRowEndIndex = -1 m_AutoFillColumnEndIndex = -1 m_AutoFillColumnStartIndex2 = -1 m_AutoFillRowStartIndex2 = -1 dgMain.Invalidate() dgMain.EndEdit() Me.OnDragDrop(e) End Sub Private Sub dgMain_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles dgMain.CellPainting If Not m_AutoFillMultipleCells Then If Not m_AutoFillColumnStartIndex = -1 And Not m_AutoFillRowStartIndex = -1 And Not m_AutoFillColumnEndIndex = -1 And Not m_AutoFillRowEndIndex = -1 Then Dim pen As Pen = New Pen(Color.Red, 1) Dim pen2 As Pen = New Pen(Color.Blue, 2) Dim StartRectangle As Rectangle = dgMain.GetCellDisplayRectangle(m_AutoFillColumnStartIndex, m_AutoFillRowStartIndex, False) Dim EndRectangle As Rectangle = dgMain.GetCellDisplayRectangle(m_AutoFillColumnEndIndex, m_AutoFillRowEndIndex, False) Dim Width, Height, X, Y As Integer If (EndRectangle.X > StartRectangle.X) Then Width = EndRectangle.X - StartRectangle.X + EndRectangle.Width X = StartRectangle.X Else Width = StartRectangle.X - EndRectangle.X + StartRectangle.Width X = EndRectangle.X End If If (EndRectangle.Y > StartRectangle.Y) Then Height = EndRectangle.Y - StartRectangle.Y + EndRectangle.Height Y = StartRectangle.Y Else Height = StartRectangle.Y - EndRectangle.Y + StartRectangle.Height Y = EndRectangle.Y End If Dim AutoFillRectangle As Rectangle = New Rectangle(X, Y, Width, Height) e.Graphics.DrawRectangle(pen, AutoFillRectangle) e.Graphics.DrawRectangle(pen2, StartRectangle) End If Else If Not m_AutoFillColumnStartIndex = -1 And Not m_AutoFillRowStartIndex = -1 And Not m_AutoFillColumnEndIndex = -1 And Not m_AutoFillRowEndIndex = -1 Then Dim pen As Pen = New Pen(Color.Red, 1) Dim pen2 As Pen = New Pen(Color.Blue, 2) Dim StartRectangle As Rectangle = dgMain.GetCellDisplayRectangle(m_AutoFillColumnStartIndex, m_AutoFillRowStartIndex, False) Dim StartRectangle2 As Rectangle = dgMain.GetCellDisplayRectangle(m_AutoFillColumnStartIndex2, m_AutoFillRowStartIndex2, False) Dim EndRectangle As Rectangle = dgMain.GetCellDisplayRectangle(m_AutoFillColumnEndIndex, m_AutoFillRowEndIndex, False) Dim Width, Height, X, Y As Integer If (EndRectangle.X > StartRectangle.X) Then Width = EndRectangle.X - StartRectangle.X + EndRectangle.Width X = StartRectangle.X Else Width = StartRectangle.X - EndRectangle.X + StartRectangle.Width X = EndRectangle.X End If If (EndRectangle.Y > StartRectangle.Y) Then Height = EndRectangle.Y - StartRectangle.Y + EndRectangle.Height Y = StartRectangle.Y Else Height = StartRectangle.Y - EndRectangle.Y + StartRectangle.Height Y = EndRectangle.Y End If Dim AutoFillRectangle As Rectangle = New Rectangle(X, Y, Width, Height) e.Graphics.DrawRectangle(pen, AutoFillRectangle) e.Graphics.DrawRectangle(pen2, New Rectangle(StartRectangle.X, StartRectangle.Y, StartRectangle2.X - StartRectangle.X + StartRectangle2.Width, StartRectangle2.Y - StartRectangle.Y + StartRectangle2.Height)) End If End If End Sub

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.