Issue with FlexCel reports and AdvGridFilters

[Steve_Collins]

Hi,

I've just started to experiment with FlexCel reports (until now I've only used FlexCel for native .xlsx import and export)

My first 'test' was a simple report in a standalone app using TList<> as the data source, and the report was generated perfectly.

I then incorporated the exact same code into my main application, but running it in here I got an error:

Error in cell Sheet1!A4: "Column "repVehCode" does not exist on table "Vehicles""

After a lot of experimenting, changing compiler setting, removing units from my code, I think I've tracked the problem down to the fact that my main application uses AdvGridFilters. I seems that including either UAdvGridExcelImport or UAdvGridExcelExport anywhere in my code causes the problem.

I've further checked this by modifying your demo program "ReportsFromLists" by adding UAdvGridExcelImport to the uses clause. When I run the demo with this extra unit in the unit clause, I get the following error:

The
 names range "__Elements__" on the Excel template refers to DataTable 
"elements" which is not defined. Verify that you added the dataset with 
FlexCelReport.AddTable method.

If I then remove that unit from the uses clause, the report runs ok again!!

I
originally had an older version of FlexcelGridFilters (2.12), so have
downloaded the latest (2.16) but still have the same issue.

I'm using Delphi XE5, FlexCel 6.22 and TMSComponentPack 8.9.5.0

Is
there some incompatibility between FlexCelReports and AdvGridFilters,
or have I somehow got some configuration settings incorrect??

[adrian]

Wow, this is a weird one. 

Sadly as you might imagine, I can't reproduce it here (I tried adding UAdvGridExcelImport to the uses clause in the main form, in the project, also dropping a GridFlexCelImport and Export component and it always worked as expected.

Now, while of course adding a ExcelExport should have no incompatibilities with FlexCelReport, and this is a bug in Delphi somewhere that is causing this, I have an idea of what might be happening and how it could be solved.

My first question would be: Did you install FlexCel and the Filters manually or via the Setup? (I am interested specially in the filters) If you installed the filters manually, did you put the path to the .pas units in the library path?

What I think might be happening is this: In order to reduce the binary size, FlexCel is compiled with RTTI off, since it itself doesn't use it. So every unit on it(and in the filters too) includes a FlexCel.inc file which has the code:

{ Reduce EXE size by disabling as much of RTTI as possible (delphi 2009/2010) }
{.$WEAKLINKRTTI ON} //this affects the whole project, not just the unit, at link time.
{$RTTI EXPLICIT METHODS([]) PROPERTIES([]) FIELDS([])}

Note how WEAKLINKRTTI ON is commented out, because if you set it, it will turn on WEAKLINKRTTI for all your project, not just for FlexCel. The $RTTI clause is supposed to only affect the unit it is in, but if you recompile the unit with your app, after it has been recompiled, it will change the default and units compiled after that will not have RTTI.

This is why I wanted to be sure that you are not recompiling the AdvGridExcelIO.pas file (make sure it is not in the library path). If it is, what is likely happening here is that when Delphi compiles that file, it sets the RTTI to off for all units it will compile after that.

And that means that the unit where the class "Vehicles" is stored will be compiled without RTTI, and FlexCel won't be able to use reflection to find its members. And so, it will complain that any member on it doesn't exist.

But before continuing, I would like to confirm if this can be the cause. A simple way to do it would be to explicitly turn RTTI on in the unit where Vehicles is defined. Just write:

{$RTTI EXPLICIT METHODS([vcPublic]) PROPERTIES([vcPublic])}

after the "unit" line. Because Delphi is kind of buggy with all of this, the order here is important: put it exactly after the unit and before the interface. It might wrk somewhere else too, but to make sure, put it there. I remember that in some places (I believe before the "unit" line) it doesn't work.

That is, your code should be something like this:

unit Ureportingclasses;
{$RTTI EXPLICIT METHODS([vcPublic]) PROPERTIES([vcPublic])}


interface

Does it get fixed after you add this line? If it does, then somehow your AdvGridExcelExport/Import.pas units are in your library path and are being recompiled with your app.

[Steve_Collins]

Hi Adrian,

Thank you for the prompt reply. I had a feeling it was something to do with 'missing' RTTI, as I'd read in the documentation that you use RTTI to get data from objects.

In reply to your first question, FlexCel was installed via setup, but the Filters were installed manually. When I tried to install the Filters earlier, I got a message saying my AdvStringGrid was too early a version (or some similar message), so I clicked ignore, the install failed, and then I added them manualy [I'm not sure why the install said I have incorrect version of AdvStringGrid, as I've got the latest TMS Pack - 8.9.5.0]

Additionally, I've just checked and my library path DOES include the folder where the Filters are installed.

So, I'm assuming RTTI is being turned off during the build.

So, I added the {$RTTI...} line as you suggested, but I still get the issue!

I also tried adding it to the UMainForm unit of your ReportsFromLists demo, which was where I'd added UAdvGridExcelImport, and got the same error in there!

I think maybe Delphi is a bit more buggy with this line than you think!!

Finally, I thought I'd try to stop the turning off of RTTI altogether by editing the FlexCel.inc file, and commenting out the 

{$RTTI EXPLICIT METHODS([]) PROPERTIES([]) FIELDS([])}

line. When I did this, the reports start working again!!

So, for now I have a fix by editing the FlexCel.inc. When I do this my EXE increases in size from about 50MB to 54MB, which I can live with!

I'd like to try to get the filters installed by the setup - any idea why the setup isn't correctly detecting my AdvStringGrid installation? Alternatively, what are the steps for manual installation, without having the Filters path in my library path?

It would also be nice to get the $RTTI line in my units working, but if that's a Delphi bug, it's probably not something you can help me with!!

[Steve_Collins]

Further update:

The RTTI line that you asked me to insert needs to be

{$RTTI EXPLICIT METHODS([vcPublic]) PROPERTIES([vcPublic]) FIELDS([vcPublic])}

ie has the FIELDS section as well! The test class I'd written to hold the data had public fields rather than properties holding the data.

Also, the reason it didn't work in the ReportsFromList demo is that I should have put the line in the DataModel unit, not uMainForm, although again without the FIELDS section, it still errors, unable to get ElementId from TElementName.

[adrian]

Well, at least we have a better idea of what is happening. 

The line should work: I use it myself a lot here in testing, because I am continuously recompiling FlexCel and tests all together doing development, so I needed to add that line to the units testing reports. But you need to add it to the DataModel.pas unit, that is where the classes are defined. (It shouldn't hurt to add it to both MainDemo and DataModel though). Well, I just got a mail that you got that sorted out, that's great. Indeed it might be missing the fields parts because I just copied it from a test unit that didn't had it, because it didn't use fields as variables (only properties). Sorry about that.

Note that when you edit the line in FlexCel.inc the already compiled FlexCel dcus still don't have RTTI, so maybe the size increase isn't that big.

Now, about the error installing the filters, could it be that you have an older version of the grid higher in the search path and the setup is finding that? Maybe you installed first advStringGrid, then the full component pack, and both are installed? 

 Trying to reproduce your problem earlier today I fired a clean VM with DXE5, installed FlexCel, the grid and the filters, and it went all fine. 

Probably the best is to check at the registry. The setup looks for the following entries in this order:

HKEY_CURRENT_USER\Software\tmssoftware\TMS TAdvStringGrid

HKEY_CURRENT_USER\Software\tmssoftware\TMS Component Pack

HKEY_CURRENT_USER\Software\tmssoftware\TMS Grid Pack

For the first entry it checks version > 8.0,  if it doesn't find the first entry then it will look at the scond for version > 8.2 and if it doesn't find that entry either it will check the third against version > 6.5

What values do you have in those keys in regedit? Note that if you have an entry for TMS AdvStringGrid, it will check that, and not the component pack. Note that if you have TMS AdvStringGrid installed, uninstall it instead of just removing the registry entry. After uninstalling, probably you can reinstall the component pack to be safe.

But anyway, if you ignore it should install. Can you post or send me the install log for the filters (<Filters install folder>\Setup\AdvGridFilters_build_log.txt 

Finally, to compile manually and make sure you don't have this problem, you need to put the path to the dcus in the library path, not to the .pas files. Check http://www.tmssoftware.biz/flexcel/doc/vcl/guides/installation-guide.html#manual-installation for more information: It is about installing FlexCel, but the filters should be installed the same way.

[Steve_Collins]

Hi Adrian,

I've just checked the registry for the TMS entries and I know what's happened. Several weeks ago I was editing the registry on this dev machine to update some values for our software, and by mistake I deleted the TMS node!! I checked one of my other VMs for the settings to re-enter it, but it looks like I mis-typed 8.9.5.0 as 8.0.5.0!!

So, I've corrected that value, removed all references to the Grid Filters, including any library path entries in my Delphi settings, and reinstalled from the setup and all went ok.

Thanks again for all your help.