Hey folks! I have a flow where I'm able to get the data from the database to the HTML table. after that, on the dashboard, I can click the export excel button and get the excel sheet of that data.
But I wanted to attach that sheet to the mail. I have no idea how to achieve that?
here is what my flow looks like.
I see no button in the image of your flow.
Providing the actual flow would help
well, that is on the HTML side. so On the dashboard, I have that button.
[
{
"id": "90e795a6.555d68",
"type": "debug",
"z": "5747a3f5.ce56ec",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 1190,
"y": 1000,
"wires": []
},
{
"id": "199ded53.6d4e83",
"type": "postgres",
"z": "5747a3f5.ce56ec",
"postgresdb": "9bb6219d.ec69c",
"name": "Database",
"output": true,
"outputs": 1,
"x": 640,
"y": 1000,
"wires": [
[
"9188bd18.d642b"
]
]
},
{
"id": "a557c658.b69c58",
"type": "inject",
"z": "5747a3f5.ce56ec",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
},
{
"p": "today",
"v": "202167",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 200,
"y": 1000,
"wires": [
[
"b8ec9008.94de"
]
]
},
{
"id": "b8ec9008.94de",
"type": "function",
"z": "5747a3f5.ce56ec",
"name": "Get the report data ",
"func": "\nvar columns = 'ltpcc_i_c,dg_ltpcc_i_c'\nvar yesterday_date = \"2021-6-6\"//msg.yesterday;\nvar today_date = \"2021-6-7\"//msg.today;\n\nmsg.payload = [\n {\n query: 'begin',\n },\n {\n query: '(select * from table1 ORDER BY date_1 )',\n \n //query: 'DELETE FROM hemstar_report WHERE date_1 = $today',\n \n \n output: true,\n },\n \n {\n query: 'commit',\n },\n];\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 410,
"y": 1000,
"wires": [
[
"199ded53.6d4e83"
]
]
},
{
"id": "9188bd18.d642b",
"type": "function",
"z": "5747a3f5.ce56ec",
"name": "Date convz",
"func": "\nvar date =[]\nvar currenttime = []\n\n// Here converting the date column to date format\n\nfor (let i = 0 ; i < msg.payload.length; i++){\n\n\n\n date[i] = new Date(msg.payload[i].date_1);\n\n currenttime[i] =(+date[i].getFullYear()+\n \"-\"+(date[i].getMonth()+1)+\n \"-\"+date[i].getDate());\n\nmsg.payload[i].date_1 = currenttime[i]\n\n\n}\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 830,
"y": 1000,
"wires": [
[
"8bd90f45.ee947"
]
]
},
{
"id": "8bd90f45.ee947",
"type": "ui_template",
"z": "5747a3f5.ce56ec",
"group": "c404c3a2.70cd4",
"name": "Trail",
"order": 2,
"width": 0,
"height": 0,
"format": "<html>\n<head>\n\n<script src=\"https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js\"></script>\n\n<link rel=\"stylesheet\" type=\"text/css\" href=\"styles.css\">\n\n<style>\n \n table, th, td {\n border: 1px solid black;\n border-collapse: collapse;\n padding: 4px;\n text-align:center;\n }\n </style>\n\n</head>\n\n\n\n<body>\n \n <center>\n <h1 style=\"color: green;\">Heading Testing</h1>\n <h2>HTML colspan Attribute</h2>\n </center>\n<div id=\"wrapper\">\n\n<table id=\"mytable\" >\n \n <tr>\n <th colspan=\"4\">Expense</th>\n </tr>\n\n\n<tr>\n\n<th style=\"background-color: yellow\">Date</th>\n<th style=\"background-color:#A93226; color:#ffffff;\">LTPCC I/C</th> \n<th style=\"background-color:#A93226; color:#ffffff;\">DIFF IN KW</th>\n<th style=\"background-color:#A93226; color:#ffffff;\">D.G LT PCC I/C KWH</th>\n<th style=\"background-color:#A93226; color:#ffffff;\">DIFF IN KW</th>\n</tr>\n\n\n\n\n<tr ng-repeat=\"x in msg.payload | limitTo:200\">\n\n<td>{{msg.payload[$index].date_1}}</td> \n<td>{{msg.payload[$index].ltpcc_i_c}}</td> \n<td>{{msg.payload[$index].diff_1}}</td>\n<td>{{msg.payload[$index].dg_ltpcc_i_c}}</td>\n<td>{{msg.payload[$index].diff_2}}</td>\n</tr>\n</table>\n\n<button id=\"btnExport\" onclick=\"exportReportToExcel(this)\">EXPORT REPORT</button>\n\n\n</div>\n\n\n<script>\n function exportReportToExcel() {\n let table = document.getElementsByTagName(\"mytable\"); // you can use document.getElementById('tableId') as well by providing id to the table tag\n TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag\n name: `export.xls`, // fileName you could use any name\n sheet: {\n name: 'Sheet 1' // sheetName\n }\n });\n}\n\n\n</script>\n \n \n</body>\n</html>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": true,
"templateScope": "local",
"x": 1010,
"y": 1000,
"wires": [
[
"90e795a6.555d68"
]
]
},
{
"id": "9bb6219d.ec69c",
"type": "postgresdb",
"hostname": "localhost",
"port": "5432",
"db": "asd",
"ssl": false
},
{
"id": "c404c3a2.70cd4",
"type": "ui_group",
"name": "Excel",
"tab": "7ee7d04d.42ce2",
"order": 1,
"disp": true,
"width": "20",
"collapse": false
},
{
"id": "7ee7d04d.42ce2",
"type": "ui_tab",
"name": "Excel Generate",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
Here is the how flow looks like.
To used the node-red-node-email the file to send must be server-side (at node-red)
Your excel generation code is client side (using tableToExcel)
Your choices are make the excel creation server side or create a HTML endpoint to upload the file and email it.
Actually, managed to do this without endpoint - just used dashboards scope.send feature to pass the workbook back to node-red.
You now just have to add the email node and attach the buffer (no need to save it to file first)
[{"id":"a557c658.b69c58","type":"inject","z":"c25b4176.1ca91","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"today","v":"202167","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":620,"y":520,"wires":[["fc4fff601c133313"]]},{"id":"9188bd18.d642b","type":"function","z":"c25b4176.1ca91","name":"Date convz","func":"\nvar date =[]\nvar currenttime = []\n\n// Here converting the date column to date format\n\nfor (let i = 0 ; i < msg.payload.length; i++){\n\n\n\n date[i] = new Date(msg.payload[i].date_1);\n\n currenttime[i] =(+date[i].getFullYear()+\n \"-\"+(date[i].getMonth()+1)+\n \"-\"+date[i].getDate());\n\nmsg.payload[i].date_1 = currenttime[i]\n\n\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":970,"y":520,"wires":[["8bd90f45.ee947"]]},{"id":"8bd90f45.ee947","type":"ui_template","z":"c25b4176.1ca91","group":"c404c3a2.70cd4","name":"Trail","order":2,"width":0,"height":0,"format":"<html>\n <head>\n <script src=\"https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js\"></script>\n <link rel=\"stylesheet\" type=\"text/css\" href=\"styles.css\">\n <style>\n \n table, th, td {\n border: 1px solid black;\n border-collapse: collapse;\n padding: 4px;\n text-align:center;\n }\n </style>\n </head>\n <body>\n <center>\n <h1 style=\"color: green;\">Heading Testing</h1>\n <h2>HTML colspan Attribute</h2>\n </center>\n\n <div id=\"wrapper\">\n\n <table id=\"mytable\" >\n <tr>\n <th colspan=\"4\">Expense</th>\n </tr>\n <tr>\n <th style=\"background-color: yellow\">Date</th>\n <th style=\"background-color:#A93226; color:#ffffff;\">LTPCC I/C</th> \n <th style=\"background-color:#A93226; color:#ffffff;\">DIFF IN KW</th>\n <th style=\"background-color:#A93226; color:#ffffff;\">D.G LT PCC I/C KWH</th>\n <th style=\"background-color:#A93226; color:#ffffff;\">DIFF IN KW</th>\n </tr>\n\n <tr ng-repeat=\"x in msg.payload | limitTo:200\">\n <td>{{msg.payload[$index].date_1}}</td> \n <td>{{msg.payload[$index].ltpcc_i_c}}</td> \n <td>{{msg.payload[$index].diff_1}}</td>\n <td>{{msg.payload[$index].dg_ltpcc_i_c}}</td>\n <td>{{msg.payload[$index].diff_2}}</td>\n </tr>\n </table>\n\n <md-button id=\"btnExport\" ng-click=\"exportReportToExcel('#mytable')\">EXPORT REPORT</md-button>\n <md-button id=\"btnExport\" ng-click=\"exportReportToExcel()\">EXPORT REPORT</md-button>\n\n </div>\n\n <script>\n var _scope = this.scope;\n this.scope.exportReportToExcel = function(selector) {\n debugger\n let table = $(selector || '#mytable'); \n let wb = TableToExcel.tableToBook(table[0], { \n name: `export.xls`, \n sheet: {\n name: 'Sheet 1'\n }\n });\n wb.xlsx.writeBuffer().then(function(buffer) { \n _scope.send({\n topic:'report', \n payload:buffer\n });\n });\n }\n </script>\n\n </body>\n</html>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":1150,"y":520,"wires":[["5212c3d21178b5e3"]]},{"id":"fc4fff601c133313","type":"function","z":"c25b4176.1ca91","name":"","func":"var fakedata = function(i) {\n return {\n date_1: new Date(),\n ltpcc_i_c: i,\n diff_1: (Math.random() * 100).toFixed(0),\n dg_ltpcc_i_c: i+1,\n diff_2: (Math.random() * 100).toFixed(0)\n }\n}\nmsg.payload = [];\nmsg.payload.push(fakedata(1));\nmsg.payload.push(fakedata(2));\nmsg.payload.push(fakedata(3));\nmsg.payload.push(fakedata(4));\nmsg.payload.push(fakedata(5));\nmsg.payload.push(fakedata(6));\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":520,"wires":[["9188bd18.d642b"]]},{"id":"5212c3d21178b5e3","type":"debug","z":"c25b4176.1ca91","name":"the output is a bunary buffer (excel) send this in attachement to email node)","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1540,"y":520,"wires":[]},{"id":"c404c3a2.70cd4","type":"ui_group","name":"Excel","tab":"7ee7d04d.42ce2","order":1,"disp":true,"width":"20","collapse":false},{"id":"7ee7d04d.42ce2","type":"ui_tab","name":"Excel Generate","icon":"dashboard","disabled":false,"hidden":false}]
@Steve-Mcl Thanks, but I'm not able to attach buffer to my Gmail node. also is it possible that if I click inject node and I get output(buffer) from HTML node and send as an Attachment.
Why, what did you try? show me your flow.
Not really, because you used tableToExcel in client side code (ui-template), a browser MUST be connected and opened on the correct tab to generate the HTML which is then converted into excel spreadsheet.
I cant see what is in payload but I suspect you are trying to push the buffer directly into msg.attachements - the email node built in help says...
Sends the
msg.payloadas an email, with a subject ofmsg.topic.
and
Alternatively you may provide
msg.attachmentswhich should contain an array of one or more attachments in nodemailer format.
e.g...
var buf = msg.payload;
msg.topic = "Report"; //subject
msg.payload = "Here is your report"; //body of email
msg.attachments = [{
filename: 'report.xlsx',
content: buf
}]
return msg;
thanks @Steve-Mcl. Just change this to
var buf = new Buffer(msg.payload);//create a buffer with enough space
msg.topic = "Report"; //subject
msg.payload = "Here is your report"; //body of email
msg.attachments = [{
filename: 'report.xlsx',
content: buf
}]
return msg;
If this is not possible so can i use javascript inbuilt set timeout function that will give me buffer when the inject node press???
the problem is the spreadsheet is only created in a browser. If no browser is open the code wont run.
You would be better off using something like one of these nodes to build the spreadsheet at server side (node-red side)
Hi @Steve-Mcl Good evening.. I was thinking to make http endpoint and I come up with this example. I tried to pass buffer to http response node. But I can't able to download excel file.
I set headers.content-type : application/vnd.ms-excel
Anything I can do so that in response I can download the excel file?
Why?
What are you trying to achieve?
Your original code created and downloaded the excel workbook without involving any server-side code. You could revert back to that.
I'm exploring the http endpoint and found it needs buffer to send file over response. so Thinking that I've already created buffer for my excel file so why not try to send with the response.
Exploring it to send excel file. for testing I've save buffer response over flow.set()
Send excel where?
The first solution you had downloaded the excel file (client side)
The solution I provided sent it back to node-red (server side)
Why are you trying to now create an end point?
Are you attempting to provide a download link to Get the excel file? - you already had that in your first attempt.
No. I was working with http node for getting an image download. and then I thought if I create an api and another client get that image?? so for that purpose I was looking also on my excel buffer so was trying to use it same as this.
You can do this i am just exploring your resons.
If I help you will end up with a client side (in browser) solution to create EXCEL spreadshet that then posts it to node-red that then stores it server side then a http end point that serves the file back to the browser (client side) - full circle!
So if you want help you will need to post your flow and describe the issue.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy