Export WPF DataGrid vers Excel (.xslx)

Version imprimable

Bonjour,

Je suis débutant en WPF (et plus généralement en .Net) et j'ai besoin d'aide sur une fonctionnalité d'export Excel qu'on me demande d'implémenter.

J'ai déjà vu plusieurs exemples sur le net, notamment avec la librairie EPPlus mais je n'arrive pas à les faire fonctionner dans mon cas.

Voila le code de mon DataGrid :

MainPanel.xaml

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
                    <DataGrid x:Name="Grille" AutoGenerateColumns="False" AlternatingRowBackground="#FFF8F8F8" AlternationCount="1" GridLinesVisibility="None">
                        <DataGrid.CommandBindings>
                            <CommandBinding Command="ApplicationCommands.Delete" Executed="SupprimerDonneeTableau"/>
                        </DataGrid.CommandBindings>
                        <DataGrid.ContextMenu>
                            <ContextMenu x:Name="GrilleMenu">
                                <MenuItem Header="Supprimer" Command="ApplicationCommands.Delete" />
                            </ContextMenu>
                        </DataGrid.ContextMenu>
                        <DataGrid.Columns>
                            <DataGridTextColumn x:Name="Id" Header="{Binding ApplicationStrings.Id, Source={StaticResource ResourceWrapper}}" SortMemberPath="Creation" Binding="{Binding Id}"/>
                            <DataGridTextColumn Header="{Binding ApplicationStrings.Creation, Source={StaticResource ResourceWrapper}}" SortMemberPath="Creation" Binding="{Binding Creation}"/>
                            <DataGridTextColumn Header="{Binding ApplicationStrings.Peremption, Source={StaticResource ResourceWrapper}}" SortMemberPath="Peremption"  Binding="{Binding Destruction}"/>
                            <DataGridTextColumn Header="{Binding ApplicationStrings.Designation, Source={StaticResource ResourceWrapper}}" SortMemberPath="Designation"  Binding="{Binding Designation}" Width="*" />
                        </DataGrid.Columns>
                    </DataGrid>

Merci d'avance.

bonjour
Je ne vois le rapport avec EEPLUS et WPF....
Bref
-1ere complication : il n' y a pas de collections Rows dans le DataGridWpf ...car WPF est oriente binding...
Pour retrouver chaque Row il faut passer par DataGrid.Items et DataGrid.ItemContainerGenerator.ContainerFromIndex(indexItem);
-Parcours des colonnes
-3eme complication :marcher "pieds en haut et tete en bas" car on utilise :
- DataGridColumn.GetCellContent(currentrow)
- caster vers le bon type de control(DataGridTextColumn=>textblock,DataGridCheckBoxColumn =>CheckBox,etc...) et
-recuperer la prop Content (dans notre exemple Text)
On utilise comme en winforms l'interop.excel...
code exemple xaml:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<Window x:Class="WpfDataGridExcel.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:WpfDataGridExcel"
        Title="MainWindow" Height="350" Width="525">
    <Window.Resources>
        <local:Products x:Key="l"></local:Products>
        <Style
            x:Key="styleTB1"
            TargetType="TextBlock">
            <Setter Property="Foreground" Value="White"/>
            <Setter Property="Background" Value="DarkBlue"/>
        </Style>
        <Style
            x:Key="styleTB2"
            TargetType="TextBlock">
            <Setter Property="Foreground" Value="Red"/>
            <Setter Property="Background" Value="Yellow"/>
        </Style>
 
    </Window.Resources>
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="auto"></RowDefinition>
            <RowDefinition></RowDefinition>
            <RowDefinition></RowDefinition>
        </Grid.RowDefinitions>
        <DockPanel Grid.Row="0">
            <Button x:Name="btnExport" 
                    Content="Export to Excel"
                    Click="btnExport_Click" >
            </Button>
        </DockPanel>
        <DataGrid 
            Grid.Row="1"
            x:Name="Grille" 
            AutoGenerateColumns="False" 
            AlternatingRowBackground="#FFF8F8F8" 
            AlternationCount="1" 
            GridLinesVisibility="None"
            ItemsSource="{StaticResource l}"
            >
 
            <DataGrid.Columns>
                <DataGridTextColumn Header="ID Product"   Binding="{Binding ID}"/>
                <DataGridTextColumn Header="QTE "   Binding="{Binding QTE}"/>
               <DataGridCheckBoxColumn 
            </DataGrid.Columns>
        </DataGrid>
        <TextBox
            Grid.Row="2"
            x:Name="tb"></TextBox>
    </Grid>
 
</Window>

code behind.cs du form:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Windows.Controls.Primitives;
//interop
using Microsoft.Office.Interop;
using XL = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace WpfDataGridExcel
{
    /// <summary>
    /// Logique d'interaction pour MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        private XL._Application appExcel = null;
        private XL.Workbook wbk=null;
        private XL.Worksheet sheet = null;
 
 
        private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            appExcel = new XL.Application();
            wbk = appExcel.Workbooks.Add();
            sheet = wbk.Worksheets.Add();
            appExcel.Visible = true;
            int colExcel = 1;
            int rowExcel = 1;
            XL.Range rng = null;
            // Headers  Colonnes
            foreach (DataGridColumn  ob in this.Grille.Columns)
            {
                rng = this.sheet.Cells[rowExcel, colExcel];
                rng.Value= ob.Header.ToString();
                colExcel++;
            }
            //Contenu Colonnes
            //Contenu caste -vers TextBlock - car nous utilisons DataGridTextColumn 
            rowExcel++;
            for (int i=0;i<this.Grille.Items.Count;i++ )
            {
              DataGridRow dRow=(DataGridRow) this.Grille.ItemContainerGenerator.ContainerFromIndex(i);
              if (dRow == null) continue;
              for (int j = 0; j < this.Grille.Columns.Count; j++)
              {
                  colExcel = j + 1;
                  DataGridColumn dCol = this.Grille.Columns[j];
                  rng = sheet.Cells[rowExcel, colExcel];
                  rng.Value = ((TextBlock)dCol.GetCellContent(dRow)).Text; 
 
              }
              rowExcel++;
            }
            wbk.Save();
 
            if (this.appExcel != null)
            {
                if (this.sheet != null) Marshal.ReleaseComObject(this.sheet);
                if (this.wbk != null) Marshal.ReleaseComObject(this.wbk);
                this.appExcel.Quit();
                Marshal.ReleaseComObject(this.appExcel);
 
            }
 
        }
 
    }
}

bon code...........

re
Oups ! ....
J'ai oublie de poster le code fichier data exemple utilise Product.cs:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Collections.ObjectModel;
 
namespace WpfDataGridExcel
{
    public class Product : INotifyPropertyChanged
    {
        public Product()
        {
            id = string.Empty;
            qte = 0;
            stockmin = 200;
 
        }
        public Product(string pid, int pq)
            : this()
        {
            id = pid;
            qte = pq;
 
 
        }
        private string id;
        public string ID
        {
            get { return id; }
            set
            {
                id = value; OnPropertyChanged("ID");
            }
        }
        private int qte;
        public int QTE
        {
            get { return qte; }
            set
            {
                qte = value; OnPropertyChanged("QTE");
            }
        }
        private int stockmin;
        public int STOCKMIN
        {
            get { return stockmin; }
            set
            {
                stockmin = value; OnPropertyChanged("STOCKMIN");
            }
        }
 
        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string np)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(np));
            }
 
        }
    }
    public class Products : ObservableCollection<Product>
    {
        private Product p;
        private Random rnd = new Random();
        public Products()
        {
 
            for (int i = 1; i < 500; i++)
            {
                int num = rnd.Next(100, 500);
                p = new Product("ID" + i.ToString(), num);
                this.Add(p);
            }
        }
 
    }
}

bon code...