How to export database data in php and convert it to excel table

In website development and data processing, databases play a very important role. How to correctly export database data and convert it into Excel tables is a problem we often need to solve. In this article, we will explain how to easily do this using PHP language.

1. Preparation

Before starting our operation, we need to install the PHP environment and PHPExcel class library. PHPExcel is a powerful PHPExcel class library used to generate Various types of Excel files. The method of installing the PHP environment and PHPExcel class library will not be described here. If you have not installed it yet, you can refer to the PHP official website and PHPExcel official website for installation and use.

2. Connecting to the database

Connecting to the database is a very important operation. We need to ensure that the connection is normal and the database table is correctly selected. The following is a basic code example for connecting to a MySQL database through mysqli:

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

3. Query database data

After completing the database connection work, we need to query through SQL statements database data and process it. The following is a simple SQL statement example:

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

By using the mysqli_query function, we can easily query the database and obtain the query result $result.

4. Generate Excel table

After obtaining the database query results, we next need to convert them into Excel table form. The following is a basic code example of the PHPExcel class library:

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

//设置Excel文件属性
$objPHPExcel->getProperties()
    ->setCreator("your name")
    ->setLastModifiedBy("your name")
    ->setTitle("title")
    ->setSubject("subject")
    ->setDescription("description")
    ->setKeywords("keywords")
    ->setCategory("category");

//设置当前活动表格
$objPHPExcel->setActiveSheetIndex(0);

//循环遍历查询结果，将数据写入Excel表格
$i = 1;
while ($row = mysqli_fetch_array($result)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $row['column1']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row['column2']);
    $i++;
}

Using the above code, we can easily convert the MySQL query results into the Excel table format, and at the same time, input the data into the Excel table.

5. Output or download Excel files

After writing the data into the Excel table, we can choose to output it to the browser or download it locally for saving. The following is a basic code example to implement this operation:

Output to browser:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="filename.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

Download to local:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('file_path/filename.xls');

6. Summary

PHP language and PHPExcel class library provide simple and powerful tools that can help us export database data to Excel tables. The steps introduced in this article are only the basic operations of the entire process. I hope it can provide some help to beginners and also provide a reference for experienced developers.

The above is the detailed content of How to export database data in php and convert it to excel table. For more information, please follow other related articles on the PHP Chinese website!