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