javascriptrshinyhandsontablerhandsontable

How to Select a Cell Range in rhandsontable Using Text Input in a Shiny App?


I am currently working on a Shiny app in R that incorporates an rhandsontable element. I would like to add a feature where a user can specify a cell range (e.g., "A1:B2") in a text input field, and the rhandsontable would then select that range on the table.

Here's a simplified version of my current code:

library(shiny)
library(rhandsontable)

ui <- fluidPage(
    textInput("cell_range", "Enter cell range:"),
    rHandsontableOutput("table")
)

server <- function(input, output) {
    output$table <- renderRHandsontable({
        df <- data.frame(A = 1:5, B = 6:10, C = 11:15)
        rhandsontable(df)
    })
}

shinyApp(ui = ui, server = server)

This is the expected outcome:

enter image description here

I'm not sure how to proceed with the cell range selection part. I've looked through the rhandsontable documentation but couldn't find a straightforward way to do this.


Solution

  • Here is a way but it does not allow to enter an Excel cell range. Instead, you have to enter the cell range as rowStart:columnStart:rowEnd:columnEnd.

    library(shiny)
    library(rhandsontable)
    library(htmlwidgets)
    
    js <- c(
      "function(el, x) {",
      "  let hot = this.hot;",
      "  $('#cell_range').on('keyup', function(e) {",
      "    if(e.keyCode == 13) {",
      "      let cellRange = $('#cell_range').val();",
      "      let x = cellRange.split(':');",
      "      let rowStart    = parseInt(x[0]) - 1;",
      "      let columnStart = parseInt(x[1]) - 1;",
      "      let rowEnd      = parseInt(x[2]) - 1;",
      "      let columnEnd   = parseInt(x[3]) - 1;",
      "      hot.selectCells([[rowStart, columnStart, rowEnd, columnEnd]]);",
      "    }",
      "  });",
      "}"
    )
    
    ui <- fluidPage(
      textInput(
        "cell_range", 
        "Enter cell range (rowStart:columnStart:rowEnd:columnEnd) and press Enter:"
      ),
      rHandsontableOutput("table")
    )
    
    server <- function(input, output) {
      output$table <- renderRHandsontable({
        df <- data.frame(A = 1:5, B = 6:10, C = 11:15)
        rhandsontable(df) %>% onRender(js)
      })
    }
    
    shinyApp(ui = ui, server = server)
    

    If you want to trigger the range selection with a button rather than a press on the Enter key, you can do:

    js <- c(
      "function(el, x) {",
      "  let hot = this.hot;",
      "  $('#submit').on('click', function(e) {",
      "      let cellRange = $('#cell_range').val();",
      "      let x = cellRange.split(':');",
      "      let rowStart    = parseInt(x[0]) - 1;",
      "      let columnStart = parseInt(x[1]) - 1;",
      "      let rowEnd      = parseInt(x[2]) - 1;",
      "      let columnEnd   = parseInt(x[3]) - 1;",
      "      hot.selectCells([[rowStart, columnStart, rowEnd, columnEnd]]);",
      "  });",
      "}"
    )
    
    ui <- fluidPage(
      textInput(
        "cell_range", 
        "Enter cell range (rowStart:columnStart:rowEnd:columnEnd) and press the button:"
      ),
      actionButton("submit", "Submit"),
      rHandsontableOutput("table")
    )