24 Oct, 2017

Export data from DB table to Excel Sheet using PHPExcel library

24 Oct, 2017

Dear Reader, let us know about “How to Export data from DB table to Excel Sheet using PHPExcel library“.

For this download PHPExcel Library from here.
Extract this Library and place it into your server /var/www/html/WebPreparations/Import_Export/PHPExcel.
Now, we should have a table ‘excel_data_new’ of database ‘my_db’, having some dummy data.

mysql> show databases;
+——————————+
| Database                     |
+——————————+
| information_schema  |
| my_db                          |
+——————————+
2 rows in set (0.00 sec)

mysql> use my_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+————————+
| Tables_in_my_db|
+————————-+
| emp                       |
| excel_data_new   |
+————————-+
2 rows in set (0.00 sec)

mysql> select * from excel_data_new;
+——+———+———————-+—————-+————-+
| id   | name  | email                  | phone         | city       |
+——+———+———————-+—————–+————+
| 1    | ck       | ck@gmail.com  | 2147483647 | Noida |
| 2    | rk       | rk@gmail.com   | 2147483647 | Noida |
| 3    | rk1     | rk1@gmail.com | 2147483647 | Patna |
+——+———+———————+——————+————+
3 rows in set (0.00 sec)

1. Create a file ‘/var/www/html/WebPreparations/Import_Export/db_connect.php’

2. Now place a download button in ‘/var/www/html/WebPreparations/Import_Export/exp_excel.php’

3. Now your final file ‘/var/www/html/WebPreparations/Import_Export/download.php’ having main code for exporting data to excel sheet with some design formatting.

Finally, you will get the result in this screenshot attached.