Shiny App开发问题：Excel列名动态加载至下拉菜单失败

Hey there! Let's work through your Shiny App issues step by step—you're really close to getting this working! The main problems right now are how you're handling uploaded Excel files and populating those dropdown menus. Let's break this down:

First, Fixing the Dropdown Menus (Column Name Population) #

Your current approach calls read_excel(input$file) directly in each renderUI, which has two critical flaws:

• input$file is a dataframe of uploaded file metadata, not a single file path—so read_excel(input$file) throws an error immediately.
• Re-reading files every time dropdowns render is inefficient and prone to bugs.

Instead, we'll create reactive objects to store processed uploaded data and column names, so we only load files once and reuse that data everywhere.

Second, Fixing the getData Reactive Function #

Your current getData has a syntax error: you can’t put a for loop directly inside a list() call like that. We’ll use lapply to cleanly read and process each file instead.

Here's the Corrected Full Code #

library(rsconnect)
library(readxl)
library(shiny)
library(ggplot2)
library(shinythemes) # Don't forget to load this for themeSelector!

ui <- fluidPage(
  shinythemes::themeSelector(),
  titlePanel("Volunteer stats"),
  sidebarLayout(
    sidebarPanel(
      fileInput(inputId = "file", label = "Choose Excel file", multiple = TRUE),
      uiOutput("org_select"),
      uiOutput("num_select"),
      uiOutput("year_select"),
      textInput(inputId = "org_label", label = "X axis label"),
      textInput(inputId = "vols_label", label = "Y axis label"),
      textInput(inputId = "plot_title", label = "Chart title"),
      textInput(inputId = "pdf_title", label = "PDF title")
    ),
    mainPanel(
      plotOutput(outputId = "histogram")
    )
  )
)

server <- function(input, output) {
  # Reactive object to store all uploaded and processed Excel files
  uploaded_data <- reactive({
    req(input$file) # Only run this if files are uploaded
    
    lapply(input$file$datapath, function(file_path) {
      # Read the Excel file
      xl_file <- read_excel(file_path, header = TRUE)
      
      # Apply your shift logic to the 'identity' column (add check to avoid errors)
      if ("identity" %in% colnames(xl_file)) {
        shift <- function(x, n) c(x[-(seq(n))], rep(NA, n))
        xl_file$identity <- shift(xl_file$identity, 1)
        # Remove the last row (since we shifted up)
        xl_file <- xl_file[-nrow(xl_file), ]
      }
      
      xl_file
    })
  })
  
  # Reactive object to get column names (uses first file's columns; adjust if files differ)
  column_names <- reactive({
    req(uploaded_data()) # Only run if data is loaded
    colnames(uploaded_data()[[1]])
    # If files have different columns, use this instead:
    # unique(unlist(lapply(uploaded_data(), colnames)))
  })
  
  # Render organization column dropdown
  output$org_select <- renderUI({
    selectInput(
      inputId = "org_col",
      label = "Which column has the organization name?",
      choices = column_names()
    )
  })
  
  # Render volunteer metric dropdown
  output$num_select <- renderUI({
    selectInput(
      inputId = "num_vols",
      label = "Which column has the relevant metric?",
      choices = column_names()
    )
  })
  
  # Render year column dropdown
  output$year_select <- renderUI({
    selectInput(
      inputId = "year",
      label = "Which column has the year?",
      choices = column_names()
    )
  })
  
  # Combine all uploaded data into a single dataframe for plotting
  combined_data <- reactive({
    req(uploaded_data())
    do.call(rbind, uploaded_data())
  })
  
  # Example grouped bar chart (matches your requirement of side-by-year columns)
  output$histogram <- renderPlot({
    req(combined_data(), input$org_col, input$num_vols, input$year)
    
    ggplot(combined_data(), aes(x = .data[[input$org_col]], y = .data[[input$num_vols]], fill = .data[[input$year]])) +
      geom_col(position = "dodge") +
      labs(
        x = input$org_label %||% input$org_col,
        y = input$vols_label %||% input$num_vols,
        title = input$plot_title %||% "Volunteer Stats by Organization",
        fill = "Year"
      ) +
      theme(axis.text.x = element_text(angle = 45, hjust = 1))
  })
}

shinyApp(ui = ui, server = server)

Key Changes Explained #

1. uploaded_data Reactive: Uses lapply to read each uploaded file, applies your shift logic safely (with a check for the identity column), and stores all processed files in a list. req(input$file) ensures this only runs when files are uploaded.
2. column_names Reactive: Pulls column names from the first uploaded file (swap in the commented line if your files have different columns).
3. Dropdown Rendering: Each dropdown now uses column_names() for choices—this will automatically populate once files are uploaded, no more errors!
4. combined_data Reactive: Merges all uploaded files into one dataframe, making ggplot plotting straightforward.
5. Example Plot: Added a grouped bar chart (side-by-side year columns) using .data[[input$col]] to safely reference user-selected columns. The %||% operator uses your custom labels if provided, or falls back to column names.

Quick Next Steps #

• If your Excel files have mismatched columns, update the column_names reactive to include all unique columns.
• Double-check the shift logic does what you need (it looks like you’re handling merged cells—adjust if needed).
• For PDF downloads, add a downloadButton and use ggsave() with input$pdf_title to generate the file.

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