Package 'qxl'

An R-style expression

A data frame that expression x relates to

Integer reflecting the first row of data in the Excel sheet to be output. Defaults to 2.

(optional) password required to unprotect the worksheet

Tidy-selection specifying the columns that protection should apply to. Defaults to dplyr::everything to select all columns.

The number of additional rows (beyond the range of the current data) to unprotect within columns not specified in argument cols. See explanation in Description. Defaults to 0.

Whether to protect or unprotect the sheet (default=TRUE)

Whether selecting locked cells is locked

Whether selecting unlocked cells is locked

Whether formatting cells is locked

Whether formatting columns is locked

Whether formatting rows is locked

Whether inserting columns is locked

Whether inserting rows is locked

Whether inserting hyperlinks is locked

Whether deleting columns is locked

Whether deleting rows is locked

Whether sorting is locked

Whether auto-filter is locked

Whether pivot tables are locked

Whether objects are locked

Whether scenarios are locked

Path to the xls/xlsx file.

Sheet to read. Either a string (the name of a sheet), or an integer (the position of the sheet). Ignored if the sheet is specified via range. If neither argument specifies the sheet, defaults to the first sheet.

A cell range to read from, as described in cell-specification. Includes typical Excel ranges like "B3:D87", possibly including the sheet name like "Budget!B2:G14", and more. Interpreted strictly, even if the range forces the inclusion of leading or trailing empty rows or columns. Takes precedence over skip, n_max and sheet.

TRUE to use the first row as column names, FALSE to get default names, or a character vector giving a name for each column. If user provides col_types as a vector, col_names can have one entry per column, i.e. have the same length as col_types, or one entry per unskipped column.

Either NULL to guess all from the spreadsheet or a character vector containing one entry per column from these options: "skip", "guess", "logical", "numeric", "date", "text" or "list". If exactly one col_type is specified, it will be recycled. The content of a cell in a skipped column is never read and that column will not appear in the data frame output. A list cell loads a column as a list of length 1 vectors, which are typed using the type guessing logic from col_types = NULL, but on a cell-by-cell basis.

Logical indicating whether to convert date columns lacking a time component to class "Date". By default readxl::read_excel reads columns containing dates or datetimes as class POSIX, even if there is no time component (i.e. in which case the times will all be "00:00:00"). If simplify_posix is TRUE (the default), columns containing dates with no nonzero time values are converted to class "Date" using lubridate::as_date.

Logical indicating whether columns of class "logical" containing all missing values should be converted to class "character". If argument col_types is NULL (the default), columns containing all missing values are read in by readxl::read_excel as class "logical". If empty_cols_to_chr is TRUE (the default), such columns are converted to class "character".

Character vector of strings to interpret as missing values. By default, readxl treats blank cells as missing data.

Should leading and trailing whitespace be trimmed?

Minimum number of rows to skip before reading anything, be it column names or data. Leading empty rows are automatically skipped, so this is a lower bound. Ignored if range is given.

Maximum number of data rows to read. Trailing empty rows are automatically skipped, so this is an upper bound on the number of rows in the returned tibble. Ignored if range is given.

Maximum number of data rows to use for guessing column types.

Display a progress spinner? By default, the spinner appears only in an interactive session, outside the context of knitting a document, and when the call is likely to run for several seconds or more. See readxl_progress() for more details.

Handling of column names. Passed along to tibble::as_tibble(). readxl's default is '.name_repair = "unique", which ensures column names are not empty and are unique.

Which rows the style should apply to. Can be set using either:

Keyword: (e.g. rows = "data" or rows = "all")
Keyword "data" (the default) applies a style to all data rows (excludes the header), whereas keyword "all" applies a style to all rows (header and data)

Integer rows indexes: (e.g. rows = c(2, 5, 6))
Note that in this case indexes represent Excel rows rather than R rows (i.e. the header is row 1).

An expression: (e.g. rows = cyl > 4)
Given an expression the style is applied using conditional formatting, with the expression translated into its Excel formula equivalent.

Expressions can optionally include a .x selector (e.g. .x == 1) to refer to multiple columns. See section Using a .x selector below.

Note that conditional formatting can update in real time if relevant data is changed within the workbook.

Tidy-selection specifying the columns that the style should apply to. Defaults to dplyr::everything to select all columns.

A name of a font. Note the font name is not validated. If fontName is NULL, the workbook base font is used. (Defaults to Calibri)

Font size. A numeric greater than 0. If fontSize is NULL, the workbook base font size is used. (Defaults to 11)

Colour of text in cell. A valid hex colour beginning with "#" or one of colours(). If fontColour is NULL, the workbook base font colours is used. (Defaults to black)

Cell border. A vector of "top", "bottom", "left", "right" or a single string).

• "top" Top border

• bottom Bottom border

• left Left border

• right Right border

• TopBottom or c("top", "bottom") Top and bottom border

• LeftRight or c("left", "right") Left and right border

• TopLeftRight or c("top", "left", "right") Top, Left and right border

• TopBottomLeftRight or c("top", "bottom", "left", "right") All borders

Colour of cell border vector the same length as the number of sides specified in "border" A valid colour (belonging to colours()) or a valid hex colour beginning with "#"

Border line style vector the same length as the number of sides specified in "border"

• none No Border

• thin thin border

• medium medium border

• dashed dashed border

• dotted dotted border

• thick thick border

• double double line border

• hair Hairline border

• mediumDashed medium weight dashed border

• dashDot dash-dot border

• mediumDashDot medium weight dash-dot border

• dashDotDot dash-dot-dot border

• mediumDashDotDot medium weight dash-dot-dot border

• slantDashDot slanted dash-dot border

Cell background fill colour. A valid colour (belonging to colours()) or a valid hex colour beginning with "#". – Use for conditional formatting styles only.

Cell foreground fill colour. A valid colour (belonging to colours()) or a valid hex colour beginning with "#"

Horizontal alignment of cell contents

• left Left horizontal align cell contents

• right Right horizontal align cell contents

• center Center horizontal align cell contents

• justify Justify horizontal align cell contents

A name Vertical alignment of cell contents

• top Top vertical align cell contents

• center Center vertical align cell contents

• bottom Bottom vertical align cell contents

Text styling.

• bold Bold cell contents

• strikeout Strikeout cell contents

• italic Italicise cell contents

• underline Underline cell contents

• underline2 Double underline cell contents

• accounting Single accounting underline cell contents

• accounting2 Double accounting underline cell contents

Logical. If TRUE cell contents will wrap to fit in column.

Rotation of text in degrees. 255 for vertical text.

Horizontal indentation of cell contents.

Whether cell contents are locked (if worksheet protection is turned on)

Whether the formula of the cell contents will be hidden (if worksheet protection is turned on)

A Workbook object to write to file

A character string naming an xlsx file

If TRUE, overwrite any existing file.

A data frame, or list of data frames

Filename to write to. If NULL the resulting workbook is returned as an openxlsx object of class "Workbook" rather than written to a file.

An openxlsx workbook object to write to. Defaults to a fresh workbook created with openxlsx::createWorkbook. Only need to update when repeatedly calling qxl() to add worksheets to an existing workbook.

Optional character vector of worksheet names. If NULL (the default) and x is a named list of data frames, worksheet names are taken from names(x). Otherwise, names default to "Sheet1", "Sheet2", ...

Optional column header. Defaults to NULL in which case column names are taken directly from the data frame(s) in x, to create normal single-row headers. Can alternatively pass a named character vector to set custom names as the first row and a subheader with variable names as a hidden second row.

header = c(
  mpg = "Miles per US gallon",
  cyl = "Number of cylinders",
  disp = "Engine displacement (cubic in.)
)

Style for the header row. Set with qstyle(), or set to NULL for no header styling. Defaults to bold text.

Logical indicating whether to hide the subheader (if present). Defaults to TRUE.

Optional style to set using qstyle()

Optional vector of one or more column names used to create alternating groupings of rows, with every other row grouping styled as per argument group_style. See section Grouping rows.

Optional style to apply to alternating groupings of rows, as specified using argument groups. Set using qstyle()

Numeric vector of row heights (in Excel units). The vector is recycled if shorter than the number of rows in x. Defaults to NULL to use default row heights.

Vector of column widths (in Excel units). Can be numeric or character, and may include keyword "auto" for automatic column sizing. The vector is recycled if shorter than the number of columns in x. Defaults to "auto".

Use named vector to give column widths for specific columns, where names represent column names of x or the keyword ".default" to set a default column width for all columns not otherwise specified. E.g.

# specify widths for cols mpg and cyl, all others default to "auto"
col_widths <- c(mpg = 5, cyl = 10)

# specify widths for cols mpg and cyl, and explicit default for all others
col_widths <- c(mpg = 5, cyl = 10, .default = 7)

Integer specifying a row to freeze at. Defaults to 1 to add a freeze below the header row. Set to 0 or NULL to omit freezing.

Integer specifying a column to freeze at. Defaults to NULL. Set to 0 or NULL to omit freezing.

Optional function specifying how to protect worksheet components from user modification. See function qprotect.

Optional specification of list-style data validation for one or more columns. Can specify either as a list of vectors giving options for one or more column in x, e.g.:

list(
  var_x = c("Yes", "No"),
  var_y = c("Small", "Medium", "Large")
)

or as a data.frame where the first column gives column names and the second column gives corresponding options, e.g.:

data.frame(
  col = c("var_x", "var_x", "var_y", "var_y", "var_y"),
  val = c("Yes", "No", "Small", "Medium", "Large")
)

Validation options are written/appended to a hidden worksheet named "valid_options".

Optional specification of conditional list-style validation, where the set of values to be allowed in a given column depends on the corresponding value within one or more other columns (e.g. the allowed values in column 'city' depend on the corresponding value in columns 'country' and 'province'). Must be a data.frame with at least two columns, where the first column(s) give the conditional entries (e.g. 'country', 'province') and the last column gives the corresponding allowed entries (e.g. 'city') to be implemented as data validation. The column names in validate_cond should match the relevant columns within x.

Note that in the current implementation validation is based on values in the conditional column(s) of x at the time the workbook is written, and will not update in real time if those values are later changed.

Optional vector of value(s) to always allow, independent of the value in the conditional column (e.g. "Unknown").

Logical indicating whether to add column filters.

Tidy-selection specifying which columns to filter. Only used if filter is TRUE. Defaults to everything() to select all columns.

Integer specifying initial zoom percentage. Defaults to 130.

Excel format for date columns. Defaults to "yyyy-mm-dd".

Logical indicating whether to overwrite existing file. Defaults to TRUE