CODEWITHSUNDEEP
rdatatablesshinydata.tabledt
maniA
maniA

Reputation: 1457

Keep the format of numbers in Excel using Buttons extensions

I am using in Shiny Buttons extension to download figures in a Excel-File. 

DTa <- data.table( 
      dataSum()[,1],
      format(round((10^-6)*dataSum()[,-1],2),nsmall = 2,decimal.mark=",",big.mark=".")
           )
   DTa<- DT::datatable( DTa,  extensions=c("Buttons"),options = list(paging = FALSE, 
                               searching = FALSE,
                               dom = 'Bfrtip',
                               #buttons = c('copy','excel')
                               buttons = list(
                                 list(
                                   extend = 'excel', 
                                   text = "Save ", 
                                   title = 'KRB'
                                 ),   list(
                                   extend = 'copy', title = 'krb'
                                 )
                               )
                               ),
                   caption=  paste("Stichtag:", 
                             as.character(sub("([0-9]{2})([0-9]{2})([0-9]{4})KRB.csv", "\\1.\\2.\\3",input$date))))

In the first part above, I transform the figures in the German format, i.e., I set , as a decimal separator and . as a thousands separator. In the second part, I call the extensions Buttons of DT. In Shiny, the figures look as follows:

Shiny

Problem: After pressing the Save Button in Shiny, all figures with just , and without . have the wrong format.

Excel screenshot

For example, after saving 34,21 becomes 3.421! But 67.809,97 is correct!

How can I keep the format of the figures during the export or save process?

I don't know if it helps:

When I change into the debug mode and execute the second part DTa<- DT::datatable( DTa, extensions ... I see the following:

data bevore excel extension

As one can see the figures in data are characters!

Is it possible to write a JavaScript function in my server.R to use the language.decimal option? There is an example here, however I cannot use it exactly.

Upvotes: 4

Views: 552

Answers (1)

beni
beni

Reputation: 190

Try this

DTa<- DT::datatable(DTa, extensions=c("Buttons"), options = list(paging = FALSE, 
                                                  searching = FALSE,
                                                  dom = 'Bfrtip',
                                                  #buttons = c('copy','excel')
                                                  buttons = list(
                                                   list(
                                                        extend = 'excel', 
                                                        text = "Save ", 
                                                        title = 'KRB'
                                                        ), list(
                                                          extend = 'copy', title = 'KRB'
                                                         )
                                                        )
                                                        ),
          caption= paste("Stichtag:", 
                   as.character(sub("([0-9]{2})([0-9]{2})([0-9]{4})KRB.csv", "\\1.\\2.\\3",input$date)))
          ) %>% formatCurrency(-1,' ', digits = 2 , interval = 3, mark = ".", dec.mark = ",")

Upvotes: 2

Related Questions