/**
* @FILE_NAME:ExcelExport.php
* @Description csv导出数据
* @User ZHaoGuiBin
* @Date 2019-12-04 13:52:58
*/
class ExcelExport
{
/**
* @FuncName:sqlQuery
* @Description 生成器模式返回数据
* @User ZHaoGuiBin
* @Date 2020-01-08 16:43
* @param $sql
* @return Generator
*/
public static function sqlQuery($sql)
{
$sys = new SysModel();
$db = $sys->getAdapter();
foreach ($db->fetchAll($sql) as $key => $val) {
yield $val; //生成器返回数据,避免内存溢出
}
}
/**
* @FuncName:exportExcel
* @Description
* @User ZHaoGuiBin
* @Date 2020-01-09 09:23
* @param string $title
* @param string $sql
* @param array $data example : ["单号" => 'order_key'],['报表title名称'=>'mysql语句字段名']
* @return bool
*/
public static function exportExcel($title = '报表导出', $sql = '', $data = [])
{
$data_dir = getcwd() . "/data_export/";
//删除历史表格
self::deleteExcel();
if (!file_exists($data_dir) || !is_writable($data_dir)) {
if (!@mkdir($data_dir, 0755, TRUE)) {
Tools::dieJs("alert('导出失败,请联系管理员处理');history.go(-1);");
}
}
$file_name = $title . time() . ".csv";
$file_path = $data_dir . $file_name;
//拼接sql
$fields = '';
$title_fields = [];
$temp_key = 0;
$fields_count = count($data);
foreach ($data as $key => $field) {
$temp_key += 1;
$fields .= "ifnull({$field},'') as {$field}" . ($temp_key < $fields_count ? ',' : '');
array_push($title_fields, $key);
}
$fp = fopen($file_path, 'w'); //生成临时文件
fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));//转码,防止乱码
fputcsv($fp, $title_fields);//插入标题
foreach (self::sqlQuery("SELECT {$fields} FROM ({$sql}) as t") as $key => $value) {
fputcsv($fp, $value);
}
fclose($fp);
self::downloadExcel($file_path, $file_name);
return true;
}
/**
* @FuncName:downloadExcel
* @Description
* @User ZHaoGuiBin
* @Date 2020-01-08 16:46
* @param string $file_path
* @param string $file_name
* @return void
*/
public static function downloadExcel($file_path = '', $file_name = '')
{
ob_end_clean();
if (!file_exists($file_path)) {
Tools::dieJs("alert('文件不存在,请联系管理员处理');history.go(-1);");
}
$fp = fopen($file_path, "r");
$file_size = filesize($file_path);
//下载文件需要用到的头
header("Content-type:text/html;charset=utf-8");
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length:" . $file_size);
Header("Content-Disposition: attachment; filename=" . $file_name);
$buffer = 1024;
$file_count = 0;
while (!feof($fp) && $file_count < $file_size) {
$file_con = fread($fp, $buffer);
$file_count += $buffer;
echo $file_con;
}
fclose($fp);
}
/**
* @FuncName:deleteExcel
* @Description 删除没用的表格
* @User ZHaoGuiBin
* @Date 2019-12-24 15:05:01
* @return bool
*/
public static function deleteExcel()
{
//几分钟前的时间戳
$delete_time = strtotime("-5 minute");
$data_dir = getcwd() . "/data_export/*.csv";
$data_dir = glob($data_dir);
if (empty($data_dir)) {
return true;
}
foreach ($data_dir as $key => $file_path) {
$temp_time = self::findNum($file_path);
if ($temp_time < $delete_time) {
unlink($file_path);
}
}
return true;
}
/**
* @FuncName:findNum
* @Description
* @User ZHaoGuiBin
* @Date 2019-12-24 14:54:11
* @param string $str
* @return string
*/
public static function findNum($str = '')
{
$str = trim($str);
if (empty($str)) {
return '';
}
$temp = array('1', '2', '3', '4', '5', '6', '7', '8', '9', '0');
$result = '';
for ($i = 0; $i < strlen($str); $i++) {
if (in_array($str[$i], $temp)) {
$result .= $str[$i];
}
}
return $result;
}
}