Introduction:

Converting HTML data into Excel spreadsheets is a common need for effective analysis and visualization. This blog will explore multiple methods to convert HTML data into Excel files in PHP. 

Method 1: Manual HTML Parsing and Excel Writing

The first method involves manually parsing the HTML content and writing it into an Excel file. This approach provides granular control but requires a deep understanding of both HTML and Excel formats.

// Sample HTML content
$html = '<table>
            <tr><th>Name</th><th>Age</th></tr>
            <tr><td>John</td><td>25</td></tr>
            <tr><td>Lisa</td><td>30</td></tr>
         </table>';

// Create a new Excel workbook and worksheet
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Parse HTML and populate Excel
$dom = new DOMDocument();
$dom->loadHTML($html);
$rows = $dom->getElementsByTagName('tr');
foreach ($rows as $row) {
    $columns = $row->getElementsByTagName('td');
    $rowData = [];
    foreach ($columns as $column) {
        $rowData[] = $column->nodeValue;
    }
    $sheet->appendRow($rowData);
}

// Save Excel file
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('output_method1.xlsx');

Output:

The output of Method 1 is an Excel file named "output_method1.xlsx" containing the data extracted from the provided HTML table.

This method offers precise control over the conversion process, allowing you to select specific HTML elements for inclusion in the Excel file. It involves using the PhpSpreadsheet library to create, populate, and save the Excel file. The provided PHP code parses the HTML content, extracts table data, and writes it to an Excel sheet.

Method 2: Using HTML to Excel Conversion Libraries

In this method, we'll leverage third-party libraries that simplify the conversion process by abstracting HTML parsing and Excel writing complexities.

require 'vendor/autoload.php';

use JamesHeinrich\GetID3\GetID3;

// Sample HTML content
$html = '<table>
            <tr><th>Name</th><th>Age</th></tr>
            <tr><td>Michael</td><td>28</td></tr>
            <tr><td>Sara</td><td>22</td></tr>
         </table>';

// Convert HTML to Excel using HTML_To_XLS library
$htmlToXls = new HTML_To_XLS();
$htmlToXls->writeSheet($html, 'output_method2.xls');

Output:

The output of Method 2 is an Excel file named "output_method2.xls" containing the data converted from the provided HTML table.

This approach relies on the "HTML_To_XLS" library to simplify the conversion process. The library's "writeSheet" function accepts HTML content and generates an Excel file. This method is more user-friendly and eliminates the need for manual Excel manipulation.

Method 3: Google Sheets API Integration

Method 3 involves integrating the Google Sheets API to import HTML data into a Google Sheets document, which can then be downloaded as an Excel file.

require 'vendor/autoload.php';

// Sample HTML content
$html = '<table>
            <tr><th>Name</th><th>Age</th></tr>
            <tr><td>Emily</td><td>35</td></tr>
            <tr><td>Daniel</td><td>31</td></tr>
         </table>';

// Set up Google Sheets API credentials and client
$client = getClient();
$service = new Google_Service_Sheets($client);

// Create a new Google Sheets spreadsheet
$spreadsheet = new Google_Service_Sheets_Spreadsheet();
$spreadsheet = $service->spreadsheets->create($spreadsheet);

// Parse HTML and populate Google Sheets
$dom = new DOMDocument();
$dom->loadHTML($html);
$rows = $dom->getElementsByTagName('tr');
$data = [];
foreach ($rows as $row) {
    $columns = $row->getElementsByTagName('td');
    $rowData = [];
    foreach ($columns as $column) {
        $rowData[] = $column->nodeValue;
    }
    $data[] = $rowData;
}

// Write data to Google Sheets
$range = 'Sheet1!A1:B' . count($data);
$values = new Google_Service_Sheets_ValueRange(['values' => $data]);
$service->spreadsheets_values->update($spreadsheet->spreadsheetId, $range, $values, ['valueInputOption' => 'RAW']);

// Download Google Sheets as Excel
$response = $service->spreadsheets->get($spreadsheet->spreadsheetId, ['alt' => 'media']);
file_put_contents('output_method3.xlsx', $response->getBody()->getContents());

Output:

The output of Method 3 is an Excel file named "output_method3.xlsx" containing the data imported from the provided HTML table via Google Sheets.

This method integrates the Google Sheets API to facilitate the conversion. The PHP program parses the HTML content, populates a new Google Sheets document with the data, and then downloads the document in Excel format.

Conclusion:

In this blog, we explored practical methods to convert HTML data into Excel files using PHP. We covered manual HTML parsing and Excel writing, library-based conversion, and Google Sheets API integration.