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.

SIMPLE USERS TABLE id         | INT(11) PRIMARY KEY name  | VARCHAR (255) email  | VARCHAR (255)

Jon Doe | jon@doe.com Joe Doe | joe@doe.com Joy Doe | joy@doe.com Job Doe | job@doe.com

CONNECT TO DATABASE $pdo = new PDO(   "mysql:host=HOST;dbname=NAME;".   "charset=utf8", USER, PASSWORD );

PHPSPREADSHEET require "vendor/autoload.php"; $reader = new \PhpOffice\ PhpSpreadsheet\Reader\Xlsx();

LOAD EXCEL FILE $ss = $reader->load("FILE.xlsx"); $as = $ss->getActiveSheet();

INSERT SQL STATEMENT $sql = "INSERT INTO `users` (`name`, `email`) VALUES (?, ?)";

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