PHP MYSQL
IMPORT EXCEL SPREADSHEET INTO MYSQL WITH PHP
Open command line, navigate to project folder, run composer require phpoffice/phpspreadsheet.
Composer will automatically download the latest version into the vendor/ folder.
DOWNLOAD PHPSPREADSHEET
01
SIMPLE USERS TABLE id | INT(11) PRIMARY KEY name | VARCHAR (255) email | VARCHAR (255)
DUMMY TABLE
02
Jon Doe | jon@doe.com Joe Doe | joe@doe.com Joy Doe | joy@doe.com Job Doe | job@doe.com
DUMMY EXCEL
03
CONNECT TO DATABASE $pdo = new PDO( "mysql:host=HOST;dbname=NAME;". "charset=utf8", USER, PASSWORD );
PHP IMPORT PART 1
04
PHPSPREADSHEET require "vendor/autoload.php"; $reader = new \PhpOffice\ PhpSpreadsheet\Reader\Xlsx();
PHP IMPORT PART 2
05
LOAD EXCEL FILE $ss = $reader->load("FILE.xlsx"); $as = $ss->getActiveSheet();
INSERT SQL STATEMENT $sql = "INSERT INTO `users` (`name`, `email`) VALUES (?, ?)";
PHP IMPORT PART 3
06
LOOP THROUGH WORKSHEET foreach ($as->getRowIterator() as $r) { READ CELLS $data = []; $It = $r->getCellIterator(); $It->setIterateOnlyExistingCells(false); foreach ($It as $cell) { $data[] = $cell->getValue(); } INSERT INTO DATABASE $stmt = $pdo->prepare($sql); $stmt->execute($data); $stmt = null; }