2

I have a default workbook with my company logo and formating already in place, in order to quickly generate workbooks to my clients, without having to reformat everything all the time.

I manage to do it for a single sheet. I would like to do it for as many sheets as I need, in a single workbook.

I do it, now, as follows

  wb      = loadWorkbook("O:/R/handle_wb.xlsx") # loads default workbook
  sheets  = getSheets(wb) # get pre-formatted sheet

  # change styles
  cs      = CellStyle(wb) + ...
  # add df of data to excel with the chosen styles
  addDataFrame(data, sheets$Sheet1, startRow = 6, startColumn = 1,
               colnamesStyle = cs, row.names = F)

Then, I would like to generate another sheet in the same wb, but using the preformatted sheet I already have, by writing a function like

  add.sheet <- funtion(newdata, original.wb, default.wb){

          wb      = loadWorkbook(default.wb) # loads default workbook
          sheets  = getSheets(wb) # get pre-formatted sheet
          ob      = loadWorkbook(original.wb) # loads orginal workbook

          # change styles
          cs      = CellStyle(wb) + ...

          # add df of data to excel with the chosen styles
          addDataFrame(newdata, sheets$Sheet1, startRow = 6, startColumn = 1, colnamesStyle = cs, row.names = F)

# create a new sheet in the original workbook to receive the newsheet created above
  createSheet(ob, "sheet2")

  she <- getSheets(ob)
  she$sheet2 <- sheets$Sheet1 # designate to sheet2 the created default sheet

  saveWorkbook(ob, original.wb)

    }

The question is, how do I replace the blank sheet I created in the original workbook with the one in my default workbook, i.e., she$sheet2 <- sheets$Sheet1 ?

lmo
  • 37,904
  • 9
  • 56
  • 69
Felipe Alvarenga
  • 2,572
  • 1
  • 17
  • 36
  • have you found a solution for this problem? – ifreak Jun 26 '18 at 12:11
  • Yes and no. I moved to `openxlsx` package, which was easier to handle, specially by not requiring java for any operation. `openxlsx` allow me to do most of what I wanted, however, I ended up configuring the whole formatting inside R, instead of loading a pre formatted workbook from disk – Felipe Alvarenga Jun 26 '18 at 14:03
  • @FelipeAlvarenga Would you consider answering your own question above with your new `openxlsx` solution to it? I'm sure many would benefit from that. – coip Nov 16 '21 at 18:59

0 Answers0