Как экспортировать данные базы данных MySQL в Excel с помощью PHP

Последнее обновление Июл 12, 2021

Содержание

Экспорт данных базы данных MySQL в Excel с помощью PHP

Экспорт данных базы данных MySQL в CSV с помощью PHP

Отправить электронное письмо с файлом в качестве вложения

Недавно я опубликовал статью «Как читать файлы CSV и Excel в PHP с помощью PhpSpreadsheet», и один из читателей спросил об экспорте записей базы данных MySQL в файл Excel. Пользователь может нуждаться в своих данных в файле Excel или CSV, чтобы он мог легко читать или делиться ими. В этой статье мы обсудили, как можно экспортировать данные из базы данных в файлы Excel и CSV. В дополнение к этому, мы также изучим, как можно отправить этот экспортированный файл в виде вложения в электронное письмо.

Начиная

Для начала вам необходимо установить библиотеку PhpSpreadsheet. Для установки библиотеки рекомендую использовать Composer. Откройте терминал в корневом каталоге вашего проекта и выполните команду:


composer require phpoffice/phpspreadsheet

PhpSpreadsheet — это библиотека, которая обеспечивает поддержку чтения и записи различных типов файловых форматов. Ниже приведен снимок экрана с поддерживаемыми форматами файлов.

Наша конечная цель — экспортировать записи таблицы базы данных в файл 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, а также отправить его как вложение. Пожалуйста, поделитесь своими мыслями и предложениями в разделе комментариев ниже.

Статьи по Теме

Источник записи: artisansweb.net