Home > Backend Development > PHP Problem > How to implement php query export to excel

How to implement php query export to excel

PHPz
Release: 2023-03-29 11:47:28
Original
1572 people have browsed it

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());
}
Copy after login

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);
Copy after login

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);
Copy after login

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
Copy after login

Or manually download the latest version of the compressed package:

https://github.com/PHPOffice/PHPExcel/releases
Copy after login

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

require_once 'Classes/PHPExcel.php';
Copy after login

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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template