Format openxlsx worksheet based on changes

Highlights cells that changed, coloring differently for increasing, decreasing, and non-numeric cells. Typically used on a worksheet that contains the $sheet.diff dataframe from the same sheet comparison as provided in the cur.sheet argument. In some cases it may be useful to define custom color schemes (e.g., if increasing numbers are good and decreasing numbers are bad, you may want green and red foregrounds for those types of changes). Individual styles can be provided with optional arguments see ?openxlsx::createStyle for options in defining styles.

Usage

add_changed_formats(
  wb,
  cur.sheet,
  sheet.comp,
  rows.invert = NULL,
  cols.invert = NULL,
  df.invert = NULL,
  nofillStyle = NULL,
  changeStyle = NULL,
  posStyle = NULL,
  negStyle = NULL
)

Arguments

wb

openxlsx workbook to make on which to make changes.

cur.sheet

sheet name in openxlsx workbook on which to make changes.

sheet.comp

list of comparison dataframes generated by sheet_comp()

rows.invert

Optional vector of row numbers to invert color scheme for increase vs decrease.

cols.invert

Optional vector of column numbers to invert color scheme for increase vs decrease.

df.invert

Optional data frame with $row and $col entries to indentify individual cells to invert color schemes for increase vs decrease. Defaults to NULL.

nofillStyle

Optional openxlsx style object for cells with no changes flagged. Default has black text, white foreground. Create custom style with openxlsx::createStyle().

changeStyle

As nofillStyle, but for non-numeric cells with changed values. Default has black text, light purple forecround.

posStyle

As nofillStyle, but for numeric cells that increase in value. Default has black text, light coral foreground.

negStyle

As nofillStyle, but for numeric cells that decrease in value. Default has black text, light green foreground.

Details

In some cases it may make sense to reverse the color scheme of numeric changes for individual rows, columns, or cells (e.g., when scanning fishery model outputs, increasing fish escapement and decreasing fish exploitation rates logically should both show the same color. Similarly, increasing profits and decreasing costs logically should both show the same color.). Optional arguments rows.invert, cols.invert, and df.invert allow you to specify individual regions of the dataframe to reverse the color scheme.