Best IT Recipes

Copy / Paste from Excel to a web page

Is there a standard way or library for copying and pasting from a table into a web form? When I select more than one cell from Excel, I (obviously) lose the separator and everything is pasted into one cell of the web form. Do I need to do this in VB? or can the processing be performed after the insert action is launched in the web form?

+61
javascript html excel forms
Jan 05
source share
8 answers

You do not lose the delimiters, the cells are separated by tabs ( \t ) and rows with newline lines ( \n ), which may not be visible on the form. Try it yourself: copy the content from Excel to notepad, and you will see that your cells are well-built. It is easy to separate the fields into tabs and replace them with something else, so you can even build a table from them. Here is an example using jQuery:

 var data = $('input[name=excel_data]').val(); var rows = data.split("\n"); var table = $('<table />'); for(var y in rows) { var cells = rows[y].split("\t"); var row = $('<tr />'); for(var x in cells) { row.append('<td>'+cells[x]+'</td>'); } table.append(row); } // Insert into DOM $('#excel_table').html(table);

Thus, essentially, this script creates an HTML table from the inserted Excel data.

+62
Jan 05
source share

In response to Tatu's answer, I created a quick jsFiddle to demonstrate its solution:

http://jsfiddle.net/duwood/sTX7y/

HTML

 <p>Paste excel data here:</p> <textarea name="excel_data" style="width:250px;height:150px;"></textarea><br> <input type="button" onclick="javascript:generateTable()" value="Genereate Table"/> <br><br> <p>Table data will appear below</p> <hr> <div id="excel_table"></div>

Js

 function generateTable() { var data = $('textarea[name=excel_data]').val(); console.log(data); var rows = data.split("\n"); var table = $('<table />'); for(var y in rows) { var cells = rows[y].split("\t"); var row = $('<tr />'); for(var x in cells) { row.append('<td>'+cells[x]+'</td>'); } table.append(row); } // Insert into DOM $('#excel_table').html(table); }
+14
Jul 08 '14 at 14:19
source share

The same idea as Tatu (thanks, I need it soon in our project), but with a regular expression.
What could be faster for a large dataset.

 <html> <head> <title>excelToTable</title> <script src="../libs/jquery.js" type="text/javascript" charset="utf-8"></script> </head> <body> <textarea>a1 a2 a3 b1 b2 b3</textarea> <div></div> <input type="button" onclick="convert()" value="convert"/> <script> function convert(){ var xl = $('textarea').val(); $('div').html( '<table><tr><td>' + xl.replace(/\n+$/i, '').replace(/\n/g, '</tr><tr><td>').replace(/\t/g, '</td><td>') + '</tr></table>' ) } </script> </body> </html>
+8
Jan 05 '10 at 2:59
source share

For all future googlers ending here like me, I used the @tatu Ulmanen concept and just turned it into an array of objects. This simple function takes an excel (or Google sheet) embedded data string (preferably from textarea ) and turns them 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 < rows.length; rowNr++) { var o = {}; var data = rows[rowNr].split('\t'); //Loop through all the data for (var cellNr = 0; cellNr < data.length; cellNr++) { o[columns[cellNr]] = data[cellNr]; } objects.push(o); } return objects; }

Hope this helps someone in the future.

+5
Nov 05 '15 at 17:57
source share

On OSX and Windows , there are several types of clipboards for different types of content. When you copy content to Excel, the data is stored in clear text and in the html clipboard.

The right way (which doesn't work for separator problems) is to parse the HTML. http://jsbin.com/uwuvan/5 is a simple demo showing how to get the HTML clipboard. The key is binding to the onpaste event and reading

 event.clipboardData.getData('text/html')
+2
Sep 21 '13 at 22:08
source share

Perhaps it would be better if you read your excel file with PHP, and then either save it to the database or do some processing on it.

here is a tutorial on reading and writing Excel data using PHP:
http://www.ibm.com/developerworks/opensource/library/os-phpexcel/index.html

0
Jan 05 '10 at 14:13
source share

Excel 2007 has a function for this on the Data tab, which works very well.

0
Jan 05 '10 at 14:28
source share

Dig it if someone comes across it in the future. I used the above code as intended, but then ran into a problem displaying the table after it was sent to the database. This is much easier if you saved data to use PHP to replace newlines and tabs in your query. You can do the replacement on presentation, $ _POST [request] will be the name of your text field:

 $postrequest = trim($_POST[request]); $dirty = array("\n", "\t"); $clean = array('</tr><tr><td>', '</td><td>'); $request = str_replace($dirty, $clean, $postrequest);

Now just paste $ request into your database and it will be saved as an HTML table.

0
Jan 27 '13 at 18:50
source share


More articles:


All Articles