Add function to automatically create URL, email & file links

[JamoCA]

I'm exporting a column of URLs and would like them to be clickable. I'm currently using newStreamingXlsx and addRows to populate the table with a header and defined datatypes. I'm able to generate clickable links by looping over the query after all rows have been added.

for (row in myQuery){
    if (isvalid("url", row.link)){
        spreadsheet.setCellHyperLink(workbook, row. Link, myQuery.currentrow+1, linkColumnPosition);
    }
}

I was wondering if setCellHyperlink could be integrated with new datatypes like URL, Email, File where values are formatted as string and then automatically set using setCellHyperLink if length (or valid) using the cell's value as the link. This would allow the links to be created when addRows is performed instead of a separate routine. (If this isn't a good idea, perhaps a new function to perform this function to an entire column if the cell's value has length or is valid.) Thoughts?

[cfsimplicity]

Great suggestion, James!

[cfsimplicity]

url, email and file can now be used in your datatypes struct. For example:

testUri = "https://w3c.org";
testEmail = "test@test.com";
testFilePath = "test.xlsx";
data = QueryNew( "urls,emails,files", "VarChar,VarChar,VarChar", [ [ testUri, testEmail, testFilePath ] ] );
datatypes = { url: [ "urls" ], email: [ "emails" ], file: [ "files" ] };
spreadsheet.newChainable( "streamingXlsx" )
  .addRows( data=data, datatypes=datatypes )
  .download( "test.xlsx" );

Please give it a try on the develop branch and let me know if there are any problems.

[JamoCA]

It appears to work perfectly. I tested by added an email column to my initial script, added valid & bogus email address strings, removed the now-deprecated logic (for the links) and all populated + validated cell values were correctly hyperlinked.

Thanks!

[JamoCA]

I'm encountering an issue (using CF2016 or CF2021) where not all URL datatypes are being converted to links. I've extended your example to include 3 additional columns with the same exact URL value and the even columns (urls2 & urls4) aren't converting to hyperlinks.

spreadsheet = new luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newStreamingXlsx();

testUri = "https://w3c.org";
testEmail = "test@test.com";
testFilePath = "test.xlsx";
data = QueryNew( "urls,urls2,urls3,urls4,emails,files", "VarChar,VarChar,VarChar,VarChar,VarChar,VarChar", [ [ testUri, testUri, testUri, testUri, testEmail, testFilePath ] ] );
datatypes = { url: [ "urls", "urls2", "urls3", "urls4" ], email: [ "emails" ], file: [ "files" ] };
spreadsheet.addRows( workbook=workbook, data=data, datatypes=datatypes, includeQueryColumnNames=true ).download(workbook, "test_#datetimeformat(now(), 'yyyymmddHHnnss')#.xlsx");

[cfsimplicity]

I can replicate that in Lucee too. Let's treat this as a new separate issue.