An R interface to jExcel library to create web-based interactive tables and spreadsheets compatible with Excel or any other spreadsheet software.

To install the stable CRAN version:

To install the latest development version from GitHub:

library(devtools)
install_github('Swechhya/excelR')

Usage

Getting Started

Excel tables can be created using excelTable function as shown below:

library(excelR)

data = data.frame(A =c (1:5), B = c(LETTERS[1:5]))

excelTable(data=data)

Basic Usage

library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ),
                   Availability = c(TRUE, FALSE, TRUE, TRUE))

 columns = data.frame(title=c('Model', 'Date', 'Availability'),
                      width= c(300, 300, 300),
                      type=c('text', 'calendar', 'checkbox')) 
 excelTable(data=data, columns = columns)

Features

This documentation gives example for following features:

  1. Different types of columns
  2. Row and column resizing
  3. Drag and drop rows and columns
  4. Merge rows and columns
  5. Nested headers
  6. Pagination
  7. Search
  8. Excel formulas
  9. Style
  10. Automated Column Types
  11. Toolbar
  12. Shiny Integration

Different types of columns

  • Calendar
library(excelR)

data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                  Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ))

columns = data.frame(title=c('Model', 'Date' ),
                     width= c(300, 300),
                     type=c('text', 'calendar')) 
excelTable(data=data, columns = columns)

  • Checkbox
library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Availability = c(TRUE, FALSE, TRUE, TRUE))

 columns = data.frame(title=c('Model', 'Availability'),
                      width= c(300, 300),
                      type=c('text', 'checkbox')) 
 excelTable(data=data, columns = columns)

  • Dropdown
library(excelR)

 data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'), 
          Car = c('Civic', 'City', 'Polo'))

 columns = data.frame(title=c('Make', 'Car'),
                      width= c(300, 300),
                      type=c('text', 'dropdown'),
                      source=I(list(0,c('Civic', 'City',  'Polo', 'Creta', 'Santro'))))
 excelTable(data=data, columns = columns)

  • Radio
library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Choice = c(FALSE, FALSE, FALSE, TRUE))

 columns = data.frame(title=c('Model', 'Choice'),
                      width= c(300, 300),
                      type=c('text', 'radio')) 
 excelTable(data=data, columns = columns)

  • Color
library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Color= c('red', 'blue', 'yellow', 'black'))

 columns = data.frame(title=c('Model', 'Color'),
                      width= c(300, 300),
                      type=c('text', 'color'),
                      render=c(0, 'square')) 
 excelTable(data=data, columns = columns)

  • Image
library(excelR)

data <- data.frame(Title = c("Paul Parker", "Mark Ellen"),
                   Cover =  c("https://marketplace.canva.com/MACcZp2p4po/2/0/thumbnail_large/canva-black-white-acoustic-album-cover-MACcZp2p4po.jpg",                          "https://marketplace.canva.com/MACcY55adP4/1/0/thumbnail_large/canva-black-and-white-masculine-acoustic-modern-album-cover-MACcY55adP4.jpg"))

columns <- data.frame(title = colnames(data),
                      type = c("text", "image"),
                      width = c(120, 200))

excelTable(data = data, columns = columns)

Row and Column Resizing

library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ),
                   Availability = c(TRUE, FALSE, TRUE, TRUE))

 columns = data.frame(title=c('Model', 'Date', 'Availability' ),
                      width= c(300, 300, 300),
                      type=c('text', 'calendar', 'checkbox')) 
 excelTable(data=data, columns = columns, rowResize = TRUE)

Drag and Drop Rows and Columns

library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ),
                   Availability = c(TRUE, FALSE, TRUE, TRUE))

 columns = data.frame(title=c('Model', 'Date', 'Availability' ),
                      width= c(300, 300, 300),
                      type=c('text', 'calendar', 'checkbox')) 
 excelTable(data=data, columns = columns, rowDrag = TRUE, columnDrag = TRUE)

Merge Rows and Columns

library(excelR)

data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'),
                   Car = c('Civic', 'City', 'Creta'))

 columns = data.frame(title=c('Make', 'Car'), width= c(300, 300))
                      
excelTable(data=data, columns = columns,  mergeCells = list(A1=c(1,2)))

Nested Headers

library(excelR)

 columns = data.frame(title=c('Sepal Length', 'Sepal Width', 'Petal Length','Petal Width', 'Species'),
                      width= c(200, 200, 200, 200, 200))
                      
 nestedHeaders = list( data.frame(title=c("Plant Attribute"), colspan=c(5)), 
                       data.frame(title=c("Sepal Attributes", "Petal Attributes", "Species"), 
                                  colspan=c(2, 2, 1)))
 excelTable(data=head(iris), columns = columns, nestedHeaders = nestedHeaders)

Excel formulas

library(excelR)

 columns = data.frame(title=c('Sepal Length', 'Sepal Width', 'Petal Length','Petal Width', 'Species'),
                      width= c(200, 200, 200, 200, 200))
                      
 excelTable(data=head(iris), columns = columns, minDimension = c(5, 7))

Style

Styling in version 0.1.0:

 library(excelR)

 data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'),
                   Car = c('Civic', 'City', 'Creta'))

 columns = data.frame(title=c('Make', 'Car'), width= c(300, 300))
  
 style = list(A1=c('background-color:orange', 'color:green'), 
              B1=c('background-color:orange', 'color:green'))
                      
 excelTable(data=data, columns = columns, style = style)

Styling from version 0.2.0 onwards:

 library(excelR)

 data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'),
                   Car = c('Civic', 'City', 'Creta'))

 columns = data.frame(title=c('Make', 'Car'), width= c(300, 300))
  
 style = list(A1='background-color:orange; color:green;', 
              B1='background-color:orange; color:green;')
                      
 excelTable(data=data, columns = columns, style = style)

Automated Column Types

library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Date=c(as.Date('2006-01-01'), as.Date('2005-01-01'), as.Date('2004-01-01'), as.Date('2003-01-01') ),
                   Availability = c(TRUE, FALSE, TRUE, TRUE), stringsAsFactors = FALSE)

 columns = data.frame(title=c('Model', 'Date', 'Availability' ),
                      width= c(300, 300, 300)) 
 excelTable(data=data, columns = columns)

Toolbar

library(excelR)

 data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
                   Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ))

 columns = data.frame(title=c('Model', 'Date'),
                      width= c(200, 200),
                      type=c('text', 'calendar')) 
 excelTable(data=data, columns = columns, showToolbar=TRUE)

Shiny Integration

Creating excel table in Shiny

  library(shiny)
  library(excelR)

   shinyApp(
     ui = fluidPage(excelOutput("table")),
     server = function(input, output, session) {
       output$table <-
      renderExcel(excelTable(data = head(iris)))
      }
    )

Reading changed data from excel table in Shiny

  library(shiny)
  library(excelR)

   shinyApp(
     ui = fluidPage(excelOutput("table")),
     server = function(input, output, session) {
       output$table <-
      renderExcel(excelTable(data = head(iris)))
      observeEvent(input$table,{
        table_data <- excel_to_R(input$table)
        if(!is.null(table_data)){
        print(table_data)
        }
        
      })
      }
    )

Getting/Setting comment in Shiny

library(excelR)
library(shiny)

shinyApp(
  
  ui = fluidPage(actionButton('set', 'Set Comments to cell A1'),
                 actionButton('get', 'Get Comments from cell A1'),
                 textAreaInput("fetchedComment", "Comments from A1:"),
                 excelOutput("table", height = 175)),
                 
  server = function(input, output, session) {
  
      output$table <- renderExcel(excelTable(data = head(iris), allowComments = TRUE))
      
      # Set the comment
      observeEvent(input$set,{
        setComments("table", "A1", "This is a comment")
      })
      
      # Get the comment
      observeEvent(input$get,{
          getComments("table", "A1")
      })
      
      # Print the comment to the text area
      observeEvent(input$table,{
        updateTextAreaInput(session, 'fetchedComment', value = input$table$comment )
      })
    }
  )

Getting table data in Shiny

library(excelR)
library(shiny)

shinyApp(
  
  ui = fluidPage(actionButton('get', 'Get table data'),
                 tableOutput("fetchedData"),
                 excelOutput("table", height = 175)),
  
  server = function(input, output, session) {
    
    output$table <- renderExcel(excelTable(data = head(iris)))
    
    # Get the table data
    observeEvent(input$get,{
      getTableData("table")
    })
    
    # Print the table
    observeEvent(input$table,{
      output$fetchedData <- renderTable(excel_to_R(input$table))
    })
  }
)

Getting selected data in Shiny

library(excelR)
library(shiny)

shinyApp(
  
  ui = fluidPage( tags$h6("Selected Data:"),
                  tableOutput("selectedData"),
                  tags$h6("Excel Table:"),
                 excelOutput("table", height = 175)),
  
  server = function(input, output, session) {
    
    output$table <- renderExcel(excelTable(data = head(iris), getSelectedData = TRUE))
    
    # Print the selected data in table
    observeEvent(input$table,{
      output$selectedData <- renderTable(get_selected_data(input$table))
    })
  }
)