I am attaching the file i am trying to open.... When I delete the the contents of the cell next to label(cell value), I am able to open the file... otherwise it throws an exception.
----- Original Message ----- From: Nagaraj_K <[EMAIL PROTECTED]> Date: Thursday, November 20, 2003 11:14 am Subject: RE: Urgent: setting formulas ..? > Suma, > > Can you explain as to what the requirement is? I created an XL > sheet with > the conditions matching the explanation given by u. but opening > the XL is > not giving any error for me... > > Nagaraj > > -----Original Message----- > From: Suma G Shanthappa [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 20, 2003 11:05 AM > To: POI Users List > Subject: Re: Urgent: setting formulas ..? > > > > Nagaraj, your code works fine to get the name of the cell. Thanks > for > that. > > I am facing one more problem.... > > Can't I have a cell with the value as =namecell > where namecell is the name of a cell which has a formula like =sum > (A1:B1) or any other formula? > When I try to open an XLS file with the above specified > condintion, it > throws an exception at this line of code... > > HSSFWorkbook workbook = new HSSFWorkbook(inFile); > > and the exception thrown is... > > java.lang.reflect.InvocationTargetException: > java.lang.ArrayIndexOutOfBoundsException > at org.apache.poi.util.LittleEndian.getNumber > (LittleEndian.java:557) > at org.apache.poi.util.LittleEndian.getShort > (LittleEndian.java:90) > at org.apache.poi.hssf.record.formula.NamePtg.<init> > (NamePtg.java:100) > at org.apache.poi.hssf.record.formula.Ptg.createPtg > (Ptg.java:251) > at > org.apache.poi.hssf.record.FormulaRecord.getParsedExpressionTokens > (FormulaRecord.java:171) > at org.apache.poi.hssf.record.FormulaRecord.fillFields > (FormulaRecord.java:147) > at > org.apache.poi.hssf.record.Record.fillFields(Record.java:143) > at org.apache.poi.hssf.record.Record.<init>(Record.java:105) > at org.apache.poi.hssf.record.FormulaRecord.<init> > (FormulaRecord.java:118) > at java.lang.reflect.Constructor.newInstance(Native Method) > at org.apache.poi.hssf.record.RecordFactory.createRecord > (RecordFactory.java:254) > at org.apache.poi.hssf.record.RecordFactory.createRecords > (RecordFactory.java:192) > at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init> > (HSSFWorkbook.java:187) > at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init> > (HSSFWorkbook.java:154) > at testXLSCellName.main(testXLSCellName.java:36) > > > Any help on this? > > ----- Original Message ----- > From: "Suma G Shanthappa" <[EMAIL PROTECTED]> > Date: Thursday, November 20, 2003 9:54 am > Subject: Re: Urgent: setting formulas ..? > > > Following is the code which gets the updated formula.... this > > works > > fine only if you try to copy the formula to the cell in the same > > column > > as the formula cell. > > > > -------------------- > > > > /** > > * This function creates new formula that is to be copied > > to > > next row in the same column > > * @param oldFormula > > * @param noOfRowShift > > * @return > > */ > > public static String getCopyFormula(String oldFormula, > int > > noOfRowShift) > > { > > // creating new work book for parsing > > Workbook work = new Workbook(); > > // creating parser for parsing the formula > > FormulaParser parser = new > > FormulaParser(oldFormula, > > work); > > // parsing the formula > > parser.parse(); > > > > // get PTG's in the formula > > Ptg[] ptg = parser.getRPNPtg(); > > > > // iterating through all PTG's > > for(int i=0; i<ptg.length; i++) > > { > > // for area of cells A1:A4 > > if( ptg[i] instanceof AreaPtg ) > > { > > AreaPtg arePtg = (AreaPtg)ptg[i]; > > // if first row is relative > > if( arePtg.isFirstRowRelative() ) > > { > > arePtg.setFirstRow((short) > > (arePtg.getFirstRow()+noOfRowShift)); > > } > > // if last row is relative > > if( arePtg.isLastRowRelative()) > > { > > arePtg.setLastRow((short) > > (arePtg.getLastRow()+noOfRowShift)); > > } > > } > > // for references such as A1, A2, B3 > > else if( ptg[i] instanceof ReferencePtg ) > > { > > ReferencePtg refPtg = > > (ReferencePtg)ptg[i]; > > // if row is relative > > if( refPtg.isRowRelative() ) > > { > > refPtg.setRow((short) > > (refPtg.getRow()+1)); > > } > > } > > } > > return FormulaParser.toFormulaString > > (work.getSheetReferences(),ptg); > > } > > > > > > ----- Original Message ----- > > From: Avik Sengupta <[EMAIL PROTECTED]> > > Date: Tuesday, November 18, 2003 6:44 pm > > Subject: Re: Urgent: setting formulas ..? > > > > > > That really works... > > > Have you managed to implement it? It would be good if you > could > > share> it. > > > > > > Regards > > > - > > > Avik > > > > > > On Tue, 2003-11-18 at 18:11, Suma G Shanthappa wrote: > > > > Thanks a lot... That really works... > > > > Is there a way through which I can get the name of the cell? > > > > > > > > Regards, > > > > Suma > > > > > > > > ----- Original Message ----- > > > > From: [EMAIL PROTECTED] > > > > Date: Tuesday, November 18, 2003 12:28 pm > > > > Subject: Re: Urgent: setting formulas ..? > > > > > > > > > On second thoughts, this wont be very difficult. > > > > > > > > > > Get the formula string from a cell. Parse it thru formula > > > parser > > > > > and get the > > > > > token array. Check the class of each token, if they are > > > > > ReferencePtg's or > > > > > AreaReferencePtg's , then change the reference in them. > The > > > Ptgs > > > > > will have > > > > > methods to get the row and col, and will also tell u if > the > > > > > reference is > > > > > absolute or relative ($A1 vs A1) .. so its quite simple. > > > Finally, > > > > > transform the > > > > > array to a string, and set it to the new cell.. > > > > > > > > > > So you should be able to do this easily > > > > > > > > > > Regards > > > > > - > > > > > Avik > > > > > > > > > > > > > > > Quoting Suma G Shanthappa <[EMAIL PROTECTED]>: > > > > > > > > > > > > > > > > > Is there no other way other than manually parsing the > > > formula? > > > > > > Parsing formula manually can be done for the formula > that > > is > > > known..> > > How do I parse if I don't know what kind of > > formulas > > > can be > > > > > present in > > > > > > XLS sheet. > > > > > > I am parsing an XLS sheet where I only know that there > are > > > some > > > > > > formulas.. but don't really know what kind of formulas > can > > > be > > > > > present > > > > > > in the sheet. > > > > > > Can somebody help me on this? > > > > > > > > > > > > Regards, > > > > > > Suma > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > > > From: Danny Mui <[EMAIL PROTECTED]> > > > > > > Date: Monday, November 17, 2003 9:13 pm > > > > > > Subject: Re: Urgent: setting formulas ..? > > > > > > > > > > > > > I do not believe we shift the cell values when you > > > reassign > > > > > cell > > > > > > > formulas. You're going to have to update the cell > > > references > > > > > > > manually > > > > > > > when you set the new formula through string manipulation. > > > > > > > > > > > > > > prashant neginahal wrote: > > > > > > > > > > > > > > >Hi All, > > > > > > > >I am facing problem in setting formulas to different > cells.> > > > > > >Lets say for C2 cell already set formula is > > > > > SUM(A1:B1)/SUM(A1:A2).> > >In programme i know this cell > has > > > > > formula and i want to set this > > > > > > > to > > > > > > > >another cell C3. Using getCellFormula() i will > retrieve > > > the > > > > > C2 > > > > > > > cell > > > > > > > >formula and set it to C3, and now formula for C3 > should > > > be > > > > > > > >SUM(A2:B2)/SUM(A2:A3). > > > > > > > >How this changing of formula can be achieved using POI? > > > > > > > >This is urgent, please somebody guide me. > > > > > > > > > > > > > > > >Regards, > > > > > > > >Prashant > > > > > > > > > > > > > > > > > > > > > > > >Yahoo! India Mobile: Ringtones, Wallpapers, Picture > > > Messages > > > > > and > > > > > > > more.Download now. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------- > -- > > -- > > > ---- > > > > > ---- > > > > > > > -- > > > > > > > To unsubscribe, e-mail: poi-user- > > > [EMAIL PROTECTED]> > > > For additional commands, > e- > > > mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > --------------------------------------------------------- > -- > > -- > > > ---- > > > > > ---- > > > > > > To unsubscribe, e-mail: poi-user- > > [EMAIL PROTECTED]> > > > For additional commands, e- > > mail: poi-user- > > > [EMAIL PROTECTED]> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ----------------------------------------------------------- > -- > > -- > > > ---- > > > > > -- > > > > > To unsubscribe, e-mail: poi-user- > [EMAIL PROTECTED]> > > > For additional commands, e- > mail: poi-user- > > [EMAIL PROTECTED]> > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------- > -- > > -- > > > ---- > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > > For additional commands, e-mail: poi-user- > [EMAIL PROTECTED]> > > > > > > > > > > > --------------------------------------------------------------- > -- > > -- > > > -- > > > 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] > > > > > > > ------------------------------------------------------------------- > -- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > ************************************************************************ ** > This email (including any attachments) is intended for the sole > use of the > intended recipient/s and may contain material that is CONFIDENTIAL AND > PRIVATE COMPANY INFORMATION. Any review or reliance by others or > copying or > distribution or forwarding of any or all of the contents in this > message is > STRICTLY PROHIBITED. If you are not the intended recipient, please > contactthe sender by email and delete all copies; your cooperation > in this regard > is appreciated. > ************************************************************************ ** >
test1.xls
Description: MS-Excel spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
