Format openxlsx2 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 colors can be provided for increasing, decreasing, or text changes. 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.

Usage

add_changed_formats(
  wb,
  cur_sheet,
  sheet_comp,
  rows_invert = NULL,
  cols_invert = NULL,
  df_invert = NULL,
  change_color = "#E1C0FF",
  pos_color = "#FFC7CE",
  neg_color = "#C6EFCE"
)

Arguments

wb

openxlsx2 workbook to make on which to make changes.

cur_sheet

sheet name in openxlsx2 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 identify individual cells to invert color schemes for increase vs decrease. Defaults to NULL.

change_color

Hex code for color to highlight cells that changed (and were not numerics). Defaults to lavendar ("#E1C0FF").

pos_color

As change_color, but for numeric cells that increase in value. Defaults to coral ("#E1C0FF").

neg_color

As change_color, but for numeric cells that decrease in value. Defaults to light green ("#C6EFCE")