Post
Ask Question

Export To Excel In PHP With MySQL

Hello everyone. Today in this article, I will explain to you about exporting the reports in Excel format using PHP and MySQL. So, I will create one simple PHP page that will display data from the My SQL database in tabular format and then we put one button. By clicking this button, we can export the Excel file which is automatically downloaded to your machine.

  1. Make Table in My SQL for storing the user. Give it the name “tbl_User”. This table contains UserName, Password etc. For creating a table, run the below script.
    1. CREATE TABLE IF NOT EXISTS `tbl_user` (  
    2. `ur_Id` int(11) NOT NULL AUTO_INCREMENT,  
    3. `ur_username` varchar(50) NOT NULL,  
    4. `ur_password` varchar(50) NOT NULL,  
    5. `ur_status` int(11) NOT NULL,  
    6. PRIMARY KEY (`ur_Id`)  
    7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  
    By running the above script, it will create a table like below. Here, table contains Id, Username, Password, Status etc… columns

    table

  2. Now, insert some temporary data to this table. So, for inserting data in the  temporary table, I run the below SQL script.
    1. INSERT INTO `tbl_user` (`ur_Id`, `ur_username`, `ur_password`, `ur_status`) VALUES  
    2. (1, '[email protected]', 'nirav', 1),  
    3. (2, '[email protected]', 'kapil', 1),  
    4. (3, '[email protected]', 'arvind', 1);  
    After running the below script you can show  a table something like below.

    table

  3. Now, first we make one simple PHP page that will display the above table data in tabular format with one “Export link”.
    1. <html>  
    2.   
    3. <head>  
    4.     <title>User Detail Report</title>  
    5. </head>  
    6.   
    7. <body> </body>  
    8.   
    9. </html>  
    Save the above file with “UserReport.php”.

    2. Now, we will add one table for displaying the data. Our table contains 3 columns, “Sr No”, “User Name” ,” Password”.
    1. <table border="1">  
    2.     <tr>  
    3.         <th>Sr NO.</th>  
    4.         <th width="120">User Name</th>  
    5.         <th>Password</th>  
    6.     </tr>  
    7. </table>  
    Here, we take table border as “1” so user can display, and we create 3 columns for the table header rows.

  4. Now, we have to fetch the SQL table value for displaying table data rows. So, first we make MySQL Server database connection. Now, we make MySQL server connection from the PHP file. So, pass server name, user name, password, database name etc.
    1. <?php  
    2.    $conn = new mysqli('localhost', 'root', '');   
    3.    mysqli_select_db($conn, 'EmployeeDB');   
    4. ?>  
    Here, we pass server name as ‘localhost’, username as ‘root’, password to be blank(’’), and we pass database name as ‘EmployeeDB’.

  5. Now, we make Query which will select the SrNo, Username and password from the database and pass this with MySQL connection object. As show below:
    1. $sql = mysqli_query($conn,"SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`");  
    Above, Query fetches the records from the MySQL database and stores it in “$sql” variable.

  6. Here, we have record set. So, now we separate it in row-wise data. Repeat the while loop and fetch each row and show in tabular format, as show below:
    1. while($data = mysqli_fetch_row($sql))  
    2. {  
    3.     echo '  
    4.     <tr>  
    5.     <td>'.$data[0].'</td>  
    6.     <td>'.$data[1].'</td>  
    7.     <td>'.$data[2].'</td>  
    8.     </tr>  
    9.     ';  
    10. }  
    Above code will display the data row for the table. So, now complete table with header row and data row is displayed.

  7. Now, we put one link; that is, export the data to Excel file.
    1. <a href="UserReport_Export.php"> Export To Excel </a> 
  8. Now, complete code for the “UserReport.php” is shown below:
    1. <html>  
    2.   
    3. <head>  
    4.     <title>User Detail Report</title>  
    5. </head>  
    6.   
    7. <body>  
    8.     <table border="1">  
    9.         <tr>  
    10.             <th>Sr NO.</th>  
    11.             <th width="120">User Name</th>  
    12.             <th>Password</th>  
    13.         </tr>  
    14.         <?php  
    15. $conn = new mysqli('localhost', 'root', '');   
    16. mysqli_select_db($conn, 'EmployeeDB');   
    17.   
    18. $sql = mysqli_query($conn,"SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`");  
    19.   
    20.   
    21. while($data = mysqli_fetch_row($sql)){  
    22. echo '  
    23. <tr>  
    24. <td>'.$data[0].'</td>  
    25. <td>'.$data[1].'</td>  
    26. <td>'.$data[2].'</td>  
    27. </tr>  
    28. ';  
    29. }  
    30. ?>  
    31.     </table> <a href="UserReport_Export.php"> Export To Excel </a> </body>  
    32.   
    33. </html>  
    Run the above PHP page http://localhost:8081/UserReport.php, It displays like this,

    output

    Here, displaying the table data in PHP page is now complete. Now, we have to create file for exporting this table data to excel file in PHP.

  9. Create another PHP file for exporting data to excel and give its name “UserReport_Export.php”. And, write the below code :
    1. <?php  
    2. ?>  
  10. First, I create the My SQL connection for fetching the data from the database, as shown in above steps.
    1. $conn = new mysqli('localhost', 'root', '');   
    2. mysqli_select_db($conn, 'EmployeeDB');   
    3.   
    4. $setSql = "SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`";  
    5. $setRec = mysqli_query($conn,$setSql);  
    Here, in variable $setRec, record set is stored. So, we loop the record set and separate the each row of record by writing while loop to fetch each row.
    1. while($rec = mysqli_fetch_row($setRec))   
    2. {  
    3. }  
  11. Now, we have to get the column wise data from the row. So, we write “for each” loop in while loop.
    1. foreach($rec as $value)   
    2. {  
    3. }  
  12. Now, we have to add one tab (“\t”) value between each and every column values and store this full string to any new variable. So, we write some function in between for each loop, as show below,
    1. $rowData = '';  
    2. foreach($rec as $value)   
    3. {  
    4.    $value = '"' . $value . '"' . "\t";  
    5.    $rowData .= $value;  
    6. }  
  13. Now, our columns are ready. We have to separate the rows now. So, we have to add new line character (“\n”) between each new row:
    1. $setData='';  
    2.   
    3. while($rec = mysqli_fetch_row($setRec))   
    4. {  
    5.    $rowData = '';  
    6. foreach($rec as $value)   
    7. {  
    8.    $value = '"' . $value . '"' . "\t";  
    9.    $rowData .= $value;  
    10. }  
    11.    $setData .= trim($rowData)."\n";  
    12. }  
  14. Our table data row and columns are ready now. We have to define a new variable for header row, as shown in below code,
    1. $columnHeader ='';  
    2. $columnHeader = "Sr NO"."\t"."User Name"."\t"."Password"."\t";  
  15. Here, when user clicks on link, we have to download the excel file directly, without showing the data. Just add the below code.

    header("Content-type: application/octet-stream");

  16. Now, we have to set the filename for downloading excel file.

    header("Content-Disposition: attachment; filename=User_Detail_Reoprt.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

  17. Now, finally we print all the table row data and header data to the excel file.

    echo ucwords($columnHeader)."\n".$setData."\n";

    Complete code for “UserReport_Export.php”,
    1. <?php  
    2.   
    3. $conn = new mysqli('localhost', 'root', '');  
    4. mysqli_select_db($conn, 'EmployeeDB');  
    5.   
    6. $setSql = "SELECT `ur_Id`,`ur_username`,`ur_password` FROM `tbl_user`";  
    7. $setRec = mysqli_query($conn, $setSql);  
    8.   
    9. $columnHeader = '';  
    10. $columnHeader = "Sr NO" . "\t" . "User Name" . "\t" . "Password" . "\t";  
    11.   
    12. $setData = '';  
    13.   
    14. while ($rec = mysqli_fetch_row($setRec)) {  
    15.     $rowData = '';  
    16.     foreach ($rec as $value) {  
    17.         $value = '"' . $value . '"' . "\t";  
    18.         $rowData .= $value;  
    19.     }  
    20.     $setData .= trim($rowData) . "\n";  
    21. }  
    22.   
    23.   
    24. header("Content-type: application/octet-stream");  
    25. header("Content-Disposition: attachment; filename=User_Detail_Reoprt.xls");  
    26. header("Pragma: no-cache");  
    27. header("Expires: 0");  
    28.   
    29. echo ucwords($columnHeader) . "\n" . $setData . "\n";  
    30.   
    31. ?>  
  18. Now, run the “UserReport.php” in your WAMP or any other related server. It will show like the below image and then, click on “Export to Excel” link . It will download the excel file, as show in below.

    Export to Excel

  19. Now, open the Excel file and show the result.

    Excel
         Thank you for reading my article , if you have any problem than fill free to ask in comment box.


Similar Articles