Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / WPF

Easy WPF Copy/Paste Excel

Rate me:
Please Sign up or sign in to vote.
5.00/5 (14 votes)
14 Oct 2021CPOL2 min read 30.8K   418   23   12
An easy way to copy and paste data between WPF application and Excel

Introduction

I have developed an easy way to copy/paste data between WPF application and Excel. This code's compatibility for the controls inherit from System.Windows.Controls.ItemsControls - these are the controls with the ItemsSource property. Controls of this type are DataGrid, ListBox, ListView, etc.

Is OpendSource and its code is in GitHub.

Its use is very easy, we will install the utility by nuget and we will setup any properties of our control in the XAML code.

This is how it works:

Image 1

Prerequisites

The utility has been tested in WPF applications, and we don’t know if run in (Silverlight, WP or WUP) apps.

It is necessary 4.5.2 NET Framework version or later.

Installation

We will install from nuget:

Image 2

SetUp

After installation has been completed, we will setup the WPF window. We will add the next import in XAML:

XML
xmlns:ml="clr-namespace:MoralesLarios.Utilities.Excel;assembly=MoralesLarios.Utilities"

We will add the following properties in the ItemsControl in XAML:

XML
<DataGrid x:Name="dataGrid" 
          ml:ExcelActions.EnabledCopyExcel ="True"  <!—- enabled copy/copiall excel  -->
          ml:ExcelActions.EnabledPasteExcel="True"  <!—- enabled paste        excel  -->
          />

This simple code enabled the copy/paste data between DataGrid control and Excel.

Image 3

In terms of its simple form, we will make the actions with the keyboard:

  • Ctrl + A -> CopyAll -> Copy all DataGrid Rows
  • Ctrl + C -> Copy Selected -> Copy DataGrid selected Rows.
  • Ctrl + P -> Paste -> Paste data in DataGrid.

Other AttachProperties

We can add functionality with the next AttachProperties:

XML
ml:ExcelActions.EnabledCopyExcel  ="True" 
ml:ExcelActions.EnabledPasteExcel ="True" 
ml:ExcelActions.ContainsHeader    ="False"
ml:ExcelActions.CreateContextMenu ="True"
ml:ExcelActions.CancelWithErrors  ="False"
ml:ExcelActions.PaintFlash        ="True"
ml:ExcelActions.ColorFlash        ="Yellow"
ml:ExcelActions.ShowErrorMessages ="True"

CreateContextMenu

Enabled or disabled showed the context menu in the ItemsControl.

XML
ml:ExcelActions.CreateContextMenu="True"

True is its default value.

Image 4

ContainsHeader

Enabled or disabled copy control headers.

XML
ml:ExcelActions.ContainsHeader="False"

True is its default value.

Image 5

PaintFlash

Show or hide the color flash mark in the control that occurs when we copy or paste in the control.

XML
ml:ExcelActions.PaintFlash="True"

Image 6

ColorFlash

Setup the color of flash of Copy Paste Action (is only visible if the property PaintFlash is true).

XML
ml:ExcelActions.ColorFlash="Yellow"

Bruhes.Gray is its default value.

ShowErrorMessages

This property specifies whether show error message if an errors occurs. For example, if the data copied isn’t compatibility with the datasource of control.

XML
ml:ExcelActions.ShowErrorMessages="True"

True is its default value.

Image 7

In moving:

Image 8

CancelWithErrors

When we copy many rows from Excel, if any row isn’t correct and if an error occurs, this property specifies if paste the correct rows or don’t paste any row.

  • True -> Paste correct rows
  • False -> Don’t paste anything

True is a default value.

Image 9

Recommendations

It is necessary that the ItemsSource property of ItemsControl is an ObservableCollection type, because this type refreshes the items correctly and informs the remove and add changes.

Limitations

In this version, in the paste action, only insert data, doesn’t update the rows, or paste incomplete types.

If someone wants, ask me and I will develop this part.

History

  • 23rd February, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Cecabank
Spain Spain
MVP C# Corner 2017

MAP Microsoft Active Professional 2014

MCPD - Designing and Developing Windows Applications .NET Framework 4
MCTS - Windows Applications Development .NET Framework 4
MCTS - Accessing Data Development .NET Framework 4
MCTS - WCF Development .NET Framework 4

Comments and Discussions

 
QuestionHow do I get rid of the first column on the left, because I have a checkbox Pin
YJinzz1-Feb-23 16:37
YJinzz1-Feb-23 16:37 
QuestionIt works great, but seems to parse out DataTime fields into multiple columns in excel. Pin
Member 1391813710-Nov-22 11:50
Member 1391813710-Nov-22 11:50 
QuestionMy vote of 5 ! It works fine with Excel 365, executable file generated with VS2019 community Pin
seakleng15-Oct-21 2:17
professionalseakleng15-Oct-21 2:17 
GeneralMy vote of 5 Pin
seakleng15-Oct-21 2:02
professionalseakleng15-Oct-21 2:02 
QuestionHow to paste excel data without context menu Pin
xavras9-Jun-20 22:45
xavras9-Jun-20 22:45 
Questioncope a single cell using Ctrl + C Pin
Member 1155732026-Aug-19 18:16
Member 1155732026-Aug-19 18:16 
GeneralMy vote of 3 Pin
Пак Владимир28-Jan-19 2:39
Пак Владимир28-Jan-19 2:39 
QuestionUnable to Paste? Pin
CCB20107-Mar-18 4:27
CCB20107-Mar-18 4:27 
QuestionSelectionUnit="Cell" ? Pin
CCB201028-Feb-18 9:51
CCB201028-Feb-18 9:51 
QuestionSend Data from form to IE Pin
ajw198523-Feb-17 23:23
ajw198523-Feb-17 23:23 
SuggestionMulti language support Pin
ydude23-Feb-17 21:06
ydude23-Feb-17 21:06 
GeneralRe: Multi language support Pin
mag1315-Oct-21 6:26
mag1315-Oct-21 6:26 
Use resource class for resources as text, images, sound, etc, and just change file for selected language and/or for curent/default language
magsoft

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.