R Shiny中结合提交按钮基于数值输入生成datatable的实现方法

1. UI部分修改 #

需要新增2个组件：提交按钮、表格输出容器，调整后完整UI代码如下：

ui <- fluidPage(
  numericInput("HomePrice", "Home Price", value = NA, min = 0, max = NA, step = 50),
  numericInput("DownPaymentDollars", "Downpayment (Dollars)", value = NA, min = 0, max = NA, step = 50, width = NULL),
  numericInput("DownPaymentPercent", "Downpayment (Percent)", value = NA, min = 0, max = 100, step = 0.05, width = NULL),
  numericInput("LoanAmount", "Loan Amount", value = NA, min = 0, max = NA, step = 50, width = NULL),
  numericInput("InterestRate", "Interest Rate", value = NA, min = 0, max = NA, step = 0.0005, width = NULL),
  numericInput("TermYears", "Term (Years)", value = NA, min = 0, max = NA, step = 0.5, width = NULL),
  numericInput("MonthlyPrincipleInterest", "Monthly Principle + Interest", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  numericInput("PMIRate", "Annual PMI Rate", value = NA, min = 0, max = NA, step = 0.0005, width = NULL),
  numericInput("MonthlyPMI", "Monthly PMI (Escrow)", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  numericInput("MonthlyTaxes", "Monthly Taxes (Escrow)", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  numericInput("MonthlyEscrow", "Monthly Escrow", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  numericInput("MinimumPayment", "Minimum Monthly Payment", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  numericInput("ExtraMonthlyPayment", "Extra Monthly Payment", value = NA, min = 0, max = NA, step = 0.005, width = NULL),
  # 新增提交按钮
  actionButton("submit_btn", "生成还款计划表"),
  # 新增表格输出容器
  DT::dataTableOutput("repayment_table")
)

*说明：把原来输入框的默认值从空字符串改为NA，避免数值类型转换报错。

2. Server部分修改 #

需要做3处调整：

• 保留原有输入联动更新逻辑不变，新增req()空值校验避免参数为空时的报错
• 新增提交按钮触发的还款计划生成逻辑，只有点击按钮才会执行计算
• 新增表格渲染逻辑，把生成的数据框输出到前端
  调整后完整Server代码如下：

server = function(input, output, session){
  # 原有输入联动逻辑保留
  observeEvent({
    input$HomePrice
    input$DownPaymentDollars
  }, {
    req(input$HomePrice, input$DownPaymentDollars)
    updateNumericInput(session, "DownPaymentPercent", value = input$DownPaymentDollars * 100 / input$HomePrice)
  })
  
  observeEvent({
    input$HomePrice
    input$DownPaymentPercent
  }, {
    req(input$HomePrice, input$DownPaymentPercent)
    updateNumericInput(session, "DownPaymentDollars", value = input$DownPaymentPercent * input$HomePrice / 100)
  })
  
  observeEvent({
    input$HomePrice
    input$DownPaymentDollars
  }, {
    req(input$HomePrice, input$DownPaymentDollars)
    updateNumericInput(session, "LoanAmount", value = input$HomePrice - input$DownPaymentDollars)
  })
  
  observeEvent({
    input$LoanAmount
    input$InterestRate
    input$TermYears
  }, {
    req(input$LoanAmount, input$InterestRate, input$TermYears)
    updateNumericInput(session, "MonthlyPrincipleInterest", value = input$LoanAmount*((input$InterestRate/100/12)*((1+(input$InterestRate/100/12))^(input$TermYears*12)))/(((1+(input$InterestRate/100/12))^(input$TermYears*12))-1))
  })
  
  observeEvent({
    input$LoanAmount
    input$PMIRate
  }, {
    req(input$LoanAmount, input$PMIRate)
    updateNumericInput(session, "MonthlyPMI", value = (input$PMIRate / 100) / 12 * input$LoanAmount)
  })
  
  observeEvent({
    input$LoanAmount
    input$MonthlyPMI
  }, {
    req(input$LoanAmount, input$MonthlyPMI)
    updateNumericInput(session, "PMIRate", value = input$MonthlyPMI / input$LoanAmount * 12 * 100)
  })

  observeEvent({
    input$MonthlyPMI
    input$MonthlyTaxes
  }, {
    req(input$MonthlyPMI, input$MonthlyTaxes)
    updateNumericInput(session, "MonthlyEscrow", value = input$MonthlyPMI + input$MonthlyTaxes)
  })
  
  observeEvent({
    input$MonthlyPrincipleInterest
    input$MonthlyEscrow
  }, {
    req(input$MonthlyPrincipleInterest, input$MonthlyEscrow)
    updateNumericInput(session, "MinimumPayment", value = input$MonthlyPrincipleInterest + input$MonthlyEscrow)
  })
  
  # 提交按钮触发生成表格
  output$repayment_table <- DT::renderDataTable({
    # 点击提交按钮才触发
    input$submit_btn
    isolate({
      # 校验所有必填参数非空，缺项不执行计算
      req(input$TermYears, input$LoanAmount, input$MonthlyPMI, input$MonthlyTaxes, 
          input$InterestRate, input$MonthlyPrincipleInterest, input$ExtraMonthlyPayment, input$HomePrice)
      
      Month <- seq(1, input$TermYears*12, 1)
      df <- data.frame(Month)
      df$PreviousBalance <- input$LoanAmount
      df$PMI <- input$MonthlyPMI
      df$Taxes <- input$MonthlyTaxes
      df$Interest <- df$PreviousBalance*(input$InterestRate/100/12)
      df$Principal <- input$MonthlyPrincipleInterest-df$Interest+input$ExtraMonthlyPayment
      df$EquityDollars <- input$HomePrice-df$PreviousBalance+df$Principal
      df$EquityPercent <- (df$EquityDollars/input$HomePrice)*100
      df$EndingBalance <- df$PreviousBalance-df$Principal
      x <- seq(1, input$TermYears*12, 1)
      for (i in x) {
        if(i > 1) {
          df$PreviousBalance[i] <- df$EndingBalance[i-1]
          if(df$EquityPercent[i-1]<20) {
            df$PMI[i] <- input$MonthlyPMI
          }
          else {
            df$PMI[i] <- 0
          }
          df$Taxes[i] <- input$MonthlyTaxes
          df$Interest[i] <- df$PreviousBalance[i]*(input$InterestRate/100/12)
          if(df$PreviousBalance[i]>df$Principal[i]) {
            df$Principal[i] <- input$MonthlyPrincipleInterest-df$Interest[i]+input$ExtraMonthlyPayment
          }
          else {
            df$Principal[i] <- df$PreviousBalance[i]
          }
          df$EquityDollars[i] <- input$HomePrice-df$PreviousBalance[i]+df$Principal[i]
          df$EquityPercent[i] <- (df$EquityDollars[i]/input$HomePrice)*100
          df$EndingBalance[i] <- df$PreviousBalance[i]-df$Principal[i]
        }
      }
      # 过滤掉贷款结清后的无效行
      df <- df[df$EndingBalance >= 0, ]
      # 数值保留2位小数优化展示
      df[, -1] <- round(df[, -1], 2)
      return(df)
    })
  }, options = list(pageLength = 15))
}

3. 依赖安装 #

首次运行需要提前安装表格渲染依赖包，执行一次install.packages("DT")即可。

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