Problème calcul Excel dans une application C# VSTO

Version imprimable

14/09/2015, 10h23
elbe06

Problème calcul Excel dans une application C# VSTO

Bonjour à tous,
j'ai développé une application C# VSTO : un document personnalisé Excel.
Cette application crée une feuille à partir de données extraites d'une base de données MySQL, crée des formules matricielles pour faire différents calculs sur ces données.
Ces données correspondent à différentes promotions d'élèves.
Une feuille différente est créée à chaque fois que je sélectionne une promotion dans un Action Pane Control.
Chaque promotion a un nombre à peu près identique d'élèves, pourtant le calcul de la seconde feuille prend toujours plus de temps que la première, quelle que soit la promotion que je choisis en premier.
Je ne comprends vraiment pas pourquoi...
Qui peut m'aider à y voir clair ?
Merci d'avance.
14/09/2015, 11h37
popo

Bonjour et bienvenue,

Sais-tu quelle partie est longue :
La récupération des données en base ?
Le remplissage de la feuille de calcul ?

Pur qu'on puisse t'aider, il faudrait :
- dans le premier cas : savoir quelle méthode tu utilise pour accéder à la base, si elle est distante ,ce genre de choses
- dans le premier comme le deuxième cas : expliquer ce que tu fais plus en détail (le code qui prend du temps à s'exécuter serait un minimum).

Sans plus d'informations, je ne pense pas que quelqu'un puisse te répondre.

Merci pour votre réponse.
Cela n'est pas dans l'accès à la base que le temps est long, mais dans les calculs des formules une fois les données copiées dans la feuille.
Il y a plusieurs zones de la feuille qui contiennent des formules :

ici :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
private void calculerTOTAUX1()
        {
            // zoneTOTAUX1 clear formules
            zoneTOTAUX1.Cells.ClearContents();
 
            // formules et calcul
            // pour chaque colonne
            for (int j = 1; j <= nbColonnesResultat; j++)
            {
                // définition de la colonne à traiter
                Excel.Range r = rangeRESU.Columns[nbColonnesInscription + j];
                string lettreColonne = ExcelUtils.lettreColonne(r);
                if (lettreColonne == null) return;
 
                // pour les 8 types de résultats
                for (int i = 1; i <= tabTitres1.Length - 2; i++)
                    zoneTOTAUX1.Cells[i, j].FormulaArray = "=SOMME(SI(" + r.Address.ToString() + "=\"" + tabTitres1[i - 1] + "\";"+ "SOUS.TOTAL(3;INDIRECT(\""+ lettreColonne + "\"&LIGNE("+ r.Address.ToString() + ")))))";
                // Total1
                zoneTOTAUX1.Cells[tabTitres1.Length - 1, j].FormulaLocal = "=SOMME(" + rgColTitres1.Resize[tabTitres1.Length - 2, 1].Offset[0, j].Address.ToString() + ")";
                // Total2
                zoneTOTAUX1.Cells[tabTitres1.Length, j].FormulaLocal = "=SOUS.TOTAL(3; " + r.Address.ToString() + ")-1";
            }
        }

et là :

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
private void calculerTOTAUX2()
        {
            string formule = "";
 
            // zoneTOTAUX2 clear formules
            zoneTOTAUX2.Cells.ClearContents();
 
            // formules et calcul
            // pour chaque colonne
            for (int j = 1; j <= nbColonnesResultat; j++)
            {
                // définition de la colonne à traiter
                Excel.Range r = zoneTOTAUX1.Columns[j];
 
                // pour les 4 informations dans tabTitres2
                formule  = "=SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"ADM*\")";
                formule += "+SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"AJAP\")";
                zoneTOTAUX2.Cells[1, j].FormulaLocal = formule;
                zoneTOTAUX2.Cells[2, j].FormulaLocal = "=SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"=AJ\")";
                zoneTOTAUX2.Cells[3, j].FormulaLocal = "=SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"=AUR\")";
                formule = "=SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"NAP\")";
                formule += "+SOMME.SI.ENS(" + r.Address.ToString() + ";" + rgColTitres1.Address.ToString() + ";" + "\"DEM\")";
                zoneTOTAUX2.Cells[4, j].FormulaLocal = formule;
            }
        }

Mais le problème se situe quand je crée la 2e feuille, j'ai l'impression que le document Excel est recalculé de nombreuses fois, beaucoup que plus que pour la 1ére, alors que les données sont de même taille.
J'ai essayé EnableCalculation à false pendant la définition des zones et à true pour le calcul mais cela ne change rien.

15/09/2015, 19h37
clementmarcotte
Bonjour,

Citation:

Envoyé par elbe06

Merci pour votre réponse.
Cela n'est pas dans l'accès à la base que le temps est long, mais dans les calculs des formules une fois les données copiées dans la feuille.
Il y a plusieurs zones de la feuille qui contiennent des formules :

Mais le problème se situe quand je crée la 2e feuille, j'ai l'impression que le document Excel est recalculé de nombreuses fois, beaucoup que plus que pour la 1ére, alors que les données sont de même taille.
J'ai essayé EnableCalculation à false pendant la définition des zones et à true pour le calcul mais cela ne change rien.

Je ne suis pas un expert en C# et je ne connais pas MySQL; mais je connais (au moins un peu) Excel. J'ai l'impression que tu affrontes les limites d'Excel. Excel est un bien gentil garçon très (trop) polyvalent, mais il a une faiblesse : il doit avoir tout le classeur en mémoire vive pour travailler. Puisque la première feuille semble bien se passer et que la seconde passe mal, cela me fait penser à un problème de mémoire vive disponible insuffisante, d'autant plus qu'une formule qui travaille sur des tableaux prend également beaucoup de mémoire. Cela veut donc dire qu'Excel est en compétition avec tous les programmes qui s'exécutent en mémoire vive pour garder son classeur. Cela inclut tous les services même les plus inutiles, comme la mise à jour automatique des programmes que les fabricants changent une fois par année.

Commence par jeter un œil sur la quantité de mémoire vive disponible avant de lancer ton programme et après ta première feuille. Tu devrais avoir une première indication sur les besoins en mémoire, en pensant que tu dois quand même en avoir un peu plus pour effectuer les calculs.

Ajout 16 sept 2015:

Deux autres choses que tu peux aussi regarder:

Si ce sont des choses que tu connais, mes plus plates excuses.

Tu peux éviter l'affichage dans Excel de toutes tes opérations intermédiaires et gagner du temps, souvent de manière appréciable:

En VBA cela se ferait comme ça et cela devrait se ressembler en C#, sauf qu'il faut ajouter ton objet Excel ou ton objet "addin" dans ton instruction:

Au début du processus :

Code:

application.screenupdating = false 'l'écran restera figé sur son dernier affichage

À la fin du processus :

Code:

application.screenupdating = tue ' l'affichage revient à la normale.

Également, si tu n'as pas besoin de conserver les formules, mais juste les résultats, tu peux utiliser les fonctions intrinsèques d'Excel directement dans ton code et sans les ajouter à ta feuille, en passant par WorksheetFunction et le nom de la fonction en anglais.

Par exemple, en VBA, pour mettre la somme de "A1:A10" dans "A12":
Code:

1 2 3 4 5 Sub total() Set myRange = Worksheets("Feuil1").Range("A1:a10") Answer = Application.WorksheetFunction.Sum(myRange) Range("a12").Value = Answer End Sub
J'ai adapté un exemple de l'aide de VBA-Excel 2010. J'ai bien essayé les vieilles méthodes pour faire plus court, mais VBA est maintenant bien capricieux, je trouve. (En principe, je devrais être content parce que plus capricieux veut dire moins passoire et plus formel, mais cela dérange mes habitudes.)
28/09/2015, 10h27
Pierre Fauconnier

Salut.

A la remarque judicieuse de Clément (Salut Clément) sur le screenupdating, j'ajouretais de passer en calcul manuel et de restituer le calcul automatique en fin de procédure, surtout avec des matricielles. Il faut savoir qu'une matricielle est une formule qui calcule en boucle sur la plage de données, ça devient vitre très coûteux en ressource. Des fonctions comme MOYENNE, SOMME... (bref, toute fonction qui calcule sur un ensemble de cellules) sont très coûteuses lors d'un calcul automatique car chaque modification d'une cellule de la plage concernée induit un recalcul des ces fonctions.