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,
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)