tracker issue : CF-4175063

select a category, or use search below
(searches all categories and all time range)

Apache POI getHyperlink integration for CFSpreadsheet

| View in Tracker

Status/Resolution/Reason: To Fix//

Reporter/Name(from Bugbase): matt wilde / matt wilde (matt wilde)

Created: 07/22/2016

Components: Document Management, Office Integration

Versions: 2016

Failure Type: Enhancement Request

Found In Build/Fixed In Build: CF2016_Update1 /

Priority/Frequency: Trivial / Unknown

Locale/System: English / Win 2008 Server R2 86 bit

Vote Count: 1

I love Spreadsheet and Query functions in CF but have not been able to find a Coldfusion Function for getting a Cell's hyperlink data. cellFormat does not contain that data.

I wrote a wrapper function to do this with java objects, loading the Apache POI classes.

Could you please add this to CFSpreadsheet?

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4175063

External Customer Info:
External Company:  
External Customer Name: matt wilde
External Customer Email:  
External Test Config: My Hardware and Environment details:

Coldfusion 2016


  1. July 22, 2016 00:00:00: 1_LinkFetcher.cfc
  2. August 04, 2016 00:00:00: 3_JUNE_2016_RESIDENTIAL_PERMITS.change_this_to_xlsx.txt


Matt, Thanks for raising this request and for sharing the implementation details. I just wanted to get your perspective on the expected behavior for different types of content that the hyperlink can point to. I guess, this feature would be most relevant if the link is pointing to someplace in the same excel document (say different cell, or cell in another sheet). But how would you expect it to behave if it is pointing to a webpage, or some content in the local file-system? Also, can you share the specific use case you need this for, so that we have at least one real world scenario for this feature.
Comment by Piyush K.
2138 | July 27, 2016 09:05:20 AM GMT
I didn't think about the cases in which it would point to a local directory or a location in the same document, my use case was when it pointed to an external webpage. I was able to make it work the way I want it to, by using the code in the attached file. I extracted the external webpage link as a string. This is why I needed it: I get a spreadsheet from a source, I don't have any say on how this spreadsheet is formatted, but each row has a cell with a hyperlink to a webpage. The Text for the hyperlink is not the link itself. SO i need the underlying data. My job is to take all of the spreadsheet data and organize it and separate it into structs of json Data. So to do that I convert the spreadsheet to a query object and then dissect the information from there. But at that point hyperlink data is lost, i just get the text value like "Link 1" for example. So, what I wanted to do was before converting it to a query object, was take the spreadsheet, loop through the column with all my links, and run a function like SpreadsheetGetCellHyperlink(spreadsheet,row,column) similar to SpreadsheetGetCellFormula(spreadsheet,row,column). and it would return the hyperlink in the form of a string. right now this is accomplished by creating all the necessary POI java objects in coldfusion to loop through a spreadsheet, and when you finally get down to the cell level you can run cell.getHyperlink(); a java function in the apache library. can this function be wrapped up to use straight from the coldfusion spreadsheet interface instead of having to go to java to dissect the spreadsheet? the function I wrote returns all the hyperlinks in an array, each spot in the array represents a row and all links found in that row. It's an array of structs. array[1] = {"col_1"="","col_4"=""...etc} which i think is a logical thing to return if you want to get all the links in the spreadsheet and then just get the specific link you want by sifting through that array. So maybe the function should be SpreadsheetGetHyperlinks instead of SpreadsheetGetCellHyperlink. either way, this is the only thing i found to fix my problem was this java workaround and I think it would be helpful to include it in the spreadsheet functions. Thanks for the reply, look forward to a response. matthew
Comment by External U.
2139 | August 01, 2016 04:48:04 PM GMT
Real world scenario: A city has publicly available information about building permits that are requested. This info is in a spreadsheet that anyone can download. Column one of this spreadsheet holds the info for the Permit Number. Suppose the first record has permit number 1111. That 1111 has a hyperlink that points to a unique webpage with more info on that permit. I want to take the spreadsheet and dissect each record and present all of it's information to a client. I would also like to provide the unique link to the client so they can look at the permit details directly on this external site provided by the city in question. So, I need to get the hyperlink data from the cell, similar to how you would get the format data from a cell.
Comment by External U.
2140 | August 01, 2016 04:53:08 PM GMT
I am attaching the spreadsheet I built this for.
Comment by External U.
2141 | August 03, 2016 04:46:46 PM GMT