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’
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php $hostname = 'localhost'; $username = 'root'; $password = '123456'; $db_name = 'my_db'; $conn = new mysqli($hostname, $username, $password, $db_name); if($conn->connect_error) { die('Connection Failed '.$conn->connect_error); } else { //echo 'Connected Successfully<br>'; } ?> |
2. Now place a download button in ‘/var/www/html/WebPreparations/Import_Export/exp_excel.php’
1 2 3 4 5 6 7 8 9 10 |
<html> <head> <title>Export In Excel</title> </head> <body> <form action="download.php"> <input type="submit" value="Download" /> </form> </body> </html> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
<?php require_once 'PHPExcel/Classes/PHPExcel.php'; include 'db_connect.php'; $result = array(); $sql = "SELECT * FROM excel_data_new"; $result_sql = mysqli_query($conn, $sql); while ($rows = mysqli_fetch_assoc($result_sql)) { $result[] = $rows; } //echo "<pre>"; //print_r($result); $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel->setActiveSheetIndex(0); // Merge Columns for showing 'Student's Data' start--------------- $objPHPExcel->setActiveSheetIndex(0) ->mergeCells('A1:E1'); $objPHPExcel->getActiveSheet() ->getCell('A1') ->setValue("Student's Data"); $objPHPExcel->getActiveSheet() ->getStyle('A1') ->getAlignment() ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet() ->getStyle('A1:E1') ->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor() ->setARGB('FF3399'); //FF3399 33F0FF F28A8C // Merge Columns for showing 'Student's Data' close---------------> // Initialise the Excel row number $rowCount1 = 2; $column1 = 'A'; $sql1 = "SHOW COLUMNS FROM excel_data_new"; $result1 = mysqli_query($conn,$sql1); while($row1 = mysqli_fetch_array($result1)) { //echo $row1['Field']."<br>"; $objPHPExcel->getActiveSheet()->setCellValue($column1.$rowCount1, $row1['Field']); $styleArray = array( 'font' => array( 'bold' => true, 'color' => array('rgb' => '3333FF'), 'size' => 11, 'name' => 'Verdana' ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '33F0FF')) ); $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->applyFromArray($styleArray); $column1++; } //end of adding column names //start foreach loop to get data $rowCount = 3; foreach($result as $key => $values) { //start of printing column names as names of MySQL fields $column = 'A'; foreach($values as $value) { //echo $value.'<br>'; //echo $column.$rowCount.'<br>'; $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value); $column++; } $rowCount++; } // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="results.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); ?> |
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.
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 🙂 🙂
Alok Singh October 02, 2018
Thanks for this useful article. You saved my time. Thank you….
Webpreparations Team October 04, 2018
Thank you 🙂
ASHOK June 22, 2019
THANKS BUT HOW TO PRINT HEADER NAMES
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.