Copy Paste from Excel to a web page

Question

Is there a standard way or library to copy and paste from a spreasheet to a web form  When I select more than one cell from Excel I  obviously  lose the delimiter and all is pasted into one cell of the web form  Does it have to be done in VB  or could the processing be done once the paste action is started on the web form

User · Answer

On OSX and Windows   there are multiple types of clipboards for different types of content   When you copy content in Excel  data is stored in the plaintext and in the html clipboard     The right way  that doesn t get tripped up by delimiter issues  is to parse the HTML   http   jsbin com uwuvan 5 is a simple demo that shows how to get the HTML clipboard   The key is to bind to the onpaste event and read   event clipboardData getData  text html

User · Answer

Digging this up  in case anyone comes across it in the future   I used the above code as intended  but then ran into an issue displaying the table after it had been submitted to a database   It s much easier once you ve stored the data to use PHP to replace the new lines and tabs in your query   You may perform the replace upon submission    POST request  would be the name of your textarea    postrequest   trim   POST request     dirty   array   n     t     clean   array   lt  tr gt  lt tr gt  lt td gt      lt  td gt  lt td gt      request   str replace  dirty   clean   postrequest     Now just insert  request into your database  and it will be stored as an HTML table

User · Answer

For any future googlers ending up here like me  I used  tatu Ulmanen s concept and just turned it into an array of objects  This simple function takes a string of pasted excel  or Google sheet  data  preferably from a textarea  and turns it into an array of objects  It uses the first row for column property names   function excelToObjects stringData       var objects             split into rows     var rows   stringData split   n           Make columns     columns   rows 0  split   t           Note how we start at rowNr   1  because 0 is the column row     for  var rowNr   1  rowNr  lt  rows length  rowNr              var o               var data   rows rowNr  split   t               Loop through all the data         for  var cellNr   0  cellNr  lt  data length  cellNr                  o columns cellNr     data cellNr                      objects push o              return objects      Hopefully it helps someone in the future

User · Answer

UPDATE  This is only true if you use ONLYOFFICE instead of MS Excel   There is actually a flow in all answers provided here and also in the accepted one  The flow is that whenever you have an empty cell in excel and copy that  in the clipboard you have 2 tab chars next to each other  so after splitting you get one additional item in array  which then appears as an extra cell in that row and moves all other cells by one  So to avoid that you basically need to replace all double tab  tabs next to each other only  chars in a string with one tab char and only then split it   An updated version of  userfuser s jsfiddle is here to fix that issue by filtering pasted data with removeExtraTabs  http   jsfiddle net sTX7y 794   function removeExtraTabs string      return string replace new RegExp   t t    g      t       function generateTable       var data   removeExtraTabs     pastein   val       var rows   data split   n      var table       lt table   gt        for  var y in rows        var cells   rows y  split   t        var row       lt tr   gt         for  var x in cells          row append   lt td gt     cells x      lt  td gt               table append row             Insert into DOM       excel table   html table        document  ready function           pastein   on  paste   function event            pastein   on  input   function           generateTable              pastein   off  input

User · Answer

Maybe it would be better if you would read your excel file from PHP  and then either save it to a DB or do some processing on it   here an in-dept tutorial on how to read and write Excel data with PHP   http   www ibm com developerworks opensource library os-phpexcel index html

User · Answer

You don t lose the delimiters  the cells are separated by tabs   t  and rows by newlines   n  which might not be visible in the form  Try it yourself  copy content from Excel to Notepad  and you ll see your cells nicely lined up  It s easy then to split the fields by tabs and replace them with something else  this way you can build even a table from them  Here s a example using jQuery   var data      input name excel data    val    var rows   data split   n     var table       lt table   gt      for var y in rows        var cells   rows y  split   t        var row       lt tr   gt         for var x in cells            row append   lt td gt   cells x    lt  td gt               table append row         Insert into DOM     excel table   html table     So in essence  this script creates an HTML table from pasted Excel data

User · Answer

In response to the answer by Tatu I have created a quick jsFiddle for showcasing his solution   http   jsfiddle net duwood sTX7y   HTML   lt p gt Paste excel data here  lt  p gt     lt textarea name  excel data  style  width 250px height 150px   gt  lt  textarea gt  lt br gt   lt input type  button  onclick  javascript generateTable    value  Genereate Table   gt   lt br gt  lt br gt       lt p gt Table data will appear below lt  p gt   lt hr gt   lt div id  excel table  gt  lt  div gt    JS  function generateTable         var data      textarea name excel data    val        console log data       var rows   data split   n         var table       lt table   gt          for var y in rows        var cells   rows y  split   t        var row       lt tr   gt         for var x in cells            row append   lt td gt   cells x    lt  td gt               table append row         Insert into DOM     excel table   html table

User · Answer

The same idea as Tatu thanks I ll need it soon in our project   but with a regular expression  Which may be quicker for large dataset    lt html gt   lt head gt       lt title gt excelToTable lt  title gt       lt script src     libs jquery js  type  text javascript  charset  utf-8  gt  lt  script gt   lt  head gt   lt body gt       lt textarea gt a1    a2  a3 b1  b2  b3 lt  textarea gt       lt div gt  lt  div gt       lt input type  button  onclick  convert    value  convert   gt       lt script gt          function convert                var xl      textarea   val                   div   html                     lt table gt  lt tr gt  lt td gt                      xl replace   n   i      replace   n g    lt  tr gt  lt tr gt  lt td gt    replace   t g    lt  td gt  lt td gt                         lt  tr gt  lt  table gt                                lt  script gt   lt  body gt   lt  html gt

User · Answer

Excel 2007 has a feature for doing this under the  Data  tab that works pretty nicely

[javascript] Copy/Paste from Excel to a web page

Examples related to javascript

Examples related to html

Examples related to forms

Examples related to excel