error trying export hyperlink

[dwaggott]

Is there a way to export the following text from a data.frame? I got an error

=HYPERLINK("http://www.ncbi.nlm.nih.gov/gene?term=2526","FUT4")

Unknown function HYPERLINK() in formula at /home/apps/R/R-3.1.0/library/WriteXLS/Perl/WriteXLS.pl line 293.

[marcschwartz]

Hi,

Try writing the data frame to an XLSX file, rather than an XLS file.

When creating an XLS file, the older and no longer supported Perl module Spreadsheet::WriteExcel is used, whereas when creating an XLSX file, the newer Perl module Excel::Writer::XLSX is used and has better support for writing Excel functions.

Here is an example:

DF <- data.frame(URL = "=HYPERLINK(\"http://www.ncbi.nlm.nih.gov/gene?term=2526\",\"FUT4\")")

require(WriteXLS)

# This does not work:

> WriteXLS("DF", "DF.xls") 
Unknown function HYPERLINK() in formula at
/Library/Frameworks/R.framework/Versions/3.1/Resources/library/WriteXLS/Perl/WriteXLS.pl line 293.
The Perl script 'WriteXLS.pl' failed to run successfully.

# This works:

WriteXLS("DF", "DF.xlsx")

Note that to change the target Excel file type, use '.xlsx' as the extension rather then '.xls'. The WriteXLS() function will automatically select the correct Perl script based upon the file extension.

Let me know if that works for you.

Regards,

Marc

[dwaggott]

This works!

Only caveat is that the hyperlinks are not formatted (i.e. blue / underlined). Any ideas?

Ideally, I'd like to apply apply some type of excel template for pretty formatting.

[marcschwartz]

Hi,

Thanks for letting me know that it does work for you.

The formatting of links within a worksheet cell is outside the scope of WriteXLS, as cell level functionality is not supported. It is primarily designed to support cross-OS exporting of raw data from an R data frame to an Excel file. This grew out of a need to assist clients some years ago with raw data dumps, as an alternative to simply providing them with CSV files. At the time I was on Linux and so needed to use Perl to export to a file format that was primarily Windows based, without having MS Office installed. That it supports exporting Excel function content, such as you raised here, is not really intentional, but a byproduct of the Perl module being used to create the Excel file. The Perl modules provide a variety of other functionality that I do not intend to use in WriteXLS.

The only real formatting that I include is to bold the header row, which typically contains column names and then to widen columns to mimic the auto width capability for ease of reading. In the latter case it is an approximation as the actual auto width function is not available outside of Excel.

I would recommend using one of the other Excel exporting packages like XLConnect (http://cran.r-project.org/package=XLConnect), which provides more flexibility at the cell and worksheet level, including formatting. I am not sure if it supports the use of templates, so you would need to look at the documentation on that.

[dwaggott]

I appreciate the help and effort on this package. I can sympathize with only working on linux and regularly needing to import/export xls from R and perl. I'll take a look at your recommendations.