|
One common thing you might want to do when entering formulas in FlexCel is to change the column or rows where they appear. So for example, let’s imagine you have this formula in A1:
=A2 * 2
And you want to expand the formula to Columns B to Z.
In B1, you will want the formula =B2 * 2, in C1 you will want =C2 * 2 and so on.
There are multiple ways to achieve this:
1)You can enter the formula in A1:
xls.SetCellValue(1, 1, TFormula.Create('=A2 * 2'));
And then copy the cell to the range B:X:
xls.InsertAndCopyRange(TXlsCellRange.Create(1, 1, 1, 1), 1, 2, 25, TFlxInsertMode.NoneRight, TRangeCopyMode.All);
This will work the same as in Excel, and the formulas will be adapted when copied. Same as in Excel absolute formulas (like $B$1) won’t be changed, but relative formulas will change when copied.
2)You can manually create the formulas by using TCellAddress
TCellAddress is the record you use in FlexCel to convert cell references from/to numbers to letters. Here is a little example on how you can get the Row and column from the string "B5" and also how to get the string "B5" from the row and column:
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;
So, for our original example, we could use some code like this:
for col := 1 to 26 do
begin
xls.SetCellValue(1, col, TFormula.Create('=' + TCellAddress.Create(2, col).CellRef +' * 2'));
end;
3)Using R1C1 notation
R1C1 is an alternative notation to the classical “A1” notation, to describe formulas based in their rows and columns, instead of in a letter and a number. R1C1 is completely equivalent to A1, but has the advantages of always using row numbers, and that the cells are relative to their position, which is what you normally want. You can find a lot of information in R1C1 cell references internet just by a web search, so we will focus in how to use it from FlexCel.
There are a couple of properties that govern R1C1 in FlexCel.:
A)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.
B) 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 A)
So for our original example, here is the code to do it with R1C1 notation. Note that due to the fact that R1C1 is relative, the formula is always exactly the same. There is no need to calculate a formula for each cell as we did in Solution 2):
xls.FormulaReferenceStyle := TReferenceStyle.R1C1;
for col := 1 to 26 do
begin
xls.SetCellValue(1, col, TFormula.Create('=R[1]C * 2'));
end;
Also note that while we used R1C1 internally to enter the formulas, in Excel they will show in A1 exactly the same as they do with the other 2 solutions. FlexCel comes with full R1C1 support built in.
Bonus track
R1C1 formulas are not only nice to enter formulas, but also to check for consistency in existing files. Imagine you have a file with formulas like in our example above, and you want to check that they are all as they are supposed to be. So for example in Column J, you have =J2 * 2 and not =A2 * 2. Checking this in A1 notation can be very complex, specially if the formulas are not simple. But retrieve the formulas in R1C1 instead, and all you need to do to check for consistence is that all formulas in A1:Z1 are the same!.
That is, retrieve the formula in A1 (in this case "=R[1]C * 2") and then check that all other formulas in the range are the same as the text in A1. If a formula is different, then it is not consistent.
|
