Export Data to XLSX Files — export

The natural complement to import_xlsx(). Takes a combined data object (the kind produced by import_xlsx() with rbind = TRUE) and writes it back to disk. The output destination is decided by a single path argument, and worksheet splitting follows the data automatically — there are no separate "modes" to choose:

path is a directory (no .xlsx extension) — write one file per file_col value into that directory, with one worksheet per sheet_col value inside each file. This is the transparent round-trip of import_xlsx().
path is a file (ends in .xlsx) — write everything into a single workbook. Worksheets are named "<file_col>_<sheet_col>" (or just "<sheet_col>" / "<file_col>" when only one tracking column is present), and plain data without tracking columns becomes a single sheet.

Columns injected by import_xlsx() (file_col, sheet_col) are stripped from the output by default so the exported sheets are identical to the originals. Plain data.frames without any tracking columns (e.g. mtcars) are supported and are written as a single sheet — but only to a file path, since there is nothing to split files by.

export_xlsx(
  data,
  path,
  file_col = "excel_name",
  sheet_col = "sheet_name",
  sheet_name = "Sheet1",
  drop_cols = TRUE,
  overwrite = TRUE,
  verbose = FALSE
)

Arguments

data: A data.frame, data.table, or tibble to export. Tracking columns (file_col / sheet_col) are optional.
path: character(1). The output destination. If it ends in .xlsx (case-insensitive) it is treated as a single workbook; otherwise it is treated as an output directory and one file is written per file_col value. Parent directories are created recursively as needed.
file_col: character(1). Name of the column identifying the source file. Default "excel_name".
sheet_col: character(1). Name of the column identifying the source sheet. Default "sheet_name".
sheet_name: character(1). Worksheet tab name used when data has neither tracking column (single-sheet fallback). Default "Sheet1".
drop_cols: logical(1). When TRUE (default) the tracking columns are removed from every exported sheet so the round-trip is transparent.
overwrite: logical(1). Allow overwriting existing files. Default TRUE.
verbose: logical(1). Print a message for every sheet/file written. Default FALSE.

Value

Invisibly, a named character vector of written file paths: named by file_col value in directory mode, or by path in single-workbook mode.

Details

Why writexl?

writexl writes .xlsx via a minimal C library with no Java or Perl dependency. It is faster than openxlsx for plain export and produces smaller files, at the cost of no cell formatting, formulas, or styles. For those, use openxlsx / openxlsx2.

Sheet-name sanitisation

Excel sheet names are limited to 31 characters and may not contain [ ] * ? / \ :. Both constraints are enforced automatically.

Directory vs. file dispatch

The single path argument is classified purely by its extension: a trailing .xlsx means "one workbook", anything else means "a directory of files". If you want a directory whose name happens to end in .xlsx, append a trailing slash, or pass an explicit file name.

Examples

# Example: Excel file export demonstrations
# Example 1: Export a plain data.frame to a single workbook
out_file <- file.path(tempdir(), "test.xlsx")
export_xlsx(
  mtcars,                             # Data to export (no tracking columns)
  path       = out_file,              # Ends in .xlsx -> one workbook
  sheet_name = "test"                 # Worksheet tab name for the single sheet
)
# Clean up the generated file
file.remove(out_file)
#> [1] TRUE

# Example 2: Split into one file per group
out_files <- export_xlsx(
  iris,                               # Data to export
  path      = tempdir(),              # A directory -> one file per file_col value
  file_col  = "Species",              # Column whose values name the output files
  drop_cols = FALSE                   # Keep the Species column in each output file
)
# Clean up the generated files (export_xlsx returns the written paths)
file.remove(out_files)
#> [1] TRUE TRUE TRUE