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.

«
»

6 Comments:

  • Amit Shee August 28, 2018

    Thanks guys for sharing this helpful article with us. it’s work fine. Really great.

    Reply

    • Webpreparations Team August 28, 2018

      Hello Dear Reader
      We are always ready to share our knowledge and experience with you 🙂

      Thank you so much for appreciation 🙂 🙂

      Reply

  • Alok Singh October 02, 2018

    Thanks for this useful article. You saved my time. Thank you….

    Reply

    • Webpreparations Team October 04, 2018

      Thank you 🙂

      Reply

  • ASHOK June 22, 2019

    THANKS BUT HOW TO PRINT HEADER NAMES

    Reply

    • Webpreparations Team June 24, 2019

      Thank you dear reader for your comments. As we have discussed about this, i have suggested you to please read the whole code, with code copy and pasting. As
      ” $objPHPExcel->getActiveSheet()
      ->getCell(‘A1’)
      ->setValue(“Student’s Data”); ”
      This is written for Header Data or Names, so you need to change your header names with this and also check with exact name of the object of PHP Excel Library, as written here “$objPHPExcel”. So that it can call all the necessary things needed for this Excel Export code running properly with all formatting.

      Reply

Leave a comment:

Your email address will not be published. Required fields are marked *