效果預(yù)覽：
封面.png
本文介紹了 shiny 如何實(shí)現(xiàn)信息的增刪改查，并將數(shù)據(jù)持久化到 SQLite 數(shù)據(jù)庫中儡司。
首先在 SQLite 數(shù)據(jù)庫中新建表 users几苍，表結(jié)構(gòu)如下构订。設(shè)置 id 為主鍵并自動(dòng)遞增。
| 字段 | 類型 | 是否允許空 |
|---|---|---|
| id | INTEGER | 主鍵非空摸航，自動(dòng)遞增 |
| username | TEXT | |
| age | TEXT | |
| birthday | TEXT | |
| address | TEXT |
新增信息
- UI 設(shè)計(jì)
新增信息UI設(shè)計(jì).png
- 實(shí)現(xiàn)
observeEvent(input$adduser, {
showModal(modalDialog(
title = "新建用戶",
size = "m",
fluidPage(
fluidRow(
column(width = 12, textInput(inputId = "username", label = "姓名"))
),
fluidRow(
column(width = 12, textInput(inputId = "age", label = "年齡"))
),
fluidRow(
column(width = 12, dateInput(inputId = "birthday", label = "出生日期"))
),
fluidRow(
column(width = 12, textInput(inputId = "address", label = "地址"))
)
),
easyClose = FALSE,
fade = FALSE,
footer = tagList(
actionButton(inputId = "useradd_confirm", label = "確認(rèn)"),
modalButton(label = "取消")
)
))
})
observeEvent(input$useradd_confirm, {
con <- RSQLite::dbConnect(RSQLite::SQLite(), "./data/users.db")
tryCatch({
addusersql <- paste0("INSERT INTO users (username, age, birthday, address) VALUES ", "('",
input$username, "', '",
input$age, "', '",
input$birthday, "', '",
input$address, "')")
RSQLite::dbExecute(con, addusersql)
# refresh table instance
user_data <<- user_base()
output$user_table <- renderReactable({
# reactable中文
options(reactable.language = reactableLang(
pageSizeOptions = "\u663e\u793a {rows}",
pageInfo = "{rowStart} \u81f3 {rowEnd} \u9879\u7ed3\u679c,\u5171 {rows} \u9879",
pagePrevious = "\u4e0a\u9875",
pageNext = "\u4e0b\u9875"
))
reactable(data = user_data[c("username", "age", "birthday", "address")],
bordered = TRUE,
striped = TRUE,
highlight = TRUE,
filterable = TRUE,
defaultPageSize = 5,
showPageSizeOptions = TRUE,
selection = "multiple",
onClick = "select",
defaultColDef = colDef(
align = "left",
minWidth = 50
),
columns = list(
.selection = colDef(
width = 80,
style = list(cursor = "pointer"),
headerStyle = list(cursor = "pointer")
),
address = colDef(minWidth = 140) # overrides the default
)
)
})
toastr_success(title = "保存成功", message = "")
removeModal()
},
warning = function(w) {
toastr_warning("保存失敗", message = w)
},
error = function(e) {
toastr_error("保存失敗", message = e)
},
finally = {
RSQLite::dbDisconnect(con)
})
})
刪除信息
- UI 設(shè)計(jì)
- 允許同時(shí)刪除多個(gè) item；
- 當(dāng)未勾選任何信息時(shí)，刪除按鈕為禁用樣式洪规；
刪除信息UI設(shè)計(jì).png
- 實(shí)現(xiàn)
# 刪除
output$deleteuser <- renderUI({
if (length(selected()) > 0) {
actionButton(inputId = "deleteuser", label = "刪除", icon = icon("trash-alt"),
style = "background-color: #DC3545;
color: #ffffff;
border: none;
margin-left: 15px;
margin-bottom: 15px")
} else {
shinyjs::disabled(actionButton(inputId = "deleteuser", label = "刪除", icon = icon("trash-alt"),
style = "background-color: #DC3545;
color: #ffffff;
border: none;
margin-left: 15px;
margin-bottom: 15px"))
}
})
observeEvent(input$deleteuser, {
showModal(modalDialog(
title = "刪除用戶",
size = "m",
fluidPage(
div("確定刪除用戶 ",
span(paste(user_data[selected(), "username"], collapse = ", "),
style="padding: .2em .2em;
margin:0;
font-size:85%;
background-color:rgb(175,184,193,20%);
color:#DC3545;
border-radius:2px;"),
" 嗎 ?")
),
easyClose = FALSE,
fade = FALSE,
footer = tagList(
actionButton(inputId = "userdelete_confirm", label = "確認(rèn)"),
modalButton(label = "取消")
)
))
})
observeEvent(input$userdelete_confirm, {
con <- RSQLite::dbConnect(RSQLite::SQLite(), "./data/users.db")
tryCatch({
deleteSql <- paste0("DELETE FROM users WHERE id=", "'", user_data[selected(), "id"], "'")
for (sql in deleteSql) {
RSQLite::dbExecute(con, sql)
}
# refresh table instance
user_data <<- user_base()
output$user_table <- renderReactable({
options(reactable.language = reactableLang(
pageSizeOptions = "\u663e\u793a {rows}",
pageInfo = "{rowStart} \u81f3 {rowEnd} \u9879\u7ed3\u679c,\u5171 {rows} \u9879",
pagePrevious = "\u4e0a\u9875",
pageNext = "\u4e0b\u9875"
))
reactable(data = user_data[c("username", "age", "birthday", "address")],
bordered = TRUE,
striped = TRUE,
highlight = TRUE,
filterable = TRUE,
defaultPageSize = 5,
showPageSizeOptions = TRUE,
selection = "multiple",
onClick = "select",
defaultColDef = colDef(
align = "left",
minWidth = 50
),
columns = list(
.selection = colDef(
width = 80,
style = list(cursor = "pointer"),
headerStyle = list(cursor = "pointer")
),
address = colDef(minWidth = 140) # overrides the default
)
)
})
toastr_success(title = "刪除成功", message = "")
removeModal()
},
warning = function(w) {
toastr_warning(title = "刪除失敗", message = w)
},
error = function(e) {
toastr_error(title = "刪除失敗", message = e)
},
finally = {
RSQLite::dbDisconnect(con)
})
})
}
修改信息
- UI 設(shè)計(jì)
- 復(fù)用新增信息的 UI
- 當(dāng)
未勾選任何 item或勾選 item 數(shù)量 > 1時(shí)，編輯按鈕為禁用狀態(tài)循捺；
- 實(shí)現(xiàn)
# 編輯
output$edituser <- renderUI({
# 只有選擇一項(xiàng)時(shí)可以編輯
if (length(selected()) == 1) {
actionButton(inputId = "edituser", label = "編輯", icon = icon("edit"),
style = "background-color: #007BFF;
color: #ffffff;
border: none;
margin-left: 15px;
margin-bottom: 15px")
} else {
shinyjs::disabled(actionButton(inputId = "edituser", label = "編輯", icon = icon("edit"),
style = "background-color: #007BFF;
color: #ffffff;
border: none;
margin-left: 15px;
margin-bottom: 15px"))
}
})
observeEvent(input$edituser, {
showModal(modalDialog(
title = "修改信息",
size = "m",
fluidPage(
fluidRow(
column(width = 12,
textInput(inputId = "username_new", label = "姓名",
value = user_data[selected(), "username"]))
),
fluidRow(
column(width = 12,
textInput(inputId = "age_new", label = "年齡",
value = user_data[selected(), "age"]))
),
fluidRow(
column(width = 12,
dateInput(inputId = "birthday_new", label = "出生日期",
value = user_data[selected(), "birthday"]))
),
fluidRow(
column(width = 12,
textInput(inputId = "address_new", label = "地址",
value = user_data[selected(), "address"]))
)
),
easyClose = FALSE,
fade = FALSE,
footer = tagList(
actionButton(inputId = "useredit_confirm", label = "確認(rèn)"),
modalButton(label = "取消")
)
))
})
observeEvent(input$useredit_confirm, {
# 更新用戶信息
con <- RSQLite::dbConnect(RSQLite::SQLite(), "./data/users.db")
tryCatch({
edit_standard_user_sql <- paste0("UPDATE users set username=", "'", input$username_new, "'",
", age=", "'", input$age_new, "'",
", birthday=", "'", input$birthday_new, "'",
", address=", "'", input$address_new, "'",
" WHERE id=", "'", user_data[selected(), "id"], "'")
RSQLite::dbExecute(con, edit_standard_user_sql)
# refresh table instance
user_data <<- user_base()
output$user_table <- renderReactable({
options(reactable.language = reactableLang(
pageSizeOptions = "\u663e\u793a {rows}",
pageInfo = "{rowStart} \u81f3 {rowEnd} \u9879\u7ed3\u679c,\u5171 {rows} \u9879",
pagePrevious = "\u4e0a\u9875",
pageNext = "\u4e0b\u9875"
))
reactable(data = user_data[c("username", "age", "birthday", "address")],
bordered = TRUE,
striped = TRUE,
highlight = TRUE,
filterable = TRUE,
defaultPageSize = 5,
showPageSizeOptions = TRUE,
selection = "multiple",
onClick = "select",
defaultColDef = colDef(
align = "left",
minWidth = 50
),
columns = list(
.selection = colDef(
width = 80,
style = list(cursor = "pointer"),
headerStyle = list(cursor = "pointer")
),
address = colDef(minWidth = 140) # overrides the default
)
)
})
toastr_success(title = "修改成功", message = "")
removeModal()
},
warning = function(w) {
toastr_warning(title = "修改失敗", message = w)
},
error = function(e) {
toastr_error(title = "修改失敗", message = e)
},
finally = {
RSQLite::dbDisconnect(con)
})
})
查找信息
reactable 中設(shè)置 filterable = TRUE 即可實(shí)現(xiàn)信息過濾查找的效果斩例；
信息查找.gif
源代碼
完整代碼詳見 https://github.com/redburning/reactable
