mschart (version 0.5.1)

sheet_add_drawing.ms_chart: Add an ms_chart to an Excel sheet

Description

Add an ms_chart object to a sheet in an xlsx workbook created with officer::read_xlsx(). The chart data is written into the sheet and the chart is displayed at the specified position.

Usage

# S3 method for ms_chart
sheet_add_drawing(
  x,
  value,
  sheet,
  start_col = 1L,
  start_row = 1L,
  write_data = TRUE,
  left = 1,
  top = 1,
  width = 6,
  height = 4,
  anchor = NULL,
  edit_as = c("twoCell", "oneCell", "absolute"),
  ...
)

Value

the rxlsx object (invisibly)

Arguments

x

an rxlsx object (created by officer::read_xlsx())

value

an ms_chart object

sheet

sheet name where the chart and its data will be placed. The sheet must already exist (see officer::add_sheet()).

start_col

column index where chart data will be written (default 1, i.e. column A). When write_data = FALSE, this is still the cell position that the chart XML will point at, but no data is written.

start_row

row index where chart data will be written (default 1). Same semantics with write_data = FALSE as for start_col.

write_data

if TRUE (the default), the chart's data_series is written into the sheet at (start_col, start_row) before the chart is added. Pass FALSE when the data is already present in the sheet (for example to avoid rewriting it when several charts share the same dataset, or when inserting a chart that references data written independently via officer::sheet_write_data()). Not to be confused with the constructor's asis argument (see ms_barchart()), which controls how the input data frame is read at construction time. The two are independent.

left, top

top-left anchor of the chart, in inches. Defaults to (1, 1). Used when anchor = NULL. Same convention as officer::sheet_add_drawing() and rvg::sheet_add_drawing.dml().

width, height

size of the chart, in inches. Defaults to 6 x 4. Used when anchor = NULL or when anchor is a single cell reference.

anchor

optional Excel cell-based anchor. Either NULL (the default; absolute placement via left/top/width/height), a single cell reference like "B2" (the chart is anchored to that cell and keeps width/height, i.e. "Move but don't size with cells"), or a range like "B2:H20" (the chart is anchored from the first cell to the second, i.e. Excel's default "Move and size with cells").

edit_as

one of "twoCell", "oneCell", "absolute". Sets the editAs attribute on <xdr:twoCellAnchor>. Ignored unless anchor is a range.

...

unused

Examples

library(officer)
library(mschart)

my_chart <- ms_barchart(
  data = data.frame(
    x = c("A", "B", "C"),
    y = c(1, 3, 2),
    group = rep("serie1", 3)
  ),
  x = "x", y = "y", group = "group"
)

x <- read_xlsx()
x <- add_sheet(x, label = "chart_sheet")
x <- sheet_add_drawing(x, value = my_chart, sheet = "chart_sheet")
print(x, target = tempfile(fileext = ".xlsx"))

# Sharing one dataset between several charts on the same sheet:
# write the data once, then add each chart with write_data = FALSE.
shared <- data.frame(
  x = c("A", "B", "C"),
  y = c(1, 3, 2),
  group = rep("serie1", 3)
)
chart_a <- ms_barchart(shared, x = "x", y = "y", group = "group")
chart_b <- ms_linechart(shared, x = "x", y = "y", group = "group")

x <- read_xlsx()
x <- add_sheet(x, label = "multi")
x <- sheet_write_data(x, value = chart_a$data_series, sheet = "multi")
x <- sheet_add_drawing(x, value = chart_a, sheet = "multi",
                       write_data = FALSE,
                       left = 3, top = 0.5, width = 5, height = 3.5)
x <- sheet_add_drawing(x, value = chart_b, sheet = "multi",
                       write_data = FALSE,
                       left = 9, top = 0.5, width = 5, height = 3.5)
print(x, target = tempfile(fileext = ".xlsx"))