How to export MySql data to excel using php
by Yogi P - December 3, 2022
Export MySql data to excel using php
Exporting data from a MySQL database to Excel is a common task for many users, and it can be easily accomplished using PHP. PHP is a popular scripting language that is often used for web development, and it has built-in support for working with MySQL databases.
To export MySQL data to Excel using PHP, you will need to first establish a connection to the database using the PHP MySQLi extension. This is done using the mysqli_connect() function, which takes the database hostname, username, password, and database name as arguments.
Once the connection is established, you can use the mysqli_query() function to execute a SQL query and retrieve the data you want to export. This function returns a result set, which can be processed using a while loop to iterate over each row of data.
Once you have retrieved the data, you can use the PHPExcel library to create an Excel file and populate it with the data from the MySQL result set. This library provides a simple and powerful set of classes that make it easy to work with Excel files in PHP.
To create an Excel file and add data to it, you will need to first include the PHPExcel library in your PHP script and create a new PHPExcel object. Then, you can use the setActiveSheetIndex() and getActiveSheet() methods to select the active sheet and add data to it using the setCellValue() method.
Once the data has been added to the Excel file, you can use the PHPExcelWriterFactory class to save the file in the desired format. This class provides a set of writer classes for different Excel file formats, such as .xlsx, .xls, and .csv.
In summary, exporting MySQL data to Excel using PHP is a simple and straightforward process that can be easily accomplished using the PHP MySQLi extension and the PHPExcel library. With a few lines of code, you can easily create an Excel file and populate it with data from a MySQL database.
Here is an example of how to export MySQL data to Excel using PHP:
<?php
// Include PHPExcel library
include "PHPExcel.php";
// Database credentials
$host = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Establish database connection
$conn = mysqli_connect($host, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL query
$sql = "SELECT * FROM table_name";
// Execute query and store result set
$result = mysqli_query($conn, $sql);
// Check if query was successful
if ($result) {
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set active sheet
$objPHPExcel->setActiveSheetIndex(0);
// Get active sheet
$objWorksheet = $objPHPExcel->getActiveSheet();
// Set column headers
$objWorksheet->setCellValue("A1", "Column 1");
$objWorksheet->setCellValue("B1", "Column 2");
$objWorksheet->setCellValue("C1", "Column 3");
// Populate sheet with data from MySQL result set
$row = 2;
while ($data = mysqli_fetch_assoc($result)) {
$objWorksheet->setCellValue("A$row", $data["column1"]);
$objWorksheet->setCellValue("B$row", $data["column2"]);
$objWorksheet->setCellValue("C$row", $data["column3"]);
$row++;
}
// Save Excel file as .xlsx
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save("data.xlsx");
// Free memory
mysqli_free_result($result);
}
// Close connection
mysqli_close($conn);
?>
In this example, we first establish a connection to the MySQL database using the mysqli_connect() function. Then, we execute a SQL query using the mysqli_query() function and store the result set in the $result variable.
Next, we create a new PHPExcel object and set the active sheet using the setActiveSheetIndex() and getActiveSheet() methods. Then, we set the column headers using the setCellValue() method and iterate over the MySQL result set using a while loop to populate the Excel sheet with data.
Finally, we save the Excel file in the .xlsx format using the PHPExcel_IOFactory::createWriter() method and close the database connection using the mysqli_close() function.
Pin It
