Post
Ask Question

An Easy Way To Transfer Data Between WPF Applications And Excel

I have developed an easy way to copy/paste the data between WPF Applications and Excel. This code is compatible for the controls inherited from System.Windows.Controls.ItemsControls. These are the controls with the ItemsSource property. Controls of this type are DataGrid, ListBox, ListView etc.

It's OpenSource and its code in GitHub.

It's very easy to use  and we will install the utility by NuGet and we will set up any properties of our control in the XAML code.

This is how it works

 
 

https://www.youtube.com/watch?v=Vym1OiF9z3E&feature=youtu.be

Prerequisites

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

It is necessary to have 4.5.2 NET Framework version or later.

Installation

We will install it from NuGet.

SetUp

After installation has been completed, we will setup the WPF Window and we add the next import in XAML,

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

We will add the properties given below in the ItemsControl in XAML. 

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

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

 

https://www.youtube.com/watch?v=79Q7g81klz8&feature=youtu.be

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

  • Ctrol + A - CopyAll - Copy all DataGrid rows
  • Ctrol + C - Copy Selected - Copy DataGrid selected rows.
  • Ctrol + P - Paste - Paste data in DataGrid.

Other AttachProperties

We can add functionality with the next AttachProperties. 

  1. ml: ExcelActions.EnabledCopyExcel = "True"  
  2. ml: ExcelActions.EnabledPasteExcel = "True"  
  3. ml: ExcelActions.ContainsHeader = "False"  
  4. ml: ExcelActions.CreateContextMenu = "True"  
  5. ml: ExcelActions.CancelWithErrors = "False"  
  6. ml: ExcelActions.PaintFlash = "True"  
  7. ml: ExcelActions.ColorFlash = "Yellow"  
  8. ml: ExcelActions.ShowErrorMessages = "True"   

CreateContextMenu

Enabled or disabled shows the context menu in the ItemsControl.

  1. ml:ExcelActions.CreateContextMenu="True"   

Its default value is True.


ContainsHeader

Enabled or disabled copy controls headers.

  1. ml:ExcelActions.ContainsHeader="False"   

Its default value is True.


PaintFlash

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

  1. ml:ExcelActions.PaintFlash="True"   

Its default value is True.

 

https://www.youtube.com/watch?v=nN77KFOriso&feature=youtu.be

ColorFlash

Setup the color of flash for the  copy paste action (it is only visible if the property PaintFlash is true).

  1. ml:ExcelActions.ColorFlash="Yellow"   

Bruhes.Gray is its default value.

ShowErrorMessages

This property shows an error message, if an errors occurs, for example, if the data copied is not compatibile with the datasource of control.

  1. ml:ExcelActions.ShowErrorMessages="True"   

Its default value is True.


 

https://www.youtube.com/watch?v=Oyk7uTvmMeM&feature=youtu.be

CancelWithErrors

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

True - Paste the correct rows

False - Don’t paste anything.

Its default value is True.


Recommendations

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

Limitations

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


Similar Articles