是否有类似的方法可以对Shiny中的excel中的多列进行条件格式设置 [英] Is there any similar approach to conditional formating for multiple columns from excel in Shiny

问题描述

我有一个从excel上传的数据框，并以闪亮的数据表形式显示。在excel中，我们使用了条件格式来根据单元格本身的值和其他单元格的值来更改单元格的颜色。因此，例如对于ID，X，Y列，逻辑如下：

I have a dataframe that is uploaded from excel and displayed as datatable in shiny, in excel we have used conditional formating to change the color of cells based on the values of both the cell itself and the value of other cells. So for example for columns ID, X,Y the logic is as follow:

• 如果-4< X ＜ 4且Y ＜ X，Y的10种颜色是粉红色


• elseifY Y> X，Y的10种颜色是粉红色


• else X =或Y = 那么X，Y的颜色是白色

我尝试使用DT程序包，但没有成功，有人可以帮助我解决这个问题这还是建议其他方法？预先感谢您
这是我的代码，具有可重现的数据框。

I tried to use DT package but with no success, could anyone help me to get through this or suggest some other approach? thank you in advance Here is my code with a reproducible dataframe.

ui <- shinyUI(fluidPage(
  titlePanel("Column Plot"),
  tabsetPanel(
    tabPanel("Upload File",
             titlePanel("Uploading Files"),
             sidebarLayout(
               sidebarPanel(
                 fileInput('file1', 'Choose xlsx file',
                           accept = c(".xlsx")
                 ),
                 tags$br(),
                 checkboxInput('header', 'Header', TRUE),
                 radioButtons('sep', 'Separator',
                              c(Comma=',',
                                Semicolon=';',
                                Tab='\t'),
                              ','),
                 radioButtons('quote', 'Quote',
                              c(None='',
                                'Double Quote'='"',
                                'Single Quote'="'"),
                              '"')

               ),
               mainPanel(
                 tableOutput('contents')
               )
             )
    ),
tabPanel("contents_extra",
             pageWithSidebar(
               headerPanel('contents_extra'),
               sidebarPanel(
                 checkboxInput('test', 'test', TRUE)

               ),
               mainPanel(
                 dataTableOutput('contents_extra')
               )
             )
    ),
output$contents_extra <- renderDataTable({
      df <- data.frame(
      id = 1:10, 
      X = c(-2, 4, 40, -0.1228, 2.9, 9, 2.7, 2.7, 31, -30),
      Y = c(-18.9, -19.5, 19.6, 12, 11.1, 73, 4.3, 39, 2.5, 1.6),
      A = c(-7.3, 5.1 ,0.12, 15, 21, 1.2, -0,07, 4.3, 39, 2.5) 
      B = c(-18.9, 0.12, 15, 11.1, 73, -2, 4, 40, -19.5, 19.6)
      C = c(4.3, 39, 2.5, 1.6, -7.3, 6, 5.1 ,0.12, -0.07, 4.3)
      library(DT)
      options(DT.options = list(pageLength = 100))
      datatable(df, options = list(
      columnDefs = list(list(targets = X, visible = TRUE)))) %>% formatStyle(
      columns = c("X","Y"),
      valueColumns = c("X","Y"),
      backgroundColor = styleEqual(c(X > -4 && X < 4 && Y < 10, Y > 10, X ="" or Y=""), c('pink', 'bleu','white'))
       )
    })

推荐答案

这是关于 DT 的问题（答案是无论您是否使用 shiny ，都一样）。

This is a question about DT (answer is the same whether you use shiny or not).

在R中导出颜色更容易。然后使用 rowCallback 。

It's easier to derive the colors in R. Then use a rowCallback.

library(DT)
df <- data.frame(
  id = 1:10, 
  X = c(-2, 4, 40, -0.1228, 2.9, 9, 2.7, 2.7, 31, -30),
  Y = c(-18.9, -19.5, 19.6, 12, 11.1, 73, 4.3, 39, 2.5, 1.6)
)
colors <- with(df, ifelse(X > -4 & X < 4 & Y < 10, 
                          "pink", 
                          ifelse(Y > 10, 
                                 "blue", "white")))

rgbcolors <- apply(grDevices::col2rgb(colors), 2, 
                   function(rgb) sprintf("rgb(%s)", paste(rgb, collapse=",")))
columns <- c(2,3) # columns X and Y
jscode <- 
  paste("function(row, data, index) {",  
        sprintf("var colors=%s;\n%s", 
                sprintf("[%s]", 
                        paste(sprintf("'%s'", rgbcolors), collapse=", ")), 
                paste(sprintf("$(this.api().cell(index, %s).node()).css('background-color', colors[index]);", 
                              columns), collapse="\n")), 
        "}", sep="\n")
datatable(df, escape=FALSE, 
          options = list(rowCallback=JS(jscode))
)

创建的Javascript代码为：

The created Javascript code is:

> cat(jscode)
function(row, data, index) {
var colors=['rgb(255,192,203)', 'rgb(255,255,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(255,192,203)', 'rgb(0,0,255)', 'rgb(255,255,255)', 'rgb(255,255,255)'];
$(this.api().cell(index, 2).node()).css('background-color', colors[index]);
$(this.api().cell(index, 3).node()).css('background-color', colors[index]);
}

这篇关于是否有类似的方法可以对Shiny中的excel中的多列进行条件格式设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！