Laravel excel3 导入 WithHeadingRow设置
最近在做excel导入时被中文标题给搞得很乱
laravel是一个优雅的框架,它应该不会去干一些蠢事。其自集成的excel插件自3以后有了很大的变革,导致网上很难找到Demo
excel实现很简单,utf编码却卡了我小半天,还有几个蠢一些的实现就不上了(先取数组,在遍历转集合之类的),上常规代码
导入三种实现:
- Collection ,ToCollection接口;
- Model ,ToModel接口;
- Array ,ToArray接口;
数组实现:以我代码为例
import接口,另写只是为了与业务代码的分离,当然数据一致情况下可以复用,方法很蠢,最简单的遍历赋值,要求Excel列的顺序必须与你的数据顺序一致:.
$row=$collection->toArray();
for ($i=1;$i<count($row);$i++){
if ($row[$i][0]&&$row[$i][1]&&$row[$i][3]&&$row[$i][5]){
$carrier=Carrier::where('name',$row[$i][0])->first();
$unit=Unit::where('name',$row[$i][1])->first();
$province=Province::where('name',$row[$i][3])->first();
if ($carrier&&$unit&&$province){
$billingModel=['carrier_id'=>$carrier->id,'unit_id'=>$unit->id,'province_id'=>$province->id,'unit_price'=>$row[$i][5]];
if ($row[$i][2]){
$str=explode('-',$row[$i][2]);
$billingModel=array_merge($billingModel,['range_min'=>$str[0],'range_max'=>$str[1]]);
}
if ($row[$i][4]){
$city=City::where('name',$row[$i][4])->first();
if ($city){
$billingModel=array_merge($billingModel,['city_id'=>$city->id]);
}
}
if ($row[$i][6]){
$billingModel=array_merge($billingModel,['base_fee'=>$row[$i][6]]);
}
if ($row[$i][7]){
$billingModel=array_merge($billingModel,['initial_weight'=>$row[$i][7]]);
}
BillingModel::create($billingModel);
}
}
}
集合实现:
要求Excel标题列必须为英文:.
<?php
namespace App\Imports;
use App\Commodity;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class CommodityImport implements ToCollection, WithHeadingRow
{
protected $isOverride=false;
public function __construct($isOverride)
{
if($isOverride=='1')
$this->isOverride=true;
}
/**
* @param Collection $collections
*/
public function collection(Collection $collections)
{
foreach ($collections as $row)
{
$barcode = $row['barcode'] ?? $row['BARCODE'] ?? $row['Barcode'];
if(!$barcode)continue;
$name = $row['name'] ?? $row['NAME'] ?? $row['Name'] ?? '';
$sku = $row['sku'] ?? $row['SKU'] ?? $row['Sku'] ?? '';
$owner = $row['owner'] ?? $row['owner_name'] ?? $row['OWNER'] ?? $row['Owner'] ?? '';
$commodity=Commodity::where('barcode',$row['barcode'])->first();
if($commodity){
if($this->isOverride){
$name?$commodity['name']= $name:false;
$sku?$commodity['sku']= $sku:false;
$owner?$commodity['owner_name']= $owner:false;
$commodity->update();
}
}else{
Commodity::create([
'name' => $name,
'sku' => $sku,
'owner_name' => $owner,
'barcode' => $barcode,
]);
}
}
}
}
集合实现解决标题中文:
类去实现 WithHeadingRow 会让laravel自动匹配标题与列形成键值队的集合,但WithHeadingRow有自己的默认格式设置,导致其不支持中文,中文标题会置空,只需要在类前去 禁用其格式化 即可。
//禁用默认设置
HeadingRowFormatter::default('none');
class BillingModelsImport implements ToCollection,WithHeadingRow
{
Controller部分:
同文件上传类似,获取路径设置后缀即可,return时调用
Excel::import(/*Excel设置类*/,/*文件路径*/,null);