Yeah, thats what shared formulas are.. a method excel uses to optimise storage of formulas. It stores ONE formula record, and stores shared formula records for each cell, referencing the original formula record.
As you figured, there is no support in poi for reading shared formulas. As far as writing is concerned, we dont need to write shared formualas. Probabaly never will. So, as Andy mentioned, as far as formulas are concerned, write functionality is more robust than read functionality. Hopefully, we'll get there soon. Quoting David N Horn <[EMAIL PROTECTED]>: > Actually, folks, after some digging around I have come up with the solution > to my own problem. It turns out the formulas which were not being read > correctly by HSSF had been generated in Excel in the standard manner of > "dragging down". That is, when a range of cells all need to contain the > same formula but applied to different ranges, you enter the formula for the > first range and then drag-copy that cell into the rest of the range, making > it apply correctly to the various cell ranges (but you all knew that > already!) > > It appears to be this which causes the api to read the cell formula > incorrectly. I have replaced the dragged versions of all formulae in my > test-case workbook with the longhand versions and they now read correctly. > This is good in the short term, but might need to be looked at in the > future > as we can only assume that most .xls workbooks will have been generated in > the "dragged-down" manner. > > Thanks anyway. > > David > > -----Original Message----- > From: David N Horn [mailto:[EMAIL PROTECTED]] > Sent: 16 July 2002 21:57 > To: [EMAIL PROTECTED] > Subject: problems reading simple formulas > > > Hello all. This is my first post to the list and I hope someone will be > able to help me out. > > I am currently using the poi api as part of a larger project (aren't we > all?!) and I need to be able to read in any .xls file and then manipulate > its contents as per the needs of the project. I am able to read in the > files and output the cell contents well enough but I am having problems > when > it comes to formula cells. > > Now, from what I have read here, I know formulas are not yet well > supported, > but I understand there is _some_ support. I am using dev version 1.8.0 and > my formulas are far from complicated. They are of the type SUM(D3:D15) or > AVERAGE(C9:I9), nothing fancy. > > The code I use reads in the formula cells okay and even recognises them to > be formula cells, but usually displays them as "NO IDEA SHARED FORMULA EXP > PTG". I understand this to mean a shared formula is being used (a shared > formula being one that spans sheets/workbooks?) which is not the case. > Moreover, some of the cells read using poi (actually HSSF) do show as the > correct formula, even though I can see no difference whatsoever between > those that read right and those that do not! Which only adds to the > confusion. > > Is there something I am doing wrong here? Is there no support yet for what > I am trying to do? What can I do to my code to ensure formulas are read > right across the board? > > Thanks for your help folks. > > David N Horn > > > "I like work: it fascinates me. I could sit and look at it for hours." > > - Jerome K Jerome > "Three Men in a Boat" > > > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > > > > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
