Kuinka viedä MySQL-tietokantatiedot Exceliin PHP: n avulla
Äskettäin julkaisin artikkelin CSV- ja Excel-tiedostojen lukeminen PHP: ssä PhpSpreadsheet-sovelluksen avulla ja yksi lukijoista kysyi MySQL-tietokantatietueiden viemisestä Excel-tiedostoon. Käyttäjä saattaa tarvita tietojaan Excel- tai CSV-tiedostossa, jotta hän voi lukea tai jakaa ne helposti. Tässä artikkelissa keskusteltiin siitä, kuinka tietoja voidaan viedä tietokannasta Excel- ja CSV-tiedostoihin. Tämän lisäksi tutkitaan, kuinka tämä viety tiedosto voidaan lähettää sähköpostin liitteenä.
Päästä alkuun
Aloittamiseksi sinun on ensin asennettava PhpSpreadsheet- kirjasto. Suosittelen, että kirjaston asennukseen käytetään Composeria. Avaa pääte projektin juurihakemistossa ja suorita komento:
composer require phpoffice/phpspreadsheetPhpSpreadsheet on kirjasto, joka tukee erityyppisten tiedostomuotojen lukemista ja kirjoittamista. Alla on kuvakaappaus tuetuista tiedostomuodoista.
Tavoitteenamme on viedä tietokantataulukoiden tietueet Excel / CSV-tiedostoihin. Otan "tuotteet" -taulukon esimerkkinä. Luodaan taulukko ‘tuotteet’ tietokantaan suorittamalla alla oleva kysely.
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;Lisää seuraavaksi joitain nuken merkintöjä taulukkoon, jotta näet muutaman tietueen viedyssä tiedostossa.
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');Tämän jälkeen luo db.phptiedosto, johon kirjoitamme tietokantayhteyden.
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);
}Muista korvata paikkamerkit todellisilla arvoilla yllä olevassa koodissa. Opetusohjelman seuraavassa osassa jaon erillisen osan tietojen viemisestä Exceliin, CSV: hen ja sähköpostin lähettämiseen viedyn tiedoston liitteenä.
Vie MySQL-tietokantatiedot Exceliin PHP: n avulla
Olet asentanut kirjaston ja olet valmis tietokantataulukkoon, jossa on nuken merkinnät. Nyt voit mennä eteenpäin ja kirjoittaa todellisen koodin, joka vie Excel-tiedoston, jossa on täytetyt tiedot.
Luo index.phptiedosto juurihakemistoon. Kirjoitan tähän tiedostoon SELECT-kyselyn hakemaan tietoja tietokannasta, kirjoitan nämä tiedot Exceliin ja lopuksi saamaan ne lataamaan automaattisesti.
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');Kun suoritat tämän PHP-tiedoston selaimessa, Excel-tiedosto alkaa ladata automaattisesti ja tällä Excel-taulukolla on seuraava tulos.
Vie MySQL-tietokantatiedot CSV-muotoon PHP: n avulla
Edellisessä osassa vietiin tietoja Excel-tiedostoon. Jos joku haluaa viedä tietoja CSV-tiedostona, sinun on muutettava muutama rivi yllä olevassa koodissa.
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');Lähetä sähköpostiviesti tiedostona liitteenä
Toistaiseksi tässä artikkelissa olemme nähneet, miten tiedosto ladataan CSV- ja Excel-muodossa. Jotkut käyttäjät saattavat haluta lähettää viedyn tiedoston sähköpostin liitteenä. Katsotaanpa, miten se saavutetaan.
Asenna ensin PHPMailer- kirjasto komennolla:
composer require phpmailer/phpmailerKun olet asentanut kirjaston, voit lähettää sähköpostia millä tahansa SMTP-palvelimella. Se on sinusta kiinni. Voit käyttää isännöimääsi SMTP-palvelinta, AWS SES -palvelua tai Gmailin SMTP-palvelinta. Jos aiot käyttää Gmailin SMTP-palvelinta, lue artikkeli Lähetä sähköpostia Gmailin SMTP-palvelimen avulla PHP-komentosarjalta, jossa selitetään sen määritykset.
Aikaisemmin kirjoitettu koodi lataa tiedoston automaattisesti. Mutta nyt sen sijaan, että tekisimme sen ladattavaksi, tallennamme tiedoston hakemistoon ja lähetämme sen sitten liitteenä. Lyhyesti sanottuna meillä on alla oleva koodi tiedoston tallentamiseen.
...
$filename = 'products.csv';
if (!file_exists('files')) {
mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);Viimeinen koodi lähettääksesi liitteen sähköpostitse on seuraava:
<?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;
}Korvaa paikkamerkit todellisilla arvoilla, ja sähköposti lähetetään liitteenä vastaanottajan sähköpostiosoitteeseen.
Toivon, että ymmärrät kuinka viedä MySQL-tietokantatietueet Excel- tai CSV-tiedostoon ja lähettää se myös liitteenä. Jaa ajatuksesi ja ehdotuksesi alla olevassa kommenttiosassa.