EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Andy Chien <hs...@utoronto.ca>]

Hi All:

As subject. How would I get the text formula string from the 
FormulaRecord? Upon inspecting the code for cellGetFormula from the 
usermodel, it seems that the answer lies within the ptg object. But I 
have no idea what it is or how to manipulate it. HSSFCell.GetFormula() 
seem to call Formula.Parse feeding in a Workbook object (not 
HSSFWorkbook, i just assume it's the raw form of HSSFWorkbook) and the 
ptg object.

Secondly, is there a way of setting the formula for a cell without it 
getting parsed?


The reasons why I am asking the above questions is because I would like 
to copy and paste excel file in plain text without formatting at the 
same time bypassing the Excel App's tendency to parse link (because some 
cell link will be pasted without the sheet actually being there that is 
why the formula must not be parsed). I am trying to use such methods to 
relocate a large and potentially corrupted Excel file into a fresh new 
workbook.

I have had some success in using HSSFCell.SetCellValue(String) by 
setting the string to things like "=Sheet5!A1+B1" it will show up as 
String but it will not be parsed as formula (by Excel) until you do F2 
and press enter on each and every cell.


If somebody can show me some insight that would be much appreciated.


Thanks in advance.

Andy Chien

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Andy Chien <hs...@utoronto.ca>]

Thanks for reply Nick.

I am getting an odd exception as follows while using

Code
=======
FormulaRecord frec = (FormulaRecord) record;
String fs = FormulaParser.toFormulaString(null,frec.getParsedExpression());

Exception
============
org.apache.poi.hssf.record.RecordFormatException: Coding Error: Expected 
ExpPtg to be converted from Shared to Non-Shared Formula by 
ValueRecordsAggregate, but it wasn't

What could this mean? Thanks in advance.

Andy Chien

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Nick Burch <ni...@torchbox.com>]

On Tue, 27 May 2008, Andy Chien wrote:
> I was able to initialize model.Workbook by using ExternSheetRecord (for
> sheet reference) and BoundSheetRecord (for sheet name) and achieve what
> I have been intending to do with slight imperfection.

That's great news. Based on the information you've found, I've now coded
something up in svn trunk - eventusermodel.EventWorkbookBuilder

The idea is that it'll handle building you a stub HSSFWorkbook, so you can
pass it off to the formula parser and resolve formula ptgs into strings.
Hopefully now everyone'll be able to do this.

So, thanks for your digging and perseverance :)

Nick

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Andy Chien <hs...@utoronto.ca>]

Success!!

I was able to initialize model.Workbook by using ExternSheetRecord (for 
sheet reference) and BoundSheetRecord (for sheet name) and achieve what 
I have been intending to do with slight imperfection.

With a bit of VBA help I was able to iterate through the workbook and 
set those forced excel text string back into formula.

The only imperfection is that the external file reference (i.e. 
"[c:\document\myxls.xls]Sheet1!A5:B20") does not seem to be included in 
ExternSheet or BoundSheet, I suspect it will be in SST table. But I have 
got enough to achieve what I need. thanks Nick.

Andy Chien

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Nick Burch <ni...@torchbox.com>]

On Fri, 16 May 2008, Andy Chien wrote:
> Upon random browsing though various children of ptgs there are several 
> references to hssf.model.Workbook.getSheetReferences(). Is this it?

I believe it is. One way to check is to look on the ooxml branch. There, 
we've got all the Ptgs working with just a 
org.apache.poi.ss.usermodel.Workbook object. This allows them to work with 
both HSSF, and XSSF.

What you could try doing within the 3.1 series codebase is extend 
hssf.model.Workbook, and only implement the methods that are actually used 
by Ptgs (external sheet references, and named ranges, if memory serves 
correct). Try passing that in, and see if it works for you.

Nick

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Andy Chien <hs...@utoronto.ca>]

Hi All:

Did some tracing have not had the time to write code to verify this yet. 
Does anybody knows what type of ptg in the ptg list returned by the 
frec.getParsedExpression() is that causes the sheet reference lookup? 
That would help pinpoint my trace.

I did some tracing, unfortunately the ptg list returned by 
frec.getParsedExpression() appears to be an aggregated type of ptg. 
Where ptg.toForumlaString() is defined to be an abstract, leaving it to 
the child classes to implement. Since ptg list is designed to be 
aggregated, it becomes difficult to identify which children of ptg is 
involved in making the inter-sheet reference. Notable call sequence as 
follows:

FormulaParser.toFormulaString(,) Calls and results...
ptgs[i].toFormulaString(book)

Upon random browsing though various children of ptgs there are several 
references to hssf.model.Workbook.getSheetReferences(). Is this it? If 
so I have inspected the hssf.model.Workbook then ExternSheetRecord is 
really the records I need to get to make the hssf.model.Workbook work 
with FormulaParser? All of these are just speculation. Please let me 
know to confirm or correct me if I am going awry. Thanks.


Andy Chien

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Nick Burch <ni...@torchbox.com>]

On Wed, 14 May 2008, Andy Chien wrote:
> I think I found out what is going on, the following is just a suspicion 
> but I have not had enough time to trace through the source code.
>
> POI is having trouble parsing inter-sheet references because the 
> Workbook Object is not supplied.

That sounds quite possible, yes. Basic formula references should be fine, 
but complex intra-sheet ones won't be possible without all the usermodel 
data structures kicking about

> It seems that workbook is a lower level data structure container in 
> HSSFworkbook.

That's correct. model.Workbook handles just record stuff, and then 
HSSFWorkbook presents a sane view on top of it all. You won't have a 
model.Workbook when using eventusermodel


Possibly your only option is to pick up the records that the formula 
parser needs, as they go past. You can probably persuade model.Workbook to 
initialise off just a few records, which you then pass into the formula 
stuff. Won't be pretty though :(

Nick

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Andy Chien <hs...@utoronto.ca>]

I think I found out what is going on, the following is just a suspicion 
but I have not had enough time to trace through the source code.

POI is having trouble parsing inter-sheet references because the 
Workbook Object is not supplied.

Original -----
FormulaParser.toFormulaString(Workbook book, java.util.List lptgs)

On Example -----
FormulaParser.toFormulaString(null, frec.getParsedExpression())

Therefore the syntax like the above (in the example suggested by Nick 
earlier) does not work while parsing inter-sheet references.

It seems that workbook is a lower level data structure container in 
HSSFworkbook. Even though a method in usermodel 
HSSFWorkbook.getWorkbook() gets it it is not easily accessible by the 
eventusermodel because you would have to fall back to usermodel (which 
gobbles up memory on large excel file and defeats the purpose of 
eventusermodel).

If anybody can shed some light, it would be greatly appreciated. Thanks.


Andy Chien

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

Re: EventUserModel - Getting the Formula String from FormulaRecord and Setting Formula

[Nick Burch <ni...@torchbox.com>]

On Tue, 6 May 2008, Andy Chien wrote:
> As subject. How would I get the text formula string from the
> FormulaRecord?

See lines 146 to 166 of
src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
from svn trunk, or 3.1 beta 1. That should hopefully show you how to get
both the evaluated answer of a formula, and the string the defines it.

> Secondly, is there a way of setting the formula for a cell without it
> getting parsed?

Not in OLE2 excel. The formula is only ever stored in the file in its
parser form, so we have to process the ptgs to get back the string. In
OOXML excel, the formula is stored as a string, so it's much simpler

Nick

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