Let’s talk about how to export Excel files using PHP query tables

PHP is a widely used server-side scripting language, and many websites are developed based on PHP. MySQL is a popular relational database management system that can be used to store and manage data. In PHP, we can use MySQL database to store and retrieve data. Sometimes, we need to export the data in the MySQL database to an Excel file to facilitate data analysis and processing. This article will introduce how to export Excel files using PHP query tables.

Step One: Connect to MySQL Database

Before exporting data, we need to connect to the MySQL database and select the table to export. First, we need to connect to the MySQL server using the following code:

//数据库链接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

//创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

//检测连接
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

In the above code, we use the mysqli_connect() function to connect to the MySQL server and check whether the connection is successful. We need to replace the variables with the correct server name, username, password and database name. If the connection is successful, we will perform further operations through the $conn object.

Step 2: Query the data and store it as an array

Next, we need to query the data in the MySQL database and store it as an array. We can query the data in the database using the following PHP code:

//查询要导出的数据
$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    //将查询结果存储为数组
    $data = array();
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
} else {
    echo "0 results";
}

In the above code, we use the SELECT statement to select all the rows in the table to be exported from the MySQL database. We use the mysqli_query() function to execute the query and store the results in the $result object. If the query returns results, we store the results as an array $data and use a while loop to iterate through each row. Finally, we use the fetch_assoc() function to get each row from the result set and add it to the array $data.

Step 3: Store the data as an Excel file

Once we have the data in the MySQL table we want to export and store it as an array, we need to store it as an Excel file . We can export array data to Excel file using PHPExcel library. PHPExcel is a powerful PHP class library that provides the function of creating Excel files.

The following is a sample code to store data as an Excel file:

//加载PHPExcel类库
require_once 'PHPExcel.php';

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

//设置Excel文件属性
$objPHPExcel->getProperties()->setCreator("Your name")
                             ->setLastModifiedBy("Your name")
                             ->setTitle("Title")
                             ->setSubject("Subject")
                             ->setDescription("Description")
                             ->setKeywords("keywords")
                             ->setCategory("Category");

//将数据存储到Excel文件中
$objPHPExcel->setActiveSheetIndex(0);
$rowNum = 1;
foreach($data as $row) {
    $col = 'A';
    foreach($row as $cell) {
        $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNum,$cell);
        $col++;
    }
    $rowNum++;
}

//将Excel文件输出至浏览器
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="file.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

In the above code, we use the PHPExcel class library to create a PHPExcel object and set the Excel file properties such as title, theme, Keywords etc. We use a foreach loop to read each row of data from the $data array and use the setCellValue() function to write the data to the Excel file. Finally, we output the Excel file to the browser and save it as a file.

Conclusion

It is very common to use MySQL database to store and retrieve data in PHP, and exporting data from MySQL database to Excel file is also an important task. In this article, we introduce the method of exporting Excel files using PHP query table. This is a very useful technique that can help us better manage and process data.

The above is the detailed content of Let’s talk about how to export Excel files using PHP query tables. For more information, please follow other related articles on the PHP Chinese website!