Laravel/Excel导出订单
2019-10-12 本文已影响0人
三喵w
本导出使用了maatwebsite/excel 3.1版本composer包
导出包含 订单数据，商品数据，付款数据，退款数据
样式包含 水平垂直居中，合并单元格，设置行高
控制器中
public function exportOrder() {
return new OrderExport();
}
Export目录中 OrderExport导出类
<?php
namespace App\Exports;
use App\Order;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Excel;
use PhpOffice\PhpSpreadsheet\Exception;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Illuminate\Contracts\Support\Responsable;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
class OrderExport implements FromArray, WithTitle, Responsable, ShouldAutoSize, WithEvents, WithHeadings, WithColumnFormatting, WithStrictNullComparison
{
use Exportable, RegistersEventListeners;
/**
* 响应头
* @var array
*/
private $headers = ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
/**
* excel文件名称
* @var string
*/
private $fileName = '订单信息.xlsx';
/**
* 设置导出excel的后缀类型
* @var string
*/
private $writerType = Excel::XLSX;
/**
* 从模型中获取数据，并且格式化好写入excel中
* @return array
*/
public function array(): array
{
$data = Order::query()
->select([
'order_no', 'status', 'delivery_name', 'created_at', 'cash_fee', 'logistics_price', 'cash_fee',
'cash_fee', 'cash_fee', 'delivery_name', 'delivery_mobile', 'province', 'city', 'area',
'delivery_address', 'logistic_identity', 'logistic_no', 'remarks', 'sign_at', 'send_at',
])
->with([
'orderGoods:order_no,sku_no,spu_name,category_name,sell_price,quantity,sub_price',
'payment:created_at,third_payment_no',
'payRefund:refund_fee,created_at,transaction_id'
])
->orderBy('id', 'desc')
->limit(2000)
->get()
->toArray();
return $this->format($data);
}
/**
* 设置excel第一行头信息
* @return array
*/
public function headings(): array
{
return [
'订单编号', '订单状态', '下单账户', '下单时间', '商品编号', '商品名称', '商品规格', '商品单价', '商品数量', '小计', '商品总额',
'运费', '订单总额', '实付金额', '平台实收金额', '支付时间', '支付流水号（微信支付）', '收货人姓名', '收货人联系电话', '省', '市',
'区', '详细地址', '物流公司', '物流编号', '发货时间', '收货时间', '退款总金额', '退款金额', '退款到账时间',
'退款流水单号（单号皆对应订单信息非具体商品）', '备注',
];
}
/**
* 格式化每一行要写入的数据
* @param $data
* @return array
*/
public function format($data)
{
$result = [];
foreach ($data as $item) {
foreach ($item['order_goods'] as $goods) {
$result[] = [
$item['order_no'],
$item['status'],
$item['delivery_name'],
$item['created_at'],
$goods['sku_no'],
$goods['spu_name'],
implode(',', json_decode($goods['category_name'], true)),
$goods['sell_price'] / 100,
$goods['quantity'],
$goods['sub_price'] / 100,
$item['cash_fee'] / 100,
$item['logistics_price'] / 100,
$item['cash_fee'] / 100,
$item['cash_fee'] / 100,
$item['cash_fee'] / 100,
$item['payment']['created_at'],
$item['payment']['third_payment_no'],
$item['delivery_name'],
$item['delivery_mobile'],
$item['province'],
$item['city'],
$item['area'],
$item['delivery_address'],
$item['logistic_identity'],
$item['logistic_no'],
date('Y-m-d H:i:s', $item['send_at']),
date('Y-m-d H:i:s', $item['sign_at']),
$item['pay_refund']['refund_fee'] ?? '',
$item['pay_refund']['refund_fee'] ?? '',
$item['pay_refund']['created_at'] ?? '',
$item['pay_refund']['transaction_id'] ?? '',
$item['remarks'],
];
}
}
return $result;
}
/**
* 在数据写入sheet表后, 触发此事件, 进行样式调整，宽高、合并单元格等
* @param AfterSheet $event
* @throws Exception
*/
public static function afterSheet(AfterSheet $event)
{
// 总行数(包含标题头)
$heights = $event->sheet->getDelegate()->getHighestRow();
// 总列数(字母 A B 等)
$rows = $event->sheet->getDelegate()->getHighestColumn();
//区域单元格 - 字体、颜色、背景、对齐等
$event->sheet->getDelegate()->getStyle('A1:AF' . $heights)->applyFromArray([
// vertical 水平对齐 - horizontal 垂直居中
'alignment' => [
'vertical' => Alignment::VERTICAL_CENTER,
'horizontal' => Alignment::HORIZONTAL_CENTER,
],
]);
// 设置行高，$i为数据行数
for ($i = 1; $i <= $heights; $i++) {
$event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(34);
}
// 获取每个单元格与下边的对比, 一样进行合并
$row = 'A';
do {
if (in_array($row, ['E', 'F', 'G', 'H', 'I', 'J']) === false) {
for ($height = 2; $height <= $heights; $height++) {
// 当前迭代单元格的值
$current = $event->sheet->getDelegate()->getCell($row . $height)->getValue();
// 值有效的话, 进行传递去查找下一个单元格的值进行对比
if (empty($current) === false || $current === 0) {
// 去找最后一个和当前单元格值一样的位置
$realHeight = self::matchData($row, $height, $heights, $event, $current);
// 找到最后一个一样的值后, 进行合并
if ($height != $realHeight) {
$event->sheet->getDelegate()->mergeCells("{$row}{$height}:{$row}{$realHeight}");
}
}
}
}
$row++;
} while (static::strSize($row, $rows));
}
/**
* 递归查询列中一样值, 返回最后一个一样的值得位置
* @param $row
* @param $height
* @param $heights
* @param $event
* @param $current
* @return mixed
*/
public static function matchData($row, $height, $heights, $event, $current)
{
// 获取当前列单元格的前面的订单号 和 下一个订单号
$currentOrder = $event->sheet->getDelegate()->getCell('A' . $height)->getValue();
$nextOrder = $event->sheet->getDelegate()->getCell('A' . ($height + 1))->getValue();
// 如果是订单号一样, 或者下一个单元格的订单号值为null, 那么说明要合并的值是同一个订单的信息
if ($currentOrder === $nextOrder || $nextOrder === null) {
// 如果是最后一个单元, 不进行递归查找是否还有一样的值
if ($height == $heights) {
return $height;
}
// 下一个值得坐标
$nextHeight = $height + 1;
// 拿到下一个单元的值
$down = $event->sheet->getDelegate()->getCell($row . $nextHeight)->getValue();
// 当前的值和下一个值一样, 就继续找后面一个值得位置
if ($down == $current) {
return self::matchData($row, $nextHeight, $heights, $event, $current);
}
}
// 不是一个订单,就返回原单元的位置,不向下找
return $height;
}
/**
* 匹配两个字母的大小 A B AA AB 此类型的值
* @param $str1
* @param $str2
* @return bool
*/
public static function strSize($str1, $str2)
{
$len1 = strlen($str1);
$len2 = strlen($str2);
// 左边短, 右边长
if ($len1 < $len2) {
return true;
}
// 左右一样长
for ($i = 0; $i < $len1; $i++) {
$outChar1 = substr($str1, $i, 1);
$outChar2 = substr($str2, $i, 1);
if ($outChar1 < $outChar2) {
return true;
}
if ($outChar1 > $outChar2) {
return false;
}
if ($outChar1 == $outChar2) {
$char1 = substr($str1, $i + 1, 1);
$char2 = substr($str2, $i + 1, 1);
if ($char1 <= $char2) {
return true;
}
if ($char1 > $char2) {
return false;
}
}
}
}
/**
* 设置每一列的值类型
* @return array
*/
public function columnFormats(): array
{
return [
'A' => NumberFormat::FORMAT_NUMBER,
'B' => NumberFormat::FORMAT_NUMBER,
'C' => NumberFormat::FORMAT_TEXT,
'D' => NumberFormat::FORMAT_TEXT,
'E' => NumberFormat::FORMAT_TEXT,
'F' => NumberFormat::FORMAT_TEXT,
'G' => NumberFormat::FORMAT_TEXT,
'H' => NumberFormat::FORMAT_NUMBER_00,
'I' => NumberFormat::FORMAT_NUMBER,
'J' => NumberFormat::FORMAT_NUMBER_00,
'K' => NumberFormat::FORMAT_NUMBER_00,
'L' => NumberFormat::FORMAT_NUMBER_00,
'M' => NumberFormat::FORMAT_NUMBER_00,
'N' => NumberFormat::FORMAT_NUMBER_00,
'O' => NumberFormat::FORMAT_NUMBER_00,
'P' => NumberFormat::FORMAT_TEXT,
'Q' => NumberFormat::FORMAT_NUMBER,
'R' => NumberFormat::FORMAT_TEXT,
'S' => NumberFormat::FORMAT_NUMBER,
'T' => NumberFormat::FORMAT_TEXT,
'U' => NumberFormat::FORMAT_TEXT,
'V' => NumberFormat::FORMAT_TEXT,
'W' => NumberFormat::FORMAT_TEXT,
'X' => NumberFormat::FORMAT_TEXT,
'Y' => NumberFormat::FORMAT_TEXT,
'Z' => NumberFormat::FORMAT_TEXT,
'AA' => NumberFormat::FORMAT_TEXT,
'AB' => NumberFormat::FORMAT_TEXT,
'AC' => NumberFormat::FORMAT_TEXT,
'AD' => NumberFormat::FORMAT_TEXT,
'AE' => NumberFormat::FORMAT_TEXT,
'AF' => NumberFormat::FORMAT_TEXT,
];
}
/**
* 设置sheet的名称
* @return string
*/
public function title(): string
{
return '订单信息表';
}
}
