1

I have been researching this issue for a few days now and so far none of the offered fixes have worked for me I am hoping someone will be able to help.

The Setup:

I am working on a project where the final step is to take an Rstudio dataset and export it into an excel workbook with different sections of data on separate sheets with appropriate names. I have tried working from one major dataset and splitting to sheets and I have also tried splitting the dataset into the different subsets of data and creating sheets from those. (I have included this information in case it is relevant but I don't think the issue stems from this) I seem to get through the splitting step without issue but when I try to write the sheets to an .xlsx file I get a zipping up workbook failed error. For obvious reasons all names have been changed to something more generic but I hope the intent still comes through.

##Setting up the list Option 1
require(openxlsx)
wbCluster <- list(
  "Server1" = Server1
  , "Server2" = Server2
  , "Server3" = Server3
  , "Server4" = Server4
  , "Server5" = Server5
  , "Server6" = Server6
  , "Server7" = Server7
  , "Server8" = Server8
  , "Server9" = Server9
  , "Server10" = Server10
  , "Server11" = Server11
  , "Server12" = Server12
  , "Server13" = Server13
  , "Server14" = Server14
  , "Server15" = Server15
  , "Server16" = Server16
  , "Server17" = Server17
  , "AllServers"  = datadump
)

Option 2:
require(openxlsx)
wbCluster <- list(
  "Server1" = subset(ReadyforExportServers, server == "Server1")
  , "Server2" = subset(ReadyforExportServers, server == "Server2")
  , "Server3" = subset(ReadyforExportServers, server == "Server3")
  , "Server4" = subset(ReadyforExportServers, server == "Server4")
  , "Server5" = subset(ReadyforExportServers, server == "Server5")
  , "Server6" = subset(ReadyforExportServers, server == "Server6")
  , "Server7" = subset(ReadyforExportServers, server == "Server7")
  , "Server8" = subset(ReadyforExportServers, server == "Server8")
  , "Server9" = subset(ReadyforExportServers, server == "Server9")
  , "Server10" = subset(ReadyforExportServers, server == "Server10")
  , "Server11" = subset(ReadyforExportServers, server == "Server11")
  , "Server12" = subset(ReadyforExportServers, server == "Server12")
  , "Server13" = subset(ReadyforExportServers, server == "Server13")
  , "Server14" = subset(ReadyforExportServers, server == "Server14")
  , "Server15" = subset(ReadyforExportServers, server == "Server15")
  , "Server16" = subset(ReadyforExportServers, server == "Server16")
  , "Server17" = subset(ReadyforExportServers, server == "Server17")
  , "AllServers"  = datadump 
)

The code to the left of the equal sign is what I want the sheet name to be and the code to the right is the dataset in rStudio I am working off of. In Option 1 it is where I made a different dataset in R for each sheet I want and in Option 2 this is where I am selecting from the same base dataset and grabbing different cuts of the data for the sheets.

I get through this section either way with out issue but when I try to write the wbCluster to an .xlsx file I run into problems.

I have tried both:

write.xlsx(wbCluster, file = "2_9_2018.xlsx", overwrite = TRUE)
    ##and 
write.xlsx(wbCluster, file = "2_9_2018.xlsx")

and I get the same error each time.

Error: zipping up workbook failed. Please make sure Rtools is installed or a 
zip application is available to R.
     Try installr::install.rtools() on Windows.

Which when I run gives me the following:

> installr::install.rtools()
No need to install Rtools - You've got the relevant version of Rtools 
installed

I am working with - R version: 3.3.2 - rStudio version: 1.1.414 - openxlsx version: 3.0.0 - rTools version: 34

I have tried uninstalling rTools and reinstalling it with the box checked as recommended in https://github.com/awalker89/openxlsx/issues/111 . But that didn't resolve the error.

I have tried installing from github 

devtools::install_github("awalker89/openxlsx")

in case there was a newer version available that wasn't on CRAN yet as recommended in https://github.com/awalker89/openxlsx/issues/164 . But that didn't resolve the error either.

I also tried a few variations on the Sys.getenv as recommended in Error: zipping up workbook failed when trying to write.xlsx (With running the command to write to .xlsx after each potential fix but only getting the same error each time). 

Sys.setenv(R_ZIPCMD = "C:/Rtools/bin/zip.exe")
Sys.setenv("R_ZIPCMD" = "path/to/zip.exe")

But it still doesn't appear in my 

 Sys.getenv("PATH")

If anyone can help me with this issue it would be so appreciated as I am really out of ideas for ways to fix this.

M. Pope
  • 41
  • 3
  • Have you tried adding Rtools to your system path by hand? – Steven Feb 12 '18 at 17:12
  • Can you elaborate? I want to make sure I am understanding. - @Steven – M. Pope Feb 12 '18 at 18:27
  • You can add edit your system path by hand. That way, you're not relying on an automated process to update your `PATH` to include the path to `Rtools`. [Here's a write up on how to do it.](https://www.java.com/en/download/help/path.xml) – Steven Feb 12 '18 at 22:06
  • @Steven Thank you for the link it was extremely helpful! and when I look at that path it has `C:\RBuildTools\3.4\bin;C:\RBuildTools\3.4\mingw_32\bin` right at the beginning of the path but when I execute `Sys.getenv("PATH")` in RStudio it doesn't have any rTools or rBuildTools in the output. If it is in the PATH should it be returning from that Code? Or is there something I am missing/not understanding? – M. Pope Feb 13 '18 at 19:00
  • based on [link](https://github.com/stan-dev/rstan/wiki/Install-Rtools-for-Windows) I thought RbuildTools and Rtools are equivalent. Let me know if that is incorrect. – M. Pope Feb 13 '18 at 19:03
  • I have no idea if they are equivalent. When you run `devtools::find_rtools()`, do you get a positive response? If yes, I don't know what to tell you. If not, I suggest uninstalling/resinstalling RStudio and `Rtools`. Beyond that, I am at a loss. – Steven Feb 14 '18 at 17:33
  • @Steven that code returns TRUE for me. and yeah this is the barrier I was running into. Every "solution" I find doesn't seem to solve the issue. I am not sure if I am focusing on the wrong section which is why I put so much detail in it. From what you are seeing does it seem like a Rtools error or do you think it would be the package or something else? – M. Pope Feb 14 '18 at 18:15
  • My guess that it's not an `Rtools` error, nor is it an `openxlsx` error. I've used both with great success. I suspect that it's an installation problem, hence my solution to uninstall then reinstall. – Steven Feb 14 '18 at 18:16

0 Answers0