Hello Gilles, I think you are on the right track. I suspected that Excel might do exactly as you suggest. I.E. set firstSheet, lastSheet to -1 for the deleted sheet, decrement all subsequent firstSheet,lastSheet indexes. (all adjustment performed entirely within the externsheet record).
It's theoretically possible to get rid of the '-1' entry in the externsheet record, but this would be quite complex. It would involve adjusting every externSheetIndex (all 3D refs in *every* formula). Can you write up some simple POI test code that reproduces this problem as you describe? Is it important that an autofilter be present? I suspect the problem will occur for any 3D references pointing to subsequent remaining sheets after a deletion. -Josh On Fri, Nov 21, 2008 at 1:28 PM, Gilles Duboscq <[EMAIL PROTECTED]> wrote: > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
