FlexCel Reports - error with empty dataset

Hi Adrian

I have a FlexCel template which includes an Excel Table, which I've called __NewVehs__

There are 9 columns of data in the table, the contents of each cell are  <#NewVehs.Field1>, <#NewVehs.Field2> etc
The second row of the table is blank (so that FlexCel can expand the table!)

In my Delphi App, I link the FlexCel table NewVehs to a simple query.

All is fine if the query returns some data, the excel file output from FlexCel contains the correct data from the query.

However, if the query returns no records (which can happen sometimes), when I try to open the output file, I get the following error from Excel:

Excel found unreadable content in "NewVehicles.xlsx" Do you want to recover the contents of this workbook?

If I say YES, the file is opened, with the following message in the 'repairs' dialog:

Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table)
Removed Feature: Table from /xl/tables/table1.xml part (Table)

Does FlexCel not cope with empty datasets? or am I doing something wrong??

Kind Regards,
Steve Collins


FlexCel copes with empty datasets, but the problem here is likely that Excel doesn't cope with empty tables. You can try it: Create a table with 2 rows in Excel and then try to remove a row. You'll see the rows is not deleted, as Excel doesn't allow you to have only 1 row in a table.

I remembered that FlexCel should raise an Exception if you try to save a table with 0 rows, so you should get an exception instead of an invalid file, but looking through the changelog, I see this was added in april, and well, last FlexCel release was in march (time flies...).So this exception is not in 6.26, but will be in 7.0, which will be released this week.

In any case, while having an exception is an improvement over having an empty file, my guess is that you would prefer to have no errors. As we can't save a table with 0 rows, there are 2 options:

1. You can check if the dataset has 0 rows before running the report, and if that is the case, warn the user that there is no data and not run the report. An empty report isn't that useful anyway.

2. You can set 

report.DeleteEmptyBands := TDeleteEmptyBands.ClearDataOnly

(see http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.Report/TFlexCelReport/DeleteEmptyBands.html )

In this case, you will get a table with an empty row if there are no records, and that is as good as you can get, because as said Excel won't allow you to create a table with 0 rows.

I am thinking if there could be other options to add in the future to make this more intuitive, but I can' think of much. The "no zero rows in tables" limitation in Excel is the culprit here, and I am not sure we can make something that makes that limitation look intuitive.
The table is just one of many on a workbook with multiple sheets. The sheets contain various tables, for example, New Vehicles, Modified Vehicles, Deleted Vehicles, so at various times when producing the final Excel file, we could have some New Vehicles, but no Deleted Vehicles; or no New Vehicles but some Deleted Vehicles etc, so in this case we do need to know when the tables are empty.

However, as suggested I've just done
and the final excel file now loads ok, and I'm quite happy having a table with an empty row!

Thanks for the prompt reply.