PHP MYSQL
HOW TO EXPORT MYSQL TO EXCEL IN PHP
DOWNLOAD PHPSPREADSHEET
01
Open terminal, navigate to project folder, run composer require phpoffice/phpspreadsheet.
Composer will download the latest version into vendor folder.
id | int(11) PRIMARY name | varchar(255) INDEXED email | varchar(255) UNIQUE
DUMMY DATABASE
02
CONNECT TO DATABASE $pdo = new PDO("mysql:host=HOST; dbname=NAME;charset=utf8", USER, PASSWORD);
EXPORT MYSQL TO EXCEL (A)
03
LOAD PHPSPREADSHEET require "vendor/autoload.php"; use PhpOffice\PhpSpreadsheet\ Spreadsheet; use PhpOffice\PhpSpreadsheet\ Writer\Xlsx;
EXPORT MYSQL TO EXCEL (B)
04
CREATE SPREADSHEET $ss = new Spreadsheet(); $as = $ss->getActiveSheet(); $as->setTitle("Users");
FETCH USERS $stmt = $pdo->prepare ( "SELECT * FROM `users`"); $stmt->execute();
WRITE TO SPREADSHEET $i = 1; while ($r = $stmt->fetch()) { $as->setCellValue("A$i", $r['id']); $as->setCellValue("B$i", $r['name']); $as->setCellValue("C$i", $r['email']); $i++; }
EXPORT MYSQL TO EXCEL (C)
05
SAVE FILE $writer = new Xlsx($ss); $writer->save("test.xlsx");