CODEWITHSUNDEEP
rgradientopenxlsx
Steven Cipullo
Steven Cipullo

Reputation: 13

R - openxlsx formatting - Is there a way to format a single cell with a 2-color gradient?

I'm replicating a manually created Excel spreadsheet, in which the users are very specific on HOW the sheet should look (exact cell spacing, exact coloring with specific conditions, margins/borders, etc).

I'm able to do everything I need in R using openxlsx (and a few other packages).

The problem is simple: I can't find a way to apply a two-color gradient to a single cell. Yellow on top, orange on bottom.

I've been looking at the notes on the openxlsx package, and found information on gradient data bars. This is NOT what I'm looking for... I need just simple two-color formatting.

If it is possible, my guess would be something like this:

library(openxlsx)

line1 <- createStyle(halign = "left", textDecoration = "bold", fontName = "Calibri", fontSize = "11", fgFill = c(_____,_____))

Note: I'm working on a non-friendly Java computer. Xlsx package is not an option for me. Additionally, I'm somewhat familiar with the nuance of tidyxl, but I would prefer to use openxlsx.

Thanks in advance.

Upvotes: 1

Views: 975

Answers (2)

Fideldue
Fideldue

Reputation: 237

Since I stumbled upon this question by searching for an answer and it still seems not officially implemented, I just want to share my workaround solution:

Your styles are saved in your workbook object as after creation with createStyle follow:

> str(wb[["styleObjects"]])
List of 1
 $ :List of 4
  ..$ style:Reference class 'Style' [package "openxlsx"] with 28 fields
  .. ..$ fontName            : NULL
  .. ..$ fontColour          : NULL
  .. ..$ fontSize            : NULL
  .. ..$ fontFamily          : NULL
  .. ..$ fontScheme          : NULL
  .. ..$ fontDecoration      : chr(0) 
  .. ..$ borderTop           : NULL
  .. ..$ borderLeft          : NULL
  .. ..$ borderRight         : NULL
  .. ..$ borderBottom        : NULL
  .. ..$ borderTopColour     : NULL
  .. ..$ borderLeftColour    : NULL
  .. ..$ borderRightColour   : NULL
  .. ..$ borderBottomColour  : NULL
  .. ..$ borderDiagonal      : NULL
  .. ..$ borderDiagonalColour: NULL
  .. ..$ borderDiagonalUp    : logi FALSE
  .. ..$ borderDiagonalDown  : logi FALSE
  .. ..$ halign              : NULL
  .. ..$ valign              : NULL
  .. ..$ indent              : NULL
  .. ..$ textRotation        : NULL
  .. ..$ numFmt              : NULL
  .. ..$ fill                :List of 1
  .. .. ..$ fillFg:List of 1
  .. .. .. ..$ rgb: chr "FF1F497D"
  .. ..$ wrapText            : NULL
  .. ..$ locked              : NULL
  .. ..$ hidden              : NULL
  .. ..$ xfId                : NULL
  .. ..and 17 methods, of which 3 are  possibly relevant:
  .. ..  as.list, initialize, show#envRefClass
  ..$ sheet: chr "test"
  ..$ rows : int 3
  ..$ cols : int 1

At this point you can alter the fill attribute of your preferred style with something like this:

wb$styleObjects[[1]][["style"]][["fill"]] <- "<fill><gradientFill degree=\"90\"><stop position=\"0\"><color rgb=\"FFfec200\"/></stop><stop position=\"1\"><color rgb=\"FF5a9ad7\"/></stop></gradientFill></fill>"

The easiest way to find out how to alter the styles is to build an excel file with your preferred style and load the whole workbook via loadWorkbook and have a look at the wb[["styleObjects"]].

Upvotes: 1

Steven Cipullo
Steven Cipullo

Reputation: 13

Two-color gradients within a cell are not currently supported by openxlsx.

Upvotes: 0

Related Questions