Placing Datatable data into the clipboard.

2008-04-07

Question

_{Monday, April 7, 2008 3:58 PM}

Could someone please explain how to place the data of a Datatable object into the clipboard? My situation is actually a pretty straightforward one: I have a datatable object in my application in which I would Iike to have a menu item that allows the user to place the data into clipboard so that it can be pasted into either an Excel worksheet or even as plain text (headers with columns). I don't really care about special formatting or retaining the datable properties like keys or constraints.

I have put some effort into learning about the Clipboard class but I just can't quite grasp how to get it done. Do any of you know?

All replies (3)

_{Monday, April 7, 2008 4:36 PM ✅Answered}

I found this on codeproject:

http://www.codeproject.com/KB/shell/clipboard02.aspx

maybe there's something in it that will help.

_{Monday, April 7, 2008 4:41 PM ✅Answered}

Hi Digboy,

Simple example

Code Snippet

        'Data
        Dim dt As New DataTable
        dt.Columns.Add("valA", GetType(String))
        dt.Columns.Add("valB", GetType(String))
        dt.Columns.Add("valC", GetType(String))
        dt.Rows.Add(New String() {"1", "test", "yes"})

        'Add headers
        Dim result As String = ""
        For Each col As DataColumn In dt.Columns
            If col Is dt.Columns.Item(dt.Columns.Count - 1) Then
                result &= col.ColumnName & vbCrLf
            Else
                result &= col.ColumnName & ","
            End If
        Next

        'Convert data to string
        Dim s() As Object = dt.Rows.Item(0).ItemArray
        For i As Int32 = 0 To s.Length - 1
            If i = s.Length - 1 Then
                result &= s(i)
            Else
                result &= s(i) & ","
            End If
        Next

'Set clipboard
Clipboard.SetText(result)

_{Monday, April 7, 2008 6:02 PM}

Hey guys, thanks for the quick responses.

Ralf, I gave your code a try and found that when the resulting text was pasted into Excel it was understood as a single text value, so only a single cell of comma-delimited values was pasted. I even tried using the TextDataFormat.CommaSeparatedValue flag but that just dumped in unreadable text (geez, it seems like that should have worked too). As a result I tried using the VbTab constant as a delimiter and that was definitley understood by Excel. So the following code ended up being a solution to the problem -- thanks for leading me there...

Code Snippet

'Note: This is a custom collection class for my Invoice objects -- ToTable is a method that exports

' all the collection objects to a table format.

Dim dt As DataTable = Invoices.ToTable

Dim result As String = ""

For Each c As DataColumn In dt.Columns

result &= c.ColumnName & vbTab

result &= vbCrLf

For Each r As DataRow In dt.Rows

For Each c As DataColumn In dt.Columns

Try

result &= r(c).ToString

Finally

result &= vbTab

End Try

result &= vbCrLf

'Set clipboard

Clipboard.SetText(result)

Rick, I looked over the article you sent from CodeProject and now have a better idea of how implementing the IDataObject interface works. I read about the interface when learning about the clipboard functions but wasn't exactly sure how to make it work for me. Since my data's original format is actually a collection of custom class objects this article (part II) is very relevant to my situation. I was using a table to dumb it down a bit to something I thought was more easily digestable by clipboard but apparently is not. In fact, I'd probably do better to have a Sub called SetToClipboard on my Invoices custom collection class rather than bothering with ToTable.

The article mentions registering a custom format with Windows and in the code example all I see is it registering the name of a type through the DataFormats.GetFormat method. I tried this but could not get Excel (or Notepad) to even register anything in the clipboard. In teh follwing example I am placing a bunch of PostLine objects (marked as serializable) into an IDataObject object and then placing that object into the clipboard. Neither the SetData or SetDataObject methods of Clipboard worked for me. Any idea what I'm doing wrong here? The PostLine class is nothing special, just a few strings and numeric properties...

Code Snippet

Dim ido As IDataObject = New DataObject

Dim fmt As Windows.Forms.DataFormats.Format = Windows.Forms.DataFormats.GetFormat(GetType(PostLine).FullName)

For Each pl As PostLine In Invoices

'Neither of these methods worked -- no data is registered in the clipboard.

ido.SetData(fmt.Name, pl)

ido.SetData(GetType(PostLine), pl)

Clipboard.SetDataObject(ido)

שתף באמצעות

Placing Datatable data into the clipboard.

Question

All replies (3)

משאבים נוספים