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. 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)

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)

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,{
        print(excel_to_R(input$table))
      })
      }
    )