{"id":25184,"date":"2021-05-25T11:18:00","date_gmt":"2021-05-25T08:18:00","guid":{"rendered":"https:\/\/themewp.inform.click\/?p=25184"},"modified":"2021-10-17T18:49:17","modified_gmt":"2021-10-17T15:49:17","slug":"kuidas-eksportida-mysql-i-andmebaasiandmeid-excelisse-php-abil","status":"publish","type":"post","link":"https:\/\/themewp.inform.click\/et\/kuidas-eksportida-mysql-i-andmebaasiandmeid-excelisse-php-abil\/","title":{"rendered":"Kuidas eksportida MySQL-i andmebaasiandmeid Excelisse PHP abil"},"content":{"rendered":"

Hiljuti avaldasin artikli Kuidas lugeda CSV- ja Exceli faile PHP-s PhpSpreadsheetiga<\/a> ning \u00fcks lugejatest k\u00fcsis MySQL-i andmebaasikirjete ekspordi kohta Exceli faili. Kasutajal v\u00f5ib olla vaja oma andmeid Exceli v\u00f5i CSV-failis, et nad saaksid neid h\u00f5lpsalt lugeda v\u00f5i jagada. Selles artiklis k\u00e4sitlesime, kuidas saab andmeid andmebaasist eksportida Exceli ja CSV-failidesse. Lisaks sellele uurime ka seda, kuidas saab seda eksporditud faili manusena manusena saata.<\/p>\n

Alustamine<\/h3>\n

Alustamiseks peate k\u00f5igepealt installima PhpSpreadsheet’i<\/a> teegi. Teegi installimiseks soovitan kasutada Composerit<\/a>. Avage terminal oma projekti juurkataloogis ja k\u00e4ivitage k\u00e4sk:<\/p>\n

composer require phpoffice\/phpspreadsheet<\/code><\/pre>\n
PhpSpreadsheet on raamatukogu, mis pakub tuge erinevat t\u00fc\u00fcpi failivormingute lugemiseks ja kirjutamiseks. Allpool on ekraanipilt toetatud failivormingutest.<\/p>\n
\"Kuidas<\/a><\/p>\n
Meie eesm\u00e4rk on andmebaasitabelite kirjete eksportimine Exceli \/ CSV-faili. V\u00f5tan n\u00e4iteks toote tabeli. Loome andmebaasi tabeli ‘tooted’, k\u00e4ivitades alloleva p\u00e4ringu.<\/p>\n
CREATE TABLE `products` (\u00a0`id` int(11) NOT NULL AUTO_INCREMENT,\n\u00a0`product_name` varchar(255) NOT NULL,\n\u00a0`product_sku` varchar(255) NOT NULL,\n\u00a0`product_price` varchar(255) NOT NULL,\n\u00a0PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/code><\/pre>\n
J\u00e4rgmisena lisage tabelisse m\u00f5ned n\u00e4ivkirjed, nii et eksporditud failis n\u00e4ete m\u00f5nda kirjet.<\/p>\n
INSERT INTO `products` (`id`, `product_name`, `product_sku`, `product_price`) VALUES\n(1, 'Apple', 'app_111', '$1000'),\n(2, 'Lenovo', 'len_222', '$999'),\n(3, 'Samsung', 'sam_689', '$1200'),\n(4, 'Acer', 'ace_620', '$900');<\/code><\/pre>\n
P\u00e4rast seda looge db.php<\/code>fail, kuhu kirjutame andmebaasi \u00fchenduse.<\/p>\n
db.php<\/strong><\/p>\n
<?php\n$db_host = 'DATABASE_HOST';\n$db_username = 'DATABASE_USERNAME';\n$db_password = 'DATABASE_PASSWORD';\n$db_name = 'DATABASE_NAME';\n\u00a0\n$db = new mysqli($db_host, $db_username, $db_password, $db_name);\n\u00a0\nif($db->connect_error){\n\u00a0\u00a0\u00a0\u00a0die(\"Unable to connect database: \". $db->connect_error);\n}<\/code><\/pre>\n
Asendage kohat\u00e4ited \u00fclaltoodud koodi tegelike v\u00e4\u00e4rtustega. \u00d5petuse j\u00e4rgmises osas eraldan eraldi jaotise andmete eksportimiseks Excelisse, CSV-sse ja meilis\u00f5numi saatmiseks koos eksporditud failiga.<\/p>\n
MySQL-i andmebaasiandmete eksportimine Excelisse PHP abil<\/h3>\n
Olete installinud kogu ja olete valmis mannekeenikirjetega andmebaasitabeliga. N\u00fc\u00fcd v\u00f5ite j\u00e4tkata ja kirjutada tegeliku koodi, mis ekspordib Exceli faili koos t\u00e4idetud andmetega.<\/p>\n
Looge index.php<\/code>fail juurkataloogis. Selles failis kirjutan andmebaasist andmete toomiseks p\u00e4ringu SELECT, kirjutan need andmed Excelisse ja panen need l\u00f5puks automaatselt alla laadima.<\/p>\n
index.php<\/strong><\/p>\n
<?php\nrequire_once \"vendor\/autoload.php\";\nrequire_once \"db.php\";\n\u00a0\nuse PhpOfficePhpSpreadsheetSpreadsheet;\nuse PhpOfficePhpSpreadsheetWriterXlsx;\n\u00a0\n$spreadsheet = new Spreadsheet();\n$Excel_writer = new Xlsx($spreadsheet);\n\u00a0\n$spreadsheet->setActiveSheetIndex(0);\n$activeSheet = $spreadsheet->getActiveSheet();\n\u00a0\n$activeSheet->setCellValue('A1', 'Product Name');\n$activeSheet->setCellValue('B1', 'Product SKU');\n$activeSheet->setCellValue('C1', 'Product Price');\n\u00a0\n$query = $db->query(\"SELECT * FROM products ORDER BY id DESC\");\n\u00a0\nif($query->num_rows > 0) {\n\u00a0\u00a0\u00a0\u00a0$i = 2;\n\u00a0\u00a0\u00a0\u00a0while($row = $query->fetch_assoc()) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('A'.$i, $row['product_name']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('B'.$i, $row['product_sku']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('C'.$i, $row['product_price']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$i++;\n\u00a0\u00a0\u00a0\u00a0}\n}\n\u00a0\n$filename = 'products.xlsx';\n\u00a0\nheader('Content-Type: application\/vnd.ms-excel');\nheader('Content-Disposition: attachment;filename='. $filename);\nheader('Cache-Control: max-age=0');\n$Excel_writer->save('php:\/\/output');<\/code><\/pre>\n
Kui k\u00e4ivitate selle PHP-faili brauseris, hakkab Exceli fail automaatselt alla laadima ja sellel Exceli lehel on j\u00e4rgmine v\u00e4ljund.<\/p>\n
\"Kuidas<\/a><\/p>\n
MySQL-i andmebaasiandmete eksportimine CSV-faili kasutades PHP-d<\/h3>\n
Eelmises osas eksportisime andmed Exceli faili. Kui keegi soovib eksportida andmeid CSV-faili, peate \u00fclaltoodud koodis muutma m\u00f5nda rida.<\/p>\n
csv.php<\/strong><\/p>\n
<?php\nrequire_once \"vendor\/autoload.php\";\nrequire_once \"db.php\";\n\u00a0\nuse PhpOfficePhpSpreadsheetSpreadsheet;\nuse PhpOfficePhpSpreadsheetWriterCsv;\n\u00a0\n$spreadsheet = new Spreadsheet();\n$Excel_writer = new Csv($spreadsheet);\n\u00a0\n$spreadsheet->setActiveSheetIndex(0);\n$activeSheet = $spreadsheet->getActiveSheet();\n\u00a0\n$activeSheet->setCellValue('A1', 'Product Name');\n$activeSheet->setCellValue('B1', 'Product SKU');\n$activeSheet->setCellValue('C1', 'Product Price');\n\u00a0\n$query = $db->query(\"SELECT * FROM products ORDER BY id DESC\");\n\u00a0\nif($query->num_rows > 0) {\n\u00a0\u00a0\u00a0\u00a0$i = 2;\n\u00a0\u00a0\u00a0\u00a0while($row = $query->fetch_assoc()) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('A'.$i, $row['product_name']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('B'.$i, $row['product_sku']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('C'.$i, $row['product_price']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$i++;\n\u00a0\u00a0\u00a0\u00a0}\n}\n\u00a0\n$filename = 'products.csv';\n\u00a0\nheader('Content-Type: application\/text-csv');\nheader('Content-Disposition: attachment;filename='. $filename);\nheader('Cache-Control: max-age=0');\n$Excel_writer->save('php:\/\/output');<\/code><\/pre>\n
Saada meil, mille fail on manusena<\/h3>\n
Siiani oleme selles artiklis n\u00e4inud, kuidas fail CSV ja Exceli vormingus alla laadida. M\u00f5ned kasutajad v\u00f5ivad soovida saata eksporditud faili e-kirja manusena. Vaatame, kuidas seda saavutada.<\/p>\n
Esmalt installige PHPMaileri<\/a> teek k\u00e4suga:<\/p>\n
composer require phpmailer\/phpmailer<\/code><\/pre>\n
P\u00e4rast teegi installimist saate meili saatmiseks kasutada mis tahes SMTP-serverit. See on sinu teha. V\u00f5ite kasutada oma hostimise pakutavat SMTP-serverit, AWS SES-i v\u00f5i Gmaili SMTP-serverit. Kui kasutate Gmaili SMTP-serverit, lugege meie artiklit Saada<\/a> meil Gmaili SMTP-serveri abil PHP-skriptist,<\/a> mis selgitab selle jaoks vajalikku seadistamist.<\/p>\n
Kood on varem kirjutatud, laadib faili automaatselt alla. N\u00fc\u00fcd aga salvestame faili allalaaditavaks muutmise asemel kataloogi ja saadame selle seej\u00e4rel manusena. L\u00fchidalt \u00f6eldes on meil faili salvestamiseks allpool kood.<\/p>\n
...\n$filename = 'products.csv';\n\u00a0\nif (!file_exists('files')) {\n\u00a0\u00a0\u00a0\u00a0mkdir('files', 0755);\n}\n$Excel_writer->save('files\/'.$filename);<\/code><\/pre>\n
Meie l\u00f5plik kood manuse saatmiseks e-kirjaga on j\u00e4rgmine:<\/p>\n
<?php\nrequire_once \"vendor\/autoload.php\";\nrequire_once \"db.php\";\n\u00a0\nuse PhpOfficePhpSpreadsheetSpreadsheet;\nuse PhpOfficePhpSpreadsheetWriterCsv;\n\u00a0\n\/\/Import PHPMailer classes into the global namespace\nuse PHPMailerPHPMailerPHPMailer;\nuse PHPMailerPHPMailerException;\n\u00a0\n$spreadsheet = new Spreadsheet();\n$Excel_writer = new Csv($spreadsheet);\n\u00a0\n$spreadsheet->setActiveSheetIndex(0);\n$activeSheet = $spreadsheet->getActiveSheet();\n\u00a0\n$activeSheet->setCellValue('A1', 'Product Name');\n$activeSheet->setCellValue('B1', 'Product SKU');\n$activeSheet->setCellValue('C1', 'Product Price');\n\u00a0\n$query = $db->query(\"SELECT * FROM products ORDER BY id DESC\");\n\u00a0\nif($query->num_rows > 0) {\n\u00a0\u00a0\u00a0\u00a0$i = 2;\n\u00a0\u00a0\u00a0\u00a0while($row = $query->fetch_assoc()) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('A'.$i, $row['product_name']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('B'.$i, $row['product_sku']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$activeSheet->setCellValue('C'.$i, $row['product_price']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$i++;\n\u00a0\u00a0\u00a0\u00a0}\n}\n\u00a0\n$filename = 'products.csv';\n\u00a0\nif (!file_exists('files')) {\n\u00a0\u00a0\u00a0\u00a0mkdir('files', 0755);\n}\n$Excel_writer->save('files\/'.$filename);\n\u00a0\n\/\/ send as an attachment\n$mail = new PHPMailer(true);\ntry {\n\u00a0\u00a0\u00a0\u00a0$mail->isSMTP();\n\u00a0\u00a0\u00a0\u00a0$mail->Host = 'SMTP_HOST';\n\u00a0\u00a0\u00a0\u00a0$mail->SMTPAuth = true;\n\u00a0\u00a0\u00a0\u00a0$mail->Username = 'SMTP_USERNAME';\u00a0\u00a0 \/\/username\n\u00a0\u00a0\u00a0\u00a0$mail->Password = 'SMTP_PASSWORD';\u00a0\u00a0 \/\/password\n\u00a0\u00a0\u00a0\u00a0$mail->SMTPSecure = 'ssl';\n\u00a0\u00a0\u00a0\u00a0$mail->Port = 465;\n\u00a0\n\u00a0\u00a0\u00a0\u00a0$mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME');\n\u00a0\u00a0\u00a0\u00a0$mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME');\n\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0$mail->addAttachment('files\/'.$filename);\n\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0$mail->isHTML(true);\n\u00a0\u00a0\u00a0\u00a0$mail->Subject = 'Our Exported File';\n\u00a0\u00a0\u00a0\u00a0$mail->Body\u00a0\u00a0\u00a0 = 'Our Exported File';\n\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0$mail->send();\n\u00a0\u00a0\u00a0\u00a0echo 'Message has been sent';\n} catch (Exception $e) {\n\u00a0\u00a0\u00a0\u00a0echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo;\n}<\/code><\/pre>\n
Asendage kohat\u00e4ited tegelike v\u00e4\u00e4rtustega ja meil saadetakse manusega saaja e-posti aadressile.<\/p>\n
Loodan, et saate aru, kuidas MySQL-i andmebaasikirjeid Exceli v\u00f5i CSV-faili eksportida ja ka manusena saata. Palun jagage oma m\u00f5tteid ja ettepanekuid allpool olevas kommentaaride jaotises.<\/p>\n
seotud artiklid<\/h4>\n