主要参考最后两个方法
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use ZipArchive;
class TestController extends Controller
{
/**
* 忽略
* PS:
* 1.mb_convert_encoding,iconv 可能会导致内存溢出
* 2.使用fputcsv()
*/
public function indexTwo()
{
set_time_limit(0);
Log::info("IndexTwo起始时间:" . date('Y-m-d H:i:s', time()));
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=2.csv');
header('Cache-Control: max-age=0');
$myfile = fopen('php://output', 'a');
$headlist = [
'编号1',
'编号2',
'编号3'
];
//输出Excel列名信息
foreach ($headlist as $key => $value) {
//CSV的Excel支持GBK编码，一定要转换，否则乱码
$headlist[$key] = iconv('UTF-8', 'GBK', $value);
}
//将数据通过fputcsv写到文件句柄
fputcsv($myfile, $headlist);
$select = [
"orders.*",
"order_senders.sender_name",
"order_senders.sender_phone",
"order_senders.sender_province_name",
"order_senders.sender_city_name",
"order_senders.sender_county_name",
"po.order_number as parent_order_number",
"order_senders.sender_street_name",
"order_senders.sender_detail",
"orders.order_status",
"order_expresses.express_company_name",
"order_expresses.weight_from_express",
"order_expresses.freight",
"order_expresses.remark_from_express",
"order_senders.order_id",
"order_expresses.express_number",
"order_expresses.express_company_code",
"warehouses.warehouse_name",
"order_expresses.id as order_expressesId",
DB::raw("( SELECT GROUP_CONCAT( order_waybills.warehouse_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS waybill_signCreatedAt"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.tracking_number ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS tracking_number"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.express_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id) AS express_signed_at"),
];
$query = DB::table('orders')
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->orderBy('orders.created_at', 'desc');
$query->where('orders.created_at', '>', '2019-1-23')
->where('orders.created_at', '<', '2019-4-10')
->select($select)->chunk(20000, function ($terminal) use (&$myfile) {
$data = [];
foreach ($terminal as $key => $value) {
$data['sender_name'] = mb_convert_encoding($value->sender_name, 'GBK', 'utf-8');
$data['sender_phone'] = mb_convert_encoding($value->sender_phone, 'GBK', 'utf-8');
$data['sender_province_name'] = mb_convert_encoding($value->sender_province_name, 'GBK', 'utf-8');
$data['sender_city_name'] = mb_convert_encoding($value->sender_city_name, 'GBK', 'utf-8');
$data['sender_county_name'] = mb_convert_encoding($value->sender_county_name, 'GBK', 'utf-8');
$data['parent_order_number'] = mb_convert_encoding($value->parent_order_number, 'GBK', 'utf-8');
$data['sender_street_name'] = mb_convert_encoding($value->sender_street_name, 'GBK', 'utf-8');
$data['sender_detail'] = mb_convert_encoding($value->sender_detail, 'GBK', 'utf-8');
$data['order_status'] = mb_convert_encoding($value->order_status, 'GBK', 'utf-8');
$data['express_company_name'] = mb_convert_encoding($value->express_company_name, 'GBK', 'utf-8');
$data['weight_from_express'] = mb_convert_encoding($value->weight_from_express, 'GBK', 'utf-8');
$data['freight'] = mb_convert_encoding($value->freight, 'GBK', 'utf-8');
$data['remark_from_express'] = mb_convert_encoding($value->remark_from_express, 'GBK', 'utf-8');
$data['order_id'] = mb_convert_encoding($value->order_id, 'GBK', 'utf-8');
$data['express_number'] = mb_convert_encoding($value->express_number, 'GBK', 'utf-8');
$data['express_company_code'] = mb_convert_encoding($value->express_company_code, 'GBK', 'utf-8');
$data['warehouse_name'] = mb_convert_encoding($value->warehouse_name, 'GBK', 'utf-8');
$data['order_expressesId'] = mb_convert_encoding($value->order_expressesId, 'GBK', 'utf-8');
$data['waybill_signCreatedAt'] = mb_convert_encoding($value->waybill_signCreatedAt, 'GBK', 'utf-8');
$data['tracking_number'] = mb_convert_encoding($value->tracking_number, 'GBK', 'utf-8');
$data['express_signed_at'] = mb_convert_encoding($value->express_signed_at, 'GBK', 'utf-8');
fputcsv($myfile, $data);
}
unset($data);
});
fclose($myfile);
Log::info("IndexTwo截止时间:" . date('Y-m-d H:i:s', time()));
}
/**
* 略
* 使用 offset limit 获取数据
* PS:多次使用mb_convert_encoding，可能导致内存泄漏
*/
public function indexFour()
{
set_time_limit(0);
header('Content-Encoding: UTF-8');
header("Content-type:application/vnd.ms-excel;charset=UTF-8");
header('Content-Disposition: attachment;filename=3.csv');
//打开php标准输出流以写入追加的方式打开
$fp = fopen('php://output', 'a');
//用fputcsv从数据库中导出1百万的数据,比如我们每次取1万条数据，分100步来执行
$nums = 10000;
//设置标题
$title = array('id', '编号', '姓名', '年龄'); //注意这里是小写id,否则ID命名打开会提示Excel 已经检测到"xxx.xsl"是SYLK文件,但是不能将其加载: CSV 文或者XLS文件的前两个字符是大写字母"I"，"D"时，会发生此问题。
foreach ($title as $key => $item)
$title[$key] = iconv("UTF-8", "GB2312//IGNORE", $item);
fputcsv($fp, $title);
// 获取数据
$select = [
"orders.*",
"order_senders.sender_name",
"order_senders.sender_phone",
"order_senders.sender_province_name",
"order_senders.sender_city_name",
"order_senders.sender_county_name",
"po.order_number as parent_order_number",
"order_senders.sender_street_name",
"order_senders.sender_detail",
"orders.order_status",
"order_expresses.express_company_name",
"order_expresses.weight_from_express",
"order_expresses.freight",
"order_expresses.remark_from_express",
"order_senders.order_id",
"order_expresses.express_number",
"order_expresses.express_company_code",
"warehouses.warehouse_name",
"order_expresses.id as order_expressesId",
DB::raw("( SELECT GROUP_CONCAT( order_waybills.warehouse_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS waybill_signCreatedAt"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.tracking_number ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS tracking_number"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.express_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id) AS express_signed_at"),
];
$query = DB::table('orders')
->select($select)
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->where('orders.created_at', '>', '2019-3-28')
->where('orders.created_at', '<', '2019-3-29')
->orderBy('orders.created_at', 'desc');
// 总记录数
$count = $query->count();
// 分批次数
$step = ceil($count / $nums);
for ($s = 1; $s <= $step; $s++) {
$start = ($s - 1) * $nums;
$data = $query->offset($start)->limit($nums)->get();
foreach ($data as $key => $item) {
$tmp = [];
$tmp['sender_name'] = mb_convert_encoding($item->sender_name, 'GBK', 'utf-8');
$tmp['sender_phone'] = mb_convert_encoding($item->sender_phone, 'GBK', 'utf-8');
$tmp['sender_province_name'] = mb_convert_encoding($item->sender_province_name, 'GBK', 'utf-8');
$tmp['sender_city_name'] = mb_convert_encoding($item->sender_city_name, 'GBK', 'utf-8');
$tmp['sender_county_name'] = mb_convert_encoding($item->sender_county_name, 'GBK', 'utf-8');
$tmp['parent_order_number'] = mb_convert_encoding($item->parent_order_number, 'GBK', 'utf-8');
$tmp['sender_street_name'] = mb_convert_encoding($item->sender_street_name, 'GBK', 'utf-8');
$tmp['sender_detail'] = mb_convert_encoding($item->sender_detail, 'GBK', 'utf-8');
$tmp['order_status'] = mb_convert_encoding($item->order_status, 'GBK', 'utf-8');
$tmp['express_company_name'] = mb_convert_encoding($item->express_company_name, 'GBK', 'utf-8');
$tmp['weight_from_express'] = mb_convert_encoding($item->weight_from_express, 'GBK', 'utf-8');
$tmp['freight'] = mb_convert_encoding($item->freight, 'GBK', 'utf-8');
$tmp['remark_from_express'] = mb_convert_encoding($item->remark_from_express, 'GBK', 'utf-8');
$tmp['order_id'] = mb_convert_encoding($item->order_id, 'GBK', 'utf-8');
$tmp['express_number'] = mb_convert_encoding($item->express_number, 'GBK', 'utf-8');
$tmp['express_company_code'] = mb_convert_encoding($item->express_company_code, 'GBK', 'utf-8');
$tmp['warehouse_name'] = mb_convert_encoding($item->warehouse_name, 'GBK', 'utf-8');
$tmp['order_expressesId'] = mb_convert_encoding($item->order_expressesId, 'GBK', 'utf-8');
$tmp['waybill_signCreatedAt'] = mb_convert_encoding($item->waybill_signCreatedAt, 'GBK', 'utf-8');
$tmp['tracking_number'] = mb_convert_encoding($item->tracking_number, 'GBK', 'utf-8');
$tmp['express_signed_at'] = mb_convert_encoding($item->express_signed_at, 'GBK', 'utf-8');
fputcsv($fp, $tmp);
}
unset($data);
ob_flush(); //每1万条数据就刷新缓冲区
flush();
}
fclose($fp);
}
/**
* 分片查询写入文件 合并 压缩后导出
*/
public function indexFive()
{
Log::info("起始时间:" . date('Y-m-d H:i:s', time()));
set_time_limit(0);
$select = [
"orders.*",
"order_senders.sender_name",
"order_senders.sender_phone",
"order_senders.sender_province_name",
"order_senders.sender_city_name",
"order_senders.sender_county_name",
"po.order_number as parent_order_number",
"order_senders.sender_street_name",
"order_senders.sender_detail",
"orders.order_status",
"order_expresses.express_company_name",
"order_expresses.weight_from_express",
"order_expresses.freight",
"order_expresses.remark_from_express",
"order_senders.order_id",
"order_expresses.express_number",
"order_expresses.express_company_code",
"warehouses.warehouse_name",
"order_expresses.id as order_expressesId",
DB::raw("( SELECT GROUP_CONCAT( order_waybills.warehouse_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS waybill_signCreatedAt"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.tracking_number ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS tracking_number"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.express_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id) AS express_signed_at"),
];
$query = DB::table('orders')
->select($select)
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->where('orders.created_at', '>', '2019-1-23')
->where('orders.created_at', '<', '2019-4-10')
->orderBy('orders.created_at', 'desc');
// 总记录数
$sqlCount = $query->count();
// 单个Excel记录条数
$sqlLimit = 100000;
// 循环记录初始值
$cnt = 0;
// 分割文件名数组
$fileNameArr = [];
// 分割文件名前缀标识
$mark = "mark";
// Excel列头信息
$headArr = ['编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号', '编号'];
// 检测Excel临时文件夹是否存在(按日期生成目录)
$fileTmpDir = public_path('Export/FileTmp/' . date('Ymd'));
if (!is_dir($fileTmpDir))
mkdir($fileTmpDir, 0777, true);
$bom = chr(0xEF) . chr(0xBB) . chr(0xBF);
// 写入数据
for ($i = 0; $i < ceil($sqlCount / $sqlLimit); $i++) {
$fileName = $fileTmpDir . '/' . $mark . "_" . microtime(true) . '_' . $i . ".csv";
$fp = fopen($fileName, "w");
// 输出Excel列头信息
foreach ($headArr as $key => $value) {
if ($key == 0) {
$headArr[$key] = $bom . $value; // 添加Bom头解决乱码问题
} else {
$headArr[$key] = $value;
}
}
fputcsv($fp, $headArr);
// 保存文件名称
$fileNameArr[] = $fileName;
// 获取分片数据
$dataArr = $query->offset($i * $sqlLimit)->limit($sqlLimit)->get()->toArray();
// 数据处理
foreach ($dataArr as $item) {
$item = json_decode(json_encode($item), true);
$tmpItemArr = []; // 数据编码处理
$tmpItemArr['sender_name'] = $bom . $item['sender_name'];
$tmpItemArr['sender_phone'] = $item['sender_phone'];
$tmpItemArr['sender_province_name'] = $item['sender_province_name'];
$tmpItemArr['sender_city_name'] = $item['sender_city_name'];
$tmpItemArr['sender_county_name'] = $item['sender_county_name'];
$tmpItemArr['parent_order_number'] = $item['parent_order_number'];
$tmpItemArr['sender_street_name'] = $item['sender_street_name'];
$tmpItemArr['sender_detail'] = $item['sender_detail'];
$tmpItemArr['order_status'] = $item['order_status'];
$tmpItemArr['express_company_name'] = $item['express_company_name'];
$tmpItemArr['weight_from_express'] = $item['weight_from_express'];
$tmpItemArr['freight'] = $item['freight'];
$tmpItemArr['remark_from_express'] = $item['remark_from_express'];
$tmpItemArr['order_id'] = $item['order_id'];
$tmpItemArr['express_number'] = $item['express_number'];
$tmpItemArr['express_company_code'] = $item['express_company_code'];
$tmpItemArr['warehouse_name'] = $item['warehouse_name'];
$tmpItemArr['order_expressesId'] = $item['order_expressesId'];
$tmpItemArr['waybill_signCreatedAt'] = $item['waybill_signCreatedAt'];
$tmpItemArr['tracking_number'] = $item['tracking_number'];
$tmpItemArr['express_signed_at'] = $item['express_signed_at'];
$cnt++;
if ($cnt == $sqlLimit) {
ob_flush();
flush();
$cnt = 0;
}
fputcsv($fp, $tmpItemArr);
unset($tmpItemArr);
}
fclose($fp);
}
// 生成Zip包并导出
$zip = new \ZipArchive();
$zipName = $mark . '_' . microtime(true) . '.zip';
// 检测Zip文件夹是否存在(按日期生成目录)
$zipTmpDir = public_path('Export/ZipTmp/' . date('Ymd'));
if (!is_dir($zipTmpDir))
mkdir(iconv("UTF-8", "GBK", $zipTmpDir), 0777, true);
// 打开压缩包
$zip->open($zipTmpDir . '/' . $zipName, ZipArchive::CREATE);
foreach ($fileNameArr as $fileNameValue) {
$zip->addFile($fileNameValue, basename($fileNameValue));
}
$zip->close();
// 删除Excel临时文件
foreach ($fileNameArr as $fileNameValue) {
unlink($fileNameValue);
}
header('Cache-Control: max-age=0');
header('Content-Description: File Transfer');
header('Content-Disposition: attachment;filename=' . $zipName);
header('Content-type: application/zip');
header('Content-Transfer-Encoding: binary');
header('Content-Length: ' . filesize($zipTmpDir . '/' . $zipName));
@readfile($zipTmpDir . '/' . $zipName);
unlink($zipTmpDir . '/' . $zipName);
Log::info("截止时间:" . date('Y-m-d H:i:s', time()));
}
/**
* 导出CSV(分片查询写入文件)
*/
public function indexSix()
{
set_time_limit(0);
Log::info("起始时间:" . date('Y-m-d H:i:s', time()));
// 保存目录
$dir_path = public_path('Six');
if (!is_dir($dir_path))
mkdir($dir_path, 0777, true);
// 文件名
$fileName = microtime(true) . '.csv';
// 检测文件是否已存在
if (file_exists($dir_path . '/' . $fileName))
unlink($dir_path . '/' . $fileName);
// 打开文件
$myfile = fopen($dir_path . '/' . $fileName, "w") or die("Unable to open file!");
// Excel列表头
$head = "\""
. '编号1' . "\",\""
. '编号2' . "\",\""
. '编号3' . "\",\""
. '编号4' . "\",\""
. '编号5' . "\",\""
. '编号6' . "\",\""
. '编号7' . "\",\""
. '编号8' . "\",\""
. '编号9' . "\",\""
. '编号10' . "\",\""
. '编号11' . "\",\""
. '编号12' . "\",\""
. '编号13'
. "\"\n";
$head = mb_convert_encoding($head, 'GBK', 'utf-8');
// 写入头部信息
fwrite($myfile, $head);
// 写入内容
$select = [
"orders.*",
"order_senders.sender_name",
"order_senders.sender_phone",
"order_senders.sender_province_name",
"order_senders.sender_city_name",
"order_senders.sender_county_name",
"po.order_number as parent_order_number",
"order_senders.sender_street_name",
"order_senders.sender_detail",
"orders.order_status",
"order_expresses.express_company_name",
"order_expresses.weight_from_express",
"order_expresses.freight",
"order_expresses.remark_from_express",
"order_senders.order_id",
"order_expresses.express_number",
"order_expresses.express_company_code",
"warehouses.warehouse_name",
"order_expresses.id as order_expressesId",
DB::raw("( SELECT GROUP_CONCAT( order_waybills.warehouse_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS waybill_signCreatedAt"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.tracking_number ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS tracking_number"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.express_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id) AS express_signed_at"),
];
$query = DB::table('orders')
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->orderBy('orders.created_at', 'desc');
$query->where('orders.created_at', '>', '2019-1-23')
->where('orders.created_at', '<', '2019-4-11')
->select($select)->chunk(20000, function ($terminal) use (&$myfile) {
foreach ($terminal as $key => $value) {
$txt = "\""
. $value->sender_name . "\t\",\""
. $value->sender_phone . "\t\",\""
. $value->sender_province_name . "\t\",\""
. $value->sender_city_name . "\t\",\""
. $value->sender_county_name . "\t\",\""
. $value->parent_order_number . "\t\",\""
. $value->sender_street_name . "\t\",\""
. $value->sender_detail . "\t\",\""
. $value->order_status . "\t\",\""
. $value->express_company_name . "\t\",\""
. $value->weight_from_express . "\t\",\""
. $value->freight . "\t\",\""
. $value->remark_from_express . "\t\",\""
. $value->order_id . "\t\",\""
. $value->express_number . "\t\",\""
. $value->express_company_code . "\t\",\""
. $value->warehouse_name . "\t\",\""
. $value->order_expressesId . "\t\",\""
. $value->waybill_signCreatedAt . "\t\",\""
. $value->tracking_number . "\t\",\""
. $value->express_signed_at . "\t\",\""
. "\t\"\n";
$txt = mb_convert_encoding($txt, 'GBK', 'utf-8');
fwrite($myfile, $txt);
unset($txt);
}
});
fclose($myfile);
// 浏览器下载
header('Cache-Control: max-age=0');
header('Content-Description: File Transfer');
header('Content-Disposition: attachment;filename=' . $fileName);
header('Content-type: application/vnd.ms-excel');
header('Content-Transfer-Encoding: binary');
header('Content-Length: ' . filesize($dir_path . '/' . $fileName));
@readfile($dir_path . '/' . $fileName);
// 删除临时文件
unlink($dir_path . '/' . $fileName);
Log::info("截止时间:" . date('Y-m-d H:i:s', time()));
}
}
Laravel写法
此方法实际测试过，多表关联查询，百万数据5min，万级数据秒级别
<?php
namespace App\Http\Controllers\AdminV1\Excel;
use App\Order;
use function GuzzleHttp\Psr7\str;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use mysql_xdevapi\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Illuminate\Support\Facades\Log;
use \ZipArchive;
class OrderExcelExportController extends Controller
{
/**
*
* @param Request $request
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
* @throws \Exception
*/
public function orderList(Request $request)
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
Log::notice('excel导出请求数据：'.json_encode($request->all()));
$titles = [
'商家订单编号',
'平台订单编号',
'物流单号',
'用户名',
'电话',
'省/直辖市',
'市',
'县/区',
'详细地址',
'订单状态',
'预计收件时间',
'预计重量',
'下单时间',
'父级订单编号',
'快递公司',
'实际重量',
'实际费用',
'签收仓库',
'快递公司备注',
'取消原因',
'仓库签收时间',
'运单号'
];
$limit = 10000; //每个excel多少条数据+
$excelPaths = [];
for ($i = 0; $i < 9999; $i++) {
$offset = $i * $limit;
$data = $this->orderListData($request, $offset, $limit);
if ($data->isEmpty()) {
break;
}
$number = $i + 1;
$excelPaths[] = $this->collectionToZipPath($data, $titles, $excelName = time() . $number);
}
$zipFileName = $this->excelToZip($excelPaths);
return $this->orderExcelDown($zipFileName);
}
/**
* 创建csv写入
*
* @param $collection
* @param array $titles
* @param $excelName
* @return string
*/
private function collectionToZipPath($collection, array $titles, $excelName)
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
$path = storage_path('app/excelOriginal/' . $excelName . '.csv');
$fp = fopen($path, 'a'); //打开php文件句柄，php://output表示直接输出到PHP缓存,a表示将输出的内容追加到文件末尾
$head = $titles; //表头信息
foreach ($head as $k => $v) {
$head[$k] = iconv("UTF-8", "GBK//IGNORE", $v); //将utf-8编码转为gbk。理由是： Excel 以 ANSI 格式打开，不会做编码识别。如果直接用 Excel 打开 UTF-8 编码的 CSV 文件会导致汉字部分出现乱码。
}
fputcsv($fp, $head); //fputcsv() 函数将行格式$head化为 CSV 并写入一个打开的文件$fp。
if (!empty($collection)) {
$data = []; //要导出的数据的顺序与表头一致；提前将最后的值准备好（比如：时间戳转为日期等）
foreach ($collection as $key => &$val) {
switch ($val['order_status']) {
case 'CREATED':
$val['order_status'] = '创建成功';
break;
case 'ASSIGNED':
$val['order_status'] = '等待收衣';
break;
case 'RECEIVED':
$val['order_status'] = '已收衣';
break;
case 'SIGNED':
$val['order_status'] = '已签收';
break;
case 'CANCELLED':
$val['order_status'] = '已取消';
break;
default:
$val['order_status'] = '未知状态';
}
// $val['business_order_number'] = $val['business_order_number'].'';
foreach ($val->toArray() as $i => $item) { //$item为一维数组哦
$data[$i] = iconv("UTF-8", "GBK//IGNORE", $item); //转为gbk的时候可能会遇到特殊字符‘-’之类的会报错，加 ignore表示这个特殊字符直接忽略不做转换。
}
fputcsv($fp, $data);
}
return $path; //记得加这个，不然会跳转到某个页面。
}
}
/**
* 下载文件
*
* @param $zipFileName
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
*/
private function orderExcelDown($zipFileName)
{
$aliasFileName = date("Ymd", time()) . '.zip';
return response()->download($zipFileName, $aliasFileName)->deleteFileAfterSend(true);
}
/**
* 压缩文件
*
* @param $excelPaths
* @return string
* @throws \Exception
*/
public function excelToZip($excelPaths)
{
$zipFileName = storage_path('app/excelZip/' . time() . mt_rand(1111, 9999) . '.zip'); //压缩包（ZIP）名称
$zip = new \ZipArchive;
if ($zip->open($zipFileName, \ZipArchive::CREATE) !== TRUE) {
throw new \Exception('创建压缩文件失败');
}
foreach ($excelPaths as $f) {
if (!empty($f)) {
$zip->addFromString(pathinfo($f)['basename'], file_get_contents($f));
}
unlink($f);
}
$zip->close();
return $zipFileName;
}
/**
* 查询数据
*
* @param $request
* @param $offset
* @param $limit
* @return Order[]|\Illuminate\Database\Eloquent\Collection|\Illuminate\Database\Query\Builder[]|\Illuminate\Support\Collection
*/
public function orderListData($request, $offset, $limit)
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
$order = Order::select('*');
$where = [];
$order->Where($where);
if ($request->starting_time && $request->end_time) {
$where = [
['orders.created_at', '>', $request->starting_time],
['orders.created_at', '<', $request->end_time]
];
$order->Where($where);
}
if ($request->order_status) {
$where[] = ['orders.order_status', '=', $request->order_status];
$order->Where($where);
}
if ($request->start_weight || $request->end_weight) {
if ($request->end_weight == '' && $request->start_weight == 200) {
$where[] = ['orders.estimated_weight', '>', $request->start_weight];
} else if ($request->end_weight == '' && $request->start_weight == 5) {
$where[] = ['orders.estimated_weight', '<', $request->start_weight];
} else {
$request->start_weight and $where[] = ['orders.estimated_weight', '>', $request->start_weight];
$request->end_weight and $where[] = ['orders.estimated_weight', '<', $request->end_weight];
}
$order->Where($where);
}
if ($request->search_universal) {
$order->Where(function ($query) use ($request) {
if ($request->searchType == 'phone') {
$where[] = ['order_senders.sender_phone', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'trackingNumber') {
$query->orWhereRaw("(select count(*) from order_waybills as has_order_waybill where has_order_waybill.tracking_number = '{$request->search_universal}' and has_order_waybill.order_express_id = order_expresses.id )>0");
//TODO 存在SQL注入安全问题，改为查询构造器即可解决
} else if ($request->searchType == 'express') {
$where[] = ['order_expresses.express_company_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'sender') {
$where[] = ['order_senders.sender_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'province') {
$where[] = ['order_senders.sender_province_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'city') {
$where[] = ['order_senders.sender_city_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'business_order_number') {
$where[] = ['orders.business_order_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'platform_order_number') {
$where[] = ['orders.order_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'logisticsNumber') {
$where[] = ['order_expresses.express_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'merchant_order_number') {
$where[] = ['orders.business_id', '=', $request->search_universal];
$query->orWhere($where);
}
});
}
DB::enableQueryLog();
$data = $order
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
// ->leftJoin('order_waybills', 'order_expresses.id', '=', 'order_waybills.order_express_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->select(
'orders.business_order_number',
'orders.order_number',
'order_expresses.express_number',
'order_senders.sender_name',
'order_senders.sender_phone',
'order_senders.sender_province_name',
'order_senders.sender_city_name',
'order_senders.sender_county_name',
'order_senders.sender_detail',
'orders.order_status',
'orders.meet_at',
'orders.estimated_weight',
'orders.created_at',
'po.order_number as parent_order_number',
'order_expresses.express_company_name',
'order_expresses.weight_from_express',
'order_expresses.freight',
'warehouses.warehouse_name',
'order_expresses.remark_from_express',
'orders.cancelled_reason'
)
->selectRaw('(SELECT
GROUP_CONCAT(order_waybills.warehouse_signed_at)
FROM
order_waybills
WHERE
order_expresses.id = order_waybills.order_express_id
) AS waybill_signCreatedAt'
)
->selectRaw('(SELECT
GROUP_CONCAT(order_waybills.tracking_number)
FROM
order_waybills
WHERE
order_expresses.id = order_waybills.order_express_id
) AS tracking_number'
)
->orderBy('orders.created_at', 'desc')
->offset($offset)
->limit($limit)->get();
return $data;
}
/**************************************************jason订单导出开始****************************************************/
public function orderCsvExport(Request $request)
{
try {
// 执行时间(0 无上限)
set_time_limit(0);
// 内存限制(-1 无上限)
ini_set('memory_limit', '1024M');
// 程序执行时间(0无上限)
ini_set('max_execution_time', 0);
// 日志开始
Log::info("订单导出开始|时间:" . date('Y-m-d H:i:s', time()) . "|");
// 设置临时目录
$dirPath = storage_path('app/OrderExport');
if (!is_dir($dirPath))
mkdir($dirPath, 0777, true);
// 文件名
$fileName = date("YmdHis") . '.csv';
// 检测文件是否已存在(存在则删除)
if (file_exists($dirPath . '/' . $fileName))
unlink($dirPath . '/' . $fileName);
// 打开文件
$myFile = fopen($dirPath . '/' . $fileName, "w");
// BOM头(解决CSV乱码问题)
$bom = chr(0xEF) . chr(0xBB) . chr(0xBF);
// Excel列表头
$head =
$bom
. '商家订单编号' . ",\""
. '平台订单编号' . "\",\""
. '物流单号' . "\",\""
. '用户名' . "\",\""
. '电话' . "\",\""
. '省/直辖市' . "\",\""
. '市' . "\",\""
. '县/区' . "\",\""
. '详细地址' . "\",\""
. '订单状态' . "\",\""
. '预计收件时间' . "\",\""
. '预计重量' . "\",\""
. '下单时间' . "\",\""
. '父级订单编号' . "\",\""
. '快递公司' . "\",\""
. '实际重量' . "\",\""
. '实际费用' . "\",\""
. '签收仓库' . "\",\""
. '快递公司备注' . "\",\""
. '取消原因' . "\",\""
. '仓库签收时间' . "\",\""
. '运单号'
. "\"\n";
// 写入头部信息
fwrite($myFile, $head);
// 写入订单数据
// 查询字段
$select = [
'orders.business_order_number',
'orders.order_number',
'order_expresses.express_number',
'order_senders.sender_name',
'order_senders.sender_phone',
'order_senders.sender_province_name',
'order_senders.sender_city_name',
'order_senders.sender_county_name',
'order_senders.sender_detail',
DB::raw("CASE orders.order_status WHEN 'CREATED' THEN '创建成功' WHEN 'ASSIGNED' THEN '等待收衣' WHEN 'RECEIVED' THEN '已收衣' WHEN 'SIGNED' THEN '已签收' WHEN 'CANCELLED' THEN '已取消' ELSE '未知状态' END as order_status"),
'orders.meet_at',
'orders.estimated_weight',
'orders.created_at',
'po.order_number as parent_order_number',
'order_expresses.express_company_name',
'order_expresses.weight_from_express',
'order_expresses.freight',
'warehouses.warehouse_name',
'order_expresses.remark_from_express',
'orders.cancelled_reason',
DB::raw("( SELECT GROUP_CONCAT( order_waybills.warehouse_signed_at ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS waybill_signCreatedAt"),
DB::raw("( SELECT GROUP_CONCAT( order_waybills.tracking_number ) FROM order_waybills WHERE order_expresses.id = order_waybills.order_express_id ) AS tracking_number")
];
// 实例化
$order = Order::select($select);
// 搜索条件
// 日期
$starting_time = isset($request->starting_time) ? $request->starting_time : date("Y-m-d");
$end_time = isset($request->end_time) ? $request->end_time : date('Y-m-d H:i:s', mktime(23, 23, 59, date('m'), date('d'), date('Y')));
$where = [
['orders.created_at', '>', $starting_time],
['orders.created_at', '<', $end_time]
];
// 重量
if (!empty($request->start_weight) || !empty($request->end_weight)) {
if ($request->end_weight == '' && $request->start_weight == 200) {
$where[] = ['orders.estimated_weight', '>', $request->start_weight];
} else if ($request->end_weight == '' && $request->start_weight == 5) {
$where[] = ['orders.estimated_weight', '<', $request->start_weight];
} else {
$request->start_weight and $where[] = ['orders.estimated_weight', '>', $request->start_weight];
$request->end_weight and $where[] = ['orders.estimated_weight', '<', $request->end_weight];
}
}
// 预约状态
if (!empty($request->order_status)) {
$where[] = ['orders.order_status', '=', $request->order_status];
}
// 其他条件
if (!empty($request->search_universal)) {
$order->Where(function ($query) use ($request) {
if ($request->searchType == 'phone') {
$where[] = ['order_senders.sender_phone', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'trackingNumber') {
$query->orWhereRaw("(select count(*) from order_waybills as has_order_waybill where has_order_waybill.tracking_number = '{$request->search_universal}' and has_order_waybill.order_express_id = order_expresses.id )>0");
//TODO 存在SQL注入安全问题，改为查询构造器即可解决
} else if ($request->searchType == 'express') {
$where[] = ['order_expresses.express_company_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'sender') {
$where[] = ['order_senders.sender_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'province') {
$where[] = ['order_senders.sender_province_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'city') {
$where[] = ['order_senders.sender_city_name', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'business_order_number') {
$where[] = ['orders.business_order_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'platform_order_number') {
$where[] = ['orders.order_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'logisticsNumber') {
$where[] = ['order_expresses.express_number', '=', $request->search_universal];
$query->orWhere($where);
} else if ($request->searchType == 'merchant_order_number') {
$where[] = ['orders.business_id', '=', $request->search_universal];
$query->orWhere($where);
}
});
}
$query = $order
->leftJoin('order_expresses', 'orders.id', '=', 'order_expresses.order_id')
->leftJoin('order_senders', 'orders.id', '=', 'order_senders.order_id')
->leftJoin('orders as po', 'po.id', '=', 'orders.parent_id')
->leftJoin('order_receivers', 'orders.id', '=', 'order_receivers.order_id')
->leftJoin('warehouses', 'order_receivers.warehouse_code', '=', 'warehouses.warehouse_code')
->where($where)
->orderBy('orders.created_at', 'desc');
// 分片取消数据写入文件
$query->chunk(30000, function ($data) use (&$myFile) {
foreach ($data as $key => $value) {
$tmp = [];
$tmp[] = "\t". $value->business_order_number;
$tmp[] = "\t". $value->order_number;
$tmp[] = "\t". $value->express_number;
$tmp[] = "\t". $value->sender_name;
$tmp[] = "\t". $value->sender_phone;
$tmp[] = "\t". $value->sender_province_name;
$tmp[] = "\t". $value->sender_city_name;
$tmp[] = "\t". $value->sender_county_name;
$tmp[] = "\t". $value->sender_detail;
$tmp[] = "\t". $value->order_status;
$tmp[] = "\t". $value->meet_at;
$tmp[] = "\t". $value->estimated_weight;
$tmp[] = "\t". $value->created_at;
$tmp[] = "\t". $value->parent_order_number;
$tmp[] = "\t". $value->express_company_name;
$tmp[] = "\t". $value->weight_from_express;
$tmp[] = "\t". $value->freight;
$tmp[] = "\t". $value->warehouse_name;
$tmp[] = "\t". $value->remark_from_express;
$tmp[] = "\t". $value->cancelled_reason;
$tmp[] = "\t". $value->waybill_signCreatedAt;
$tmp[] = "\t". $value->tracking_number;
fputcsv($myFile, $tmp);
unset($tmp);
}
unset($data);
});
fclose($myFile);
// 日志结束
Log::info("订单导出结束|时间:" . date('Y-m-d H:i:s', time()) . "|");
// 下载文件并删除本地文件
return $this->orderCsvDown($dirPath . '/' . $fileName);
} catch (\Exception $e) {
Log::error("订单导出失败|时间:" . date("Y-m-d H:i:s") . "|错误行号:" . $e->getLine() . "|错误原因:" . $e->getMessage() . "|");
}
}
/**
* 下载
* @param $filePath
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
*/
private function orderCsvDown($filePath)
{
$aliasFileName = "订单信息_" . date('YmdHis') . ".csv";
return response()->download($filePath, $aliasFileName)->deleteFileAfterSend(true);
}
/**************************************************jason订单导出结束****************************************************/
}
扫一扫
388
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
