Formatting and workbook properties

writexl writes plain data frames with sensible defaults, but you can also control formatting at three levels that share one vocabulary:

• cell formatting, attached to columns with xl_cell_general();
• worksheet layout, attached to a sheet with xl_sheet();
• workbook defaults and metadata, attached with xl_workbook() / xl_properties().

All three reuse the same format engine, so once you know how to build an xl_format, you can apply it anywhere.

The format engine

A format is built from group constructors, each covering one family of Excel properties. Every argument defaults to “unset”, so you only specify what you want to change.

xl_font(bold = TRUE, color = "navy", size = 12)
#> <xl_format>
#>   font: size=12, color=128, bold=TRUE
xl_fill(background = "#FFF2CC")
#> <xl_format>
#>   fill: background=16773836, pattern=solid
xl_border(all = "thin", color = "gray")
#> <xl_format>
#>   border: left=thin, right=thin, top=thin, bottom=thin, left_color=12500670, right_color=12500670, top_color=12500670, bottom_color=12500670
xl_align(horizontal = "center", vertical = "top", wrap = TRUE)
#> <xl_format>
#>   align: horizontal=center, vertical=top, wrap=TRUE
xl_num_format("#,##0.00")
#> <xl_format>
#>   num_format: format=#,##0.00
xl_protection(locked = FALSE)
#> <xl_format>
#>   protection: locked=FALSE

Colors accept R color names ("navy"), hex strings ("#FF0000"), or integers, via xl_color().

Each group constructor already returns a complete xl_format, so a single group can be used on its own. Combine groups with + (or xl_format()), which merges property by property — later values win, but nothing you did not touch is lost:

fmt <- xl_font(bold = TRUE) + xl_fill(background = "yellow") + xl_num_format("0.0%")
fmt
#> <xl_format>
#>   font: bold=TRUE
#>   fill: background=16776960, pattern=solid
#>   num_format: format=0.0%

Cell formatting

Attach a format to a column of values with xl_cell_general(). Pass one format for the whole column, or a list of formats for individual cells.

df <- data.frame(item = c("Widget", "Gadget", "Gizmo"))
df$price <- xl_cell_general(
  value  = c(1234.5, 67.25, 890),
  format = xl_num_format("$#,##0.00") + xl_font(color = "darkgreen")
)
df$flag <- xl_cell_general(
  value  = c(10, -5, 3),
  format = list(
    xl_fill(background = "lightgreen"),
    xl_fill(background = "salmon"),
    xl_fill(background = "lightgreen")
  )
)
path <- write_xlsx(df, tempfile(fileext = ".xlsx"))

xl_formula() and xl_hyperlink_cell() also take a format argument. When a cell holds a date or time and its format sets no number format, the default date/time format is applied automatically.

Worksheet layout

xl_sheet() wraps a data frame with column and row specifications and sheet-level options. xl_col_spec() and xl_row_spec() are themselves xl_format objects (so they combine with +) that additionally carry a target and geometry.

sheet <- xl_sheet(
  data.frame(
    date    = as.Date("2024-01-01") + 0:2,
    revenue = c(1000.5, 2000.25, 1500.75)
  ),
  cols = list(
    xl_col_spec("date", width = 12),
    xl_col_spec("revenue", width = 14,
                format = xl_num_format("#,##0.00") + xl_font(bold = TRUE))
  ),
  rows      = xl_row_spec(1, height = 20),
  freeze    = "A2",          # freeze the header row
  tab_color = "steelblue",
  zoom      = 120
)
path <- write_xlsx(list(Sales = sheet), tempfile(fileext = ".xlsx"))

xl_sheet() also adds autofilters and worksheet protection:

locked <- xl_sheet(
  data.frame(id = 1:3, note = c("a", "b", "c")),
  autofilter = TRUE,                       # or a range like "A1:B4"
  protect    = list(password = "secret",   # protect, but still allow sorting
                    sort = TRUE)
)
path <- write_xlsx(list(Locked = locked), tempfile(fileext = ".xlsx"))

Cell locking set with xl_protection() only takes effect once the sheet is protected.

Set auto_colwidth = TRUE to size every column to its contents (a character-count estimate, since the xlsx format has no true “AutoFit”); columns given an explicit width via xl_col_spec() are left as-is.

Geometry can be given in pixels instead of Excel’s character units and points, which is easier to reason about when sizing a column around an image or a fixed layout. Give one unit or the other, not both:

xl_col_spec("thumbnail", width_pixels = 100)
#> <xl_col_spec>
#>   target: kind=col, index=thumbnail 
#>   geometry: width=13.57143
xl_row_spec(1, height_pixels = 40)
#> <xl_row_spec>
#>   target: kind=row, index=1 
#>   geometry: height=30

Excel stores character units and points, so the value is converted on the way in — a column set to 100 pixels reads back as 13.57 character units, and renders at 100 pixels again.

Grouping and outlines

level in xl_col_spec() / xl_row_spec() puts columns or rows into an outline group, which Excel draws with the +/- controls that collapse it. xl_outline() changes how those controls are drawn — never which rows are grouped — and its defaults already match Excel’s, so it is rarely needed:

grouped <- xl_sheet(
  data.frame(q1 = 1:2, q2 = 3:4, total = 4:5),
  cols    = list(xl_col_spec(c("q1", "q2"), level = 1)),
  outline = xl_outline(symbols_right = FALSE)   # summary column on the left
)
path <- write_xlsx(list(Grouped = grouped), tempfile(fileext = ".xlsx"))

Silencing Excel’s error indicators

Excel puts a green triangle in cells it thinks are wrong — most often a number deliberately stored as text. ignore_errors turns that off for a range, one entry per error type:

codes <- xl_sheet(
  data.frame(zip = c("01234", "02138")),
  ignore_errors = list(number_stored_as_text = "A2:A3")
)
path <- write_xlsx(list(Codes = codes), tempfile(fileext = ".xlsx"))

The range takes any spelling the other range arguments accept, so list(cols = "zip") would cover the whole column. The available types are number_stored_as_text, eval_error, formula_differs, formula_range, formula_unlocked, empty_cell_reference, list_data_validation, calculated_column and two_digit_text_year.

Columns can be targeted by name or position. A plain data frame passed alongside an xl_sheet behaves exactly as before:

path <- write_xlsx(list(
  Styled = sheet,
  Raw    = data.frame(a = 1:3, b = letters[1:3])
), tempfile(fileext = ".xlsx"))

Workbook defaults and metadata

xl_workbook() binds sheets to xl_properties(), which holds document metadata plus the formatting defaults that would otherwise be built in. Because those defaults are ordinary xl_format objects, you can change them — for example, restyle the header row or set a workbook-wide default font.

wb <- xl_workbook(
  list(Report = data.frame(quarter = c("Q1", "Q2"), sales = c(12000, 15000))),
  properties = xl_properties(
    title  = "Quarterly Report",
    author = "Finance Team",
    company = "Example Corp",
    custom = list(Project = "Alpha", Reviewed = TRUE),
    default_format = xl_font(name = "Calibri"),
    header_format  = xl_font(bold = TRUE, color = "white") +
                     xl_fill(background = "navy"),
    read_only = TRUE
  )
)
path <- write_xlsx(wb, tempfile(fileext = ".xlsx"))

Formats cascade from the workbook down: the effective format for a cell is default_format merged with the column/row/header/hyperlink default, then with the cell’s own format. Identical resulting formats are written once, so applying the same format to thousands of cells stays compact.

Note that a workbook-wide default_format is emulated (it is applied beneath every cell) because the underlying library has no native “Normal style” setter; it does not change Excel’s built-in Normal style.

Comments

Cell comments (notes) are attached through xl_cell_general()’s comment argument. The simplest form is just text — a character vector, one comment per cell (NA for none):

df <- data.frame(item = c("Widget", "Gadget", "Gizmo"))
df$item <- xl_cell_general(
  value   = c("Widget", "Gadget", "Gizmo"),
  comment = c("Discontinued", NA, "Low stock")
)
path <- write_xlsx(df, tempfile(fileext = ".xlsx"))

xl_comment() adds options — an author, initial visibility, and box size/position — and styling. Excel comment boxes support only a background color and a font name/size/family, so styling is supplied by reusing the format engine via format; any other format property triggers a warning.

note <- xl_comment(
  "Please verify with Finance",
  author  = "Reviewer",
  visible = TRUE,
  format  = xl_font(name = "Arial", size = 10) + xl_fill(background = "lightyellow")
)
d <- data.frame(x = 1)
d$x <- xl_cell_general(value = 1234.5, comment = note)
path <- write_xlsx(d, tempfile(fileext = ".xlsx"))

A default author, and showing all of a sheet’s comments at once, are set on xl_sheet() (comment defaults are worksheet-scoped in Excel). A comment’s own author overrides the sheet default, and visible overrides show_comments:

sheet <- xl_sheet(df, comment_author = "QA", show_comments = TRUE)
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

Page setup and printing

xl_page_setup() collects everything on Excel’s “Page Layout” ribbon. Pass it as xl_sheet(page = ). None of it touches the cell data — it changes only how the sheet prints and how it looks in page-break preview.

sheet <- xl_sheet(df, page = xl_page_setup(
  orientation = "landscape",
  paper       = "A4",
  margins     = c(left = 0.5, right = 0.5),
  fit_to      = c(width = 1, height = 0),   # one page wide, any number tall
  header      = "&LQuarterly report&RPage &P of &N"
))
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

margins takes one value for all four sides, four values in the order left, right, top, bottom, or a named vector naming only the sides you care about. paper takes a friendly name or an Excel paper-type integer for the envelope and specialist sizes.

fit_to scales the printout to a number of pages, where 0 means “as many as needed in that direction”. scale sets a plain percentage instead; Excel ignores it when fit_to is set.

Headers and footers

Header and footer text uses Excel’s own codes: &L, &C and &R start the left, centre and right sections, &P is the page number, &N the page count, &D the date, &A the sheet name, and && a literal ampersand. The whole string is limited to 255 characters.

xl_page_setup(header = "&LDraft&CQ1&R&D", footer = "&CPage &P of &N")
#> <xl_page_setup: 2 settings>
#>   header: &LDraft&CQ1&R&D
#>   footer: &CPage &P of &N

Header and footer images are not supported yet, so an &G or &[Picture] placeholder is rejected.

Repeating headings and choosing what prints

sheet <- xl_sheet(df, page = xl_page_setup(
  print_area  = "A1:D50",
  repeat_rows = 1,          # repeat the header row on every page
  repeat_cols = "A:A"       # and the first column
))

print_area takes any range writexl accepts, including a list(rows = , cols = ) spec. repeat_rows and repeat_cols take either a count or a range string.

Note that these count sheet rows from 1, with the header row included — unlike xl_row_spec(), which indexes data rows. Repeating the header row is the usual reason to use them, so it has to be nameable.

Manual page breaks

h_breaks and v_breaks give 1-based positions at which a new page starts, so h_breaks = 21 breaks between rows 20 and 21.

xl_page_setup(h_breaks = c(21, 41), v_breaks = "D")
#> <xl_page_setup: 2 settings>
#>   h_breaks: 20, 40
#>   v_breaks: 3

Excel ignores manual breaks when fit_to is set, and writexl warns if you ask for both.

Sheet tabs and the opening view

xl_sheet() also controls the tab strip and what the user sees when the sheet opens.

wb <- list(
  Summary = xl_sheet(df, view = xl_sheet_view(active = TRUE, selection = "B2")),
  Detail  = xl_sheet(df, view = xl_sheet_view(hide_zero = TRUE)),
  Working = xl_sheet(df, view = xl_sheet_view(visible = FALSE))     # hidden tab
)
path <- write_xlsx(wb, tempfile(fileext = ".xlsx"))

active picks the tab Excel opens on, selected adds a tab to the selected group, visible = FALSE hides one, and first_tab chooses the leftmost tab in the strip.

These are the only worksheet settings whose rules span the whole workbook, and writexl checks them before writing anything:

• a hidden sheet cannot be active or selected;
• at most one sheet may be active;
• the first sheet cannot be hidden unless another is made active, because Excel opens on the first sheet by default;
• at least one sheet must stay visible, or Excel refuses to open the file.

Each failure names the sheet at fault. libxlsxwriter enforces none of them, so without these checks a bad combination would produce a workbook that simply does not open.

Where the sheet is scrolled to

selection sets the selected cell or range, and top_left the cell scrolled to the top-left of the window:

xl_sheet(df, view = xl_sheet_view(selection = "B2:D10", top_left = "A2"))
#> <xl_sheet: 3 rows x 1 cols>

Freezing and splitting

freeze locks rows and columns in place; split gives a movable divider with independent scroll bars instead. They are mutually exclusive.

xl_sheet(df, freeze = "A2")   # keep the header row visible
#> <xl_sheet: 3 rows x 1 cols>
#>   freeze: A2
xl_sheet(df, view = xl_sheet_view(split  = "B3"))   # split above row 3 and left of column B
#> <xl_sheet: 3 rows x 1 cols>

Note that libxlsxwriter positions a split by distance, in row-height and column-width units, rather than by row and column — 15 means one default-height row, 8.43 one default-width column. writexl converts the cell reference for you using the sheet’s actual row heights and column widths, so a split still lands where you asked on a sheet whose rows or columns have been resized. Pass list(vertical = , horizontal = ) if you would rather give the units directly.

Merged cells

xl_merge() merges a rectangle of cells into one, as Excel’s “Merge and Centre” does. Pass one or a list of them as xl_sheet(merge = ).

sheet <- xl_sheet(df, merge = xl_merge(
  "A5:B5", "Total",
  format = xl_align(horizontal = "center") + xl_font(bold = TRUE)
))
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

A merged range holds a single value, so xl_merge() carries its own text rather than taking it from the data frame. Merges are applied after the sheet’s rows are written, so merging over cells the data frame filled keeps only the merged text — exactly as merging in Excel discards everything but the top-left value.

Excel has no single-cell merge, so a range covering one cell is an error.

Because a merge writes back over rows already emitted, any merge turns off the memory-efficient row-streaming mode for the whole workbook. That is invisible apart from a slightly different internal string storage, but it does mean the whole sheet is held in memory while writing.

Data validation

xl_validation() restricts what may be typed into a range, and pairs with xl_sheet(validation = ).

sheet <- xl_sheet(df, validation = list(
  xl_validation("A2:A4", type = "integer", min = 1, max = 10,
                error_message = "Enter a whole number from 1 to 10"),
  xl_validation("B2:B4", list = c("open", "high", "close"))
))
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

Excel has seventeen internal validation types; writexl exposes five kinds — "integer", "decimal", "date", "time" and "length" — plus list, "custom" and "any", and works out the rest from what you pass. A limit that is a string beginning "=" is a formula, and a Date or POSIXct is a date/time bound:

xl_validation("C2:C4", type = "integer", criteria = ">", value = "=$Z$1")
#> <xl_validation: integer on C2:C4>
xl_validation("D2:D4", type = "date", criteria = ">=",
              value = as.Date("2024-01-01"))
#> <xl_validation: date on D2:D4>

criteria is required for the five comparison kinds and must be omitted for list, "custom" and "any", which carry their own meaning. Giving both min and max implies "between", so the criteria can be left out there too.

input_title / input_message show a tooltip when the cell is selected, and error_title / error_message appear on a bad entry; error_type chooses whether Excel refuses the entry ("stop", the default) or merely warns. Excel limits titles to 32 characters and messages to 255, and a dropdown’s choices to 255 characters once joined by commas — put longer lists in a range and pass a "=..." formula instead.

Conditional formatting

Four constructors cover Excel’s conditional formatting, and all of them go into xl_sheet(conditional = ):

sheet <- xl_sheet(df, conditional = list(
  xl_cond_cell("A2:A4", criteria = ">", value = 2,
               format = xl_fill(background = "#FFC7CE")),
  xl_cond_scale("B2:B4", colors = c("red", "yellow", "green"))
))
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

xl_cond_cell() pairs a condition with a format. The condition can be a comparison, a text match, a time period, above/below average, top/bottom N, duplicates, uniques, blanks, errors, or an arbitrary formula:

xl_cond_cell("A2:A100", criteria = "contains", value = "urgent")
#> <xl_conditional: cell on A2:A100>
xl_cond_cell("A2:A100", type = "duplicate", format = xl_font(bold = TRUE))
#> <xl_conditional: cell on A2:A100>
xl_cond_cell("A2:A100", type = "formula", value = "=$B2>$C2")
#> <xl_conditional: cell on A2:A100>

Each rule type accepts its own set of criteria — text criteria only make sense for a text rule, time-period criteria only for a time rule. Pairing them wrongly is an error, because Excel would accept the file and then silently ignore the rule. Where the two are redundant the type is inferred, so criteria = "contains" alone is enough.

A rule’s format is an ordinary xl_format, but Excel stores it as a differential format rather than a cell style, so only the properties that make sense as an override are used.

Scales, bars and icons

xl_cond_scale("B2:B100", colors = c("white", "steelblue"))  # two-colour
#> <xl_conditional: scale on B2:B100>
xl_cond_bar("C2:C100", color = "steelblue", solid = TRUE)
#> <xl_conditional: bar on C2:C100>
xl_cond_icons("D2:D100", style = "3_traffic_lights")
#> <xl_conditional: icons on D2:D100>

xl_cond_scale() takes two or three colours, low to high, optionally with the values and rule types marking where each sits. xl_cond_bar() draws in-cell data bars, with control over the negative and border colours, the axis and the direction. xl_cond_icons() selects one of Excel’s seventeen built-in icon sets — these are drawn by Excel itself, so nothing is embedded in the file.

Filtering

xl_sheet(autofilter = TRUE) adds the filter dropdowns. xl_filter() goes further and sets the criteria on a column:

fruit <- data.frame(
  fruit = c("apple", "banana", "cherry", "damson", "elderberry"),
  qty   = c(5, 150, 20, 300, 75),
  stringsAsFactors = FALSE
)
sheet <- xl_sheet(fruit, filter = xl_filter("qty", ">", 100))
path <- write_xlsx(list(Data = sheet), tempfile(fileext = ".xlsx"))

That file opens showing only banana and damson.

Why writexl hides the rows

Excel stores a filter’s criteria and its hidden rows as two independent things, and it does not apply a filter when a file is opened. A file carrying criteria alone therefore opens with the funnel icon showing “greater than 100” and every row still visible — it looks filtered and is not.

So xl_filter() does both: it writes the criteria, and it hides the rows the criteria excludes. That means writexl has to reproduce Excel’s own matching rules, which were established by writing criteria with no hidden rows, opening the file and pressing Data → Reapply so that Excel computed the match itself.

Two kinds of filter

The measurements showed that the rules are not a property of the criteria on its own. A filter is written in one of two forms, and Excel matches them differently:

Form	Written for	Excel matches
value list	"==" without a wildcard, "blanks", any list	the text the cell displays, case-insensitively
typed comparison	everything else, including "==" with a wildcard	by type — numbers to numbers, text to text

A value list ignores how the value was stored, so all three of these keep the number 10 and the string "10":

xl_filter("qty", "==", 10)
#> <xl_filter: qty == 10>
xl_filter("qty", "==", "10")
#> <xl_filter: qty == 10>
xl_filter("qty", list = "10")
#> <xl_filter: qty in 1 value(s)>

A typed comparison does not. On a numeric column "==" with "1*" keeps nothing at all, because no number is text — even though "==" with "10" keeps the number 10. The wildcard is the only difference, and it changes the form:

xl_filter("qty", "==", "1*")   # typed: matches no number
#> <xl_filter: qty == 1*>
xl_filter("qty", "==", "10")   # value list: matches the number 10
#> <xl_filter: qty == 10>

Within a typed comparison, a text cell is never equal to a number — which makes "!=" true for it. So xl_filter("fruit", "!=", 10) keeps every row of a text column.

The rest is more predictable: text comparison is case-insensitive, * and ? are wildcards, and blank covers an empty cell as well as an empty string, with "non-blanks" its exact complement. Blanks are dropped by every comparison save one — "!=" keeps them, since a blank is not equal to anything.

xl_filter("fruit", "==", "ap*")                  # wildcard, case-insensitive
#> <xl_filter: fruit == ap*>
xl_filter("fruit", list = c("apple", "cherry"))  # keep only these
#> <xl_filter: fruit in 2 value(s)>
xl_filter("qty", ">", 100, "<", 200)             # two rules, combined with and
#> <xl_filter: qty > 100>
xl_filter("fruit", "non-blanks")
#> <xl_filter: fruit non-blanks NULL>

Filters on different columns combine with AND, as they do in Excel.

Mixed columns, and what cannot be filtered

A column built with xl_cell_general() may hold different types in different rows, and each cell is matched by its own type:

mixed <- data.frame(row = 1:5)
mixed$v <- xl_cell_general(value = list(10, "20", "abc", 30, 20))

Here xl_filter("v", ">", 15) keeps rows 4 and 5 — the numbers 30 and 20 — but not the text "20" in row 2. A value list works the other way: because it matches displayed text, xl_filter("v", list = "20") keeps both row 2 and row 5.

Asking without writing

The matching rule is available on its own, so you can see which rows a filter would leave visible without writing a file:

sales <- data.frame(fruit = c("apple", "banana", "cherry"),
                    qty = c(5, 150, 300))
xl_filter_keep(sales, xl_filter("qty", ">", 100))
#> [1] FALSE  TRUE  TRUE
sales[xl_filter_keep(sales, xl_filter("fruit", "==", "*a*")), ]
#>    fruit qty
#> 1  apple   5
#> 2 banana 150

xl_sheet(filter =) uses this same function to decide what to hide, so the two cannot disagree.

Two things are refused rather than guessed at. A column of formulas cannot be filtered, because writexl does not know what Excel would compute. And a date column cannot be filtered by "==" or list: those match displayed text, which depends on the number format, so use a comparison such as ">=" instead. The same caution applies to a numeric column carrying a custom number format — writexl compares the General rendering of the number, so a value list may not match what Excel shows.

Tables

xl_table() turns a range into an Excel table: a named, styled block with banded rows, a filter dropdown in its header, and structured references in formulas.

sales <- data.frame(fruit = c("apple", "banana", "cherry"),
                    qty   = c(5, 150, 300))
sheet <- xl_sheet(sales, table = xl_table(style = "medium 9"))
path <- write_xlsx(list(Sales = sheet), tempfile(fileext = ".xlsx"))

The header captions come from the data frame’s column names. That is worth knowing rather than taking for granted: Excel stores a table’s column names separately from the header cells and refuses to open a file where the two disagree, so the default is what makes a table safe to add at all. Override one with xl_table_column() and both sides change together.

Styles are a type and a number — "light 0" to "light 21", "medium 1" to "medium 28", "dark 1" to "dark 11" — or "none" for an unstyled table.

Totals and formulas

A total row is added below the data, with each column choosing a function or a label:

totalled <- xl_sheet(sales, table = xl_table(
  total_row = TRUE,
  columns = list(xl_table_column("fruit", total_label = "Total"),
                 xl_table_column("qty", total = "sum"))
))
path <- write_xlsx(list(Sales = totalled), tempfile(fileext = ".xlsx"))

The total row is written by Excel’s own machinery and the sheet’s row plan does not see it: auto_colwidth does not measure it, and an xl_row_spec() aimed at that row will fight it.

A column formula usually uses a structured reference, which names the table — so give the table a name when you write one:

projected <- sales
projected$doubled <- NA_real_
sheet <- xl_sheet(projected, table = xl_table(
  name = "Sales",
  columns = xl_table_column("doubled",
                            formula = "=Sales[[#This Row],[qty]]*2")
))
path <- write_xlsx(list(Sales = sheet), tempfile(fileext = ".xlsx"))

Left to itself, xl_table() generates a name from the sheet name and writes it explicitly, so it cannot shift when a table is added elsewhere. name = NA leaves the naming to Excel (Table1, Table2, …), which is fine until a formula refers to one — writexl warns if you combine the two.

What a table will not sit alongside

Excel silently repairs each of these, so writexl refuses them instead: a table overlapping xl_sheet(autofilter =) (the table brings its own dropdown), an xl_filter() on a column the table covers, a merged range inside the table, and two overlapping tables. Each error quotes the ranges involved.

Any table also turns off the memory-efficient row-streaming mode, which libxlsxwriter refuses to combine with tables at all.

Images

xl_image() places a picture on a sheet, anchored to a cell:

logo <- system.file("help", "figures", "logo.png", package = "writexl")

sheet <- xl_sheet(data.frame(product = c("a", "b")),
                  image = xl_image(logo, at = "D2", scale = 0.5))
path <- write_xlsx(list(Catalogue = sheet), tempfile(fileext = ".xlsx"))

The image can 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:

card <- as.raster(matrix(c("red", "blue", "green", "gold"), nrow = 2))
xl_image(card, at = "B2", scale = 20)
#> <xl_image: <png, 95 bytes> at B2>

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. Encoding an in-memory picture uses R’s PNG device, which is why grDevices and graphics are suggested packages; if they are unavailable the error tells you how to encode it yourself.

scale, offset and position control size and placement, description and decorative set the alt text screen readers use, and url makes the image a hyperlink.

Headers, footers and backgrounds

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&CQuarterly report",
              header_image = list(left = logo))

A background is a display-only backdrop — Excel never prints it. Header and footer images appear only in Page Layout view and Print Preview, not in the normal grid.

Embedded images, and what cannot be combined

embed = TRUE puts the image inside a cell, so it sizes with the 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 then repairs the file and drops the image:

• an embedded image in a workbook that holds any other image;
• a header/footer or background image on a sheet before one with an ordinary floating image.

Both errors name the sheets and the arrangement that works. Put the sheets with floating images first, and give embedded images a workbook of their own.

Only embedded images cost the memory-efficient row-streaming mode; ordinary floating images leave it on, so a large sheet with a logo stays fast.