How to write multiple named sheets to excel workbook using openxlsx

I need to use the openxlsx library due to java issues at my institution.

I have a list of dataframes called data_list that are simply named 1, 2, 3, etc.

> data_list
$`1`
  Count TaxID        Organism.Name
1     1   644 Aeromonas hydrophila

$`2`
   Count TaxID          Organism.Name
1 151263   624        Shigella sonnei
2  97868   644   Aeromonas hydrophila
3  91121  1639 Listeria monocytogenes
4   8352 43767     Rhodococcus hoagii

$`3`
  Count TaxID          Organism.Name
1 92975   644   Aeromonas hydrophila
2 16989   624        Shigella sonnei
3    35  1639 Listeria monocytogenes

However, this dataframe list was created using a file list called file_list. I want to use openxlsx to write multiple tabs to a workbook named by parsing the file name.

file_list <- list.files(path = mypath, pattern = "*.txt")
# Read in data into list of dataframes
data_list <- lapply(file_list, read.delim,quote = "", header = FALSE, col.names = c("Count", "TaxID", "Organism Name"))

I can create a workbook with all the sheets properly named by parsing the file names but no data is written to the sheets.

library(openxlsx)
wb <- createWorkbook()
for (i in seq_along(data_list)) {
  mysheetname <- strsplit(file_list[i], "_")[[1]][1:4]
  mysheetname <- paste(mysheetname, collapse = "_")
  addWorksheet(wb, sheetName = mysheetname)
}
saveWorkbook(wb, "plate2_organismAbundance.xlsx")

I can write the data to multiple sheets but the sheetnames are 1, 2, 3, etc. and not the names I need from parsing my file_list. I know this is happening because the dataframes in data_list are named 1, 2, 3.

write.xlsx(data_list, file = "plate2_organismAbundance.xlsx")

Is there a way to combine my file name parsing with the write.xlsx function to create a multisheet workbook with the sheet names coming from mysheetname in a loop?

Alternatively, I need to know how to rename the dataframes in data_list with the parsed file names created in my loop (mysheetname) which after renaming the dataframes, I would get the correct sheet names when running write.xlsx.

The sheet names would be this after parsing file_list:

"H-T14_Cond_1_T"

"H-T14_Cond_1_U"

"H-T14_Cond_1_R1-T"

Solution

You said you needed to use openxlsx because of issues with java, have you tried writexl? Perhaps something like this:

strsplit(file_list, "_") |>
  sapply(function(st) paste(st[1:4], collapse = "_") |>
  setNames(data_list, nm = _) |>
  writexl::write_xlsx("plate2_organismAbundance.xlsx")

You may also find it convenient to name the list when you read in the data, something like:

data_list <- strsplit(file_list, "_") |>
  sapply(function(st) paste(st, collapse = "_") |>
  setNames(file_list, nm = _) |>
  lapply(read.delim, quote = "", header = FALSE, col.names = c("Count", "TaxID", "Organism Name"))

at which time your data should be named as desired, and you can then simply do

writexl::write_xlsx(data_list, "plate2_organismAbundance.xlsx")