Issues with formulas

Dec 6, 2012 at 9:57 AM

I've recently tried using formulas and I've encountered several issues.

First: when I directly define a formula for one notation, the other notation is not automatically updated.

 

XLWorkbook wb = new XLWorkbook(path);
IXLCell cell = wb.Worksheets.First().FirstCell();

string address = cell.Address.ToString(); // "A1"
string fA1 = cell.FormulaA1; // "B4"
string fR1C1 = cell.FormulaR1C1; // "R[3]C[1]"


cell.FormulaR1C1 = "R[2]C";


fR1C1 == "R[2]C"; // true
fA1 == "A3"; // false -> fA1 == "B4"

workbook.Save();
// => Cell A1 didn't change, because the sheet was saved in A1 notation
// and the R1C1 notation was automatically adapted to the A1 notation by Excel

 

Second, when I use the SetFormula method, the program crashes upon saving the workbook (and the other notation is not updated either).

 

XLWorkbook wb = new XLWorkbook(path);
IXLCell cell = wb.Worksheets.First().FirstCell();

string address = cell.Address.ToString(); // "A1"
string fA1 = cell.FormulaA1; // "B4"
string fR1C1 = cell.FormulaR1C1; // "R[3]C[1]"


cell.SetFormulaR1C1("R[2]C");


fR1C1 == "R[2]C"; // true
fA1 == "A3"; // false -> fA1 == "B4"

workbook.Save(); // => ArgumentNullException
// Workbook is now corrupted