excel cell ex: A5 value as input in the shiny Rstudio numeric input

shiny
,
1

Hi
how can I call excel file cell value ex A5 cell value as numeric input in the shiny R studio
once the user input numeric number in shiny cell >> it should automatically be calculated in excel and print the result in shiny R studio as following?

process:
Excel sheet cells A5 +A6 = A7
shiny R studio insert A5 A6 input values in shiny
print the values in Excel sheet
calculate the result in cell A7
print the result back in Shiny R Studio

how can I call A5 A6 A7 from Excel sheet as input values in shiny R studio

thanks in advance for your reply

2

Hello,

Take a look at the Tidyxl package. I think this will help you with what you like to accomplish.
https://cran.r-project.org/web/packages/tidyxl/vignettes/tidyxl.html

Hope this helps,
PJ

1 Like
3

thanks alot for the quick reply I will check it and hit solution button if solved

Ahmed

4

now I can locate a specific excel sheet ex: A5 using tidyxl library , but how can I use excel cell A5 value as numeric input in shiny app?
thanks in advance

5

Hi,

Here is a simple example:

library(tidyxl)
library(dplyr)
library(shiny)

ui <- fluidPage(
  numericInput("myInput", "Value", value = 0)
)

server <- function(input, output, session) {
  shinyApp(ui, server)
  #This is just needed to access the xlsx file that comes with package
  testFile <- system.file("extdata/examples.xlsx", package = "tidyxl")
  
  #Use the path to your file here instead of testFile
  myXlsxFile = xlsx_cells(testFile, sheets = 1)
  
  #Pull whatever value you need
  newValue = myXlsxFile %>% filter(address == 'A5') %>% pull(numeric)
  
  #Update the value
  updateNumericInput(session, "myInput", value = newValue)
}

shinyApp(ui, server)

Hope this helps!
PJ

1 Like
6

thanks alot PJ for the illustrated example,
I have another question
is it possible to import the user numeric input values into the excel sheet again and run the equation embedded in the cell then pull the updated result again to the Shiny app for example

in excel A3 cell contains an equation " A1 cell + A2 cell "
in shinyapp can I use the A1 and A2 values imported by the user into the excel sheet and present the A3 value result in the shiny app output automatically?

best regards
Ahmed

7

Hi,

You can use the xlex function to decompose excel functions and then use this to create R functions. There is a good tutorial here:
https://nacnudus.github.io/tidyxl/articles/smells.html

And a demo version of how the function works here:
https://duncan-garmonsway.shinyapps.io/xlex/

PJ

1 Like
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.