How to Export MySQL data to excel sheet in PHP
In this article, you will learn how to export mysql data from database to excel sheet in php. Export data to excel in php.
So guys, to export data to excel sheet using php, we need to create a html form and submit the form request with post method which will be downloading the file Xlsx, Xls, Csv using code. Let's get started:
We will be using Bootstrap 5 to design the FORM or user interface.
Step 1: Download/Install the Package (phpSpreadSheet):
Let's install via composer, open your terminal from your application root directory:
HP@HP-PC MINGW64 /c/xampp/htdocs/php-excel
$ composer require phpoffice/phpspreadsheet
After successful installation of this package, you will find few files in your application like vendor, composer.json, etc.
Step 2: Create a index.php file and paste the below form:
<?php session_start(); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>How to Export Data from database in excel sheet using PHP</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12 mt-4">
<?php
if(isset($_SESSION['message']))
{
echo "<h4>".$_SESSION['message']."</h4>";
unset($_SESSION['message']);
}
?>
<div class="card mt-5">
<div class="card-header">
<h4>How to Export Data from database in excel sheet using PHP</h4>
</div>
<div class="card-body">
<form action="code.php" method="POST">
<select name="export_file_type" class="form-control">
<option value="xlsx">XLSX</option>
<option value="xls">XLS</option>
<option value="csv">CSV</option>
</select>
<button type="submit" name="export_excel_btn" class="btn btn-primary mt-3">Export</button>
</form>
</div>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
Step 3: Create a table named students in your database as follows:
CREATE TABLE students (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(191) NOT NULL,
email VARCHAR(191) NOT NULL,
phone VARCHAR(191) NOT NULL,
course VARCHAR(191) NOT NULL
)
Step 4: Create a code.php file and paste the below code:
<?php
session_start();
$con = mysqli_connect('localhost','root','','adminpanel');
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_excel_btn']))
{
$file_ext_name = $_POST['export_file_type'];
$fileName = "student-sheet";
$student = "SELECT * FROM students";
$query_run = mysqli_query($con, $student);
if(mysqli_num_rows($query_run) > 0)
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Full Name');
$sheet->setCellValue('C1', 'Email');
$sheet->setCellValue('D1', 'Phone');
$sheet->setCellValue('E1', 'Course');
$rowCount = 2;
foreach($query_run as $data)
{
$sheet->setCellValue('A'.$rowCount, $data['id']);
$sheet->setCellValue('B'.$rowCount, $data['fullname']);
$sheet->setCellValue('C'.$rowCount, $data['email']);
$sheet->setCellValue('D'.$rowCount, $data['phone']);
$sheet->setCellValue('E'.$rowCount, $data['course']);
$rowCount++;
}
if($file_ext_name == 'xlsx')
{
$writer = new Xlsx($spreadsheet);
$final_filename = $fileName.'.xlsx';
}
elseif($file_ext_name == 'xls')
{
$writer = new Xls($spreadsheet);
$final_filename = $fileName.'.xls';
}
elseif($file_ext_name == 'csv')
{
$writer = new Csv($spreadsheet);
$final_filename = $fileName.'.csv';
}
// $writer->save($final_filename);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attactment; filename="'.urlencode($final_filename).'"');
$writer->save('php://output');
}
else
{
$_SESSION['message'] = "No Record Found";
header('Location: index.php');
exit(0);
}
}
?>
Thanks for reading.