How to Export excel data from Database in PHP using Spreadsheet

4 min readDec 22, 2022

The data export feature is especially useful when you are saving data to a local hard drive for offline use. Exporting data to a file provides a user-friendly way to manage large amounts of data in web applications. Various file formats are available for data export and download as files. Microsoft Excel is a widely used spreadsheet format that organizes and manages data.

Typically, the data export function is used in the data management area of web applications. Excel is the best format for exporting data to files and you can easily export data to Excel using PHP. In this tutorial, we will show you how to export Excel data to PHP.

With an example PHP script you can integrate the exported data into Excel functionality. With one click, users can export data from MySQL database to Excel and download it in MS Excel file format (.xls/.xlsx and csv also).

Created Database Configuration File

In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your root directory and put bellow code:

<?php
// Database configuration
$hostname = “localhost”;
$username = “root”;
$password = “”;
$dbname = “webscodex”;

// Create database connection
$con = new mysqli($hostname, $username, $password, $dbname);

// Check connection
if ($con->connect_error) {
die(“Connection failed: “ . $con->connect_error);
}
?>

Creating View to Data

Then create another PHP file called index.php and this PHP file will be the first page to be loaded in our browser when we access view the Project folder of our web directory. And this file will load the employee table list if the employee table is not empty, because this page allows users to export CSV/Excel files from database. also export various file type export to excel or csv.

index.php

<!DOCTYPE html>
<html>
<head>
<meta charset=”utf-8">
<meta name=”viewport” content=”width=device-width, initial-scale=1">
<title>How to Export excel data from Database in PHP using Spreadsheet</title>
<link href=”https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel=”stylesheet”>
</head>
<body>
<div class=”container text-center” style=”margin-top:40px; margin-bottom: 40px;”>
<h2>How to Export excel data from Database in PHP using Spreadsheet</h2><br><hr>
</div>

<?php
// Include database

include “config.php”;

$query = “SELECT * FROM employee”;
$result = $con->query($query);
if ($result->num_rows > 0) {
?>
<div class=”container”>
<form action=”export_data.php” method=”POST”>
<div class=”row mb-3">
<label class=”col-sm-2 col-form-label offset-2">Select File Extension</label>
<div class=”form-group col-md-5">
<select class=”form-control” name=”file_type”>
<option value=”xls”>XLS</option>
<option value=”xlsx”>XLSL</option>
<option value=”csv”>CSV</option>
</select>
</div>
<div class=”form-group col-md-3">
<button type=”submit” name=”export_btn” class=”btn btn-primary”>Emport</button>
</div>
</div>
</form><br>

<table class=”table table-striped table-hover table-bordered”>
<thead>
<tr>
<th>Emp Id</th>
<th>Name</th>
<th>Job Title</th>
<th>Department</th>
<th>Gender</th>
<th>Age</th>
<th>Hire Date</th>
<th>Annual Salary</th>
<th>Bonus</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php foreach ($result as $row) { ?>
<tr>
<td><?php echo $row[‘emp_id’]; ?></td>
<td><?php echo $row[‘full_name’]; ?></td>
<td><?php echo $row[‘job_title’]; ?></td>
<td><?php echo $row[‘department’]; ?></td>
<td><?php echo $row[‘gender’]; ?></td>
<td><?php echo $row[‘age’]; ?></td>
<td><?php echo $row[‘hire_date’]; ?></td>
<td><?php echo $row[‘annual_salary’]; ?></td>
<td><?php echo $row[‘bonus’]; ?></td>
<td><?php echo $row[‘country’]; ?></td>
<td><?php echo $row[‘city’]; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<?php } ?>
<script src=”https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js”></script>
</body>
</html>

export_data.php

<?php
// include database configuration

include “config.php”;

require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

if (isset($_POST[‘export_btn’])) {

$fileType = $_POST[‘file_type’];

$query = ‘SELECT * FROM employee’;

$result = $con->query($query);

$fileName = “employee_sheet”;
if ($result->num_rows > 0) {

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue(‘A1’, ‘Emp Id’);
$sheet->setCellValue(‘B1’, ‘Name’);
$sheet->setCellValue(‘C1’, ‘Job Title’);
$sheet->setCellValue(‘D1’, ‘Department’);
$sheet->setCellValue(‘E1’, ‘Gender’);
$sheet->setCellValue(‘F1’, ‘Age’);
$sheet->setCellValue(‘G1’, ‘Hire date’);
$sheet->setCellValue(‘H1’, ‘Annual Salary’);
$sheet->setCellValue(‘I1’, ‘Bonus’);
$sheet->setCellValue(‘J1’, ‘Country’);
$sheet->setCellValue(‘K1’, ‘City’);

$rowCount = 2;
foreach ($result as $row) {
$sheet->setCellValue(‘A’.$rowCount, $row[‘emp_id’]);
$sheet->setCellValue(‘B’.$rowCount, $row[‘full_name’]);
$sheet->setCellValue(‘C’.$rowCount, $row[‘job_title’]);
$sheet->setCellValue(‘D’.$rowCount, $row[‘department’]);
$sheet->setCellValue(‘E’.$rowCount, $row[‘gender’]);
$sheet->setCellValue(‘F’.$rowCount, $row[‘age’]);
$sheet->setCellValue(‘G’.$rowCount, $row[‘hire_date’]);
$sheet->setCellValue(‘H’.$rowCount, $row[‘annual_salary’]);
$sheet->setCellValue(‘I’.$rowCount, $row[‘bonus’]);
$sheet->setCellValue(‘J’.$rowCount, $row[‘country’]);
$sheet->setCellValue(‘K’.$rowCount, $row[‘city’]);

$rowCount++;
}

if ($fileType == ‘xls’) {

$writer = new Xls($spreadsheet);
$empFileName = $fileName.’.xls’;

}else if ($fileType == ‘xlsx’) {

$writer = new Xlsx($spreadsheet);
$empFileName = $fileName.’.xlsx’;

}else if ($fileType == ‘csv’) {

$writer = new Csv($spreadsheet);
$empFileName = $fileName.’.csv’;
}

header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”’.urlencode($empFileName).’”’);
header(‘Cache-Control: max-age=0’);
$writer->save(‘php://output’);

}else{
$_SESSION[‘message’] = “File is not exported”;
header(“Location:index.php”);
exit();
}
}
?>

If you want to more readable or reliable understand code please on webscodex.com