poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nishant Chandra <nishant.chan...@gmail.com>
Subject Re: Remote reference formula support
Date Tue, 14 Jun 2005 11:29:38 GMT
Avik Sengupta <avik.sengupta <at> itellix.com> writes:

> 
> >CAn this be
> > done in HSSF?Pls help.
> 
> If its throwing an exception, then apparently not :)
> 
> Whats a remote reference formula? Is that a pipe character? What does it
> do? 
> 
> Can you provide the parsed result of such a formula (using biffviewer?)
> 
> On Tue, 2005-06-14 at 12:05 +0200, Nishant Chandra wrote:
> > POI  HSSF doesnt seem to support remote reference formula(supported by
> > excel). I am trying to do something like this:
> > 
> >         HSSFWorkbook wb = new HSSFWorkbook();
> >         HSSFSheet sheet = wb.createSheet("new sheet");
> >         
> >         HSSFRow row = sheet.createRow((short) 0);
> >          //Create a cell and put a value in it.
> > 
> >         HSSFCell mycell = row.createCell((short)0);
> >         mycell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> >         mycell.setCellFormula("BLP|M!MSFT"); //remote reference formula 
> > 
> > I am getting the following error:
> > 
> > java.lang.StringIndexOutOfBoundsException: String index out of range: 0
> > 	at java.lang.String.charAt(String.java:444)
> > 	at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:44)
> > 	at org.apache.poi.hssf.record.formula.ReferencePtg.<init>(ReferencePtg.
java:50)
> > 	at org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:320)
> > 	at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:524)
> > 	at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:602)
> > 	at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:
651)
> > 	at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:756)
> > 	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:
689)
> > 
> > 
> > I can enter the formula manually in excel and it works. CAn this be
> > done in HSSF?Pls help.
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: poi-user-unsubscribe <at> jakarta.apache.org
> > Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
> > 

This is what the excel help has to say:

you can enter a remote reference formula that refers to the data. The data is 
displayed on your worksheet as the result of the formula.

The formula has three parts: the name of the application; the file name or 
topic; and the cell range, value, field, or data that's referred to. The 
following illustration shows the parts of a remote reference formula and the 
required separator characters.

BLP|M!MSFT

A pipe character (|) separates the program name from the document or topic name. 
An exclamation point (!) separates the document or topic name from the cell 
range, value, field, or data referred to.

When to use single quotation marks   If the name of the application, document, 
topic, or data item contains spaces or characters such as colons (:) or minus 
signs ( – ) that have special uses in a formula, or if a name resembles a cell 
reference, enclose the name in single quotation marks.

DDE support required   The program that contains the data you want to link must 
support Dynamic Data Exchange (DDE). To determine whether your program supports 
DDE or to learn the DDE name and topic information for the program, contact the 
software vendor of the program. For more information about DDE, see the 
Microsoft Office 2000 Resource Kit.





---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Mime
View raw message