David: I had typed in a bunch of suggestions, but they're gone now because I created a simple sheet that reproduced your error using the usermodel approach. I only got to BO (67th cell) before the same exception that you noted was raised.
Here's my remaining suggestions & comments: 1) As a workaround, is there reason not to make sheets no more than 50 columns or so? 2) Any difference if you try eventmodel, or usermodel (whichever one you're not using) 3) If this is really a limitation of the current POI, I'm surprised the secret's kept this long. Sorry I'm not much help here. Cheers, Widmar Ps - I'm using [3.0.1 final] -----Original Message----- From: Karr, David [mailto:[EMAIL PROTECTED] Sent: Monday, August 06, 2007 6:07 PM To: POI Users List Subject: Still struggling with "Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula" A while ago I started to see cases where I would get the following exception: Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula I saw this when I tried to read cells from a "wide" spreadsheet. I don't see it when I only read from low-numbered columns, but I found a threshold where it's ok on one column and fails on the next column (for instance, if I only read up to column BR, it's fine, but it fails on BS (ironic)). The error happens in this block in "org.apache.poi.hssf.model.FormulaParser.toFormulaString(Workbook book, Ptg[] ptgs)": // Excel allows to have AttrPtg at position 0 (such as Blanks) which // do not have any operands. Skip them. stack.push(ptgs[0].toFormulaString(book)); When this doesn't fail, when I look at the ptgs list, I see a bunch of Ptg subclass instances, like RefVPtg (the 0th one), FuncPtg, AttrPtg, and others. When it fails, the only entry in the list is ExpPtg. When this is here, it's not surprising that I get this exception, as this is what it does: public String toFormulaString(Workbook book) { throw new RecordFormatException("Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula"); } I did file a bug report for this quite a while ago, and I wrote again about it in this list 3 months ago, but I've never heard anything from the bug report or the note I wrote 3 months ago. I would update the bug report with a little more info that I've determined from looking at the source (only what you see here), but I'm currently getting a 504 gateway error trying to get to the bug db. I tried searching through the source tree for references to the ExpPtg class, since the error message makes me think something might be trying to convert ExpPtg instances to something else. The only thing I got from that search is the following: * In org.apache.poi.hssf.dev.FormulaViewer.listFormula(FormulaRecord record): if (token instanceof ExpPtg) return; * In org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator, in the static block: OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class); //OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO: check // this OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); // TODO: In the past, when we've seen this, we've been able to segment the spreadsheet into multiple worksheets, and process each one indidividually. This indicates that there's some issue with how Excel stores its formula data for "wide" spreadsheets. After a certain point, it must store them differently. --------------------------------------------------------------------- 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]
