You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@poi.apache.org by harry555 <ha...@yahoo.co.uk> on 2010/01/12 15:16:50 UTC

Is there a simple way of doing this?

I have this range formula 
  'General Return Details'!$E$3
got using - 
  HSSFName name = wb.getName("organisation_name");

I want to be able to modify the cell reference bit as easy as possible - so
for example calling a method like 
  String newRef = modifyColumnValue(name.getRefersToFormula(), 1);
giving 
  'General Return Details'!$E$4
or
  String newRef = modifyRowValue(name.getRefersToFormula(), -1);
giving 
  'General Return Details'!$C$4

Is there a simple way of doing this or do I have to write my own methods?

thanks in advance

harry
-- 
View this message in context: http://old.nabble.com/Is-there-a-simple-way-of-doing-this--tp27128186p27128186.html
Sent from the POI - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Is there a simple way of doing this?

Posted by Ramin Assisi <ra...@googlemail.com>.

Hi all POI developers,

we would like to inform you that we have started to develop Java
components to view and edit OOXML documents on the basis of POI.

See: http://assisi.eu.com

These components can help you to verify POI document generations or to
integrate it in existing Java applications. We would appreciate any
ideas.

As in POI there are missing a lot of API calls, we had to wrap it with
our own classes and extend it by directly dealing with the schema. We
would like to contribute these extensions. Is there anyone who can
help?

By the way many thanks for the great work!

Ramin
Applied Soft Ltd.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Is there a simple way of doing this?

Posted by Yegor Kozlov <ye...@dinom.ru>.

> thanks for that Yegor, found this way also which seems to work but I assume
> your way is the usual so I'll switch to using that!
> 
> Also, I'm using Excel 2003 spreadsheets, as I understand it OOXML is for
> 2007+ - is that right?, 

right. OOXML is supported by Excel 2007 and later.

if not, should I be using OOXML? never heard of it
> before that's all and therefore no experiance in knowing if it;s any good!
>

The advantage of OOXML is that it allows Big Grid - the row limit in XLS is 64K while in XLSX it is 1M. The column limit 
in XLS is 256 and 16K in XLSX. If you chose OOXML, be aware that it can be viewed / edited by a less number of 
application that the binary format. To open .xlsx you need Office 2007 or OpenOffice 3.1+.

Yegor


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Is there a simple way of doing this?

Posted by harry555 <ha...@yahoo.co.uk>.

thanks for that Yegor, found this way also which seems to work but I assume
your way is the usual so I'll switch to using that!

Also, I'm using Excel 2003 spreadsheets, as I understand it OOXML is for
2007+ - is that right?, if not, should I be using OOXML? never heard of it
before that's all and therefore no experiance in knowing if it;s any good!

cheers
-- 
View this message in context: http://old.nabble.com/Is-there-a-simple-way-of-doing-this--tp27128186p27141368.html
Sent from the POI - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: Is there a simple way of doing this?

Posted by Yegor Kozlov <ye...@dinom.ru>.

POI does not have the helper methods you want. The simplest way to modify a range formula is using the CellReference 
object:


         HSSFName name = wb.getName("organisation_name");

         CellReference oldRef = new CellReference(name.getRefersToFormula());

         CellReference newRef = new CellReference(
                 oldRef.getSheetName(),
                 oldRef.getRow() + 1,
                 oldRef.getCol() - 1,
                 oldRef.isRowAbsolute(),
                 oldRef.isColAbsolute()
         );
         name.setRefersToFormula(newRef.formatAsString());

Yegor

> I have this range formula 
>   'General Return Details'!$E$3
> got using - 
>   HSSFName name = wb.getName("organisation_name");
> 
> I want to be able to modify the cell reference bit as easy as possible - so
> for example calling a method like 
>   String newRef = modifyColumnValue(name.getRefersToFormula(), 1);
> giving 
>   'General Return Details'!$E$4
> or
>   String newRef = modifyRowValue(name.getRefersToFormula(), -1);
> giving 
>   'General Return Details'!$C$4
> 
> Is there a simple way of doing this or do I have to write my own methods?
> 
> thanks in advance
> 
> harry


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org