Formulas in R1C1 style

FLX TMS FlexCel for VCL & FMX
1

Hi,

Can you show me an example of how to use formulas in R1C1 style? Using the excel type libraries, you could write the formula for a cell as follows :
  Cells.Item[10,15].Value := '=SUM(R[-2]C:R[-1]C)'

How can this be done using flexcel?
I tried it with TFormula.Create('=SUM(R[-2]C:R[-1]C)') but that results in an error during code execution.

Greetings

Luc

2

Hi,
There are a couple of properties you need to set:
1)xls.FormulaReferenceStyle := TReferenceStyle.R1C1 :
http://www.tmssoftware.biz/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.FormulaReferenceStyle.htm

This will affect how you can insert the formulas (so the formula you quoted would be valid), but Excel will still show the formulas in the file as R1C1.

2) xls.OptionsR1C1 := true
http://www.tmssoftware.biz/flexcel/hlp/vcl/index.htm?FlexCel.Core.TExcelFile.OptionsR1C1.htm
OptionsR1C1 only affects how Excel shows the formulas, but not how FlexCel expects them. So TFormula.Create('=SUM(R[-2]C:R[-1]C)') would still fail: You need to use FormulaReferenceStyle for FlexCel as shown in 1)

Now, note that no matter what you use, the main class to deal with Addresses in A1 or R1C1 is TCellAddress. With it you can convert between text like this:

var
  a: TCellAddress;
begin
  //From string to number
  a:= TCellAddress.Create(“B5”);
  xls.GetCellValue(a.Row, a.Col);

  //from numbers to string
  a := TCellAddress.Create(5, 2);
  DoSomething(a.CellRef);
end;

TCellAddress also has R1C1 methods.

3

Adrian,

Thanks for your quick response!

Using TCellAddress to encode column numbers instead of using R1C style is the best solution for me. Thanks for your tip!

Greetings

Luc