Articles in this section
Category / Section

How to copy and paste the nested table records from the WinForms GridGroupingControl to Excel?

5 mins read

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.

 

Show selected record copied with the nested records to the clipboard

Figure 1: The selected Record is copied with the nested Records to the clipboard

Clipboard content copied to the excel sheet

Figure 2: Clipboard content copied to the Excel sheet

Samples:

C#: CopyPasteToExcel-C#-1220698826.zip

VB: CopyPasteToExcel-VB-858778125.zip

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied