poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nagaraj_K <Nagara...@satyam.com>
Subject RE: RE: Urgent: setting formulas ..?
Date Tue, 18 Nov 2003 12:51:40 GMT
Hi Suma,

I am not sure if any other approaches is there but here is what I have
done.. Let me know if any explanation is reqd...
If any better approach let me know also :)

****************************************************************************
**********
    CellReference cRef = null;
    HSSFRow cRow = null;
    HSSFCell cCell = null;
    String currentSheetName = null;
  int iNumberOfSheets1 = wb.getNumberOfSheets();
  for(int iSheet=0;iSheet<iNumberOfSheets1;iSheet++){
    HSSFSheet userSheet = wb.getSheetAt(iSheet);
    currentSheetName = wb.getSheetName(iSheet);
  int iNumberOfNames = wb.getNumberOfNames();
  for(int i=0;i<iNumberOfNames;i++){
    HSSFName cName = (HSSFName)wb.getNameAt(i);
    String cNameSheetName = cName.getSheetName() ;
    String strReferenceName = cName.getReference();
    if(strReferenceName.indexOf("!") != strReferenceName.length()-1){
      AreaReference aRef = new
AreaReference(strReferenceName.substring(strReferenceName.indexOf("!")+1) );
      CellReference[] cRefs = aRef.getCells();
      for(int iSuccess=0;iSuccess<cRefs.length;iSuccess++){
        cRef = cRefs[iSuccess];
        cRow = userSheet.getRow(cRef.getRow());
        if(cRow!=null){
          cCell = cRow.getCell((short)cRef.getCol());
          if(cRefs.length==1){
            if(cNameSheetName.equals(currentSheetName))
              arlCells.add(cCell);
           if(cNameSheetName.equals(currentSheetName) )
              arlCellNames.add(cName.getNameName());
          }else{
            arlCells.add(cCell);
            arlCellNames.add(cName.getNameName());
          }

        }
      }
    }
  }
}

  int iNumberOfSheets = wb.getNumberOfSheets();
  for(int iSheet=0;iSheet<iNumberOfSheets;iSheet++){
    HSSFSheet userSheet = wb.getSheetAt(iSheet);
  Iterator rowIterator = userSheet.rowIterator();
  while( rowIterator.hasNext() ){
    HSSFRow userRow = (HSSFRow)rowIterator.next();
    int iNumberOfCells = userRow.getPhysicalNumberOfCells();
    Iterator cellIterator = userRow.cellIterator();
    int iTotal = 1;
    while(cellIterator.hasNext() ){
      HSSFCell userCell = (HSSFCell)cellIterator.next();
      if(arlCells.indexOf(userCell) > -1 ){
        System.out.println("CELL -- [" +
arlCellNames.get(arlCells.indexOf(userCell)) + "] -- VALUE -- [" +
userCell.getStringCellValue() + "] ");
        iTotal++;
      }
    }
  }
 }
}catch(Exception e){
  e.printStackTrace();
}

}
****************************************************************************
**************************

Nags

-----Original Message-----
From: Suma G Shanthappa [mailto:suma.shanthappa@cgi.com]
Sent: Tuesday, November 18, 2003 6:19 PM
To: POI Users List
Subject: Re: RE: Urgent: setting formulas ..?


Yes you are very correct... I am refering to named cell in XLS...

----- Original Message -----
From: Nagaraj_K <Nagaraj_K@satyam.com>
Date: Tuesday, November 18, 2003 6:12 pm
Subject: RE: Urgent: setting formulas ..?

> are u refering about namedCells in XL ??
> 
> Nags
> 
> -----Original Message-----
> From: Suma G Shanthappa [mailto:suma.shanthappa@cgi.com]
> Sent: Tuesday, November 18, 2003 6:12 PM
> To: POI Users List
> Subject: Re: Urgent: setting formulas ..?
> 
> 
> 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: avik.sengupta@itellix.com
> 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 <suma.shanthappa@cgi.com>:
> > 
> > > 
> > > 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 <danny@muibros.com>
> > > 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-unsubscribe@jakarta.apache.org
> > > > For additional commands, e-mail: poi-user-
> help@jakarta.apache.org> > > 
> > > > 
> > > 
> > > 
> > > ---------------------------------------------------------------
> --
> > ----
> > > To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> > > 
> > > 
> > 
> > 
> > 
> > 
> > 
> > -----------------------------------------------------------------
> --
> > --
> > To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> > 
> > 
> 
> 
> -------------------------------------------------------------------
> --
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
> 
************************************************************************
** 
> 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.
> 
************************************************************************
**
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
************************************************************************** 
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.
**************************************************************************

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message