前言
最近在用PHP开发一个加班系统,后台有个员工资料的信息模块需要用户从Excel导入到系统,因为以前没有做过类似的功能,所以特意去百度了一下,倒腾了半天终于把这个从Excel导数据到系统的功能做好了。
PHP从Excel导入数据代码如下:
//数据导入 public function storeSql() { $file = input('file.excel'); $path = ROOT_PATH . 'public' . DS . 'uploads'; if ($file) { $info = $file->move($path); if ($info) { $this->dataStore($info->getPathname()); } else { $this->error($file->getError()); } } } //堆代码 duidaima.com //数据导入 public function dataStore($filePath) { import('phpoffice.phpexcel.Classes.PHPExcel'); import('phpoffice.phpexcel.Classes.IOFactory'); import('phpoffice.phpexcel.Classes.Reader.Excel2007'); $PHPExcel = new \PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { $this->error('上传失败!'); } } //读取Excel文件 $PHPExcel = $PHPReader->load($filePath); //读取excel文件中的第一个工作表 $sheet = $PHPExcel->getSheet(0); //取得最大的列号 $allColumn = $sheet->getHighestColumn(); //取得最大的行号 $allRow = $sheet->getHighestRow(); $user = new UserOff; $phones = $user->where('merchant_id', $this->userID)->column('phone'); $all = []; //从第二行开始插入,第一行是列名 for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $data['phone'] = $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue(); $data['point'] = $PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue(); $data['growth'] = $PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue(); $data['card_num'] = $PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue(); $data['user_name'] = $PHPExcel->getActiveSheet()->getCell("E" . $currentRow)->getValue(); $data['merchant_id'] = $this->userID; $data['add_time'] = time(); $data['phone_no'] = $data['phone'] . $this->userID . "AcDE"; //编号 empty($data['card_num']) && $data['card_num'] = 0; empty($data['user_name']) && $data['user_name'] = ""; empty($data['phone']) && $data['user_name'] = ""; empty($data['point']) && $data['point'] = 0; empty($data['growth']) && $data['growth'] = 0; array_push($all,$data); } $allData =$this->diffArr($all,$phones); $update = $user->saveAll($allData['allDataUp'], true); //$update = true; $insert = $user->saveAll($allData['allDataIn'], false); if ($update || $insert) { $this->success('数据导入成功!', url('dump/index')); } else { $this->error('数据导入失败!'); } }总结:
从Excel导入数据到系统是我们日常开发系统中最常见的功能,其大概流程就是先从Excel读取数据,然后转换成对应插入SQL语句把从Excel读取的数据插入到数据库就可以了。