CODEWITHSUNDEEP
javascripthtmlhtml-table
Ebenezer
Ebenezer

Reputation: 102

How can I convert a html table to an excel file with formulas I specify?

I have a table that's generated from data in a firebase database. I want to export this to an excel file when a button is clicked. But I also want to be able to style the excel in some way by adding formulas so it can add the 'Amount Sent' row together and give the total at the bottom of the row.

I've tried looking online and have not been successful.

I think the easiest way would be to import an excel that I have already made, that has formulas, and set the variables in my HTML table to the headers I already have in my excel.

Javascript Code

    var database = firebase.database().ref().child('transactions');
    database.once('value', function(snapshot){
        if(snapshot.exists()){
            var content = '';

            snapshot.forEach(function(data){
                var AmountSent = data.val().AmountSent;
                var CustomerName = data.val().CustomerName;
                var DateEffected = data.val().DateEffected;
                var Successful = data.val().Successful;
                var TimeEffected= data.val().TimeEffected;


            content += '<tr>';
            content += '<td>' + AmountSent + '</td>'; //column1
            content += '<td>' + CustomerName + '</td>';//column2

            content += '<td>' + DateEffected + '</td>'; //column1
            content += '<td>' + Successful + '</td>'; //column1
            content += '<td>' + TimeEffected + '</td>';//column2

            content += '</tr>';

            });

            $('#ex-table').append(content);
        }
    });

HTML

<body>
        <table style="width:100%" id="ex-table">
        <tr id="tr">
            <th>Amount Sent:</th>
            <th>Customer Name:</th> 
            <th>Date Effected:</th>
            <th>Successful</th>
            <th>Time Effected</th>
    </table> 

</body>

CSS

    #ex-table {
  font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
}

#ex-table td, #ex-table th {
  border: 1px solid #ddd;
  padding: 8px;
}

#ex-table tr:nth-child(even){background-color: #f2f2f2;}

#ex-table tr:hover {background-color: #ddd;}

#ex-table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #4CAF50;
  color: white;
}

Upvotes: 0

Views: 919

Answers (1)

GenericUser
GenericUser

Reputation: 3230

I'd take a look at SheetJS. I've used them for numerous applications and they handle several formats. They can also start from HTML tables for conversion: https://github.com/SheetJS/js-xlsx#html-table-input.

Updated

In regards to formula, you can reference this answer as to how to embed formula. Note this line in his answer where you can explicitly set a formula:

var cell = {f: 'A2+A3'};

Basically formulas can be set by putting in a key for f and a value with the function definition. Most of the work will involve iterating through the results after the table is fed into the library and adjusting cell values with formulas as necessary. The documentation for dealing with formulas is here.

Upvotes: 1

Related Questions