Exporting database data to Excel is a common requirement in many web applications. PHP 8 provides various techniques and libraries that enable you to export database data to Excel files easily.
Using the PhpSpreadsheet Library:
PhpSpreadsheet is a powerful PHP library that allows you to read from and write to spreadsheet files, including Excel files. To export database data to Excel using PhpSpreadsheet, you can utilize the library's features. Here's an example:
//...
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Connect to the database
$dsn = 'mysql:host=localhost;dbname=test';
$username = 'username';
$password = 'password';
$pdo = new PDO($dsn, $username, $password);
// Fetch data from the database
$query = 'SELECT * FROM users';
$stmt = $pdo->query($query);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Create a new spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Add the data to the spreadsheet
$sheet->fromArray($data, null, 'A1');
// Save the spreadsheet to a file
$writer = new Xlsx($spreadsheet);
$writer->save('users.xlsx');
//...In this example, we first require the PhpSpreadsheet library. We establish a database connection using `PDO`. We fetch the data from the database using a query and store it in the `$data` variable. We create a new spreadsheet using `$spreadsheet = new Spreadsheet()`. We get the active sheet and add the data to the spreadsheet using `$sheet->fromArray()`. Finally, we save the spreadsheet to an Excel file using the `Xlsx` writer and the `save()` method.
Using the PHPSpreadsheet Exporter:
PHPSpreadsheet also provides an exporter class that simplifies the process of exporting database data to Excel. Here's an example:
//...
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Connect to the database
$dsn = 'mysql:host=localhost;dbname=test';
$username = 'username';
$password = 'password';
$pdo = new PDO($dsn, $username, $password);
// Fetch data from the database
$query = 'SELECT * FROM users';
$stmt = $pdo->query($query);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Create a new spreadsheet
$spreadsheet = IOFactory::load('template.xlsx');
// Add the data to the spreadsheet
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray($data, null, 'A2');
// Save the spreadsheet to a file
$writer = new Xlsx($spreadsheet);
$writer->save('users.xlsx');
//...In this example, we first require the PhpSpreadsheet library. We establish a database connection using `PDO`. We fetch the data from the database using a query and store it in the `$data` variable. We load a template spreadsheet using `IOFactory::load()`. We get the active sheet and add the data to the spreadsheet using `$sheet->fromArray()`. Finally, we save the spreadsheet to an Excel file using the `Xlsx` writer and the `save()` method.
These examples demonstrate techniques for exporting database data to Excel using PHP 8 and the PhpSpreadsheet library. You can customize the data retrieval and export process based on your database structure and requirements. Experiment with these examples and integrate them into your web applications to efficiently export database data to Excel files.