PHP如何更新、导入Excel30W+数据到数据表

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

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

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

• PHPExcel
• 异步、消息队列
• mysql的update

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

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

1. 导入的excel是否存在错误的数据？(是否每一行过滤)，数据是否会重复、单元格能否空行(判断跳过)
2. 允许非实时，但导入的频率如何？(我这里是一个月导1到2次)
3. 导入完成怎么通知用户？(后台站内信)

• 方案一

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;
        }
    }

到这里基本就结束了，文中参考到的mysql update、xlxs扩展(如果项目不允许或者麻烦的话不适用也可以的)https://stackoverflow.com/que…https://github.com/viest/php-…

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

原文地址：https://segmentfault.com/a/1190000022541657

喜欢 0