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?

CSV files are text-only. You cannot include any formatting. You can use [R (D)COM](http://cran.r-project.org/contrib/extra/dcom/) to write to and format Excel files from R. — Joshua Ulrich, Jun 27 '12 at 14:54
@joran The question contains an important part : keeping the formatting of the original template. Go try that... — Joris Meys, Jun 27 '12 at 15:26
@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, Jun 27 '12 at 15:43

Joris Meys · Accepted Answer · 2012-06-28T12:37:39.323

25

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.

edited Jun 28 '12 at 12:37

answered Jun 27 '12 at 15:24

Joris Meys

106,551
31
221
263

3

FWIW the "xslx" package does that too. – Dirk Eddelbuettel Jun 27 '12 at 15:58
Thanks joris, much appreciated! – user1165199 Jun 27 '12 at 16:04
7

@DirkEddelbuettel; How do we do this in using xlsx package? I did not find any function that can save the formatting of an entire sheet from being preserved? – name_masked Jul 20 '12 at 18:49
4

I've done quite a bit of writing from `R` to `.xlsx` files and prefer the `openxlsx` package much more than `xlsx`. My reasoning is that I always had troubles getting my OS (64-bit) to work easily with `rjava` (a dependency of `xlsx`). `openxlx` doesn't require `rJava` which made my life--at the time, at least--much easier. – Steven May 25 '16 at 16:20
openxlsx works great for 99% of the time but some 3rd party programs I use reject the openxlsx xlsx files and claim they cannot read the excel document. If I open the excel file, then save and retry it always works. Anyone know a workaround for this? – Michael Cantrall Oct 04 '18 at 18:02
@DirkEddelbuettel, how to do it in `xlsx` package? – pajonk Apr 01 '20 at 08:01

score 2 · Answer 2 · answered Apr 19 '21 at 22:27

2

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.

answered Apr 19 '21 at 22:27

allanvc

1,096
10
23

LMc · Answer 3 · 2022-06-10T20:44:14.387

Here is a function that is based on openxlsx where you specify the workbook (from_wb) and sheet name/location (from_sheet) that has the styles and the workbook (to_wb) and sheet name/location (to_sheet) to which you want to transfer the style:

Note: I used the purrr and glue packages as well, but this could be re-written in base R.

copyStyle <- function(from_wb, to_wb, from_sheet, to_sheet) {
  # check for workbook objects
  if (!(inherits(from_wb, "Workbook") && inherits(to_wb, "Workbook"))) { 
    stop("from_wb and to_wb must be Workbook objects.")
    }
  
  # get all sheet names from workbooks
  from_sheets <- from_wb$sheet_names
  to_sheets <- to_wb$sheet_names

  # convert sheets from numeric to sheet name. wb$styleObjects uses sheet name
  if (is.numeric(from_sheet)) {
      from_sheet <- from_wb$getSheetName(from_sheet)
  }
  
  if (is.numeric(to_sheet)) { 
      to_sheet <- to_wb$getSheetName(to_sheet)
  }

  # if sheet name given check that it exists
  if (is.character(from_sheet) && !from_sheet %in% from_sheets) {
    stop(glue::glue("{from_sheet} was not found in from_wb"))
  }
  
  if (is.character(to_sheet) && !to_sheet %in% to_sheets) {
    stop(glue::glue("{to_sheet} was not found in to_wb"))
  }
  
  # get from_wb sheet styles
  from_styles <- purrr::keep(from_wb$styleObjects, ~ .x$sheet == from_sheet)

  # add styles to to_wb
  purrr::walk(from_styles, ~ openxlsx::addStyle(to_wb, 
                                                to_sheet, 
                                                .x$style, 
                                                rows = .x$rows, 
                                                cols = .x$cols))
  return(to_wb)
}

Usage

library(openxlsx)

wb <- loadWorkbook("getStyle.xlsx")

new_wb <- write.xlsx(head(iris), "transfer_style.xlsx")
# can give sheet name or index
copyStyle(from_wb = wb, to_wb = new_wb, from_sheet = "Sheet1", to_sheet = 1)

# must save workbook after copying style
saveWorkbook(new_wb, "transfer_style.xlsx", overwrite = T)

Input

getStyles.xlsx is a local Excel workbook with multiple styles on several sheets:

Output

transfer_styles.xlsx:

Write from R into template in excel while preserving formatting

3 Answers3

Linked