Multiple Cell Edit Custom DataGridView

uini

Rate me:

4.85/5 (21 votes)

22 Jun 2009CPOL2 min read

149.4K

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.

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