Write from R into template in excel while preserving formatting

Question

I want to write a dataframe in R to excel (or csv) and output it in a good way (e.g. with a border, and title for the table, not starting from cell A1).

Currently, I export my dataframes to a CSV file using the function write. table, and then I copy and paste the data into an Excel document with ready-made templates for my tables.

When there is only one dataframe, this is not a big deal, but I want to do it for numerous dataframes and have them on different Excel tabs.

Is there a way in which I can automatically copy my dataframes to specific cells in an existing excel spreadsheet with all the formatting correctly set?

Answer 1

you have the XLConnect package. Read the documentation or the vignette of that package carefully to know exactly what is possible.

Using XLConnect you normally overwrite the cell styles unless you set the style action to be "none" using.

setStyleAction(wb,XLC$"STYLE_ACTION.NONE")

To set you on the right road, a trivial example :

require(XLConnect)
wb <- loadWorkbook("test.xlsx", create=TRUE)
setStyleAction(wb,XLC$"STYLE_ACTION.NONE")

Data <- data.frame(
  a = 1:10,
  b = letters[1:10]
)

writeWorksheet(wb,Data,"aSheet",startRow=1,startCol=1,header=TRUE)

saveWorkbook(wb)