Jak wyeksportować dane bazy danych MySQL do programu Excel za pomocą PHP

Ostatnia aktualizacja paź 18, 2021

Zadowolony

Eksportuj dane bazy danych MySQL do Excela za pomocą PHP

Eksportuj dane bazy danych MySQL do CSV za pomocą PHP

Wyślij e-mail z plikiem jako załącznikiem

Niedawno opublikowałem artykuł Jak czytać plik CSV i Excel w PHP za pomocą arkusza PhpSpreadsheet i jeden z czytelników zapytał o eksport rekordów bazy danych MySQL do pliku Excel. Użytkownik może potrzebować swoich danych w pliku Excel lub CSV, aby móc je łatwo czytać lub udostępniać. W tym artykule omówiliśmy, w jaki sposób można eksportować dane z bazy danych do plików Excel i CSV. Oprócz tego przeanalizujemy również, w jaki sposób można wysłać ten wyeksportowany plik jako załącznik do wiadomości e-mail.

Pierwsze kroki

Aby rozpocząć, musisz najpierw zainstalować bibliotekę PhpSpreadsheet. Do instalacji biblioteki polecam używać Composera. Otwórz terminal w katalogu głównym projektu i uruchom polecenie:

composer require phpoffice/phpspreadsheet

PhpSpreadsheet to biblioteka, która zapewnia obsługę odczytu i zapisu różnych typów formatów plików. Poniżej znajduje się zrzut ekranu obsługiwanych formatów plików.

Naszym celem końcowym jest wyeksportowanie rekordów tabeli bazy danych do pliku Excel/CSV. Jako przykład biorę tabelę „produkty". Utwórzmy tabelę „products” w bazie danych, uruchamiając poniższe zapytanie.

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;

Następnie dodaj kilka fikcyjnych wpisów w tabeli, aby zobaczyć kilka rekordów w wyeksportowanym pliku.

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');

Następnie utwórz db.phpplik, w którym zapiszemy połączenie z bazą danych.

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);
}

Pamiętaj, aby zastąpić symbole zastępcze rzeczywistymi wartościami w powyższym kodzie. W dalszej części tutoriala przeznaczam osobną sekcję dotyczącą eksportu danych do Excela, do CSV oraz wysyłania maila z wyeksportowanym plikiem jako załącznikiem.

Eksportuj dane bazy danych MySQL do Excela za pomocą PHP

Zainstalowałeś bibliotekę i jesteś gotowy z tabelą bazy danych z fikcyjnymi wpisami. Teraz możesz iść dalej i napisać rzeczywisty kod, który wyeksportuje plik Excela z wypełnionymi danymi.

Utwórz index.phpplik w katalogu głównym. W tym pliku napiszę zapytanie SELECT, aby pobrać dane z bazy danych, zapisać te dane do Excela i na koniec sprawić, że zostaną one automatycznie pobrane.

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');

Po uruchomieniu tego pliku PHP w przeglądarce automatycznie rozpocznie się pobieranie pliku Excel, a ten arkusz Excela będzie zawierał następujące dane wyjściowe.

Eksportuj dane bazy danych MySQL do CSV za pomocą PHP

W poprzedniej sekcji wyeksportowaliśmy dane do pliku Excel. Jeśli ktoś chce wyeksportować dane w pliku CSV, to trzeba zmienić kilka linijek w powyższym kodzie.

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');

Wyślij e-mail z plikiem jako załącznikiem

Do tej pory w tym artykule widzieliśmy, jak pobrać plik w formacie CSV i Excel. Niektórzy użytkownicy mogą chcieć wysłać wyeksportowany plik jako załącznik do wiadomości e-mail. Zobaczmy, jak to osiągnąć.

Najpierw zainstaluj bibliotekę PHPMailer za pomocą polecenia:

composer require phpmailer/phpmailer

Po zainstalowaniu biblioteki możesz użyć dowolnego serwera SMTP do wysłania wiadomości e-mail. To zależy od Ciebie. Możesz użyć serwera SMTP, AWS SES lub serwera SMTP Gmaila. Jeśli wybierasz się z serwerem Gmail SMTP, przeczytaj nasz artykuł Wysyłanie wiadomości e-mail za pomocą serwera SMTP Gmail z PHP Script, który wyjaśnia potrzebną konfigurację.

Kod napisany wcześniej pobiera plik automatycznie. Ale teraz, zamiast udostępniać go do pobrania, zapiszemy plik w katalogu, a następnie wyślemy go jako załącznik. W skrócie, poniżej będziemy mieli kod do zapisania pliku.

...
$filename = 'products.csv';
 
if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);

Nasz ostateczny kod do wysłania załącznika w wiadomości e-mail będzie wyglądał następująco:

<?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;
}

Zastąp symbole zastępcze rzeczywistymi wartościami, a wiadomość e-mail zostanie wysłana z załącznikiem na adres e-mail odbiorcy.

Mam nadzieję, że rozumiesz, jak wyeksportować rekordy bazy danych MySQL do pliku Excel lub CSV, a także wysłać go jako załącznik. Podziel się swoimi przemyśleniami i sugestiami w sekcji komentarzy poniżej.

Powiązane artykuły

Źródło nagrywania: artisansweb.net