Click here to Skip to main content
14,696,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,
I developed a C # VSTO : a custom Excel document.
This application creates a sheet from data from a MySQL database , create array formulas for different calculations on that data.
These data correspond to different promotions of students.
A different sheet is created every time I select a promotion in an Action Pane Control.
Each class has about the same number of students , however, the calculation of the second sheet always takes more time than the first , whatever promotion I choose first.
I really do not understand why ...
Who can help me to see clearly ?
Thank you in advance.

Update

Data are copied from a datatable to a sheet. After copy, several areas will contain array formulas.
The problem isn't when copying, nor creating or calculating formulas, I guess.
The problem is when I create a second sheet , I feel that the Excel document is recalculated many times, much more than for the 1st , while the data are the same size .
I tried EnableCalculation to false for the definition of zones and to calculate true but that does not change.
For example, the formulas in ZoneTotaux2 :
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;
        }
    }
Posted
Updated 14-Sep-15 4:11am
v2
Comments
Patrice T 14-Sep-15 5:51am
   
Define more time please.
Without knowing what the sheet is doing, it will be complicated to guess the why.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900