All TalkersCode Topics

Follow TalkersCode On Social Media

devloprr.com - A Social Media Network for developers Join Now ➔

PHP Export To Excel With Formatting

Last Updated : Jul 1, 2023

PHP Export To Excel With Formatting

In this tutorial we will show you the solution of PHP export to excel with formatting, here we are going to export database data to excel so first we need database connection then we have retrieve all data from server then we prints on browser for user verification how many data are present in database table.

Then when user clicks on export to excel button our database data are exported to excel and downloaded with formatting.

Step By Step Guide On PHP Export To Excel With Formatting :-

As we seen earlier when we need to export data we have header() method in php. header() function with two types of responses, first one is within header parameter of ‘content-type’ used for indicate the media type of the resource.

The media type is a string sent along with the file indicating the format of the file.

In response it tells about the type of returned content, to the client. Next is ‘content-disposition’ it takes values enclosed in the main body, forms or multiple parts of the content.

It has option to make the data available locally or display.

Before header() method definition we need database connection then we have to retrieve all datas then only we can locally save using header method and downloads.

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dbase";
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$sql_query = "SELECT ID,Name,Country FROM info LIMIT 10";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
$developer_records = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
 $developer_records[] = $rows;
}
if(isset($_POST["export_data"])) {
 $filename = "export_".date('Ymd') . ".xls";
 header("Content-Type: application/vnd.ms-excel");
 header("Content-Disposition: attachment; filename=\"$filename\"");
 $show_coloumn = false;
 if(!empty($developer_records)) {
   foreach($developer_records as $record) {
  if(!$show_coloumn) {
    // display field/column names in first row
    echo implode("\t", array_keys($record)) . "\n";
    $show_coloumn = true;
  }
  echo implode("\t", array_values($record)) . "\n";
   }
 }
 exit;
}
?>
<html>
<title>Demo</title>
<body>
<h2>Export Data to Excel</h2>
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
<button type="submit" id="export_data" name='export_data' value="Export to excel">Export to excel</button>
</form>
<table border=1 style="width:20%;height:15%;text-align:center;border-collapse: collapse;">
<tr>
<th>ID</th>
<th>Name</th>
<th>Country</th>
</tr>
<tbody>
<?php foreach($developer_records as $developer) { ?>
<tr>
<td><?php echo $developer ['ID']; ?></td>
<td><?php echo $developer ['Name']; ?></td>
<td><?php echo $developer ['Country']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</body>
</html>
  1. A php script can be placed anywhere in the document. A php script starts with <?php and end with ?>.
  2. The default file extension for php files is “.php” and php statements end with ‘;’ semicolon.
  3. First we have to collects database connection details and stored to variables ‘$servername,$username,$password,$dbname’ for execute connection with server then connected using mysqli_connect() method stored result to variable ‘$conn’. Our database name is ‘dbase’.
  4. We need to execute select query for collects database table details from server and stored to variable ‘$sql_query’. In our table name ‘info’ we have three columns ‘ID,Name,Country’ with number of values.
  5. Then executed query by mysqli_query() method and stored to variable ‘$resultset’. We created empty array variable ‘developer_records’ for store all database table data.
  6. Within while() loop fetching data from table row by row and stored to array variable ‘developer_records’.
  7. In html code we created one button for export data and printed all fetched database table data to html table using foreach loop with array variable ‘developer_records’ on webpage. Those array variables denotes ‘$developer ['ID']= database id values, $developer ['Name']= database id values, $developer ['Country']= database country values’.
  8. Then all values printed by echo and we can use ‘print’ also for printing strings or values to the webpage browser.
  9. When user clicks on ‘Export to Excel’ button of html element it loads export definitions in php. Here we giving name to our excel file as ‘export_with current date’ and stored to variable ‘$filename’. In php (.) used for appending process we used to append excel name with date format and (.xls) denotes excel type file.
  10. As we know header() method defined with excel format, filename and variable ‘$showcolumn’ sets to ‘false’.
  11. If() condition checks database table value empty or not, if it’s not empty using foreach() loop each values printed on excel cells by implode() and array_keys(), array_values() methods.
  12. The variable ‘$showcolumn’ till false all header columns prints on excel then set to ‘true’ for exit from that condition then prints all data to excel.

Conclusion :-

In conclusion we are able to know how to export excel with formatting using php.

When work with php we need to create and process php files at server location and then we need to start the server before execute the program.

When executing this program on browser page immediately it loads html code to displays all database table data with export option.

When user clicks on export it downloads exported data on excel.

I hope this tutorial on PHP export to excel with formatting helps you and the steps and method mentioned above are easy to follow and implement.