Using copy paste table data via dashboard

Dashboard
22

Nice.

No sign of spring here! Just cold, wet and windy. Driving wife and daughter round different fabric shops while they looked for fabric for daughter's prom dress. Followed by coffee and creme scone at a local stately home. :innocent:

Thanks for sharing. Interesting and certainly some ideas I'm going to investigate in my own new dashboard/control system.

23

Actually this helps a lot but i don´t undestand how to make my flow with html i will put my code @Christian-Me [{"id":"2965a5e7.2809fa","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"95273205.a823","type":"ui_table","z":"2965a5e7.2809fa","group":"615b9ae0.a9d4c4","name":"","order":6,"width":0,"height":0,"columns":[],"outputs":1,"cts":true,"x":870,"y":240,"wires":[[]]},{"id":"d12e67b8.c63008","type":"template","z":"2965a5e7.2809fa","name":"","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"<div id=\"example-table\"></div>\n//define data\nvar tabledata = [\n {id:1, name:\"Oli Bob\", location:\"United Kingdom\", gender:\"male\", rating:1, col:\"red\", dob:\"14/04/1984\"},\n {id:2, name:\"Mary May\", location:\"Germany\", gender:\"female\", rating:2, col:\"blue\", dob:\"14/05/1982\"},\n {id:3, name:\"Christine Lobowski\", location:\"France\", gender:\"female\", rating:0, col:\"green\", dob:\"22/05/1982\"},\n {id:4, name:\"Brendon Philips\", location:\"USA\", gender:\"male\", rating:1, col:\"orange\", dob:\"01/08/1980\"},\n {id:5, name:\"Margret Marmajuke\", location:\"Canada\", gender:\"female\", rating:5, col:\"yellow\", dob:\"31/01/1999\"},\n {id:6, name:\"Frank Harbours\", location:\"Russia\", gender:\"male\", rating:4, col:\"red\", dob:\"12/05/1966\"},\n {id:7, name:\"Jamie Newhart\", location:\"India\", gender:\"male\", rating:3, col:\"green\", dob:\"14/05/1985\"},\n {id:8, name:\"Gemma Jane\", location:\"China\", gender:\"female\", rating:0, col:\"red\", dob:\"22/05/1982\"},\n {id:9, name:\"Emily Sykes\", location:\"South Korea\", gender:\"female\", rating:1, col:\"maroon\", dob:\"11/11/1970\"},\n {id:10, name:\"James Newman\", location:\"Japan\", gender:\"male\", rating:5, col:\"red\", dob:\"22/03/1998\"},\n];\n\n//define table\nvar table = new Tabulator(\"#example-table\", {\n data:tabledata,\n autoColumns:true,\n});","output":"str","x":540,"y":280,"wires":[["818f2a54.e9bb08"]]},{"id":"818f2a54.e9bb08","type":"json","z":"2965a5e7.2809fa","name":"","property":"payload","action":"","pretty":false,"x":670,"y":260,"wires":[["95273205.a823"]]},{"id":"5cd72d5d.f7d494","type":"template","z":"2965a5e7.2809fa","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<div id=\"example-table\"></div>","output":"str","x":440,"y":200,"wires":[["d12e67b8.c63008"]]},{"id":"ab9309a5.aaca18","type":"inject","z":"2965a5e7.2809fa","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":240,"y":180,"wires":[["5cd72d5d.f7d494"]]},{"id":"615b9ae0.a9d4c4","type":"ui_group","z":"","name":"Lamp","tab":"8f4c42b3.c0e6","order":2,"disp":true,"width":"6","collapse":false},{"id":"8f4c42b3.c0e6","type":"ui_tab","z":"","name":"Room","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

24

hi i think this is what i was looking for but actually i'm kind of new in this and i don't know much how to use the funcions and the html @TotallyInformation

25

that is amazing, i don't get much about what you do but tha's impressive @Christian-Me

26

Nice! :sunglasses:

That overview has been on my To-Do list for some time now as well and has just moved up a few items. :grin:

27

Yes, sorry, we've gotten slightly off track as we often do here!

I think that the bottom line is that it isn't all that easy to paste data from Excel to a web page. Indeed, creating a data-driven table on a web page is quite hard, even with the excellent help from Node-RED and the Dashboard table addon.

There are probably other ways you could achieve something though. Personally, I would probably use a CSV file rather than an Excel workbook format (.xls, etc) and save it to somewhere that Node-RED could access it. Then you can easily consume it in Node-RED.

28

Hi,

I Imported your flow ... a litte bit confusing. You do not need any Template HTML if you use ui-table. Perhaps take a look in the examples of UI-Table first (Menu/Import/Example)

instead of

var table = new Tabulator("#example-table", {
 data:tabledata,
 autoColumns:true,
});

use msg.ui_control.tabulator message to configure tabulator as needed.
See Example 3 ui_control table

image
image637×538 12.7 KB

29

Annoyingly, ui-table isn't working for me at all at the moment. New issue raised:

30

Thanks to @TotallyInformation and @Christian-Me you guys are really helpfull and nice i will wait to ui-table node will works actually since yesterday i was like nothing appears, i will continue working on my dashboard.

31

@Christian-Me just help me with this i think i got pretty much of the example, but i have been trying this weeken and i really don't know how can i use my function to paste the information i will put you the flow and also the place where i'm getting information. [{"id":"2965a5e7.2809fa","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"10b9e62c.99b31a","type":"inject","z":"2965a5e7.2809fa","name":"","topic":"","payload":"[{\"name\":\"MEQ0451495\",\"room\":\"Bathroom\",\"SET_TEMPERATURE-value\":22,\"ACTUAL_TEMPERATURE-value\":21.8,\"VALVE_STATE-value\":90,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":true,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ1875547\",\"room\":\"Living Room\",\"SET_TEMPERATURE-value\":12,\"ACTUAL_TEMPERATURE-value\":16.2,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":1},{\"name\":\"MEQ1875538\",\"room\":\"Living Room\",\"SET_TEMPERATURE-value\":18,\"ACTUAL_TEMPERATURE-value\":19.5,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.6,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":2},{\"name\":\"MEQ0447462\",\"room\":\"Kitchen\",\"SET_TEMPERATURE-value\":17,\"ACTUAL_TEMPERATURE-value\":22.2,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":10,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":3},{\"name\":\"MEQ1875551\",\"room\":\"Office\",\"SET_TEMPERATURE-value\":18,\"ACTUAL_TEMPERATURE-value\":20.2,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ0447425\",\"room\":\"Dining Room\",\"SET_TEMPERATURE-value\":19,\"ACTUAL_TEMPERATURE-value\":20.4,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ1875546\",\"room\":\"Dining Room\",\"SET_TEMPERATURE-value\":20,\"ACTUAL_TEMPERATURE-value\":18.8,\"VALVE_STATE-value\":99,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ0447483\",\"room\":\"Bedroom\",\"SET_TEMPERATURE-value\":17,\"ACTUAL_TEMPERATURE-value\":22.4,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ1875541\",\"room\":\"Child\",\"SET_TEMPERATURE-value\":18,\"ACTUAL_TEMPERATURE-value\":20.4,\"VALVE_STATE-value\":0,\"BATTERY_STATE-value\":2.7,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0},{\"name\":\"MEQ1875552\",\"room\":\"Guest Room\",\"SET_TEMPERATURE-value\":20,\"ACTUAL_TEMPERATURE-value\":21.1,\"VALVE_STATE-value\":9,\"BATTERY_STATE-value\":2.8,\"BOOST_STATE-value\":0,\"AUTO_MODE-value\":false,\"CONTROL_MODE-value\":0}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":330,"y":260,"wires":[["dd80fbba.a3dfd8"]]},{"id":"dd80fbba.a3dfd8","type":"change","z":"2965a5e7.2809fa","name":"ui_control","rules":[{"t":"set","p":"ui_control.tabulator","pt":"msg","to":"{\"tabulator\":{\"columnResized\":\"function(column){ var newColumn = { field: column._column.field, visible: column._column.visible, width: column._column.width, widthFixed: column._column.widthFixed, widthStyled: column._column.widthStyled }; this.send({topic:this.config.topic,ui_control:{callback:'columnResized',columnWidths:newColumn}}); }\",\"columnMoved\":\"function(column, columns){ var newColumns=[]; columns.forEach(function (column) { newColumns.push({'field': column._column.field}); }); this.send({topic:this.config.topic,ui_control:{callback:'columnMoved',columns:newColumns}}); }\",\"groupHeader\":\"function (value, count, data, group) {return value + \\\"<span style='color:#d00; margin-left:10px;'>(\\\" + count + \\\" Termostat\\\"+((count>1) ? \\\"e\\\" : \\\"\\\") + \\\")</span>\\\";}\",\"columns\":[{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"ROom\",\"field\":\"room\",\"width\":100,\"frozen\":true},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Device\",\"field\":\"name\",\"width\":100,\"align\":\"center\"},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Type\",\"field\":\"deviceType\",\"width\":100,\"align\":\"center\"},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Measurements\",\"columns\":[{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"target\",\"field\":\"SET_TEMPERATURE-value\",\"formatter\":\"function(cell, formatterParams, onRendered){return cell.getValue()+'°C';}\",\"topCalc\":\"avg\",\"width\":100},{\"formatterParams\":{\"target\":\"_blank\",\"min\":10,\"max\":25,\"color\":[\"blue\",\"green\",\"red\"],\"legend\":\"function (value) {return '&nbsp;&nbsp;'+value+'°C';}\",\"legendColor\":\"#101010\",\"legendAlign\":\"left\"},\"title\":\"current\",\"field\":\"ACTUAL_TEMPERATURE-value\",\"formatter\":\"progress\",\"topCalc\":\"avg\",\"width\":100},{\"formatterParams\":{\"target\":\"_blank\",\"min\":0,\"max\":99,\"color\":[\"gray\",\"orange\",\"red\"],\"legend\":\"function (value) {return (value>0)? '&nbsp;&nbsp;'+value+' %' : '-';}\",\"legendColor\":\"#101010\",\"legendAlign\":\"center\"},\"title\":\"Valve\",\"field\":\"VALVE_STATE-value\",\"formatter\":\"progress\",\"topCalc\":\"max\",\"width\":100},{\"formatterParams\":{\"target\":\"_blank\",\"min\":1.5,\"max\":4.6,\"color\":[\"red\",\"orange\",\"green\"],\"legend\":\"function (value) {return value+' V';}\",\"legendColor\":\"#101010\",\"legendAlign\":\"center\"},\"title\":\"Batt\",\"field\":\"BATTERY_STATE-value\",\"formatter\":\"progress\",\"topCalc\":\"min\",\"width\":100}]},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Settings\",\"columns\":[{\"formatterParams\":{\"target\":\"_blank\",\"min\":0,\"max\":30,\"color\":[\"red\",\"orange\",\"green\"],\"legend\":\"function (value) { if (value>0) return \\\"<span style='color:#101010;'>\\\"+value+\\\" min</span>\\\"; else return \\\"<span style='color:#A0A0A0;'>aus</span>\\\"; }\",\"legendColor\":\"#101010\",\"legendAlign\":\"center\"},\"title\":\"Boost\",\"field\":\"BOOST_STATE-value\",\"formatter\":\"progress\",\"width\":100},{\"formatterParams\":{\"target\":\"_blank\",\"allowEmpty\":true,\"allowTruthy\":true,\"tickElement\":\"<i class='fa fa-clock-o'></i>\",\"crossElement\":\"<i class='fa fa-ban'></i>\"},\"title\":\"Auto\",\"field\":\"AUTO_MODE-value\",\"formatter\":\"tickCross\",\"width\":100,\"align\":\"center\"},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Mode\",\"field\":\"CONTROL_MODE-value\",\"formatter\":\"function(cell, formatterParams, onRendered){ var html=\\\"<i class=\\\\\\\"\\\"; switch(cell.getValue()) { case 0: html+=\\\"fa fa-calendar-check-o\\\"; break; case 1: html+=\\\"fa fa-hand-o-up\\\"; break; case 2: html+=\\\"fa fa-suitcase\\\"; break; case 3: html+=\\\"fa fa-spinner fa-spin fa-fw\\\"; break; } html+='\\\\\\\"></i>'; return html; }\",\"width\":100,\"align\":\"center\"},{\"formatterParams\":{\"target\":\"_blank\"},\"title\":\"Auto\",\"field\":\"AUTO_MODE-value\",\"formatter\":\"tick\",\"width\":100,\"align\":\"center\"}]}],\"layout\":\"fitColumns\",\"movableColumns\":true,\"groupBy\":\"\"},\"customHeight\":12}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":140,"wires":[["566e5c92.161a74"]]},{"id":"c358ec1c.ffd55","type":"debug","z":"2965a5e7.2809fa","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":789,"y":260,"wires":[]},{"id":"566e5c92.161a74","type":"ui_table","z":"2965a5e7.2809fa","group":"15157f81.1db74","name":"Thermostats","order":1,"width":"20","height":"7","columns":[],"outputs":1,"cts":true,"x":639,"y":260,"wires":[["c358ec1c.ffd55"]]},{"id":"2219afcd.494d1","type":"function","z":"2965a5e7.2809fa","name":"","func":"var table = new Tabulator(\"#example-table\", {\n height:\"311px\",\n data:tabledata,\n clipboard:true,\n clipboardPasteAction:\"replace\",\n columns:[\n {title:\"Name\", field:\"name\", width:200},\n {title:\"Progress\", field:\"progress\", width:100, sorter:\"number\"},\n {title:\"Gender\", field:\"gender\"},\n {title:\"Rating\", field:\"rating\", width:80},\n {title:\"Favourite Color\", field:\"col\"},\n {title:\"Date Of Birth\", field:\"dob\", align:\"center\", sorter:\"date\"},\n {title:\"Driver\", field:\"car\", align:\"center\", formatter:\"tickCross\"},\n ],\n });","outputs":1,"noerr":0,"x":470,"y":260,"wires":[[]]},{"id":"15157f81.1db74","type":"ui_group","z":"","name":"ui_control","tab":"1079ad9e.6b2382","order":1,"disp":true,"width":"22","collapse":false},{"id":"1079ad9e.6b2382","type":"ui_tab","z":"","name":"Home","icon":"track_changes","order":1,"disabled":false,"hidden":false}]. http://tabulator.info/docs/4.4/clipboard

32

Hi Eduardo,

I gave it a try on my train ride to the office and copy past is totally doable! Interesting way to enter data into Node-RED via the dashboard.
I will prepare a demo on my way home ...

Chris

3 Likes
33

Thanks @Christian-Me i really aprecciate that

34

And here you are:

image
image971×112 12.2 KB

You need only one callback function I included in a separate function note for better readability (do not wire this into your flow).
The change node does the preparation of the table with a msg.ui_control object. Because you need minimum one cell to paste into (you can`t paste into an empty table) I put in some dummy data
It is not necessary to configure the the table node only tick "send data on click" to activate the output.

copy-paste
copy-paste820×387 82.7 KB

And in Node-RED:
image

Here is the fow:

[{"id":"4512106a.2f145","type":"change","z":"4ddb981f.f34df8","name":"ui_control & placeholder","rules":[{"t":"set","p":"ui_control","pt":"msg","to":"{\"customHeight\":20,\"tabulator\":{\"layout\":\"fitColumns\",\"clipboard\":true,\"clipboardCopySelector\":\"table\",\"clipboardCopyStyled\":false,\"clipboardPasteAction\":\"function(rowData){     this.send({ui_control:{callback:'clipboardPasteAction'},payload:rowData});     return this.table.replaceData(rowData); }\",\"columns\":[{\"field\":\"col1\",\"title\":\"Column #1\"},{\"field\":\"col2\",\"title\":\"Column #2\"},{\"field\":\"col3\",\"title\":\"Column #3\"}]}}","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"[{\"col1\":\"click here & CTRL+v\",\"col2\":\"or click here & CTRL+v\",\"col3\":\"or click here & CTRL+v\"},{\"col1\":\" or on any other cell\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"\",\"col2\":\"\",\"col3\":\"\"},{\"col1\":\"there is a issue with small or empty tables\",\"col2\":\"will be dixed soon\",\"col3\":\"\"},{\"col1\":\"Untill then we have\",\"col2\":\"to fill in some\",\"col3\":\"dummy lines\"}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":399,"y":255,"wires":[["71d16773.2b0a38"]]},{"id":"8d8d7ea4.2fe3f","type":"debug","z":"4ddb981f.f34df8","name":"clipboardPasteAction","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1018,"y":238,"wires":[]},{"id":"71d16773.2b0a38","type":"ui_table","z":"4ddb981f.f34df8","group":"113a4af6.0aab85","name":"Copy & Paste","order":1,"width":"17","height":"7","columns":[],"outputs":1,"cts":true,"x":641,"y":255,"wires":[["d9789454.020d58"]]},{"id":"a0dee6b9.6423c8","type":"function","z":"4ddb981f.f34df8","name":"clipboardPasteAction Callback","func":"// callback: clipboardPasteAction\n// DO NOT USE IN A FLOW!\n\nclipboardPasteAction = \n// copy from here\nfunction(rowData){\n    this.send({ui_control:{callback:'clipboardPasteAction'},payload:rowData});\n    return this.table.replaceData(rowData);\n}\n// until here\n// use the Visual Editor!\n// paste into ui_table JSON \"clipboardPasteAction\":\"function(....\"\n\n// technical details\n//\n// the example function ends with\n//\n// return this.table.updateData(rows);\n// \n// but that's not updating the data (because it does try to update data through\n// the index field (default \"id\")\n// replaceData simply erase all existing rowData and fills in the pasted data\n// that's what we want","outputs":1,"noerr":0,"x":419,"y":204,"wires":[[]]},{"id":"38c0e825.798328","type":"ui_ui_control","z":"4ddb981f.f34df8","name":"on tab change","events":"change","x":182,"y":255,"wires":[["4512106a.2f145"]]},{"id":"d9789454.020d58","type":"switch","z":"4ddb981f.f34df8","name":"","property":"ui_control.callback","propertyType":"msg","rules":[{"t":"eq","v":"clipboardPasteAction","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":815,"y":255,"wires":[["8d8d7ea4.2fe3f"],["7b094d08.4d1224"]]},{"id":"7b094d08.4d1224","type":"debug","z":"4ddb981f.f34df8","name":"something else","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":998,"y":272,"wires":[]},{"id":"113a4af6.0aab85","type":"ui_group","z":"","name":"ui-table Copy & Paste","tab":"a1c14418.954cd8","disp":true,"width":"17","collapse":false},{"id":"a1c14418.954cd8","type":"ui_tab","z":"","name":"ui-table test","icon":"dashboard","disabled":false,"hidden":false}]

CTRL-c is working too. It is set to "table" (including currently invisble cells) and no styling.

Detailed documentation of this tabulator feature can be found here.

Be aware that colum field names must match cells of the first row of the pasted data and the clipboard is tab \t and new line \n formatted, as windows clipboard does. http://tabulator.info/docs/4.5/clipboard#paste-parser

There could be still a little issue in the vertical size of the table. So this is why the table is initialized with blank or dummy rows. Will be solved shortly

2 Likes
35

Thanks, that was really helpfull i will share my flow when i finish. And sorry for all the request

36

Hi Eduardo,
hope it work on your side. No problem always interested in unusual problems to solve.
Chris

37

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.