Exporting data from databases to Excel is a common demand that can be achieved easily with the help of various tools and libraries, one of which is PhpSpreadsheet. With PhpSpreadsheet, users will be able to export their data into Excel files with minimal effort. This article will guide you through the process of exporting data from databases to Excel using PhpSpreadsheet.
What is PhpSpreadsheet?
PhpSpreadsheet is a PHP library that replaces the PHPExcel library, which became unreliable and wasn’t regularly updated. It provides a set of classes that allows developers to read and write spreadsheet files in various formats. PhpSpreadsheet supports a wide range of file formats including XLS, XLSX, CSV, ODS, and HTML.
How to Export Data from Databases to Excel Using PhpSpreadsheet:
The following are the steps to export data from databases to Excel using PhpSpreadsheet:
Step 1: Install and Include the Required PhpSpreadsheet Library
The first step is to download and install the PhpSpreadsheet library. You can use composer to install the library by running the following command:
composer require phpoffice/phpspreadsheet
Once you have installed the library, the next step is to include it in your PHP script by adding the following lines:
require 'vendor/autoload.php';
Step 2: Connect to the Database and Retrieve Data
The next step is to establish a connection to your database and retrieve the data that you want to export to Excel. For this example, we will use a MySQL database. The following PHP code shows how to connect to your MySQL database and retrieve data:
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error)
die("Connection failed: " . $conn->connect_error);
// SQL query to retrieve data
$sql = "SELECT * FROM your_table_name";
$result = $conn->query($sql);Step 3: Create a New Spreadsheet Object and Define the Excel File Properties
After retrieving the data from the database, the next step is to create a new PhpSpreadsheet object and define the properties of the Excel file that you want to generate. You can set the following properties:
- Document title
- Author
- Subject
- Keywords
- Category
- Comments
The following PHP code shows how to create a new PhpSpreadsheet object and define the Excel file properties:
// Create new PhpSpreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
// Set document properties
$spreadsheet->getProperties()
->setCreator("Your Name")
->setLastModifiedBy("Your Name")
->setTitle("Export Data to Excel using PhpSpreadsheet")
->setSubject("Excel Export")
->setDescription("Export data from database to Excel using PhpSpreadsheet")
->setKeywords("phpspreadsheet, export, excel")
->setCategory("Export");Step 4: Add Data to the Excel File
The next step is to add the data that you retrieved from the database to the Excel file. PhpSpreadsheet provides several methods for adding data to the Excel file including:
- setCellValue
- fromArray
The following PHP code shows how to add data to the Excel file using the setCellValue method:
// Add data to the first worksheet
$worksheet = $spreadsheet->getActiveSheet();
$rowIndex = 1;
while($row = $result->fetch_assoc())
// Set cell values
$worksheet->setCellValue('A'.$rowIndex, $row['column1']);
$worksheet->setCellValue('B'.$rowIndex, $row['column2']);
$worksheet->setCellValue('C'.$rowIndex, $row['column3']);
$worksheet->setCellValue('D'.$rowIndex, $row['column4']);
$rowIndex++;
The following PHP code shows how to add data to the Excel file using the fromArray method:
// Add data to the first worksheet
$worksheet = $spreadsheet->getActiveSheet();
$rowIndex = 1;
$data = array();
while($row = $result->fetch_assoc())
// Add row data to array
array_push($data, array(
$row['column1'],
$row['column2'],
$row['column3'],
$row['column4']
));
$rowIndex++;
// Add data to worksheet
$worksheet->fromArray($data, NULL, 'A1');Step 5: Save the Excel File
The final step is to save the Excel file to a location on your server or to provide it as a download to the user. To save the Excel file to a location on your server, you can use the following PHP code:
// Save PHP Spreadsheet object to file
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('/path/to/your/excel/file.xlsx');To provide the Excel file as a download to the user, you can use the following PHP code:
// Send HTTP headers
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
// Output PHP Spreadsheet object to the browser
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('php://output');FAQs:
1. How do I export data to Excel from a different database?
To export data to Excel from a different database, you need to modify the database connection configuration in your PHP script. Ensure that you have the correct database name, database username, and database password. You can update the following lines of code:
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);Replace the values of the $servername, $username, $password, and $dbname variables with the correct values for your database.
2. How do I format the Excel file?
To format the Excel file, you can use PhpSpreadsheet’s formatting functions. These functions allow you to format the cell values, set the background color, set the font style, and much more. The following PHP code shows how to format the cell values in the Excel file:
// Set cell values and format
$worksheet->setCellValue('A1', 'Column 1')
->setCellValue('B1', 'Column 2')
->setCellValue('C1', 'Column 3')
->setCellValue('D1', 'Column 4');
$worksheet->getStyle('A1:D1')->getFont()
->setBold(true)
->getColor()->setRGB('FFFFFF');
$worksheet->getStyle('A1:D1')->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setRGB('000000');Conclusion
Exporting data from databases directly to Excel can be a challenging task, but with the help of a library like PhpSpreadsheet, it can be done seamlessly. By following the steps outlined in this article, you can easily export data from your database to Excel and format the data to suit your requirements. Additionally, with PhpSpreadsheet’s formatting functions, you can customize the Excel file to suit your style.
