Table of Contents

Copy and paste (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\40.Copy And Paste and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​vb/​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.vb

Imports System.Reflection
Imports 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 - 2014 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("6.2.1.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.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Text
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Namespace CopyAndPaste
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		''' <summary>
		''' Required designer variable.
		''' </summary>
		Private components As System.ComponentModel.Container = Nothing

		''' <summary>
		''' Clean up any resources being used.
		''' </summary>
		Protected Overrides Sub Dispose(ByVal disposing As Boolean)
			If disposing Then
				If components IsNot Nothing Then
					components.Dispose()
				End If
			End If
			MyBase.Dispose(disposing)
		End Sub

		#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 Sub InitializeComponent()
			Me.btnPaste = New System.Windows.Forms.Button()
			Me.btnNewFile = New System.Windows.Forms.Button()
			Me.btnCopy = New System.Windows.Forms.Button()
			Me.label1 = New System.Windows.Forms.Label()
			Me.label2 = New System.Windows.Forms.Label()
			Me.label3 = New System.Windows.Forms.Label()
			Me.label4 = New System.Windows.Forms.Label()
			Me.btnDragMe = New System.Windows.Forms.Button()
			Me.label5 = New System.Windows.Forms.Label()
			Me.DropHere = New System.Windows.Forms.Label()
			Me.btnOpenFile = New System.Windows.Forms.Button()
			Me.openFileDialog = New System.Windows.Forms.OpenFileDialog()
			Me.SuspendLayout()
			' 
			' btnPaste
			' 
			Me.btnPaste.Location = New System.Drawing.Point(44, 251)
			Me.btnPaste.Margin = New System.Windows.Forms.Padding(6, 6, 6, 6)
			Me.btnPaste.Name = "btnPaste"
			Me.btnPaste.Size = New System.Drawing.Size(138, 42)
			Me.btnPaste.TabIndex = 0
			Me.btnPaste.Text = "Paste"
'			Me.btnPaste.Click += New System.EventHandler(Me.btnPaste_Click)
			' 
			' btnNewFile
			' 
			Me.btnNewFile.Location = New System.Drawing.Point(44, 74)
			Me.btnNewFile.Margin = New System.Windows.Forms.Padding(6, 6, 6, 6)
			Me.btnNewFile.Name = "btnNewFile"
			Me.btnNewFile.Size = New System.Drawing.Size(138, 42)
			Me.btnNewFile.TabIndex = 1
			Me.btnNewFile.Text = "New File"
'			Me.btnNewFile.Click += New System.EventHandler(Me.btnNewFile_Click)
			' 
			' btnCopy
			' 
			Me.btnCopy.Location = New System.Drawing.Point(44, 428)
			Me.btnCopy.Margin = New System.Windows.Forms.Padding(6, 6, 6, 6)
			Me.btnCopy.Name = "btnCopy"
			Me.btnCopy.Size = New System.Drawing.Size(138, 42)
			Me.btnCopy.TabIndex = 2
			Me.btnCopy.Text = "Copy"
'			Me.btnCopy.Click += New System.EventHandler(Me.btnCopy_Click)
			' 
			' label1
			' 
			Me.label1.BackColor = System.Drawing.Color.LightSkyBlue
			Me.label1.Location = New System.Drawing.Point(44, 15)
			Me.label1.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.label1.Name = "label1"
			Me.label1.Size = New System.Drawing.Size(719, 44)
			Me.label1.TabIndex = 4
			Me.label1.Text = "1) Begin by creating a new file or opening an existing file..."
			Me.label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
			' 
			' label2
			' 
			Me.label2.BackColor = System.Drawing.Color.LightSkyBlue
			Me.label2.Location = New System.Drawing.Point(44, 148)
			Me.label2.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.label2.Name = "label2"
			Me.label2.Size = New System.Drawing.Size(719, 44)
			Me.label2.TabIndex = 5
			Me.label2.Text = "2) Now go to Excel, copy some cells and paste them here..."
			Me.label2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
			' 
			' label3
			' 
			Me.label3.BackColor = System.Drawing.Color.LightSkyBlue
			Me.label3.Location = New System.Drawing.Point(44, 325)
			Me.label3.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.label3.Name = "label3"
			Me.label3.Size = New System.Drawing.Size(719, 44)
			Me.label3.TabIndex = 6
			Me.label3.Text = "3) After pasting, you can copy back the results to the clipboard"
			Me.label3.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
			' 
			' label4
			' 
			Me.label4.BackColor = System.Drawing.Color.SteelBlue
			Me.label4.ForeColor = System.Drawing.Color.White
			Me.label4.Location = New System.Drawing.Point(44, 369)
			Me.label4.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.label4.Name = "label4"
			Me.label4.Size = New System.Drawing.Size(719, 44)
			Me.label4.TabIndex = 7
			Me.label4.Text = "Press the ""Copy"" button or drag the ""Drag Me!"" into Excel."
			Me.label4.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
			' 
			' btnDragMe
			' 
			Me.btnDragMe.AllowDrop = True
			Me.btnDragMe.Location = New System.Drawing.Point(205, 428)
			Me.btnDragMe.Margin = New System.Windows.Forms.Padding(6, 6, 6, 6)
			Me.btnDragMe.Name = "btnDragMe"
			Me.btnDragMe.Size = New System.Drawing.Size(138, 42)
			Me.btnDragMe.TabIndex = 8
			Me.btnDragMe.Text = "Drag Me!"
'			Me.btnDragMe.MouseDown += New System.Windows.Forms.MouseEventHandler(Me.btnDragMe_MouseDown)
			' 
			' label5
			' 
			Me.label5.BackColor = System.Drawing.Color.SteelBlue
			Me.label5.ForeColor = System.Drawing.Color.White
			Me.label5.Location = New System.Drawing.Point(44, 192)
			Me.label5.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.label5.Name = "label5"
			Me.label5.Size = New System.Drawing.Size(719, 44)
			Me.label5.TabIndex = 10
			Me.label5.Text = "Press the ""Paste"" button or drag some cells from Excel into ""Drop Here!""."
			Me.label5.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
			' 
			' DropHere
			' 
			Me.DropHere.AllowDrop = True
			Me.DropHere.BackColor = System.Drawing.Color.FromArgb((CInt((CByte(192)))), (CInt((CByte(255)))), (CInt((CByte(192)))))
			Me.DropHere.Location = New System.Drawing.Point(200, 251)
			Me.DropHere.Margin = New System.Windows.Forms.Padding(6, 0, 6, 0)
			Me.DropHere.Name = "DropHere"
			Me.DropHere.Size = New System.Drawing.Size(183, 42)
			Me.DropHere.TabIndex = 11
			Me.DropHere.Text = "Drop Here!"
			Me.DropHere.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
'			Me.DropHere.DragDrop += New System.Windows.Forms.DragEventHandler(Me.DropHere_DragDrop)
'			Me.DropHere.DragOver += New System.Windows.Forms.DragEventHandler(Me.DropHere_DragOver)
			' 
			' btnOpenFile
			' 
			Me.btnOpenFile.Location = New System.Drawing.Point(205, 74)
			Me.btnOpenFile.Margin = New System.Windows.Forms.Padding(6)
			Me.btnOpenFile.Name = "btnOpenFile"
			Me.btnOpenFile.Size = New System.Drawing.Size(138, 42)
			Me.btnOpenFile.TabIndex = 12
			Me.btnOpenFile.Text = "Open File"
'			Me.btnOpenFile.Click += New System.EventHandler(Me.btnOpenFile_Click)
			' 
			' openFileDialog
			' 
			Me.openFileDialog.DefaultExt = "xls"
			Me.openFileDialog.Filter = "Excel Files|*.xls|All files|*.*"
			Me.openFileDialog.Title = "Select a file to preview"
			' 
			' mainForm
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(11F, 24F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.ClientSize = New System.Drawing.Size(836, 615)
			Me.Controls.Add(Me.btnOpenFile)
			Me.Controls.Add(Me.DropHere)
			Me.Controls.Add(Me.label5)
			Me.Controls.Add(Me.btnDragMe)
			Me.Controls.Add(Me.label4)
			Me.Controls.Add(Me.label3)
			Me.Controls.Add(Me.label2)
			Me.Controls.Add(Me.label1)
			Me.Controls.Add(Me.btnCopy)
			Me.Controls.Add(Me.btnNewFile)
			Me.Controls.Add(Me.btnPaste)
			Me.Margin = New System.Windows.Forms.Padding(6, 6, 6, 6)
			Me.Name = "mainForm"
			Me.Text = "Copy and Paste Demo"
			Me.ResumeLayout(False)

		End Sub
		#End Region

		Private WithEvents btnPaste As Button
		Private WithEvents btnNewFile As Button
		Private WithEvents btnCopy As Button
		Private label1 As Label
		Private label2 As Label
		Private label3 As Label
		Private label4 As Label
		Private label5 As Label
		Private WithEvents btnDragMe As Button
		Private WithEvents DropHere As Label
		Private WithEvents btnOpenFile As Button
		Private openFileDialog As OpenFileDialog

	End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Text
Imports FlexCel.Core
Imports FlexCel.XlsAdapter

Namespace CopyAndPaste
	''' <summary>
	''' Copy and Paste Example.
	''' </summary>
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		Public Sub New()
			InitializeComponent()
		End Sub

		Private Xls As XlsFile

		Private Sub btnNewFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewFile.Click
			Try
				Xls = New XlsFile()
				Xls.NewFile(1, TExcelFileFormat.v2019)
			Catch ex As Exception
				MessageBox.Show(ex.Message)
			End Try
		End Sub

		Private Sub btnOpenFile_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpenFile.Click
			Try
				If openFileDialog.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
					Return
				End If
				Xls = New XlsFile(openFileDialog.FileName)
			Catch ex As Exception
				MessageBox.Show(ex.Message)
			End Try

		End Sub


		Private Sub DoPaste(ByVal iData As IDataObject)
			If Xls Is Nothing Then
				MessageBox.Show("Please push the New File button before pasting")
				Return
			End If

			Try
				If iData.GetDataPresent(FlexCelDataFormats.Excel97) Then
					'DO NOT CALL -> using (MemoryStream ms = (MemoryStream)iData.GetData(FlexCelDataFormats.Excel97))
					'You shouldn't dispose the stream, as it belongs to the Clipboard.
					Dim o As Object = iData.GetData(FlexCelDataFormats.Excel97)
					Dim ms As MemoryStream = CType(o, MemoryStream)
						Xls.PasteFromXlsClipboardFormat(1, 1, TFlxInsertMode.NoneDown, ms)
						MessageBox.Show("NATIVE Data has been pasted at cell A1")
				Else
					If iData.GetDataPresent(DataFormats.UnicodeText) Then
					Xls.PasteFromTextClipboardFormat(1, 1, TFlxInsertMode.NoneDown, CStr(iData.GetData(DataFormats.UnicodeText)))
					MessageBox.Show("UNICODE TEXT Data has been pasted at cell A1")
				Else
						If iData.GetDataPresent(DataFormats.Text) Then
					Xls.PasteFromTextClipboardFormat(1, 1, TFlxInsertMode.NoneDown, CStr(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")
				End If
				End If
				End If
			Catch ex As Exception
				MessageBox.Show(ex.Message)
				Xls = New XlsFile()
				Xls.NewFile(1, TExcelFileFormat.v2019)
			End Try
		End Sub

		Private Sub btnPaste_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPaste.Click
			DoPaste(Clipboard.GetDataObject())
		End Sub

		Private Sub DropHere_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DropHere.DragOver
			If e.Data.GetDataPresent(FlexCelDataFormats.Excel97) OrElse e.Data.GetDataPresent(DataFormats.UnicodeText) OrElse e.Data.GetDataPresent(DataFormats.Text) Then
				e.Effect = DragDropEffects.Copy
			End If
		End Sub


		Private Sub DropHere_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DropHere.DragDrop
			DoPaste(e.Data)
		End Sub


		Private Sub DoCopy(ByVal ToClipboard As Boolean)
			If Xls Is Nothing Then
				MessageBox.Show("Please push the New File button before copying")
				Return
			End If

			'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
				Dim data As New DataObject()
				Dim dataStreams As New List(Of MemoryStream)() 'we will use this list to dispose the memorystreams after they have been used.
				Try
					For Each cf As FlexCelClipboardFormat In System.Enum.GetValues(GetType(FlexCelClipboardFormat))
						Dim dataStream As New MemoryStream()
						dataStreams.Add(dataStream)
						Xls.CopyToClipboard(cf, dataStream)
						dataStream.Position = 0
						data.SetData(FlexCelDataFormats.GetString(cf), dataStream)

					Next cf
					If ToClipboard Then
						Clipboard.SetDataObject(data, True)
					Else
						DoDragDrop(data, DragDropEffects.Copy)
					End If
				Finally
					For Each ms As MemoryStream In dataStreams
						ms.Dispose()
					Next ms
				End Try
			Catch ex As Exception
				MessageBox.Show(ex.Message)
			End Try
		End Sub

		Private Sub btnCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCopy.Click
			DoCopy(True)
		End Sub

		Private Sub btnDragMe_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles btnDragMe.MouseDown
			DoCopy(False)
		End Sub

	End Class
End Namespace

Program.vb

Namespace CopyAndPaste
	Friend NotInheritable Class Program

		Private Sub New()
		End Sub

		''' <summary>
		''' The main entry point for the application.
		''' </summary>
		<STAThread> _
		Shared Sub Main()
			Application.EnableVisualStyles()
			Application.SetCompatibleTextRenderingDefault(False)
			Application.Run(New mainForm())
		End Sub
	End Class
End Namespace