How to Export excel data from Database in PHP using Spreadsheet

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).

  1. Import Excel File into MySQL Database in PHP using Spreadsheet

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:

// 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.


<!DOCTYPE html>
<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=”" rel=”stylesheet”>
<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>

// 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>
<div class=”form-group col-md-3">
<button type=”submit” name=”export_btn” class=”btn btn-primary”>Emport</button>

<table class=”table table-striped table-hover table-bordered”>
<th>Emp Id</th>
<th>Job Title</th>
<th>Hire Date</th>
<th>Annual Salary</th>
<?php foreach ($result as $row) { ?>
<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>
<?php } ?>
<?php } ?>
<script src=””></script>


// 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’]);


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’);

$_SESSION[‘message’] = “File is not exported”;

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

How to Export excel data from Database in PHP using Spreadsheet

You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions/problems about this tutorial, please comment on the form below.😊



Webs Codex is programming and web development blog

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store