Shiny & handsontable JS spreadsheet component

[Yann Richet]

Hi,

I tried to link the handsontable spreadsheet component to shiny, as I think the shinyIncubator/matrixInput is not sufficient in my case.

I well display the handsontable component, which data comes from server.R, but I fail to implement the javascript events triggering part to return modification of datat table.

I carefully tried to inspire from tableinput.js in matrixInput, but without success. (I am surely not a javascript guru :)

=========== ui.R

library(shiny)

library(shinyIncubator)

shinyUI(pageWithSidebar(  

  headerPanel("Title"),

  sidebarPanel(

    tags$head(

      tags$script(src = "jquery.handsontable.full.js"),

      tags$script(src = "shiny-handsontable.js"),

      tags$link(rel="stylesheet", media="screen", href = "jquery.handsontable.full.css")

    )

  ),

  

  mainPanel(

    #matrixInput(inputId="matrixInput",label="matrixInput",data=cars),

    uiOutput("example"),

    tableOutput(outputId="result")

  )

))

=========== server.R

shinyServer(function(input, output) {

  

  output$result <- renderTable({

    return(input$example)

  })

   

  

  output$example <- renderUI({

    return(tags$div(class="handsontable",tags$script(script.data(cars,"example"))))

  })

  

  #   ["2010", 5, 2905, 2867, 412, 5284]

  print.line <- function(line) {

    return(paste('["',paste(line,collapse='","'),'"]',sep=''))

  }

  #   ["", "Maserati", "Mazda", "Mercedes", "Mini", "Mitsubishi"],

  #   ["2009", 0, 2941, 4303, 354, 5814],

  #   ["2012", 2, 2422, 5399, 776, 4151]

  print.data <- function(data) {

    str = print.line(names(data))

    for (i in 1:nrow(data))

      str = paste(str,print.line(data[i,]),sep=",\n")

    return(str)

  }

  

  

  #   <script>

  #     var data = [

  #       ["", "Maserati", "Mazda", "Mercedes", "Mini", "Mitsubishi"],

  #       ["2009", 0, 2941, 4303, 354, 5814],

  #       ["2012", 2, 2422, 5399, 776, 4151]

  #       ];

  #   

  #   $('#example').handsontable({

  #     data: data,

  #     minRows: 5,

  #     minCols: 6,

  #     minSpareRows: 1,

  #     autoWrapRow: true,

  #     colHeaders: true,

  #     contextMenu: true

  #   });

  #   

  #   $('.ver').html($('#example').data('handsontable').version);

  #   </script>

  script.data <- function(data,name) {

    return(gsub("__name__",name,paste(sep="\n",

                 "var __name__ = [",

                 print.data(data),

                 "    ];",

                 "$('#__name__').handsontable({",

                 "  data: __name__,",

                 "  minRows: 5,",

                 "  minCols: 2,",

                 "  minSpareRows: 1,",

                 "  autoWrapRow: true,",

               #  "  colHeaders: true,",

                 "  contextMenu: true",

                 "});",

                 "$('.ver').html($('#__name__').data('handsontable').version);")))

  }

  

})

============== www/shiny-handsontable.js

var tableInputBinding = new Shiny.InputBinding();

  $.extend(tableInputBinding, {

    find: function(scope) {

      return scope.find('table.htCore');

    },

    getValue: function(el) {

      alert("getValue");

      var data = [];

      var dataTable = {};

      $(el).find('tr').each(function() {

        var divs = $(this).find('td');

        if (divs.length) {

          divs.each(function(i, div) {

            if (data.length <= i) {

              data.push([]);

              dataTable['V' + (i+1)] = data[data.length-1];

            }

            data[i].push($(div));//getValidator($(div)).parseInput($(div)));

          });

        }

      });

      

      if (data.length > 0 && data[0].length != data[data.length-1].length) {

        throw "Error retrieving data from table--data was not rectangular";

      }

      return data;

    },

    setValue: function(el) {

    },

    getType: function(el) {

      return "matrix";

    },

    subscribe: function(el, callback) {

      $(el).on('change.htCore', function(e) { callback(); });

    },

    unsubscribe: function(el) {

      $(el).off('')

    }

  });

  Shiny.inputBindings.register(tableInputBinding);

If anybody here has sufficient javascript knowledge (which may be not so rare, compared to my own skills :), I am interested in your advice...

Best regards,

[Joe Cheng]

Very cool table, I had not heard of this one!

In this little chunk:

                 "$('#__name__').handsontable({",

                 "  data: __name__,",

                 "  minRows: 5,",

                 "  minCols: 2,",

                 "  minSpareRows: 1,",

                 "  autoWrapRow: true,",

               #  "  colHeaders: true,",

                 "  contextMenu: true",

                 "});",

You want to add something to the effect of:

"  onChange: function() { $('#__name__').trigger('change'); }, "

Also in tableInputBinding's unsubscribe, it should be $(el).off('.htCore')

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Yann Richet]

Well, I tried that, but it seems that the triggering is still not working.

As I did not know what to put instead of the 

$(document).on("click", "button.increment", function(evt) {

  // evt.target is the button that was clicked
  var el = $(evt.target);

  // Set the button's text to its current value plus 1
  el.text(parseInt(el.text()) + 1);

  // Raise an event to signal that the value changed
  el.trigger("change");
});

provided in tutorial, I put nothing... This is surely a bad idea, and I don't know if what you suggested is sufficient.

Following your suggestion, in my server.R, the code became :

  script.data <- function(data,name) {

    return(gsub("__name__",name,paste(sep="\n",

                 "var __name__ = [",

                 print.data(data),

                 "    ];",

                 "$('#__name__').handsontable({",

                 "  data: __name__,",

                 "  minRows: 5,",

                 "  minCols: 2,",

                 "  minSpareRows: 1,",

                 "  autoWrapRow: true,",

               #  "  colHeaders: true,",

                 "  contextMenu: true,",

                 "  onChange: function() { $('#__name__').trigger('change');}", 

[Yann Richet]

Still ugly code, but working:

runGitHub("try-shiny","yannrichet",subdir="handsontable")

[Maxim Korneev]

That's really great. Works like a charm for tables with numbers.
Will see if I can add editing of text columns as well.

[Yann Richet]

No, for now, the cast is done for numeric. But should be changed somewher in the toJson* functions.

[DZJ]

I am working on a version that can do text. Unfortunately I didn't see this before I started. I will be studying it closely even though I've written code similar to it already.

[DZJ]

Hi Yann,

Have you been modifying the code? I can't seem to get it to show me things.

[ZJ]

http://spark.rstudio.com/xiaodai/CloudScorer_alpha/

Checkout some implementation of handsontable. See Binning Panel for example. Be a bit paitent as lags can happen sometimes.

[Andrew Andrade]

Any chance any of you were able to figure a good solution out for this? 

[Enzo]

ZJ implementation is at https://github.com/AnalytixWare/ShinySky (it is embedded alongside loads of other stuff).

Jeff Allen has posted a feature-reacher implementation (with its own problems: it doesn't manage partials) here: https://github.com/trestletech/shinyTable