thinkphp添加excel更新数据表数据(优化篇)
无敌奥特曼 人气:0由于主管说使用saveAll更新数据效率太低,要改用sql语句一次执行现在修改
/** * excel开启上传 * author: panzhide * @return array * Date: 2021/4/14 */ public function logisticsImportExcel() { $file = request()->file('file'); if (!$file) { return json_success('excel文件不能为空', 'file'); } //将文件保存到public/storage/uploads/目录下面 $savename = \think\Facade\Filesystem::disk('public')->putFile('uploads', $file); //获取文件路径 $filePath = getcwd() . '/storage/' . $savename; if (!is_file($filePath)) { return json_success('没有发现结果'); } //实例化reader $ext = pathinfo($filePath, PATHINFO_EXTENSION); if (!in_array($ext, ['csv', 'xls', 'xlsx'])) { return json_success('未知的数据格式'); } if ($ext === 'csv') { $file = fopen($filePath, 'r'); $filePath = tempnam(sys_get_temp_dir(), 'import_csv'); $fp = fopen($filePath, "w"); $n = 0; while ($line = fgets($file)) { $line = rtrim($line, "\n\r\0"); $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']); if ($encoding != 'utf-8') { $line = mb_convert_encoding($line, 'utf-8', $encoding); } if ($n == 0 || preg_match('/^".*"$/', $line)) { fwrite($fp, $line . "\n"); } else { fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n"); } $n++; } fclose($file) || fclose($fp); $reader = new Csv(); } elseif ($ext === 'xls') { $reader = new Xls(); } else { $reader = new Xlsx(); } //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment'; //默认的表名 $table = OrderAddress::build()->getTable(); $fieldArr = []; $list = Db::query('SHOW FULL COLUMNS FROM `' . $table . '`'); foreach ($list as $k => $v) { if ($importHeadType == 'comment') { $fieldArr[$v['Comment']] = $v['Field']; } else { $fieldArr[$v['Field']] = $v['Field']; } } //加载文件 $update_data = []; // 需要更新的数据 $time = time(); try { if (!$PHPExcel = $reader->load($filePath)) { return json_success('未知的数据格式'); } $currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表 // $currentSheet -> setReadDataOnly(true); $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $maxColumnNumber = Coordinate::columnIndexFromString($allColumn); $fields = []; for ($currentRow = 1; $currentRow <= 1; $currentRow++) { for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $fields[] = $val; } } if (!in_array('物流公司', $fields)) { return json_error('缺少物流公司列'); } if (!in_array('物流编号', $fields)) { return json_error('缺少物流编号列'); } $max_row = 5000; if ($allRow > $max_row) { return json_error("每次最多导入${max_row}条数据"); } $kuaidi_code_data = KuaidiCode::build() -> column('code', 'name'); // 查询当前支持的所有物流公司 for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); if ($currentColumn == 4 && is_numeric($val)) { // 如果时间是数字类型,需要转格式 $toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($val); $date = date("Y-m-d", $toTimestamp ); $val = $date; } $values[] = is_null($val) ? '' : $val; } $row = []; // dump($values); $temp = array_combine($fields, $values); foreach ($temp as $k => $v) { if (isset($fieldArr[$k]) && $k !== '') { $row[$fieldArr[$k]] = $v; } } if ($row) { if ($row['kuaidicom'] || $row['logistics_sn']) { // 只处理kuaidicom或者logistics_sn有内容的数据 if (!$row['kuaidicom']) { $error[] = '第' . $currentRow . '行物流公司不得为空'; } elseif (!$row['logistics_sn']) { $error[] = '第' . $currentRow . '行物流编号不得为空'; } else { // 判断物流公司是否合法 $kuaidicom_code = $kuaidi_code_data[$row['kuaidicom']] ?? ''; // 物流公司对应编码 if ($kuaidicom_code) { $row['kuaidicom_code'] = $kuaidicom_code; // 物流公司对应编码 $row['shipping_time'] = strtotime($row['shipping_time']) ; // 发货时间 $update_data[] = $row; } else { $error[] = '第' . $currentRow . '行物流公司名称"'.$row['kuaidicom'].'"错误或系统不支持'; } } } } } } catch (Exception $exception) { return json_success($exception->getMessage()); } if (!$update_data) { return json_success('没有更新行'); } $n = 0; Db::startTrans(); try { $this -> logisticsImportExcelUpdate($update_data); Db::commit(); } catch (\Exception $e) { Db::rollback(); $msg = $e->getMessage(); return json_error($msg); } $msg = '成功导入' . count($update_data) . '条数据,失败'.count($error).'条数据'; $data['complete'] = 1; $data['error'] = $error; return json_success($msg, $data); } private function logisticsImportExcelUpdate($update_data) { // 整合更新语句 $update_value_sql = ''; $update_id_sql = ''; $update_key = ['kuaidicom', 'kuaidicom_code', 'logistics_sn']; foreach ($update_key as $key) { $n = 0; foreach ($update_data as $value) { $n ++; if ($n == 1) { $update_value_sql .= $key." = CASE order_sn "; } $update_value_sql .= 'WHEN \''.$value['order_sn'].'\' THEN \''.$value[$key].'\''; if ($n != count($update_data)) { $update_value_sql .= " "; } else { $update_value_sql .= " END,"; } } } $update_value_sql = substr($update_value_sql,0,strlen($update_value_sql)-1); $n = 0; foreach ($update_data as $value) { $n ++; if ($n == 1) { $update_id_sql .= "("; } $update_id_sql .= ' \''.$value['order_sn'].'\' '; if ($n != count($update_data)) { $update_id_sql .= ","; } else { $update_id_sql .= ")"; } } if (count($update_data)) { $update_sql = 'UPDATE cy_order_address SET '.$update_value_sql.' WHERE order_sn IN '.$update_id_sql.';'; Db::query($update_sql); // 执行更新语句,更新订单地址表 物流公司、物流编号、物流公司编号 } // 整合更新语句 $update_value_sql = ''; $update_id_sql = ''; $update_key = ['shipping_time']; foreach ($update_key as $key) { $n = 0; foreach ($update_data as $value) { $n ++; if ($n == 1) { $update_value_sql .= $key." = CASE order_sn "; } $update_value_sql .= 'WHEN \''.$value['order_sn'].'\' THEN \''.$value[$key].'\''; if ($n != count($update_data)) { $update_value_sql .= " "; } else { $update_value_sql .= " END,"; } } } $update_value_sql = substr($update_value_sql,0,strlen($update_value_sql)-1); $n = 0; foreach ($update_data as $value) { $n ++; if ($n == 1) { $update_id_sql .= "("; } $update_id_sql .= ' \''.$value['order_sn'].'\' '; if ($n != count($update_data)) { $update_id_sql .= ","; } else { $update_id_sql .= ")"; } } if (count($update_data)) { $update_sql = 'UPDATE cy_order SET '.$update_value_sql.' WHERE order_sn IN '.$update_id_sql.';'; Db::query($update_sql); // 执行更新语句,更新订单表 发货时间 } }
加载全部内容