Allow parallelization of getSheetNames

[sgrote]

Is your feature request related to a problem? Please describe.
It does not seem to be possible to run openxlsx::getSheetNames in parallel. Here's my code:

getSheetNames <- function(excel_files, parallel = FALSE, BPPARAM = BiocParallel::bpparam()) {
  if (parallel) {
    sheet_list <- BiocParallel::bplapply(excel_files,
      FUN = function(excel_file) {
        if (!file.exists(excel_file)) {
          return(NULL)
        }
        openxlsx::getSheetNames(excel_file)
      },
      BPPARAM = BPPARAM
    )
  } else {
    sheet_list <- lapply(excel_files,
      FUN = function(excel_file) {
        if (!file.exists(excel_file)) {
          warning("File '", excel_file, "' could not be found.")
          return(NULL)
        }
        openxlsx::getSheetNames(excel_file)
      }
    )
  }
  names(sheet_list) <- excel_files
  sheet_list
}

Now, getSheetNames(excel_files, parallel = TRUE) sometimes throws an error

 Error: BiocParallel errors
  element index: 1
  first error: cannot open file '/tmp/Rtmpxttndw/_excelXMLRead/[Content_Types].xml': No such file or directory 

or

Error: BiocParallel errors
  element index: 1
  first error: cannot open the connection 

Describe the solution you'd like
My guess would be that each of the parallel tasks writes the same temporary file.
Maybe using a directory or a file based on tempfile() would be a solution.

Describe alternatives you've considered
Parallelization does work with readxl::excel_sheets, but that is much slower than openxlsx::getSheetNames.
Another alternative is to not run it in parallel, but with a growing number of excel files this will be more time consuming.

[jmbarbone]

I'm not familiar with BiocParallel but I don't have an issue trying to read with the parallel package and the current dev version of openxlsx

library(openxlsx)
library(parallel)
packageVersion("openxlsx")
#> [1] '4.2.4.9000'

files <- sapply(1:10, function(x) tempfile(fileext = ".xlsx"))
invisible(sapply(files, function(x) {
  write.xlsx(list(a = data.frame(1)), x)
}))

cl <- makeCluster(2)
res <- parSapply(cl, files, getSheetNames)
stopCluster(cl)

names(res) <- basename(names(res))
res
#> filed028503a5e45.xlsx filed0286ced491a.xlsx filed0285d1e260b.xlsx 
#>                   "a"                   "a"                   "a" 
#> filed0283073600e.xlsx filed02835787e93.xlsx filed0281e6a4f69.xlsx 
#>                   "a"                   "a"                   "a" 
#> filed028396510da.xlsx filed0283efb25a2.xlsx filed028127869b2.xlsx 
#>                   "a"                   "a"                   "a" 
#> filed028696535d4.xlsx 
#>                   "a"
all(file.remove(files))
#> [1] TRUE

^{Created on 2021-09-27 by the reprex package (v2.0.1)}

There is a line in getSheetNames() that creates a temporary directory:

openxlsx/R/wrappers.R

Lines 3401 to 3404 in 0cc668b

	xmlDir <- file.path(tempdir(), "_excelXMLRead")
	xmlFiles <- unzip(file, exdir = xmlDir)
	on.exit(unlink(xmlDir, recursive = TRUE), add = TRUE)

Changing this could work, but I haven't been able to get the same issues.

[sgrote]

Hi Jordan,
thanks for the quick reply and fix! Your example seemed to work because makeCluster uses the default type = "PSOCK".
Using a fork cluster instead, leads to the same issues as in my example above:

library(openxlsx)
library(parallel)

files <- sapply(1:10, function(x) tempfile(fileext = ".xlsx"))
invisible(sapply(files, function(x) {
  write.xlsx(list(a = data.frame(1)), x)
}))

cl <- makeCluster(4, type = "FORK")
res <- parSapply(cl, files, getSheetNames)
# Error in checkForRemoteErrors(val) : 
#  3 nodes produced errors; first error: cannot open file '/tmp/RtmpjScHsw/_excelXMLRead/_rels/.rels': No such file or directory

stopCluster(cl)

The issue of using the same tempdir() in forked processes is also discussed e.g. here.

This demonstrates that only the first version uses distinct tempdirs in parallel processes:

# parallel::makeCluster PSOCK
cl <- parallel::makeCluster(2, type = "PSOCK")
res <- parallel::parSapply(cl, 1:4, tempdir)
parallel::stopCluster(cl)
res
# [1] "/tmp/RtmpTMgTqX" "/tmp/RtmpTMgTqX" "/tmp/RtmpF6A2EO" "/tmp/RtmpF6A2EO"

# parallel::makeCluster FORK
cl <- parallel::makeCluster(2, type = "FORK")
res <- parallel::parSapply(cl, 1:4, tempdir)
parallel::stopCluster(cl)
res
# [1] "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z"

# parallel::mclapply
res <- parallel::mclapply(
 1:4, tempdir
)
unlist(res)
# [1] "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z"

# BiocParallel
BiocParallel::register(BiocParallel::MulticoreParam(2))
res <- BiocParallel::bplapply(
 1:4, tempdir
)
unlist(res)
# [1] "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z" "/tmp/RtmpoEql3Z"

So while using parallel::makeCluster(..., type = "PSOCK") clearly can be used for parallelization of getSheetNames,
I think it would still be great to use tempfile() instead of tempdir() to also allow parallelization with other commonly used approaches like parallel::mclapply or BiocParallel::bplapply.
Also, type = "PSOCK" seems to be slower than type = "FORK", at least in my test-case.