<?php
/**
* 大量数据导出excel(csv)的实现。
* Created by PhpStorm.
* User: Huming
* Date: 2017-04-16
* Time: 14:04
* */
//使用MS sqlserver数据测试的
require_once 'MSSqlServerHelper.php';
$sqlHelper = new MSSqlServerHelper();
set_time_limit(0);
ini_set('memory_limit', '128M');
date_default_timezone_set("PRC");
$fileName = date('YmdHis');
//浏览器直接下载,加头文件
//header('Content-Type: application/vnd.ms-execl');
//header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
$begintime = microtime(true);
//打开php标准输出流
//以写入追加的方式打开
//$fp = fopen('php://output', 'ab');
//对于大数据,使用在服务器生成文件,完成后下载
$path = $_SERVER['DOCUMENT_ROOT'] . "\\output\\";
$fp = fopen("$path$fileName.csv", "a");
//我们要获取$nums条数据,每次取$step条,分步执行,避免内存溢出
//如果线上环境无法支持一次性读取$step条数据,可反$step相应减小。
$step = 10000;
//指定要取的记录数
//$nums =80000;
//动态获取总共的记录数
$rowscount = $sqlHelper->execute_dql_rowsCount("select count(id) from temp_sjbb");
$nums = $rowscount;
//$nums = 22;
//设置标题
/*$title = array('ID', '用户名', '用户年龄', '用户描述', '用户手机', '用户QQ', '用户邮箱', '用户地址');
foreach($title as $key => $item) {
$title[$key] = iconv('UTF-8', 'GBK', $item);
}
//标题写到标准输出中
fputcsv($fp, $title);*/
//分批获取,写入大数据,防止内存溢出
for ($s = 1; $s <= ceil($nums / $step); ++$s) {
$start = (($s - 1) * $step) + 1;
$end = ($s * $step) > $nums ? $nums : $s * $step;
$sql = "select * from (select *,rOW_NUMBER() OVER (ORDER BY id asc) AS counts from temp_sjbb ) as b";
$sql .= " where counts >= ";
$sql .= $start;
$sql .= " and counts <= ";
$sql .= $end;
// echo $sql;
$result = $sqlHelper->execute_dql_array($sql);
for ($i = 0; $i < count($result); $i++) {
//第一次循环把列标题打印出来
if ($s == 1 && $i == 0) {
$arr = array();
foreach ($result[$i] as $key => $values) {
$arr[] = $key;
}
fputcsv($fp, $arr);
}
fputcsv($fp, $result[$i]);
}
//刷新缓冲区
unset($result);
ob_flush();
flush();
}
fclose($fp);
//打印下载路径
echo "<a href='./output/$fileName.csv'>点击下载</a>";
$endtime = microtime(true);
echo '用时:', $endtime - $begintime;
测试字段数为81个的8万余条数据。