Copy all sheet of a workbook to another file

Question

i would like to do the following steps to all my .xlsx:

Copy first sheet
Paste the copied sheet to another file
Rename the pasted sheet as the name of the file
1. Repeat step 1 and copy she sheet in the same file of step 2 repeat step 3

Can i do this steps with R? my .xlsx contein img and the cells are formatted.

THANKS

have a look at the `openxlsx` package. – ottlngr Feb 06 '17 at 12:15 — ottlngr, Feb 06 '17 at 12:15

score 1 · Accepted Answer · answered Sep 01 '21 at 13:47

Here is another approach based on RDCOMClient :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
path_Excel_File1 <- "C:\\...\\File1.xlsx"
path_Excel_File2 <- "C:\\...\\File2.xlsx"
xlWbk1 <- xlApp$Workbooks()$Open(path_Excel_File1)
xlWbk2 <- xlApp$Workbooks()$Open(path_Excel_File2)
nb_Sheets <- xlWbk1$Sheets()$Count()

for(l in 1 : nb_Sheets)
{
  wbSheet <- xlWbk1$Worksheets(l)$Copy(after = xlWbk2$Worksheets(l))
}

xlWbk2$SaveAs("C:\\...\\FileMerged12.xlsx")
xlWbk2$Close()
xlApp$Quit()

score 0 · Answer 2 · answered Feb 06 '17 at 12:15

0

Your answer lies in using the xlsx package.

This link gives a good guide

http://www.sthda.com/english/wiki/r-xlsx-package-a-quick-start-guide-to-manipulate-excel-files-in-r

answered Feb 06 '17 at 12:15

Frost_Maggot

309
2
12

This merhod doesn't work, it create a sheet with list of my file. – Matteo M Feb 06 '17 at 14:27

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

Using the XLConnect package (which requires Java). This answer is motivated by the method in another answer on this site.

file <- "Book1.xlsx"

# install.packages("XLConnect")
library(XLConnect)

wb <- loadWorkbook(file)    
sheets <- readWorksheet(wb,getSheets(wb))

for(x in getSheets(wb)){
    path <- paste0(x,".xlsx")
    out <- loadWorkbook(path,create = TRUE)
    createSheet(out,x)
    writeWorksheet(out,sheets[[x]],x)
    saveWorkbook(out)
}

edited May 23 '17 at 11:53

Community

1
1

answered Feb 06 '17 at 13:27

Therkel

1,379
1
16
31

I am not sure my procedure of creating two answers is correct. The two answers differ in their methods (and packages used), why I created two answers. If the correct approach is to append both versions in the same answer, let me know and I will do so. – Therkel Feb 06 '17 at 13:29
My sheet contein layout and img, this script doesn't import all my element. – Matteo M Feb 06 '17 at 14:29
I very much doubt this can be done in R as it is for data handling and not file handling. I suggest you edit your question to elaborate that your sheets contain layout and images. – Therkel Feb 06 '17 at 14:42

score -1 · Answer 4 · answered Feb 06 '17 at 13:02

Using the gdata package (which requires Perl). The files are here written to .csv-files. From gdata we use the function sheetNames to get all sheet names and extract them with read.xls. The files are created in the working directory.

file <- "Book1.xlsx"  ## File to extract from

# install.packages("gdata")
library(gdata)
sheetnames <- sheetNames(file)

sheets <- lapply(setNames(sheetnames,sheetnames),read.xls,xls = file)

for(x in sheetnames){
    write.csv(sheets[[x]],paste0(x,".csv"),row.names = FALSE)
}

Copy all sheet of a workbook to another file

4 Answers4

Linked