Як експортувати дані бази даних MySQL у Excel за допомогою PHP
Нещодавно я опублікував статтю Як читати CSV та файл Excel у PHP за допомогою таблиці PhpSpreadsheet, і один із читачів запитав про експорт записів бази даних MySQL у файл Excel. Користувачеві можуть знадобитися їх дані у файлі Excel або CSV, щоб він міг легко читати їх або ділитися ними. У цій статті ми обговорили, як можна експортувати дані з бази даних у файли Excel та CSV. На додаток до цього, ми також вивчимо, як можна надіслати цей експортований файл як вкладення в електронному листі.
Починаємо
Для початку вам спочатку потрібно встановити бібліотеку PhpSpreadsheet. Я рекомендую використовувати Composer для встановлення бібліотеки. Відкрийте термінал у кореневому каталозі проекту та запустіть команду:
composer require phpoffice/phpspreadsheetPhpSpreadsheet – це бібліотека, яка забезпечує підтримку читання та запису різних типів форматів файлів. Нижче наведено знімок екрана підтримуваних форматів файлів.
Наша кінцева мета – експорт записів таблиць бази даних у файл Excel / CSV. Я беру для прикладу таблицю "продукти". Давайте створимо таблицю «продукти» в базі даних, виконавши наведений нижче запит.
CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(255) NOT NULL,
`product_sku` varchar(255) NOT NULL,
`product_price` varchar(255) NOT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;Далі додайте кілька фіктивних записів у таблицю, щоб ви побачили кілька записів у експортованому файлі.
INSERT INTO `products` (`id`, `product_name`, `product_sku`, `product_price`) VALUES
(1, 'Apple', 'app_111', '$1000'),
(2, 'Lenovo', 'len_222', '$999'),
(3, 'Samsung', 'sam_689', '$1200'),
(4, 'Acer', 'ace_620', '$900');Після цього створіть db.phpфайл, де ми будемо писати підключення до бази даних.
db.php
<?php
$db_host = 'DATABASE_HOST';
$db_username = 'DATABASE_USERNAME';
$db_password = 'DATABASE_PASSWORD';
$db_name = 'DATABASE_NAME';
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
if($db->connect_error){
die("Unable to connect database: ". $db->connect_error);
}Не забудьте замінити заповнювачі фактичними значеннями у наведеному вище коді. У наступній частині підручника я виділяю окремий розділ про експорт даних до Excel, до CSV та надсилання електронного листа з експортованим файлом як вкладення.
Експортуйте дані бази даних MySQL у Excel за допомогою PHP
Ви встановили бібліотеку і готові до створення таблиці бази даних із фіктивними записами. Тепер ви можете продовжити і написати фактичний код, який експортуватиме файл Excel із заповненими даними.
Створіть index.phpфайл у кореневому каталозі. У цьому файлі я напишу запит SELECT для отримання даних із бази даних, запишу ці дані в Excel і, нарешті, змушу їх завантажувати автоматично.
index.php
<?php
require_once "vendor/autoload.php";
require_once "db.php";
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
if($query->num_rows > 0) {
$i = 2;
while($row = $query->fetch_assoc()) {
$activeSheet->setCellValue('A'.$i, $row['product_name']);
$activeSheet->setCellValue('B'.$i, $row['product_sku']);
$activeSheet->setCellValue('C'.$i, $row['product_price']);
$i++;
}
}
$filename = 'products.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');Коли ви запускаєте цей PHP-файл у веб-переглядачі, файл Excel почне завантажуватися автоматично, і цей аркуш Excel отримає такі дані.
Експортуйте дані бази даних MySQL у CSV за допомогою PHP
У попередньому розділі ми експортували дані у файл Excel. Якщо хтось хоче експортувати дані у файл CSV, вам потрібно змінити кілька рядків у наведеному вище коді.
csv.php
<?php
require_once "vendor/autoload.php";
require_once "db.php";
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterCsv;
$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
if($query->num_rows > 0) {
$i = 2;
while($row = $query->fetch_assoc()) {
$activeSheet->setCellValue('A'.$i, $row['product_name']);
$activeSheet->setCellValue('B'.$i, $row['product_sku']);
$activeSheet->setCellValue('C'.$i, $row['product_price']);
$i++;
}
}
$filename = 'products.csv';
header('Content-Type: application/text-csv');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');Надішліть електронний лист із файлом як додатком
Поки що в цій статті ми бачили, як завантажити файл у форматі CSV та Excel. Деякі користувачі можуть захотіти надіслати експортований файл як вкладення в електронному листі. Давайте подивимося, як цього досягти.
Спочатку встановіть бібліотеку PHPMailer за допомогою команди:
composer require phpmailer/phpmailerПісля встановлення бібліотеки ви можете використовувати будь-який SMTP-сервер для надсилання електронного листа. Тобі вирішувати. Ви можете використовувати SMTP-сервер, що надається хостингом, AWS SES або SMTP-сервер Gmail. Якщо ви хочете використовувати SMTP-сервер Gmail, прочитайте нашу статтю Надіслати електронну пошту за допомогою SMTP-сервера Gmail зі сценарію PHP, де пояснюється необхідна для нього конфігурація.
Код написаний раніше завантажує файл автоматично. Але тепер, замість того, щоб зробити його завантажуваним, ми збережемо файл у каталозі, а потім надішлемо його як вкладення. Коротше, у нас буде код нижче для збереження файлу.
...
$filename = 'products.csv';
if (!file_exists('files')) {
mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);Наш остаточний код для надсилання вкладення в електронному листі буде таким:
<?php
require_once "vendor/autoload.php";
require_once "db.php";
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterCsv;
//Import PHPMailer classes into the global namespace
use PHPMailerPHPMailerPHPMailer;
use PHPMailerPHPMailerException;
$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
if($query->num_rows > 0) {
$i = 2;
while($row = $query->fetch_assoc()) {
$activeSheet->setCellValue('A'.$i, $row['product_name']);
$activeSheet->setCellValue('B'.$i, $row['product_sku']);
$activeSheet->setCellValue('C'.$i, $row['product_price']);
$i++;
}
}
$filename = 'products.csv';
if (!file_exists('files')) {
mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);
// send as an attachment
$mail = new PHPMailer(true);
try {
$mail->isSMTP();
$mail->Host = 'SMTP_HOST';
$mail->SMTPAuth = true;
$mail->Username = 'SMTP_USERNAME'; //username
$mail->Password = 'SMTP_PASSWORD'; //password
$mail->SMTPSecure = 'ssl';
$mail->Port = 465;
$mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME');
$mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME');
$mail->addAttachment('files/'.$filename);
$mail->isHTML(true);
$mail->Subject = 'Our Exported File';
$mail->Body = 'Our Exported File';
$mail->send();
echo 'Message has been sent';
} catch (Exception $e) {
echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo;
}Замініть заповнювачі фактичними значеннями, і електронне повідомлення буде надіслано з вкладенням на адресу електронної пошти одержувача.
Сподіваюся, ви розумієте, як експортувати записи бази даних MySQL у файл Excel або CSV, а також надіслати їх як вкладення. Будь ласка, поділіться своїми думками та пропозиціями в розділі коментарів нижче.
Пов’язані статті
- Як перенести файли на віддалений сервер у PHP
- Надіслати електронну пошту за допомогою Mailjet (альтернатива SMTP-серверу Gmail) у PHP
- Як конвертувати HTML в PDF у PHP