How to copy and paste the nested table records from the WinForms GridGroupingControl to Excel?
Clipboard operation
To copy and paste the Nested table records from the GridGroupingControl to Excel, you must first copy the Grid content to the Clipboard. This is achieved by handling the ClipboardCopy event. In the event, the selected records are copied by iterating through the Nested table collection by using the GetTable method. This iterating loop spans through their columns to get values. These values are appended to a string variable and finally copied to the Clipboard as strings by using the Clipboard.SetDataObject() method.
ClipboardCopy Event:
The ClipboardCopy event is fired when the content is copied from the Grid’s TableModel.
C#
//Subscribes to the events as required. this.gridGroupingControl1.TableModel.ClipboardCopy += new GridCutPasteEventHandler(TableModel_ClipboardCopy); this.gridGroupingControl1.TableModel.ClipboardCut += new GridCutPasteEventHandler(TableModel_ClipboardCut); //Copies in Parent Table. void TableModel_ClipboardCopy(object sender, GridCutPasteEventArgs e) { this.CopySelectedRecords(false); e.Handled = true; } //Cuts in Parent Table. private void TableModel_ClipboardCut(object sender, GridCutPasteEventArgs e) { this.CopySelectedRecords(true); e.Handled = true; }
VB
'Subscribes to the events as required Private Me.gridGroupingControl1.TableModel.ClipboardCopy += New GridCutPasteEventHandler(AddressOf TableModel_ClipboardCopy) Private Me.gridGroupingControl1.TableModel.ClipboardCut += New GridCutPasteEventHandler(AddressOf TableModel_ClipboardCut) 'Copies in Parent Table. Private Sub TableModel_ClipboardCopy(ByVal sender As Object, ByVal e As GridCutPasteEventArgs) Me.CopySelectedRecords(False) e.Handled = True End Sub 'Cuts in Parent Table. Private Sub TableModel_ClipboardCut(ByVal sender As Object, ByVal e As GridCutPasteEventArgs) Me.CopySelectedRecords(True) e.Handled = True End Sub
CopySelectedRecords Method:
This method is used to copy the Selected Records and add it to the Clipboard object as a string.
C#
private void CopySelectedRecords(bool cut) { string s = ""; //Copies visible column names to the string buffer. foreach (GridVisibleColumnDescriptor cd in this.gridGroupingControl1.TableDescriptor.VisibleColumns) { int index = this.gridGroupingControl1.TableDescriptor.VisibleColumns.IndexOf(cd); if(index != 0) s += '\t'; s += cd.Name; } s += Environment.NewLine; //Copies the selected records. if(this.gridGroupingControl1.Table.SelectedRecords.Count > 0) { //Selection is made in the parent table. foreach(SelectedRecord selRec in this.gridGroupingControl1.Table.SelectedRecords) s = CopySelectedRecordsToBuffer(s, selRec.Record, gridGroupingControl1.TableDescriptor, cut); } else { //Selection is made in the child table. s = ""; GridTable child = gridGroupingControl1.GetTable("MarkSheet"); if(child.SelectedRecords.Count > 0) { //Copies visible column names of the child table into the string buffer. foreach(GridVisibleColumnDescriptor cd in child.TableDescriptor.VisibleColumns) { int index = child.TableDescriptor.VisibleColumns.IndexOf(cd); if(index != 0) s += '\t'; s += cd.Name; } s += Environment.NewLine; foreach (SelectedRecord r in child.SelectedRecords) s = CopySelectedRecordsToBuffer(s, r.Record, child.TableDescriptor, cut); } } //Sets the copied string to the Clipboard data object. Clipboard.SetDataObject(new DataObject(s), true); }
VB
Private Sub CopySelectedRecords(ByVal cut As Boolean) Dim s As String = "" 'Copies visible column names to the string buffer. For Each cd As GridVisibleColumnDescriptor In Me.gridGroupingControl1.TableDescriptor.VisibleColumns Dim index As Integer = Me.gridGroupingControl1.TableDescriptor.VisibleColumns.IndexOf(cd) If index <> 0 Then s &= ControlChars.Tab End If s &= cd.Name Next cd s &= Environment.NewLine 'Copies the selected records. If Me.gridGroupingControl1.Table.SelectedRecords.Count > 0 Then 'Selection is made in the parent table. For Each selRec As SelectedRecord In Me.gridGroupingControl1.Table.SelectedRecords s = CopySelectedRecordsToBuffer(s, selRec.Record, gridGroupingControl1.TableDescriptor, cut) Next selRec Else 'Selection is made in the child table. s = "" Dim child As GridTable = gridGroupingControl1.GetTable("MarkSheet") If child.SelectedRecords.Count > 0 Then 'Copies visible column names of the child table into the string buffer. For Each cd As GridVisibleColumnDescriptor In child.TableDescriptor.VisibleColumns Dim index As Integer = child.TableDescriptor.VisibleColumns.IndexOf(cd) If index <> 0 Then s &= ControlChars.Tab End If s &= cd.Name Next cd s &= Environment.NewLine For Each r As SelectedRecord In child.SelectedRecords s = CopySelectedRecordsToBuffer(s, r.Record, child.TableDescriptor, cut) Next r End If End If 'Sets the copied string to the Clipboard data object. Clipboard.SetDataObject(New DataObject(s), True) End Sub
CopySelectedRecordsToBuffer Method:
In this method, the selected records are added in the buffer string with the visible column descriptor. It has the same appearance as the nested table records when the Clipboard content is pasted to the Excel sheet.
C#
private string CopySelectedRecordsToBuffer(string buffer, Record rec, GridTableDescriptor tableDesc, bool cut) { //Since a 'Cut' operation in parent record eliminates the child records too, it is prohibited. if(cut && tableDesc.Name.Equals("Students")) { MessageBox.Show("Parent record cannot be null"); return ""; } //Copies the data records into the string buffer. for(int i = 0; i < tableDesc.VisibleColumns.Count; i++) { GridVisibleColumnDescriptor vcd = tableDesc.VisibleColumns[i]; if(i != 0) buffer += '\t'; buffer += rec.GetValue(vcd.Name).ToString(); if (cut) rec.SetValue(vcd.Name, ""); } buffer += Environment.NewLine; //When the selection is made in a parent record, its child records also get copied. if(rec.HasNestedTables) { buffer += Environment.NewLine; buffer += '\t'; NestedTable child = rec.NestedTables[0]; GridTableDescriptor childDesc = gridGroupingControl1.GetTableDescriptor(child.ChildTable.Name); //Copies visible column names of the child table. foreach(GridVisibleColumnDescriptor cd in childDesc.VisibleColumns) { int index = childDesc.VisibleColumns.IndexOf(cd); if(index != 0) buffer += "\t"; buffer += cd.Name; } buffer += Environment.NewLine; //Copies child records. foreach (Record r in child.FilteredRecords) { buffer += "\t"; for(int i = 0; i < childDesc.VisibleColumns.Count; ++i) { GridVisibleColumnDescriptor vcd = childDesc.VisibleColumns[i]; if(i != 0) buffer += '\t'; buffer += r.GetValue(vcd.Name).ToString(); } buffer += Environment.NewLine; } //Repeats the above process for every nested table associated with the record. foreach (Record r in child.FilteredRecords) { CopySelectedRecordsToBuffer(buffer, r, childDesc, cut); } } return buffer; }
VB
Private Function CopySelectedRecordsToBuffer(ByVal buffer As String, ByVal rec As Record, ByVal tableDesc As GridTableDescriptor, ByVal cut As Boolean) As String 'Since a 'Cut' operation in parent record eliminates the child records too, it is prohibited. If cut AndAlso tableDesc.Name.Equals("Students") Then MessageBox.Show("Parent record cannot be null") Return "" End If 'Copies the data records into the string buffer. For i As Integer = 0 To tableDesc.VisibleColumns.Count - 1 Dim vcd As GridVisibleColumnDescriptor = tableDesc.VisibleColumns(i) If i <> 0 Then buffer &= ControlChars.Tab End If buffer &= rec.GetValue(vcd.Name).ToString() If cut Then rec.SetValue(vcd.Name, "") End If Next i buffer &= Environment.NewLine 'When the selection is made in a parent record, its child records also get copied. If rec.HasNestedTables Then buffer &= Environment.NewLine buffer &= ControlChars.Tab Dim child As NestedTable = rec.NestedTables(0) Dim childDesc As GridTableDescriptor = gridGroupingControl1.GetTableDescriptor(child.ChildTable.Name) 'Copies visible column names of the child table. For Each cd As GridVisibleColumnDescriptor In childDesc.VisibleColumns Dim index As Integer = childDesc.VisibleColumns.IndexOf(cd) If index <> 0 Then buffer &= Constants.vbTab End If buffer &= cd.Name Next cd buffer &= Environment.NewLine 'Copies child records. For Each r As Record In child.FilteredRecords buffer &= Constants.vbTab For i As Integer = 0 To childDesc.VisibleColumns.Count - 1 Dim vcd As GridVisibleColumnDescriptor = childDesc.VisibleColumns(i) If i <> 0 Then buffer &= ControlChars.Tab End If buffer &= r.GetValue(vcd.Name).ToString() Next i buffer &= Environment.NewLine Next r 'Repeats the above process for every nested table associated with the record. For Each r As Record In child.FilteredRecords CopySelectedRecordsToBuffer(buffer, r, childDesc, cut) Next r End If Return buffer End Function
The following screenshot displays the selected Record copied with the nested Records to the clipboard and then to the Excel sheet.
Figure 1: The selected Record is copied with the nested Records to the clipboard
Figure 2: Clipboard content copied to the Excel sheet
Samples: