Convert table to excel php

Convert a table from an HTML file into an excel file with PHP using PHPSpreadSheet.

Import an HTML table into an excel file using PHPSpreadSheet. Maintain the same columns and rows, and keep the exact data to their specific positions.

Some PHP functions will be used like file_get_contents() to read the entire file into a string. To represent the entire HTML document, we will be using the class domDocument with the function loadHTML(). The function DOMXpath() with query() will evaluate the given XPath expression (table).

For PHPSpreadSheet, the classes Reader Html with loadFromString() will be used, then finally the IOFactory::createWriter() will write the new XLSX file which will contain the table from the HTML file.

This article will discuss the process of creating an excel file with the data coming from a given HTML file in PHP using PHPSpreadSheet.

Requirements:

Step 1.

composer.json

Step 2.

$ composer install
command line

Step 3.

Create a new PHP file (convert-html-table-into-excel.php), and start coding.

Step 4.

Prepare the sheet.

// Autoload dependencies require 'vendor/autoload.php'; // Import the IOFactory class use \PhpOffice\PhpSpreadsheet\IOFactory; // Import and assign the HTML reader class $reader = new \PhpOffice\PhpSpreadsheet\Reader\Html();

Step 5.

Prepare the data.

Read the HTML file into a string.

// Read the entire file into a string $data = file_get_contents('single-html-table.html');

Below is the content of the HTML file single-html-table.html

   
 
A1 B1 C1 D1 E1 F1 
 
A2 B2 C2 D2 E2 F2 
 
A3 B3 C3 D3 E3 F3 
 
A4 B4 C4 D4 E4 F4 
 
A5 B5 C5 D5 E5 F5

Step 6.

Create a new domDocument to load and get the table from the HTML string.

// Represent the entire HTML document $dom = new domDocument; // Load HTML from the string @$dom->loadHTML($data); // Create a new DOMXPath object $xpath = new DOMXpath($dom); // Evaluates the given XPath expression - table $elements = $xpath->query('//table'); // Get the string-formatted table $table = $elements[0]->C14N();

Step 7.

Use the loadFromString() to load the string into the $reader

// Load the html table string $spreadsheet = $reader->loadFromString($table);

Step 8.

Create and save the excel file.

// Write a new .xlsx file $writer = IOFactory::createWriter($spreadsheet, 'Xls'); // Save the new .xlsx file $writer->save('convert-html-table-into-excel.xls');

Complete code.

loadHTML($data); // Create a new DOMXPath object $xpath = new DOMXpath($dom); // Evaluates the given XPath expression - table $elements = $xpath->query('//table'); // Get the string-formatted table $table = $elements[0]->C14N(); // Load the html table string $spreadsheet = $reader->loadFromString($table); // Write a new .xlsx file $writer = IOFactory::createWriter($spreadsheet, 'Xls'); // Save the new .xlsx file $writer->save('convert-html-table-into-excel.xls'); 
convert-html-table-into-excel.php

Test.

Run the following codes.

$ php convert-html-table-into-excel.php
command line

Result.

Open the generated file convert-html-table-into-excel.xlsx.

Before – Browser preview.

After – Excel result.

References: