Convert HTML Table to Excel using JavaScript

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 writing data into excel and exporting.

This article will give different options and approaches to achieving the HTML table to excel conversion with JavaScript.

This simple example includes a few lines of JavaScript that build the template for excel export.

It uses the following steps to convert the exported excel report of tabular data.

  1. Defines HTML template structure with required meta.
  2. Include the table’s inner HTML into the template.
  3. Marks the location by specifying the protocol to download the file via browser.
  4. Redirect via JavaScript to point to the location with the encoded excel content.

Quick example

A JavaScript handler to set export template and push HTML table data.

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);
}

View demo

The below HTML table code displays a product listing and it is static. Refer jsPDF AutoTables examples to render a dynamic table by loading row by row.

The button below this table triggers the export to excel process on the click event. The exportToExcel() function handles the event and proceeds the client-side export.

<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>
</div>

html table excel javascript

Let us see more options available for converting HTML table data into an excel file.

Option 2: Export as CSV to view in excel

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.

  1. Accessing HTML table element object.
  2. Convert the object into an array of row data.
  3. Iterate row data array to prepare comma-separated values of records.
  4. Set the protocol and content type to export the CSV data prepared from the table.

The below JavaScript function implements the above steps to export table data on the client side.

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(",") + "rn";
  i++;
 }
 window.location.href = encodeURI(sourceData);
}

Option 3 – Export HTML table to excel using jQuery-based plugin

The jQuery table2excel plugin is a popular solution to arrive HTML to excel export.

It has many features with the export core functionalities. Those are,

  1. Excludes/includes HTML tags like inputs, links, or images that are in the source table HTML.
  2. Excludes some table components with the reference of that particular element’s class or id selectors.
  3. Provides properties to set file name with which the exported data will be downloaded to the browser.

Include the jQuery and the table2excel plugin file in the <head> section as below.

There are alternative methods to install the table2excel plugin. Its Github page provides documentation of installation and usage methodologies.

<script
    src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script
    src="https://cdn.rawgit.com/rainabba/jquery-table2excel/1.1.0/dist/jquery.table2excel.min.js"></script>

Then, initiate the plugin class by pointing to the source HTML table element.

The below JavaScript does the initiation. During instantiation, it sets the options array to specify a file name, extension and applicable flags.

function exportCSVExcel() {
 $('#table-product-list').table2excel({
  exclude: ".no-export",
  filename: "download.xls",
  fileext: ".xls",
  exclude_links: true,
  exclude_inputs: true
 });
}

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 class.

This class is configured in the above script with exclude property of this plugin class.

Conclusion:

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.

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.

View demoDownload

↑ Back to Top

Credit: Source link

Add Watermark using PHP to Existing or New PDF

by Vincy. Last modified on July 16th, 2022.

FPDF is a fantastic library for working with PDF documents in PHP. It is the most popular one with a large number of plugins that enhances its core features.

Adding a watermark to a PDF document is a basic need in document editing work. Imagine that you have a project where you need to create a feature-rich PDF document editor project with the sole purpose of managing watermarks.

This PHP script will help you as a foundation code to solve your basic watermarking needs. You can use this as a base and build on top of it if you require more.

I have covered two aspects of adding watermarks.

  1. Add a watermark to an existing PDF document.
  2. Add a watermark to a new PDF document.

I am also presenting an online demo that watermarks a PDF document using this PHP script.

If you are looking for adding a watermark to a web page with a generated image using PHP, refer to this linked article.

View demo

PHP Watermark PDF

Add a watermark to an existing PDF document

To add a watermark to an existing PDF document, I am using FPDF and FPDI libraries. FPDF is the foundation and FPDI is used to load and edit an existing document.

FPDI helps to load an existing PDF document and use it as a template in FPDF to create a document.

You need to download both FPDF and FPDI libraries and add it to the project. Refer to the project file structure image below.

I have earlier written with an example to convert HTML to PDF in JavaScript. Check it out and may help you.

existing-pdf-watermark.php

<?php
require_once __DIR__ . '/fpdf/fpdf.php';
require_once __DIR__ . '/FPDI/src/autoload.php';

function addWatermark($x, $y, $watermarkText, $angle, $pdf)
{
    $angle = $angle * M_PI / 180;
    $c = cos($angle);
    $s = sin($angle);
    $cx = $x * 1;
    $cy = (300 - $y) * 1;
    $pdf->_out(sprintf('q %.5F %.5F %.5F %.5F %.2F %.2F cm 1 0 0 1 %.2F %.2F cm', $c, $s, - $s, $c, $cx, $cy, - $cx, - $cy));
    $pdf->Text($x, $y, $watermarkText);
    $pdf->_out('Q');
}

$pdf = new setasignFpdiFpdi();
$fileInput="example.pdf";
$pages_count = $pdf->setSourceFile($fileInput);
for ($i = 1; $i <= $pages_count; $i ++) {
    $pdf->AddPage();
    $tplIdx = $pdf->importPage($i);
    $pdf->useTemplate($tplIdx, 0, 0);
    $pdf->SetFont('Times', 'B', 70);
    $pdf->SetTextColor(192, 192, 192);
    $watermarkText="CONFIDENTIAL";
    addWatermark(105, 220, $watermarkText, 45, $pdf);
    $pdf->SetXY(25, 25);
}
$pdf->Output();
?>

Add a watermark to a new PDF document

Following PHP script is as simple as it gets. The Header function is used to render the PDF document page header. This is called by the AddPage function.

addWatermark is the key function responsible for creating the watermark. It sets the watermark text and performs the rotation to position it across the document. The X and Y location of where to start the watermark text and its color is defined in the Header function.

You can make adjustments by setting a smaller font, position, color, etc as per your choice.

new-pdf-watermark.php

<?php
require __DIR__ . '/fpdf/fpdf.php';

class PDF extends FPDF
{

    function Header()
    {
        // setting the font, color and text for watermark
        $this->SetFont('Times', 'B', 48);
        $this->SetTextColor(140, 180, 205);
        $watermarkText="New PDF Watermark - PHP";
        $this->addWatermark(35, 190, $watermarkText, 45);
    }

    function addWatermark($x, $y, $watermarkText, $angle)
    {
        $angle = $angle * M_PI / 180;
        $c = cos($angle);
        $s = sin($angle);
        $cx = $x * $this->k;
        $cy = ($this->h - $y) * $this->k;
        $this->_out(sprintf('q %.5F %.5F %.5F %.5F %.2F %.2F cm 1 0 0 1 %.2F %.2F cm', $c, $s, - $s, $c, $cx, $cy, - $cx, - $cy));
        $this->Text($x, $y, $watermarkText);
        $this->_out('Q');
    }
}

$pdf = new PDF();
$pdf->AddPage();
$pdf->SetFont('Arial', '', 12);
$pdfDocumentContent = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. .nn";
for ($i = 0; $i < 15; $i ++) {
    $pdf->MultiCell(0, 5, $pdfDocumentContent, 0, 'J');
}
$pdf->Output();
?>

PHP project structure

PHP PDF Watermark Project Structure

I have given the complete PHP project as a free download below. The dependent libraries FPDF and FPDI are not available in the project download zip file. You can download them from their official website and add them to your project as per the given project structure above before adding watermarks.

View demo Download

↑ Back to Top

Credit: Source link