Trying to insert a formula into a sheet,
Used APIMate to generate code, and the code look OK
xls.SetCellValue(23, 3, TFormula.Create('=SUM(C7:C22)'));
But when i open excel the cell 23,3 contains =SUM($G:$G:$V:$V)
I'm using version 6.0.6
Hi,
I can it be that you are inserting rows or columns after setting the cell formula? Or maybe setting it again in the code?
I've tried this code:
program Project14;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter;
var
xls: TExcelFile;
begin
xls := TXlsFile.Create(1, true);
xls.SetCellValue(23, 3, TFormula.Create('=SUM(C7:C22)'));
xls.Save('r:\test.xls');
xls.Save('r:\test.xlsx');
end.
And the generated files (both xls and xlsx) have the correct formula. This is basic functionality and should work fine, that's why I wonder is maybe the formula isn't being modified after you set the cell. (either by setting it again or by inserting / deleting rows or columns).
Can you try the code I posted above and see what it generates?
Your code worked so i moved code to your sample until i got problems.
This line made the problem
xls.FormulaReferenceStyle := TReferenceStyle.R1C1;
Hi,
If you use FormulaReferenceStyle = R1C1, you need to set the formulas in R1C1 mode:
ApiMate will also show you the correct R1C1 syntax: In the left pane, there is a checkbox: "Use R1C1 in Formulas. If you check it, you'll get the code:
xls.SetCellValue(23, 3, TFormula.Create('=SUM(R[-16]C:R[-1]C)'));
But well, if you don't care about R1C1, just don't set the FormulaReferenceStyle to it, and keep using A1 references.
Thank you, it was ApiMate
that put in
xls.FormulaReferenceStyle := TReferenceStyle.R1C1;
So i just remove ti.
I think you might have pressed the checkbox "Use R1C1" by mistake in the left pane of APIMate. If you press that button, APIMate will add the line xls.FormulaReferenceStyle := TReferenceStyle.R1C1 but it will also show the formulas in R1C1 notation.