formatXL: Format data with font colors, font sizes, alignments,...

In shirewoman2/LaurasHelpers: Miscellaneous Helper Functions for Common PK or Analytical Chemistry Tasks

Format data with font colors, font sizes, alignments, borders, etc. when you write them to an Excel file

Description

This function takes a data.frame you want to save to an Excel file using the package xlsx and applies the formatting you want for the Excel file. Warning: The arguments for this function are a lot of lists, and it can be tricky to get the syntax exactly right, but I haven't yet come up with a better way of doing things. If this isn't working, check that your lists are laid out correctly and named correctly.

Usage

formatXL(
  DF,
  file,
  sheet = NA,
  colWidth = list(colNum = NULL, colName = NULL, width = NULL),
  styles
)

Arguments

DF	input data.frame
file	file name (character)
sheet	sheet name (character). Defaults to the name of the supplied data.frame if no other name is supplied.
colWidth	A named list of colNum or colName and width in pixels. Examples: colWidth = list(colNum = c(1, 5, 6), width = 25) colWidth = list(colName = c("ColA", "ColG"), width = c(10, 30)) If colNum and colName are set to NULL, all columns will be set to the width listed. For any columns not specifically set or if width is set to NULL, reasonable guesses for column widths will be used.
styles	A list of lists (one list for every set of cells that you want to format) that contains the following named objects: rows A vector of the row indices for the cells whose formatting we're setting. Leaving rows unspecified applies the formatting to all rows in the sheet. Important note: For consistency with row naming elsewhere in R, row 1 refers to the 1st row of data in the data.frame, NOT the header. If you would like to apply the formatting to the header, set the row to 0. Examples: rows = 1:5 rows = c(5, 23, 60) columns A vector of the column indices or names for all the cells whose formatting we're setting. Leaving columns unspecified applies the formatting to all columns. Examples: columns = c(1, 2, 8) columns = c("ColB", "ColH", "ColQ") numberFormat The format for displaying any numbers in the cells. Options are "date", "general" (doesn't format anything), or "currency" (dollar sign included, negative numbers are red, and 2 digits included). Example: numberFormat = "currency" font A named list of "color", "size", "bold", "italics", and/or "underline" to set the font style. color Color options are standard color names s/a "red", "blue", "purple" or any of the possible named colors available in R. For a list of indexed color names, with the xlsx package loaded, type "INDEXED_COLORS_" into the console. size Size is the font size, with typical values being 10 or 11. Setting it to NULL means it will not change from the default. bold, italics, underline The "bold", "italics", and "underline" values are logical and default to FALSE. Examples: font = list(color = "blue", bold = TRUE, italics = FALSE, underline = FALSE) font = list(color = "red", size = 16) textposition A named list of "alignment" and "wrapping". alignment Alignment can be left, right, center, or general (all are vertically aligned to the bottom), or "middle" for vertical and horizontal alignment to the center. wrapping Wrapping is TRUE or FALSE for whether the text should be wrapped. Examples: textposition = list(alignment = "center", wrapping = TRUE) textposition = list(alignment = "right") border A named list of color, position, and pen. color The same color options that worked for assigning the font color work for coloring the border. position The position can be "bottom", "left", "top", or "right". pen The options for the pen setting, which is optional and defaults to a thin border, are "solid" ("BORDER_THIN" behind the scenes), "hair" for really thin hairline, "medium" for a medium-thickness line, "thick" for a thick line, "dashed", "dotted", and "none". Examples: border = list(color = "black", position = c("top", "bottom")) border = list(color = "red", position = "left", pen = "thick") fill A color s/a "red", "blue", "purple" or any of the possible named colors available in R.

Details

If this will create a new Excel file or if your current Excel file has no other sheets but this one, this will generate a message "Workbook has no sheets!" that I cannot seem to get rid of.

COMMON ERRORS or ERROR MESSAGES since this is a tad (ok, a LOT) glitchy: styles is meant to accommodate multiple sets of formatting, so you have to have styles be a list but then also each set of cells that you're formatting must also be a list. This means that styles should probably look like this in your code: styles = list(list(...))

If you set a style for a cell and then set another style for that same cell, the final style will be the 2nd one, not a combination of the two. For example, say you select row 1 and make it blue and bold, and then you add a border on the left side of all cells in column 4. The cell in row 1, column 4 will be the default Excel style of text plus having a border on the left side - not blue, bold, and border on left.

Value

This does not return any R objects; it saves an Excel file.

Examples

data(iris)
iris$Date <- as.Date("2020-11-03")
iris$Money <- rnorm(nrow(iris), 20, 20)

formatXL(DF = iris, file = "test.xlsx", sheet = "iris1",
         colWidth = list(width = 30),
         styles = list(list(columns = 6,
                            numberFormat = "date"),
                       list(columns = 7,
                            numberFormat = "currency"),
                       list(rows = 0,
                            font = list(bold = TRUE, size = 18),
                            textposition = list(alignment = "middle",
                                                wrapping = TRUE))))

formatXL(DF = iris, file = "test.xlsx", sheet = "iris2",
         colWidth = list(colNum = c(1, 5, 6), width = c(25, 10, 30)),
        styles = list(list(rows = 4,
                            font = list(color = "blue", bold = TRUE)),
                       list(rows = 8:9, columns = 3,
                            font = list(color = "red", italics = TRUE),
                            textposition = list(alignment = "center",
                                                wrapping = TRUE),
                            fill = "dodgerblue3"),
                       list(rows = 0, columns = 1,
                            font = list(color = "purple", underline = TRUE),
                            textposition = list(alignment = "right",
                                                wrapping = TRUE))))

shirewoman2/LaurasHelpers documentation built on Oct. 22, 2023, 2:07 p.m.