{"id":126804,"date":"2022-07-25T14:55:03","date_gmt":"2022-07-25T14:55:03","guid":{"rendered":"https:\/\/phppot.com\/?p=18737"},"modified":"2022-07-25T14:55:03","modified_gmt":"2022-07-25T14:55:03","slug":"convert-html-table-to-excel-using-javascript","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2022\/07\/25\/convert-html-table-to-excel-using-javascript\/","title":{"rendered":"Convert HTML Table to Excel using JavaScript"},"content":{"rendered":"
by Vincy<\/a>. Last modified on July 25th, 2022.<\/div>\n

Converting a HTML table to an excel file is a standard requirement of reporting websites. It will be simple and easier if the conversion is taking place on the client side.<\/p>\n

There are many client-side and server-side plugins to perform the excel export. For example, PHPSpreadSheet allows writing data into excel and exporting.<\/p>\n

This article will give different options and approaches to achieving the HTML table to excel conversion with JavaScript.<\/p>\n

This simple example includes a few lines of JavaScript that build the template for excel export.<\/p>\n

It uses the following steps to convert<\/span> the exported excel report of tabular data.<\/p>\n

    \n
  1. Defines HTML template structure with required meta.<\/li>\n
  2. Include the table\u2019s inner HTML into the template.<\/li>\n
  3. Marks the location by specifying the protocol to download the file via browser.<\/li>\n
  4. Redirect via JavaScript<\/a> to point to the location with the encoded excel content.<\/li>\n<\/ol>\n
    \n

    Quick example<\/h2>\n

    A JavaScript handler to set export template and push HTML table data.<\/p>\n

    function exportToExcel() { var location = 'data:application\/vnd.ms-excel;base64,'; var excelTemplate = '<html> ' + '<head> ' + '<meta http-equiv=\"content-type\" content=\"text\/plain; charset=UTF-8\"\/> ' + '<\/head> ' + '<body> ' + document.getElementById(\"table-conatainer\").innerHTML + '<\/body> ' + '<\/html>' window.location.href = location + window.btoa(excelTemplate);\n}\n<\/code><\/pre>\n<\/div>\n
    View demo<\/a><\/p>\n
    The below HTML table code displays a product listing and it is static. Refer jsPDF AutoTables examples<\/a> to render a dynamic table by loading row by row.<\/p>\n
    The button below this table triggers the export to excel process on the click event. The exportToExcel()<\/em> function handles the event and proceeds the client-side export.<\/p>\n
    <div id=\"table-conatainer\"> <table class=\"striped\"> <thead> <tr> <th>S.No<\/th> <th>Product Name<\/th> <th>Price<\/th> <th>Model<\/th> <\/tr> <\/thead> <tbody> <tr> <td>1q<\/td> <td>GIZMORE Multimedia Speaker with Remote Control, Black<\/td> <td>2300<\/td> <td>2020<\/td> <\/tr> <tr> <td>2<\/td> <td>Black Google Nest Mini<\/td> <td>3400<\/td> <td>2021<\/td> <\/tr> <\/tbody> <\/table>\n<\/div>\n<\/code><\/pre>\n
    \"html<\/p>\n
    Let us see more options available for converting HTML table data into an excel file.<\/p>\n
    Option 2: Export as CSV to view in excel<\/h2>\n
    This example is for parsing the HTML table content via JavaScript. It follows the below steps to export an HTML table to excel as CSV.<\/p>\n
      \n
    1. Accessing HTML table element object.<\/li>\n
    2. Convert the object into an array of row data.<\/li>\n
    3. Iterate row data array to prepare comma-separated values<\/a> of records.<\/li>\n
    4. Set the protocol and content type to export the CSV data prepared from the table.<\/li>\n<\/ol>\n
      The below JavaScript function implements the above steps to export table data on the client side.<\/p>\n
      function exportCSVExcel() { var tableElement = document.getElementById(\"table-product-list\"); var sourceData = \"data:text\/csv;charset=utf-8,\"; var i = 0; while (row = tableElement.rows[i]) { sourceData += ([ row.cells[0].innerText, row.cells[1].innerText, row.cells[2].innerText, row.cells[3].innerText ]).join(\",\") + \"\\r\\n\"; i++; } window.location.href = encodeURI(sourceData);\n}\n<\/code><\/pre>\n
      Option 3 \u2013 Export HTML table to excel using jQuery-based plugin<\/h2>\n
      The jQuery table2excel plugin is a popular solution to arrive HTML to excel export.<\/p>\n
      It has many features with the export core functionalities. Those are,<\/p>\n
        \n
      1. Excludes\/includes HTML tags like inputs, links, or images that are in the source table HTML.<\/li>\n
      2. Excludes some table components with the reference of that particular element\u2019s class or id selectors.<\/li>\n
      3. Provides properties to set file name with which the exported data will be downloaded to the browser.<\/li>\n<\/ol>\n
        Include the jQuery and the table2excel plugin file in the <head><\/em> section as below.<\/p>\n
        There are alternative methods to install the table2excel plugin. Its Github page provides documentation<\/a> of installation and usage methodologies.<\/p>\n
        <script src=\"https:\/\/ajax.googleapis.com\/ajax\/libs\/jquery\/2.2.4\/jquery.min.js\"><\/script>\n<script src=\"https:\/\/cdn.rawgit.com\/rainabba\/jquery-table2excel\/1.1.0\/dist\/jquery.table2excel.min.js\"><\/script>\n<\/code><\/pre>\n
        Then, initiate the plugin class by pointing to the source HTML table element.<\/p>\n
        The below JavaScript does the initiation. During instantiation, it sets the options array to specify a file name, extension and applicable flags.<\/p>\n
        function exportCSVExcel() { $('#table-product-list').table2excel({ exclude: \".no-export\", filename: \"download.xls\", fileext: \".xls\", exclude_links: true, exclude_inputs: true });\n}\n<\/code><\/pre>\n
        This example uses the same HTML table source for the export operation. The difference is that the source table content marks some of the rows with no-export<\/em> class.<\/p>\n
        This class is configured in the above script with exclude<\/em> property of this plugin class.<\/p>\n
        Conclusion:<\/h2>\n
        So, we have seen three simple implementations of adding HTML table to excel feature. Though the export feature is vital, we must have a component that provides a seamless outcome.<\/p>\n
        I hope, the above solution can be a good base for creating such components. It is adaptable for sure to dock more features to have an efficient excel report generation tool.<\/p>\n
        View demo<\/a>Download<\/a><\/p>\n
          <\/p>\n
        \n
        Popular Articles<\/h2>\n<\/p><\/div>\n
         \u2191 Back to Top<\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"
        by Vincy. Last modified on July 25th, 2022. Converting a HTML table to an excel file is a standard requirement of reporting websites. It will be simple and easier if the conversion is taking place on the client side. There are many client-side and server-side plugins to perform the excel export. For example, PHPSpreadSheet allows […]<\/p>\n","protected":false},"author":2,"featured_media":126805,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65],"tags":[],"class_list":["post-126804","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php-updates"],"_links":{"self":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/126804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/comments?post=126804"}],"version-history":[{"count":0,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/posts\/126804\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media\/126805"}],"wp:attachment":[{"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/media?parent=126804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/categories?post=126804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickgaming.net\/blog\/wp-json\/wp\/v2\/tags?post=126804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}