How to implement php query export to excel

In the era of modern technology and big data, data processing has become an indispensable and important link in various industries and enterprises. Especially in the Internet field, data processing is a key issue. As a WEB development language, PHP is powerful and widely used. In terms of data processing, PHP can also provide good support. This article will introduce how to use PHP to query data and export it to Excel.

1. PHP query database

In PHP, the process of querying the database and obtaining data requires first connecting to the database, then selecting the database, and finally executing the SQL statement. Compared with ordinary SQL statements, SQL statements in PHP need to be escaped to prevent SQL injection attacks.

The function to connect to the database is mysqli_connect(), which contains four parameters: host name, user name, password and database name. If the connection fails, false will be returned, otherwise a database connection object will be returned.

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

The function to select the database is mysqli_select_db(), which contains two parameters: database connection object and database name.

mysqli_select_db($conn, $dbname);

The function to execute SQL statements is mysqli_query(), which contains two parameters: database connection object and SQL statement.

$sql = "SELECT * FROM `table1`";
$result = mysqli_query($conn, $sql);

After executing the above three steps, the $result object will contain all the queried data.

2. Export Excel with PHP

There are many ways to export Excel with PHP. This article introduces one of the simpler methods. To process Excel, you need to use the PHPExcel library, which needs to be installed before you can read and write Excel files.

You can install the PHPExcel library through Composer:

composer require phpoffice/phpexcel

Or manually download the latest version of the compressed package:

https://github.com/PHPOffice/PHPExcel/releases

After decompression, put the Classes folder into the project directory, and then introduce PHPExcel Library:

require_once 'Classes/PHPExcel.php';

Excel table is mainly composed of three parts: "workbook", "worksheet" and "cell". PHPExcel encapsulates many classes and methods, making it easy to create, edit and save Excel tables. The following is a simple example:

//创建PHPExcel对象
$objPHPExcel = new PHPExcel();

//设置工作簿属性
$objPHPExcel->getProperties()
    ->setCreator("Creator")
    ->setLastModifiedBy("Last Modified By")
    ->setTitle("Title")
    ->setSubject("Subject")
    ->setDescription("Description")
    ->setKeywords("keywords")
    ->setCategory("Category");

//创建工作表
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');

//添加表头
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Age');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Gender');

//循环添加数据
$i = 2;
while ($row = mysqli_fetch_assoc($result)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $row['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row['age']);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row['gender']);
    $i++;
}

//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

//设置单元格格式
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);

//将表格导出为Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit;

In this example, a PHPExcel object is first created, the workbook and worksheet properties are set, the header and data are added, and then the column width and cell format are set. .

Finally, export the data of the PHPExcel object to an Excel file through the createWriter() method in the PHPExcel_IOFactory class. The correct MIME type, file name and cache control header need to be set before exporting.

Summary

The above are the basic steps for using PHP to query the database and export to Excel. As an efficient and flexible programming language, PHP provides many convenient methods for processing data. Using the PHPExcel library, Excel files can also be easily manipulated, allowing for flexible data processing and export.

The above is the detailed content of How to implement php query export to excel. For more information, please follow other related articles on the PHP Chinese website!