如何在Shiny DT datatable中按单元格背景色筛选列 类似Excel功能

实现逻辑 #

现有代码已经为每个需要背景色渲染的业务列生成了对应的隐藏标记列，1代表单元格为黄色背景，0代表白色背景。我们只需要将筛选条件和这些隐藏标记列的取值绑定，即可实现类似Excel的按背景色筛选效果。

完整可运行代码 #

library(shiny)
library(DT)
library(stringr)

# 原始数据构造
input_data <- data.frame(Record_Status = c("Modified","NO","NO","Modified","NO","NO","Modified","NO","NO"),
                         Field_Changed = c("Brand,ratio","Gender","Name","ratio,Name,Gender","cost","Brand,cost","ratio,cost","cost","Name"),
                         Brand = c(3,6,9,12,15,18,21,24,27),
                         ratio = c (1,2,3,4,5,6,7,8,9),
                         cost = c(3,6,9,12,15,18,21,24,27),
                         Name = c("A","B","C","A","B","C","A","B","C"),
                         Gender = c("A","B","C","A","B","C","A","B","C"),
                         stringsAsFactors = FALSE)

# 构造背景色标记隐藏列
dataCol_df <- ncol(input_data) 
dataColRng <- 3:dataCol_df
dataColNames <- names(input_data)[dataColRng]
argColRng <- (dataCol_df + 1):(dataCol_df * 2 -2)

df <- sapply(1:ncol(input_data),function(i) ifelse(input_data[[1]]=="Modified" & 
                                                     str_detect(input_data[[2]], names(input_data)[i]),
                                                   "1","0"))
df <- df[,-c(1,2)]
input_data <- data.frame(input_data, df)

# Shiny应用
shinyApp(
  ui = navbarPage("按背景色筛选测试",
                  sidebarLayout(
                    sidebarPanel(width = 3,
                      # 选择要筛选的列
                      selectInput("filter_col", "选择要筛选的列", choices = dataColNames),
                      # 筛选条件
                      checkboxInput("only_yellow", "仅显示该列黄色背景的行", value = FALSE)
                    ),
                    mainPanel(dataTableOutput('dt'))
                  )
  ),
  server = function(input, output, session) {
    # 响应式过滤数据
    filtered_data <- reactive({
      res <- input_data
      if(input$only_yellow){
        # 找到选中列对应的隐藏标记列位置
        target_flag_col <- argColRng[which(dataColNames == input$filter_col)]
        res <- res[res[[target_flag_col]] == "1", ]
      }
      res
    })
    
    output$dt = DT::renderDataTable(
      datatable(filtered_data(),
                options=list(columnDefs = list(list(visible=FALSE, targets=argColRng)))) %>% 
        formatStyle(columns = dataColRng,
                    valueColumns = argColRng,
                    backgroundColor = styleEqual(c('1', '0'), c("yellow", "white")))
    )}
)

使用说明 #

• 在左侧下拉框选择你要筛选的业务列
• 勾选「仅显示该列黄色背景的行」后，表格会自动过滤，只保留选中列单元格为黄色的记录
• 取消勾选即可恢复显示全部数据

内容的提问来源于stack exchange，提问作者am3010