PHP development skills: How to implement table export function

PHP development skills: How to implement the table export function

In web development, we often encounter situations where we need to export data in the database into tables. This article will introduce how to use PHP to implement the table export function, with specific code examples.

1. Create a database connection

First, we need to create a connection to the database. This can be achieved by using PHP extensions like PDO or mysqli. The following is a sample code using the mysqli extension:

<?php
$host = 'localhost';
$dbUsername = 'root';
$dbPassword = 'password';
$dbName = 'database_name';

// 创建数据库连接
$mysqli = new mysqli($host, $dbUsername, $dbPassword, $dbName);

// 检查连接是否成功
if ($mysqli->connect_error) {
    die("连接失败: " . $mysqli->connect_error);
}

// 设置字符集
$mysqli->set_charset('utf8');
?>

2. Query data and organize it into tables

Next, we need to write SQL query statements to obtain the data that needs to be exported , and organize it into an HTML table. The following is a sample code:

<?php
$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // 输出表头
    echo "<table><tr><th>ID</th><th>姓名</th><th>邮箱</th></tr>";
    
    // 输出每一行数据
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row['id']."</td><td>".$row['name']."</td><td>".$row['email']."</td></tr>";
    }
    
    echo "</table>";
} else {
    echo "没有数据";
}
?>

3. Set HTTP response header

To implement the table export function, we need to set the Content-Type and Content-Disposition headers of the HTTP response, to tell the browser to download the table as a file. The following is a sample code:

<?php
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="users.xls"');
?>

In the above code, the Content-Type header specifies the MIME type of the file. The application/vnd.ms-excel here indicates that the Excel table to be downloaded is . The Content-Disposition header specifies the downloaded file name. users.xls here indicates that the downloaded file name is users.xls.

4. Output data to Excel file

Finally, we need to output the HTML code of the table to the Excel file. Table HTML code can be written to a file using the file_put_contents function. The following is a sample code:

<?php
$file = 'users.xls';
$html = "<table>...</table>";

file_put_contents($file, $html);
?>

Based on the above steps, we can integrate the code together to achieve a complete table export function. Here is a sample code:

<?php
$host = 'localhost';
$dbUsername = 'root';
$dbPassword = 'password';
$dbName = 'database_name';

// 创建数据库连接
$mysqli = new mysqli($host, $dbUsername, $dbPassword, $dbName);
if ($mysqli->connect_error) {
    die("连接失败: " . $mysqli->connect_error);
}
$mysqli->set_charset('utf8');

// 查询数据并组织为表格
$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>姓名</th><th>邮箱</th></tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row['id']."</td><td>".$row['name']."</td><td>".$row['email']."</td></tr>";
    }
    echo "</table>";
} else {
    echo "没有数据";
}

// 设置HTTP响应头
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="users.xls"');

// 将数据输出到Excel文件
$file = 'users.xls';
$html = "<table>...</table>";
file_put_contents($file, $html);
?>

The above are the steps and code examples for using PHP to implement the table export function. Through these codes, we can export the data in the database into Excel tables and provide them to users for download and review. Hope this article is helpful to you.

The above is the detailed content of PHP development skills: How to implement table export function. For more information, please follow other related articles on the PHP Chinese website!