Brilliant DT: column date format in excel via extension buttons

Question

I have a date column that shows the UTC time zone. Using the latest version of DT , you can choose to convert the date column to a locale string, and everything looks good in a brilliant webapp. However, if the user loads the table through the button extension, the date column will be exported to the UTC time zone (and unreadable format)

library(DT) library(shiny) df <- data.frame(a = 1:100, b = 1:100, d=seq(as.POSIXct("2017-08-23 10:00:00"), as.POSIXct("2017-11-30 10:00:00"), by = "days")) ui <- fluidPage( dataTableOutput("table") ) server <- function(input, output){ output$table <- DT::renderDataTable({ datatable(df, extensions = c("Buttons"), options = list(dom = 'Bfrtip', buttons = list("csv",list(extend='excel',filename="DF")) )) %>% formatDate(3, "toLocaleString", params = list('fr-FR')) }) } shinyApp(ui, server) 

Therefore, if the local time zone of the OS is +5, it will display "23/08/2017 à 10:00:00" in a brilliant web application, but "2017-08-23T05:00:00Z" in the excel file. Is there a way to format the export?

Answer 1

To achieve what you want, I propose two methods, both require you to convert the dataset to a user locale.

Using input

In the same view as in the table, enter a brilliant input that allows the user to select a language. Use this value to convert UTC records.

 library(DT) library(shiny) library(dplyr) ui <- fluidPage( selectInput( "timezone", "Timezone", choices = c("Europe/Paris", "America/Los_Angeles", "Australia/Sydney") ), DT::dataTableOutput("table") ) server <- function(input, output, session){ df <- data.frame( a = 1:100, b = 1:100, d = seq( as.POSIXct("2017-08-23 10:00:00", tz = "UTC"), as.POSIXct("2017-11-30 10:00:00", tz = "UTC"), by = "days") ) df_locale <- reactive({ df %>% mutate( local = format(d, "%d %B %Y %I:%M:%S %p %Z", tz = input$timezone) ) }) output$table <- DT::renderDataTable({ DT::datatable( df_locale(), extensions = 'Buttons', options = list( dom = 'Bfrtip', buttons = list("copy", "csv", list(extend = "excel", filename = "DF")) ) ) %>% formatDate(3, "toLocaleString", params = list("fr-FR")) }) } shinyApp(ui, server) 

Automatically based on client machine

It is more active and depends on the answer to this question.

 library(DT) library(shiny) library(dplyr) library(lubridate) ui <- fluidPage( HTML('<input type="text" id="client_time" name="client_time" style="display: none;"> '), HTML('<input type="text" id="client_time_zone_offset" name="client_time_zone_offset" style="display: none;"> '), tags$script(' $(function() { var time_now = new Date() $("input#client_time").val(time_now.getTime()) $("input#client_time_zone_offset").val(time_now.getTimezoneOffset()) }); '), DT::dataTableOutput("table") ) server <- function(input, output, session){ df <- data.frame( a = 1:100, b = 1:100, d = seq( as.POSIXct("2017-08-23 10:00:00", tz = "UTC"), as.POSIXct("2017-11-30 10:00:00", tz = "UTC"), by = "days") ) client_time <- reactive({as.numeric(input$client_time) / 1000}) time_zone_offset <- reactive({-as.numeric(input$client_time_zone_offset) * 60}) df_locale <- reactive({ df %>% mutate( local = format(d + seconds(time_zone_offset()), "%d %B %Y %I:%M:%S %p") ) }) output$table <- DT::renderDataTable({ DT::datatable( df_locale(), extensions = 'Buttons', options = list( dom = 'Bfrtip', buttons = list("copy", "csv", list(extend = "excel", filename = "DF")) ) ) %>% formatDate(3, "toLocaleString", params = list("fr-FR")) }) } shinyApp(ui, server) 

NB . Although the advantage of the automatic option is that there is no user interaction, I did not try to determine the location of the Olson Name client and, therefore, did not allow the time zone outside the time offset from UTC. There are likely options for improving the use of alternative javascript.

Update using the download button

If you want to download something else in what is available in DT::datatable via the button extension, you can use the standard downloadHandler and the corresponding button. The code below shows how you can combine the source code to display a table and offer a csv download of data converted according to the client’s time zone offset shown in the two previous approaches.

 library(DT) library(shiny) library(dplyr) library(readr) library(lubridate) ui <- fluidPage( HTML('<input type="text" id="client_time" name="client_time" style="display: none;"> '), HTML('<input type="text" id="client_time_zone_offset" name="client_time_zone_offset" style="display: none;"> '), tags$script(' $(function() { var time_now = new Date() $("input#client_time").val(time_now.getTime()) $("input#client_time_zone_offset").val(time_now.getTimezoneOffset()) }); '), downloadButton("download_data", "Get Data"), DT::dataTableOutput("table") ) server <- function(input, output, session){ df <- data.frame( a = 1:100, b = 1:100, d = seq( as.POSIXct("2017-08-23 10:00:00", tz = "UTC"), as.POSIXct("2017-11-30 10:00:00", tz = "UTC"), by = "days") ) client_time <- reactive({as.numeric(input$client_time) / 1000}) time_zone_offset <- reactive({-as.numeric(input$client_time_zone_offset) * 60}) df_locale <- reactive({ df %>% mutate( d = format(d + seconds(time_zone_offset()), "%d %B %Y %I:%M:%S %p") ) }) output$download_data <- downloadHandler( filename <- function() { paste0(format(Sys.Date(), "%Y%m%d"), "-data.csv") }, content <- function(file) { write_csv(df_locale(), file) }, contentType = "text/csv" ) output$table <- DT::renderDataTable({ DT::datatable(df) %>% formatDate(3, "toLocaleString") }) } shinyApp(ui, server) 

The button extension for DT currently cannot be configured with R. Changing the behavior may be possible using javascript, you can read about the API here .