Exporting MySQL Data into Excel is a very useful method to keep the data list in a relevant area for offline usage. Exporting data method performs your web application as most user-friendly and improves the user to manage list data in offline. Excel is the greatest construction to Export data in a .xls file and you can easily export database table data into Excel using this method. This post explains to you how to arrange and export our data into Excel file format.
Step 1: Make a Connection with database using MySQLi
Create a database with a relevant table and make a MySQLi connection using MySQLi connection methods.
|
1 2 3 4 5 6 7 |
CREATE TABLE `li_ajax_post_load` ( `post_id` int(11) NOT NULL, `post_title` varchar(250) NOT NULL, `post_desc` text NOT NULL, `status` int(11) NOT NULL, primary key(post_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
And connection details,
|
1 2 3 4 5 6 7 8 9 10 |
<?php //db details $db_host = 'localhost'; $db_user = 'root'; $db_pass = ''; $db_name = 'li_demo'; //connect and select db $con = mysqli_connect($db_host, $db_user, $db_pass, $db_name); ?> |
Step 2: Prepare Post list view page
Here we need to define and execute the MySQLi query with MySQLi connection object and display the details with a page. Here additively we need to add a button for exporting listed post details into excel.
|
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 |
<?php require_once('./include/mysqli_connect.php'); ?> <a class="btn btn-warning" href="export_excel.php" target="_new" ><i class="fa fa-download"></i> Export to Excel</a> <table width="100%" border="1"> <tr> <th>SNo.</th> <th>Post Name</th> <th>Post Description</th> </tr> <?php //get rows query $query = mysqli_query($con, "SELECT * FROM li_ajax_post_load ORDER BY post_id DESC"); //number of rows $rowCount = mysqli_num_rows($query); $sno = 1; if($rowCount > 0){ while($row = mysqli_fetch_assoc($query)){ ?> <tr> <td align="center"><?php echo $sno; ?>)</td> <td><?php echo ucfirst($row["post_title"]); ?></td> <td><?php echo ucfirst($row["post_desc"]); ?></td> </tr> <?php $sno++; } } ?> </table> |
Step 3: Header files including
|
1 2 3 4 5 |
// headers for exporting excel header("Content-Disposition: attachment; filename='file_name_goes_here.xls'"); header("Content-Type: application/vnd.ms-excel"); |
Step 4: Make a result data array
Define a dataset array object for exporting post details with a related key or column name.
|
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 |
function dataFilter(&$str_val) { $str_val = preg_replace("/\t/", "\\t", $str_val); $str_val = preg_replace("/\r?\n/", "\\n", $str_val); if(strstr($str_val, '"')) $str_val = '"' . str_replace('"', '""', $str_val) . '"'; } $post_list = array(); //get rows query $query = mysqli_query($con, "SELECT * FROM li_ajax_post_load ORDER BY post_id DESC limit 0,5"); //number of rows $rowCount = mysqli_num_rows($query); $sno = 1; if($rowCount > 0){ while($row = mysqli_fetch_assoc($query)){ $post_list[] = array( "Sno"=>$sno, "post_title"=>$row["post_title"], "post_desc"=>$row["post_desc"] ); $sno++; } } $title_flag = false; foreach($post_list as $post) { if(!$title_flag) { // Showing column names echo implode("\t", array_keys($post)) . "\n"; $title_flag = true; } // data filtering array_walk($post, 'dataFilter'); echo implode("\t", array_values($post)) . "\n"; } |
