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).
File Structure
Take a look at the example Excel export file structure shown below. The provisioning folder contains the PHPSpreadsheet library and its dependencies. I have provided a sample import template with these files to experiment with the export process.
See also:
- Import Excel File into MySQL Database using PHP
- How to Export excel data from Database in PHP using Spreadsheet
Create Database and Table by SQL query
For example, we need to create database and table, so here I created webscodex database and employee table. table holds the records which will be exported to excel You can simply create employee table as following SQL query.
-- Database: `webscodex` -- -- Table structure for table `employee` -- CREATE TABLE `employee` ( `id` int(11) NOT NULL, `emp_id` varchar(100) NOT NULL, `full_name` varchar(100) NOT NULL, `job_title` varchar(100) NOT NULL, `department` varchar(100) NOT NULL, `gender` varchar(100) NOT NULL, `age` varchar(100) NOT NULL, `hire_date` date NOT NULL, `annual_salary` varchar(100) NOT NULL, `bonus` varchar(100) NOT NULL, `country` varchar(100) NOT NULL, `city` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for table `employee` -- ALTER TABLE `employee` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `employee` -- ALTER TABLE `employee` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=912; COMMIT;
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:
config.php
<?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>Next, we’re going to create another PHP file naming export_data.php that we will use to export to excel data from Database. The following code helps to export data in excel and download it as a File. Here’s the following code:
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();
}
}
?>
Conclusion
If you want to add export options to a list of data, the Export to Excel feature is perfect for that. The export option allows users to download data to an Excel file and save it to a local drive. You can use this simple code to add data export functionality to your web application using PHP.
Frequently asked questions
This is index. php file, in this file first we have make database connection, and fetch data from customer table and display on web page. After this we have make form with input select option for select how many records you want to export in single Excel file and below you can find submit button
On the External Data tab, in the Export group, click Excel. In the Export – Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access uses the name of the source object). If you want, you can modify the file name. In the File Format box, select the file format that you want.
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the form below.😊
