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:
public partial class MultiEditDataGridViewPanel : UserControl
{
public MultiEditDataGridViewPanel()
{
InitializeComponent();
}
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;
}
}
void DataGridView_EditingControlShowing(object sender,
System.Windows.Forms.DataGridViewEditingControlShowingEventArgs e)
{
if ( DataGridView.CurrentCell != null )
{
e.Control.TextChanged +=
new EventHandler(CurrentCell_TextChanged);
}
}
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:
void MultiEditDataGridView_PreviewKeyDown(object sender, PreviewKeyDownEventArgs e)
{
if (e.KeyCode == Keys.Delete)
{
if (SelectedCells.Count > 0)
{
foreach (DataGridViewCell cell in SelectedCells)
{
cell.Value = cell.DefaultNewRowValue;
}
}
}
if (e.Control && e.KeyCode == Keys.C)
{
DataObject d = this.GetClipboardContent();
Clipboard.SetDataObject(d);
}
else if (e.Control && e.KeyCode == Keys.V)
{
String HtmlFormat = Clipboard.GetData("HTML Format") as String;
List<List<string>> rowContents = new List<List<string>>();
if (HtmlFormat != null)
{
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
{
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;
if (iRow + rowContents.Count > this.Rows.Count - 1)
{
int iNumNewRows = iRow + rowContents.Count - this.Rows.Count + 1;
if (this.DataSource == null)
{
this.Rows.Add(iNumNewRows);
}
else
{
try
{
BindingSource bindingSource = this.DataSource as BindingSource;
if (bindingSource != null)
{
bindingSource.CancelEdit();
for (int i = 0; i < iNumNewRows; i++)
{
Object obj = bindingSource.AddNew();
}
}
}
catch
{
}
}
}
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:
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin