How to Copy, Cut, and Paste Cell Data without Accessing the Clipboard

• Oct 20, 2023
• 4 minutes to read

The most popular cell data copy and move techniques use the clipboard. However, if you need to keep its content intact, you can use a worksheet’s CopyCellDataToStream and PasteCellDataFromStream procedures, allowing you to copy and paste cell data via a stream object, without accessing the clipboard. In addition to the cell data and formatting, these procedures copy comment containers associated with cells. However, other floating container types cannot be copied in this manner.

You can use these methods to emulate:

• The CopySelection, CutSelection, and PasteSelection commands;

• A worksheet’s CopyToClipboard, CutToClipboard, and PasteFromClipboard procedures.

In order to work with the CopyCellDataToStream and PasteCellDataFromStream procedures, first, you need to create a valid stream object (that is, a TStream descendant instance). The following code example demonstrates how to copy the A1:C4 cell range into the same worksheet, starting from the D1 cell:

Delphi

var
  ATableView: TdxSpreadSheetTableView;
  AStream: TMemoryStream;
  ASourceRect: TRect;
  ADestinationPoint: TPoint;
//...
  ATableView := dxSpreadSheet1.ActiveSheetAsTable;
  AStream := TMemoryStream.Create;  // Create a TStream descendant instance
  try
    ASourceRect := Rect(0, 0, 2, 3);  // Pass the column and row indexes of the range's left-top and right-bottom cells as the Rect's parameters
    ADestinationPoint := Point(3, 0);  // Pass the destination cell's row and column indexes as the Point's parameters
    ATableView.CopyCellDataToStream(ASourceRect, AStream);
    AStream.Position := 0;  // Reset the stream object's read position
    ATableView.PasteCellDataFromStream(ADestinationPoint, AStream);
  finally
    AStream.Free;

C++

  TdxSpreadSheetTableView *ATableView;
  TMemoryStream *AStream;
  TRect ASourceRect;
  TPoint ADestinationPoint;
//...
  ATableView = dxSpreadSheet1->ActiveSheetAsTable;
  AStream = new TMemoryStream();  // Create a TStream descendant instance
  try
  {
    ASourceRect = Rect(0, 0, 2, 3);  // Pass the column and row indexes of the range's left-top and right-bottom cells as the Rect's parameters
    ADestinationPoint = Point(3, 0);  // Pass the destination cell's row and column indexes as the Point's parameters
    ATableView->CopyCellDataToStream(Rect(0, 0, 2, 3), AStream);
    AStream->Position = 0;  // Reset the stream object's read position
    ATableView->PasteCellDataFromStream(Point(3, 0), AStream);
  }
  __finally
    delete AStream;

To emulate the behavior of a worksheet’s CopyToClipboard and PasteFromClipboard procedures without modifying the clipboard’s content, you need to:

• Pass the current selection’s area as the CopyCellDataToStream procedure’s AArea parameter.

• Specify the current selection’s left and top positions as the destination point position passed as the PasteCellDataFromStream procedure’s ADestination parameter.

The following code example demonstrates how to copy the current cell selection to the same place in a different worksheet:

Delphi

var
  ATableView1, ATableView2: TdxSpreadSheetTableView;
  ADestinationPoint: TPoint;
  AStream: TMemoryStream;
begin
  ATableView1 := dxSpreadSheet1.ActiveSheetAsTable;  // The source worksheet
  dxSpreadSheet1.AddSheet;
  AStream := TMemoryStream.Create;
  try
    ATableView2 := TdxSpreadSheetTableView(dxSpreadSheet1.Sheets[1]);  // The destination worksheet
    ADestinationPoint := Point(ATableView1.Selection.Area.Left, ATableView1.Selection.Area.Top);  // Destination point matches the selection area's origin
    ATableView1.CopyCellDataToStream(ATableView1.Selection.Area, AStream);  // Note that the Area may include multiple cell selections
    AStream.Position := 0;
    ATableView2.PasteCellDataFromStream(ADestinationPoint, AStream);
  finally
    AStream.Free;
end;

C++

  TdxSpreadSheetTableView *ATableView1, *ATableView2;
  TPoint ADestinationPoint;
  TMemoryStream *AStream;
//...
  ATableView1 = dxSpreadSheet1->ActiveSheetAsTable;  // The source worksheet
  dxSpreadSheet1->AddSheet();
  AStream = new TMemoryStream();
  try
  {
    ATableView2 = dynamic_cast<TdxSpreadSheetTableView*>(dxSpreadSheet1->Sheet[1]);  // The destination worksheet
    ADestinationPoint = Point(ATableView1->Selection->Area.Left, ATableView1->Selection->Area.Top);  // Destination point matches the selection area's origin
    ATableView1->CopyCellDataToStream(ATableView->Selection->Area, AStream);  // Note that the Area may include multiple cell selections
    AStream->Position = 0;
    ATableView2->PasteCellDataFromStream(ADestionationPoint, AStream);  // Paste the stream's content to the destination worksheet
  }
  __finally
    delete AStream;

In order to emulate the behavior of a worksheet’s CutToClipboard procedure, you can call the CopyCellDataToStream procedure followed by invoking the ClearCells procedure. Pass the current selection area as the Area parameter of both procedures:

Delphi

var
  ATableView: TdxSpreadSheetTableView;
  AStream: TMemoryStream;
//...
  AStream := TMemoryStream.Create;
  ATableView.CopyCellDataToStream(ATableView.Selection.Area, AStream);  // Copy the selected cells to the created stream object
  ATableView.ClearCells(ATableView.Selection.Area);  // Clear the copied cells to emulate the CutToClipboard procedure's behavior

C++

TdxSpreadSheetTableView *ATableView;
  TMemoryStream *AStream;
//...
  AStream = new TMemoryStream();
  ATableView->CopyCellDataToStream(ATableView->Selection->Area, AStream);  // Copy the selected cells to the created stream object
  ATableView->ClearCells(ATableView->Selection->Area);  // Clear the copied cells to emulate the CutToClipoard procedure's behavior