Bug 45970 - Unble to read and write the formula string which has "HTTP url"
Summary: Unble to read and write the formula string which has "HTTP url"
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 49896
Blocks: 46670
  Show dependency tree
Reported: 2008-10-08 17:46 UTC by kdurairaj
Modified: 2012-02-29 11:54 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description kdurairaj 2008-10-08 17:46:40 UTC
I am using HSSF api to automate our excel sheets which is really helpful. We are able to reduce lot of manual works due to the automation with help of HSSF api package. Thank you so much for contribution and I have one quick doubt on handling formula's which has ( sheet reference with http address). I tried to read and write formulas with sheet reference with http address. Looks like it's not supported. If it's supported, kindly request you to send me some sample/examples on how to do that.
Example : my excel sheet cell has formula as :
when I read it using "Cell.getCellFormula()" it returns the formula string as "(QualityIndex!$X$6)" where Quality Index is my excel sheet name.
But I don't see the way to convert the formula to the actual string which has HTTP value. Coz, I want to modify the http location file name and set the formula back.
I couldn't read the formula and get the value 'http://dummyurl/~qa/p4depot3/qa/main/doc/logan/QualityIndex/API_FVT/VC-ESX40/[VC40-ESX40-IndexRating_100608.xls]Summary' 
as well if I try to write the above formula string using cell.setCellFormula ("('http://dummyurl/~qa/p4depot3/qa/main/doc/logan/QualityIndex/API_FVT/VC-ESX40/[VC40-ESX40-IndexRating_100608.xls]Summary'!$X$6)"); also didn't work out. 
Pls let me know if this is a restriction with HSSF api or I need to use alternate api.
Thanks in Advance,
Comment 1 Nick Burch 2010-06-04 10:44:47 UTC
I've just tested this, and added a unit test for it (partly disabled)

POI can read URL-based formula references just fine. However, attempting to set one (either on a new cell, or changing an existing one) will break it

Probably one for Josh to look at when he has a minute, as at a guess I'd say there's something going wrong with encoding to a Ref3DPtg / ExternSheetRecord / SupBookRecord
Comment 2 Yegor Kozlov 2011-06-25 13:56:35 UTC
Seems to be related to https://issues.apache.org/bugzilla/show_bug.cgi?id=49896

Comment 3 Yegor Kozlov 2012-02-29 11:54:31 UTC
The problem should be fixed in r1242701

Please try with the latest build form trunk.