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: [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]
