I have an existing XLS file from a customer with VLOOKUP-Formulas referencing a separate sheet. I can read the cached values using value = cell.getStringCellValue(); where cell contains a formula =VLOOKUP(.... - for example: =VLOOKUP($D3;$Entity.$A$1:$B$1048576;2;FALSE()) (I am not going to re-evaluate the value because references can be also to a separate file I didn't get from the customer.) So far so good - looks fine. Now the thing is, that I am on Linux (Ubuntu 9.10) and there are a few invalid (invalid in they way that they contain data I don't want to process) lines and I just deleted them using Open Office Calc v3.1.1 build 9420. After I saved the file with Open Office all columns containing a VLOOKUP-formula throw an IllegalStateException when I try to call value = cell.getStringCellValue(); I tested the same saving the OO XLS file as XLSX - and there it works. So this is a workaround.
Can you please upload the offending file (and identify one cell will the problem). The full stack trace of your IllegalStateException would also be useful.
Sorry, for the delay, but 1.: I was under stress finishing at deadline. 2.: I tried to produce the simplest possible test file to produce the error without success. - Unfortunately I cannot post the original file as it is confidential. Further: In the meantime I also got a new updated file from the customer which does not produce the error any more. The customer also changed structure (not only content - nice from the customer when already struggling to get the project ;-) ). I tried to find out differences that might have an effect but I was not successful. I only can imagine two possible reasons: a) On my tests I edited the original file (to make a test case out of it) with Excel 2003 incl. latest patches. Customer might have different patch level or anyone in the team might have edited the file with Excel 2007 and that caused some corruption not kept properly ( ;-) ) when saving with OO. b) The earlier version simply was corrupt or had some other bad link in it that created the problem when saving with OO. However, I made you loose enough time reading this and myself lost a very lot of time also initially when facing the problem and later when trying to generate a test case - just because of yet another issue touching a shitty M$ format. I would say: Close the issue and forget it.
Resolving as suggested long ago.