How can I paste data into Windows datagridview

[gprabaka]

I have a windows form application with a datagridview control. The user can edit and add records. Can someone advise me on how I can paste data in the same format on the dataviewgrid.

Thanks for your help.
Guha

 

[tfowler]

DataGridViewCopyPaste Class

Attached is a Class that I created to copy and paste DataGridView contents. Hope it helps.

Todd

 

[gprabaka]

Attached is a Class that I created to copy and paste DataGridView contents. Hope it helps.

Todd

Thank you very much. I will give this a try.
Guha

 

[gprabaka]

Todd,
I was wondering if you had a C# version of this class. I converted this class but for some reason the DataGridView class does not have the Item property on my IDE. I am on .Net Framework 2.0.
Thanks

 

[tfowler]

I was wondering if you had a C# version of this class.

Unfortunately, I don't. I pretty much work exclusively in VB (because my company requires that we use it).

The code that I sent you was created using the .NET Framework 2.0.

Sorry,

Todd

 

[gprabaka]

Thanks. I was able to translate the class to c# except I can not find the class Microsoft.VisualBasic.ControlChars.
Thanks for the help.
Guha

 

[PlausiblyDamp]

.Tab can be replaced with '\t' and .CrLf with "\r\n", or you could always reference the microsoft.visualbasic.dll and then use the vb constants.

 

[gprabaka]

I created a VB project and created a class there for just the controlchars and I was able to reference that dll in my C# project.
Thanks
Guha

 

[mskeel]

I created a VB project and created a class there for just the controlchars and I was able to reference that dll in my C# project.

It sounds like you're making everything harder for yourself. Just sub out the controlChar characters as PlausiblyDamp suggested. There should be a C# equivalent for every ControlChar in the VB code.

 

[jehanzeb_musani]

Can you post the C# version of copy/paste class?

 

[andre_rosenthal]

PART 2:


//**************************************************************************
//determine the direction in which the cells were selected
//**************************************************************************

private static selectDirection GetSelectDirection(DataGridViewSelectedCellCollection selectedCells)
{
//initialize direction to Left-Right/Top-Bottom
selectDirection direction = selectDirection.LeftToRightTopToBottom;

//get the first and last cells in the collection
DataGridViewCell initCell = selectedCells[0];
DataGridViewCell finalCell = selectedCells[selectedCells.Count - 1];

//check for column direction
bool leftToRight = true;
if (finalCell.ColumnIndex < initCell.ColumnIndex)
{
leftToRight = false;
}

//check for row direction
bool topToBottom = true;
if (finalCell.RowIndex < initCell.RowIndex)
{
topToBottom = false;
}

//set the final direction combination
if (leftToRight)
{
if (topToBottom)
{
direction = selectDirection.LeftToRightTopToBottom;
}
else
{
direction = selectDirection.LeftToRightBottomToTop;
}
}
else
{
if (topToBottom)
{
direction = selectDirection.RightToLeftTopToBottom;
}
else
{
direction = selectDirection.RightToLeftBottomToTop;
}
}

return direction;
}

//**************************************************************************
//organize the contents so that they are from Left to Right & Top to
//Bottom, as needed
//**************************************************************************

private static DataGridViewCell[,] SortCells(DataGridViewSelectedCellCollection selectedCells)
{
selectDirection direction = GetSelectDirection(selectedCells);

int rowCount = 0;
int minRow = 0;
int colCount = 0;
int minCol = 0;

//determine the column and row limits based on the selection direction
switch (direction)
{
case selectDirection.LeftToRightTopToBottom:
rowCount = selectedCells[selectedCells.Count - 1].RowIndex - selectedCells[0].RowIndex + 1;
colCount = selectedCells[selectedCells.Count - 1].ColumnIndex - selectedCells[0].ColumnIndex + 1;
minRow = selectedCells[0].RowIndex;
minCol = selectedCells[0].ColumnIndex;
break;
case selectDirection.LeftToRightBottomToTop:
rowCount = selectedCells[0].RowIndex - selectedCells[selectedCells.Count - 1].RowIndex + 1;
colCount = selectedCells[selectedCells.Count - 1].ColumnIndex - selectedCells[0].ColumnIndex + 1;
minRow = selectedCells[selectedCells.Count - 1].RowIndex;
minCol = selectedCells[0].ColumnIndex;
break;
case selectDirection.RightToLeftTopToBottom:
rowCount = selectedCells[selectedCells.Count - 1].RowIndex - selectedCells[0].RowIndex + 1;
colCount = (int)selectedCells.Count / rowCount;
minRow = selectedCells[0].RowIndex;
minCol = selectedCells[0].ColumnIndex - colCount + 1;
break;
case selectDirection.RightToLeftBottomToTop:
rowCount = selectedCells[0].RowIndex - selectedCells[selectedCells.Count - 1].RowIndex + 1;
colCount = (int)selectedCells.Count / rowCount;
minRow = selectedCells[0].RowIndex - rowCount + 1;
minCol = selectedCells[0].ColumnIndex - colCount + 1;
break;
}

//size the array
DataGridViewCell[,] cells = new DataGridViewCell[colCount, rowCount];
//fill the array
foreach (DataGridViewCell cell in selectedCells)
{
cells[cell.ColumnIndex - minCol, cell.RowIndex - minRow] = cell;
}

return cells;
}

//**************************************************************************
//copy the cell information to the clipboard
//**************************************************************************

private static void CopyToClipboard(DataGridViewCell[,] cells)
{
//create the tab and CrLf delimited string
System.Text.StringBuilder cellsTable = new System.Text.StringBuilder();
for (int r = 0; r <= cells.GetUpperBound(1); r++)
{
for (int c = 0; c <= cells.GetUpperBound(0); c++)
{
if (!(cells[c, r].Value.GetType().ToString() == "System.DBNull"))
{
cellsTable.Append((string)cells[c, r].Value + Tab);
}
else
{
cellsTable.Append(Tab);
}
}
cellsTable.Append(CrLf);
}

//send the string to the clipboard
Clipboard.SetText(cellsTable.ToString());
}

//**************************************************************************
//convert clipboard ASCII table to an array of DataGridViewCells
//**************************************************************************

private static DataGridViewCell[,] AsciiTableToDataGridViewCellsArray(string table)
{
DataGridViewCell[,] cells = null;

//parse the text into a 2-dimensional array of columns and rows
if (table.Contains(CrLf) || table.Contains(Tab))
{
//split the rows by the CrLf end of line string
string[] separator = { CrLf };
string[] rows = table.Split(separator, StringSplitOptions.None);

//determine how many columns are in each row
int colCount = 0;
int foundIndex = 1;
do
{
foundIndex = rows[0].IndexOf(Tab, foundIndex + 1);
if (foundIndex > 0)
{
colCount += 1;
}
}
while (!(foundIndex <= 0));

cells = new DataGridViewCell[colCount, rows.GetUpperBound(0) + 1];

//get the cells in each row
separator[0] = Tab;
for (int r = 0; r <= rows.GetUpperBound(0); r++)
{
string[] cellText = rows[r].Split(separator, StringSplitOptions.None);

//fill the 2-dimensional array with the cell values
for (int c = 0; c <= cellText.GetUpperBound(0); c++)
{
cells[c, r] = new DataGridViewTextBoxCell();
cells[c, r].Value = cellText[c];
}
}
}
else
{
cells = new DataGridViewCell[0, 0];

cells[0, 0] = new DataGridViewTextBoxCell();
cells[0, 0].Value = table;
}

return cells;
}
}
}

 

[andre_rosenthal]

Here is the equivalent C# code (in two parts since it is bigger than 10000 chars)

PART 1:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace FooBar
{
class ClipboardSupport
{


private static string Tab = @"\t";
private static string CrLf = System.Environment.NewLine;

private enum selectDirection
{
LeftToRightTopToBottom,
LeftToRightBottomToTop,
RightToLeftTopToBottom,
RightToLeftBottomToTop
}

//**************************************************************************
//private constructor
//**************************************************************************

private ClipboardSupport()
{
}

//**************************************************************************
//copy the contents of the selected cells
//**************************************************************************

public static DataGridViewSelectedCellCollection CopyDataGridViewCells(DataGridView dgv, bool alsoCopyToClipBoard)
{
//get the selected cells
DataGridViewSelectedCellCollection selectedCells = dgv.SelectedCells;

//if the selected cell contents are to be copied to the clipboard
if (alsoCopyToClipBoard)
{
DataGridViewCell[,] cells = SortCells(selectedCells);
CopyToClipboard(cells);
}

return selectedCells;
}

//**************************************************************************
//paste data into the selected cells
//**************************************************************************

public static void PasteDataGridViewCells(DataGridView dgv, DataGridViewSelectedCellCollection copiedCells, bool maintainColumns)
{
DataGridViewCell[,] cells = null;

if (copiedCells == null)
{
//if a DataGridViewSelectedCellCollection was not passed to the
//method, then get the contents of the clipboard
if (!Clipboard.ContainsText())
{
//the clipboard does not contain any text
return;
}

//get the text from the clipboard
string table = Clipboard.GetText();

//parse the text into a 2-dimensional array of columns and rows
cells = AsciiTableToDataGridViewCellsArray(table);
}
else
{
//make sure data is sorted from Left to Right and Top to Bottom
cells = SortCells(copiedCells);
}

if (dgv.SelectedCells.Count == 1)
{
//determine the number of rows and columns available
int availableRows = dgv.RowCount - dgv.CurrentCell.RowIndex + 1;
int availableCols = dgv.ColumnCount - dgv.CurrentCell.ColumnIndex + 1;

if (copiedCells == null || maintainColumns == false)
{
//don't worry about maintaining the columns
for (int r = 0; r <= cells.GetUpperBound(1); r++)
{
if (r > availableRows)
{
break; // TODO: might not be correct. Was : Exit For
}
for (int c = 0; c <= cells.GetUpperBound(0); c++)
{
if (c > availableCols)
{
break; // TODO: might not be correct. Was : Exit For
}
if ((cells[c, r] != null))
{
int dc = dgv.CurrentCell.ColumnIndex + c;
int dr = dgv.CurrentCell.RowIndex + r;
dgv[ dc,dr ].Value = cells[c, r].Value;
}
}
}
}
else
{
//keep track of column from which the cell was copied
for (int r = 0; r <= cells.GetUpperBound(1); r++)
{
if (r > availableRows)
{
break; // TODO: might not be correct. Was : Exit For
}
for (int c = 0; c <= cells.GetUpperBound(0); c++)
{
if ((cells[c, r] != null) && cells[c, r].ColumnIndex >= dgv.CurrentCell.ColumnIndex)
{
dgv[cells[c, r].ColumnIndex, dgv.CurrentCell.RowIndex + r].Value = cells[c, r].Value;
}
}
}
}

}
else if (dgv.SelectedCells.Count > 1)
{
//a range of cells is selected. Fill the selected range,
//truncating or repeating the copied cells, as needed

//make sure the cells are sorted Left to Right and Top to Bottom
DataGridViewCell[,] pasteCells = SortCells(dgv.SelectedCells);

//determine the number of rows and columns available
int availableRows = pasteCells.GetUpperBound(1) + 1;
int availableCols = pasteCells.GetUpperBound(0) + 1;

//copiedCells is Nothing when data is copied from somewhere else
//other than a DataGridView control
if (copiedCells == null || maintainColumns == false)
{
//don't worry about maintaining the columns
int pasteRow = 0;
while (pasteRow <= pasteCells.GetUpperBound(1))
{
for (int r = 0; r <= cells.GetUpperBound(1); r++)
{
if (r > availableRows)
{
break; // TODO: might not be correct. Was : Exit For
}

for (int c = 0; c <= cells.GetUpperBound(0); c++)
{
if (c > availableCols)
{
break; // TODO: might not be correct. Was : Exit For
}
if ((cells[c, r] != null))
{
pasteCells[c, pasteRow].Value = cells[c, r].Value;
}
}

//repeat the copied data until all of the rows
//selected for pasting are filled
pasteRow += 1;
if (pasteRow > pasteCells.GetUpperBound(1))
{
break; // TODO: might not be correct. Was : Exit For
}
}
}
}
else
{
//keep track of column from which the cell was copied
int pasteRow = 0;
while (pasteRow <= pasteCells.GetUpperBound(1))
{
for (int r = 0; r <= cells.GetUpperBound(1); r++)
{
if (r > availableRows)
{
break; // TODO: might not be correct. Was : Exit For
}

for (int c = 0; c <= cells.GetUpperBound(0); c++)
{
if ((cells[c, r] != null) && cells[c, r].ColumnIndex >= pasteCells[0, pasteRow].ColumnIndex)
{
pasteCells[c, pasteRow].Value = cells[c, r].Value;
}
}

//repeat the copied data until all of the rows
//selected for pasting are filled
pasteRow += 1;
if (pasteRow > pasteCells.GetUpperBound(1))
{
break; // TODO: might not be correct. Was : Exit For
}
}
}
}

}

return;


}

 

[loosigoosi]

Hello,

it seems there is a bug in the Clipboard text-to-table conversion subroutine...

The code uses a loop and a indexOf method over a line of text for searching the tab chars, and consider the columns count was given by the tab count.

However, a selection coming i.e. from Excel have (number of tab)+1 columns.

I think a better method is to split the line directly, and use the Ubound() method against the cellText array to get the correct redim parameter.

This is the modified code:

    '**************************************************************************
    'convert clipboard ASCII table to an array of DataGridViewCells
    '**************************************************************************

    Private Shared Function AsciiTableToDataGridViewCellsArray( _
    ByVal table As String) As DataGridViewCell(,)
        Dim cells(,) As DataGridViewCell

        'parse the text into a 2-dimensional array of columns and rows
        If table.Contains(Microsoft.VisualBasic.ControlChars.CrLf) _
        OrElse table.Contains(Microsoft.VisualBasic.ControlChars.Tab) Then
            'split the rows by the CrLf end of line string
			Dim separator() As String = {Microsoft.VisualBasic.ControlChars.CrLf}
			Dim rows() As String = table.Split(separator, StringSplitOptions.RemoveEmptyEntries)

            		' ''determine how many columns are in each row
			' ''Dim colCount As Integer = 0
			' ''Dim foundIndex As Integer = 1
			' ''        Do
			' ''			foundIndex = rows(0).IndexOf(Microsoft.VisualBasic.ControlChars.Tab, foundIndex + 1)
			' ''            If foundIndex > 0 Then
			' ''                colCount += 1
			' ''            End If
			' ''        Loop Until foundIndex <= 0

			' '''size the array to fit
			' ''ReDim cells(colCount, rows.GetUpperBound(0) + 1)

            'get the cells in each row
            separator(0) = Microsoft.VisualBasic.ControlChars.Tab.ToString
			For r As Integer = 0 To rows.GetUpperBound(0)
				Dim cellText() As String = rows(r).Split(separator, StringSplitOptions.None)

				''CHANGED: directly redim the cells array based on the actual bounds of the cellText array.
				If r = 0 Then ReDim cells(UBound(cellText), rows.GetUpperBound(0) + 1)

				'fill the 2-dimensional array with the cell values
				For c As Integer = 0 To cellText.GetUpperBound(0)
					cells(c, r) = New DataGridViewTextBoxCell
					cells(c, r).Value = cellText(c)
				Next
			Next
		Else
			'there is only a single cell
			ReDim cells(0, 0)
			cells(0, 0) = New DataGridViewTextBoxCell()
			cells(0, 0).Value = table
        End If

        Return cells
    End Function

 

[cjr9968]

You may be making it more complicated than it needs to be.
See the following VB.NET functions:

    Public Sub CopySelection()
        Clipboard.Clear()
        Clipboard.SetText(Me.SelectedCells.ToString(), TextDataFormat.Text)
    End Sub

    Public Sub Paste(ByVal overwriteSelectiona As Boolean)
        Dim str As String = Clipboard.GetText()
        Dim Rows() As String = str.Split(vbNewLine)
        Dim RowCount As Integer = 0
        Dim ColCount As Integer = 0
        Dim TrimChars() As String = New String() {vbNewLine, vbTab, " "}
        For Each row As String In Rows
                Dim RowArray() As String = row.Split(vbTab)
                RowArray(0) = RowArray(0).Trim()
                ColCount = 0
                For Each cellValue As String In RowArray
                    If (ColCount < (Me.ColumnCount - Me.SelectedCells(0).ColumnIndex) And RowCount < (Me.RowCount - Me.SelectedCells(0).RowIndex)) Then
                        Me.Rows(RowCount + Me.SelectedCells(0).RowIndex).Cells(ColCount + Me.SelectedCells(0).ColumnIndex).Value = cellValue.Trim(" " & vbNewLine & vbTab)
                    End If
                    ColCount += 1
                Next
            RowCount += 1
        Next
    End Sub

The copy function copies all selected cells as text where cells are delimited by tabs and rows are delimited by the newline character. Easily pastes into notepad or excel.
The paste function sets cells starting at the currently selected cell and overwrites all values. If the values extend beyond the available cells they are lost. Uses the same tab/newline delimiters as the copy function.

 

[thesignalman]

Being somewhat new to vb .net, I am having a little difficulty implementing this. I have a datagridview displayed and want to be able to cut copy and paste between the different rows.

Not being familiar on how to implement this, I have created a file DataGridViewCopy in which I have put the whole of the contents of the example file (Class DataGridCopyPaste) .

Within the application, I have created the Copy & Paste Icons on the toolbar. When selecting these I have tried to call CopyDataGridViewCells() for the copy. (Same for Paste). For some reason, it is saying that this is not declared.

Can some one please point me in the right direction. It is always possible that I have totally got the wrong way of implementing this.

Thanks.