The xlsx package

I ran into problems very early on with this package. The installation was dependent on the rJava package which gave an error during installation. Given that we’re using a Unix platform, the fix to this was to run sudo apt-get install r-cran-rjava in console. Executing this command succesfully installed the package and after restarting R-studio I was able to install and load the xlsx package.

The next problem I ran into was a java out of memory error, specifically java.lang.OutOfMemoryError. This time the fix was allocating more memory to Java trough the options(java.parameters = "-Xmx40000m") setting. The memory increase amount (and therefore the amount of data that can be written) is dependent on the amount of memory your computer can allocate to R. In this case I have allocated 40gig, which most of us do not have laying around. Once this was done I was able write up to 10 worksheets without any problems, but again, I ran into memory problems when I tried to scale up.

#---- Create function performing xlsx tasks ----#

Checkxlsx <- function ( num, sample.matrix ) {

  wb <- xlsx::createWorkbook(type = "xlsx")

  for ( i in 1:length(num) ) {
  
    sheet.i = paste0("sheet", i)
    sheet <- xlsx::createSheet(wb, sheet.i)
    xlsx::addDataFrame(x = sample.matrix, sheet = sheet, col.names = FALSE, row.names = FALSE) 
  }

  xlsx::saveWorkbook(wb, "xlsx_test.xlsx")
}

The XLConnect package

This package had all the same problems as the previous with one added extra. During testing, the writing process was done many times; each time it was done the previous .xlsx file saved as the same name needed to be deleted. If this wasn’t done R would continue attempting to write to the file indefinitely with no indications of stopping. There seems to be a fix for this using the createNames() function but it didn’t seem worth the effort.

#---- Create function performing XLConnect tasks ----# 

CheckXLConnect <- function ( num, sample.matrix ) {

  wb <- XLConnect::loadWorkbook("XLConnect_test.xlsx")
  sapply(num, function(x) XLConnect::createSheet(wb, x))

  for ( i in 1:length(num) ) {
    XLConnect::writeWorksheet(wb, sample.matrix, as.character(i), header = FALSE)
  }
  
  XLConnect::saveWorkbook(wb)
}

The WriteXLS package

This package was easy enough to use with no apparent errors. The problem was that because this method writes the data directly into a workbook, instead of first creating it locally within the R environment, it was extremely slow.

# Name list of dataframes to be used to name sheets
names(sample.matrix.list) <- num

# Create list of dataframes
sample.matrix.list <- lapply(seq_len(as.numeric(num)), function(X) sample.matrix)

# Write list of dataframes to xlsx
WriteXLS(sample.matrix.list, "sample_matrix2.xlsx", col.names = FALSE)

The openxlsx package

Finally, I looked to the opnexlsx package. Again, no problems here; the most user friendly of the four.

#---- Create function performing openxlsx tasks ----#

CheckOpenxlsx <- function ( num, sample.matrix ) {
    
  wb <- openxlsx::createWorkbook()
  sapply(num, function(x) openxlsx::addWorksheet(wb, x))

  for ( i in 1:length(num) ) {
    openxlsx::writeData(wb, i, sample.matrix, colNames = FALSE)
  }

  openxlsx::saveWorkbook(wb, file = "openxlsx_test.xlsx", overwrite = TRUE)
}