xl_chart() puts a chart on a sheet;
xl_chart_series() says what it plots.
sheet <- xl_sheet(sales,
chart = xl_chart("column",
xl_chart_series(values = list(cols = "revenue")),
title = "Revenue by quarter", at = "E2"))
path <- write_xlsx(list(Sales = sheet), tempfile(fileext = ".xlsx"))All 22 types Excel offers are available: "column",
"bar", "line", "pie",
"doughnut", "area", "scatter",
"radar", and the stacked, percent-stacked, smoothed and
marker variants.
A series range is written either by column,
list(cols = "revenue"), or as A1 text,
"Data!B2:B5". Prefer the column form: it is checked against
the data frame, moves with the column rather than with a letter, and
accounts for the header row itself. rows narrows it, and
sheet points at another sheet:
A range reaching past the data is an error rather than a chart with blanks on the end — a chart of the wrong numbers looks plausible, which is the worst way to be wrong.
A series that plots a column is named after that column’s
header, which is what Excel does when you chart a column along
with its header. The header is referenced, not copied, so renaming it in
Excel renames the series. name = FALSE leaves it unnamed,
and a string is always taken literally — a series may legitimately be
called "Q1!" — so to take a name from a cell, give a spec:
list(header = "cost"), or
list(rows = 1, cols = 1).
Charts reuse the format objects from Formatting cells. xl_border()
becomes the line, xl_fill() the fill or pattern,
xl_font() the text:
xl_chart("line",
xl_chart_series(values = list(cols = "revenue"),
format = xl_border(all = "dashed", color = "#4472C4")),
title = "Revenue", title_format = xl_font(size = 14, bold = TRUE))
#> <xl_chart: line, 1 series, "Revenue">Each part takes the groups it can use and refuses the rest by
name. A series is a shape, so it takes a line and a fill but no
font; a title is text, so it takes a font and nothing else.
xl_fill() and xl_border() also take
transparency, which charts honour and cells ignore, and
chart lines have fewer dash styles than cell borders because a chart
line has no width. None of this is dropped quietly.
style picks one of Excel’s 48 built-in chart styles.
xl_chart_axis() covers everything an axis has — the
title and its font, tick labels with their number format, bounds, a log
scale, tick marks and their spacing, gridlines, and the axis line
itself:
xl_chart("column", xl_chart_series(values = list(cols = "revenue")),
x_axis = xl_chart_axis(title = "Quarter", major_tick = "none"),
y_axis = xl_chart_axis(title = "Revenue", min = 0,
num_format = "$#,##0",
major_gridlines_format =
xl_border(all = "dotted", color = "gray")))
#> <xl_chart: column, 1 series>There are two kinds of axis and several options belong to one of them. A scatter chart plots numbers against numbers, so both its axes are value axes; every other type has a category x axis and a value y axis — bar charts included, since Excel draws their categories up the side but does not rename the axes.
| value axes | category axes |
|---|---|
min, max, log_base,
major_unit, minor_unit,
display_units, display_units_visible |
position, label_align,
interval_unit, interval_tick |
Setting a minimum on a category axis is accepted by Excel and then ignored, so writexl refuses it and names the axis that would work. Pie and doughnut charts have no axes at all.
A series can carry a marker at each point, labels beside them, a trendline through them and error bars on them, and individual points can be styled:
xl_chart_series(
values = list(cols = "revenue"),
marker = xl_chart_marker(type = "circle", size = 7),
labels = xl_chart_labels(num_format = "$#,##0", position = "above"),
trendline = xl_chart_trendline("linear", equation = TRUE),
y_error_bars = xl_chart_error_bars("percentage", 5))
#> <xl_chart_series>xl_chart_labels() with no arguments prints each point’s
value, which is Excel’s default. Naming any part means the label holds
exactly those, so show_percentage = TRUE
alone gives a percentage with no value beside it. custom =
takes a list of xl_chart_label()s, one per point, so a
single label can be renamed or removed with hide = TRUE.
points = takes a list of formats the same way — one red
slice, the rest untouched:
xl_chart("pie", xl_chart_series(values = list(cols = "revenue"),
points = list(xl_fill(background = "red"),
NULL, NULL, NULL)))
#> <xl_chart: pie, 1 series>Four more of Excel’s silent rules are checked here: which label positions each chart type allows, that a moving average has no forecast, equation or R-squared, that an intercept applies only to exponential, linear and polynomial fits, and that an automatic marker takes neither a size nor a format.
xl_chart("column", xl_chart_series(values = list(cols = "revenue")),
legend = xl_chart_legend(position = "bottom"),
data_table = xl_chart_table(show_keys = TRUE),
plot_area_format = xl_fill(background = "#F8F8F8"),
chart_area_format = xl_border(all = "thin", color = "gray"))
#> <xl_chart: column, 1 series>xl_chart_legend(position = "none") removes the legend,
and delete_series leaves one series out of the key while
still plotting it. Anything placed by hand takes a layout
rather than an at — title_layout,
plot_area_layout,
xl_chart_axis(title_layout =) and
xl_chart_legend(layout =) are fractions of the chart, where
at means a cell everywhere else. show_blanks
decides what an empty cell does, and show_hidden_data plots
rows Excel would leave out.
Six options belong to one family of chart: the doughnut
hole_size, pie rotation,
drop_lines, high_low_lines,
up_down_bars, and the bar series_gap and
series_overlap. Each takes TRUE for Excel’s
own drawing or a format to style it, and each is refused on a type that
has no such feature.
A scatter series must have categories — they are its x
axis, and libxlsxwriter crashes without them.
xl_chartsheet() is a tab holding one chart and no
cells:
chart <- xl_chart("column",
xl_chart_series(values = list(sheet = "Data",
cols = "revenue"),
categories = list(sheet = "Data",
cols = "quarter")),
title = "Revenue")
path <- write_xlsx(list(Data = sales, Overview = xl_chartsheet(chart)),
tempfile(fileext = ".xlsx"))Every range must name its sheet, since a chartsheet has no cells for
list(cols = ) to resolve against. It supports a fraction of
what a worksheet does — of xl_page_setup() the orientation,
paper, margins, header and footer; of xl_sheet_view() the
four tab-state options — plus tab_color, zoom
and protect. The rest is refused rather than dropped.
xl_image() places a picture, anchored to a cell:
card <- as.raster(matrix(c("red", "blue", "green", "gold"), nrow = 2))
xl_sheet(sales, image = xl_image(card, at = "G2", scale = 20))
#> <xl_sheet: 4 rows x 3 cols>The image may be a file path, a raw
vector of encoded bytes, or an in-memory
picture — a raster, a colour matrix, an RGB array
or a nativeRaster. Anything rasterImage() can
draw can be written, so a plot never has to touch the disk; that path
uses R’s PNG device, which is why grDevices is a suggested
package.
PNG, JPEG, GIF and BMP are supported, and the format is read from the
file’s own bytes rather than its extension, so a mislabelled
.png is caught rather than misfiled. scale,
offset and position control size and
placement, description and decorative set the
alt text, and url makes the image a hyperlink.
An image can also go in the printed header or footer, matched to a
&G placeholder, or be tiled behind the cells as a
screen watermark:
xl_page_setup(header = "&L&G", header_image = list(left = logo))
xl_sheet(sales, background_image = logo)embed = TRUE puts the image inside a cell, the
Excel 365 “place in cell” feature; older Excel shows
#VALUE! in its place.
Two combinations are refused, because libxlsxwriter miscounts them and Excel repairs the file: an embedded image alongside any other image, and a header/footer or background image on a sheet before one with a floating image or a chart. Both errors name the sheets and the arrangement that works.