请写出完整的代码示例给我
时间: 2024-02-27 18:52:41 浏览: 90
JSP中读文件和写文件的源代码
好的,下面是一个完整的使用 PHPExcel 导出数据库大量数据的示例代码:
```php
<?php
require 'PHPExcel/PHPExcel.php';
// 连接到数据库
$host = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8";
$options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$db = new PDO($dsn, $username, $password, $options);
// 查询需要导出的数据
$sql = "SELECT * FROM users";
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
// 创建 PHPExcel 对象
$objPHPExcel = new PHPExcel();
// 设置文件属性
$objPHPExcel->getProperties()->setCreator("Your Name")
->setLastModifiedBy("Your Name")
->setTitle("Export Data")
->setSubject("Export Data")
->setDescription("Export Data")
->setKeywords("Excel")
->setCategory("Export Data");
// 设置表头
$fields = $stmt->fetchAll(PDO::FETCH_ASSOC);
$column = 0;
foreach ($fields as $field) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow($column, 1, $field['COLUMN_NAME']);
$column++;
}
// 将数据添加到 Excel 文件中
$row = 2;
while ($data = $result->fetch(PDO::FETCH_ASSOC)) {
$column = 0;
foreach ($data as $value) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
// 输出 Excel 文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="export.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
```
阅读全文