• 注意：* 使用此代码，您可以指定更具体的十六进制颜色值。例如“#00FF00”而不是“绿色”。

library(purrr)
library(openxlsx)
library(tidyr)

wide <- long_data |>
  pivot_wider(id_cols = digits, names_from = category, values_from = value)

colors <- long_data |>
  pivot_wider(id_cols = digits, names_from = category, values_from = color_code)

color_loc <- map(set_names(unique(long_data$color_code)), ~ as.data.frame(which(colors == .x, arr.ind = TRUE)))
color_style <- imap(color_loc, ~ createStyle(fontColour = .y))

wb <- write.xlsx(wide, "output.xlsx")
iwalk(color_loc, ~ addStyle(wb, 1, color_style[[.y]], rows = .x$row + 1, cols = .x$col))                    
saveWorkbook(wb, "output.xlsx", overwrite = TRUE)

1. wide是宽格式数据，colors是相同的结构化数据，但是具有单元级颜色作为值。

> wide
#   digits     A     B     C
#    <int> <dbl> <dbl> <dbl>
# 1      1    10    20    30
# 2      2    15    25    35
# 3      3    12    22    32
# 4      4    18    28    38
# 5      5    14    24    34

> colors
#   digits A     B     C    
#    <int> <chr> <chr> <chr>
# 1      1 red   red   red  
# 2      2 red   blue  blue 
# 3      3 blue  blue  blue 
# 4      4 green green green
# 5      5 green green green

1.对于每个唯一的颜色，我们得到该颜色的所有单元格的行和列索引位置。因此，如果您更改颜色，添加颜色和/或删除颜色，则此代码将工作：

> color_loc
# $red
#   row col
# 1   1   2
# 2   2   2
# 3   1   3
# 4   1   4
# 
# $blue
#   row col
# 1   3   2
# 2   2   3
# 3   3   3
# 4   2   4
# 5   3   4
# 
# $green
#   row col
# 1   4   2
# 2   5   2
# 3   4   3
# 4   5   3
# 5   4   4
# 6   5   4

1.对于每种颜色，我们创建一个工作表样式color_style。
1.最后，在创建工作簿对象（wb）之后，我们将each样式应用于特定的行和列索引（row + 1表示Excel中的标题行）。

输出

library(tidyverse)

# Pivot data as described
wide_data <-
  long_data |>
  pivot_wider(names_from = category,
              values_from = c(value, color_code))

library(openxlsx)

# Prepare the sheet with the data
wb <- createWorkbook()
addWorksheet(wb, "sheet1")
# we only write the values, not the color columns to the sheet
writeData(wb, "sheet1", wide_data[1:4])

# function that creates a style for the supplied color
color_style <- function(x)
  createStyle(fontColour = x)

# function that iterates through column cells and applies font color styles
color_cells <-
  function(colors, col_index, row_index)
    walk2(
      colors,
      row_index,
      \(x, y) addStyle(
        wb,
        "sheet1",
        color_style(x),
        cols = col_index,
        rows = y + 1
      )
    )

# put it all together and apply it to the value columns
walk2(wide_data[5:7],
     LETTERS[2:4],
     \(x, y) color_cells(x, y, seq_along(x)))

# save workbook
saveWorkbook(wb, "color.xlsx", overwrite = TRUE)