Skip to contents

Add a drawing to an Excel sheet

Source: R/read_xlsx.R
sheet_add_drawing.Rd

Add a graphical element into a sheet of an xlsx workbook. This is a generic function dispatching on value. Supported inputs include:

  • external_img(): an image file (PNG, JPEG, GIF, ...) copied into the workbook and placed on the sheet.

  • gg: a ggplot object rendered to PNG via ragg::agg_png() and embedded via the external_img path. Extra arguments: res (default 300 ppi), alt_text (auto-detected via ggplot2::get_alt_text() when empty), scale (same semantics as ggplot2::ggsave()).

The drawing can be positioned in three ways, matching Excel's own "Format Picture" options:

  • Fixed position (default): pass left / top and width / height in inches. The drawing stays put when rows or columns are inserted/resized.

  • Move and size with cells (Excel's default): pass anchor = "B2:H20" (a cell range). The drawing spans those two cells and follows them.

  • Move but don't size with cells: pass anchor = "B2" (a single cell). The drawing anchors to that cell, keeping the size set by width / height.

Additional methods for ms_chart and dml are provided by the extension packages 'mschart' and 'rvg'.

Use sheet_write_data() to write data into the sheet before or after adding a drawing.

Usage

sheet_add_drawing(x, value, sheet, ...)

Arguments

x

rxlsx object created by read_xlsx()

value

object to add (dispatched to the appropriate method)

sheet

sheet name (must already exist, see add_sheet())

...

method-specific arguments. Common across external_img and gg methods: left / top (fixed-position top-left in inches, default (1, 1)), width / height (size in inches), anchor (cell reference like "B2" or "B2:H20", see description), edit_as (one of "twoCell", "oneCell", "absolute"; how Excel treats the drawing when rows/columns are resized — only meaningful with a cell range anchor). The gg method also accepts res (ppi, default 300), alt_text (auto-detected via ggplot2::get_alt_text() if empty) and scale (passed to ragg::agg_png()).

Value

the rxlsx object (invisibly)

See also

read_xlsx(), add_sheet(), sheet_write_data()

Examples

img <- system.file("extdata", "example.png", package = "officer")
if (nzchar(img) && file.exists(img)) {
  x <- read_xlsx()
  x <- add_sheet(x, label = "pics")
  x <- sheet_add_drawing(
    x, sheet = "pics",
    value = external_img(img, width = 2, height = 2)
  )
  print(x, target = tempfile(fileext = ".xlsx"))
}

if (requireNamespace("ggplot2", quietly = TRUE)) {
  gg <- ggplot2::ggplot(iris, ggplot2::aes(Sepal.Length, Sepal.Width)) +
    ggplot2::geom_point()
  x <- read_xlsx()
  x <- add_sheet(x, label = "plots")
  x <- sheet_add_drawing(x, value = gg, sheet = "plots",
                         width = 4, height = 3)
  print(x, target = tempfile(fileext = ".xlsx"))
}