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.Cells.ClearContents();
for (int j = 1; j <= nbColonnesResultat; j++)
{
Excel.Range r = zoneTOTAUX1.Columns[j];
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;
}
}