Feature Request: Clone Worksheet

[ksak3]

This would be awesome to have for copying of template sheets before writing. Thank you!

[debarros]

I was playing around with this, and hit an interesting error. I have a workbook that I read in wb1 = loadWorkbook("scores10.xlsx") I then extracted the sheet I want x = wb1$worksheets[[8]] I then tried to make a copy of it y = x$copy() That generated the error

Error in lapply(x, function(x) { : 
  argument "oddHeader" is missing, with no default

I am going to try writing something that (1) pulls the formatting info, (2) calls addWorksheet with that info, (3) pulls the data with read.xlsx, and (4) calls writeData on the new worksheet. I'm not sure that will get everything, but maybe it will be a start.

[debarros]

The following function did not work. I am guessing that I am missing some stuff. I tried loading the package sample workbook, running the function to copy the IrisSample tab, and saving the workbook. It generated an error when I tried to open it (something about removing the "Slicer"), and the copied tab had none of the formatting of the original tab.

copyWorksheet = function (wb, sheetName, newSheetName){
  wb$addWorksheet(newSheetName)
  n = which(wb$sheet_names == newSheetName)
  old = which(wb$sheet_names == sheetName)
  wb$worksheets[[n]]$autoFilter <- wb$worksheets[[old]]$autoFilter
  wb$worksheets[[n]]$colBreaks <- wb$worksheets[[old]]$colBreaks
  wb$worksheets[[n]]$cols <- wb$worksheets[[old]]$cols
  wb$worksheets[[n]]$conditionalFormatting <- wb$worksheets[[old]]$conditionalFormatting
  wb$worksheets[[n]]$dimension <- wb$worksheets[[old]]$dimension
  wb$worksheets[[n]]$drawing <- wb$worksheets[[old]]$drawing
  wb$worksheets[[n]]$extLst <- wb$worksheets[[old]]$extLst
  wb$worksheets[[n]]$freezePane <- wb$worksheets[[old]]$freezePane
  wb$worksheets[[n]]$legacyDrawing <- wb$worksheets[[old]]$legacyDrawing
  wb$worksheets[[n]]$legacyDrawingHF <- wb$worksheets[[old]]$legacyDrawingHF
  wb$worksheets[[n]]$oleObjects <- wb$worksheets[[old]]$oleObjects
  wb$worksheets[[n]]$pageMargins <- wb$worksheets[[old]]$pageMargins
  wb$worksheets[[n]]$pageSetup <- wb$worksheets[[old]]$pageSetup
  wb$worksheets[[n]]$rowBreaks <- wb$worksheets[[old]]$rowBreaks
  wb$worksheets[[n]]$sheetFormatPr <- wb$worksheets[[old]]$sheetFormatPr
  wb$worksheets[[n]]$sheetPr <- wb$worksheets[[old]]$sheetPr
  wb$worksheets[[n]]$sheetViews <- wb$worksheets[[old]]$sheetViews
  wb$worksheets[[n]]$tableParts <- wb$worksheets[[old]]$tableParts
  df = read.xlsx(wb, sheetName)
  writeData(wb = wb, x = df, sheet = newSheetName)
}

## load existing workbook from package folder
wb <- loadWorkbook(file = system.file("loadExample.xlsx", package= "openxlsx"))
names(wb)  #list worksheets
## Duplicate a worksheet
copyWorksheet(wb, "IrisSample","IrisSample2")
## Save workbook
saveWorkbook(wb, "loadExample.xlsx", overwrite = TRUE)

[dovrosenberg]

Are there plans to pull @kainhofer's work into the package? While not 100%, it does cover many use cases. Thanks!

[cryptobiotic]

@debarros Did you ever figure out how to get this to work?? I have tried copying a number of the underlying objects but when i go to save the wb i get 'attempt to select less than one element in get1index' I'm trying to combine workbooks with very unique formatting and i'm stuck. "I am going to try writing something that (1) pulls the formatting info, (2) calls addWorksheet with that info, (3) pulls the data with read.xlsx, and (4) calls writeData on the new worksheet. I'm not sure that will get everything, but maybe it will be a start."

[debarros]

@cryptobiotic No, I don't think I ever did get it to work. I'll have to go back and check the project I was working on at the time.