PHP实时生成并下载超大数据量EXCEL文件的方法

在实际项目中，有时候需要将大量数据导出为Excel文件，一般的做法是将数据循环写入到Excel文件中，但是当数据量很大时，这种写入方式会导致程序卡死或者内存溢出，因此我们需要寻找一种更合适的方法来解决这个问题。本文将介绍一种实时生成并下载超大数据量EXCEL文件的方法。

1、分页查询数据

为了避免内存溢出的问题，我们可以采用分页查询的方式，每次只查询一部分数据，然后写入到Excel文件中，最后合并成一个完整的Excel文件。采用分页查询还有一个好处，就是不占用过多的服务器资源，同时也可以提高查询效率。

2、Excel文件格式

在生成Excel文件之前，我们需要先了解Excel文件的格式。Excel文件是一种电子表格文件，一般使用的扩展名为.xlsx或者.xls，它可以包含一些工作表，每个工作表可以有多个单元格，每个单元格可以包含文本、数值、日期等等数据。

3、PHPExcel

PHPExcel 是一种 PHP 生成 Excel 文档的类库，它提供了一种简单易用的方式来创建、读取和修改 Excel 文件，同时支持多种文件格式，包括.xls 和.xlsx 等。我们可以通过安装PHPExcel类库来使用它的功能。

4、实现步骤

接下来，我们来详细介绍如何实现实时生成并下载超大数据量EXCEL文件的方法。

步：连接数据库

我们需要先连接数据库，将数据从数据库中查询出来。我们可以采用PDO或者mysqli等方式来连接数据库，例如：

try {
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '123456');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo '连接数据库失败：' . $e->getMessage();
}

第二步：分页查询数据

为了避免内存溢出，我们需要采用分页查询的方式，每次只查询一部分数据，然后写入到Excel文件中。我们可以通过LIMIT语句来实现分页查询，例如：

$page_size = 1000; //每页的记录数
$count_sql = "SELECT count(*) FROM user"; //获取总记录数的sql语句
$query_sql = "SELECT * FROM user LIMIT ?, ?"; //分页查询的sql语句

//获取总记录数
$count_stmt = $pdo->prepare($count_sql);
$count_stmt->execute();
$total_count = $count_stmt->fetchColumn();

//分页查询数据
$excel_output = fopen('php://output', 'w');
for ($i = 0; $i < $total_count; $i += $page_size) {
    $query_stmt = $pdo->prepare($query_sql);
    $query_stmt->bindParam(1, $i, PDO::PARAM_INT);
    $query_stmt->bindParam(2, $page_size, PDO::PARAM_INT);
    $query_stmt->execute();
    $result = $query_stmt->fetchAll(PDO::FETCH_ASSOC);

    //将数据写入Excel文件中
    foreach ($result as $key => $value) {
        $row = array(
            $value['id'],
            $value['name'],
            $value['age'],
            $value['gender']
        );
        fputcsv($excel_output, $row);
    }
}
fclose($excel_output);

第三步：生成Excel文件

我们可以使用PHPExcel类库来生成Excel文件。PHPExcel提供了丰富的API来操作Excel文件，例如打开文件、创建工作表、读写单元格等等。我们可以通过以下步骤来生成Excel文件：

（1）创建Excel文件和工作表：

require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Sheet1');

（2）写入数据：

$row = 1;
foreach ($result as $value) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $value['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $value['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $value['age']);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $value['gender']);
    $row++;
}

（3）设置文件格式：

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');
exit;

第四步：下载Excel文件

最后一步就是将生成的Excel文件下载到本地。我们可以通过设置Header头来实现文件下载，例如：

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');
exit;

5、总结

实时生成并下载超大数据量EXCEL文件的方法主要是通过分页查询数据，每次只查询一部分数据，然后写入到Excel文件中，最后合并成一个完整的Excel文件。通过分页查询可以避免内存溢出的问题，同时也可以提高查询效率。我们可以采用PHPExcel类库来生成Excel文件，它提供了丰富的API来操作Excel文件，使用起来非常方便。