paul frith paul frith - 9 days ago 5
Vb.net Question

get back to users datagridview selection after selecting all programmatically

In order to speed up the export of data held in a datagridview to excel, I am using a method suggest in this forum where the contents of the datagridview are copied to the clipboard, and then pasted into the excel spreadsheet, in order to cut down on the amount of times the application has to communicate with excel.

Whilst this works fine, one draw back is that if a user has particular cells selected in the datagridview - the code will cause this selection to be lost, as it uses the

datagridview.SelectAll()
method.

I was hoping to find a simple solution to reselect the users original selection post exporting to excel. I have tried the following:

Dim mySelection As DataGridViewSelectedCellCollection

mySelection = myDataGridView.SelectedCells

ExportToExcel(myDataGridView, "Exported Data")

myDataGridView.SelectedCells = mySelection


I suspect that
DataGridViewSelectedCellCollection
is not the correct thing to use here, as it seems to be about the data held within the selection rather than the positions of the selected cells.

Alternative is there a way of getting all of the datagridview into the clipboard without having to use
SelectAll()
?

If needed the Export to Excel code is here:

Private Sub ExportToExcel(myDataGridView As DataGridView, myWorksheetName As String)

' Creating a Excel object.
Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
Dim xlWorkSheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

Try

xlWorkSheet = workbook.ActiveSheet

xlWorkSheet.Name = myWorksheetName


'Data transfer from grid to Excel.
With xlWorkSheet
.Range("1:1").EntireRow.Font.Bold = True
'Set Clipboard Copy Mode
myDataGridView.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
myDataGridView.SelectAll()

'Get the content from Grid for Clipboard
Dim str As String = TryCast(myDataGridView.GetClipboardContent().GetData(DataFormats.UnicodeText), String)

'Set the content to Clipboard
Clipboard.SetText(str, TextDataFormat.UnicodeText)

'Identify and select the range of cells in Excel to paste the clipboard data.
.Range("A1").Select()

'Paste the clipboard data
.Paste()
Clipboard.Clear()
End With

'Getting the location and file name of the excel to save from user.
Dim saveDialog As New SaveFileDialog()
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"
saveDialog.FilterIndex = 2

If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
workbook.SaveAs(saveDialog.FileName)
MessageBox.Show("Export Successful")
End If
Catch ex As System.Exception
MessageBox.Show(ex.Message)
Finally
excel.Quit()
workbook = Nothing
excel = Nothing
End Try

End Sub

SSS SSS
Answer

You can copy the contents of the DataGridView without changing the selection by assembling a tab-delimited unicode String and pushing it into the ClipBoard:

  Sub CopyDataGridViewToClipboard(dgv As DataGridView, includeHeader As Boolean)
    Dim sbl As New System.Text.StringBuilder
    If includeHeader Then
      For intCol As Integer = 0 To dgv.Columns.Count - 1
        Dim dgvc As DataGridViewColumn = dgv.Columns(intCol)
        If intCol > 0 Then sbl.Append(vbTab)
        sbl.Append(dgvc.HeaderText)
      Next intCol
      sbl.AppendLine()
    End If
    For intRow As Integer = 0 To dgv.Rows.Count - 1
      Dim dgvr As DataGridViewRow = dgv.Rows(intRow)
      For intCol As Integer = 0 To dgv.Columns.Count - 1
        If intCol > 0 Then sbl.Append(vbTab)
        sbl.Append(dgvr.Cells(intCol).Value)
      Next intCol
      sbl.AppendLine()
    Next intRow
    Clipboard.SetText(sbl.ToString, TextDataFormat.UnicodeText)
  End Sub