write_xlsx {openxlsx2}

Write data to an xlsx file

Description

Write a data frame or list of data frames to an xlsx file.

Usage

write_xlsx(x, file, as_table = FALSE, ...)

Arguments

x

An object or a list of objects that can be handled by wb_add_data() to write to file.

file

An optional xlsx file name. If no file is passed, the object is not written to disk and only a workbook object is returned.

as_table

If TRUE, will write as a data table, instead of data.

...

Arguments passed on to wb_workbook, wb_add_worksheet, wb_add_data_table, wb_add_data, wb_freeze_pane, wb_set_col_widths, wb_save

creator

Creator of the workbook (your name). Defaults to login username or options("openxlsx2.creator") if set.

sheet

A name for the new worksheet

grid_lines

A logical. If FALSE, the worksheet grid lines will be hidden.

tab_color

Color of the sheet tab. A wb_color(), a valid color (belonging to grDevices::colors()) or a valid hex color beginning with "#".

zoom

The sheet zoom level, a numeric between 10 and 400 as a percentage. (A zoom value smaller than 10 will default to 10.)

total_row

logical. With the default FALSE no total row is added.

start_col

A vector specifying the starting column to write x to.

start_row

A vector specifying the starting row to write x to.

col_names

If TRUE, column names of x are written.

row_names

If TRUE, the row names of x are written.

na.strings

Value used for replacing NA values from x. Default looks if options(openxlsx2.na.strings) is set. Otherwise na_strings() uses the special ⁠#N/A⁠ value within the workbook.

first_active_row

Top row of active region

first_active_col

Furthest left column of active region

first_row

If TRUE, freezes the first row (equivalent to first_active_row = 2)

first_col

If TRUE, freezes the first column (equivalent to first_active_col = 2)

widths

Width to set cols to specified column width or "auto" for automatic sizing. widths is recycled to the length of cols. openxlsx2 sets the default width is 8.43, as this is the standard in some spreadsheet software. See Details for general information on column widths.

overwrite

If FALSE, will not overwrite when file already exists.

Details

columns of x with class Date or POSIXt are automatically styled as dates and datetimes respectively.

Value

A workbook object

Examples

## write to working directory
write_xlsx(iris, file = temp_xlsx(), col_names = TRUE)

write_xlsx(iris,
  file = temp_xlsx(),
  col_names = TRUE
)

## Lists elements are written to individual worksheets, using list names as sheet names if available
l <- list("IRIS" = iris, "MTCARS" = mtcars, matrix(runif(1000), ncol = 5))
write_xlsx(l, temp_xlsx(), col_widths = c(NA, "auto", "auto"))

## different sheets can be given different parameters
write_xlsx(l, temp_xlsx(),
  start_col = c(1, 2, 3), start_row = 2,
  as_table = c(TRUE, TRUE, FALSE), with_filter = c(TRUE, FALSE, FALSE)
)

# specify column widths for multiple sheets
write_xlsx(l, temp_xlsx(), col_widths = 20)
write_xlsx(l, temp_xlsx(), col_widths = list(100, 200, 300))
write_xlsx(l, temp_xlsx(), col_widths = list(rep(10, 5), rep(8, 11), rep(5, 5)))