Question 388751 in Html Question posted in
Some basic documentation can be found here.

Export HTML tables to excel

I know very basic html and very little javascript. I’m hoping someone can write a code i can cut and past and update a few things. We’re currently using Tridion for our intranet. I have to use a code block component for javascript to allow me to export the HTML table to excel. The issue is it will only export on table to excel and there are several tables on the page. all the tables are formatted the same. Is there is JavaScript that can combine all the tables into one excel spreadsheet?

This is the HTML tables I’m using.

<button onclick="exportTableToExcel('Dept1')">Download To Excel</button> 
<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
<!-------------------Table Header Rows------------------->
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
    
  </tr>
  </thead>
  <tbody>

  <tr id="Dept1">
    <td>Ted</td>
    <td>Sam</td>    
    <td>Sally</td>
    <td>John<td>
  </tr>
   </tbody>
</table>



<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
<!-------------------Table Header Rows------------------->
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
    
  </tr>
  </thead>
  <tbody>

  <tr id="Dept2">
    <td>Bird</td>
    <td>Cat</td>    
    <td>Dog</td>
    <td>Bunny<td>
  </tr>
   </tbody>
</table>


<table border="1" width="100%" cellspacing="2" cellpadding="3" summary="Contacts">
<thead>
<tr class="" valign="top" style="vertical-align: top; text-align: center;">
<!-------------------Table Header Rows------------------->
    <th scope="col" style="text-align: center;">Contact 1</th>
    <th scope="col" style="text-align: center;">Contact 2</th>
    <th scope="col" style="text-align: center;">Contact 3</th>
    <th scope="col" style="text-align: center;">Contact 4</th>
    
  </tr>
  </thead>
  <tbody>

  <tr id="Dept3">
    <td>Star</td>
    <td>Sun</td>    
    <td>Moon</td>
    <td>Cloud<td>
  </tr>
   </tbody>
</table>

This is the current Javascript that I’m using, but it is only pulling in one table.

<script>
function exportTableToExcel(dept1, filename = ''){
    var downloadLink;
    var dataType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(dept 1);
    var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');
    
    // Specify file name
    filename = filename?filename+'.xls':'Departments';
    
    // Create download link element
    downloadLink = document.createElement("a");
    
    document.body.appendChild(downloadLink);
    
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['ufeff', tableHTML], {
            type: dataType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
    
        // Setting the file name
        downloadLink.download = filename;
        
        //triggering the function
        downloadLink.click();
    }
}

</script>
Tags:

2

Answers


  1. 0

    Unless you need something Excel specific, I’d advise saving your html table in "Comma Separated Value (CSV)" format (https://support.google.com/google-ads/answer/9004364?hl=en#:~:text=A%20CSV%20(comma%2Dseparated%20values,in%20a%20table%20structured%20format.)

    (From: https://www.geeksforgeeks.org/how-to-export-html-table-to-csv-using-javascript/)

    In case this webpage ever disappears, here is a reproduction of the final solution cut and pasted

    <!DOCTYPE html>
<html>
 
<body>
    <center>
        <h1 style="color:green">GeeksForGeeks</h1>
        <h2>Table to CSV converter</h2>
        <table border="1" cellspacing="0" cellpadding="10">
            <tr>
                <th>Name</th>
                <th>age</th>
                <th>place</th>
            </tr>
            <tr>
                <td>Laxman</td>
                <td>19</td>
                <td>Hyderabad</td>
            </tr>
            <tr>
                <td>Dhoni</td>
                <td>22</td>
                <td>Ranchi</td>
            </tr>
            <tr>
                <td>Kohli</td>
                <td>25</td>
                <td>Delhi</td>
            </tr>
        </table>
        <br><br>
        <button type="button" onclick="tableToCSV()">
            download CSV
        </button>
    </center>
 
    <script type="text/javascript">
        function tableToCSV() {
 
            // Variable to store the final csv data
            let csv_data = [];
 
            // Get each row data
            let rows = document.getElementsByTagName('tr');
            for (let i = 0; i < rows.length; i++) {
 
                // Get each column data
                let cols = rows[i].querySelectorAll('td,th');
 
                // Stores each csv row data
                let csvrow = [];
                for (let j = 0; j < cols.length; j++) {
 
                    // Get the text data of each cell
                    // of a row and push it to csvrow
                    csvrow.push(cols[j].innerHTML);
                }
 
                // Combine each column value with comma
                csv_data.push(csvrow.join(","));
            }
 
            // Combine each row data with new line character
            csv_data = csv_data.join('n');
 
            // Call this function to download csv file  
            downloadCSVFile(csv_data);
 
        }
 
        function downloadCSVFile(csv_data) {
 
            // Create CSV file object and feed
            // our csv_data into it
            CSVFile = new Blob([csv_data], {
                type: "text/csv"
            });
 
            // Create to temporary link to initiate
            // download process
            let temp_link = document.createElement('a');
 
            // Download csv file
            temp_link.download = "GfG.csv";
            let url = window.URL.createObjectURL(CSVFile);
            temp_link.href = url;
 
            // This link should not be displayed
            temp_link.style.display = "none";
            document.body.appendChild(temp_link);
 
            // Automatically click the link to
            // trigger download
            temp_link.click();
            document.body.removeChild(temp_link);
        }
    </script>
</body>
 
</html>

    CSV is very simple and portable

    Login or Signup to reply.
  2. 0

    I just upgraded your button’s function to export multiple Excels… 🙂

    Add this new function anywhere in your JS code…

    function exportManyTablesToExcel(){

exportTableToExcel('Dept1');
exportTableToExcel('Dept2');
exportTableToExcel('Dept3'); // <-- Edit these names as required and add as many of these entries as required

}

    And replace your existing download button with this one…

    <button onclick="exportManyTablesToExcel();">Download To Excel</button>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search