openxlsx: why is applying styling to an entire sheet/range impossible?-CodePudding

according to documentation and numerous answers here, you need to apply styling to individual row and column indices, passed as lists to addStyle.

this seems to be true of many other excel libraries. according to openpyxl, styling needs to be applied to individual cells as a restriction of the file format.

formatting this way has the effect of bloating the filesize of any excel spreadsheet that has a large number of empty styled cells. meanwhile, using ctrl A to apply styling to an entire sheet in the excel application performs this styling efficiently.

does anyone know what's going on? why is it impossible to achieve this seemingly supported behavior?

I encountered this problem while attempting to apply a foreground color to all cells in a sheet with openxlsx. I read other answers, which indicated that styling must be applied to individual cells as a restriction of the xlsx format. After noticing the difference in filesize between the generated workbooks and files produced via the desktop app, I found myself questioning the answers I'd been given.

CodePudding user response：

It is possible to assign a style, though the library you are using has to support it. With openxlsx2 release 0.3.1 you can do it like this:

library(openxlsx2)
# Create the colors. It looks like every colored row must start with a cell in the requested
# color otherwise the A column is empty for this row.
# Colored rows do not share this restriction. All we need is a single row in a style
wb <- wb_workbook() %>% 
  wb_add_worksheet() %>% 
  wb_add_fill(dims = "D1", color = wb_colour("orange")) %>% 
  wb_add_fill(dims = "A4:A6", color = wb_colour("blue"))

# get the styles 1 and 2 in our case
wb %>% wb_get_cell_style(dims = "D1")
#> [1] "1"
wb %>% wb_get_cell_style(dims = "A4")
#> [1] "2"

# apply style 1 (simply assign the xml string)
wb$worksheets[[1]]$cols_attr <- "<col min=\"4\" max=\"6\" width=\"8.83203125\" style=\"1\"/>"

# apply style 2 (extract, modify and insert) 
rows <- wb$worksheets[[1]]$sheet_data$row_attr
rows$customFormat[rows$r %in% c(4:6)] <- "1"
rows$s[rows$r %in% c(4:6)] <- "2"
wb$worksheets[[1]]$sheet_data$row_attr <- rows

# check the final result
wb$open()