Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tut] Convert HTML Table to Excel using JavaScript

#1
Convert HTML Table to Excel using JavaScript

<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/07/convert-html-table-to-excel-using-javascript.jpg" width="550" height="318" title="" alt="" /></div><div><div class="modified-on" readability="7.0697674418605"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on July 25th, 2022.</div>
<p>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>
<p>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>
<p>This article will give different options and approaches to achieving the HTML table to excel conversion with JavaScript.</p>
<p>This simple example includes a few lines of JavaScript that build the template for excel export.</p>
<p>It uses the following <span>steps to convert</span> the exported excel report of tabular data.</p>
<ol>
<li>Defines HTML template structure with required meta.</li>
<li>Include the table’s inner HTML into the template.</li>
<li>Marks the location by specifying the protocol to download the file via browser.</li>
<li><a href="https://phppot.com/javascript/javascript-redirect/">Redirect via JavaScript</a> to point to the location with the encoded excel content.</li>
</ol>
<div class="post-section-highlight" readability="38">
<h2>Quick example</h2>
<p>A JavaScript handler to set export template and push HTML table data.</p>
<pre class="prettyprint"><code class="language-javascript">function exportToExcel() { var location = 'data:application/vnd.ms-excel;base64,'; var excelTemplate = '&lt;html&gt; ' + '&lt;head&gt; ' + '&lt;meta http-equiv="content-type" content="text/plain; charset=UTF-8"/&gt; ' + '&lt;/head&gt; ' + '&lt;body&gt; ' + document.getElementById("table-conatainer").innerHTML + '&lt;/body&gt; ' + '&lt;/html&gt;' window.location.href = location + window.btoa(excelTemplate);
}
</code></pre>
</div>
<p><a class="demo" href="https://phppot.com/demo/html-table-excel-javascript/">View demo</a></p>
<p>The below HTML table code displays a product listing and it is static. Refer <a href="https://phppot.com/javascript/jspdf-autotable/">jsPDF AutoTables examples</a> to render a dynamic table by loading row by row.</p>
<p>The button below this table triggers the export to excel process on the click event. The <em>exportToExcel()</em> function handles the event and proceeds the client-side export.</p>
<pre class="prettyprint"><code class="language-html">&lt;div id="table-conatainer"&gt; &lt;table class="striped"&gt; &lt;thead&gt; &lt;tr&gt; &lt;th&gt;S.No&lt;/th&gt; &lt;th&gt;Product Name&lt;/th&gt; &lt;th&gt;Price&lt;/th&gt; &lt;th&gt;Model&lt;/th&gt; &lt;/tr&gt; &lt;/thead&gt; &lt;tbody&gt; &lt;tr&gt; &lt;td&gt;1q&lt;/td&gt; &lt;td&gt;GIZMORE Multimedia Speaker with Remote Control, Black&lt;/td&gt; &lt;td&gt;2300&lt;/td&gt; &lt;td&gt;2020&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;2&lt;/td&gt; &lt;td&gt;Black Google Nest Mini&lt;/td&gt; &lt;td&gt;3400&lt;/td&gt; &lt;td&gt;2021&lt;/td&gt; &lt;/tr&gt; &lt;/tbody&gt; &lt;/table&gt;
&lt;/div&gt;
</code></pre>
<p><img loading="lazy" class="alignnone size-large wp-image-18767" src="https://phppot.com/wp-content/uploads/2022/07/html-table-excel-javascript-550x318.jpg" alt="html table excel javascript" width="550" height="318" srcset="https://phppot.com/wp-content/uploads/2022/07/html-table-excel-javascript-550x318.jpg 550w, https://phppot.com/wp-content/uploads/20...00x173.jpg 300w, https://phppot.com/wp-content/uploads/20...68x444.jpg 768w, https://phppot.com/wp-content/uploads/20...script.jpg 800w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>Let us see more options available for converting HTML table data into an excel file.</p>
<h2>Option 2: Export as CSV to view in excel</h2>
<p>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>
<ol>
<li>Accessing HTML table element object.</li>
<li>Convert the object into an array of row data.</li>
<li>Iterate row data array to prepare <a href="https://phppot.com/php/php-csv-file-read/">comma-separated values</a> of records.</li>
<li>Set the protocol and content type to export the CSV data prepared from the table.</li>
</ol>
<p>The below JavaScript function implements the above steps to export table data on the client side.</p>
<pre class="prettyprint"><code class="language-javascript">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);
}
</code></pre>
<h2>Option 3 – Export HTML table to excel using jQuery-based plugin</h2>
<p>The jQuery table2excel plugin is a popular solution to arrive HTML to excel export.</p>
<p>It has many features with the export core functionalities. Those are,</p>
<ol>
<li>Excludes/includes HTML tags like inputs, links, or images that are in the source table HTML.</li>
<li>Excludes some table components with the reference of that particular element’s class or id selectors.</li>
<li>Provides properties to set file name with which the exported data will be downloaded to the browser.</li>
</ol>
<p>Include the jQuery and the table2excel plugin file in the&nbsp;<em>&lt;head&gt;</em> section as below.</p>
<p>There are alternative methods to install the table2excel plugin. Its <a href="https://github.com/rainabba/jquery-table2excel" target="_blank" rel="noopener">Github page provides documentation</a> of installation and usage methodologies.</p>
<pre class="prettyprint"><code class="language-html">&lt;script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"&gt;&lt;/script&gt;
&lt;script src="https://cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"&gt;&lt;/script&gt;
</code></pre>
<p>Then, initiate the plugin class by pointing to the source HTML table element.</p>
<p>The below JavaScript does the initiation. During instantiation, it sets the options array to specify a file name, extension and applicable flags.</p>
<pre class="prettyprint"><code class="language-javascript">function exportCSVExcel() { $('#table-product-list').table2excel({ exclude: ".no-export", filename: "download.xls", fileext: ".xls", exclude_links: true, exclude_inputs: true });
}
</code></pre>
<p>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 <em>no-export</em> class.</p>
<p>This class is configured in the above script with&nbsp;<em>exclude</em> property of this plugin class.</p>
<h2>Conclusion:</h2>
<p>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>
<p>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>
<p><a class="demo" href="https://phppot.com/demo/html-table-excel-javascript/">View demo</a><a class="download" href="https://phppot.com/downloads/html-table-excel-javascript.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/javascript/convert-html-table-excel-javascript/#top" class="top">↑ Back to Top</a> </p>
</div>


https://www.sickgaming.net/blog/2022/07/...avascript/
Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

Forum software by © MyBB Theme © iAndrew 2016