Add categorical colors to Excel xlsx worksheets

Add categorical colors to Excel xlsx worksheets

applyXlsxCategoricalFormat(
  xlsxFile,
  sheet = 1,
  rowRange = NULL,
  colRange = NULL,
  colorSub = NULL,
  colorSubText = setTextContrastColor(colorSub),
  trimCatNames = TRUE,
  overwrite = TRUE,
  wrapText = FALSE,
  stack = TRUE,
  verbose = FALSE,
  ...
)

Arguments

xlsxFile

character filename to a file with ".xlsx" extension, or Workbook object defined in the openxlsx package. When xlsxFile is a Workbook the output is not saved to a file.

sheet

integer index of the worksheet or worksheets.

rowRange, colRange

integer vectors of rows and columns to apply categorical colors in the Excel xlsx worksheet, passed as openxlsx::readWorkbook(..., rows=rowRange, cols=colRange). This step defines which columns are read from each workbook, however when colorSub is provided as a list whose names are intended to match colnames(), only matching colnames are processed.

colorSub

one of the following types of input:

• Named character vector of valid R colors, whose names correspond to values in worksheet cells.

• Named list whose names correspond to colnames one or more workbooks in sheet. Each list element should be a character vector named by column values, or color function that takes column values and returns a character vector of colors for each value.

colorSubText

optional character vector of colors, whose names correspond to values in the worksheet cells. In absence of a specific text color, jamba::setTextContrastColor() is used to define a contrasting text color to be visible on the colored background.

trimCatNames

logical whether to trim whitespace and punctuation from colorSub and from Excel cell fields before matching colors to Excel values.

overwrite

logical indicating whether new cell color styles should be forced overwrite of previous cell styles.

wrapText

logical indicating whether to wrap text.

stack

logical indicating whether new color rules should be applied above existing styles, many of whose styles may not affect the specific cell color, for example the font size and font name.

verbose

logical indicating whether to print verbose output.

...

additional arguments are ignored.

style_coord_list

list named by unique hex R colors, containing matrix objects with colnames c("row", "col") with individual cell positions to apply the corresponding style

styles_list

list named by hex R color, containing openxlsx::style objects using the corresponding color as the background fill color.

wb, sheet, stack

arguments passed to openxlsx::addStyle()

Value

Workbook object as defined by the openxlsx package is returned invisibly with invisible(). This Workbook

can be used in argument wb to provide a speed boost when saving multiple sheets to the same file.

list named by the primary fill color of each cell, containing a openxlsx::style object suitable for use by openxlsx::addStyle().

list named by style, by default the hex color with no alpha where each element is a matrix with colnames row and col

indicating individual row/column coordinate positions.

NULL, this function is called for the by-product of adjusting

Details

This function is a convenient wrapper for applying categorical color formatting to cell background colors, and applies a contrasting color to the text in cells using jamba::setTextContrastColor(). It uses a named character vector of colors supplied as colorSub to define cell background colors, and optionally colorSubText to define a specific color for the cell text.

See also

Other jam export functions: applyXlsxConditionalFormat(), readOpenxlsx(), set_xlsx_colwidths(), set_xlsx_rowheights(), writeOpenxlsx()

Examples

if (FALSE) {
   df <- data.frame(a=LETTERS[1:5], b=1:5);
   jamba::writeOpenxlsx(x=df,
      file="jamba_test.xlsx",
      sheetName="test_jamba");

   colorSub <- nameVector(
      rainbow(5, s=c(0.8, 1), v=c(0.8, 1)),
      LETTERS[1:5]);
   applyXlsxCategoricalFormat(
      xlsxFile="jamba_test.xlsx",
      sheet="test_jamba",
      colorSub=colorSub
   )
}