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: [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] > > --------------------------------------------------------------------- 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 contact the sender by email and delete all copies; your cooperation in this regard is appreciated. **************************************************************************
