先看下导入的操作,一个很简单的需求,假设老猫开了一家包子铺,每天的收入流水是这样的。
我想把这些数据导入到我的MySQL数据表中去,怎么操作?首先肯定是要建一张表:
CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` varchar(255) NOT NULL COMMENT '日期', `money` DECIMAL(11,2) NOT NULL COMMENT '收款', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
按照官方提供的这个例子我们来做一做,搞定后,包子店就可以营业了,是不是很开心。
Importing basics | Laravel Excel
在使用我写的这些代码之前,请务必保证你的数据库连接好了,另外我的excel文件是放在public目录下面的,如果你没做好这些就开始卖包子,亏了钱我不负责的。
操作的命令行入口
<?php
namespace App\Console\Commands;
use App\Imports\AccountImport;
use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;
class ExcelRead extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'command:ExcelRead';
/**
* The console command description.
*
* @var string
*/
protected $description = '操作excel';
/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
parent::__construct();
}
/**
* Execute the console command.
*
* @return int
*/
public function handle()
{
Excel::import(new AccountImport(), public_path('test.xlsx'));
}
}
还需要特别说明一下的是这个包的引入问题:use Maatwebsite\Excel\Facades\Excel;
Excel::import 方法中第二个参数有很多灵活的配置。如果你直接这样写:
Excel::import(new AccountImport(), 'test.xlsx');他将会根据你config/filesystems.php的默认配置地址去读取文件,当然你可以传第三个参数去纠正这种默认方式。
导入模型文件: AccountImport.php
<?php
namespace App\Imports;
use App\Models\Account;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
class AccountImport implements ToModel
{
/**
* @param array $row
*
* @return Account|null
*/
public function model(array $row)
{
return new Account([
'date' => $row[0],
'money' => $row[1],
]);
}
}
模型: Account.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Account extends Model
{
use HasFactory;
protected $table = 'account';
protected $fillable = ['date', 'money'];
}
好了,开始算账吧。直接执行下面的命令
php artisan command:ExcelRead
我满心欢喜要去算账,结果发现翻车了!
问题不大,大概就是第一行中文表头引起的。直接干掉第一行,再试!导入成功了!
等等,怎么日期变成了一个数值了!!!
这里有两个问题:
问题1.表头是中文怎么处理?
难道我每次都手动去删除表头??显然不是一种成熟的行为。
问题2.excel中的特殊格式怎么处理?
有哪些特殊格式,比如,某天有个大老板经过我的包子铺,看我的包子好吃,直接给我打了一个亿,当天我把这个数据输到excel中去时发现变成了科学计数法显示格式了。我的乖乖,这可咋整。
先来处理问题1:
当第一列的数据与后面数据明显格式不一样的时候,我们直接在AccountImport中返回null即可。
public function model(array $row)
{
if (!is_numeric($row[1])) {
return null;
}
return new Account([
'date' => $row[0],
'money' => $row[1],
]);
}
关于第二个问题:数据格式的问题:
1.1 数字转日期的问题:在PhpOffice\PhpSpreadsheet\Shared\Date下有两个函数可以解决这个问题:
excelToDateTimeObject($excelTimestamp, $timeZone = null) 这个是变成datetime对象
excelToTimestamp($excelTimestamp, $timeZone = null) 这个是将数字变成时间戳
使用其中任何一个就可以了。
1.2 科学计数法的问题::目前可以正常显示成数字,无需解决!
在实际应用中,除了上述问题外还有一些问题,也可以在这里一起讨论一下,细心的小伙伴应该注意到上面的数据是一条条插入进数据库的,那么有没有方法可以批量入库呢?答案是有的。
此时AccountImport的代码是这样的:
<?php
namespace App\Imports;
use App\Models\Account;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
class AccountImport implements ToModel,WithBatchInserts
{
/**
* @param array $row
*
* @return Account|null
*/
public function model(array $row)
{
if (!is_numeric($row[1])) {
return null;
}
return new Account([
'date' => $row[0],
'money' => $row[1],
]);
}
public function batchSize(): int
{
return 1000;//这里代表一次性写入的数据量
}
}
是不是感觉快多了。能不能再快一点,这里我们只加快了写入的速度,如果还想快一点就只能从读入手了。
此时AccountImport的代码可以这样写:
<?php
namespace App\Imports;
use App\Models\Account;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\RemembersRowNumber;
class AccountImport implements ToModel,WithBatchInserts,WithChunkReading
{
use RemembersRowNumber;
/**
* @param array $row
*
* @return Account|null
*/
public function model(array $row)
{
$currentRowNumber = $this->getRowNumber();//取得当前所在的行数!
echo $currentRowNumber."\r\n";
if (!is_numeric($row[1])) {
return null;
}
return new Account([
'date' => $row[0],
'money' => $row[1],
]);
}
public function batchSize(): int
{
return 1000;
}
public function chunkSize(): int
{
return 50;
}
}
通过上面的操作,我们大概知道怎么去操作excel。并且这个组件已经帮我们想好了大部分的事情,我们要做的就是不停地引入接口来处理我们的业务即可。