Hi Patrick,

a nice workaround is the following:

Please create your file.xls file, but not in the path galaxy will provide, use a temporary path, for example in the current working dir in your wrapper. Than after your program has finished, just move the *.xls file to the galaxy output path. Done. Like this.

myprogram.r -i $input -o foo.xls -p 4;
mv foo.xls $output;

Cheers,
Bjoern





Am 04.09.2014 um 23:27 schrieb Patrick Leyshock:

Hello,

I am trying to write a Galaxy tool that will output an Excel file.

Currently the tool wrapper calls an R script, which uses the "xlsx" package
to read and write to and from Excel files.

After being invoked by Galaxy, the script is able to successfully read an
input Excel file:

     suppressPackageStartupMessages(library(xlsx, quietly=TRUE));
     raw.data <- read.xlsx(commandArgs(trailingOnly=TRUE)[1]),
sheetName="input_records");

The script then does its work on the data just read in.  Then, when the
work is done, I'd like to output the results to an Excel file.  Here's
where I run into a problem.  I first tried to output the result like this:

     write.xlsx(processed.data, file=commandArgs(trailingOnly=TRUE)[2]);

but write.xlsx throws an error.  Looks like  ".xls" and ".xlsx" are the
only legal file extensions for the write.xlsx() function.  Inspecting
commandArgs(trailingOnly=TRUE)[2] shows that Galaxy provided a filename
extension of ".dat".

I tried a workaround using this:

     write.xlsx(processed.data,
file=paste(commandArgs(trailingOnly=TRUE)[2]), ".xls", sep="");

The write.xlsx function no longer throws an error (since the file name
supplied as a parameter has an acceptable file extension) but now Galaxy
won't display the result in the History.  If I look in Galaxy's database I
see two files there corresponding to my tool's output.  Supposing that
Galaxy assigned the result the name "dataset_87", then looking in the
Galaxy database I see:

     dataset_87.dat
     dataset_87.dat.xls

"dataset_87.dat" is empty but visible to Galaxy (and so displayed as an
empty dataset in the History window). "dataset_87.dat.xls" has the results
I want but isn't displayed by Galaxy in the History window.

There might be R libraries that can write xls or xlsx files without
requiring a ".xls" or ".xlsx" file extension.  That's a path I'm willing to
explore.  That said, is there a configuration option I can set that'll let
me continue to use write.xlsx()?

I've been working from the suggestions made on this helpful thread:

http://lists.bx.psu.edu/pipermail/galaxy-dev/2011-December/007807.html

so the relevant entries in datatypes_conf.xml are:

     <datatype extension="xls" type="galaxy.datatypes.binary.Xls"
display_in_upload="true" />
     <datatype extension="xlsx" type="galaxy.datatypes.binary.Xlsx"
display_in_upload="true" />

and the relevant additions to binary.py are:

     class Xls(Binary):
         '''Class describing an Excel 2003 (xls) file'''

         file_ext='xls'

     class Xlsx(Binary):
         '''Class describing an Excel 2007 (xlsx) file'''

         file_ext='xlsx'

Any suggestions appreciated.

Thanks, Patrick

___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client.  To manage your subscriptions to this
and other Galaxy lists, please use the interface at:
   http://lists.bx.psu.edu/

To search Galaxy mailing lists use the unified search at:
   http://galaxyproject.org/search/mailinglists/