Export From Html Table or Database to Excel file

General
1

Hello,

I need help solving a problem.

I have a table with values ​​and I wanted to export it to an excel or csv file.

the table I have is generated by a query the database and then it is shown by a table built in html.

I leave an attachment an example of what I have.fff

Anyone have an idea where to start?
thanks

2

How are you building that table? If you have the raw data in a flow then you can equally generate the CSV format of the data at the same time.

3

yes , using “template node” and this code

<table style="width:100%">
  <tr>
<th>Index</th> 
<th>Timestamp</th>
<th>Value</th> 
<th>Bool</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:20">
<td>{{$index}}</td>
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>{{msg.payload[$index].VALUE}}</td> 
<td>{{msg.payload[$index].BOOL}}</td>
  </tr>
</table>

Using the CSV node I only got one line.

thanks

4

So if you are building the html table from an array.

There are many different ways to iterate through this in Node-RED but a good one to learn would be to use a function and some javascript.
Try reading https://www.w3schools.com/js/js_loop_for.asp as well as https://nodered.org/docs/user-guide/messages

5

If your payload contains an array of objects (like your query results), the csv node will convert that into a string of comma separated values, with individual “lines” separated by a newline character (linux), or by Cr/Nl (on windows):

TIMESTAMP,VALUE,BOOL
1530107171775,5,0
1530107151774,21,0
1530107131774,71,1

FYI, you can simplify your ng-repeat logic by not using the index, something like this:

    <tr ng-repeat="row in msg.payload | limitTo:20">
        <td>{{$index}}</td>
        <td>{{row.TIMESTAMP}}</td>
        <td>{{row.VALUE}}</td> 
        <td>{{row.BOOL}}</td>
    </tr>
6 
<script>
function exportTableToExcel(yourtablename, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(yourtablename);
      var tableHTML = tableSelect.outerHTML.replace(/ /g,  '%20');
    filename = filename?filename+'.xls':'Rapport.xls';
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML, text], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
        downloadLink.download = filename;
        downloadLink.click();
    }
}
    </script>

<div>
   
<button onclick="exportTableToExcel('yourtablename')">SAVE TO EXEL</button>

</div>

Im using this code

Karl

7

hello,

thank you all for the help and answers.

Karlbo, the function works really well, thanks.

btw, do you have anything similar for .pdf?

thanks

8

did you find a solution for .pdf??

9

how to implement this code?

10

how to implement this code together with the preceding code above mention by cristiano?

11

This thread is two years old. It would be better if you open a new thread explainng your issue. Thanks.

12

yes i have something take a screenshoot and print in .pdf

<script src="http://mrrio.github.io/jsPDF/dist/jspdf.debug.js"></script>
<style>
table { 
    width: 750px; 
    border-collapse: collapse; 
    margin:10px auto;
    }
/* Zebra striping */
tr:nth-of-type(odd) { 
    background: #eee; 
    }
th { 
    background: #414c6a; 
    color: white; 
    font-weight: bold; 
    text-align: center; 
    }
td, th { 
    padding: 10px; 
    border: 1px solid #ccc; 
    text-align: right; 
    font-size: 18px;
    }
.button {
  background-color: #4CAF50; /* Green */
  border: none;
  color: white;
  padding: 8px 20px;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 16px;
  margin: 0px 20px;
  cursor: pointer;
  border-radius: 4px;
}

.button1 {/* Green */
  background-color: #4CAF50; 
  color: white; 
  border: 2px solid #4CAF50;
}

.button3 {/* red */
  background-color: #f44336; 
  color: white; 
  border: 2px solid #f44336;
}

</style>
<div id="customers">
    <table id="t01" class="mytable" ng-repeat="res in msg.payload.results">
        <tr>
            <th>Date and Time</th>
            <th>Effective Energy</th>
            <th>Reactive Energy</th>
        </tr>
        <tr ng-repeat="x in msg.payload.results[0].series[0].values">
            <td>{{msg.payload.results[0].series[0].values[$index][0]*1000 | date: "yyyy-MM-dd"}}</td>
            <td>{{(msg.payload.results[0].series[0].values[$index][1]/1000).toFixed(2)}} kWh</td>
            <td>{{(msg.payload.results[0].series[0].values[$index][2]/1000).toFixed(2)}} kVArh</td>
        </tr>
    </table>
</div>
<div>
  <button class="button button1" onclick="exportTableToExcel('t01')">CSV</button>
  <button class="button button3" id="btnExport2">PDF</button>
</div>


<script>
function exportTableToExcel(yourtablename, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(yourtablename);
      var tableHTML = tableSelect.outerHTML.replace(/ /g,  '%20');
    filename = filename?filename+'.xls':'Rapport.xls';
    downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTML, text], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
        downloadLink.download = filename;
        downloadLink.click();
    }
}
</script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.22/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/0.4.1/html2canvas.min.js"></script>
<script type="text/javascript">
    $("body").on("click", "#btnExport2", function () {
        html2canvas($('#customers')[0], {
            onrendered: function (canvas) {
                var data = canvas.toDataURL();
                var docDefinition = {
                    content: [{
                        image: data,
                        width: 500
                    }]
                };
                pdfMake.createPdf(docDefinition).download("Table.pdf");
            }
        });
    });
</script>

my dashboard

image
image1600×821 72.7 KB

document
Table (1).pdf (42.4 KB)

13

Hi @Cristiano. any better option than a screenshot? or does table to excel have the option for including a chart or picture on it.