{"id":24960,"date":"2021-05-21T16:35:00","date_gmt":"2021-05-21T13:35:00","guid":{"rendered":"https:\/\/themewp.inform.click\/?p=24960"},"modified":"2021-10-17T18:57:51","modified_gmt":"2021-10-17T15:57:51","slug":"kuidas-lugeda-csv-ja-exceli-faile-php-s-kasutades-phpspreadsheet","status":"publish","type":"post","link":"https:\/\/themewp.inform.click\/et\/kuidas-lugeda-csv-ja-exceli-faile-php-s-kasutades-phpspreadsheet\/","title":{"rendered":"Kuidas lugeda CSV- ja Exceli faile PHP-s, kasutades PhpSpreadsheet"},"content":{"rendered":"

Veebirakenduste kallal t\u00f6\u00f6tades satume m\u00f5nikord olukorrani, kus peame andmebaasi importima CSV- v\u00f5i Exceli-faili. See on eelistatud viis andmebaasi suurte andmete importimiseks, selle asemel et neid \u00fckshaaval sisestada.<\/p>\n

PhpSpreadsheet<\/a> on teek, mis aitab teil CSV- v\u00f5i Exceli faili lugeda. Raamatukogu pakub tuge erinevat t\u00fc\u00fcpi failivormingute lugemiseks ja kirjutamiseks. Allpool on ekraanipilt toetatud failivormingutest.<\/p>\n

\"Kuidas<\/a><\/p>\n

Enne selle teegiga tutvumist kasutasin<\/a> CSV-faili lugemiseks fgetcsv-<\/a> meetodit. Ja Exceli failide puhul pidin k\u00f5igepealt Exceli teisendama CSV-vormingusse ja seej\u00e4rel fgetcsv()<\/code>funktsiooni abil l\u00e4bi lugema .<\/p>\n

PhpSpreadsheet lihtsustab seda \u00fclesannet arendajatele. PhpSpreadsheet’i abil on CSV- ja Exceli-failide andmeid lihtsam k\u00e4sitleda. Lisaks pole selle lugemiseks vaja Exceli CSV-vormingusse teisendada, teek loeb Exceli faili otse.<\/p>\n

Sellest hoolimata vaatame, kuidas lugeda CSV- ja Exceli faile PhpSpreadsheetiga.<\/p>\n

Paigaldamine<\/h3>\n

Selle teegi installimiseks soovitan kasutada heliloojat<\/a>. Avage terminal oma projekti juurkataloogis ja k\u00e4ivitage k\u00e4sk:<\/p>\n

composer require phpoffice\/phpspreadsheet<\/code><\/pre>\n
\u00d5petuse j\u00e4rgmises osas loen CSV \/ Exceli faile ja sisestan nende andmed andmebaasi. Selle jaoks l\u00f5in n\u00e4ivfaili, mis sisaldab j\u00e4rgmisi veerge ja andmeid:<\/p>\n
\"Kuidas<\/a><\/p>\n
Nende \u00fcksikasjade salvestamiseks l\u00f5in andmebaasi ka tabeli ‘kasutajad’.<\/p>\n
CREATE TABLE `users` (\u00a0`id` int(11) NOT NULL AUTO_INCREMENT,\n\u00a0`name` varchar(255) DEFAULT NULL,\n\u00a0`email` varchar(255) DEFAULT NULL,\n\u00a0PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;<\/code><\/pre>\n
\u00d5petuse huvides kasutan \u00fclaltoodud n\u00e4iv andmeid. Kasutaja peaks muutma tabeli struktuuri vastavalt oma andmekogumile.<\/p>\n
Looge db.php<\/code>ja lisage sinna andmebaasi\u00fchenduse kood.<\/p>\n
db.php<\/strong><\/p>\n
<?php\n$db_host = 'DB_HOST';\n$db_username = 'DB_USERNAME';\n$db_password = 'DB_PASSWORD';\n$db_name = 'DB_NAME';\n\u00a0\u00a0\n$db = new mysqli($db_host, $db_username, $db_password, $db_name);\n\u00a0\u00a0\nif($db->connect_error){\n\u00a0\u00a0\u00a0\u00a0die(\"Unable to connect database: \". $db->connect_error);\n}<\/code><\/pre>\n
Asendage kohat\u00e4ited tegelike v\u00e4\u00e4rtustega.<\/p>\n
Lugege CSP \/ Exceli faili PHP-s<\/h3>\n
Teeme tegeliku kodeerimise. K\u00f5igepealt looge vorm, millel on faili sisestamise ja esitamise nupp. Kasutaja laadib \u00fcles oma CSV \/ Exceli faili, vajuta nuppu Esita. Esitamisel tuleks \u00fcleslaaditud faili kanded lisada tabelisse \u201eKasutajad".<\/p>\n
vorm.php<\/strong><\/p>\n
<form method=\"post\" enctype=\"multipart\/form-data\">\n\u00a0\u00a0\u00a0\u00a0<div class=\"form-group\">\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<label for=\"exampleInputFile\">File Upload<\/label>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<input type=\"file\" name=\"file\" class=\"form-control\" id=\"exampleInputFile\">\n\u00a0\u00a0\u00a0\u00a0<\/div>\n\u00a0\u00a0\u00a0\u00a0<button type=\"submit\" name=\"submit\" class=\"btn btn-primary\">Submit<\/button>\n<\/form><\/code><\/pre>\n
J\u00e4rgmisena on esitamisel t\u00e4idetav kood j\u00e4rgmine.<\/p>\n
<?php\nrequire_once 'vendor\/autoload.php';\nrequire_once 'db.php';\n\u00a0\u00a0\nuse PhpOfficePhpSpreadsheetSpreadsheet;\nuse PhpOfficePhpSpreadsheetReaderCsv;\nuse PhpOfficePhpSpreadsheetReaderXlsx;\n\u00a0\u00a0\nif (isset($_POST['submit'])) {\n\u00a0\n\u00a0\u00a0\u00a0\u00a0$file_mimes = array('text\/x-comma-separated-values', 'text\/comma-separated-values', 'application\/octet-stream', 'application\/vnd.ms-excel', 'application\/x-csv', 'text\/x-csv', 'text\/csv', 'application\/csv', 'application\/excel', 'application\/vnd.msexcel', 'text\/plain', 'application\/vnd.openxmlformats-officedocument.spreadsheetml.sheet');\n\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$arr_file = explode('.', $_FILES['file']['name']);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$extension = end($arr_file);\n\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if('csv' == $extension) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$reader = new PhpOfficePhpSpreadsheetReaderCsv();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0} else {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$reader = new PhpOfficePhpSpreadsheetReaderXlsx();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$spreadsheet = $reader->load($_FILES['file']['tmp_name']);\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$sheetData = $spreadsheet->getActiveSheet()->toArray();\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if (!empty($sheetData)) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0for ($i=1; $i<count($sheetData); $i++) {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$name = $sheetData[$i][1];\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$email = $sheetData[$i][2];\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$db->query(\"INSERT INTO USERS(name, email) VALUES('$name', '$email')\");\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0}\n}\n?><\/code><\/pre>\n
\u00dclaltoodud koodis uurin v\u00e4lja faili t\u00fc\u00fcbi ja selle alused, mida loen faili kasutades raamatukogu pakutavaid funktsioone. Te saate lugeda rohkem infot selle kohta ametlikud dokumendid siin<\/a>.<\/p>\n
Loodan, et saate aru, kuidas lugeda CSV- ja Exceli faile PhpSpreadsheetiga. Tahaksin kuulda teie m\u00f5tteid ja ettepanekuid allpool olevas kommentaaride jaotises.<\/p>\n
seotud artiklid<\/h4>\n