方法集 · 开发笔记

#### 使用fputcsv导出csv数据文件 ~~~ /** * 创建csv文件写入数据并下载(万级以下数据量) * @param type var 数据主体，格式为二维数组，例如：[['name'=>'Jim','sex'=>'boy'],['name'=>'Lucy','sex'=>'girl']] * @param array $title 表格标题，格式为二维数组，例如：[['name'=>'name','text'=>'姓名'],['name'=>'sex','text'=>'性别']] * @param string $fileName 文件名[可选] */ function export_csv($data = [], $title = [], $fileName = false) { $fileName = $fileName ? mb_convert_encoding($fileName, "GB2312", "UTF-8, GB2312") . ".csv" : date("YmdHis") . time() . ".csv"; header('Content-Type: text/csv; CHARSET=gb2312'); header('Content-Disposition: attachment; filename=' . $fileName); $output = fopen('php://output', 'w'); $titleText = array_column($title, 'text'); $titleName = array_column($title, 'name'); $withSerial = in_array('序号', $titleText) ? true : false; unset($title); array_walk($titleText, function (&$item) { $item = mb_convert_encoding($item, "GB2312", "UTF-8, GB2312"); }); $content = [$titleText]; unset($titleText); $serial = 1; foreach ($data as $record) { $row = []; $item = $record; if ($withSerial) { $item['serial'] = $serial; } foreach ($titleName as $v) { $row[] = mb_convert_encoding($item[$v], "GB2312", "UTF-8, GB2312"); } $content[] = $row; $serial++; } unset($data); unset($titleName); unset($withSerial); unset($serial); foreach ($content as $row) { fputcsv($output, $row); } return true; } /** * 创建csv文件写入数据并下载(万级以上数据量) */ public function downloadFile() { if (IS_GET) { $beginTime = time(); echo "开始: " . date('Y-m-d H:i:s') . " "; echo "初始内存占用: " . memory_get_usage() . " "; // 接收查询参数 $param = I('get.'); $folder = './Public/Temp'; $filename = mb_convert_encoding($param['activity_name'], "GB2312", "UTF-8, GB2312"); $timestr = time() . mt_rand(111, 999) . mt_rand(111, 999); $file = $folder . '/' . $filename . '_' . $timestr . '.csv'; // 创建文件夹 if (!file_exists($filename)) { @mkdir($folder, 0777); } // 创建文件 if (!file_exists($$file)) { @mkdir($$file, 0777); } // 获得查询语句 $query = D('AdMonitor')->getexportData($param, true); // 处理文件标题 $title = C('Reflux_EXPORT_TITLE'); $titleText = array_column($title, 'text'); $titleName = array_column($title, 'name'); $withSerial = in_array('序号', $titleText) ? true : false; unset($title); array_walk($titleText, function (&$item) { $item = mb_convert_encoding($item, "GB2312", "UTF-8, GB2312"); }); // 打开文件并写入文件标题 $fp = fopen($file, "w"); fputcsv($fp, $titleText); // 查询数据 $dbna = empty(session('datadb')) ? C('DB_NAME') : session('datadb'); $conn = mysql_connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD')); mysql_select_db($dbna); // 设置SQL编码 mysql_query('set names utf8'); $result = mysql_unbuffered_query($query['data'], $conn); // 写入数据 $serial = 1; while ($record = mysql_fetch_array($result, MYSQL_ASSOC)) { // 转换数据 $row = []; // 序号 if ($withSerial) { $row[] = $serial; } foreach ($titleName as $v) { if (isset($record[$v])) { $row[] = mb_convert_encoding($record[$v], "GB2312", "UTF-8, GB2312"); } } // 写入行 fputcsv($fp, $row); $serial++; } echo "执行中内存占用: " . memory_get_usage() . " "; // 释放数据库连接 mysql_free_result($result); // 关闭文件 fclose($fp); echo "释放后内存占用: " . memory_get_usage() . " "; echo "内存占用峰值: " . memory_get_peak_usage() . " "; $endTime = time(); echo "结束: " . date('Y-m-d H:i:s') . " "; echo "耗时: " . ($endTime - $beginTime) . '秒'; // 下载文件 Http::download($file); } else { $this->ajaxReturn(['info' => '非法请求', 'status' => 0]); } } ~~~ #### CURL请求接口 ~~~ /** * 发送HTTP请求方法 * @param string $url 请求URL * @param array $params 请求参数 * @param string $method 请求方法GET/POST * @param array $header 请求头 * @param boolean $multi 是否允许参数以数组形式提交 * @return array 响应数据 */ function curl_request($url, $params, $method = 'GET', $header = array(), $multi = false) { $opts = array( CURLOPT_TIMEOUT => 30, CURLOPT_RETURNTRANSFER => 1, CURLOPT_SSL_VERIFYPEER => false, CURLOPT_SSL_VERIFYHOST => false, CURLOPT_HTTPHEADER => $header, ); /* 根据请求类型设置特定参数 */ switch (strtoupper($method)) { case 'GET': $opts[CURLOPT_URL] = $url . '?' . http_build_query($params); break; case 'POST': //判断是否传输文件 $params = $multi ? $params : http_build_query($params); $opts[CURLOPT_URL] = $url; $opts[CURLOPT_POST] = 1; $opts[CURLOPT_POSTFIELDS] = $params; break; default: throw new Exception('不支持的请求方式！'); } /* 初始化并执行curl请求 */ $ch = curl_init(); curl_setopt_array($ch, $opts); $data = curl_exec($ch); $error = curl_error($ch); curl_close($ch); if ($error) { throw new Exception('请求发生错误：' . $error); } return $data; } ~~~ #### PHP快速读取超大文件(csv,txt) 1.http://www.thinkphp.cn/code/1019.html 2.http://blog.csdn.net/ebw123/article/details/46651499 #### PHP快速读取超大文件(excel) 1.https://www.cnblogs.com/anniu1122/p/6593522.html #### PHP导出百万级数据到csv 1.http://blog.csdn.net/lzj_dandan/article/details/78490687?locationNum=7&fps=1