Write from R into template in excel while preserving formatting

Question

I have a dataframe's in R which want to write to excel (or csv) and to output in a nice format (e.g. with a border, title for the table, not starting from the cell A1).

At the moment I use the function write.table to write my dataframes into a csv file, and then I copy and paste my data into a document in excel where I have my tables ready formatted as template.

This is not a major issue when it is only one dataframe, but I now want to do it for multiple dataframes and have these across multiple tabs in excel.

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

As Joran said, 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)

Before

After

---

EDIT : As noted by Dirk Eddelbuettel, you can do the same using the xlsx package. I personally use XLConnect as it can handle both xls and xlsx, and seemed a lot more stable than any of the old packages I used for manipulating EXCEL files. I haven't used the xlsx package yet. You can take a look at the CRAN page on Data Import/Export to know what is available.