TP框架实现Excel批量导入数据库数据

PHP Excel导入数据库数据

有个这样的需求，按数据库格式批量导入数据存入数据库 在TP框架有封装好的类 PHPExcel, 注意这是TP5.1框架的代码 我自己找了找资源写出来了，话不多说直接上代码

ini_set('memory_limit','1024M');if (!empty($_FILES)) {$file = request()->file('import');$info = $file->move(ROOT_PATH .'static'. DS . 'uploads' . DS . 'execl');if ($info) {//vendor("PHPExcel.PHPExcel");// Thinkphp5.1之后不支持 vendor$Worksheet = new \PHPExcel_Worksheet();$PHPExcel_Cell = new \PHPExcel_Cell(null,null,$Worksheet);$objPHPExcel = new PHPExcel();//excel对象$file_name = ROOT_PATH.'static/uploads/execl/'.$info->getSaveName();$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式if ($extension == 'xlsx') {$objReader =\PHPExcel_IOFactory::createReader('Excel2007');$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');} else if ($extension == 'xls'){$objReader =\PHPExcel_IOFactory::createReader('Excel5');$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');}$sheet =$objPHPExcel->getSheet(0);$highestRow = $sheet->getHighestRow();//总行数$highestColumn =$sheet->getHighestColumn(); //总列数//Db::name('goods')->execute('truncate table s_goods');//清除表中数据//i是从低级行开始导入for ($i = 2; $i <= $highestRow; $i++) {//data['id']中id是数据库得字段名A是excel得列$data[$i]['brand_id'] = $objPHPExcel->getActiveSheet()->getCell("A" . $i)->getFormattedValue(); //商品品牌$data[$i]['category_id'] = $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getFormattedValue(); //商品分类$data[$i]['goods_name'] = $objPHPExcel->getActiveSheet()->getCell("C" . $i)->getFormattedValue(); //商品名称// 一下依次按照你数据库里的字段来编写}// 启动事务Db::startTrans();// 在这里写你的入库逻辑代码//下面是我举得列子// 商品foreach($data as $k => $v) {$goods_data = ['brand_id'=> $v['brand_id'],'category_id' => $v['category_id'], 'goods_name'=> $v['goods_name'],];$query = Db::name('Goods')->insertGetId($goods_data); //执行添加if (isset($query) && $query <= 0) {// 回滚事务Db::rollback();}else{// 提交事务Db::commit();}}if($query){$this->success('导入成功!');}}else{echo $file->getError();}}else{$this->error("请选择上传的文件");}