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");