i wanna make a template to get some data from excel or anything and paste in to my template to insert data
How do you see that working, and what have you tried so far?
@Paul-Reed sorry.
[{"id":"af709301.03b","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"5f42e8d3.f4cfa8","type":"template","z":"af709301.03b","name":"html","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<head>\n <style>\n {{{payload.style}}}\n </style>\n</head>\n\n\n<table width=\"100%\">\n <tr>\n <th>Name</th>\n <th>IP</th> \n </tr>\n {{#payload}}\n <tr>\n <td>{{{name}}}</td>\n <td>{{ip}}</td> \n </tr>\n {{/payload}}\n</table>","x":670,"y":140,"wires":[["250d0ef.c7407f2"]]},{"id":"250d0ef.c7407f2","type":"http response","z":"af709301.03b","name":"","x":810,"y":140,"wires":[]},{"id":"7b12a04c.865b5","type":"template","z":"af709301.03b","name":"css","field":"payload.style","fieldType":"msg","format":"html","syntax":"mustache","template":"table {\n color: #333;\n font-family: Helvetica, Arial, sans-serif;\n width: 100%;\n border-collapse: collapse;\n border-spacing: 0;\n}\ntd, th {\n border: 1px solid transparent;\n /* No more visible border */\n height: 30px;\n transition: all 0.3s;\n /* Simple transition for hover effect */\n}\nth {\n background: #DFDFDF;\n /* Darken header a bit */\n font-weight: bold;\n}\ntd {\n background: #FAFAFA;\n text-align: center;\n}\n\n/* Cells in even rows (2,4,6...) are one color */\n\ntr:nth-child(even) td {\n background: #F1F1F1;\n}\n\n/* Cells in odd rows (1,3,5...) are another (excludes header cells) */\n\ntr:nth-child(odd) td {\n background: #FEFEFE;\n}\ntr td:hover {\n background: #666;\n color: #FFF;\n}\n\n/* Hover cell effect! */","x":530,"y":140,"wires":[["5f42e8d3.f4cfa8"]]},{"id":"fffca10d.7ddd8","type":"template","z":"af709301.03b","name":"","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"[\n {\"name\": \"Computer 1\", \"ip\": \"6.3.6.6\"},\n {\"name\": \"Computer 2\", \"ip\": \"6.3.6.6\"},\n {\"name\": \"Computer 3\", \"ip\": \"6.7.6.6\"},\n {\"name\": \"Computer 4\", \"ip\": \"6.4.6.6\"}\n]\n ","x":230,"y":140,"wires":[["d081cea9.0e997"]]},{"id":"d081cea9.0e997","type":"json","z":"af709301.03b","name":"","x":390,"y":140,"wires":[["7b12a04c.865b5"]]},{"id":"6d2e2a85.397724","type":"http in","z":"af709301.03b","name":"","url":"/table","method":"get","swaggerDoc":"","x":60,"y":140,"wires":[["fffca10d.7ddd8"]]},{"id":"fd085881.52bc68","type":"ui_template","z":"af709301.03b","group":"1c9e30f9.7d575f","name":"","order":0,"width":"0","height":"0","format":"<style>\n {{msg.style}}\n</style>\n\n<table>\n <tr ng-repeat=\"obj in msg.payload\">\n <td>{{ obj.name }}</td>\n <td>{{ obj.ip }}</td>\n </tr>\n</table>","storeOutMessages":false,"fwdInMessages":true,"templateScope":"local","x":680,"y":220,"wires":[[]]},{"id":"8fd7e7d0.112818","type":"template","z":"af709301.03b","name":"","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"[\n {\"name\": \"Computer 1\", \"ip\": {{payload}}},\n {\"name\": \"Computer 2\", \"ip\": \"6.3.6.6\"},\n {\"name\": \"Computer 3\", \"ip\": \"6.7.6.6\"},\n {\"name\": \"Computer 4\", \"ip\": \"6.4.6.6\"}\n]\n ","x":230,"y":220,"wires":[["6e0954ee.59fe0c"]]},{"id":"973abcc1.524b8","type":"inject","z":"af709301.03b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":80,"y":220,"wires":[["8fd7e7d0.112818"]]},{"id":"6e0954ee.59fe0c","type":"json","z":"af709301.03b","name":"","x":370,"y":220,"wires":[["46f55580.468e4c"]]},{"id":"46f55580.468e4c","type":"template","z":"af709301.03b","name":"css","field":"style","fieldType":"msg","format":"html","syntax":"mustache","template":"table {\n color: #333;\n font-family: Helvetica, Arial, sans-serif;\n width: 100%;\n border-collapse: collapse;\n border-spacing: 0;\n}\ntd, th {\n border: 1px solid transparent;\n /* No more visible border */\n height: 30px;\n transition: all 0.3s;\n /* Simple transition for hover effect */\n}\nth {\n background: #DFDFDF;\n /* Darken header a bit */\n font-weight: bold;\n}\ntd {\n background: #FAFAFA;\n text-align: center;\n}\n\n/* Cells in even rows (2,4,6...) are one color */\n\ntr:nth-child(even) td {\n background: #F1F1F1;\n}\n\n/* Cells in odd rows (1,3,5...) are another (excludes header cells) */\n\ntr:nth-child(odd) td {\n background: #FEFEFE;\n}\ntr td:hover {\n background: #666;\n color: #FFF;\n}\n\n/* Hover cell effect! */","x":530,"y":220,"wires":[["fd085881.52bc68"]]},{"id":"1c9e30f9.7d575f","type":"ui_group","z":"af709301.03b","name":"Test","tab":"2a76ca1b.d92466","disp":true,"width":"6"},{"id":"2a76ca1b.d92466","type":"ui_tab","z":"af709301.03b","name":"Tableau","icon":"dashboard"}]
Can you explain a bit more about what you are actually trying to achieve because your 1 sentence description isn't really making it clear.
This is the third thread in as many days about excel to dashboard. Sure sounds like an assignment to me.
1 Like
Yep! I thought the same, hence my reply in post #2...
2 Likes
@TotallyInformation sure. So i have a data from some people for a telegram user control panel so i have the data number, email and stuffs but is to much to write the data one by one to allow the access to the people
@dceejay actually i'm from Mexico and i don't have assigments since 2017 but i would like to know if it is possible because i know some stuffs from Node-red but it was like i could do a template but a table to write data like copy and paste i wasn't sure
Data from people (e.g. they type it into a web form) or from a system (e.g. database or CSV file)?
Are you wanting to show this data to people? Or are you wanting them to enter the data?
yeah so basically the thing i want to do is just paste numbers on a table on my dashboard on node red like copy and paste just that at least 3x2 table and i will figure out how to expand that
Ah, now I see.
This is not easy to do with HTML. It is also further complicated by the fact that browsers don't like you pasting rich content into web forms.
It is possible, at least with the help of a front-end library but I don't think you could include that on a dashboard without writing a Dashboard extension Node.
1 Like
thanks @TotallyInformation sorry for replay so late, i was looking for dashboard extension node but i didnt fine anything can you help with that please?
Sounds like you are looking for something like:
It does seem to support some paste features - have you tested it with a copy from Excel?
1 Like
Nope, but should be possible by passing the necessary callback functions via ui_controll
Hmm, not entirely convinced. Depends a lot I think on the clipboard formatting and whether the browser allows entry of enriched text.
Good news though is that it made me look at the library being used by the Dashboard table node. I realised that it no longer depends on jQuery so I'm looking to see how useful it is in uibuilder. 
Right but you can use build in or your own parsers.
As far as I know tabulator is pure JavaScript.
Perhaps my current project interests you
I’m currently designing a flow for displaying health information of many external devices originally based on the homie convention but expandable through individual nodes:
1.) data acquisition: translation of the data into homie and individual user defined fields (not everything fits into the homie convention but it is a good common ground)
2.) plugins: little nodes doing all kind of stuff with this data. For example a watchdog, a reset counter, max. min. or avg. uptime, add icons from the values like battery or signal and other things. To make these reusable it is essential to have a common dataset defined.
3.) table handling: universal flow to handle table formatting, data storage, column width and order, cell editing and interactive stuff like context menus.
Would be great if 1 and 2 work on the dashboard and uibuilder together that there will be a variation of plugins for different devices / firmware or useful filters usable in all scenarios.
My idea is that through simple nodes = plugins all kind of devices could be included and configured as necessary. I already have homie, mqtt in any shape or form, Homematic and http (ESPEasy json responses). Tasmota, ESP Home and others are certainly possible.
Let me know if you are interested.
4 Likes
Looks interesting. Got to pop out with the family now so don't have time to respond properly.
I too have a dashboard but it is more focused on heating right now and as part of my re-platforming from Pi to old laptop, I'm going to re-engineer a lot of my data so the ideas in your image are certainly interesting.
I don't use Dashboard though but rather uibuilder and I've currently side-tracked myself as I'm building a reusable editable table component. I've been using the "native" bootstrap-vue table component as a base but I've also been playing with Tabulator which is easy enough to get to work with Vue. Slightly concerned about size though, especially as the date transformations require MomentJS which isn't small.
You've sparked a few thoughts though.
I'm interested in how you translate from RSSI to the WiFi icon. Also interested in the other chart-like columns, what is their meaning?
Yes, it is now, previously it was dependent on jQuery and jQuery-UI.
Spend time with the family, more important than this stuff. I have to give a little love to my garden ... it`s spring is coming
You mean this columns? Tabulator has a tool-tip function build in 
But I could not do a screenshot (geenshot is nice, but sometimes ...)
Power supply (V), battery state (%), Free Heap (in bytes but converted in tabulator in KB) and CPU Load. CPU Temp is hidden (ESPs do not have a cpu temp sensor). Most of them using the progress bar format with a custom label formater to show the units and convert bytes in kB.
The base dataset is defined by the homie convention 3.0.1 and 4.0.0 with $state and $fw extensions. Other data comes form a http get on a ESPEasy device: (here you see the responsive layout feature of tabulator (there are sill a few issues in the tabulator version in ui-table with dynamic updates to investigate but works for now quite fine.
Adding Icons is done by one (I call them) plugin
[{"id":"db94aa7b.554e88","type":"function","z":"9d52d40a.a63668","name":"add icons","func":"var icons= {\n \"$state\":[\n {\"value\":\"init\",\"icon\":\"fa fa-cog fa-spin\"},\n {\"value\":\"ready\",\"icon\":\"fa fa-spinner fa-spin\"},\n {\"value\":\"disconnected\",\"icon\":\"fa fa-times\"},\n {\"value\":\"sleeping\",\"icon\":\"fa fa-moon-o\"},\n {\"value\":\"lost\",\"icon\":\"fa fa-question-circle\"},\n {\"value\":\"lostBroker\",\"icon\":\"fa fa-exclamation-triangle\"},\n {\"value\":\"alert\",\"icon\":\"fa fa-exclamation-triangle\"}\n ],\n \"signal\":[\n {\"value\":10,\"icon\":\"perm_scan_wifi\",\"style\":\"color:#cc0000\"},\n {\"value\":20,\"icon\":\"wifi\",\"style\":\"color:#cc3300\"},\n {\"value\":30,\"icon\":\"wifi\",\"style\":\"color:#cc6600\"},\n {\"value\":40,\"icon\":\"wifi\",\"style\":\"color:#cc9900\"},\n {\"value\":50,\"icon\":\"wifi\",\"style\":\"color:#cccc00\"},\n {\"value\":60,\"icon\":\"wifi\",\"style\":\"color:#99cc00\"},\n {\"value\":70,\"icon\":\"wifi\",\"style\":\"color:#66cc00\"},\n {\"value\":80,\"icon\":\"wifi\",\"style\":\"color:#33cc00\"},\n {\"value\":100,\"icon\":\"signal_wifi_4_bar\",\"style\":\"color:#00cc00\"},\n ],\n \"battery\":[\n {\"value\":10,\"icon\":\"battery-alert\",\"style\":\"color:#cc0000\"},\n {\"value\":20,\"icon\":\"battery_20\"},\n {\"value\":30,\"icon\":\"battery_30\"},\n {\"value\":50,\"icon\":\"battery_50\"},\n {\"value\":60,\"icon\":\"battery_60\"},\n {\"value\":70,\"icon\":\"battery_70\"},\n {\"value\":80,\"icon\":\"battery_80\"},\n {\"value\":90,\"icon\":\"battery_90\"},\n {\"value\":100,\"icon\":\"battery_full\",\"style\":\"color:#00cc00\"},\n ]\n};\n\nvar status = {fill:\"yellow\",shape:\"dot\",text: \"\"};\nif (msg.hasOwnProperty(\"state\")) {\n status.text=\"device=\";\n status.text+=(msg.state.$name) ? msg.state.$name : msg.topic;\n var formatHtml=function(icon) {\n var html=\"\"\n if (icon.icon.startsWith('fa')) {\n html = \"<i class=\\\"\"+icon.icon+'\\\"></i>'; \n } else {\n html+='<i class=\"material-icons\"';\n if (icon.hasOwnProperty(\"style\")) html+=' style=\"'+icon.style+'\"';\n html+='>'+icon.icon+'</i>'; \n }\n return html;\n }\n nextProperty: \n for (var state in msg.state) {\n if (icons.hasOwnProperty(state)) {\n for (var icon of icons[state]) {\n switch (typeof msg.state[state]) {\n case \"string\":\n if (msg.state[state]===icon.value) {\n msg.state[state+\"Icon\"]=formatHtml(icon);\n status.fill=\"green\";\n status.text+=state+\" \";\n continue nextProperty;\n }\n break; \n case \"number\":\n if (msg.state[state]<=icon.value) {\n msg.state[state+\"Icon\"]=formatHtml(icon);\n status.fill=\"green\";\n status.text+=state+\" \";\n continue nextProperty;\n }\n break;\n }\n }\n }\n } \n}\nnode.status(status);\nreturn msg;","outputs":1,"noerr":0,"x":332,"y":476,"wires":[["248e0178.34da0e"]],"icon":"font-awesome/fa-wifi","info":"# Add and format icons\n- This node is configured by a json object. \n- All incomming data inside `msg.state`is inspected to the parent keys of this object. \n- The value contains an array of objects defining differend icons and styles to be added.\n- a html formatted string is added to `msg.state.[key+\"Icon\"]`\n- the current version supports font awesome icons supported by the dshboard and material icons including the style and size [ToDo] property\n- Sting values will be compared for a case sensitive match\n- values will be compared `<=`. So arrange the objects in a increasing order.\n\n## configuration object\n- defining the keys to be triggered\n```json\n{\n \"$state\":[],\n \"signal\":[],\n \"battery\":[]\n}\n```\n- to trigger on a the value \"init\" to get a font awsome icon plce ´fa´ at the beginning of the `icon` value\n```json\n{\n \"value\":\"init\",\n \"icon\":\"fa fa-cog fa-spin\"\n}\n```\nfor material icons you can use\n```json\n{\n \"value\":10,\n \"icon\":\"perm_scan_wifi\",\n \"style\":\"color:#cc0000\"\n}\n```\n"}]
As homie defines $stats\signal in % I have done this in C inside the homie protocol plugin of ESPEasy. But other devices can use this translator I also use for old devices running ESPEasy without homie using the system info plugin of ESPEasy:
[{"id":"a89f27b1.0d13d8","type":"function","z":"9d52d40a.a63668","name":"translate homie","func":"\n// IMPORTTANT! \n// Make shure that the topic matches the topit of homie state node that the extra data is merged correctly!\nmsg.topic=\"mqtt://192.168.2.14:1883/homie/\"+msg.deviceId;\n// if you do not use the homie convention choose a unique indetifier as msg.topic:\n// msg.topic=msg.payload.System[\"Unit Name\"];\n\nmsg.state={\"$name\":msg.deviceId,\"$state\":\"ready\"};\nswitch (msg.propertyId.toLowerCase()) {\n // messages that need conversion\n case ('rssi'):\n msg.state.signal=Math.min(Math.max(2 * (msg.value + 100), 0), 100)\n break;\n // all messages that can be translated 1:1 \n case ('freeheap'):\n case ('uptime'):\n msg.state[msg.propertyId]=msg.value;\n break;\n default: // drop message\n return;\n}\nreturn msg;","outputs":1,"noerr":0,"x":1083,"y":187,"wires":[["9e278daa.10534","f6956de0.9b59d"]],"icon":"node-red/swap.svg"}]
This is using the simple linear approach to convert db into %. Not precise but ok.
msg.state.signal=Math.min(Math.max(2 * (msg.value + 100), 0), 100)
And finally this is how the hole flow currently looks like (forget all the debug nodes then it is not so complicated).
- Data acquisition, translation and plugins
- Dashboard (ui-table and context menues)
Theui-table handlersubflow is doing all the heavy lifting like holding the table layout, row data, individual cell edits, column width, hidden flag and order, handling commands coming from the context menus and so on. I tried to make it "reusable" for other purposes when I use ui-table like showing actual data:
What was intended as a quick answer got much to long, sorry.
3 Likes
