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,
- xmlns:ml="clr-namespace:MoralesLarios.Utilities.Excel;assembly=MoralesLarios.Utilities"
We will add the properties given below in the ItemsControl in XAML.
- <DataGrid x:Name="dataGrid" ml:ExcelActions.EnabledCopyExcel="True" <!—- enabled copy/copiall excel --> ml:ExcelActions.EnabledPasteExcel="True"
- <!—- 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.
- 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 shows the context menu in the ItemsControl.
- ml:ExcelActions.CreateContextMenu="True"
Its default value is True.
ContainsHeader
Enabled or disabled copy controls headers.
- 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.
- 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).
- 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.
- 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.
Juan Francisco Morales LariosPosted Mar 16, 2017, 6:06 PM
It's done, the bug is fixed. You can update nuget reference to 1.0.0.1 version. Thank you for your helper.
Juan Francisco Morales LariosPosted Mar 16, 2017, 8:07 AM
I will correct this bug and I will update the code. Thank you.
Juan Francisco Morales LariosPosted Mar 16, 2017, 8:05 AM
OK, I don't know this bug.
Sergio TMPosted Mar 15, 2017, 4:39 PM
Hi, when the datagrid have an itemsource and that ObservableCollecion has myCollection.count()==0 and you try to paste from excel,.. there is an error: "An unhandled exception of type 'System.ArgumentOutOfRangeException' ocured in mscorlib.dll" This only happens when te collection is empty.