Copy and paste (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\40.Copy And Paste and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/10.API/40.Copy And Paste
Overview
ExcelFile has a group of methods allowing you to copy/paste from/to FlexCel to/from Excel in native Excel format. All methods copy and paste the data on BIFF8 and Tabbed-Text format, to allow for copying/pasting from other sources besides Excel.
Copying and pasting in native BIFF8 format is a great advance over copying/pasting on plain text only. It allows you to keep cell formats/colors/rounding decimals/merged cells/etc. It allows a new world of interoperation between your applications and Excel. Your users will be able to interchange data back and forward between your application and Excel just by copying and pasting. But keep in mind that it has its limitations too:
It can't copy/paste images
It can't copy/paste strings longer than 255 characters
It can't copy the data on multiple sheets.
I would like to say that these limitations are not FlexCel's fault. The BIFF8 specification is correctly implemented, those are limitations on Excel's part.
Of course, Excel can copy and paste everything without problems, but this is so because Excel doesn't use the clipboard to do the operation. If you close all instances of Excel, open a Worksheet, copy some cells to the clipboard, close Excel and open it again you will run into the same limitations. Copy/paste limitations on Excel don't show when it is kept in memory.
Concepts
- FlexCel methods return datastreams for maximum flexibility. No data will be actually copied/pasted from/to the clipboard by FlexCel, this is your task. Here you can see how it is done.
Files
AssemblyInfo.cs
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("")]
[assembly: AssemblyCopyright("(c) 2002 - 2025 TMS Software")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("7.24.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\<configuration>. For example, if your KeyFile is
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("")]
Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Text;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.Collections.Generic;
namespace CopyAndPaste
{
/// <summary>
/// Copy and Paste Example.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private XlsFile Xls;
private void btnNewFile_Click(object sender, System.EventArgs e)
{
try
{
Xls = new XlsFile();
Xls.NewFile(1, TExcelFileFormat.v2019);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
try
{
if (openFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK) return;
Xls = new XlsFile(openFileDialog.FileName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void DoPaste(IDataObject iData)
{
if (Xls == null)
{
MessageBox.Show("Please push the New File button before pasting");
return;
}
try
{
if (iData.GetDataPresent(FlexCelDataFormats.Excel97))
{
//DO NOT CALL -> using (MemoryStream ms = (MemoryStream)iData.GetData(FlexCelDataFormats.Excel97))
//You shouldn't dispose the stream, as it belongs to the Clipboard.
object o = iData.GetData(FlexCelDataFormats.Excel97);
MemoryStream ms = (MemoryStream)o;
{
Xls.PasteFromXlsClipboardFormat(1, 1, TFlxInsertMode.NoneDown, ms);
MessageBox.Show("NATIVE Data has been pasted at cell A1");
}
}
else
if (iData.GetDataPresent(DataFormats.UnicodeText))
{
Xls.PasteFromTextClipboardFormat(1, 1, TFlxInsertMode.NoneDown, (string)iData.GetData(DataFormats.UnicodeText));
MessageBox.Show("UNICODE TEXT Data has been pasted at cell A1");
}
else
if (iData.GetDataPresent(DataFormats.Text))
{
Xls.PasteFromTextClipboardFormat(1, 1, TFlxInsertMode.NoneDown, (string)iData.GetData(DataFormats.Text));
MessageBox.Show("TEXT Data has been pasted at cell A1");
}
else
{
MessageBox.Show("There is no Excel or Text data on the clipboard");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Xls = new XlsFile();
Xls.NewFile(1, TExcelFileFormat.v2019);
}
}
private void btnPaste_Click(object sender, System.EventArgs e)
{
DoPaste(Clipboard.GetDataObject());
}
private void DropHere_DragOver(object sender, System.Windows.Forms.DragEventArgs e)
{
if (e.Data.GetDataPresent(FlexCelDataFormats.Excel97) ||
e.Data.GetDataPresent(DataFormats.UnicodeText) ||
e.Data.GetDataPresent(DataFormats.Text)
)
e.Effect = DragDropEffects.Copy;
}
private void DropHere_DragDrop(object sender, System.Windows.Forms.DragEventArgs e)
{
DoPaste(e.Data);
}
private void DoCopy(bool ToClipboard)
{
if (Xls == null)
{
MessageBox.Show("Please push the New File button before copying");
return;
}
//VERY IMPORTANT!!!!!
//****************************************************************************
//The MemoryStreams CAN NOT BE DISPOSED UNTIL WE CALL Clipboard.SetObjectData.
//Even when we assigned the Stream with the DataObject Data, it is still in use and can't be freed.
//****************************************************************************
try
{
DataObject data = new DataObject();
List<MemoryStream> dataStreams = new List<MemoryStream>(); //we will use this list to dispose the memorystreams after they have been used.
try
{
foreach (FlexCelClipboardFormat cf in Enum.GetValues(typeof(FlexCelClipboardFormat)))
{
MemoryStream dataStream = new MemoryStream();
dataStreams.Add(dataStream);
Xls.CopyToClipboard(cf, dataStream);
dataStream.Position = 0;
data.SetData(FlexCelDataFormats.GetString(cf), dataStream);
}
if (ToClipboard)
Clipboard.SetDataObject(data, true);
else
DoDragDrop(data, DragDropEffects.Copy);
}
finally
{
foreach (MemoryStream ms in dataStreams)
{
ms.Dispose();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnCopy_Click(object sender, System.EventArgs e)
{
DoCopy(true);
}
private void btnDragMe_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
{
DoCopy(false);
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Text;
using FlexCel.Core;
using FlexCel.XlsAdapter;
namespace CopyAndPaste
{
public partial class mainForm: System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnPaste = new System.Windows.Forms.Button();
this.btnNewFile = new System.Windows.Forms.Button();
this.btnCopy = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.btnDragMe = new System.Windows.Forms.Button();
this.label5 = new System.Windows.Forms.Label();
this.DropHere = new System.Windows.Forms.Label();
this.btnOpenFile = new System.Windows.Forms.Button();
this.openFileDialog = new System.Windows.Forms.OpenFileDialog();
this.SuspendLayout();
//
// btnPaste
//
this.btnPaste.Location = new System.Drawing.Point(44, 251);
this.btnPaste.Margin = new System.Windows.Forms.Padding(6, 6, 6, 6);
this.btnPaste.Name = "btnPaste";
this.btnPaste.Size = new System.Drawing.Size(138, 42);
this.btnPaste.TabIndex = 0;
this.btnPaste.Text = "Paste";
this.btnPaste.Click += new System.EventHandler(this.btnPaste_Click);
//
// btnNewFile
//
this.btnNewFile.Location = new System.Drawing.Point(44, 74);
this.btnNewFile.Margin = new System.Windows.Forms.Padding(6, 6, 6, 6);
this.btnNewFile.Name = "btnNewFile";
this.btnNewFile.Size = new System.Drawing.Size(138, 42);
this.btnNewFile.TabIndex = 1;
this.btnNewFile.Text = "New File";
this.btnNewFile.Click += new System.EventHandler(this.btnNewFile_Click);
//
// btnCopy
//
this.btnCopy.Location = new System.Drawing.Point(44, 428);
this.btnCopy.Margin = new System.Windows.Forms.Padding(6, 6, 6, 6);
this.btnCopy.Name = "btnCopy";
this.btnCopy.Size = new System.Drawing.Size(138, 42);
this.btnCopy.TabIndex = 2;
this.btnCopy.Text = "Copy";
this.btnCopy.Click += new System.EventHandler(this.btnCopy_Click);
//
// label1
//
this.label1.BackColor = System.Drawing.Color.LightSkyBlue;
this.label1.Location = new System.Drawing.Point(44, 15);
this.label1.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(719, 44);
this.label1.TabIndex = 4;
this.label1.Text = "1) Begin by creating a new file or opening an existing file...";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// label2
//
this.label2.BackColor = System.Drawing.Color.LightSkyBlue;
this.label2.Location = new System.Drawing.Point(44, 148);
this.label2.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(719, 44);
this.label2.TabIndex = 5;
this.label2.Text = "2) Now go to Excel, copy some cells and paste them here...";
this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// label3
//
this.label3.BackColor = System.Drawing.Color.LightSkyBlue;
this.label3.Location = new System.Drawing.Point(44, 325);
this.label3.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(719, 44);
this.label3.TabIndex = 6;
this.label3.Text = "3) After pasting, you can copy back the results to the clipboard";
this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// label4
//
this.label4.BackColor = System.Drawing.Color.SteelBlue;
this.label4.ForeColor = System.Drawing.Color.White;
this.label4.Location = new System.Drawing.Point(44, 369);
this.label4.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(719, 44);
this.label4.TabIndex = 7;
this.label4.Text = "Press the \"Copy\" button or drag the \"Drag Me!\" into Excel.";
this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// btnDragMe
//
this.btnDragMe.AllowDrop = true;
this.btnDragMe.Location = new System.Drawing.Point(205, 428);
this.btnDragMe.Margin = new System.Windows.Forms.Padding(6, 6, 6, 6);
this.btnDragMe.Name = "btnDragMe";
this.btnDragMe.Size = new System.Drawing.Size(138, 42);
this.btnDragMe.TabIndex = 8;
this.btnDragMe.Text = "Drag Me!";
this.btnDragMe.MouseDown += new System.Windows.Forms.MouseEventHandler(this.btnDragMe_MouseDown);
//
// label5
//
this.label5.BackColor = System.Drawing.Color.SteelBlue;
this.label5.ForeColor = System.Drawing.Color.White;
this.label5.Location = new System.Drawing.Point(44, 192);
this.label5.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(719, 44);
this.label5.TabIndex = 10;
this.label5.Text = "Press the \"Paste\" button or drag some cells from Excel into \"Drop Here!\".";
this.label5.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// DropHere
//
this.DropHere.AllowDrop = true;
this.DropHere.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(255)))), ((int)(((byte)(192)))));
this.DropHere.Location = new System.Drawing.Point(200, 251);
this.DropHere.Margin = new System.Windows.Forms.Padding(6, 0, 6, 0);
this.DropHere.Name = "DropHere";
this.DropHere.Size = new System.Drawing.Size(183, 42);
this.DropHere.TabIndex = 11;
this.DropHere.Text = "Drop Here!";
this.DropHere.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
this.DropHere.DragDrop += new System.Windows.Forms.DragEventHandler(this.DropHere_DragDrop);
this.DropHere.DragOver += new System.Windows.Forms.DragEventHandler(this.DropHere_DragOver);
//
// btnOpenFile
//
this.btnOpenFile.Location = new System.Drawing.Point(205, 74);
this.btnOpenFile.Margin = new System.Windows.Forms.Padding(6);
this.btnOpenFile.Name = "btnOpenFile";
this.btnOpenFile.Size = new System.Drawing.Size(138, 42);
this.btnOpenFile.TabIndex = 12;
this.btnOpenFile.Text = "Open File";
this.btnOpenFile.Click += new System.EventHandler(this.btnOpenFile_Click);
//
// openFileDialog
//
this.openFileDialog.DefaultExt = "xls";
this.openFileDialog.Filter = "Excel Files|*.xls|All files|*.*";
this.openFileDialog.Title = "Select a file to preview";
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(11F, 24F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(836, 615);
this.Controls.Add(this.btnOpenFile);
this.Controls.Add(this.DropHere);
this.Controls.Add(this.label5);
this.Controls.Add(this.btnDragMe);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.btnCopy);
this.Controls.Add(this.btnNewFile);
this.Controls.Add(this.btnPaste);
this.Margin = new System.Windows.Forms.Padding(6, 6, 6, 6);
this.Name = "mainForm";
this.Text = "Copy and Paste Demo";
this.ResumeLayout(false);
}
#endregion
private Button btnPaste;
private Button btnNewFile;
private Button btnCopy;
private Label label1;
private Label label2;
private Label label3;
private Label label4;
private Label label5;
private Button btnDragMe;
private Label DropHere;
private Button btnOpenFile;
private OpenFileDialog openFileDialog;
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace CopyAndPaste
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new mainForm());
}
}
}