Multiple Cell Edit Custom DataGridView
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:
