Cell.Formula


#1

Hi,

I have problems to read a formula from a cell.

I create a new excel file like this:

Excel excel = new Excel();

Worksheet sheet = excel.Worksheets[0];
sheet.Name = "Sheet1";

Cells cells = sheet.Cells;

Cell cell_a1 = cells["A1"];
Cell cell_a2 = cells["A2"];
Cell cell_a3 = cells["A3"];

cell_a1.PutValue("test");
cell_a2.Formula = "=A1";
cell_a3.Formula = "=Sheet1!A1";

excel.CalculateFormula();

Console.WriteLine("Cell: {0}, string: {1}, formula: {2}", cell_a1.Name, cell_a1.StringValue, cell_a1.Formula);
Console.WriteLine("Cell: {0}, string: {1}, formula: {2}", cell_a2.Name, cell_a2.StringValue, cell_a2.Formula);
Console.WriteLine("Cell: {0}, string: {1}, formula: {2}", cell_a3.Name, cell_a3.StringValue, cell_a3.Formula);

excel.Save("Test_011.xls");

The output on the console is:
Cell: A1, string: test, formula:
Cell: A2, string: test, formula: =A1
Cell: A3, string: test, formula: =Sheet1!A1

That's correct.

Now I open the file and do the same "Console.WriteLine", but the result is not what I'm expecting:

excel = new Excel();
excel.Open("Test_011.xls");

sheet = excel.Worksheets[0];

cells = sheet.Cells;

cell_a1 = cells["A1"];
cell_a2 = cells["A2"];
cell_a3 = cells["A3"];

Console.WriteLine("Cell {0}, string = {1}, formula = {2}", cell_a1.Name, cell_a1.StringValue, cell_a1.Formula);
Console.WriteLine("Cell {0}, string = {1}, formula = {2}", cell_a2.Name, cell_a2.StringValue, cell_a2.Formula);
Console.WriteLine("Cell {0}, string = {1}, formula = {2}", cell_a3.Name, cell_a3.StringValue, cell_a3.Formula);

Cell: A1, string: test, formula:
Cell: A2, string: test, formula: =A1
Cell: A3, string: test, formula: =

Why is the formula in cell A3 not set?


Andreas


#2

Please try this attached fix.


#3

Hi Laurence,

thank you very much for the quick fix. It works fine.

But I found another problem:

I create a new file:

Excel excel = new Excel();

Worksheet sheet = excel.Worksheets[0];

sheet.Cells["A1"].PutValue(40);
sheet.Cells["B1"].Formula = "=IF(A3>0, ROUND(A1/A3*100,2), 0)";
sheet.Cells["A2"].PutValue(60);
sheet.Cells["B2"].Formula = "=IF(A3>0, ROUND(A2/A3*100,2), 0)";
sheet.Cells["A3"].Formula = "=SUM(A1:A2)";
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";

excel.CalculateFormula();

Console.WriteLine("Cell A1 string: {0}", sheet.Cells["A1"].StringValue);

Returns ==> Cell A1 string: 40

Console.WriteLine("Cell A3 string: {0}", sheet.Cells["A3"].StringValue);

Returns ==> Cell A3 string: 100

Console.WriteLine("Cell B1 string: {0}", sheet.Cells["B1"].StringValue);

Returns ==> Cell B1 string: 0
This should be 40, or am I wrong?

Console.WriteLine(" formula: {0}", sheet.Cells["B1"].Formula);

Returns the correct formula.

Now I save the file and open it again.

excel.Save("Z:\\PCSE07\\Test_012.xls");

excel = new Excel();
excel.Open("Z:\\PCSE07\\Test_012.xls");

sheet = excel.Worksheets[0];

Console.WriteLine("After save file and open file");
Console.WriteLine("Cell B1 string: {0}", sheet.Cells["B1"].StringValue);

Returns still 0

Console.WriteLine(" formula: {0}", sheet.Cells["B1"].Formula);

And at this statement I get an System.ArgumentOutOfRangeException.

What's wrong in my program?


Andreas


#4

Please try this fix.


#5

Hi Laurence,

thanks for the quick response.

The first part of my problem is solved, but after I open the excel file and try to access the formula in cell B1 with

Console.WriteLine("{0}", sheet.Cells[“B1”].Formula);

i got the same error as I posted before:

System.ArgumentOutOfRangeException


Andreas


#6

I used the following code and all work fine.


Excel excel = new Excel();

Worksheet sheet = excel.Worksheets[0];

sheet.Cells["A1"].PutValue(40);
sheet.Cells["B1"].Formula = "=IF(A3>0, ROUND(A1/A3*100,2), 0)";
sheet.Cells["A2"].PutValue(60);
sheet.Cells["B2"].Formula = "=IF(A3>0, ROUND(A2/A3*100,2), 0)";
sheet.Cells["A3"].Formula = "=SUM(A1:A2)";
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";

excel.CalculateFormula();

Console.WriteLine("Cell A1 string: {0}", sheet.Cells["A1"].StringValue);

Console.WriteLine("Cell A3 string: {0}", sheet.Cells["A3"].StringValue);


Console.WriteLine("Cell B1 string: {0}", sheet.Cells["B1"].StringValue);

Console.WriteLine(" formula: {0}", sheet.Cells["B1"].Formula);


excel.Save("c:\\abc.xls");

excel = new Excel();
excel.Open("c:\\abc.xls");

excel.CalculateFormula();

Console.WriteLine("Cell A1 string: {0}", sheet.Cells["A1"].StringValue);

Console.WriteLine("Cell A3 string: {0}", sheet.Cells["A3"].StringValue);


Console.WriteLine("Cell B1 string: {0}", sheet.Cells["B1"].StringValue);

Console.WriteLine(" formula: {0}", sheet.Cells["B1"].Formula);


#7

Hi Laurence,

after open the saved file you forgot

sheet = excel.Worksheets[0];

When I insert this statement in your code, I can reproduce the error.


Andreas


#8

Thank you. Now I find the problem. I will check and fix it ASAP.