Menu
Using Javascript, how do I create an HTML table that can "accept" numeric matrix data from excel (or google spreadsheet), via "copy" in the spreadsheet and then "paste" into the table in the browser.
496
On your XLSX document "Select All" then "copy" the data, come to this page, put focus on the <textarea> box and "paste" the text in. In some instance (large data sets) this might take a couple of seconds. Once there simply click the button to product the table.
The insertRow() method creates an empty <tr> element and adds it to a table. The insertRow() method inserts the new row(s) at the specified index in the table. Note: A <tr> element must contain one or more <th> or <td> elements.
import readXlsxFile from 'read-excel-file' const input = document. getElementById('input') input. addEventListener('change', () => { readXlsxFile(input. files[0]).
In Excel, highlight the data in one cell or in a range of cells, and press Ctrl+C to copy the data onto the clipboard. Highlight and select the target cell or cells in the Planning Web form, and then press Ctrl+V. When the Clipboard helper is displayed, press Ctrl+V again. The data is pasted to the Clipboard helper.
This would only work reliably on IE since Firefox (and likely others) don't allow access to the clipboard without specifically allowing it; the earlier suggestion of pasting into a textarea first might work better than this.
When you copy from a spreadsheet, generally the cells are separated with a tab (chr9) and the rows with a CR (chr13). This script converts the clipboard into a 2D array and then builds a table from it. Not too elegant but it seems to work copying data out of Excel.
<html> <head> <script language="javascript"> function clip() { // get the clipboard text var clipText = window.clipboardData.getData('Text'); // split into rows clipRows = clipText.split(String.fromCharCode(13)); // split rows into columns for (i=0; i<clipRows.length; i++) { clipRows[i] = clipRows[i].split(String.fromCharCode(9)); } // write out in a table newTable = document.createElement("table") newTable.border = 1; for (i=0; i<clipRows.length - 1; i++) { newRow = newTable.insertRow(); for (j=0; j<clipRows[i].length; j++) { newCell = newRow.insertCell(); if (clipRows[i][j].length == 0) { newCell.innerText = ' '; } else { newCell.innerText = clipRows[i][j]; } } } document.body.appendChild(newTable); } </script> </head> <body> <input type="button" onclick="clip()"> </body> </html> Here is the javascript code I created (based on the helpful answers). I'm new to javascript, so I'm sure there is much better way to do this, but it seems to work... The goal is to "paste" two columns of numerical data into the text area from a spreadsheet (I've tried both excel and google spreadsheet) and create floating point vectors "xf" and "yf". Hopefully useful to someone. Criticism welcome...
It assumes these exist on an html page...
<textarea id="psmtext" rows=24 cols=72> </textarea> <input type="button" value="run code" onClick="psmtest();"> function psmtest(){ var psmtext = document.getElementById("psmtext"); var st = psmtext.value; Ast = st.split("\n"); var numrows = Ast.length;
var ii; var xs = []; var ys = []; for (ii = 0 ; ii < numrows ; ii++) { // tab or comma deliminated data if ( Ast[ii].split(",",2)[1] != null ){ ys[ii] = Ast[ii].split(",")[1]; xs[ii] = Ast[ii].split(",")[0];} if ( Ast[ii].split("\t",2)[1] != null ){ ys[ii] = Ast[ii].split("\t")[1]; xs[ii] = Ast[ii].split("\t")[0];} }
var xss = []; var yss = []; var numgoodrows = 0; var iii =0; for (ii = 0 ; ii < numrows ; ii++) { if ( xs[ii] != null && ys[ii] != null) { xss[iii] = xs[ii]; yss[iii] = ys[ii]; iii++; } } numgoodrows = iii; // next I need to convert to floating point array var xf = [], var yf = [];
var xf = []; var yf = []; for (ii = 0 ; ii < numgoodrows ; ii++) { xf[ii] = parseFloat(xss[ii]); yf[ii] = parseFloat(yss[ii]); }
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With