Maybe I'm missing the obvious here, but why doesn't this code sequence produce the correct results? It shows: "A7=15, B7=7, A1=14, B1=14, But everything should be 22". Is there some restriction on how CalculateFormula can be used? Do I need to invalidate cells so that they are recalculated or something?
Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];
sheet.Cells["A7"].PutValue(7);
sheet.Cells["B7"].PutValue(7);
sheet.Cells.CreateRange("A7", "A7").Name = "X_Val";
sheet.Cells.CreateRange("B7", "B7").Name = "Y_Val";
sheet.Cells["A1"].Formula = "=X_Val + Y_Val";
sheet.Cells["B1"].Formula = "=A7 + B7";
excel.CalculateFormula();
sheet.Cells["A7"].PutValue(15);
excel.CalculateFormula();
System.Console.Out.WriteLine("A7={0}, B7={1}, A1={2}, B1={3}\nBut everything should be {4}",
sheet.Cells["A7"].StringValue,
sheet.Cells["B7"].StringValue,
sheet.Cells["A1"].StringValue,
sheet.Cells["B1"].StringValue,
sheet.Cells["A7"].IntValue + sheet.Cells["B7"].IntValue);
This isn't actually what I want to do, but it demonstrates part of the problem. What I really want to do is load an existing excel spreadsheet with formulas that reference named fields, change the value in those fields programmatically, and then read the results dynamically. However, that doesn't seem to work at all. The above is a first step to see what the problem is.
Using version 3.5.3.0 (same issue with 3.5.2.0)