前端导入excel到php后台,PHP如何更新、导入Excel30W+数据到数据表

最近产品出了个需求，要求：

在后台管理系统导入10~50w左右的数据到系统，通过excel上传，可以非实时，模板为下图

作为程序员，第一反应脑海里应该出现了以下几个关键字

PHPExcel

异步、消息队列

mysql的update

很好，其实主要的解决方案的确围绕在这几个问题，其实现实问题中有很多的解决方案，比如能否通过csv，naviecat导、load data、写入一个 sql 文件，然后 mysql source执行等等。那么接下来我们应该按What – Why – How来对应选择方案

但是在实际场景中，我们应该把需求更加具体化，比如

导入的excel是否存在错误的数据？(是否每一行过滤)，数据是否会重复、单元格能否空行(判断跳过)

允许非实时，但导入的频率如何？(我这里是一个月导1到2次)

导入完成怎么通知用户？(后台站内信)

方案一

PHPExcel+Mysql Update，简单粗暴的方案，读一行(excel)写一行(update)，但是必须在后台常驻内存执行，否则会超时、严重影响业务进行。我这里通过thinkphp5的命令行+定时任务执行，innodb是行锁，要注意更新内容的范围

方案二

PHP 高性能 Excel 扩展VtifulKernelExcel + Mysql update(case when)，使用游标模式逐行读取excel单元格，然我这里循环excel，然后大概1000条更新一次，拼接后sql的语句类似以下：

UPDATE `mytable` SET `name` = CASE

WHEN `title` = 'My title' THEN 'My Name 2'

WHEN `title` = 'Another title' THEN 'Another Name 2'

ELSE `name` END,

`date` = CASE

WHEN `title` = 'My title' THEN 'My date 2'

WHEN `title` = 'Another title' THEN 'Another date 2'

ELSE `date` END

WHERE `title` IN ('My title','Another title')

直接上业务代码

//先使用phpexcel获取总行数，Vtiful没找到这个功能

$objReader = \PHPExcel_IOFactory::createReader('Excel2007');

$objReader->setReadDataOnly(true);

try {

// 载入文件

$objPHPExcel = $objReader->load('tutorial.xlsx');

} catch (\Exception $e) {

throw new \Exception("载入文件失败");

}

// 获取表中的第一个工作表

$currentSheet = $objPHPExcel->getSheet(0);

// 获取总行数

$allRow = (int)$currentSheet->getHighestRow();

$excel = new \Vtiful\Kernel\Excel($config);

$data = $excel->openFile('tutorial.xlsx')->openSheet();

$data->nextRow();//手动跳过第一行

//算出每多少更新一次

$commit_every = 1000;

$commit_k = ceil($allRow / $commit_every);

$arr_commit = [];

for ($i = 1; $i <= $commit_k; $i++) {

$arr_commit[] = $i * $commit_every;

}

if (end($arr_commit) < $allRow) {

$arr_commit[] = $allRow - 1;//因为跳过了第一条

}

//

while ($rowData = $excel->nextRow()) {

//todo 非数字设置为0

//todo 评分三也要

//todo 导入评分行数

if (!is_numeric($rowData[0])) {

continue;

}

if (!is_numeric($rowData[1])) {

$rowData[1] = 0;

}

if (!is_numeric($rowData[2])) {

$rowData[2] = 0;

}

if (!is_numeric($rowData[3])) {

$rowData[3] = 0;

}

$upd[] = [

'id' => $rowData[0],//主键

'a' => (int)$rowData[1],

'b' => (int)$rowData[2],

'c' => (int)$rowData[3],

];

if (in_array($line, $arr_commit)) {

$this->updateBatch('table', $upd);

unset($upd);

}

}

public function updateBatch($tableName = "", $multipleData = array())

{

if ($tableName && !empty($multipleData)) {

// column or fields to update

$updateColumn = array_keys($multipleData[0]);

$referenceColumn = $updateColumn[0]; //e.g id

unset($updateColumn[0]);

$whereIn = "";

$q = "UPDATE " . $tableName . " SET ";

foreach ($updateColumn as $uColumn) {

$q .= $uColumn . " = CASE ";

foreach ($multipleData as $data) {

$q .= "WHEN " . $referenceColumn . " = " . $data[$referenceColumn] . " THEN '" . $data[$uColumn] . "' ";

}

$q .= "ELSE " . $uColumn . " END, ";

}

foreach ($multipleData as $data) {

$whereIn .= "'" . $data[$referenceColumn] . "', ";

}

$q = rtrim($q, ", ") . " WHERE " . $referenceColumn . " IN (" . rtrim($whereIn, ', ') . ")";

// Update

return Db::execute(Db::raw($q));

} else {

return false;

}

}

作者：4k显示器电竞鼠标