I have an Excel worksheet with a table. If I use FlexCel's TXlsFile.DeleteRange function to delete worksheet rows that are also rows of the table, the resulting xlsx file becomes corrupted. I can send you a Delphi project with a sample xlsx file.
Hi,
Thanks for reporting this. Tables can be a little tricky in what you are allowed to do or not, and sometimes FlexCel might let you do stuff that Excel won't, and Excel doesn't like this.
I've been trying to reproduce it here with a table in an Excel file from A1 to B3:
xls.DeleteRange(txlscellrange.Create(2, 1, 2, 5), TFlxinsertMode.ShiftRowDown);
Works ok. (deletes row 2)
As does:
xls.DeleteRange(txlscellrange.Create(3, 1, 3, 5), TFlxinsertMode.ShiftRowDown);
(deletes row 3)
and
xls.DeleteRange(txlscellrange.Create(1, 1, 3, 5), TFlxinsertMode.ShiftRowDown);
(deletes the full table)
If you try:
xls.DeleteRange(txlscellrange.Create(1, 1, 2, 5), TFlxinsertMode.ShiftRowDown);
There is an Exception, and that is ok since Excel won't let you either delete a row which contains the headers of a table.
Finally, I think I was able to reproduce your issue with:
xls.DeleteRange(txlscellrange.Create(2, 3, 2, 5), TFlxinsertMode.ShiftRowDown);
That is, deleting all the rows inside the table. In this case Excel will complain that the file is invalid when you open it, and if you recover it, it will remove the table.
So my first question would be to confirm this is indeed your problem. Are you trying to delete all rows but not the header of a table? If you think that it might be a different problem, please send me a project/file to adrian@tmssoftware.com so I can check it out.
About this particular problem, this should indeed be fixed, but I am not sure how yet; I'll have to think a little more about it.
The thing is, normally what we should do in this case is throw an exception telling you you can't have a table with 0 rows. That's what makes more sense, and how Excel behaves in most cases where you want to do something that is not allowed.
But tables in Excel behave a little weird: If you try to delete all rows in a table from Excel, Excel won't show an error, but just not delete all rows. It will keep the first row and clear it, even when you were supposed to delete it.
So I'll have to review it more. I think it makes more sense to just tell you there is a problem via an Exception, but someone might like the "excel way" of just ignoring what you said and not deleting all the rows so the table doesn't become invalid.
In any case, if this is the issue you are having and not a different one, just be aware that you just can't delete all rows in a table, so check the code to not try to do it. As said above our fix is likely to be an Exception anyway in this case, so it will help if you don't try to do it.
Yes, this is my problem. When I open the corrupted file in Excel, it tells me that it has removed the affected table to repair the file. When I do the same operation with OLE automation, OLE will delete the rows but ensures that there is at least one table row left. As you proposed, I will adapt the program and ensure that there always remains at least one row.
Just a follow up on my part. After some thinking, I believe that failing to delete a row when the user told you to delete the row is just wrong, and can lead to a lot of unexpected issues.
Imagine for example that there is sensitive data on that row (not necessarily in the table, but maybe outside the table but in the same row). If you are in Excel you can easily see that you deleted the row and it didn't delete it (in fact, it is even more weird: It clears the data on the table but keeps the data outside the table). In FlexCel, you are not seeing in real time what the app is doing, and you are likely not going to realize that FlexCel didn't delete the row even when you said you wanted it deleted.
So I am going for the exception approach: We've already modified the code here so it throws an exception if you try to save a file with tables with 0 data rows. I believe this is the most logical default. If needed, we could add an option so FlexCel deletes the tables with 0 data rows before saving, but I am not sure on how useful that would be.