Getting started with writexl

One data frame

path <- write_xlsx(iris, tempfile(fileext = ".xlsx"))

write_xlsx() returns the path it wrote, so it composes with anything that takes a file name.

Several sheets

A named list becomes one sheet per element, and the names become the tabs:

path <- write_xlsx(list(Flowers = iris, Cars = mtcars[1:5, 1:4]),
                   tempfile(fileext = ".xlsx"))

Sheet names are repaired rather than rejected: Excel forbids [ ] : * ? / \, limits a name to 31 characters, and refuses duplicates. writexl fixes each of those and warns, naming the original and the replacement, because a sheet name is often taken from data and losing the whole export over one stray / would be unkind.

What is written

Column types map to Excel’s own:

NA becomes an empty cell. A column of a type xlsx has no representation for — complex, raw, a plain list column — is an error rather than a silent approximation.

na writes something else in place of an empty cell, for NaN as well as NA. It keeps its own type, so a number stays a number:

tmp <- write_xlsx(data.frame(x = c(1.5, NA)), na = "not measured")

Set it for the whole workbook with xl_properties(na = ), for one column with xl_col_spec(na = ), or for one cell with xl_cell_general(na = ); the innermost one that is set wins. Substituting a string into a numeric column makes that column mixed, so reading it back gives character.

Excel has no concept of a time zone, so POSIXct needs a decision. writexl makes it once for the whole workbook: if every value shares one zone the local wall-clock reading is written, and if they differ everything is converted to UTC and you are warned. Either way nothing is mislabelled.

The header row is written by default and styled bold; col_names = FALSE and format_headers = FALSE turn each off.

Everything else

The rest of the package is optional. Nothing below is needed to write a workbook, and each has a vignette of its own:

• Formatting cells — fonts, fills, borders, number formats and alignment, built from group constructors that combine with +, plus conditional formatting.

• Worksheets and workbooks — column widths and row heights, frozen panes, tab colours, printing and page setup, and the document properties.

• Charts and images — all 22 chart types Excel offers, their axes, series parts and legends; chartsheets; and pictures placed on or in a sheet.

• Formulas, tables and the rest — formulas and array formulas, hyperlinks, comments, rich strings, data validation, autofilters, worksheet tables and merged cells.

• Everything at once — one runnable script that exercises the whole package into a couple of workbooks, for reviewing what it can do without reading five vignettes first.

Two ideas run through all of them and are worth knowing early.

A format is an object. xl_font(), xl_fill() and the rest each return a complete xl_format, they combine with +, and the same object styles a cell, a column, a chart series or a conditional rule.

money <- xl_num_format("$#,##0.00") + xl_font(bold = TRUE)

A range is written one of two ways. Either as A1 text, or against the data frame by column name — and the second is worth preferring, because it moves with the column and accounts for the header row on its own:

xl_sheet(iris, autofilter = "A1:E151")                   # A1 text
#> <xl_sheet: 150 rows x 5 cols>
xl_sheet(iris, filter = xl_filter("Species", "==", "setosa"))  # by column
#> <xl_sheet: 150 rows x 5 cols>

Reading it back

writexl only writes. To read, use readxl:

readxl::read_xlsx(write_xlsx(head(iris, 3), tempfile(fileext = ".xlsx")))
#> # A tibble: 3 × 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa