C# manipulation Excel, améliorer la rapidité de traitement de ma méthode.

Version imprimable

Bonjour,

Je vous embête encore je sais :p

J'ai réussi à transférer l'intégralité de ma DataGridView dans les cases excel que je voulais, jusque la pas de soucis. la méthode export est relativement rapide sa peut aller.

Maintenant je voulais que toutes mes cases soit entourées pour crée visuellement un vrai tableau, ma méthode fonctionne j'ai le résultat attendue, mais alors mon application met un temps fou a traiter une par une mes cases...

voici mon code (enfin que la parti qui insert mes données):

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
// toutes nos variables nécessaire au boucle d'export
                int LigneDataGridView = 0;
                int ColonneDataGridView = 0;
                int LigneExcel = 27;
                int ColonneExcel = 3;
                int LigneExcelNbRST = 27;
                int NbRST = 1;
 
                //Supprime les colonnes indésirable
                dataGridView_ContenueXML.Columns.Remove("Date_Ajout");
                dataGridView_ContenueXML.Columns.Remove("ID");
 
                for (LigneDataGridView = 0; LigneDataGridView <= dataGridView_ContenueXML.RowCount - 1; LigneDataGridView++)
                {
                    for (ColonneDataGridView = 0; ColonneDataGridView <= dataGridView_ContenueXML.ColumnCount - 1; ColonneDataGridView++)
                    {
                        DataGridViewCell cell = dataGridView_ContenueXML[ColonneDataGridView, LigneDataGridView];
                        xlWorkSheet.Cells[LigneExcel, ColonneExcel] = cell.Value;
 
                        //Crée une bordure sur les cases de tout notre tableau.
                        //Range est une zone selectionnée de façon virutelle
                        Excel.Range RangeDonneeXml = xlWorkSheet.Cells[LigneExcel, ColonneExcel];
                        Excel.Range RangeNbrRst = xlWorkSheet.Cells[LigneExcelNbRST, 2];
                        FullBorder(RangeDonneeXml);
                        FullBorder(RangeNbrRst);
 
                        // Ajoute le nombre de RST ajouter
                        xlWorkSheet.Cells[LigneExcelNbRST, 2] = NbRST;
 
                        ColonneExcel++;
                    }
                    ColonneExcel = 3;
                    LigneExcel++;
                    LigneExcelNbRST++;
                    NbRST++;
                }

voici ma methode FullBorder() :

Code:

1
2
3
4
5
6
7
8
9
10
11
private void FullBorder(Excel.Range rng)
        {
            Excel.Border border_Bottom = rng.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            Excel.Border border_Top = rng.Borders[Excel.XlBordersIndex.xlEdgeTop];
            Excel.Border border_Right = rng.Borders[Excel.XlBordersIndex.xlEdgeRight];
            Excel.Border border_Left = rng.Borders[Excel.XlBordersIndex.xlEdgeLeft];
            border_Bottom.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_Top.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_Right.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_Left.LineStyle = Excel.XlLineStyle.xlContinuous;
        }

j'ai pensée à une solution qui pourrait peut être gérer mon soucis, sa serait de faire comme une sélection range en diagonal et remplir le tout en bordure.

Excel.Range selection = xlWorkSheet.Cells[27, 3], xlWorkSheet.Cells[LigneExcel , 5]; // ligne brouillon mais pour donnée le style que j'aimerai

et après je sais remplir les contours en bordure mais comment dire que ce Range je veux les contours et tout l'intérieur ?

enfin je me noie un peut dans tous les forums et tutos je me perd tout seul..

en espérant que de remplir les bordures que à la fin accélère le déroulement de ma méthode...

ps: si vous voulez ma méthode entière pour la tester chez vous demandé moi je vous la donnerais ;)

04/05/2016, 07h58
ericlm128

Effectivement faire un seul range de ta zone et appliquer les bordures voulu serai plus rapide.
Tu peut enregistrer une macro Excel afin de voir le code correspondant qui pourra t'aider.

Sinon tu peux aussi bloquer l'affichage d'Excel pendant la mise à jour avec 'ScreenUpdating'.
https://msdn.microsoft.com/en-us/lib...nupdating.aspx

je n'affiche pas le fichier Excel a l'utilisateur donc pas besoin de figé l'affichage

par contre j'ai trouver ma méthode qu'il me faudrait mais j'ai une erreur que je ne comprend pas.

voila mon code:

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
if (fichier.Exists)
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range xlsRange;
 
                // on charge notre document excel et sélectionnons notre classeur à traiter
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(LienExcelTemplate);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
                //On ajoute la date au document au moment de la création
                xlWorkSheet.Cells[7, 5] = System.DateTime.Now.ToShortDateString();
 
                // toutes nos variables nécessaire au boucle d'export
                int LigneDataGridView = 0;
                int ColonneDataGridView = 0;
                int LigneExcel = 27;
                int ColonneExcel = 3;
                int LigneExcelNbRST = 27;
                int NbRST = 1;
 
                //Suprime les colonnes indésirable
                dataGridView_ContenueXML.Columns.Remove("Date_Ajout");
                dataGridView_ContenueXML.Columns.Remove("ID");
 
                // Boucle pour exporter le DataGridView dans Excel vec la meme architecture
                for (LigneDataGridView = 0; LigneDataGridView <= dataGridView_ContenueXML.RowCount - 1; LigneDataGridView++)
                {
                    for (ColonneDataGridView = 0; ColonneDataGridView <= dataGridView_ContenueXML.ColumnCount - 1; ColonneDataGridView++)
                    {
                        DataGridViewCell cell = dataGridView_ContenueXML[ColonneDataGridView, LigneDataGridView];
                        xlWorkSheet.Cells[LigneExcel, ColonneExcel] = cell.Value;
 
                        //Crée une bordure sur les cases de tout notre tableau.
                        //Excel.Range RangeDonneeXml = xlWorkSheet.Cells[LigneExcel, ColonneExcel];
                        //Excel.Range RangeNbrRst = xlWorkSheet.Cells[LigneExcelNbRST, 2];
                        //FullBorder(RangeDonneeXml);
                        //FullBorder(RangeNbrRst);
 
                        // Ajoute le nombre de RST ajouter
                        xlWorkSheet.Cells[LigneExcelNbRST, 2] = NbRST;
 
                        ColonneExcel++;
                    }
                    ColonneExcel = 3;
                    LigneExcel++;
                    LigneExcelNbRST++;
                    NbRST++;
                }
 
                // Mettre le border couleur noir
                xlsRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[27, 3], xlWorkSheet.Cells[LigneExcel, 5]);
                xlsRange.Borders.ColorIndex = 1;
 
                // On sauvegarde nos modifications.
                xlWorkBook.SaveAs(LienExcelTemporaire);
                xlWorkBook.Close();
                xlApp.Quit();

et sa me génère cette erreur la :

Code:

xlsRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[27, 3], xlWorkSheet.Cells[LigneExcel, 5]);

Une exception non gérée du type 'Microsoft.CSharp.RuntimeBinder.RuntimeBinderException' s'est produite dans System.Core.dll

Informations supplémentaires : 'object' ne contient pas de définition pour 'get_Range'

je ne comprend pas j'essaie différente solution je pense que c'est la bonne mais je doit passer à coter d'un petit détail...

04/05/2016, 22h19
clementmarcotte

Bonjour,

Il n'y a pas de méthode get_range dans (VBA (le modèle-objets)) d'Excel.

J'ai également de gros doutes sur la possibilité d'utiliser get_item sans que cela plante.

Je ne cause pas vraiment C#, mais piloter Excel par automation c'est utiliser C# (ou VB.net) pour faire réaliser des opérations Excel par VBA. Si tu déclares un objet représentant l'application Excel, les propriétés et méthodes de ton objet Excel sont celles reconnues par VBA-Excel et non celles du Framework .net

À première vue et sans tester ta ligne devrait ressembler à cela:

Code:

xlsRange = xlWorkSheet.Range(xlWorkSheet.Cells(27, 3), xlWorkSheet.Cells((LigneExcel, 5));
04/05/2016, 23h03
clementmarcotte

Citation:

Envoyé par clementmarcotte

Bonjour,

Il n'y a pas de méthode get_range dans (VBA (le modèle-objets)) d'Excel.

J'ai également de gros doutes sur la possibilité d'utiliser get_item sans que cela plante.

Je ne cause pas vraiment C#, mais piloter Excel par automation c'est utiliser C# (ou VB.net) pour faire réaliser des opérations Excel par VBA. Si tu déclares un objet représentant l'application Excel, les propriétés et méthodes de ton objet Excel sont celles reconnues par VBA-Excel et non celles du Framework .net

À première vue et sans tester ta ligne devrait ressembler à cela:

Code:

xlsRange = xlWorkSheet.Range(xlWorkSheet.Cells(27, 3), xlWorkSheet.Cells((LigneExcel, 5));

Mais je doute que cela fonctionne.

Dans l'objet range l'adresse est de type String.
Dans l'objet cell et as collection cells() les indices sont des nombres.

donc

Code:

quelque_chose = Cells(27,3).value

Code:

quelque_chose = Cells(27,3)

Code:

quelque_chose = Range("C27").value

Code:

quelque_chose = Range("C27").value

Représentent tous la même chose, parce que value est la propriété par défaut.

Mais, n'oublie pas que VBA ne se conforme pas entièrement (pour être poli) aux normes de la POO. Cela fait que plusieurs propriétés d'objets, sont également des objets en eux mêmes.

C'est Cells(Numéro de ligne, Numéro de Colonne) et il n'y a pas de ligne numéro 0, ni de colonne numéro 0
04/05/2016, 23h44
ptiiben

Salut merci pour ton aide

bon pour être honnête tu ma plus perdu que guidé héhé..

j'ai essayer ta ligne mais comme tu t'en doutais ne fonctionne pas j'ai essayer de la toucher dans tous les sens voir si sa pouvais aller, ensuite j'ai rentrer mes coordonnées à la main en string et non en Int sa change rien également ..

je perd un temps fous juste pour des bordures, c'est à sens taper la tête contre les murs ^^

bon bah je retourne dans ma folle aventure de recherche de doc, si quelqu'un peut m'aider entre temps je suis preneur sur ce coup la ;)
05/05/2016, 00h32
ptiiben
Merci la patience :D

donc me re voila avec une solution et qui fonctionne en plus !
Code:

1 2 3 // Mettre le border couleur noir xlsRange = xlWorkSheet.get_Range("B27", "E31"); xlsRange.Borders.ColorIndex = 1;
par contre mon soucis dans cette histoire, comment je fait pour convertir mes valeurs qui sont depuis le début sous ce format ( 2,2 ) en ("B2")...

outre ce petit détail, cette méthode règle nickel mon problème de lenteur de mon ancienne méthode !!! donc c'est déjà un bon point.
05/05/2016, 02h16
clementmarcotte

Bonjour,

Pour une cellule:

Range("a1").column = 1
Range("a1").row = 1
Range("a1).address = "A1" ou peut-être "$A$1"
cells(1,1).address = "A1" ou peut-être "$A$1"

P.S. Les crochets de C# n'ont pas la même utilité en (VBA)-Excel, ce qui pourrait peut-être expliquer la lenteur initiale.

Les crochets dans Excel sont un raccourci pour désigner la fonction EVALUE(expression quelconque) ou EVALUATE(expression quelconque) en anglais. Dans VBA, c'est un raccourci pour la méthode EVALUATE.

Le passage par EVALUATE est inutile parce que l'objet Range a déjà sa propriété Value, même si elle est implicite dans ton code. EVALUATE est là pour des expressions plus complexes comme des équations.
05/05/2016, 10h34
ericlm128

Citation:

Envoyé par ptiiben

je n'affiche pas le fichier Excel a l'utilisateur donc pas besoin de figé l'affichage

Tu as fait le test au moins ? Ou pour toi c'est une évidence acquise ?
Personnellement, je pense que même si ta fenêtre est invisible tu gagnera en performance.

Prenons le contexte de ton traitement lourd dans Excel avec une fenêtre visible, tu croit que c'est la carte vidéo qui n'est pas assez rapide pour affiche tout ?
05/05/2016, 11h22
ptiiben
Citation:

Envoyé par ericlm128

Tu as fait le test au moins ? Ou pour toi c'est une évidence acquise ?
Personnellement, je pense que même si ta fenêtre est invisible tu gagnera en performance.

Prenons le contexte de ton traitement lourd dans Excel avec une fenêtre visible, tu croit que c'est la carte vidéo qui n'est pas assez rapide pour affiche tout ?

Pour ne pas mourir bête j'ai quand même essayé ta proposition. visuellement je dirais que sa ma effectivement amélioré légèrement la création de mon fichier, il faudrait que je fasse le test avec un fichier XML assez lourd et que je reproduise avec et sans cette méthode ;) merci

pour ceux qui voudrait voir le code pour bloquer l'affichage de Excel avant un lourd traitement :
Code:

1 2 3 Excel.Application xlApp; xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); xlApp.ScreenUpdating = false;
Pensez bien à relancer l'affichage sinon quand vous ouvrez excel l'application vous affichera une page blanche ;)

Code:

xlApp.ScreenUpdating = true;

par contre pour mon soucis d'insertion de de mes cases au bon formats j'ai du mal a résoudre mon problème.

comment convertir une coordonnée de 1,1 en "A1" ( en gros un int en string, jusque la sa va easy) mais faire comprendre que le chiffre de gauche doit se transformer en lettre qui correspond ...

Bonsoir à tous,

Je suis content ce soir j'ai réussi avec de la persévérance. Mon dernier soucis était de convertir mes coordonnées de (1,1) en ("A1") par exemple..

donc voici ma solution simple et efficace :

Code:

1
2
3
4
5
// Mettre le border couleur noir
int coord_fin = LigneExcel - 1;
string Coord_debut = "B27";
string Coord_fin = "E"+coord_fin.ToString();
xlsRange = xlWorkSheet.get_Range(Coord_debut, Coord_fin);

Dans l'esprit de communauté je vous donne ma méthode DataGridViewToExcel, qui permet de remplir d’après un datagridview un fichier Excel en partant de la case que vous voulez et crée ses bordures. Le code est commenté donc je pense facile à comprendre. J’espère que sa aidera certain ;)

Toute critique sur mon code me permettra de m'améliorer donc n’hésiter pas ;)

Ma méthode DataGridViewToExcel :

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
private void DataGridViewToExcel()
        {
            string LienExcelTemplate = @"VOTRE LIEN";
            string LienExcelTemporaire = @"VOTRE FICHIER MODIFIE";
            FileInfo fichier = new FileInfo(LienExcelTemplate);
 
            // On vérifie que le fichier Template existe.
            if (fichier.Exists)
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range xlsRange;
                object misValue = System.Reflection.Missing.Value;
 
                // on charge notre document excel.
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(LienExcelTemplate);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
                //On ajoute la date au document au moment de la création.
                xlWorkSheet.Cells[7, 5] = System.DateTime.Now.ToShortDateString();
 
                // toutes nos variables nécessaire au boucle d'export
                int LigneDataGridView = 0;
                int ColonneDataGridView = 0;
                int LigneExcel = 27;
                int ColonneExcel = 3;
                int LigneExcelNbRST = 27;
                int NbRST = 1;
 
                //Suprime les colonnes indésirable
                dataGridView_ContenueXML.Columns.Remove("Date_Ajout");
                dataGridView_ContenueXML.Columns.Remove("ID");
 
                // On bloque l'affichage de Excel pour améliorer la rapidité
                xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                xlApp.ScreenUpdating = false;
 
                // Boucle pour exporter le DataGridView dans Excel.
                for (LigneDataGridView = 0; LigneDataGridView <= dataGridView_ContenueXML.RowCount - 1; LigneDataGridView++)
                {
                    for (ColonneDataGridView = 0; ColonneDataGridView <= dataGridView_ContenueXML.ColumnCount - 1; ColonneDataGridView++)
                    {
                        DataGridViewCell cell = dataGridView_ContenueXML[ColonneDataGridView, LigneDataGridView];
                        xlWorkSheet.Cells[LigneExcel, ColonneExcel] = cell.Value;
 
                        // Ajoute le nombre de RST ajouter
                        xlWorkSheet.Cells[LigneExcelNbRST, 2] = NbRST;
 
                        ColonneExcel++;
                    }
                    ColonneExcel = 3;
                    LigneExcel++;
                    LigneExcelNbRST++;
                    NbRST++;
                }
 
                // Mettre le border couleur noir
                int coord_fin = LigneExcel - 1;
                string Coord_debut = "B27";
                string Coord_fin = "E"+coord_fin.ToString();
                xlsRange = xlWorkSheet.get_Range(Coord_debut, Coord_fin);
                xlsRange.Borders.ColorIndex = 1;
 
                // On relance l'affiche de l'application
                xlApp.ScreenUpdating = true;
 
                // On sauvegarde nos modifications.
                xlWorkBook.SaveAs(LienExcelTemporaire);
                xlWorkBook.Close();
                xlApp.Quit();
 
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
            else
            {
                MessageBox.Show("Le fichier Template est absent!!");
            }
        }

06/05/2016, 10h38
ericlm128

Juste quelques précisions.
LigneExcel et ColonneExcel semble correspondre au début de remplissage dans la feuille Excel ? Ce sont des offset ?
A quoi servent LigneExcelNbRST et NbRST ?
La mise en forme (bordure) n'est valable que si on respecte le même nombre de colonne que toi (4) ?

misValue ne semble pas utilisé.
06/05/2016, 16h17
ptiiben

1 pièce(s) jointe(s)
Une image pour mieux comprendre:
Pièce jointe 208932

Pour répondre j’espère le plus clairement , pour LigneExcelNbRST et NbRST, ce sont 2 variables qui me permette d'afficher le nombre d’ajout de ligne (ce qui correspond pour moi au nombre de Bloc que j'ai a expédier.) cette donnée n'étant pas dans la datagridview je la crée en dynamique.
LigneExcelNbRST = la ligne d'insertion (dans mon cas l'un en dessous de l'autre)
NbRST = le nombre de bloc

LigneExcel et ColonneExcel semble correspondre au début de remplissage dans la feuille Excel ? Ce sont des offset ?
LigneExcel et ColonneExcel sont mes deux variables qui me permettre de me balader dans la feuille excel pour insérer les données dans l'ordre souhaiter.
au début de mon code quand je crée ses variables je leur est donnée une valeur de départ car mon tableau ne commence pas à la case "A1" mais commence à la case "B27"

La mise en forme (bordure) n'est valable que si on respecte le même nombre de colonne que toi (4) ? Non du tout
ma méthode présenter ici est écrite pour moi mais elle peut être généraliser. en gros je simule une sélection en diagonal, donc il faut 2 paramètres la case de début et la case de fin. par exemple "A1" à "B2" ...
Code:

1 2 xlsRange = xlWorkSheet.get_Range("A1", "B2"); xlsRange.Borders.ColorIndex = 1;
Le problème c'est que je ne travaillai pas dans ce format la moi. il me fallait convertir '1 ,1' en "A1" par exemple.

Voila comment j'ai procéder pour convertir mes valeurs et rendre mes coordonnées dynamiques.

Code:

int coord_fin = LigneExcel - 1;

j'ai donc récupérer ma variable LigneExcel qui a pour valeur la ligne suivante pour une prochaine insertion, je lui est décrémenter 1 car moi je voulais me contenter de rester dans les cases remplie par des données.

Code:

string Coord_debut = "B27";

Ici j'utilise le format normal car je connais les coordonnées de départ de mon tableau.

Code:

string Coord_fin = "E"+coord_fin.ToString();

Ensuite je construit ma coordonnée final, donc un string forcement pour ce caler au format demander, "E" car je sais que mon tableau ne peut pas être plus large + le N° de la derniere ligne utiliser dans la phase d'insertion de donnée, le tout converti en strin

Code:

xlsRange = xlWorkSheet.get_Range(Coord_debut, Coord_fin);

ici nous créons notre sélection en diagonal.

Enfin cette ligne indique le style de trait que vous utilisé sur la sélection.

Code:

xlsRange.Borders.ColorIndex = 1;

Pour: misValue
j'avais suivie un tuto ou il l'utilisait et j'ai changer le code. Je l'avais laisser au cas ou que je le réutilise et c'est vrai que je sais pas trop à quoi cela sert désoler ..

j’espère être clair et cas vous aidera tous :D