Export MySQL to Excel with PHP and PhpSpreadsheet

in this article, You’ll learn How to export data from MySQL to Excel using PHP and phpspreadsheet, I have already shared the article without any third-party libs Exporting Data from MySQL to Excel using PHP.

PhpSpreadsheet is a powerful open-source PHP library that allows developers to work with spreadsheet files, particularly Microsoft Excel formats (such as .xlsx).

Prerequisites:

There are following tools need to be installed in your system:

PHP environment: You must have a PHP environment in your local development environment using tools like XAMPP, and MAMP.
MySQL database: MySQL database with the data you wish to export.

Composer: You must have Composer installed in your system.
Basic knowledge of PHP programming.

You can also check other tutorials of exporting data with PHP:

Step 1: Install PhpSpreadsheet PHP library

You can use Composer(PHP dependency manager) to install third-party packages in PHP projects, Let’s Install PhpSpreadsheet using Composer with the following command:

composer require phpoffice/phpspreadsheet

Step 2: Create a Table and Connect to MySQL

We will generate an employee table using the following SQL command. You can execute the SQL script in your MySQL query window as provided below:

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;

Seeds some data into the above employee table:

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Tiger Nixon', 320800, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamson', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23),
(11, 'Jena Gaines', 90560, 30),
(12, 'Quinn Flynn', 342000, 22),
(13, 'Charde Marshall', 470600, 36),
(14, 'Haley Kennedy', 313500, 43),
(15, 'Tatyana Fitzpatrick', 385750, 19),
(16, 'Michael Silva', 198500, 66);

Create a connection.php file into your project and write the below code into this file. Establish a connection to your MySQL database using PHP. You can use the PHP MySQL extension for this.

Class dbObj{
/* Database connection start */
var $dbhost = "localhost";
var $username = "your_username";
var $password = "your_password";
var $dbname = "your_database";
var $conn;

function getConnstring() {
$con = mysqli_connect($this->dbhost, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$this->conn = $con;
}
return $this->conn;
}
}

Replace your_username, your_password, and your_database with your MySQL credentials.

Step 3: Fetch Mysql Table Data using PHP

You can write SQL queries to retrieve the data that you want to export from the MySQL database. You can fetch data from 'employee' data using the following query:

include_once("connection.php");
$db = new dbObj();
$connString =  $db->getConnstring();
$sql = "SELECT * FROM employee";
$result = $conn->query($sql);

Step 4: Create an Excel Spreadsheet from MySql table

Let’s create Excel Spreadsheets using the powerful PhpSpreadsheet library from MySQL. The basic example of how to create an Excel workbook and add data:

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Add headers
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'employee_name');
$sheet->setCellValue('C1', 'employee_age');
$sheet->setCellValue('D1', 'employee_salary');

// Add data from the database
$row = 2;
while ($row_data = $result->fetch_assoc()) {
    $sheet->setCellValue('A' . $row, $row_data['id']);
    $sheet->setCellValue('B' . $row, $row_data['employee_name']);
    $sheet->setCellValue('C' . $row, $row_data['employee_age']);
	$sheet->setCellValue('C' . $row, $row_data['employee_salary']);
    $row++;
}

Step 5: Export to Excel

Finally, you can save the Excel file to a specific location on your server and provide a download link to the user. You can do that as follows:

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('exported_data.xlsx');

// Provide a download link
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="exported_data.xlsx"');
header('Cache-Control: max-age=0');
readfile('exported_data.xlsx');
exit;

Conclusion

We have learned here Exporting data from MySQL to Excel using PHP with the help of PhpSpreadsheet. Also created PHP’s database connectivity with mysql, you can use this feature to generate reports, analyze data, or share information, this artcle allows you to do it seamlessly and programmatically.

Conclusion

Leave a Reply Cancel reply