Hello, I've been having some troubles with some "corrupted" .xls files serialized with POI (i use POI 3.2-FINAL, i also tested 3.5-beta3, results are the same).
What i do basically is that i load a xls file, use it as a template, clone template sheets fill/modify, delete the template sheets to get the workbook clean and then serialize it to some result file. However sometimes when i open the resulting file with Excel 2007 it tells me "Excel found unreadable content in ...", i can recover the file just fine but something's definitely wrong. I dived a bit in POI 3.2-FINAL source code and in the output of BiffViewer to try to understand what was going on. I was able to find out that in fact the problem appears with a NAME Record, that is a builtin one, the _FilterDatabase one in fact. This record is copied when cloning a sheet. I found that what Excel compains about is in fact the formula inside this Name record, and more precisely about the "extern sheet index" of the Area3DPtg inside the formula. this is because the extern sheet it refers to does not exist, it point to extern sheet index 2, here is the Record: [EXTERNSHEET] numOfRefs = 3 refrec #0: extBook=0 firstSheet=0 lastSheet=0 refrec #1: extBook=0 firstSheet=1 lastSheet=1 refrec #2: extBook=0 firstSheet=2 lastSheet=2 [/EXTERNSHEET] so #2 is extBook=0 firstSheet=2 lastSheet=2. This is correct after cloning : there are originally only 2 sheets in the template so cloning one creates a third sheet (0-based id 2) and thus the Area3DPtg must point to the extern sheet record pointing to this third sheet. but after that i delete the second sheet (0-based index 1) thus, the cloned sheet becomes sheet number 1 (0-based) and the ExternSheet record should be updated : a quick solution could be to transform it to: [EXTERNSHEET] numOfRefs = 3 refrec #0: extBook=0 firstSheet=0 lastSheet=0 refrec #1: extBook=0 firstSheet=-1 lastSheet=-1 refrec #2: extBook=0 firstSheet=1 lastSheet=1 [/EXTERNSHEET] but i'm not quite sure it's a good idea to leave that ref record (#1) here... i believe it wouldn't harm Excel, but this would not be very clean, tho, in order to clean things up, we'd have to change all references to #2, i guess this should be done in the LinkTable. But i am by absolutely no mean a Excel or POI expert. Any ideas? Regards, Gilles --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
