Click here to Skip to main content
Click here to Skip to main content

Multiple Cell Edit Custom DataGridView

, 22 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

datagridview_normal.jpg

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.

datagridview_autofill1.jpg

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.

datagridview_autofill2.jpg

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:

datagridview_findandreplace.jpg

License

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

Share

About the Author

uini

Canada Canada
No Biography provided

Comments and Discussions

 
Questionfind and replace form by vb.net & you very poor programmer because you didn't answer for anyone and you Failure Pinmemberahamedgad19-Jan-14 9:33 
QuestionFlickering? Pinmemberolssoninc20-Aug-13 22:02 
GeneralMy vote of 5 Pinmembermanoj kumar choubey27-Mar-12 22:30 
Questionhorizontal scroll is not working while auto-fill Pinmemberkamsri15322-Mar-12 3:47 
QuestionAwesome stuff. But i got a question. Pinmemberwhoppwhopp21-Mar-12 20:53 
GeneralMy vote of 5 Pinmemberkamsri15320-Mar-12 4:11 
QuestionHtmlDecode fix Pinmemberkarxex9-Jan-12 0:03 
GeneralVB .Net conversion for Auto fill Datagrid. PinmemberRakesh_Sharma232-Feb-11 21:31 
GeneralMy vote of 5 PinmemberJunfengGuo21-Dec-10 16:06 
GeneralNeed to alter the Start Row for pasting Pinmembercoghlans19-May-10 2:36 
GeneralIt doesn't work properly for me Pinmembersynbari2-Mar-10 10:12 
GeneralHai Pinmembernitharsan5-Feb-10 13:23 
GeneralRe: Hai Pinmemberkamsri15320-Mar-12 4:09 
GeneralFantastic job Pinmemberash20935-Feb-10 1:50 
Generalgood work~ PinmemberMember 402346621-Oct-09 22:37 
GeneralGood work Pinmemberhijack52022-Sep-09 20:57 
GeneralMultiple Cell Edit Custom DataGridView Pinmemberincognitodave24-Jul-09 9:44 
GeneralRe: Multiple Cell Edit Custom DataGridView Pinmemberuini24-Jul-09 18:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 22 Jun 2009
Article Copyright 2009 by uini
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid