Help in Function Node - Dynamic Table properties (CSV to HTML)

General
,
1

I have a simple flow, where i convert the contents of a CSV file into an HTML table for sending it as email body.
for few rows in the table i need to colspan 6 columns so that they appear in a single cell based on the contents. i can make the starting of the content to anything i like, here in the example it SUM1, SUM2 etc..
I am not able to write a conditioal code to do that. a little help please.
I can write the same in a static code, but when using dynamic code (inspired by this post)

CSV to HTML dynamic code

From CSV to HTML table - #3 by E1cid

sample code to work with

[{"id":"ddcffe189e25e957","type":"inject","z":"70c85b46b7f69ea5","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":230,"y":1680,"wires":[["0fdc97d6f3010d9e"]]},{"id":"8152b6f5488faf5e","type":"function","z":"70c85b46b7f69ea5","name":"CSV to HTML","func":"var html = '<!doctype html><html lang=\"en\"><table  border=\"1\" Style= \"border: 1px solid pink; font-size:12px; border-collapse:collapse;\"><tr><td colspan = \"6\"><h3>Online Downtime Report (Hosur) for the date '+msg.ddate+'</h3></td></tr>';\n\n//var html = '<!doctype html><html lang=\"en\"><table  border=\"1\" Style= \"font-size:12px; border-collapse:collapse;\"><tr><td colspan = \"5\"><h3>Online Downtime Report</h3></td></tr>';\nhtml += '<td width=\"100px\"></td><td width=\"110px\"></td><td width=\"110px\"></td><td width=\"90px\"></td><td width=\"260px\"></td><td width=\"260px\"></td>'+' </tr>';\n\nmsg.payload.forEach((part) => {\n    html += '<tr>';\n    Object.values(part).forEach((bits) => {\n       html += '<td>'+bits+'</td>'\n    })\n    html+= '</tr>';\n})\nmsg.payload = html+'</table></html>';\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":600,"y":1680,"wires":[["dc879725.5e20d","f1b09195b2116af4"]]},{"id":"f1b09195b2116af4","type":"debug","z":"70c85b46b7f69ea5","name":"Debug-1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":810,"y":1680,"wires":[]},{"id":"0fdc97d6f3010d9e","type":"change","z":"70c85b46b7f69ea5","name":"Sample Data","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"col1\":\"Machine\",\"col2\":\"Time-From\",\"col3\":\"Time-To\",\"col4\":\"Duration\",\"col5\":\"Reason-1\",\"col6\":\"Reason-2\"},{\"col1\":\"MACHINE-1\",\"col2\":\"19-Jan-25 14:04\",\"col3\":\"19-Jan-25 14:12\",\"col4\":8,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"Machine\",\"col2\":\"Time-From\",\"col3\":\"Time-To\",\"col4\":\"Duration\",\"col5\":\"Reason-1\",\"col6\":\"Reason-2\"},{\"col1\":\"MACHINE-2\",\"col2\":\"19-Jan-25 05:59\",\"col3\":\"19-Jan-25 06:12\",\"col4\":13,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"MACHINE-2\",\"col2\":\"19-Jan-25 06:13\",\"col3\":\"19-Jan-25 06:34\",\"col4\":21,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"Machine\",\"col2\":\"Time-From\",\"col3\":\"Time-To\",\"col4\":\"Duration\",\"col5\":\"Reason-1\",\"col6\":\"Reason-2\"},{\"col1\":\"MACHINE-3\",\"col2\":\"19-Jan-25 05:59\",\"col3\":\"19-Jan-25 06:12\",\"col4\":13,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"Machine\",\"col2\":\"Time-From\",\"col3\":\"Time-To\",\"col4\":\"Duration\",\"col5\":\"Reason-1\",\"col6\":\"Reason-2\"},{\"col1\":\"MACHINE-4\",\"col2\":\"19-Jan-25 05:59\",\"col3\":\"19-Jan-25 13:59\",\"col4\":480,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"MACHINE-4\",\"col2\":\"19-Jan-25 14:00\",\"col3\":\"19-Jan-25 14:09\",\"col4\":9,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"Machine\",\"col2\":\"Time-From\",\"col3\":\"Time-To\",\"col4\":\"Duration\",\"col5\":\"Reason-1\",\"col6\":\"Reason-2\"},{\"col1\":\"MACHINE-5\",\"col2\":\"19-Jan-25 06:04\",\"col3\":\"19-Jan-25 06:09\",\"col4\":5,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"MACHINE-5\",\"col2\":\"19-Jan-25 06:11\",\"col3\":\"19-Jan-25 06:49\",\"col4\":38,\"col5\":\"-\",\"col6\":\"-\"},{\"col1\":\"SUM1-MACHINE-1 has produced 7,580 strokes on 2025-01-19\"},{\"col1\":\"SUM2-MACHINE-2 was not running on 2025-01-19\"},{\"col1\":\"SUM3-MACHINE-3 was not running on 2025-01-19\"},{\"col1\":\"SUM4-MACHINE-4 has produced 7,114 strokes on 2025-01-19\"},{\"col1\":\"SUM5-MACHINE-5 has produced 7,767 strokes on 2025-01-19\"}]","tot":"json"},{"t":"set","p":"ddate","pt":"msg","to":"19th Jan 2025","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":1680,"wires":[["8152b6f5488faf5e"]]}]

need something like, if the contents of col1 contains SUM in the begining,
<td colspan = "6">...,
else <td>...

2

Instead of checking the text, you could check if col2 exists, if not...


let html = ""
html += "<!doctype html><html lang='en'>"
html += "<style>body{font-family:arial;}table{border-collapse:collapse}td,th{border:1px solid #999;padding:4px;font-size:12px;}</style>"
html += `<h3>Online Downtime Report (Hosur) for the date ${msg.ddate}</h3>`
html += "<table>"
html += "<thead><tr><th>Machine</th><th>Time-From</th><th>Time-To</th><th>Duration</th><th>Reason 1</th><th>Reason 2</th></tr></thead>"
html += "<tbody>"

msg.payload.forEach((part) => {
   if(part.col2 === undefined){
       html += `<tr><td colspan='6'>${part.col1}</td></tr>`
   }else if(part.col1 !== 'Machine'){
       html += `<tr><td>${part.col1}</td><td>${part.col2}</td><td>${part.col3}</td><td>${part.col4}</td><td>${part.col5}</td><td>${part.col6}</td></tr>`
   }
})
html += "</tbody></table></html>"

msg.payload = html
return msg

This renders:

image
image1136×776 56.2 KB

1 Like
3

Wow. Thank you.
You meanwhile solved my other untold problem of repeating header row!

4

May I ask you how to change the font color of the sentence to red if the machine was not running on the day? (Machine-2, Machine-3) in the example ?

something like
part.col2===undefined and col1 contains 'not running'
then, color='red'

If we could sort all the 'not running' machines and 'running' machines that would be great as well.

5

You could replace it like;

let html = ""
html += "<!doctype html><html lang='en'>"
html += "<style>body{font-family:arial;}.running{background:#22c55e;color:#fff;}.not-running{background:#ef4444;color:#fff;}table{border-collapse:collapse}td,th{border:1px solid #999;padding:4px;font-size:12px;}</style>"
html += `<h3>Online Downtime Report (Hosur) for the date ${msg.ddate}</h3>`
html += "<table>"
html += "<thead><tr><th>Machine</th><th>Time-From</th><th>Time-To</th><th>Duration</th><th>Reason 1</th><th>Reason 2</th></tr></thead>"
html += "<tbody>"

msg.payload.forEach((part) => {
    if (part.col2 === undefined) {
        const css_class = part.col1.match("not running")  ? "not-running" : 'running'
        html += `<tr><td colspan='6' class='${css_class}'>${part.col1}</td></tr>`
        
    } else if (part.col1 !== 'Machine') {
        const machine = msg.payload.filter(x=>x.col1.startsWith("SUM")).find(x=>x.col1.match(".*"+part.col1+".*"))
      
        let css_class = "running" 
        if (machine && machine.col1.match('not running') ){
            css_class = "not-running" 
        }
        html += `<tr class="${css_class}"><td>${part.col1}</td><td>${part.col2}</td><td>${part.col3}</td><td>${part.col4}</td><td>${part.col5}</td><td>${part.col6}</td></tr>`
    }
})
html += "</tbody></table></html>"

msg.payload = html
return msg

note that I made use of styles and apply them where applicable.

image
image950×692 78.3 KB

Note I would recommend to start with different input data.
The input already contains the sum, which originates from somewhere.

1 Like
6

Thank you so much!.
today i learnt how to define css class in function node in javascript.

1 Like
7

Tomorrow learn about other forms of string in JavaScript. :grin:

The above could be:

let html = `
<!doctype html>
<html lang='en'><head>
  <style>
    body {
      font-family:arial;
    }
    .running {
      background:#22c55e;
      color:#fff;
    }
    .not-running {background:#ef4444;color:#fff;}
    table {border-collapse:collapse}
    td, th {border:1px solid #999;padding:4px;font-size:12px;}
  </style>
</head><body>
  <h3>Online Downtime Report (Hosur) for the date ${msg.ddate}</h3>
  <table>
    <thead>
      <tr>
        <th>Machine</th><th>Time-From</th><th>Time-To</th> 
        <th>Duration</th><th>Reason 1</th><th>Reason 2</th>
      </tr>
    </thead>
    <tbody>
`

I've not bothered to expand everything but you can see that it is FAR easier to read. I've also corrected a couple of errors that wouldn't be easily visible in the original.

However, don't you wish someone might have already done some of the work for you? Hint. :grin:

UIBUILDER can make much of this easier for you. Including the ability to have static HTML and CSS rather than having to recreate everything every time you need to update. It also has several different table building features.

8

I just reused whatever was provided, being lazy and things.
I am using template strings as well.

2 Likes