swechhya / excelr Goto Github PK
View Code? Open in Web Editor NEWAn Interface to 'jExcel.js' Library
Home Page: https://swechhya.github.io/excelR/
License: Other
An Interface to 'jExcel.js' Library
Home Page: https://swechhya.github.io/excelR/
License: Other
Hello!
Many thanks for developing this package! :) This is absolutely what is needed for developing a shiny app with Excel-based worksheets integrated.
May I ask if it's on the to-do list to add the features auto-width on column names and another feature to force the width and height to the container width/height? These additions would make the package a killer...
This could best be done in the excelTable
arguments
Something like this:
excelTable(data = mydata, autoWidth = TRUE, autoFill = TRUE)
together with excelOutput(id = myid, width = "100%", width = "50vh")
should produce auto-spaced columns that fill the whole width and half the height of the screen.
Instead of having to specify the column width each separately through the columns
parameter in the excelTable
function...
Describe the bug
Multiple dropdown not working, not able to select any value
To Reproduce
library(excelR)
data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'),
Car = c('Civic', 'City', 'Polo'))
columns = data.frame(title=c('Make', 'Car'),
width= c(300, 300),
type=c('text', 'dropdown'),
multiple=c(NA, TRUE),
source=I(list(0,c('Civic', 'City', 'Polo', 'Creta', 'Santro'))))
excelTable(data=data, columns = columns)
Desktop (please complete the following information):
R version 3.6.1 (2019-07-05)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)Matrix products: default
attached base packages:
[1] stats graphics grDevices utils datasets methods baseother attached packages:
[1] excelR_0.3.1loaded via a namespace (and not attached):
[1] htmlwidgets_1.5.1 compiler_3.6.1 htmltools_0.4.0 tools_3.6.1 yaml_2.2.0
[6] Rcpp_1.0.2 jsonlite_1.6 digest_0.6.21 rlang_0.4.0
Hello, I am making a Shiny app that let's the user edit certain columns of a table produced by your great package. In my situation I have a column with cells that should only have values between 0 and 100, and I do not want the table to accept any string values nor numeric values outside of the range. I got pretty close by leveraging the updateTable
parameter in the call to excelTable
with a custom javascript snippet, but I discovered that the actual value of the cell is not changed after my javascript snippet completes. A minimal reprex is below. It may be an issue with my javascript code, in that I can get the displayed value to show the new number, but when they double-click the cell to edit again, it shows the original (invalid) value.
library(shiny)
library(excelR)
df <- data.frame(
var1 = c("a", "b", "c"),
value = c(0, 0, 0),
include = c(FALSE, FALSE, FALSE)
)
columns_df <- data.frame(
title = c("Group", "Value", "Include in Assessment"),
width = c(300, 100, 100),
type = c("text", "numeric", "checkbox"),
readOnly = c(TRUE, FALSE, FALSE)
)
js_snippet <- "function(instance, cell, col, row, val, label, cellName) {
if (col == 1) {
val = cell.innerText;
if (isNaN(val)) {
alert('Error: Please enter a value between 0 and 100')
cell.innerText = 0;
cell.innerHTML = 0;
} else {
if (val < 0) {
alert('Note: Negative values are not allowed, so the value will be set to 0')
cell.innerText = 0;
cell.innerHTML = 0;
} else if (val > 100) {
alert('Note: Values greater than 100 are not allowed, so the value will be set to 100')
cell.innerText = 100;
cell.innerHTML = 100;
} else {
cell.style.color = 'green';
}
}
}
}"
shinyApp(
ui = fluidPage(
excelOutput("table"),
verbatimTextOutput("table_output")
),
server = function(input, output, session) {
table_rv <- reactiveVal(NULL)
output$table <- renderExcel({
excelTable(
data = df,
columns = columns_df,
rowDrag = FALSE,
allowInsertRow = FALSE,
allowInsertColumn = FALSE,
allowDeleteRow = FALSE,
allowDeleteColumn = FALSE,
allowRenameColumn = FALSE,
selectionCopy = FALSE,
updateTable = htmlwidgets::JS(js_snippet)
)
})
observeEvent(input$table, {
table_data <- excel_to_R(input$table)
if (!is.null(table_data)) {
table_rv(table_data)
}
})
output$table_output <- renderPrint({
req(table_rv())
table_rv()
})
}
)
Is your feature request related to a problem? Please describe.
I could not alter the height of the excelTable in Shiny without using fullscreen = TRUE
. However, fullscreen = TRUE
would hide the searchbar under the shiny navbar, and I could not insert padding for some reason.
Adjusting height in excelOutput in the Shiny UI also had no effect.
Describe the solution you'd like
adjust table height
Describe alternatives you've considered
In order to fix this, I ended up adding a line of code to excelTable:
paramList$tableHeight <- "500px"
This fixed the problem for me, because I can now set the table height myself. I'm unsure if this issue was caused by my R version being only 3.6.0. However, even with this R version, adjusting tableHeight parameter fixed the issue.
Additional context
I posted this on stackoverflow:
https://stackoverflow.com/questions/62050757/shiny-app-excelr-cant-control-table-height/62051461#62051461
example code with problem in R 3.6.0
shinyApp(
ui = navbarPage("title", selected = "main",
position = "fixed-top",
tags$style(type="text/css", "body {padding-top: 70px;}"),
tabPanel("main", id = "main",
fluidPage(
excelOutput("table", width = "100%", height = "100%")
)
)
),
server = function(input, output, session) {
output$table <-renderExcel(
excelTable(
data = iris,
autoColTypes = FALSE,
pagination = 5,
#autoFill = TRUE,
fullscreen = FALSE,
lazyLoading = TRUE,
search = TRUE
)
)
}
)
Fix I used
This is the fix, with the new parameter added at the bottom for tableHeight = 500px
shinyApp(
ui = navbarPage("title", selected = "main",
position = "fixed-top",
tags$style(type="text/css", "body {padding-top: 70px;}"),
tabPanel("main", id = "main",
fluidPage(
excelOutput("table", width = "100%", height = "100%")
#htmlOutput("table", width = "100%", height = "500px")
)
)
),
server = function(input, output, session) {
output$table <-renderExcel(
excelTable2(
data = iris,
autoColTypes = FALSE,
autoFill = TRUE,
fullscreen = FALSE,
lazyLoading = TRUE,
search = TRUE
)
)
}
)
excelTable2 = function (data = NULL, columns = NULL, colHeaders = NULL, rowHeight = NULL,
nestedHeaders = NULL, defaultColWidth = NULL, minDimensions = NULL,
columnSorting = TRUE, columnDrag = FALSE, columnResize = TRUE,
rowResize = FALSE, rowDrag = TRUE, editable = TRUE, allowInsertRow = TRUE,
allowInsertColumn = TRUE, allowDeleteRow = TRUE, allowDeleteColumn = TRUE,
allowRenameColumn = TRUE, allowComments = FALSE, wordWrap = FALSE,
selectionCopy = TRUE, mergeCells = NULL, search = FALSE,
pagination = NULL, fullscreen = FALSE, lazyLoading = FALSE,
loadingSpin = FALSE, style = NULL, autoColTypes = TRUE, showToolbar = FALSE,
dateFormat = "DD/MM/YYYY", digits = 4, autoWidth = TRUE,
autoFill = FALSE, getSelectedData = FALSE, ...)
{
paramList <- list()
if (!is.null(data)) {
if (is.data.frame(data) || is.matrix(data)) {
paramList$data <- jsonlite::toJSON(data, dataframe = "values",
na = "null", digits = digits)
}
else {
stop("'data' must be either a matrix or a data frame, cannot be ",
class(data))
}
}
if (is.null(columns) && is.null(colHeaders)) {
if (!is.null(data)) {
warning("Since both column title and colHeaders are not specified 'data' column name will be used as column headers")
paramList$colHeaders = colnames(data)
}
}
else if (is.null(columns) && !is.null(colHeaders)) {
if (!is.vector(colHeaders)) {
stop("'colHeaders' must be a vector, cannot be ",
class(colHeaders))
}
if (!is.null(data)) {
if (ncol(data) != length(colHeaders)) {
stop("length of 'colHeader' should be equal the number of columns in the 'data', 'data' has ",
ncol(data), "but the length of 'colHeader' is ",
length(colHeaders))
}
}
paramList$colHeaders <- jsonlite::toJSON(colHeaders)
}
else if (!is.null(columns)) {
if (!is.data.frame(columns)) {
stop("'columns' must be a dataframe, cannot be ",
class(columns))
}
if (!is.null(data)) {
if (nrow(columns) != ncol(data)) {
stop("number of rows in 'columns' should be equal to number of columns in 'data', expected number of rows in 'columns' to be ",
ncol(data), " but got ", nrow(columns))
}
}
if (!"title" %in% colnames(columns)) {
if (is.null(colHeaders)) {
if (!is.null(data)) {
warning("Since both column title and colHeaders are not specified 'data' column name will be used as column headers")
paramList$colHeaders = jsonlite::toJSON(colnames(data))
}
}
else {
paramList$colHeaders = jsonlite::toJSON(colHeaders)
}
}
paramList$columns <- jsonlite::toJSON(columns)
}
if (autoColTypes && !is.null(data)) {
if (is.null(columns)) {
message("Since 'type' attribute is not specified and autoColTypes is true, detecting type from 'data'")
colTypes <- get_col_types(data)
columns <- data.frame(type = colTypes)
columns <- add_source_for_dropdown_type(data, columns)
paramList$columns <- jsonlite::toJSON(columns)
}
else {
if (!"type" %in% colnames(columns) && autoColTypes) {
message("Since 'type' attribute is not specified and autoColTypes is true, detecting type from 'data'")
colTypes <- get_col_types(data)
columns$type <- colTypes
columns <- add_source_for_dropdown_type(data,
columns)
paramList$columns <- jsonlite::toJSON(columns)
}
}
}
if (!is.null(rowHeight)) {
if (!is.data.frame(rowHeight) && !is.matrix(rowHeight)) {
stop("'rowHeight' must either be a matrix or a dataframe, cannot be ",
class(rowHeight))
}
if (ncol(rowHeight) != 2) {
stop("'rowHeight' must either be a matrix or a dataframe with two columns, but got ",
ncol(rowHeight), " column(s)")
}
paramList$rowHeight <- jsonlite::toJSON(rowHeight, dataframe = "values")
}
if (!is.null(nestedHeaders)) {
if (!is.list(nestedHeaders)) {
stop("'nestedHeaders' must be a list of dataframe(s), cannot be ",
class(nestedHeaders))
}
headerAttributes <- c("title", "colspan")
for (nestedHeader in nestedHeaders) {
if (!is.data.frame(nestedHeader)) {
stop("'nestedHeaders' must be a list of dataframe(s), but got list of ",
class(nestedHeader), "(s)")
}
if (ncol(nestedHeader) < 2 || nrow(nestedHeader) <
1) {
stop("the dataframe(s) in 'nestedHeaders must contain at least two columns and one row, 'title' and 'colspan', but got only ",
ncol(nestedHeader), " column and ", nrow(nestedHeader),
" row")
}
if (!"title" %in% colnames(nestedHeader)) {
stop("one of the column in the dataframe in list of 'nestedHeaders' should have 'title' as header which will be used as title of the nested header")
}
if (!"colspan" %in% colnames(nestedHeader)) {
stop("one of the column in the dataframe in list of 'nestedHeaders' should have 'colspan' as header which will be used to determine the number of column it needs to span")
}
if (!all(colnames(nestedHeader) %in% headerAttributes)) {
warning("unknown headers(s) ", colnames(nestedHeader)[!colnames(nestedHeader) %in%
headerAttributes], " for 'nestedHeader' found, ignoring column with those header(s)")
}
}
paramList$nestedHeaders <- jsonlite::toJSON(nestedHeaders,
dataframe = "rows")
}
if (!is.null(defaultColWidth)) {
if (!is.numeric(defaultColWidth) || length(defaultColWidth) >
1) {
stop("'defaultColWidth' must be a numeric value of length 1 but got ",
class(defaultColWidth), " of length ",
length(defaultColWidth))
}
paramList$defaultColWidth <- defaultColWidth
}
if (!is.null(minDimensions)) {
if (!is.vector(minDimensions)) {
stop("'minDimensions' must be vector but got ",
class(minDimensions))
}
if (length(minDimensions) != 2) {
stop("'minDimensions' must be a vector of length of 2 but got length of ",
length(minDimensions))
}
paramList$minDimensions <- minDimensions
}
for (arg in c("columnSorting", "columnDrag",
"columnResize", "rowResize", "rowDrag",
"editable", "allowInsertRow", "allowInsertColumn",
"allowDeleteRow", "allowDeleteColumn", "allowRenameColumn",
"allowComments", "wordWrap", "selectionCopy",
"search", "fullscreen", "lazyLoading",
"loadingSpin", "showToolbar", "autoWidth",
"autoFill", "getSelectedData")) {
argvalue <- get(arg)
if (!is.null(argvalue)) {
if (is.logical(argvalue)) {
paramList[[arg]] <- argvalue
}
else {
warning("Argument ", arg, " should be either TRUE or FALSE. Ignoring ",
arg, ".", call. = FALSE)
paramList[[arg]] <- NULL
}
}
}
if (!is.null(mergeCells)) {
if (!is.list(mergeCells)) {
stop("expected 'mergeCells' to be a list but got ",
class(mergeCells))
}
for (mergeCell in mergeCells) {
if (!is.vector(mergeCell)) {
stop("expected each parameter in 'mergeCells' list to be a vector but got ",
class(mergeCell))
}
if (length(mergeCell) != 2) {
stop("expected each parameter in 'mergeCells' list to be a vector of length 2 but got vector of length ",
length(mergeCells))
}
}
paramList$mergeCells <- mergeCells
}
if (!is.null(pagination)) {
if (!is.numeric(pagination) || length(pagination) > 1) {
stop("'pagination' must be an integer of length 1 but got ",
class(pagination), " of length ", length(pagination))
}
paramList$pagination <- pagination
}
if (!is.null(style)) {
if (!is.list(style)) {
stop("'style' should be a list but got ", class(style))
}
paramList$style <- style
}
if (!is.null(dateFormat)) {
paramList$dateFormat <- dateFormat
}
paramList$tableHeight <- "500px"
paramList <- append(paramList, list(...))
htmlwidgets::createWidget(name = "jexcel", x = paramList,
width = if (fullscreen)
"100%"
else 0, height = if (fullscreen)
"100%"
else 0, package = "excelR",
)
}
Describe the bug
Sub-lists are not working in excelTable columns option. Couldn't get working example with calendar options
To Reproduce
dt <- iris[1:10, ]['Species']
dt$date <- Sys.Date()
cols <- data.frame(title = colnames(dt), width = 100, options = I(list(NA, list(
format = 'DD.MM.YYYY',
placeholder = 'my date',
months = c('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
))))
excelTable(dt, columns = cols)
Describe the bug
When I define columns in excelTable I do not get to see the table, only an empty page. Leaving out the column argument solves the issue.
To Reproduce
Steps to reproduce the behavior:
## Latest version a this point
devtools::install_github('Swechhya/excelR@9ff3edb1')
library(excelR)
data = data.frame(Model = c('Mazda', 'Pegeout', 'Honda Fit', 'Honda CRV'),
Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ),
Availability = c(TRUE, FALSE, TRUE, TRUE))
columns = data.frame(title=c('Model', 'Date', 'Availability'),
width= c(300, 300, 300),
type=c('text', 'calendar', 'checkbox'))
excelTable(data=data, columns = columns)
Expected behavior
See the table
Desktop (please complete the following information):
Hi,
I am using mergedCells as per the code below, however when I run the app the merged cells pushes all values to the right in all rows with the exception of the 1st merged row (See screenshot attached).
library(excelR)
library(shiny)
data = data.frame( Make = c('Honda', 'Honda', 'Hyundai'),
Car = c('Civic', 'City', 'Creta'),
Year = c('2018','2019','2020'))
columns = data.frame(title=c('Make', 'Car','Year'))
ui <- fluidPage(
excelTable(data=data, columns = columns, mergeCells = list(A1=c(1,2)))
)
server <- function(input, output) {
}
shinyApp(ui = ui, server = server)
Can we make columns data.frame in excelTable more flexible by removing
Line 182 in 5b3fbbb
Line 192 in 5b3fbbb
Factor data passed to excelTable is first coerced to a string and then the string is coerced back to a factor using default R behavior, which drops unused factor levels and potentially reorders factors.
To Reproduce
Run the code below and change the Name column to C, A. The expectation would be that there are still 3 levels, but only two exist.
library(shiny)
library(excelR)
# Input data
data <- data.frame(
Name = factor(c(1, 3), levels = 1:3, labels = LETTERS[1:3])
)
# Column specification
columns <- data.frame(
title = colnames(data),
type = "dropdown",
source = I(list(levels(data$Name)))
)
shinyApp(
ui = fluidPage(
excelOutput("table"),
actionButton("click", "click")
),
server = function(input, output, session) {
output$table <- renderExcel({
excelTable(data = data, columns = columns)
})
observeEvent(input$click,{
tbl <- excel_to_R(input$table)
print(tbl)
print(as.numeric(tbl$Name))
print(levels(tbl$Name))
})
}
)
Expected behavior
Factor levels and labels retained in the order given.
Desktop (please complete the following information):
Additional context
I believe the issue lies in the fact that when R data are converted to JSON (I think) they are passed as string type. Then when the JSON is passed back to R the excel_to_R function calls rbind.data.frame
, which per default behavior converts character to factor (depending on environmental settings).
You may consider this a feature request, but I found it a bit unexpected and scary. I would guess that if this is a fix/change you are willing to incorporate you will have some design decisions to make and so I leave it up to you to pick a future direction. My first guess is that the data frame passed to columns argument of excelTable
could be used to control the reordering, perhaps by attaching it to the input object with the data?
Thanks
Describe the bug
When you provide a data.frame to excelTable with only one column the column name is set to the first letter of that column instead of the full name.
To Reproduce
Steps to reproduce the behavior:
library(excelR)
excelTable(datasets::iris[1:10,1,drop=F])
Expected behavior
The full column name
Desktop (please complete the following information):
ExcelR is a great package, kudos to you. Unfortunately I run into issues when I place ExcelR table in a container using shiny dashboard. For instance, when using excelOutput() inside a box() or bsModal() UI element, it causes issues with pop-up animations. If I right click on a table the menu opens at a different location from where I clicked. Likewise, if I have drop-down columns, the menu will appear at the top of the page. These issues don't occur if I place the excelOutput() on its own inside a fluidPage(). Any recommendations?
Thanks for this awesome package!
I have a question. Is there a way to know which cells are edited by the user in shiny apps. Say, automatically changing the color of text in a cell when user makes changes in excelR. If the user makes changes in some cells, then the color of the text in these cells or the can be changed accordingly. Just like the following Excel screenshot.
Besides, how can I turn the table into a heatmap? Just like the rhandsontable
package?See the following example:
Thanks again for this super useful package! Hope you can consider my feature requests.
Describe the bug
Defining columns and setting getSelectedData to TRUE in excelTable give unexpected behaviour with column rename. Only the last column name change is remembered.
To Reproduce
Steps to reproduce the behavior:
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(excelOutput("table"), verbatimTextOutput("out")),
server = function(input, output, session) {
data = reactive({
iris[1:10,]
})
output$table <- renderExcel({
DF = data()
if (!is.null(DF)) {
excelTable(DF, columns=data.frame(title=colnames(DF)), getSelectedData=T)
}
})
output$out <- renderPrint({
if(!is.null(input$table)) {
excel_to_R(input$table)
} else {
data()
}
})
}
)
Expected behavior
Persistent column name change
Desktop (please complete the following information):
code is currently:
shinyApp(
ui = fluidPage(excelOutput("table"))
server = function(input, output, session) {
output$table <-
renderExcel(excelTable(data = head(iris)))
}
)
comma needed after "ui <- fluidPage(excelOutput("table")),"
eg
shinyApp(
ui = fluidPage(excelOutput("table")) ,
server = function(input, output, session) {
output$table <-
renderExcel(excelTable(data = head(iris)))
}
)
At the end of the readme something has mentioned as data picker
, Not sure if it's a typo so raised an issue here!
I currently have a shiny app in which a selectinput allows users to choose a particular saved table, and i display that table using this excelR package (which is extremely useful). Users currently:
This all works fine in most cases.
However, in the case that the user:
This is similar to if no edit is made at all. I see the documentation suggests the following:
table_data <- excel_to_R(input$table)
if(!is.null(table_data)){
data_to_use <- table_data
}
I have modified this to the following for my purposes:
table_data <- excel_to_R(input$table)
if(is.null(table_data)){
data_to_use <- original_table() #where original_table() is a reactive value of readRDS
} else {
data_to_use <- table_data
}
Above it will simply assign the original table where no edits have been made to the excel table.
However, this still doesn't help if edits have been made, and then the selectinput chooses a different table.
In an ideal world there would be some solution that looks as follows:
Option 1: Another check in the logic described above and below
table_data <- excel_to_R(input$table)
if(is.null(table_data)){
run_data <- original_table()
} else if ( SOME CHECK GOES HERE ) {
run_data <- original_table()
} else {
run_data <- table_data
}
Option 2:
Some way of forcing excel_to_R(input$table) to reset to null every time the selectinput is used.
Many thanks,
I'm really enjoying this package.
Is it possible to add the footer option from jexcel?
Doesn't seems like the object even carries the method.
Describe the bug
Upon the insertion of a column, the output of "excel_to_R call" in a Shiny app yields an invalid data frame where the inserted col is missing from the colHeader
To Reproduce
Using the sample app
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(excelOutput("table")),
server = function(input, output, session) {
output$table <-
renderExcel(excelTable(data = head(iris)))
observeEvent(input$table,{
table_data <- excel_to_R(input$table)
if(!is.null(table_data)){
print(table_data)
}
})
}
)
Expected behavior
After the insertion of a new column, the data table should look like the screen shot below.
However, the data frame is invalid since the new column was added to the end of the colHeaders, while the body is properly ordered.
Sepal.Width Petal.Length Petal.Width Species NA
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3.0 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5.0 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
Hello,
when trying to view a shiny app using excelR in Internet explorer 11 the table does not show up.
Upon investigation the error code provided by IE is this
SCRIPT1014: Incorrect character jexcel.js (54,50)
I think the problematic character is a backquote.
My app is running in a shiny server and I am running excelR version 0.4.0.
Thank you for your great work, I really appreciate it.
After use of getComments()
the elements of input$table
change from ; "data", "colHeaders", "colTypes, and "forSelectedVals" to just "comments". Seeing as this updates input$table
, any observeEvent(input$table)
that calls excel_to_r()
will fail unless an explicit check of the names of input$table
and will not be able to access the data in table
until an explicit change is made to the data in the table.
Possible expected behavior would be for input$table
to always have "comments" as an element which gets populated upon calls to getComments()
input
are printed to the consoleexcel_to_r()
will faillibrary(excelR)
library(shiny)
shinyApp(
ui = fluidPage(
actionButton('set', 'Set Comments to cell A1'),
actionButton('get', 'Get Comments from cell A1'),
textAreaInput("fetchedComment", "Comments from A1:"),
excelOutput("table", height = 175)
),
server = function(input, output, session) {
output$table <-
renderExcel(excelTable(data = head(iris, 2), allowComments = TRUE))
# Set the comment
observeEvent(input$set, {
setComments("table", "A1", "This is a comment")
})
# Get the comment
observeEvent(input$get, {
getComments("table", "A1")
})
# Print the comment to the text area
observeEvent(input$table, {
updateTextAreaInput(session, 'fetchedComment', value = input$table$comment)
cat("names of input\n")
print(names(input$table))
if ("data" %in% names(input$table)) {
cat("\nThe table data is currently available\n")
print(head(excel_to_R(input$table), 1))
} else{
cat("\nThe table data is not available")
}
})
}
)
Add function to get and set comments in excelTable in Shiny
Using both the dev and latest cran version and editing a calendar type column I receive the error character string is not in a standard unambigous format
.
Stepping into the code and printing the JSON object without using excel_to_R
I can see that the calendar input is of the format YYYY-MM-DD 00:00:00
instead of just YYYY-MM-DD
.
However, I cannot reproduce this issue by using the example below.
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(excelOutput("table")),
server = function(input, output, session) {
iris <- iris
iris$test <- as.Date("2019-01-01")
output$table <- renderExcel(excelTable(data = head(iris)))
observeEvent(input$table,{
print(excel_to_R(input$table))
})
}
)
Please refer to the attached print-screens for the JSON output and associated excelTable
changed table cell.
Hello team,
Thanks for this great package.
I am currently using DT for creating tables in my ShinyApp. I was looking for substitutes for the DT package and found excelR's name in out of the articles on the internet. When I gave excelR a try, it looks great - especially the checkboxes for Logical values, row and column resizing, use of excel formulae etc.
But one thing I think is missing (or I don't know how to get it as I don't know JS) is the row selected / column selected / cell selected upon clicking on table. In DT, finding the rows clicked is easy : we need to use input$table_id_rows_selected. But couldn't find any such argument in excelTable() function.
An argument like getSelectedRow to the excelTable(), similar to getSelectedData will be very helpful.
I couldn't think of an alternative that could help me solve this problem, may be because I am newbie to shiny packages powered by JavaScript.
It would be great if you can add this to future versions.
Thanks.
Cheers,
Mrugank D. Chinchkhede
Would it be reasonable to add column type mapping in excelTable if param columns$type is missing?
Something like in rhandsontable https://github.com/jrowen/rhandsontable/blob/639b0a511b216391df44c45ea7ef8c934269c28a/R/rhandsontable.R#L69
Dear,
Congratulations on the package. I would like to know if it is possible to return the table index when the user selects a certain cell. Suppose the user wants to merge columns 1 and 2 of row 1. So, I need to know these indexes when the user selects such cells.
Thank you.
Describe the bug
After shiny table edit (e.g. put char 'a' into Sepal.Length):
To Reproduce
Steps to reproduce the behavior:
library(shiny)
library(excelR)
dt <- iris[1:10,]
dt$Species <- as.character(dt$Species)
dt$date <- Sys.Date()
shinyApp(
ui = fluidPage(excelOutput("table")),
server = function(input, output, session) {
rv <- reactiveValues(
dt = dt
)
output$table <- renderExcel(
excelTable(data = rv$dt,
columns = data.frame(
title = colnames(rv$dt),
type = excelR:::get_col_types(rv$dt)
))
)
observeEvent(input$table,{
str(rv$dt)
xls <- excel_to_R(input$table)
rv$dt <- xls
str(rv$dt)
})
}
)
'data.frame': 10 obs. of 6 variables:
$ Sepal.Length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9
$ Sepal.Width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1
$ Petal.Length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5
$ Petal.Width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1
$ Species : chr "setosa" "setosa" "setosa" "setosa" ...
$ date : Date, format: "2019-10-02" "2019-10-02" "2019-10-02" ...
'data.frame': 10 obs. of 6 variables:
$ : Factor w/ 7 levels "4.4","4.6","4.7",..: 7 4 3 2 5 6 2 5 1 4
$ : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1
$ : num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5
$ : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1
$ : chr "setosa" "setosa" "setosa" "setosa" ...
$ : Date, format: "2019-10-02" "2019-10-02" "2019-10-02" ...
other attached packages:
[1] excelR_0.3.1 shiny_1.3.2
Is there a way to limit character input into numeric column? So that only numeric values are accepted. Date columns already don't allow character input
I'm very excited to see this package, so thank you for creating this! One key advantage I see over rhandsontable
right away is the ability to rename the column when right-clicking. While adding/removing rows or columns can be registered in a Shiny app, I noticed that after renaming the column that the data frame is still having the old column name afterwards. Below is a simple reprex that shows the issue. Happy to help provide more debugging output if it helps.
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(excelOutput("table"), verbatimTextOutput("out")),
server = function(input, output, session) {
values = reactiveValues()
data = reactive({
if (!is.null(input$table)) {
tmp <- input$table
DF = excel_to_R(input$table)
} else {
if (is.null(values[["DF"]]))
DF = iris[1:10,]
else
DF = values[["DF"]]
}
values[["DF"]] = DF
DF
})
output$table <- renderExcel({
DF = data()
if (!is.null(DF)) {
excelTable(DF)
}
})
output$out <- renderPrint({
data()
})
}
)
Dear Swechhya,
Can we use the onchange
event?
I tried the following, but nothing happens when I change the table value.
onchange <- "function(instance, cell, x, y, value) {
alert('test');
}"
excelTable(
data = df,
editable = TRUE,
wordWrap = TRUE,
autoWidth = TRUE,
autoFill = FALSE,
lazyLoading = TRUE,
loadingSpin = TRUE,
getSelectedData = FALSE,
columnSorting = FALSE,
columnResize = FALSE,
rowResize = FALSE,
columnDrag = FALSE,
rowDrag = FALSE,
allowInsertRow = FALSE,
allowInsertColumn = FALSE,
allowDeleteRow = FALSE,
allowDeleteColumn = FALSE,
allowRenameColumn = FALSE,
allowComments = FALSE,
columns = columns_df,
# ...
tableOverflow = TRUE,
contextMenu = FALSE,
defaultColAlign = 'left',
onchange = htmlwidgets::JS(onchange)
)
To Reproduce
Steps to reproduce the behavior:
dt <- data.frame(
a = rep(c(1, NA), 3),
b = as.character(rep(c(1, NA), 3)),
c = as.factor(rep(c(1, NA), 3))
)
dt1 <- excelR::excelTable(
data = dt
)
dt1
dt2 <- excelR::excelTable(
data = dt,
columns = data.frame(
type = excelR:::get_col_types(dt)
)
)
dt2
[1] "Component “x”: Component “colHeaders”: Lengths: 3, 1"
[2] "Component “x”: Component “colHeaders”: Attributes: < target is NULL, current is list >"
[3] "Component “x”: Component “colHeaders”: target is character, current is json"
[4] "Component “x”: Component “columns”: 1 string mismatch"
I am trying to use excelR for editable tables that can be reset by a button. The code below is a simplified extract from a larger app. I see two issues when I do this, although one might be a feature request:
When editing a cell once and then discarding, the table behaves as expected. I can make as many different edits as I want and get the expected result, but when I edit the same cell twice in the same way, the second edit (and subsequent edits) do not show up as changed. E.g., if you make the value of A1 5 and reset, and then make A1 5 again, the change is not registered.
There doesn't seem to be a way to extract the value of input$data without editing the table. I.e., if I remove the line
rvals$changes <- FALSE
, the button is always shown and the value of rvals$changes is always TRUE after the first edit, even though the table shows the reset values. In this simplified example it doesn't matter, but in a larger context it makes excelR much harder to use.
library(shiny)
library(excelR)
library(compare)
edit_table_ui <- function(id, title) {
ns <- NS(id)
div(
textOutput(ns("edits")),
excelOutput(ns("data")),
conditionalPanel(
condition = "output.edits == 'TRUE'",
ns=ns,
br(),
shiny::actionButton(ns("discard"), "Discard Changes")
)
)
}
edit_table_server <- function(id, df, changes, baseline) {
moduleServer(id, function(input, output, session) {
rvals <- reactiveValues(data = isolate(df()), changes = isolate(changes()))
observe({
rvals$data <- df()
})
# Check whether changes were made to table
observeEvent(input$data, {
rvals$data <- excel_to_R(input$data)
rvals$changes <- !isTRUE(compare(baseline, rvals$data, ignoreNames = TRUE))
})
# Render table as rhandsontable
output$data <- renderExcel(excelTable(rvals$data))
# Discard changes and reset table
observeEvent(input$discard, {
rvals$data <- baseline
rvals$changes <- FALSE
})
output$edits <- renderText(rvals$changes)
outputOptions(output, "edits", suspendWhenHidden = FALSE)
})
}
app_data <- head(iris)
shinyApp(
ui = fluidPage(excelOutput("table"), textOutput('c'), edit_table_ui("tbl2", "excelR")),
server = function(input, output, session) {
r <- reactiveValues(data = app_data, changes = FALSE)
edit_table_server("tbl2", reactive(r$data), reactive(r$changes), app_data)
}
)
Hi!
Thanks for your excelR package!
I am trying to update table content from Shiny but creates duplicate table rather than replaces existing one.
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(
radioButtons(inputId = 'data', label = 'Data', choices = c('iris', 'mtcars')),
excelOutput("table")
),
server = function(input, output, session) {
output$table <- renderExcel({
dt <- get(input$data)
excelTable(data = dt)
})
}
)
sessionInfo:
R version 3.6.0 (2019-04-26)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1Matrix products: default
locale:
[1] LC_COLLATE=Latvian_Latvia.1257 LC_CTYPE=Latvian_Latvia.1257
[3] LC_MONETARY=Latvian_Latvia.1257 LC_NUMERIC=C
[5] LC_TIME=Latvian_Latvia.1257attached base packages:
[1] stats graphics grDevices utils datasets methods baseother attached packages:
[1] excelR_0.2.0 shiny_1.3.2loaded via a namespace (and not attached):
[1] Rcpp_1.0.1 digest_0.6.18 later_0.8.0 mime_0.6 R6_2.4.0
[6] xtable_1.8-4 jsonlite_1.6 magrittr_1.5 rlang_0.3.4 promises_1.0.1
[11] tools_3.6.0 htmlwidgets_1.3 httpuv_1.5.1 yaml_2.2.0 compiler_3.6.0
[16] htmltools_0.3.6
Possible solution is to add mask options in column data.frame
dt <- data.frame(
a = 1:5,
b = NA,
c = NA
)
excelR::excelTable(
data = dt,
columns = data.frame(
title = c('a', 'b', 'c'),
type = c('integer', 'numeric', 'numeric'),
mask = c('#', '#.#', '')
)
)
There are some open issues with masks
jspreadsheet/ce#630
jsuites/jsuites#14
This is a wonderful package, I'm really enjoying exploring all of its features and how it can improve my shiny apps.
With regards to styling (described here https://swechhya.github.io/excelR/#style ) is it possible to apply style to an entire row, or an entire column (as opposed to one cell at a time).
I may have rather large tables and writing hundreds of rows of the same style setting will be tedious. (eg. A1 =, A2 =, A3 =, A4 =, A5= ..... A301 =, A302 =, etc)
Preferred solution for column:
style = list(A:A='background-color:orange; color:green;'')
Preferred solution for row:
style = list(11:11='background-color:orange; color:green;'')
Preferred solution for range:
style = list(A1:D4='background-color:orange; color:green;'')
Many thanks
:)
Describe the bug
I want to copy all the numbers to the same value, but I don't know how.
Even with autofill = FALSE, if you grab the dot at the bottom right and lower it, the value will automatically increase.
To Reproduce
library(excelR)
data = data.frame(Model = c(10,"","",""),
Date=c('2006-01-01', '2005-01-01','2004-01-01', '2003-01-01' ),
Availability = c(TRUE, FALSE, TRUE, TRUE))
columns = data.frame(title=c('Model', 'Date', 'Availability'),
width= c(300, 300, 300),
type=c('text', 'calendar', 'checkbox'))
excelTable(data=data, autoFill = FALSE, columns = columns)
Expected behavior
If autofill = FALSE, no auto increment.
Desktop (please complete the following information):
You have done a great job, but from previous experience with complex JavaScript libraries, testing has been extremely helpful for me. For instance, the logic for handling colHeaders
and columns
is perfect, but in future version and as things change, a test would help insure expected behavior.
I was wondering if there was a way to make part of an excelTable
read-only (e.g. a cell, several specific cells, a row, a column...). The excelTable()
function has the editable
argument, which makes the entire table editable or not.
Thank you
Can you remove check on date format? Because jexcel supported more different format date. For Example 'DD.MM.YYYY', 'MM.DD.YYYY', 'YYYY.MM.DD' and other kind formats.
Line 419 in a9fdb9b
Is this possible to add a scroll_x function when the table have too many columns?
It is hard t show a data frame in shiny when it have over 30 columns.
And this function is used in package like DT or rhandsontable😂😂but I think this package is more convenient to use
Hello!
This excelTable and shiny integration functions are really awesome, thanks for the package!
I'm using excelTable under a shinyApp, but it is not fitting properly inside a box( or even without a box), also I'm unable to adjust the Height and Width of excelTable under shiny.
If I make fullscreen='TRUE', it is covering full screen from left but going out of the window on the right side. I want to fix the table with some specified height and width, or else any options like auto which fits perfectly in the table similar to datatable from the package DT.
Not sure if there any options already to achieve this, or else it will be really helpful if you can add this options.
Please suggest.
Thanks,
This seems like a bug:
Describe the bug
In shiny app, when a column is deleted, the column header is not deleted accordingly. Results in an error when call "Excel_to_R" function.
To Reproduce
Steps to reproduce the behavior:
library(excelR)
library(shiny)
ui <- fluidPage(
excelOutput("tab")
)
server <- function(input, output, session) {
df <- data.frame(
l = c("A", "B"),
n = 1:2,
stringsAsFactors = FALSE
)
output$tab <- renderExcel(
excelTable(df)
)
observe(
print(input$tab)
)
}
shinyApp(ui, server)
Delete the second column from the shiny app, then the console prints:
$data
$data[[1]]
$data[[1]][[1]]
[1] "A"
$data[[2]]
$data[[2]][[1]]
[1] "B"
$col Headers
$colHeaders[[1]]
[1] "l"
$colHeaders[[2]]
[1] "n"
$colType
$colType[[1]]
[1] "text"
$forSelectedVals
[1] FALSE
Expected behavior
Please note there are still two elements in the "colHeaders", it should return sth like this:
$data
$data[[1]]
$data[[1]][[1]]
[1] "A"
$data[[2]]
$data[[2]][[1]]
[1] "B"
$colHeaders
$colHeaders[[1]]
[1] "l"
$colType
$colType[[1]]
[1] "text"
$forSelectedVals
[1] FALSE
Thanks for you work!!! excelR is great!!!
Describe the bug
Date and character columns are converted to factor after editing any cell
To Reproduce
Steps to reproduce the behavior:
library(shiny)
library(excelR)
dt <- iris[1:10,]
dt$Species <- as.character(dt$Species)
dt$date <- Sys.Date()
shinyApp(
ui = fluidPage(excelOutput("table")),
server = function(input, output, session) {
rv <- reactiveValues(
dt = dt
)
output$table <- renderExcel(
excelTable(data = rv$dt, autoColTypes = TRUE)
)
observeEvent(input$table,{
str(rv$dt)
xls <- excel_to_R(input$table)
rv$dt <- xls
str(rv$dt)
})
}
)
'data.frame': 10 obs. of 6 variables:
$ Sepal.Length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9
$ Sepal.Width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1
$ Petal.Length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5
$ Petal.Width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1
$ Species : chr "setosa" "setosa" "setosa" "setosa" ...
$ date : Date, format: "2019-09-17" "2019-09-17" "2019-09-17" ...
'data.frame': 10 obs. of 6 variables:
$ Sepal.Length: num 1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9
$ Sepal.Width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1
$ Petal.Length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5
$ Petal.Width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1
$ Species : Factor w/ 1 level "setosa": 1 1 1 1 1 1 1 1 1 1
$ date : Factor w/ 1 level "2019-09-17": 1 1 1 1 1 1 1 1 1 1
Desktop (please complete the following information):
R version 3.6.1 (2019-07-05)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17763)Matrix products: default
locale:
[1] LC_COLLATE=Latvian_Latvia.1257 LC_CTYPE=Latvian_Latvia.1257
[3] LC_MONETARY=Latvian_Latvia.1257 LC_NUMERIC=C
[5] LC_TIME=Latvian_Latvia.1257attached base packages:
[1] stats graphics grDevices utils datasets methods baseother attached packages:
[1] excelR_0.3.0 shiny_1.3.2loaded via a namespace (and not attached):
[1] Rcpp_1.0.2 digest_0.6.20 later_0.8.0 mime_0.7 R6_2.4.0
[6] xtable_1.8-4 jsonlite_1.6 magrittr_1.5 rlang_0.4.0 promises_1.0.1
[11] tools_3.6.1 htmlwidgets_1.3 httpuv_1.5.1 yaml_2.2.0 rsconnect_0.8.15
[16] compiler_3.6.1 htmltools_0.3.6
excelR
renders great with Shiny, but since it allows changes in the table, it would be great to capture those changes in R similar to rhansontable
. Fortunately, jExcel
fires events when changes are made, but unfortunately without tranformation the objects on the JavaScript side the values are passed as a vector
.
I put a quick example together below.
library(shiny)
library(excelR)
ui <- excelOutput("excel")
server <- function(input, output, session) {
output$excel <- renderExcel({
excel <- excelTable(matrix(1:100, ncol=10))
excel$x$onchange <- htmlwidgets::JS(
"
function(obj,cell,value) {
debugger
if(HTMLWidgets.shinyMode) {
Shiny.setInputValue(obj.id, this.data)
}
}
"
)
excel
})
observeEvent(input$excel, {
print(input$excel)
})
}
shinyApp(ui,server)
I am happy to work on this, but I wanted to make sure you agreed with the idea. Thanks!
Describe the bug
Fix failing tests on latest R version.
Link to failing test 👉 https://cran.r-project.org/web/checks/check_results_excelR.html
Expected behavior
All the tests should pass in latest R version
Great work, I saw jExcel
and hoped someone would make it into an htmlwidget
. I hope you don't mind me barging in without any permission, but I couldn't find any way to contact you.
RStudio viewer did not become modern until very recently, which means many users of older versions of RStudio will not be able to use excelR
.
I will submit a pull rewriting JavaScript in ancient form :)
If you agree this is a good thing, then please review that I didn't miss anything. The only real question I had was on style
. I could not find this argument listed in the jExcel
docs so I could not test.
Thanks!!!!
Many thanks (again) for developing this package!
Using the package with shiny
it would be awsome if the possibility to easily retrieve the selected row for an excelTable()
object could be implemented. This is needed for example if a graph should be updated depending on the row chosen in the table.
I can see from using the Inspector in Google Chrome that the class of <td>
and <tr>
inside the jexcel changes to "selected" when switching rows/cells, but my knowledge of JavaScript is too poor to make any use of it...
Hello again!
If the row and column numbers of the selected data that is available through get_selected_data()
could be made available this would be awsome.
Currently there is no way for a script to know exactly which columns / rows to directly filter the selected data on, as the row and column ids of the selected data is not returned.
This greatly limits the function's capabilities as a "filter & select" tool, as one has to know which column the get_selected_data
represents beforehand or do a risky compare of the data.
Thank you for the super fast response on the last feature request!
Downloading the dev (github) version I noticed that the excel_to_R()
function now behaves differently than from the latest cran release.
Instead of returning the full excel table it now only returns the changes for the edited cell. Is this an intended behaviour or a bug (I did not find it under the changelog)?
The current behaviour makes it almost impossible to extract the actual changes made by a user (as you don't know which row or column was edited by the callback).
The current (unintended?) behaviour can be seen from the example at https://swechhya.github.io/excelR/index.html
library(shiny)
library(excelR)
shinyApp(
ui = fluidPage(excelOutput("table")),
server = function(input, output, session) {
output$table <-
renderExcel(excelTable(data = head(iris)))
observeEvent(input$table,{
print(excel_to_R(input$table))
})
}
)
In the "Reading changed data from excel table in Shiny" found here if you load this and delete any column it throws an error
Warning: Error in <-: 'names' attribute [5] must be the same length as the vector [4]
74: colnames<-
73: excel_to_R
72: observeEventHandler [C:\myfilepath]
1: runApp
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.