Chapter 11 officer for Excel

officer exposes a set of functions for producing Microsoft Excel (.xlsx) files that mirrors its Word and PowerPoint APIs. A workbook is opened (possibly from a template), sheets are added and populated with tabular or rich-text content, drawings such as images, ggplots, native Microsoft charts or editable vector graphics are anchored on the sheets, and the result is written out with print().

11.1 Building a workbook

Use read_xlsx() to open a workbook. Without argument, officer uses a minimal built-in template that ships a single default sheet; passing a path lets you start from your own template (useful to carry corporate styles, named ranges, etc.).

library(officer)

wb <- read_xlsx()
sheet_names(wb)

# [1] "Feuil1"

Sheets are added with add_sheet(). The function is purely additive: the template’s default sheet is kept as-is, and subsequent calls simply append new sheets. If the default sheet is not wanted, remove it explicitly with sheet_remove():

default_sheet <- sheet_names(wb)[1]

wb <- wb |>
  sheet_remove(default_sheet) |>
  add_sheet("Data") |>
  add_sheet("Charts") |>
  add_sheet("Notes")

sheet_names(wb)

# [1] "Data"   "Charts" "Notes"

The exact name of the default sheet depends on the locale used to build the template ("Sheet1" in English, "Feuil1" in French, etc.); reading it from sheet_names(wb) avoids hard-coding a locale-specific string.

Two more operations complete the sheet lifecycle: sheet_select() chooses which sheet is active when the workbook is opened, and sheet_remove() deletes a sheet.

wb <- sheet_select(wb, "Data")
wb <- sheet_remove(wb, "Notes")
sheet_names(wb)

# [1] "Data"   "Charts"

11.2 Writing cell content

sheet_write_data() is an S3 generic. Its behaviour depends on the class of value: a data.frame writes a tabular range, a character vector writes one cell per element, an fpar() writes a single rich-text cell, and a block_list() stacks several fpar cells. The start_row and start_col arguments (1-based) control where the top-left cell lands.

11.2.1 Data frames

wb <- read_xlsx() |>
  add_sheet("Airquality")

wb <- sheet_write_data(
  wb,
  value = head(airquality, 10),
  sheet = "Airquality",
  start_row = 1, start_col = 1
)

The column headers of the data frame are written on the first row of the range; the body cells use Excel’s default numeric / text formats driven by the column types.

11.2.2 Character vectors

A character value writes one cell per element. The direction argument decides whether the cells are stacked vertically (default) or laid out horizontally on a single row.

wb <- sheet_write_data(
  wb,
  value = c("Alpha", "Beta", "Gamma"),
  sheet = "Airquality",
  start_row = 1, start_col = 8, direction = "vertical"
)

wb <- sheet_write_data(
  wb,
  value = c("North", "South", "East", "West"),
  sheet = "Airquality",
  start_row = 12, start_col = 1, direction = "horizontal"
)

11.2.3 Rich text (`fpar`)

An fpar() produces a single cell whose content is a sequence of runs with their own formatting (bold, italic, underline, strikethrough, colour, font family, size, sub / superscript). This is the Excel equivalent of the inline rich text used in Word.

rich_cell <- fpar(
  ftext("Sales ",     fp_text_lite(bold = TRUE)),
  ftext("up 12% ",    fp_text_lite(color = "#1B9E77")),
  ftext("this quarter", fp_text_lite(italic = TRUE))
)

wb <- sheet_write_data(
  wb,
  value = rich_cell,
  sheet = "Airquality",
  start_row = 14, start_col = 1
)

11.2.4 Stacked rich text (`block_list`)

Wrap several fpar() into a block_list() to write one rich-text cell per item. Cells are stacked vertically by default.

notes <- block_list(
  fpar(ftext("Data source", fp_text_lite(bold = TRUE))),
  fpar("Synthetic airquality example shipped with R."),
  fpar(
    "See ",
    ftext("?airquality", fp_text_lite(italic = TRUE)),
    " for column definitions."
  )
)

wb <- sheet_write_data(
  wb,
  value = notes,
  sheet = "Airquality",
  start_row = 16, start_col = 1
)

11.3 Embedding drawings

sheet_add_drawing() is the S3 generic that anchors visual content on a sheet. Every method shares the same placement contract — left, top, width, height in inches (from the top-left corner of the sheet) — so image, ggplot, Microsoft chart and editable vector graphic anchors are interchangeable.

wb <- add_sheet(wb, "Visuals")

11.3.1 Images (`external_img`)

img_file <- file.path(R.home("doc"), "html", "logo.jpg")

wb <- sheet_add_drawing(
  wb,
  value = external_img(img_file),
  sheet = "Visuals",
  left = 1, top = 1, width = 1.4, height = 1.1
)

external_img() accepts PNG, JPEG, GIF and other common raster formats. The source file must still exist at print() time: the image is pulled in when the workbook is written, not when sheet_add_drawing() is called.

11.3.2 ggplot objects

library(ggplot2)

gg <- ggplot(iris, aes(Sepal.Length, Petal.Length, colour = Species)) +
  geom_point() +
  theme_minimal()

wb <- sheet_add_drawing(
  wb,
  value = gg,
  sheet = "Visuals",
  left = 3, top = 1, width = 5, height = 3,
  res = 300, alt_text = "Iris scatter plot"
)

The ggplot is rendered to PNG via ragg::agg_png() — res controls the output resolution and scale (default 1) the overall size multiplier. alt_text populates the drawing’s alternative text, useful for accessibility.

11.3.3 Native Microsoft charts (`ms_chart`)

A chart built with the ‘mschart’ package is embedded as a true Excel chart (not a picture): the underlying data is written to the sheet and referenced by the chart, so the result is fully editable in Excel.

library(mschart)

barchart <- ms_barchart(
  data = browser_data,
  x = "browser", y = "value", group = "serie"
)

wb <- sheet_add_drawing(
  wb,
  value = barchart,
  sheet = "Visuals",
  start_col = 1, start_row = 10,
  left = 1, top = 3, width = 6, height = 3.5
)

start_col / start_row control where the chart’s source data table is written; the left / top / width / height arguments control where the chart itself appears. Pass write_data = FALSE when several charts should share the same data range.

11.3.4 Editable vector graphics (`dml`)

The ‘rvg’ package lets you embed editable DrawingML graphics — shapes, text and lines remain vector objects in Excel and can be selected or modified individually.

library(rvg)

editable_gg <- dml(ggobj = gg)

wb <- sheet_add_drawing(
  wb,
  value = editable_gg,
  sheet = "Visuals",
  left = 1, top = 7, width = 6, height = 4
)

dml() takes either a base R plot instruction (via code = {...}) or a ggplot object through ggobj. It replaces the deprecated xl_add_vg() helper.

11.4 Exporting the workbook

The workbook is written to an .xlsx file with print():

print(wb, target = "static/office/example_xlsx.xlsx")

The target path is returned invisibly; the workbook object can be written multiple times to different targets if needed.

Known limits at the time of writing:

embedding a flextable directly into an Excel cell range has no first-class path yet; use sheet_add_drawing.gg() on a rendered image as a workaround for static previews;
file paths passed to external_img() must exist at print() time, otherwise the resulting .xlsx will be broken.