HI,
I am trying to create a "dynamic" report where the template has cells containing e.g. <#preprocess><#field1>. And generally, I will use a SetValue statement to replace field1 with e.g. <#range.fieldname>. That works fine.
However, there are some fields which contain a code and I wish to show the corresponding literal/value rather than the code. To simplify, I've included the relevant code value in a hidden column so that the code is always present. I have a second sheet containing the code/value "table". Then when the code is to be reported, I use a VLOOKUP to lookup the hidden cell (that has the code for that record) and return the literal/value for it.
I use a (Delphi) statement like this to accomplish that:
FlexCelReport.SetValue('field2', '<#evaluate(=VLOOKUP(G6,''Unit of Measure''!$B:$C,2,FALSE))>');
Now that works OK, if the code/value table is static - i.e. if it exists already populated on the template. However, if I make that code/value "table" dynamic (where I populate it via a AddTable<Tlisttype> statement - which I have to), then it no longer works. I have re-ordered the sheets to try to ensure that the code/value table is populated first etc. I've also tried a number of variations of SetExpression. But no joy.
Any guidance would be appreciated. Thanks.
Tom
Sorry, I thought I had re-ordered the sheets to ensure the code/value table was first, but I must not have saved it. With it first, and populated first from Delphi, it now works. But maybe there is a better way?
Hi,
As a general rule, you shouldn't trust any "fill order" when running a report. It can change, and it actually has changed in FlexCel history. We used to fill the fields from the bottom to the top originally in FlexCel.NET, because we used DataSets which are in-memory tables so order was not important, and filling from the bottom has many advantages. We changed it when we supported reports from IEnumerable collections, because those only have a .Next, but not a .Previous. Order of filling might change again in the future if for example we do some parallel working, and maybe both sheets are being filled at the same time, so there is no sheet that is filled first.
This all means that you should avoid referencing cells which have to be filled by FlexCel inside expressions. Never trust the cell was already filled.
This normally isn't a problem, because if you are filling the cells from FlexCel, then you have the data to calculate the expression, so you don't need to reference the cell. You can define some <#expression> with the cell value and use it in the cell and in the other expression that would reference the cell.
In your case, I am not really sure on the details of your particular situation but I see 2 alternatives:
1)If you want to have a real vlookup formula in the sheet, use this:
fr.SetValue('field2', '<#formula>=VLOOKUP(G6,''Unit of Measure''!$B:$C,2,FALSE)');
This will enter the formula with the VLOOKUP, and it will be calculated after all data is filled.
2)If you want to enter the result of the formula, then you can do the lookup in your code before setting Field2, or you can use a <#lookup> tag that searches in the TList with the codes and descriptions.
Here is some simple code that shows both approaches:
type
TProduct = class
private
FCode: string;
FDesc: string;
public
constructor Create(const aCode, aDesc: string);
property Code: string read FCode;
property Desc: string read FDesc;
end;
procedure TForm10.Button1Click(Sender: TObject);
var
fr: TFlexCelReport;
list: TObjectList<TProduct>;
begin
fr := TFlexCelReport.Create(true);
try
list := TObjectList<TProduct>.Create();
try
list.Add(TProduct.Create('C1', 'First Product'));
list.Add(TProduct.Create('C2', 'Second Product'));
list.Add(TProduct.Create('C3', 'Third Product'));
fr.SetValue('field2', '<#formula>=VLOOKUP(G6,''Unit of Measure''!$B:$C,2,FALSE)');
fr.SetValue('g6', 'C2');
fr.SetValue('field3', '<#LOOKUP(list;code;<#g6>;Desc)>');
fr.AddTable<TProduct>('list', list);
fr.run('..\..\book1.xlsx', '..\..\result.xlsx');
finally
list.Free;
end;
finally
fr.free;
end;
end;
{ TProduct }
constructor TProduct.Create(const aCode, aDesc: string);
begin
FCode := aCode;
FDesc := aDesc;
end;
Hope it helps, let me know if it does.
Thanks again, Adrian.
I got both the #formula and the #lookup working after a few false starts. I found that with the formula, as the row moved down the page, I had to allow for this. Tried the <#ref> firstly with an offset - which worked but was obviously susceptible to changes. Then tried it by defining a named range on the cell containing the code and having the #ref reference it NOT absolutely. This worked too.
But the winner was the <#LOOKUP> - this didn't require any cell references as such and was obviously therefore preferable.
Thanks.