How to combine colour formatting with 0.000 format in openxlsx package in r?

Question

Im using openxlsx library in R. Excel output of my code is not considering 0.000 format and in my excel output instead of numbers like -0.019 I can see only 0 or 1 (underneath they have good numbers).

my dataset:

formatting_rows <- data.frame(
  driver_group = c(1, 2),
  brand = c("ALNATURA", "ALNATURA"),
  apple = c(-0.0560447485, -0.0364285838),
  banana = c(0.566816508, 0.956636920),
  pear = c(-2.655737e-02, -2.264970e-02),
  carrot = c(-0.0404093701, 0.0434975415),
  celery = c(0.40294248, 0.41057630),
  cucumber = c(-1.433510e-02, 3.704044e-03),
highlight_fruits = c(TRUE, FALSE),
highlight_veggies = c(TRUE, FALSE))
 

My code:

  wb = createWorkbook(title = "workbook")
    options("openxlsx.numFmt" = "0.000")

CellHighlight = createStyle(fontColour = '#b54c4a', textDecoration = "italic")

addStyle(wb, tab_name, CellHighlight,
           rows = c(which(formatting_rows$highlight_fruits))+1,
           cols = which(names(data) %in% c("apple", "banana", "pear")),
           gridExpand = TRUE, stack = TRUE)
 
 
  addStyle(wb, tab_name, CellHighlight,
           rows = c(which(formatting_rows$highlight_veggies))+1,
           cols = which(names(data) %in% c("carrot", "celery", "cucumber")),
           gridExpand = TRUE, stack = TRUE)
 

I tried to add numFmt = "0.000" to CellHighlight (not worked)

CellHighlight = createStyle(fontColour = '#b54c4a', textDecoration = "italic", numFmt = "0.000")

I tried also to add styles:

addStyle(wb, tab_name, createStyle(numFmt = "0.000"),
           rows = c(which(formatting_rows$highlight_fruits))+1,
           cols = which(names(data) %in% c("apple", "banana", "pear")),
           gridExpand = TRUE, stack = TRUE)


  addStyle(wb, tab_name, createStyle(numFmt = "0.000"),
           rows = c(which(formatting_rows$highlight_veggies))+1,
           cols = which(names(data) %in% c("carrot", "celery", "cucumber")),
           gridExpand = TRUE, stack = TRUE)

This is working, but when I'm using it on other data or on the same code next day is going back o 0 or 1 format. The cells are always highlighted in red. I'm writing data before addStyle, the stack = TRUE. I don't know what to do next.

Answer 1

The quickest way I could think of would be to use conditional formatting like this in openxlsx2 (sorry, no clue what happens in your openxlsx example).

I switched the highlighting column in your example data to different rows. If you really need cell coloring there are other ways to do this (but this should be the quickest), especially if you only want to highlight non consecutive cells in the data set.

formatting_rows <- data.frame(
  driver_group = c(1, 2),
  brand = c("ALNATURA", "ALNATURA"),
  apple = c(-0.0460447485, -0.0264285838),
  banana = c(0.576816508, 0.856636920),
  pear = c(-2.655937e-02, -2.263970e-02),
  carrot = c(-0.0304093701, 0.0334975415),
  celery = c(0.60294248, 0.11057630),
  cucumber = c(-1.833510e-02, 3.704034e-03),
  highlight_fruits = c(TRUE, FALSE),
  highlight_veggies = c(FALSE, TRUE) # switched order from OPs question
)

library(openxlsx2)
options("openxlsx2.numFmt" = "0.000")

fruits <- wb_dims(
  cols = match(c("apple", "banana", "pear"), names(formatting_rows)),
  rows = seq_len(nrow(formatting_rows)) + 1L
)

veggie <- wb_dims(
  cols = match(c("carrot", "celery", "cucumber"), names(formatting_rows)),
  rows = seq_len(nrow(formatting_rows)) + 1L
)

wb <- wb_workbook()$add_worksheet()$
  add_data(x = formatting_rows)$
  add_dxfs_style(name = "highlight", text_italic = TRUE, font_color = wb_color('#b54c4a'))$
  add_conditional_formatting(
    dims = fruits,
    rule = "$I2=TRUE",
    style = "highlight"
  )$
  add_conditional_formatting(
    dims = veggie,
    rule = "$J2=TRUE",
    style = "highlight"
  )

if (interactive()) wb$open()