英文:
R shiny DT table ignoring background color while exporting to excel sheet
问题
这是您提供的代码的翻译部分：
我正在尝试从R Shiny DT表中导出数据到Excel表格，但只导出了DT表中的数据，而没有导出单元格的背景颜色。
请问是否可以帮我看一下代码需要做哪些更改？非常感谢您的帮助。
库(shiny)
库(DT)
库(htmltools)
库(colourpicker)
库(shinyWidgets)
库(DT)
库(shinythemes)
dat <- mtcars
sketch <- tags$table(
tags$thead(
tags$tr(
tags$th(),
lapply(names(dat), tags$th)
),
tags$tr(
tags$th(id = "th0"),
tags$th(id = "th1"),
tags$th(id = "th2"),
tags$th(id = "th3"),
tags$th(id = "th4"),
tags$th(id = "th5"),
tags$th(id = "th6"),
tags$th(id = "th7"),
tags$th(id = "th8"),
tags$th(id = "th9"),
tags$th(id = "th10"),
tags$th(id = "th11")
)
)
)
js <- c(
"function(){",
" this.api().columns().every(function(i){",
" var column = this;",
" var select = $('<select multiple=\"multiple\"><option value=\"\"></option></select>')",
" .appendTo( $('#th'+i).empty() )",
" .on('change', function(){",
" var vals = $('option:selected', this).map(function(index,element){",
" return $.fn.dataTable.util.escapeRegex($(element).val());",
" }).toArray().join('|');",
" column.search(vals.length > 0 ? '^('+vals+')$' : '', true, false).draw();",
" });",
" var data = column.data();",
" if(i == 0){",
" data.each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }else{",
" data.unique().sort().each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }",
" select.select2({width: '120%', closeOnSelect: false});",
" });",
"}"
)
ca <- c(
"table.on('click', 'td', function(e) {",
" const $cell = table.cell(this).nodes().to$();",
" const RGB = $cell.css('background-color');",
" const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;",
" const selectedColor = $('#color').val().toLowerCase();",
" const color = cellColor == selectedColor ? '' : selectedColor;",
" $cell.css('background-color', color);",
"});"
)
customize <- "
function(xlsx, button, table) {
const LETTERS =
['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var row = 0;
const num_columns = table.columns().count();
$('row', sheet).each(function(x) {
if(x > 1) {
for(let i=0; i < num_columns; i++) {
const $cell = $(table.cell(':eq('+row+')', i).node());
const RGB = $cell.css('background-color');
const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;
if(cellColor == '#ff0000') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '10');
} else if(cellColor == '#00ff00') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '15');
}
}
row++;
}
});
}"
英文:
I am trying to export the data from R shiny DT table to the excel sheet after selecting background color to some of the cells in the DT table. But the code exports only the data from the DT table but not exporting along with the background colors selected of the cells.
Could someone please help me what changes need to be made to the code. ANy help is much appreciated.
library(shiny)
library(DT)
library(htmltools)
library(colourpicker)
library(shinyWidgets)
library(DT)
library(shinythemes)
dat <- mtcars
sketch <- tags$table(
tags$thead(
tags$tr(
tags$th(),
lapply(names(dat), tags$th)
),
tags$tr(
tags$th(id = "th0"),
tags$th(id = "th1"),
tags$th(id = "th2"),
tags$th(id = "th3"),
tags$th(id = "th4"),
tags$th(id = "th5"),
tags$th(id = "th6"),
tags$th(id = "th7"),
tags$th(id = "th8"),
tags$th(id = "th9"),
tags$th(id = "th10"),
tags$th(id = "th11")
)
)
)
js <- c(
"function(){",
" this.api().columns().every(function(i){",
" var column = this;",
" var select = $('<select multiple=\"multiple\"><option value=\"\"></option></select>')",
" .appendTo( $('#th'+i).empty() )",
" .on('change', function(){",
" var vals = $('option:selected', this).map(function(index,element){",
" return $.fn.dataTable.util.escapeRegex($(element).val());",
" }).toArray().join('|');",
" column.search(vals.length > 0 ? '^('+vals+')$' : '', true, false).draw();",
" });",
" var data = column.data();",
" if(i == 0){",
" data.each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }else{",
" data.unique().sort().each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }",
" select.select2({width: '120%', closeOnSelect: false});",
" });",
"}")
ca <- c(
"table.on('click', 'td', function(e) {",
" const $cell = table.cell(this).nodes().to$();",
" const RGB = $cell.css('background-color');",
" const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``",
" const selectedColor = $('#color').val().toLowerCase();",
" const color = cellColor == selectedColor ? '' : selectedColor;",
" $cell.css('background-color', color);",
"});"
)
ui <- fluidPage(HTML(""),
tags$head(
tags$link(rel = "stylesheet", href = "https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css"),
tags$script(src = "https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js")
),
sidebarLayout(
sidebarPanel(
colourInput(
"color",
"Choose background color",
palette = "limited",
closeOnClick = TRUE
)
),
mainPanel(
br(),
DTOutput("dtable")
)))
server <- function(input, output, session) {
output[["dtable"]] <- renderDT({
datatable(
dat, callback = JS(ca), selection = "none", container=sketch, editable = "cell", class = 'cell-border stripe', extensions = 'Buttons',
options = list(dom = 'BPrlftip', buttons = list('copy', 'pdf', 'csv', 'excel', 'print'),
orderCellsTop = TRUE,
initComplete = JS(js),
columnDefs = list(
list(targets = "_all", className = "dt-center")
)
)
)
}, server = FALSE)
}
shinyApp(ui, server)
Incorporated code
library(shiny)
library(DT)
library(htmltools)
library(colourpicker)
library(shinyWidgets)
library(DT)
library(shinythemes)
dat <- mtcars
sketch <- tags$table(
tags$thead(
tags$tr(
tags$th(),
lapply(names(dat), tags$th)
),
tags$tr(
tags$th(id = "th0"),
tags$th(id = "th1"),
tags$th(id = "th2"),
tags$th(id = "th3"),
tags$th(id = "th4"),
tags$th(id = "th5"),
tags$th(id = "th6"),
tags$th(id = "th7"),
tags$th(id = "th8"),
tags$th(id = "th9"),
tags$th(id = "th10"),
tags$th(id = "th11")
)
)
)
js <- c(
"function(){",
" this.api().columns().every(function(i){",
" var column = this;",
" var select = $('<select multiple=\"multiple\"><option value=\"\"></option></select>')",
" .appendTo( $('#th'+i).empty() )",
" .on('change', function(){",
" var vals = $('option:selected', this).map(function(index,element){",
" return $.fn.dataTable.util.escapeRegex($(element).val());",
" }).toArray().join('|');",
" column.search(vals.length > 0 ? '^('+vals+')$' : '', true, false).draw();",
" });",
" var data = column.data();",
" if(i == 0){",
" data.each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }else{",
" data.unique().sort().each(function(d, j){",
" select.append('<option value=\"'+d+'\">'+d+'</option>');",
" });",
" }",
" select.select2({width: '120%', closeOnSelect: false});",
" });",
"}")
ca <- c(
"table.on('click', 'td', function(e) {",
" const $cell = table.cell(this).nodes().to$();",
" const RGB = $cell.css('background-color');",
" const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``",
" const selectedColor = $('#color').val().toLowerCase();",
" const color = cellColor == selectedColor ? '' : selectedColor;",
" $cell.css('background-color', color);",
"});"
)
customize <- "
function(xlsx, button, table) {
const LETTERS =
['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var row = 0;
const num_columns = table.columns().count();
$('row', sheet).each(function(x) {
if(x > 1) {
for(let i=0; i < num_columns; i++) {
const $cell = $(table.cell(':eq('+row+')', i).node());
const RGB = $cell.css('background-color');
const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;
if(cellColor == '#ff0000') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '10');
} else if(cellColor == '#00ff00') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '15');
}
}
row++;
}
});
}"
ui <- fluidPage(HTML(""),
tags$head(
tags$link(rel = "stylesheet", href = "https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css"),
tags$script(src = "https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js")
),
sidebarLayout(
sidebarPanel(
colourInput(
"color",
"Choose background color",
palette = "limited",
closeOnClick = TRUE
)
),
mainPanel(
br(),
DTOutput("dtable")
)))
server <- function(input, output, session) {
output[["dtable"]] <- renderDT({
datatable(
dat, callback = JS(ca), selection = "none", container=sketch, editable = "cell", class = 'cell-border stripe', extensions = 'Buttons',
options = list(dom = 'BPrlftip', buttons = list(list(
extend = "excel",
text = "Save XLSX",
customize = JS(customize))),
orderCellsTop = TRUE,
initComplete = JS(js),
columnDefs = list(
list(targets = "_all", className = "dt-center")
)))
}, server = FALSE)
}
shinyApp(ui, server)
答案1
得分: 1
以下是您提供的代码的中文翻译部分：
必须使用Excel按钮的“customize”选项。这有点让人烦恼，因为每种颜色都对应一个特殊的Excel代码，我们必须列出所有可能性。在这里，我开始为红色和绿色实现它。
library(shiny)
library(DT)
library(colourpicker)
callback <- c(
"table.on('click', 'td', function(e) {",
" const $cell = table.cell(this).nodes().to$();",
" const RGB = $cell.css('background-color');",
" const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;",
" const selectedColor = $('#color').val().toLowerCase();",
" const color = cellColor == selectedColor ? '' : selectedColor;",
" $cell.css('background-color', color);",
"});"
)
customize <- "
function(xlsx, button, table) {
const LETTERS =
['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var row = 0;
const num_columns = table.columns().count();
$('row', sheet).each(function(x) {
if(x > 1) {
for(let i=0; i < num_columns; i++) {
const $cell = $(table.cell(':eq('+row+')', i).node());
const RGB = $cell.css('background-color');
const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;
if(cellColor == '#ff0000') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '10');
} else if(cellColor == '#00ff00') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '15');
}
}
row++;
}
});
}"
ui <- fluidPage(
sidebarLayout(
sidebarPanel(
colourInput(
"color",
"选择背景颜色",
palette = "limited",
closeOnClick = TRUE
)
),
mainPanel(
br(),
DTOutput("dtable")
)
)
)
server <- function(input, output, session) {
output[["dtable"]] <- renderDT({
datatable(
iris,
extensions = "Buttons",
selection = "none",
callback = JS(callback),
options = list(
dom = "Bfrtip",
buttons = list(
list(
extend = "excel",
text = "保存 XLSX",
customize = JS(customize)
)
)
)
)
})
}
shinyApp(ui, server)
英文:
One has to use the customize option of the Excel button. That's a bit annoying, because each color corresponds to a special Excel code and we would have to list all possibilities. Here I started to implement it for red and green only.
library(shiny)
library(DT)
library(colourpicker)
callback <- c(
"table.on('click', 'td', function(e) {",
" const $cell = table.cell(this).nodes().to$();",
" const RGB = $cell.css('background-color');",
" const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``",
" const selectedColor = $('#color').val().toLowerCase();",
" const color = cellColor == selectedColor ? '' : selectedColor;",
" $cell.css('background-color', color);",
"});"
)
customize <- "
function(xlsx, button, table) {
const LETTERS =
['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var row = 0;
const num_columns = table.columns().count();
$('row', sheet).each(function(x) {
if(x > 1) {
for(let i=0; i < num_columns; i++) {
const $cell = $(table.cell(':eq('+row+')', i).node());
const RGB = $cell.css('background-color');
const cellColor = '#' + RGB.match(/\\d+/g).map(x => (+x).toString(16).padStart(2,0)).join``;
if(cellColor == '#ff0000') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '10');
} else if(cellColor == '#00ff00') {
$('row:eq(' + (x) + ') c[r^=' + (LETTERS[i]) + ']', sheet).attr('s', '15');
}
}
row++;
}
});
}"
ui <- fluidPage(
sidebarLayout(
sidebarPanel(
colourInput(
"color",
"Choose background color",
palette = "limited",
closeOnClick = TRUE
)
),
mainPanel(
br(),
DTOutput("dtable")
)
)
)
server <- function(input, output, session) {
output[["dtable"]] <- renderDT({
datatable(
iris,
extensions = "Buttons",
selection = "none",
callback = JS(callback),
options = list(
dom = "Bfrtip",
buttons = list(
list(
extend = "excel",
text = "Save XLSX",
customize = JS(customize)
)
)
)
)
})
}
shinyApp(ui, server)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论