Excel data via Shiny

[Murali Dhar]

New to Shiny. I downloaded Shiny server on Red Hat and running fine. I would like to visualize my excel data with some calculations. How I do that? Can you help us please?

[Joe Cheng]

Start with creating a Shiny application locally to visualize your Excel data (I'd start by exporting from Excel to .csv and importing that into R using read.csv). Don't worry about deploying using Shiny Server until you have an app working locally.

On Fri, Jul 26, 2013 at 9:38 PM, Murali Dhar <mrag...@gmail.com> wrote:

New to Shiny. I downloaded Shiny server on Red Hat and running fine. I would like to visualize my excel data with some calculations. How I do that? Can you help us please?

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Huidong TIAN]

Hi Joe,

  I have the same question about uploading an Excel file to shiny. I wrote a simple application, and it works well locally under Windows, however when deployed it to shiny-server, it doesn't work at all: the interactive file selection window never appear. could you help check where is the bug? the following is my code:

###################### ui.R

shinyUI(bootstrapPage(

  ## Inputs:

  actionButton('upload', 'Upload Data:'),

  uiOutput("uiSheet"),

  

  ## Outputs:

  div(class='upload', tableOutput(outputId = "head"))

))

 

########################server.R

require(shinyIncubator)

shinyServer(function(input, output) {

  uploadData <- reactive({

    if (input$upload > 0){

      fileName <- file.choose()

      # Extract the suffix of the file;

      ptn <- "\\.[[:alnum:]]{1,4}$"

      Suf <- c(".txt", ".csv", ".xls", ".xlsx")

      suf <- tolower(regmatches(fileName, regexpr(ptn, fileName)))

      if (suf %in% Suf) {

        # .txt

        if (suf == '.txt'){

          output$uiSheet <- renderUI({

            list(

              checkboxInput('header', 'First line as header', TRUE),

              textInput(inputId = 'sep', label = 'Separator', value = " "),

              textInput(inputId = 'quote', label = 'Quote', value = '"')

            )

          })

        } else if (suf == '.xls' | suf == '.xlsx') {

          require(XLConnect)

          wb <- loadWorkbook(fileName)

          sheets <- getSheets(wb)

          output$uiSheet <- renderUI({

            selectInput(inputId = "sheet", label = "Select a sheet:", choices = sheets)

          })

        }   

      } else {

        stop("This document format is not supported currently!")

      }

      return(list(suf = suf, fileName = fileName))

    } else {

      return(NULL)

    }

  })

  output$head <- renderTable({

    Dat <- uploadData()

    if (!is.null(Dat)) {

      suf <- Dat$suf

      fileName <- Dat$fileName

      if (suf %in% c('.xls', '.xlsx')) {

        sheetName <- input$sheet

        wb <- loadWorkbook(fileName)

        Dat$data <- readWorksheet(wb, sheetName) 

      }

      if (suf %in% c('.txt', '.csv')) {

        Dat$data <- read.csv(fileName, header=input$header, sep=input$sep, quote=input$quote)

      }

      head(Dat$data, 10)

    } else NULL

  })

})

Thanks.

Huidong'

[Huidong TIAN]

I replace the 'file.choose()' with "tk_choose.files", because the first one doesn't work under Linux. However, when deployed to shiny-server, it said: could not find function "tk_choose.files", while if I run it locally, it works well.
How to let shiny-server know where to find function 'tk_choose.files'?

Regards!

[Joe Cheng]

You can't use file.choose() when deploying a web application. That runs a function on the server, not the client. Instead you need to use the file uploading features of Shiny. http://rstudio.github.io/shiny/tutorial/#uploads

[Huidong TIAN]

Hi,

  The function fileInput can upload text data, like .txt and .csv, but can it upload an Excel file? could you please give an example, I have little knowledge about HTML and Server.

Best!

--
You received this message because you are subscribed to a topic in the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/shiny-discuss/Mj2KFfECBhU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to shiny-discus...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Huidong Tian

Cancer Registry of Norway.

Phone: +47 40624112  

Email: tienh...@gmail.com

[Huidong TIAN]

Hi, I figured it out using the following code:

 inFile <- input$file1

    if (!is.null(inFile)) {

      require(XLConnect)

      wb <- loadWorkbook(inFile$datapath)

      sheets <- getSheets(wb)

      dat <- readWorksheet(wb, 'Patologi')

      head(dat)   

    }

    

[Jerônimo Guasselli]

Hello, 

I am new on shiny and I am trying to use this code you wrote, but with a header panel e sidebar panel as the example upload file in the tutorial building shiny, but of course able to read xlsx. In R I know how to read xlsx tables, but on shiny the only code I saw about that was your code.

Is that possible? or can you just use this code with bootstrapPage? I am asking because after that I want to use check box to select my inputs and outputs as my columns. 

Thank you very much.

[Huidong TIAN]

# Author: Huidong Tian

# Email: tienh...@gmail.com

library(XLConnect)

shinyServer(function(input, output) {  

  chooseFile <- reactive({

    inFile <- input$iFile

    if (!is.null(inFile)) {

      # Determine document format;

      ptn <- "\\.[[:alnum:]]{1,5}$"

      suf <- tolower(regmatches(inFile$name, regexpr(ptn, inFile$name)))

      

      # Options for Excel documents;

      if (suf %in% c('.xls', '.xlsx')) {

        wb <- loadWorkbook(inFile$datapath)

        sheets <- getSheets(wb)

        output$ui <- renderUI({

          list(

            selectInput(inputId = "sheet", label = "Select a sheet:", choices = sheets),

            textInput(inputId = 'arg', label = 'Additional Arguments:', value = ' '),

            tags$hr()

          )

        })

        return(list(path = inFile$datapath, suf = suf))

      } 

      

      # Options for txt documents;

      if (suf %in% c('.txt', '.csv')) {

        output$ui <- renderUI({

          list(

            checkboxInput(inputId = 'header', label = 'First line as header', value = TRUE),

            textInput(inputId = 'sep', label = 'Separator', value = " "),

            textInput(inputId = 'quote', label = 'Quote', value = '\"'),

            textInput(inputId = 'arg', label = 'Additional Arguments:', value = ' '),

            tags$hr()

          )

        })

        return(list(path = inFile$datapath, suf = suf))

      }

    } else {return(NULL)}

  })

  

  

  output$contents <- renderTable({

    objFile <- chooseFile()

    if (!is.null(objFile)) {

      suf <- objFile$suf

      # For Excel documents;

      if (suf %in% c('.xls', '.xlsx')) {

        Sheet <- input$sheet

        if (!is.null(Sheet)){

          

          if (input$arg %in% c(' ', '')) {

            wb <- loadWorkbook(objFile$path)

            dat <- readWorksheet(wb, Sheet)

            return(dat)

          } else {

            wb <- loadWorkbook(objFile$path)

            expr <- paste('readWorksheet(wb, Sheet,', input$arg, ')', sep = '')

            print(expr)

            dat <- eval(parse(text = expr))

            return(dat)

          }

            

        } else {return(NULL)}

      }

      # For .txt and .csv documents;

      if (suf %in% c('.txt', '.csv')) {

        if (is.null(input$header)) {

          dat <- read.table(objFile$path)

          return(dat)

        } else {

          if (input$arg %in% c(' ', '')) {

            dat <- read.table(objFile$path, header=input$header, sep=input$sep, quote=input$quote)

            return(dat)

          } else {

            expr.1 <- paste('"', gsub('\\', '/', objFile$path, fixed = TRUE), '"', sep = '')

            expr.2 <- paste(expr.1, 

                            paste('header =', input$header), 

                            paste('sep =', paste("'", input$sep, "'", sep = '')), 

                            paste('quote =', paste("'", input$quote, "'", sep = '')), input$arg,  sep = ', ')

            print(expr.2)

            expr <- paste('read.table(', expr.2, ')', sep = '')

            print(expr)

            dat <- eval(parse(text = expr))

            return(dat)

          }

        }

      }

      

    } else {return(NULL)}

  })

})

shinyUI(pageWithSidebar(

  # Include css file;

  tagList(

    tags$head(

      tags$title("Upload Data")

    )

  ), 

  # Control panel;

  sidebarPanel(

    fileInput(inputId = "iFile", label = "", accept="application/vnd.ms-excel"),

    tags$hr(),

    uiOutput(outputId = "ui"),

    submitButton("Upload!")

  ),

  # Output panel;

  mainPanel(tableOutput(outputId = "contents"))

))

[Stéphane Laurent]

I recommend such a code when using XLConnect:

    options(java.parameters = "-Xmx512m") 

    require(XLConnect) 

    wb <- loadWorkbook(XLfile)

    dat <- readWorksheet(wb, sheet = sheet, colTypes="character")

    xlcFreeMemory()

    detach("package:XLConnect", unload=TRUE)

It avoids some possible memory problems.

[Jerônimo Guasselli]

Your code is awesome, but I still have a question,  this tagList(.

I haven't found anything in the tutorial building shiny, is that a new kind of code for shiny?

Again I'm a beginner, if I'm asking something stupid let me know. I take your work very seriously and I'm trying to get good at shiny as well.

Best regards  

[Huidong TIAN]

You need to read the manual yourself to meet the most of useful functions. The shiny tutorial does not cover every aspect, and even the manual does not cover everything. Keep an eye on this forum, you will learn a lot that you will never get from the manual. Enjoy Shiny!

[Jerônimo Guasselli]

Hello Huidong, 

Here it's me again with some questions. I made some changes in the code you send, but with some difficulties.

Instead of an additional argument when uploading xls tables, I was trying to use a reactive  checkboxGroupInput with the name of the columns because I need to define my inputs for the calculus below. 

library(Benchmarking)

Here instead of data[c(2,3,4)], I would like to choose the columns of the uploaded table and then show the results in the summary.

inputs <- data.frame(data[c(2,3,4)]) # input variable at second column of the data matrix

outputs<-data.frame(data[5]) # output variables

N <- dim(data)[1] # the number of DMUs is equal to number of rows of data matrix

s <- dim(inputs)[2] # number of input variables, in this case s = 3

m <- dim(outputs)[2] # number of output variables, in this case m = 1

require(lpSolve)

f.rhs <- c(rep(0,1,N),1) 

f.dir <- c(rep("<=",1,N),"=") 

aux <- cbind(-1*inputs,outputs)

for (i in 1:N) { 

f.obj <- c(0*rep(1,s),as.numeric(outputs[i,]))

f.con <- rbind(aux ,c(as.numeric(inputs[i,]), rep(0,1,m)))

results <- lp ("max",as.numeric(f.obj), f.con, f.dir, f.rhs,scale=0, compute.sens=TRUE) 

if (i==1) {

weights <- results$solution

effcrs <- results$objval

lambdas <- results$duals[seq(1,N)] 

} else {

weights <- rbind(weights, results$solution) 

effcrs <- rbind(effcrs , results$objval) 

lambdas <- rbind(lambdas, results$duals[seq(1,N)] )

 } 

}

ui.R

shinyUI(pageWithSidebar(

  # Include css file;

  tagList(

    tags$head(

      tags$title("Upload Data"),

      tags$h1("Simple DEA - Uma analise simplificada de dados complexos")

    )

  ),

  # Control panel;

  sidebarPanel(

    radioButtons("model", "Escolha do Modelo:",

                 list("CRS" = "crs",

                      "VRS" = "vrs")),

    br(),

    fileInput(inputId = "iFile", label = "", accept="application/vnd.ms-excel"),

    tags$hr(),

    uiOutput(outputId = "ui"),

    uiOutput(outputId = "choose_columns"),

    submitButton("Atualizar")

  ),

  # Output panel;

  mainPanel(

      tabsetPanel(

        tabPanel("Tabela",tableOutput(outputId = "contents")),

        tabPanel("Resultados", verbatimTextOutput("summary"))

      )

  )

))

server.R

   output$choose_columns <- renderUI({

    # If missing input, return to avoid error later in function

    if(is.null(input$iFile))

      return()

 

    # Get the data set with the appropriate name

    dat <- get(input$iFile)

    colnames <- names(dat)

 

    # Create the checkboxes and select them all by default

    checkboxGroupInput("columns", "Escolha de Inputs", 

                        choices  = colnames,

                        selected = colnames)

Thank you very much

--
You received this message because you are subscribed to a topic in the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/shiny-discuss/Mj2KFfECBhU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Huidong TIAN]

Could you post your code and sample data as a Gist? That will let others run you code and test it easily. 

[Huidong TIAN]

Hi, You may need to read the tutorial carefully first. Here is the code: 

library(XLConnect)

shinyServer(function(input, output) { 

  Dat <- reactiveValues()

  observe({

    if (!is.null(input$iFile)) {

      inFile <- input$iFile

      wb <- loadWorkbook(inFile$datapath)

      sheets <- getSheets(wb)

      Dat$wb <- wb

      Dat$sheets <- sheets

    }

  })

  output$ui <- renderUI({

    if (!is.null(Dat$sheets)) {

      selectInput(inputId = "sheet", label = "Select a sheet:", choices = Dat$sheets)

    }

  })

  

  observe({

    if (!is.null(Dat$wb)) {

      if (!is.null(input$sheet)){

        dat <- readWorksheet(Dat$wb, input$sheet)

        print(names(dat))

        output$columns <- renderUI({

          checkboxGroupInput("columns", "Choose columns", 

                             choices  = names(dat))

        })

      }

    }

  })

})

shinyUI(pageWithSidebar(

  # Include css file;

  tagList(

    tags$head(

      tags$title("Upload Data"),

      tags$h1("Test")

    )

  ),

  

  # Control panel;

  sidebarPanel(

    fileInput(inputId = "iFile", label = "Escolha um arquivo:", accept="application/vnd.ms-excel"),

    radioButtons("model", "Escolha do Modelo:",

                 list("CRS" = "crs",

                      "VRS" = "vrs")),

    br(),

    tags$hr(),

    uiOutput(outputId = "ui"),

    uiOutput(outputId = "columns")

  ),

  # Output panel;

  mainPanel()

))

Best!

On Friday, March 14, 2014 12:12:22 AM UTC+1, Jerônimo Guasselli wrote:

[Jerônimo Guasselli]

Thank you very much.

I reading again the tutorial and also the cran shiny, if you have some more material that you may thik helpfull, I'll be glad to have it.

[Huidong TIAN]

http://www.rstudio.com/shiny/lessons/Intro/

[Jerônimo Guasselli]

Thank you very much,  that is the kind of thing that is gonna help a lot.

[Jerônimo Guasselli]

Hi every one, until yesterday I was using the package Xlconect. But since 00:00 I had this problem - Error in library(Xlconect) : there is no package called ‘Xlconect’
Please can someone help me?

ui.R
shinyUI(fluidPage(

titlePanel("DEA - Análise Envoltória de Dados"),
br(),

sidebarLayout(
sidebarPanel(radioButtons("model", "Escolha do Modelo:",

list("CRS" = "crs",
"VRS" = "vrs")),
br(),

br(),
fileInput(inputId = "iFile", label = "Escolha um Arquivo:", accept="application/vnd.ms-excel"),
uiOutput(outputId = "ui"),
uiOutput(outputId = "columns"),
uiOutput(outputId = "colum"),
submitButton("Upload!"),
br(),
br(),
br(),

img(src = "www.png", height = 300, width = 450, align = "center")
),




mainPanel(tabsetPanel(

tabPanel("Tabela",tableOutput(outputId = "contents")),
tabPanel("Resultados", verbatimTextOutput("summary")))
)

)))


server.R

library(Xlconect)

shinyServer(function(input, output) {

Jer <- reactiveValues()

observe({
if (!is.null(input$iFile)) {
inFile <- input$iFile
wb <- loadWorkbook(inFile$datapath)
sheets <- getSheets(wb)

Jer$wb <- wb
Jer$sheets <- sheets
}
})


observe({
if (!is.null(Jer$wb)) {
if (!is.null(input$sheet)){
Jer <- readWorksheet(Jer$wb, input$sheet)
print(names(Jer))
output$columns <- renderUI({
checkboxGroupInput("columns", span(strong("Escolher imputs"), style = "color:blue"),
choices = names(Jer))
})
}
}
})




Jer <- reactiveValues()

observe({
if (!is.null(input$iFile)) {
inFile <- input$iFile
wb <- loadWorkbook(inFile$datapath)
sheets <- getSheets(wb)

Jer$wb <- wb
Jer$sheets <- sheets
}
})


observe({
if (!is.null(Jer$wb)) {
if (!is.null(input$sheet)){
Jer <- readWorksheet(Jer$wb, input$sheet)
print(names(Jer))
output$colum <- renderUI({
checkboxGroupInput("columns", span(strong("Escolher outputs"), style = "color:green"),
choices = names(Jer))
})
}
}
})

chooseFile <- reactive({
inFile <- input$iFile
if (!is.null(inFile)) {
# Determine document format;
ptn <- "\\.[[:alnum:]]{1,5}$"
suf <- tolower(regmatches(inFile$name, regexpr(ptn, inFile$name)))

# Options for Excel documents;
if (suf %in% c('.xls', '.xlsx')) {
wb <- loadWorkbook(inFile$datapath)
sheets <- getSheets(wb)
output$ui <- renderUI({
list(
selectInput(inputId = "sheet", label = "Select a sheet:", choices = sheets)

)
})
return(list(path = inFile$datapath, suf = suf))
}

# Options for txt documents;
if (suf %in% c('.txt', '.csv')) {
output$ui <- renderUI({
list(
checkboxInput(inputId = 'header', label = 'First line as header', value = TRUE),
textInput(inputId = 'sep', label = 'Separator', value = " "),
textInput(inputId = 'quote', label = 'Quote', value = '\"'),
textInput(inputId = 'arg', label = 'Additional Arguments:', value = ' '),

)
})
return(list(path = inFile$datapath, suf = suf))
}
} else {return(NULL)}
})


output$contents <- renderTable({
objFile <- chooseFile()
if (!is.null(objFile)) {
suf <- objFile$suf
# For Excel documents;
if (suf %in% c('.xls', '.xlsx')) {
Sheet <- input$sheet

wb <- loadWorkbook(objFile$path)
dat <- readWorksheet(wb, Sheet)
return(dat)

}