Bug 20321 - "NO IDEA SHARED FORMULA EXP PTG" appears when read copy/paste formula
Summary: "NO IDEA SHARED FORMULA EXP PTG" appears when read copy/paste formula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-dev
Hardware: Other All
: P3 critical with 2 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 36941 (view as bug list)
Depends on:
Blocks:
 
Reported: 2003-05-29 02:36 UTC by zhaoml
Modified: 2010-03-09 14:36 UTC (History)
2 users (show)



Attachments
Starting Excel File (28.50 KB, application/octet-stream)
2003-06-04 15:10 UTC, Danny Mui
Details
Resulting Excel File (29.50 KB, application/octet-stream)
2003-06-04 15:10 UTC, Danny Mui
Details
Code (1.14 KB, text/plain)
2003-06-04 15:11 UTC, Danny Mui
Details
getCellFormula() returns NO IDEA SHARED FORMULA EXP PTG (142.00 KB, application/vnd.ms-excel)
2005-06-07 20:36 UTC, vanishree narasimhan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description zhaoml 2003-05-29 02:36:01 UTC
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.
Comment 1 Danny Mui 2003-05-29 16:45:14 UTC
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.
Comment 2 Danny Mui 2003-06-04 15:09:32 UTC
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.
Comment 3 Danny Mui 2003-06-04 15:10:27 UTC
Created attachment 6630 [details]
Starting Excel File
Comment 4 Danny Mui 2003-06-04 15:10:45 UTC
Created attachment 6631 [details]
Resulting Excel File
Comment 5 Danny Mui 2003-06-04 15:11:01 UTC
Created attachment 6632 [details]
Code
Comment 6 Jun Zuo 2004-01-16 15:10:40 UTC
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).
Comment 7 Avik Sengupta 2005-04-22 16:17:05 UTC
What's the status of this?
Comment 8 vanishree narasimhan 2005-06-07 20:36:44 UTC
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.
Comment 9 Aditya Bhardwaj 2005-09-01 09:51:25 UTC
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.

Comment 10 Trejkaz (pen name) 2006-07-12 02:48:25 UTC
*** Bug 36941 has been marked as a duplicate of this bug. ***
Comment 11 Jason Height 2006-08-27 01:01:59 UTC
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
Comment 12 Fabrizio 2010-03-09 14:36:06 UTC
Is this bug fixed?
Anyone could help me?