Export database data to Excel using PHP

To export data from a database to an Excel file, you can use thePHPExcel library in PHP. Here is a simple example code that usesPHPExcel to retrieve data from a database and export it to an Excel file:

// 导入PHPExcel库
require_once 'PHPExcel.php';

// 连接数据库
$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);
}

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

// 创建一个新的PHPExcel对象
$objPHPExcel = new PHPExcel();

// 设置当前工作表
$objPHPExcel->setActiveSheetIndex(0);

// 将数据添加到工作表中
$row = 1;
while($row_data = $result->fetch_assoc()) {
    $col = 0;
    foreach($row_data as $value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }
    $row++;
}

// 设置文件名和格式
$file_name = "database_info.xls";

// 用于告诉浏览器要下载的文件的类型和格式
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$file_name.'"');
header('Cache-Control: max-age=0');

// 将Excel文件输出到浏览器
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

// 关闭数据库连接
$conn->close();

In the above example code, first, use mysqli to connect to the database. Then, query the data from the database and add it to aPHPExcel object. Next, set the file name and format, and use the header() function to specify the file type and format to be downloaded by the browser. Finally, output the Excel file to the browser and close the database connection.

Please note that this code is only suitable for exporting smaller datasets. If you need to export large amounts of data, consider using batch export or other export options.