Hi David,

We're checking on this for you.

According to our docs, this does look like it is text-specific, so an XLS fileout might not be possible with things out of the box:

https://www.labkey.org/home/Documentation/wiki-page.view?name=substitutionParameters

However, I'm wondering whether something like the WriteXLS package might be able to pull this off instead.

https://cran.r-project.org/web/packages/WriteXLS/WriteXLS.pdf

We'll get back to you once we have more information.

Thanks for your patience.

Regards,

Jon

Hi David,

If you are already using the xlsx package to generate the excel file from your R script, you should still be able to use the fileout substitution to make the download available to your users as an excel file. Would something like this work?

require(xlsx)
write.xlsx(labkey.data, "${fileout:myExcelFile.xlsx}")

thanks, Karl

Thank you Jon and Karl,

I tried Karl's suggestion and recieved the following errors:

When I tried:

require(xlsx)
write.xlsx(labkey.data, "${fileout:myExcelFile.xlsx}")

I get:

Error executing command
javax.script.ScriptException: javax.script.ScriptException: An error occurred when running the script 'script.R', exit code: 1).
Loading required package: xlsx
Loading required package: rJava
Error : .onLoad failed in loadNamespace() for 'rJava', details:
  call: fun(libname, pkgname)
  error: JAVA_HOME cannot be determined from the Registry
In addition: Warning messages:
1: package 'xlsx' was built under R version 3.2.3
2: package 'rJava' was built under R version 3.2.3
Failed with error: 'package 'rJava' could not be loaded'
Error: could not find function "write.xlsx"
Execution halted

then tried:

require(xlsx)
require(rJava)
write.xlsx(labkey.data, "${fileout:myExcelFile.xlsx}")

and got:

Error executing command
javax.script.ScriptException: javax.script.ScriptException: An error occurred when running the script 'script.R', exit code: 1).
Loading required package: xlsx
Loading required package: rJava
Error : .onLoad failed in loadNamespace() for 'rJava', details:
  call: fun(libname, pkgname)
  error: JAVA_HOME cannot be determined from the Registry
In addition: Warning messages:
1: package 'xlsx' was built under R version 3.2.3
2: package 'rJava' was built under R version 3.2.3
Failed with error: 'package 'rJava' could not be loaded'
Loading required package: rJava
Error : .onLoad failed in loadNamespace() for 'rJava', details:
  call: fun(libname, pkgname)
  error: JAVA_HOME cannot be determined from the Registry
In addition: Warning message:
package 'rJava' was built under R version 3.2.3
Error: could not find function "write.xlsx"
Execution halted



David

Hi David,

Looks like the JAVA_HOME environmental variable in Windows isn't configured according to the error.

Can you go into your Environmental Variables in Windows and check to make sure you have it there?

You should be able to get to this by going into the Control Panel > System > Advanced System Settings > Environmental Variables

If it isn't there, you should be able to add JAVA_HOME there as a new variable and point it to where you have Java installed (probably something like C:\Program Files\Java\jdk1.8 or something to that effect).

Regards,

Jon

Hi David,

In case it wasn't apparent, that's an environment variable on the web server itself, where the script is running. A site administrator can check if it's set in a way that LabKey Server can see it by going to Admin->Site->Admin Console->Environment Variables. This can be a quick way to check if it's set at all. Someone would need to configure it directly on the web server and then restart Tomcat to get it to pick up the change.

Thanks,
Josh

Thanks Jon and Josh,

We updated the enviromental variable and that fixed that error, but now when I run

require(xlsx)
write.xlsx(labkey.data, "${fileout:myExcelFile.xlsx}")

I get the following message:

Failed to retrieve report results

see SS attached.

David

Hi David,

Are you still doing a write.table in your R-Builder Report View as well, like the default that comes up like this one?

write.table(labkey.data, file = "${tsvout:tsvfile}", sep = "\t", qmethod = "double", col.names=NA)

Regards,

Jon

Hey Jon,

Sorry for the delay, just got back from vacation.

This is what I ran in the R-Report View that resulted in the previous error.

require(xlsx)
write.xlsx(labkey.data, "${fileout:myExcelFile.xlsx}")

David

Hi David,

I've used this method successfully in a few scripts without any issues.

The following snippet should give and attachment output, TSV output, and console output as shown in the image:

require(xlsx)
write.xlsx(labkey.data, file = "${fileout:labkeyData.xlsx}")
write.table(labkey.data, file = "${tsvout:tsvfile}", sep = "\t", qmethod = "double", col.names=NA)

Alright so I was able to tease out more info by running this as a pipeline job.

So when I run

    require(xlsx)
    write.xlsx(labkey.data, file = "${fileout:labkeyData.xlsx}")

This is what I get

13 Sep 2016 16:43:10,566 INFO : Running R report job 'Test'
13 Sep 2016 16:57:03,225 ERROR: Error occurred running the report background job
javax.script.ScriptException: javax.script.ScriptException: javax.script.ScriptException: An error occurred when running the script 'script.R', exit code: 1).
Loading required package: xlsx
Loading required package: rJava
Loading required package: xlsxjars
Warning messages:
1: package 'xlsx' was built under R version 3.2.3
2: package 'rJava' was built under R version 3.2.3
3: package 'xlsxjars' was built under R version 3.2.3
Error in .jcheck(silent = FALSE) :
  Java Exception <no description because toString() failed>.jcall(row[[ir]], "Lorg/apache/poi/ss/usermodel/Cell;", "createCell", as.integer(colIndex[ic] - 1))<S4 object of class "jobjRef">
Calls: write.xlsx ... .write_block -> createCell -> .jcall -> .jcheck -> .Call
Execution halted
Caused by: javax.script.ScriptException: javax.script.ScriptException: An error occurred when running the script 'script.R', exit code: 1).

Hi David,

Can you check to make sure the call is working correctly in your R environment on the server?

From an R console, you should be able to test with something simple like this:

require(xlsx)
write.xlsx(mtcars,file="mtcars.xlsx")

I ended up creating a new dataframe with a limited amount of columns and that worked. There must be a column in labkey.data that is throwing the error. No matter though, I can export the columns I need so I am all good.

Thanks for all the help.