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 xl/media/ and placed via an absolute anchor. Position and size are inch-based (left, top, width, height).

  • 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()).

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. In particular, the external_img and gg methods accept left / top (top-left anchor, inches, default (1, 1)) and width / height (size, inches). 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"))
}