Datagridview to Clipboard Optimization

Question

I'm not asking how to copy a datagrid to my clipboard, but more of optimization of my code.

This is what I have to copy my datagridview to my clipboard so I can paste it's contents in excel.

    Dim dgv As DataGridView = dgvData
    Dim s As String = ""
    Dim oCurrentCol As DataGridViewColumn    'Get header
    oCurrentCol = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)
    oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)

    Do
        s &= oCurrentCol.HeaderText & Chr(Keys.Tab)
        oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
    Loop Until oCurrentCol Is Nothing

    s = s.Substring(0, s.Length - 1)
    s &= Environment.NewLine    'Get rows
    For Each row As DataGridViewRow In dgv.Rows
        oCurrentCol = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)
        oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
        Do
            If row.Cells(oCurrentCol.Index).Value IsNot Nothing Then
                s &= row.Cells(oCurrentCol.Index).Value.ToString
            End If
            s &= Chr(Keys.Tab)
            oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
        Loop Until oCurrentCol Is Nothing
        s = s.Substring(0, s.Length - 1)
        s &= Environment.NewLine
    Next    'Put to clipboard

    Dim o As New DataObject
    o.SetText(s)
    Clipboard.SetDataObject(o, True)

This works great! Does exactly what I want it do, including leaving out the first column, and looks perfect when pasted into excel.

Now the problem I'm having is it's extremely slow. On my development computer, it will take almost two minutes to copy ~10,000 rows of 10 columns. On some of our older servers I let it run from over ten minutes and eventually just killed the process because it was taking so long.

I don't know what I could do to speed the process up. Any help is appreciated!

Thanks!

At that size, maybe it would be better to skip the clipboard and maybe write the contents to a file. That said, StringBuilder would probably help. — LarsTech, Sep 22 '15 at 15:53
I don't have a ton of experience with StringBuilder besides very very basics of it, but is there ways that I can append things into the string so the user is able to paste the contents into an excel spreadsheet to mimic the datagridview? — Timmy, Sep 22 '15 at 15:57
StringBuilder is pretty simple to use: `s.Append(blah blah blah)` then convert it to a string in the end: `s.ToString()`. — LarsTech, Sep 22 '15 at 16:00
Firstly, your `GetFirstColumn` (and similar) approach seems quite inefficient. You should better just loop through all the columns/rows and choose the adequate ones by checking the corresponding properties (´Visible´?). On the other hand, the way in which you are communicating with Excel is certainly not-ideal; mainly when involving so many records. Relying on the clipboard shouldn't ever be an option-A alternative unless under very specific conditions (and much smaller data sizes). Why not writing to Excel directly while looping through the columns/rows? — varocarbas, Sep 22 '15 at 16:05
Additionally, if you are using the `DataSource` to populate the `DataGridView`, you might prefer to rely on it directly because the associated object (`DataTable`) is much more adaptable than `DataGridView`. — varocarbas, Sep 22 '15 at 16:12
@varocarbas Thanks! I'm going to dig into your approaches a little more. I'm updating an app which I didn't write initially, so one of the features is a filter that can change the data shown in the datagridview. It sounds like writing to excel directly might be the way to go. I'll have to get exact stipulations on what they want and hopefully head down that path. I'm going to take your suggestions and run with them. If I run into another dead end I'll be back! Thanks! — Timmy, Sep 22 '15 at 16:21
You are welcome. Filters can be problematic; in theory, you should be able to get the displayed information from the DataView (I understand that this is how you are filtering), but I do prefer to look at the DataGridView to make sure. In any case, I think (-> better confirm this point) that all the information you see when iterating through the DataGridView cells is filtered (what is not the case with the default DataSource). Writing to a text (CSV) file and converting (i.e., SaveAs) it to Excel format would also be notable faster; this is not always recommendable, but sounds fine here. — varocarbas, Sep 22 '15 at 16:59
Lars, if you want to post some sort of answer I would be glad to accept it. — Timmy, Sep 22 '15 at 17:50

Timmy · Accepted Answer · 2015-09-22T18:57:01.023

So, given my circumstances I ended swapping my string string with StringBuilder. To copy the ~10000 I went from 2 minutes to almost instantaneous! After reading up on how string builder works, it seemed very efficient for what I was doing. I was extremely surprised to see what a difference it actually made! Here are the minor changes I made.

Even on our old servers it's almost instantaneous which is huge compared to the 15+ minutes it was going to take.

    Dim dgv As DataGridView = dgvData
    Dim s As New StringBuilder
    Dim oCurrentCol As DataGridViewColumn = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)   'Get header

    oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)

    Do
        s.Append(oCurrentCol.HeaderText & Chr(Keys.Tab))
        oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
    Loop Until oCurrentCol Is Nothing

    s.Append(Environment.NewLine)    'Get rows
    For Each row As DataGridViewRow In dgv.Rows
        oCurrentCol = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)
        oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
        Do
            If row.Cells(oCurrentCol.Index).Value IsNot Nothing Then
                s.Append(row.Cells(oCurrentCol.Index).Value.ToString)
            End If
            s.Append(Chr(Keys.Tab))
            oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
        Loop Until oCurrentCol Is Nothing
        s.Append(Environment.NewLine)
    Next    'Put to clipboard

    Dim o As New DataObject
    o.SetText(s.ToString())
    Clipboard.SetDataObject(o, True)

score 1 · Answer 2 · answered Sep 22 '15 at 17:49

1

Here's the code I use to move data to Excel via the clipboard.

   Private Sub tsbtnCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnCopy.Click
    Try
        If dgv01.Rows.Count = 0 Then Exit Sub
        dgv01.SuspendLayout()
        dgv01.RowHeadersVisible = False
        If dgv01.SelectedRows.Count = 0 Then dgv01.SelectAll()
        Clipboard.SetDataObject(dgv01.GetClipboardContent())
        dgv01.ClearSelection()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        dgv01.RowHeadersVisible = True
        dgv01.ResumeLayout()
    End Try
End Sub

answered Sep 22 '15 at 17:49

rheitzman

2,247
3
20
36

That possibly could have worked, but I can't use SelectAll() due to the functionality of my App. – Timmy Sep 22 '15 at 17:51
You could possible use `GetClipboardContent` to get the data quickly into a local string delimited by vbNewline and vbTab. Also `ClipboardCopyMode` may be useful. – rheitzman Sep 22 '15 at 18:13

score 0 · Answer 3 · edited May 23 '17 at 12:29

If you will be using that many rows in between your GridView and Excel I will never use the clipboard (you might run into a roadblock at some point, memory wise talking).

Instead I will create a CSV and open that directly in Excel. You can even use ShellExecute to open it automatically.

There are several other questions similar to this one, like:

[C#] Generating CSV file from SqlDataSource/GridView

or

[VB.NET] Trying to export asp.net gridview to a .csv file

ePuntel · Answer 4 · 2023-01-14T05:07:10.587

No need for loops here, if you go with object data to clipboard, it will be fast enough... for your purpose you can select all DGV control data and then transfer the selected object data to the clipboard at once, alternatively, you can transfer only selected items. But first set DGV's ClipboardCopyMode to EnableAlwaysIncludeHeaderText at DGV's properties, so that the receiver recognizes different columns. Then, implement the code below at a control button click event, where dgv_Curves is a Data Grid View Control:

Dim sel as Boolean = False
        
My.Computer.Clipboard.Clear()
        
If dgv_Curves.GetCellCount(DataGridViewElementStates.Selected) = 0 Then
    dgv_Curves.SelectAll()
    sel = True
End If

Clipboard.SetDataObject(dgv_Curves.GetClipboardContent())

If sel Then
    dgv.Curves.ClearSelection()
End If

If no items are selected at transfer button click event, automatic selection of all grid elements is performed and corresponding data is transfered to clipboard with columns headers. You can now read it with excel spreadsheet or pandas dataframe, as you wish...

Block transfer is faster, and in your case, if DGV control is visible, loopy transfer it will keep updating selection after selection while performing the loops to gather the data, already gathered by the object and retrievable by its methods.

score -1 · Answer 5 · answered Nov 27 '20 at 16:39

First step in optimization is to only display needed data, that is do your query SELECTively with only needed columns.
There is no need to copy the first column.
Using approach DO & FOR loop are slow by nature.

just simply:

    dgv01.RowHeadersVisible = False
    dgv01.SelectAll()
    Clipboard.SetDataObject(dgv01.GetClipboardContent())
    dgv001.ClearSelection()

Datagridview to Clipboard Optimization

5 Answers5