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]
