I get the above exception when opening a customer XLSX file in our application (and in ApiMate). The file can be opened in Excel and contains no cell which has this value; most of them contain Japanese text. LibreOffice Calc opens the file without protest, but all the cells are empty.
So if it is not a cell value, is "11" a code which signifies anything? Saving the file from Excel allows the copy to be opened successfully in FlexCel (and LibreOffice) so I suspect it is really invalid, but it would be nice to have a better explanation for the user about what is wrong with it. I am trying to get permission to send you the file.
If saving from Excel fixes the problem, then there is likely a problem with the file indeed. The code 11 doesn't mean anything, it likely is a cell reference that says 11 instead of for example A11.
Say you have the xml: <c r="A11" s="3"/> that means cell A2 has the 3rd string in the shared table. If the file instead had the xml <c r="11" s="3"/> then FlexCel would complain. Excel would likely just ignore that cell. To have FlexCel also ignore the invalid cell you could turn RecoveryMode = true, but the best solution in this case is of course to fix the file, or you might be getting corrupt data.
We try really hard to give good error messages for every error, but we can't anticipate every possible error out there, and in this case, it would be difficult to get a better error description, and whatever we said, I doubt the final user will understand it. What the user needs to know is just that this file is invalid.
If you can get permission to send me the file (or some similar one which has the same error), I can give it a deeper look and let you know if it is something we can workaround without risking deleting data, or if it is just an invalid file, even if Excel can open it because it doesn't do the correct validation steps.
Thanks for the file. Indeed it was a problem with the file: as expected it had almost the exact xml I quoted in my last post :)
<x:c r="11" t="s">
The attribute "r" in the cell should have an A1 cell, like "A11" or similar, but in this case it has a number (11) which is not a valid cell reference. This file wasn't created by Excel, but with some third party tool which didn't understand what it had to write in that place. I am not sure what they tried to write, but it seems that instead of "letter of the column, then row" they are writing "col number, then row number", since the next cell is "21", then "31", etc. For the next row, the values are 12, 22, 32...
Of course with that convention, we can't really know if for example "345" means col "3, row 45" or "col 34, row 5" but it doesn't matter, since luckily for the ones who wrote that file (and unluckily for us or libreoffice), Excel just ignores those cell references and assumes it is the next cell. So if we were reading column 15 and the next cellref is invalid, we assume the next cell is column 16.
I've modified FlexCel so it works the same way: Now when it detects an invalid reference, instead of telling you with an exception, it will assume the reference is the next cell. As an added bonus, now if you open and save this file with FlexCel, it will fix it the same way Excel fixes it when you save it.
I am not really happy with the solution, but that is how Excel behaves, and we try to read anything Excel can read as long as we aren't doing something obviously wrong. We are not religious here, and understand that users don't normally care if the file is wrong or not, they just want to read it.
The next FlexCel version will have this fixed, but you can be sure this will be a rare thing (unless your users used the same tool to create lots of similar xlsx files). I've never seen a file with those errors before, and Excel has never saved files this way.
Great news that the wait is over and version 7 looks to have some useful new features.
Not
so great that this problem seems not to have been fixed as promised.
Loading the same file that I sent to you still causes the same
exception. Is there anything I could tweak in the source to avoid this?
Luckily
we still have an option to use Excel automation instead of our built-in
Flexcel code, but our customer receives incoming XLSX files (over which
they have no control) on more than one machine and it is complicated
for them to direct work from one specific client to the one machine on
which they have to install Excel.
Many apologies. Yes, the install is correct, and I had double-checked that the component and its DLL which calls Flexcel had been built today with the new version. But unfortunately I had not checked that it was this DLL that had been loaded. Thanks. as usual, for the prompt response.