0

I would like to use the apply family of functions in order to copy each file from one directory to the second sheet of each file in the second directory.

I have tried many things, ended up by making the following work, but only for a single file in the directory. How to apply it to all of the files in that folder?

setwd(".../r_path//390")
l1 <- list.files(pattern='*.xlsx')
r1 = lapply(l1, read.xlsx, sheetIndex=1, header=TRUE)
names(r1) <- l1
s1 = split(r1, names(r1))

setwd(".../r_path//390de")
l2 <- list.files(pattern='*.xlsx')
r2 = lapply(l2, read.xlsx, sheetIndex=1, header=TRUE)
names(r2) <- l2
s2 = split(r2, names(r2))

library(plyr)
library(xlsx)
l_ply (r1[1], function(x) write.xlsx(x, 
               file =paste0(names(s2[[1]])), sheetName = "TECO", 
               append = TRUE, row.names = FALSE))

The first excel file of a directory is copied as a second sheet to another excel file. But I want this to apply to all of the files in the folder. Any advice is welcome!

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
ainour
  • 11
  • 4

2 Answers2

0

From the documentation for XLConnect, the easiest way to do what you want is to first create the workbook (using loadWorkbook()) then for each worksheet you want to create, use createSheet() to create the worksheet within the workbook, and then write the data into the worksheet you've created using writeWorksheet() with the workbook and worksheet objects you're created. At the end of the loop, call saveWorkbook() to write the results out to the Excel file. The process looks something like the following:

# Create a workbook (if it doesn't currently exist it will be created)
wb <- loadWorkbook("myexcelfile.xlsx",create=TRUE)
# Create a worksheet within the workbook
createSheet(wb,name="worksheet1")
# Write a dataframe called data_to_write to the new worksheet
writeWorksheet(wb,data_to_write,sheet="worksheet1")
# Write the Excel file
saveWorkbook(wb)

Wrapping a loop around the worksheet section for your problem is straightforward.

Stephen McNeill
  • 1
0

Here is my solution:

for (i in names(r1)) {
l_ply (r1[i], function(x) write.xlsx(x,file =paste0(names(s2[[i]])),
 sheetName = "TECO", append = TRUE, row.names = FALSE))}
ainour
  • 11
  • 4