--- title: "Charts and images" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Charts and images} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r setup, include = FALSE} knitr::opts_chunk$set(collapse = TRUE, comment = "#>") library(writexl) sales <- data.frame(quarter = c("Q1", "Q2", "Q3", "Q4"), revenue = c(120, 145, 133, 180), cost = c(90, 110, 105, 130)) ``` ## A chart `xl_chart()` puts a chart on a sheet; `xl_chart_series()` says what it plots. ```{r} 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. ### Naming a range 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: ```{r} xl_chart_series(values = list(sheet = "Sales", cols = "revenue", rows = 1:2)) ``` 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)`. ### Styling Charts reuse the format objects from [Formatting cells](b-formatting.html). `xl_border()` becomes the line, `xl_fill()` the fill or pattern, `xl_font()` the text: ```{r} 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)) ``` 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. ## Axes `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: ```{r} 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"))) ``` 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. ## The parts of a series 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: ```{r} 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_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: ```{r} xl_chart("pie", xl_chart_series(values = list(cols = "revenue"), points = list(xl_fill(background = "red"), NULL, NULL, NULL))) ``` 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. ## The chart as a whole ```{r} 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_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. ## A chart on a sheet of its own `xl_chartsheet()` is a tab holding one chart and no cells: ```{r} 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. ## Images `xl_image()` places a picture, anchored to a cell: ```{r eval = isTRUE(capabilities("png"))} card <- as.raster(matrix(c("red", "blue", "green", "gold"), nrow = 2)) xl_sheet(sales, image = xl_image(card, at = "G2", scale = 20)) ``` 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: ```{r eval = FALSE} 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.