How can I paste data into Windows datagridview

gprabaka · July 19, 2006

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 · July 19, 2006

DataGridViewCopyPaste Class

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

Todd

DataGridViewCopyPaste.zip

gprabaka · July 19, 2006

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 · July 19, 2006

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 · July 20, 2006

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 · July 20, 2006

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 · July 20, 2006

.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 · July 20, 2006

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 · July 20, 2006

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.

:eek:

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 · November 1, 2008

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

andre_rosenthal · May 27, 2009

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 · May 27, 2009

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 · March 26, 2010

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 · May 20, 2010

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 · July 18, 2010

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.

Sign In

How can I paste data into Windows datagridview

Recommended Posts

gprabaka

tfowler

gprabaka

gprabaka

tfowler

gprabaka

PlausiblyDamp

gprabaka

mskeel

jehanzeb_musani

andre_rosenthal

andre_rosenthal

loosigoosi

cjr9968

thesignalman

Join the conversation

Browse

Activity