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]>
