Can write.xlsx write multiple tabs into a file?

Question

Within R, I am trying to print a series of dataframes into an Excel file using openxlsx. Specifically in this case, I'm using list.files, read.xlsx and write.xlsx.

I'm still unable to write multiple tabs into one Excel file.

Please see my code below, I've tried to approach this problem using a for loop as well as a manual solution to test the feasibility but have had no luck

This is what my code currently looks like. For the length of the file list, pipe each file into a read function which then writes the results.

lapply(
  1:length(file.list), 
  function(x) {
      write.xlsx(
        read.xlsx(file.list[i]), 
        file = file_name, 
        sheetName = file.list[i], 
        col.names = TRUE, 
        row.names = FALSE, 
        append = TRUE)
    }
)

A manual solution below also doesn't seem to have any luck for me either

df1 <- read.xlsx(file.list[1])
write.xlsx(df1, file = file_name, sheetName = file.list[1], col.names = TRUE, row.names = FALSE, append = FALSE)

df2 <- read.xlsx(file.list[2])
write.xlsx(df2, file = file_name, sheetName = file.list[2], col.names = TRUE, row.names = FALSE, append = TRUE)

No error messages so far. The final file does see data being written into it, however, it seems only the last file has the results print. I'm thinking that it's almost a cycle of overwrites,

I don't understand your question very well, but you can manually create a wb and add different sheets (If this is what you mean by tabs). If what you want is to append several pieces of information in one single sheet, it's also possible — Aaron Parrilla, Aug 09 '19 at 14:07
Hi Aaron, I am essentially trying to load multiple files and then add a different sheet within a workbook for each separate file — Grygger, Aug 09 '19 at 14:10

Aaron Parrilla · Accepted Answer · 2019-08-09T14:47:36.593

Maybe you could try this:

wb <- createWorkbook(title = "Your_Workbook_Name")
lapply(1:length(file.list), function(y) lapply(1:length(file.list), function(x) writeData(wb,file.list[i],y,col.names = TRUE, row.names = FALSE, append = TRUE)))

Since I don't have a way to replicate this, perhaps you can understand the main idea behind this.

A double loop, in which your traverse all the files you want to write, before writing it you create a sheet with the name of the index, and then you can write in the newly created sheet, the data you want. I hope it's understandable (My knowledge about lapply and sapply is not the best, but the idea still stands)

score 0 · Answer 2 · answered Apr 16 '21 at 16:28

0

You can simply use a named list of dataframes in write.xlsx. Something like this should work:

library(openxlsx)

df.list <- lapply(file.list, read.xlsx)
named.df.lst <- setNames(df.list, file.list)

write.xlsx( named.df.lst, file = file_name )

answered Apr 16 '21 at 16:28

lucazav

858
9
24

Can write.xlsx write multiple tabs into a file?

2 Answers2