Write from R into template in excel while preserving formatting

30
votes

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?

2
excelr
CSV files are text-only. You cannot include any formatting. You can use R (D)COM to write to and format Excel files from R. – Joshua Ulrich
@joran The question contains an important part : keeping the formatting of the original template. Go try that... – Joris Meys
@JorisMeys Yeah, ?setStyleAction. – joran
@joran I know (see my answer). But if I have to downvote every question that can be answered by scrolling through quite some pages of manual, there wouldn't be many questions left. This is a perfectly valid question. It took a while of using XLConnect before I encountered that feature. It's not in the vignette, and very few help pages link to that specific information. – Joris Meys

2 Answers

24
votes

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

enter image description here

After

enter image description here

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.

0
votes

Some users were asking how to do it using the xlsx package.

There is a very good and extensive usage example of the xlsx package at STHDA.

It has examples for cell formatting, workbooks with multiple sheets, how to add figures, etc.