Little (or not) problem using DT library in R Shiny

[Akilinox]

Hey Everyone,

Let me introduce myself, I'm Antoine. I really like data, but I have only recently started development with R & R Shiny as I have some free time in my company.

My goal? To enable employees internally to visualize our database while we wait for the migration to a new CRM. I found a lot of interesting things on the internet and got inspired by them! It works quite well and is much smoother than manipulating an Excel file for a B2B database of 69,000 people.

To get to the point, I managed to do quite a lot, but I am stuck with some things and would greatly appreciate any help or guidance!

So, I would like to add:

• When exporting filtered data, I don't want just the data visible on the screen but all the filters.
• Fix the bug when performing a column search.
• Ability to double-click on a cell and edit it, then have it update directly on the source Excel file upon saving.
• Similarly, the ability to add data.

Attached, you will find my code and also a sample database (obviously not the real one!).

[spoiler]library(shiny)
library(DT)

ui <- shinyUI(navbarPage(
"Application de gestion de données",
tabPanel("Base de données",
sidebarLayout(
sidebarPanel(
actionButton("delete_btn", "Déplacer vers la corbeille", class = "btn-danger")
),
mainPanel(
DTOutput("table")
)
)
),
tabPanel("Corbeille",
sidebarLayout(
sidebarPanel(
actionButton("permanent_delete_btn", "Supprimer définitivement", class = "btn-warning"),
actionButton("restore_btn", "Restaurer la ligne sélectionnée", class = "btn-primary")
),
mainPanel(
DTOutput("trash_table")
)
)
),
tabPanel("Dashboard",
# UI pour dashboard
),
tabPanel("Contacter le marketing",
# UI pour contacter le marketing
)
))
[/spoiler]

[spoiler]library(shiny)
library(DT)
library(readxl)
library(writexl)
library(openxlsx)
library(shinyFeedback)
library(dplyr)

shinyServer(function(input, output, session) {
path_to_file <- "C:/Users/a.goupil/OneDrive - BAW SAS/Bureau/visualisation-bdd/bdd2024_4.xlsx"

data <- reactiveVal(read_excel(path_to_file))
trash <- reactiveVal(data.frame())

output$table <- renderDT({
datatable(
data(),
selection = 'single',
extensions = 'Buttons',
options = list(
pageLength = 10,
lengthMenu = list(c(5, 10, 20, 50, 100, -1), c('5', '10', '20', '50', '100', 'All')),
autoWidth = TRUE,
dom = 'Blfrtip',
buttons = list(
'copy',
'csv',
'excel',
'pdf',
'print'
),
initComplete = JS(
"function(settings, json) {",
"(this.api().table().header()).css({'background-color': '#D3D3D3', 'color': '#000'});", "}" ) ), filter = "top", callback = JS( "table.on('keyup change', 'input[type=search]', function() {", " table.column((this).parent().index()).search(this.value).draw();",
"});"
)
)
}, server = TRUE)

output$trash_table <- renderDT({
datatable(
trash(),
selection = 'single',
extensions = 'Buttons',
options = list(
pageLength = 10,
lengthMenu = list(c(5, 10, 20, 50, 100, -1), c('5', '10', '20', '50', '100', 'All')),
autoWidth = TRUE,
dom = 'Blfrtip',
buttons = list(
'copy',
'csv',
'excel',
'pdf',
'print'
)
)
)
}, server = TRUE)

observeEvent(input$delete_btn, {
showModal(modalDialog(
title = "Confirmation de suppression",
textInput("password", "Entrez le mot de passe pour supprimer :", value = ""),
footer = tagList(
modalButton("Annuler"),
actionButton("confirm_delete", "Confirmer la suppression", class = "btn-danger")
)
))
})

observeEvent(input$confirm_delete, {
removeModal()
if (input$password == "BAW2024AGO") {
selected_row <- input$table_rows_selected
print(paste("Déplacement de la ligne vers la corbeille:", selected_row))

  tryCatch({
    req(selected_row)
    data_df <- data()
    trash_df <- trash()
    
    if (!is.null(selected_row) && selected_row > 0 && selected_row <= nrow(data_df)) {
      trash_df <- rbind(trash_df, data_df[selected_row, ])
      data_df <- data_df[-selected_row, ]
      data(data_df)
      trash(trash_df)
    }
    
    showToast("success", "Ligne déplacée vers la corbeille")
  }, error = function(error) {
    showToast("error", "Erreur lors du déplacement vers la corbeille")
  })
} else {
  showToast("error", "Mot de passe incorrect")
}

})

observeEvent(input$restore_btn, {
selected_row <- input$trash_table_rows_selected
if (!is.null(selected_row)) {
data_df <- data()
trash_df <- trash()

  data_df <- rbind(data_df, trash_df[selected_row, ])
  trash_df <- trash_df[-selected_row, ]
  data(data_df)
  trash(trash_df)
  
  showToast("success", "Ligne restaurée")
}

})

observeEvent(input$permanent_delete_btn, {
selected_row <- input$trash_table_rows_selected
if (!is.null(selected_row)) {
trash_df <- trash()
trash_df <- trash_df[-selected_row, ]
trash(trash_df)

  showToast("success", "Ligne supprimée définitivement")
}

})
})
[/spoiler]

Here are the names of all the columns (of the data frame):

SIREN / SIRET / Civilité / Prénom / Nom / Mail / Qualité.du.mail / Service / Match_fonction / Fonction / Fonction_INES / LinkedIn / Campagne.Marketing / Désabonnement / Niveau.hiérarchique / Business.Strategy / Resp..hiérarchique / Nom.:.raison.sociale / Société.Nb.d'Employés / Groupe.Nb.Employés / Segment.CA.(BAW) / CA.Groupe / CA / CA.GROUPE2 / Commercial / Pays / Ville / Région / Code.postal / Département / Téléphone.1 / Téléphone.2 / Site.Web / LinkedIn.de.l'entreprise / Marque.source / Marque.Active / Secteur.d’Activité / Secteur.d'Activité.BAW2 / Territoire.BAW / Provenance / Score.Marketing / Niveau.de.décision / Centre.d'intérêt / Origine / Scoring / Type / ERP / ERP2 / EPM / SIRH / BI/Data / Middleware / Infrastructures/Cloud / Autres.Solutions / Autre / Resp..Technique / Opportunity.owner / Etape / Engagement / Motifs.de.cloture / Revente./.com / Type.d'opération / BU / Campagne.Marketing2 / Action.Marketing / Axe.SAP / Axe.Oracle / Axe.RH / Initiative.Analytics / Initiative.RFE./.Démat'./.Automatisation / Initiative.IFS / Initiative.Cloud.&.Dev'Ops / Niveau.de.proximité / Niveau.Hiérarchique2 / Data.d'ajout / Spécifité / Spécifité2 / Segment.CA / Index