如何在RShiny中基于单元格颜色属性读取Excel数据（仅读取白色背景单元格）

To solve this problem, we need to access cell styles (specifically background color) from the Excel file—something the readxl package doesn’t support. Instead, we’ll use the openxlsx package, which lets us extract formatting and filter cells based on their fill color.

Here’s the complete modified Shiny app:

# Install openxlsx if you haven't already
# install.packages("openxlsx")

library(shiny)
library(openxlsx)

runApp(
  list(
    ui = fluidPage(
      titlePanel("Read Only White Background Cells from Excel"),
      sidebarLayout(
        sidebarPanel(
          fileInput('file1', 'Choose xlsx file', accept = c(".xlsx") )
        ),
        mainPanel(
          tableOutput('contents'))
      )
    ),
    server = function(input, output){
      output$contents <- renderTable({
        req(input$file1)
        inFile <- input$file1
        
        # Load the Excel workbook
        wb <- loadWorkbook(inFile$datapath)
        target_sheet <- 1
        
        # Read the data (first row is treated as headers by default)
        df <- read.xlsx(inFile$datapath, sheet = target_sheet)
        
        # Get the range of cells that actually contain data/formatting
        used_range <- getUsedRange(wb, sheet = target_sheet)
        
        # Extract styles for every cell in the used range
        cell_styles <- getCellStyles(wb, sheet = target_sheet, 
                                     rows = used_range$rows, cols = used_range$cols)
        
        # Check if each cell has a white background (including default no-fill cells)
        is_white_cell <- sapply(cell_styles, function(style) {
          fill_style <- style$fill
          # Default cells have no fill (treated as white) OR explicit white fill
          is.null(fill_style) || (fill_style$fgFill == "#FFFFFF")
        })
        
        # Exclude header row cells (our df starts from the second sheet row)
        is_white_data_cells <- is_white_cell[-(1:ncol(df))]
        
        # Convert boolean vector to a matrix matching the data frame's shape
        white_matrix <- matrix(is_white_data_cells, nrow = nrow(df), ncol = ncol(df), byrow = FALSE)
        
        # Set non-white cells to NA (ignore their values)
        df[!white_matrix] <- NA
        
        df
      })
    }
  )
)

Key Details Explained: #

1. Why openxlsx?
   Unlike readxl, openxlsx provides tools to access cell formatting (like background color), which is critical for filtering cells based on their fill.

2. Identifying White Cells:
   We consider two scenarios as "white":

   • Cells with no fill (the default Excel state, which appears white).
   • Cells with an explicit pure white fill (hex code #FFFFFF).

3. Handling Headers:
   When reading data, read.xlsx uses the first Excel row as column names. We exclude these header cells from our style check since they aren’t part of the data rows in the output data frame.

4. Filtering Non-White Cells:
   We convert our list of boolean values (white/not white) into a matrix that matches the data frame’s dimensions. Then we set all non-white cells to NA, effectively ignoring their values in the output table.

Edge Cases to Keep in Mind: #

• Merged Cells: openxlsx applies the style of the top-left cell to the entire merged range. If that cell isn’t white, all cells in the range will be set to NA.
• Off-White Shades: This solution only targets pure white (#FFFFFF). If your "white" cells use an off-white color, adjust the hex code in the fill_style$fgFill check to match.

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