之前使用PHP导出Excel,会出现格式错误和数据限制的问题,所以后来采用了CSV文件的导出,而且效率比较快
下面是PHP方法
$excel_filename = '订单列表_' . date('Ymd_His');
$headlist = array('订单ID', '商品名', '下单用户ID', '下单用户昵称', '所属名片', '商品金额', '运费', '会员折扣', '砍价金额', '订单优惠', '优惠券优惠', '总金额', '余额抵扣', '积分抵扣', '需付款', '实付款', '收货地址', '商户订单号', '订单完成时间', '订单类型', '订单来源', '状态', '预交付时间');
csv_export($list, $headlist, $excel_filename);
exit ();
function csv_export ($data = array(), $headlist = array(), $fileName)
{
header('CSV-Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
header('Cache-Control: max-age=0');
//打开PHP文件句柄,php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
//输出Excel列名信息
foreach ($headlist as $key => $value) {
//CSV的Excel支持GBK编码,一定要转换,否则乱码
$headlist[$key] = iconv('utf-8', 'gbk', $value);
}
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $headlist);
//计数器
$num = 0;
//每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
$limit = 100000;
//逐行取出数据,不浪费内存
$count = count($data);
for ($i = 0; $i < $count; $i++) {
$num++;
//刷新一下输出buffer,防止由于数据过多造成问题
if ($limit == $num) {
ob_flush();
flush();
$num = 0;
}
$row = $data[$i];
if ($row['type'] == 1) {
$order_types = '拼团订单';
} elseif ($row['type'] == 3) {
$order_types = "线下支付订单";
} else {
$order_types = "普通订单";
}
if ($row['pay_status'] == 1 && $row['order_status'] == 3) {
# 完成订单
$update_time = date('Y-m-d H:i:s', $row['update_time']);
} else {
$update_time = '';
}
$cellvalue['id'] = iconv('utf-8', 'gbk', $row['id']);
$cellvalue['names'] = iconv('utf-8', 'gbk', $row['names']);
$cellvalue['user_id'] = iconv('utf-8', 'gbk', $row['user_id']);
$cellvalue['nickName'] = iconv('utf-8', 'gbk', $row['user_info']['nickName']);
$cellvalue['name'] = iconv('utf-8', 'gbk', $row['staff_info']['name']);
$cellvalue['price'] = iconv('utf-8', 'gbk', $row['price']);
$cellvalue['freight'] = iconv('utf-8', 'gbk', $row['freight']);
$cellvalue['vip_reduce'] = iconv('utf-8', 'gbk', $row['vip_reduce']);
$cellvalue['kanjia_price'] = iconv('utf-8', 'gbk', $row['kanjia_price']);
$cellvalue['modify_reduce'] = iconv('utf-8', 'gbk', $row['modify_reduce']);
$cellvalue['record_money'] = iconv('utf-8', 'gbk', $row['record_money']);
$cellvalue['total_price'] = iconv('utf-8', 'gbk', $row['total_price']);
$cellvalue['use_balance'] = iconv('utf-8', 'gbk', $row['use_balance']);
$cellvalue['use_integral'] = iconv('utf-8', 'gbk', $row['use_integral']);
$cellvalue['total_price2'] = iconv('utf-8', 'gbk', $row['total_price']);
$cellvalue['real_price'] = iconv('utf-8', 'gbk', $row['real_price']);
$cellvalue['address_detail'] = iconv('utf-8', 'gbk', $row['name'] . $row['phone'] . $row['address'] . $row['address_detail']);
$cellvalue['out_trade_no'] = iconv('utf-8', 'gbk', $row['out_trade_no']);
$cellvalue['out_trade_no2'] = @mb_convert_encoding($update_time, 'GBK', 'UTF-8') . "\t";
$cellvalue['out_trade_no3'] = iconv('utf-8', 'gbk', $order_types);
$cellvalue['platform'] = iconv('utf-8', 'gbk', $row['platform']);
$cellvalue['order_step_text'] = iconv('utf-8', 'gbk', $row['order_step_text']);
$cellvalue['deliver_time'] = @mb_convert_encoding($row['deliver_time'], 'GBK', 'UTF-8') . "\t";
fputcsv($fp, $cellvalue);
}