Re: NO IDEA SHARED FORMULA EXP PTG

[Danny Mui]

can you run a biffview on this to ensure you dont have a shared formula 
in there?

if possible attach the offending template and the code to reproduce the 
problem to bugzilla.

thanks.
Randy Tate wrote:
I can call the method below and all is fine up to a certain point(number
of cells with formulae). Once I add more cells beyond that point, the
error is thrown and cells previously working will be the point of error.
The formula being returned and displayed will be correct until the
number is exceeded. The example returned formula is just SUM(C3:C33). 

Also adding a recalc macro does not work.
When I click on cell formula the cell's contents are displayed in the
formula bar, the formula is displayed. When the formula is displayed in
the content field and I click beside of the displayed formula and either
click another cell or the green check button on the formula bar, the
formula in the cell with calculate. Pressing the Calc Now(F9) key will
not cause the calculation/recalculation to occur.


	private void buildTotalReasonLine(HSSFRow lastRow, int
calcRowStart)
	{
		// write out the division totaling formulae and then
national total formula
		for (short column=2;column < 51;column++)
		{
			// temporaririly added this block of code to
display for diagnostic purpose
                                              Object value = null;
			cell = lastRow.getCell((short) column);
//colIncrement); 
			if (cell.getCellType() ==
HSSFCell.CELL_TYPE_BLANK) 
			{
			       value = "";
				System.out.println(column+" blank >"+
value+"<");
			} 
			else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_BOOLEAN) 
			{
				value = "" +
cell.getBooleanCellValue();
				System.out.println(column+" boolean "+
value);
			} else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_ERROR) {
				value = "ERROR";
				System.out.println(column+" error "+
value);
			} else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA) {
				value = "" + cell.getNumericCellValue()
+" formula: "+cell.getCellFormula()+
				"   encoding  "+cell.getEncoding()+
				"   NumericCellValue
"+cell.getNumericCellValue()+
				"   hashCode "+cell.hashCode();
				System.out.println(column+" value " +
value);
			} else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_NUMERIC) {
				value = "" +
cell.getNumericCellValue();
				System.out.println(column+" numeric "+
value);
			} else if (cell.getCellType() ==
HSSFCell.CELL_TYPE_STRING) {
				value = cell.getStringCellValue();
				System.out.println(column+" string "+
value);
			}
					
			// actual work happens here
			if( cell.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA)
			{
				cell.setCellFormula(cell.getCellFormula());	
				cell.setCellStyle(cell.getCellStyle());
			}
			
		}
	}   // end of buildTotalReasonLine 


 

[EMAIL PROTECTED] 04/06/05 10:00 AM >>>
       

Well it looks like the formula ends abruptly (maybe something like a
missing
closing paren?), causing the StringOutOfBoundsException. Maybe you can
show
the piece of code that's causing the exception (get the line number
from the
stack trace), including the formula you're writing to the cell.
Frans
 

-----Original Message-----
From: Randy Tate [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 06, 2005 3:54 PM
To: poi-user@jakarta.apache.org 
Subject: NO IDEA SHARED FORMULA EXP PTG

Here is the exception info that was left out of yesterday's post.
java.lang.StringIndexOutOfBoundsException: String index out of
   

range:
 

0
        at java.lang.String.charAt(String.java:1037)
        at
org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:49)
        at
org.apache.poi.hssf.record.formula.ReferencePtg.<init>(Referen
cePtg.java:55)
        at com.......
**************************************************************
************
Previous Post:
I am reading in a spreadsheet template with formulae in it. None of
the
formulae have been copied/pasted nor created by the excel wizard.
   

They
 

have been hand keyed. I can have 15 cells with a formula and have no
problem. When I assume all is well and continue to hand key in the
rest
of the formulae I get the "NO IDEA SHARED FORMULA EXP PTG" error on
   

a
 

cell that previously return the correct formula.
The only reason I am doing
   

cell.setCellFormula(cell.getCellFormula())
 

is the spreadsheet will not recalculate on being opened. The
   

retrieved
 

data is in place as are the formulae. My code would be quite smaller
if
I didn't have to loop through everything and insert formulae where
needed.
So there are a couple of problems I am seeing. The version is
poi-2.5.1-final-20040804.
Any insight into these 2 observations would be appreciated,
Randy
-----------------------------------------
This e-mail and any attachments may contain CONFIDENTIAL
   

information,
 

including PROTECTED HEALTH INFORMATION. If you are not the intended
recipient, any use or disclosure of this information is STRICTLY
PROHIBITED; you are requested to delete this e-mail and any 
attachments,
notify the sender immediately, and notify the LabCorp Privacy 
Officer at
[EMAIL PROTECTED] or call (877) 23-HIPAA / (877) 234-4722.


   

---------------------------------------------------------------------
 

To unsubscribe, e-mail: [EMAIL PROTECTED] 
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi 
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/ 

   


-----------------------------------------------------------------
ATTENTION:
The information in this electronic mail message is private and
confidential, and only intended for the addressee. Should you
receive this message by mistake, you are hereby notified that
any disclosure, reproduction, distribution or use of this
message is strictly prohibited. Please inform the sender by
reply transmission and delete the message without copying or
opening it.
Messages and attachments are scanned for all viruses known.
If this message contains password-protected attachments, the
files have NOT been scanned for viruses by the ING mail domain.
Always scan attachments before opening them.
-----------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/