Button extension to download all data or only visible data

Question

With the button extension to DT package, is there a way to specify that the buttons download either (1) all the data feeding the datatable, or (2) only the data on the visible page.

Below is the example from the documentation.

datatable(
  iris, extensions = 'Buttons', options = list(
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
  )
)

Answer 1

As @divibisan said, one option is to use the server argument of renderDT() to control whether the download button will download only the current or all rows.

That would work well if you want to have one download button. However if you want to have two buttons always appear, where one download the current page and one downloads the full dataset, you can use the following code:

library(shiny)

ui <- fluidPage(
  DT::DTOutput("table")
)

server <- function(input, output, session) {
  output$table <- DT::renderDT(server = FALSE, {
    DT::datatable(
      mtcars,
      extensions = c("Buttons"),
      options = list(
        dom = 'Bfrtip',
        buttons = list(
          list(extend = "csv", text = "Download Current Page", filename = "page",
               exportOptions = list(
                 modifier = list(page = "current")
               )
          ),
          list(extend = "csv", text = "Download Full Results", filename = "data",
               exportOptions = list(
                 modifier = list(page = "all")
               )
          )
        )
      )
    )
  })
}

shinyApp(ui, server)

Answer 2

See this answer: Buttons: download button with scroller downloads only few rows

Whether the buttons export all data or only visible data is determined by the server argument in the DT::renderDT function call. If server=FALSE then the buttons will export all data in the table, while if server=TRUE they will only export visible data.

You could set the server argument with a variable to make this a selectable option.

output$table <- DT::renderDT(server = input$download_all, {
    DT::datatable( ... )
}

The other option you might want to look at is the exportOptions: modifier: selected option that determines whether to download only selected rows (the default) or all rows. You can read about that option here: https://datatables.net/extensions/buttons/examples/print/select.html

Note that your users might run into performance and memory issues using server=FALSE if your data table is very large.

Answer 3

you are looking for the modifiers: page: selected. here is a working example

ui <- fluidPage(
  title = "Examples of DataTables",
  sidebarLayout(
    mainPanel(
      tabsetPanel(
        id = 'dataset',
        tabPanel("diamonds", DT::dataTableOutput("mytable1"))
      )
    )
  )
)

server <- function(input, output) {

  # choose columns to display
  diamonds2 = diamonds[sample(nrow(diamonds), 1000), ]
  output$mytable1 <- DT::renderDataTable({
    DT::datatable(diamonds2,
                  extensions = 'Buttons',
                  options = list(
                    dom = 'Bfrtip',
                    buttons = 
                      list(
                        list(
                        extend = 'csv',
                        buttons = c('csv'),
                        exportOptions = list(
                          modifiers = list(page = "current")
                        )
                      ))
                    )
                  )
  })

}

shinyApp(ui, server)

hope this helps!

Answer 4

If you want to include the options to download the current page and the entire dataset as both a csv or an excel file, I've managed to implement this as two separate dropdown buttons with those options, together with the copy and print buttons:

Here is the modified working code:

library(shiny)

ui <- fluidPage(
  DT::dataTableOutput("table")
)

server <- function(input, output, session) {
  output$table <- DT::renderDataTable(server = FALSE, {
    DT::datatable(
      mtcars,
      extensions = "Buttons",
      filter = "top",
      selection = "none", #this is to avoid select rows if you click on the rows
      rownames = FALSE,
      
      options = list(
        
        scrollX = TRUE,
        autoWidth = FALSE,
        dom = 'Blrtip', # the important thing is that there is the l to allow for the lengthMenu 
        # https://stackoverflow.com/questions/52645959/r-datatables-do-not-display-buttons-and-length-menu-simultaneously
        
        buttons = list(
          
          # insert buttons with copy and print
          # colvis includes the button to select and view only certain columns in the output table
          # from https://rstudio.github.io/DT/extensions.html 
          I('colvis'), 'copy', 'print',
          
          # code for the first dropdown download button
          # this will download only the current page only (depends on the number of rows selected in the lengthMenu)
          # using modifier = list(page = "current")
          # only the columns visible will be downloaded using the columns:":visible" option from:
          # https://stackoverflow.com/questions/72317260/how-to-download-only-the-selected-columns-in-a-dataframe-using-colvis-from-dt-in/72317607#72317607
          list(
            extend = 'collection',
          buttons = list(
          list(extend = "csv", filename = "page",exportOptions = list(
            columns = ":visible",modifier = list(page = "current"))
          ),
          list(extend = 'excel', filename = "page", title = NULL, 
               exportOptions = list(columns = ":visible",modifier = list(page = "current")))),
          text = 'Download current page'),
          
          # code for the  second dropdown download button
          # this will download the entire dataset using modifier = list(page = "all")
          list(
            extend = 'collection',
            buttons = list(
              list(extend = "csv", filename = "data",exportOptions = list(
                columns = ":visible",modifier = list(page = "all"))
              ),
              list(extend = 'excel', filename = "data", title = NULL, 
                   exportOptions = list(columns = ":visible",modifier = list(page = "all")))),
            text = 'Download all data')
          
        ),
        # add the option to display more rows as a length menu
        lengthMenu = list(c(10, 30, 50, -1),
                          c('10', '30', '50', 'All'))
        ),
      class = "display"
      )
    
  })
}

shinyApp(ui, server)