applyXlsxCategoricalFormat: Add categorical colors to Excel xlsx worksheets

In jmw86069/jamba: Jam Base Methods

Add categorical colors to Excel xlsx worksheets

Description

Add categorical colors to Excel xlsx worksheets

Usage

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()

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.

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

See Also

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

Examples

## Not run: 
   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
   )

## End(Not run)

jmw86069/jamba documentation built on Oct. 9, 2024, 10:52 a.m.