Environment: W2k professional + Excel 2000 + POI 2.0pre1 question 1: I copy a formula and paste it to a serial cells in a excel file in MS Excel. Then I read the pasted formulas to a String, the String is "NO IDEA SHARED FORMULA EXP PTG". question 2: After I modify one of the pasted formula to a number, and save the workbook to another excel file, I open the file in MS Excel. All of the cells which reference the modified cell appeared #REF!. I'm not sure if it is the same question as bug 15223, because after I get the latest code from CVS, things not changed.
if you can attach your spreadsheet we can take a further look at it. Shared Formulas are not rendered at this time, that's why you get the "SHARED FORMULA EXP PTG". If you want to avoid shared formulas, type the formulas without pasting them. Shared formulas were tested by dragging the cell contents across.
are you just trying to set the excel calculated value for the formula in your code ? or is there another reason you're setting the value. I haven't had a chance to completely investigate yet but it looks like when you do that, you're wiping out the SharedFormulaRecord so the others that reference it doesn't work correctly.
Created attachment 6630 [details] Starting Excel File
Created attachment 6631 [details] Resulting Excel File
Created attachment 6632 [details] Code
This problem arises when you try to read a cell that has a formula from auto- fill (selecting the cell with a formula and dragging across cells).
What's the status of this?
Created attachment 15330 [details] getCellFormula() returns NO IDEA SHARED FORMULA EXP PTG The last column is a formula column in my attached excel sheet ,it is not being read by POI. System.out.println("srcCell.getCellFormula() ="+srcCell.getCellFormula()); returns "NO IDEA SHARED FORMULA EXP PTG" It fails in HSSFFormulaEvaluator at the following code block FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), workbook.getWorkbook()); parser.parse(); //as parser expected an excel formula rather than a String Can anyone help me read the formula cell's value. Thanks regards Vani.
How soon can we expect the resolution to "Shared Formulas are not rendered at this time, that's why you get the "SHARED FORMULA EXP PTG"." or its already released. - Is there a quick fix to excel without having to go and type the formulae again (manually) (In reply to comment #8) > Created an attachment (id=15330) [edit] > getCellFormula() returns NO IDEA SHARED FORMULA EXP PTG > > The last column is a formula column in my attached excel sheet ,it is not > being read by POI. > System.out.println("srcCell.getCellFormula() ="+srcCell.getCellFormula()); > returns "NO IDEA SHARED FORMULA EXP PTG" > > It fails in HSSFFormulaEvaluator at the following code block > > FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), > workbook.getWorkbook()); > parser.parse(); //as parser expected an excel formula rather than a > String > > Can anyone help me read the formula cell's value. > > Thanks > regards > Vani. (In reply to comment #8) > Created an attachment (id=15330) [edit] > getCellFormula() returns NO IDEA SHARED FORMULA EXP PTG > > The last column is a formula column in my attached excel sheet ,it is not > being read by POI. > System.out.println("srcCell.getCellFormula() ="+srcCell.getCellFormula()); > returns "NO IDEA SHARED FORMULA EXP PTG" > > It fails in HSSFFormulaEvaluator at the following code block > > FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), > workbook.getWorkbook()); > parser.parse(); //as parser expected an excel formula rather than a > String > > Can anyone help me read the formula cell's value. > > Thanks > regards > Vani. (In reply to comment #8) > Created an attachment (id=15330) [edit] > getCellFormula() returns NO IDEA SHARED FORMULA EXP PTG > > The last column is a formula column in my attached excel sheet ,it is not > being read by POI. > System.out.println("srcCell.getCellFormula() ="+srcCell.getCellFormula()); > returns "NO IDEA SHARED FORMULA EXP PTG" > > It fails in HSSFFormulaEvaluator at the following code block > > FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), > workbook.getWorkbook()); > parser.parse(); //as parser expected an excel formula rather than a > String > > Can anyone help me read the formula cell's value. > > Thanks > regards > Vani.
*** Bug 36941 has been marked as a duplicate of this bug. ***
Svn now converts Shared formulas to their non-shared equivalent, so this problem will not occur. However, instead of returning NO IDEA SHARED FORMULA, the getFormulaString calls for shared formauls Ptgs will now throw an exception (rather than confusing the issue with a string that will cause an exception to be raised anyhow) Jason
Is this bug fixed? Anyone could help me?