PHPExcel太占内存,改用csv导出数据.
假如服务器给php可用内存为50M,需要导出200M的数据.
框架中查询数据库后, 处理结果集直接fetchall()会导致内存溢出.
所以要一行一行读取, 读一行写一行, 达到降低内存占用的目的
下边封装了一个类,
考虑到结果集某些字段需要特殊处理, ( 比如state字段,值为1表示未完成,2表示已完成) 还支持设置一个回调函数
class CSVExportModel{
/** 数据库连接对象
* @var null
*/
private $pdo = null;
/** sql语句
* @var string
*/
private $sql = '';
/** 表头
* @var array
*/
private $tableHeader = array();
/** 表头中的字段名
* @var array
*/
private $tableHeaderName = array();
/** 表头中的输出显示汉字
* @var array
*/
private $tableHeaderDesc = array();
/** 文件名
* @var string
*/
private $fileName = '';
/** 回调函数
* @var null
*/
private $convert = null;
/**
* 直接下载
* @var bool
*/
private $directDownload = true;
/**
* 脚本超时时间
* @var int
*/
private $timeout = 300;
/**
* CSVExport constructor.
*
* @param \PDO $pdo pdo对象
* @param string $sql sql语句
* @param array $tableHeader 表头,一维数组['field_name'=>'表头文字']
* @param string $fileName 下载时的文件名, 不含后缀
* @param callable $convert 回调函数, 用来处理某些字段
*/
public function __construct ($pdo , $sql , $tableHeader , $fileName , $convert = null)
{
$this->pdo = $pdo;
$this->sql = $sql;
$this->tableHeader = $tableHeader;
$this->tableHeaderName = array_keys($tableHeader);
$this->tableHeaderDesc = array_values($tableHeader);
$this->fileName = $fileName;
$this->convert = $convert;
}
/**
* @return float|int
*/
public function getTimeout ()
{
return $this->timeout;
}
/**
* @param float|int $timeout
*/
public function setTimeout ($timeout)
{
$this->timeout = $timeout;
}
/**
* 执行查询导出操作
*/
function doExport(){
set_time_limit($this->timeout);//超时限制
$this->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,false);//非缓冲查询
$statement = $this->pdo->query($this->sql);//查询
$this->sendHTTPHeader($this->fileName); // http 下载头
echo $this->tableHeaderDescImplode(); //表头
//结果集处理
while($data = $statement->fetch(\PDO::FETCH_ASSOC)){
//输出字段转换
if ($this->convert){
$data = call_user_func($this->convert,$data);
}
echo $this->dataFieldImplode($data);
}
}
/**
* 拼接表头
* @return string 逗号分割的表头字符串
*/
private function tableHeaderDescImplode(){
$str = '';
foreach ($this->tableHeaderDesc as $desc){
$str .= $desc."\t,";
}
return rtrim($str,',').PHP_EOL;
}
/**
* 按照表头字段,从结果集取出对应的字段,拼接
* @param array $data 数据库结果集, 一维数组
*
* @return string 拼接后的字符传
*/
private function dataFieldImplode($data){
$str = '';
foreach ($this->tableHeaderName as $field){
$str .= $data[$field]."\t,";
}
return rtrim($str,',').PHP_EOL;
}
/**
* 发送http下载header
*
* @param $fileName
*/
private function sendHTTPHeader($fileName){
header("Cache-control: private");
header("Pragma: public");
header('Content-type: application/x-csv');
header("Content-Disposition:attachment;filename=" . $fileName. ".csv");
}
}
调用时,需要在构造函数传入几个参数
- pdo对象
- sql语句
- 表头
- 文件名
- 回调 可选
/** csv导出*/
/** PDO对象 */
$dbms = 'mysql'; //数据库类型
$host = C('DB_HOST'); //数据库主机名
$dbName = C('DB_NAME'); //使用的数据库
$user = C('DB_USER'); //数据库连接用户名
$pass = C('DB_PWD'); //对应的密码
$dsn = "$dbms:host=$host;dbname=$dbName";
$pdo = new \PDO($dsn,$user,$pass);
/** sql语句 */
$sqlString = M('Journey_order')->fetchSql()
->alias('j')
->where($where_order)
->field('j.order_id,j.order_sn,j.order_class,j.passenger_name,j.passenger_phone,j.driver_name,j.driver_phone,j.vehicle_number,j.start_address,j.end_address,j.journey_mile,j.journey_fee,j.service_fee,j.coupon_fee,j.actual_price,j.pay_type,j.utime,j.journey_state,j.is_driver_settle,d.driver_number,j.mt_order_sn')
->join('left join t_driver as d on j.driver_id = d.driver_id')
->order('j.ctime DESC')
->select();
/** 表头 */
$tableHeader = [
'order_id'=>'订单ID',
'order_sn'=>'订单编号',
'order_class'=>'提交类别',
'passenger_name'=>'乘客姓名',
'passenger_phone'=>'乘客电话',
'driver_name'=>'司机姓名',
'driver_phone'=>'司机电话',
'vehicle_number'=>'车牌号',
'start_address'=>'乘车开始地址',
'end_address'=>'终点地址',
'journey_mile'=>'乘车里程(m)',
'journey_fee'=>'打车费用',
'service_fee'=>'叫车费',
'coupon_fee'=>'优惠费用',
'actual_price'=>'实际支付费用',
'pay_type'=>'付款类型',
'utime'=>'完成时间',
'journey_state'=>'订单状态',
'is_driver_settle'=>'司机结算状态',
'driver_number'=>'身份证号',
'mt_order_sn'=>'第三方订单号'
];
/**
* 导出时的回调函数 ,用来转换导出的字段
* @param array $data mysql的一行数据
*
* @return mixed 转换后的数据
*/
$callable = function($data){
if($data['journey_state']==10){
$data['pay_type'] = "系统免单";
}
$data['order_class'] = $this->getOrderClassName($data['order_class']);//订单类型名
$data['is_driver_settle'] = $this->getSettleStateName($data['is_driver_settle']);//结算状态
$data['pay_type'] = $this->getPayTypeName($data['pay_type']);//支付类型
$data['journey_state'] = $this->getJourneyStateName($data['journey_state']);//订单状态
$data['utime'] = date("Y-m-d",$data['utime']);
return $data;
};
$fileName = '已完成订单'.date('Ymd',time());
$csvExport = new CSVExportModel($pdo,$sqlString,$tableHeader,$fileName,$callable);
$csvExport->setTimeout(60);
$csvExport->doExport();
/** 导出结束 */
