thinkphp5+phpexcel实现excel表格导入数据到数据库

1、下载phpexcel
composer require phpoffice/phpexcel
2、html代码

<form class=\"form form-horizontal\" id=\"form-article-add\" method=\"post\"><div class=\"row clearfix\"><div class=\"form-controls col-xs-12 col-sm-12\"><span class=\"btn-upload right\" id=\"test9\"><a  class=\"btn btn-primary radius btn-upload\">导入</a></span><span class=\"btn-upload right\" id=\"test8\"><a  class=\"btn btn-primary radius btn-upload\" id=\"\"><i class=\"Hui-iconfont\"></i> 浏览文件</a></span></div></div></form>

3、js代码
这里也还是用到了layui，如果用h-ui的朋友记得下载完整版的layui框架。

<script>layui.use(\'upload\', function(){var $ = layui.jquery,upload = layui.upload;//选完文件后不自动上传upload.render({elem: \'#test8\',exts: \'xlsx\' //只允许上传压缩文件,url: \'{:url(\'system/exceladd/daoru\')}\',auto: false//,multiple: true,bindAction: \'#test9\',done: function(res){console.log(res)if(res.code == 1) {layer.msg(res.msg, {\'icon\': 1});setTimeout(function () {parent.window.location.reload();}, 1000);} else {layer.msg(res.msg);}}});});</script>

4、php上传接口代码

<?phpnamespace app\\system\\controller;use think\\Controller;class Exceladd extends Controller{//批量导入public function daoru(){if($this->request->isPost()){$file = $this->request->file(\'file\');// 移动到框架应用根目录/public/uploads/ 目录下 存$info = $file->move(ROOT_PATH . \'public\' .DS.\'uploads\'. DS . \'excel\');if($info){//获取文件所在目录名 获取$path=ROOT_PATH . \'public\' . DS.\'uploads\'.DS .\'excel/\'.$info->getSaveName();$res[\'path\'] = $path;//加载PHPExcel类vendor(\'PHPExcel.PHPExcel\');//实例化PHPExcel类（注意：实例化的时候前面需要加\'\\\'）$extension = $info->getExtension();if( $extension ==\'xlsx\' ){// 这是实例化的类$objReader = new \\PHPExcel_Reader_Excel2007();}else{$objReader = new \\PHPExcel_Reader_Excel5();}// 加载文件，以备处理$objPHPExcel = $objReader->load($path,$encode=\'utf-8\');//获取excel文件$sheet = $objPHPExcel->getSheet(0); //激活当前的表，获取第一个表单$highestRow = $sheet->getHighestRow(); // 取得总行数$highestColumn = $sheet->getHighestColumn(); // 取得总列数$a=0;//将表格里面的数据循环到数组中for($i=2;$i<=$highestRow;$i++){// 如果A列（题目内容）就将数据存到数据库中if($objPHPExcel->getActiveSheet()->getCell(\"A\".$i)->getValue()){//*为什么$i=2? (因为Excel表格第一行应该是题目，选项，班级，从第二行开始，才是我们要的数据。)$data[$a][\'single_title\'] = $objPHPExcel->getActiveSheet()->getCell(\"A\".$i)->getValue();//单选题目$data[$a][\'opt_A\'] = $objPHPExcel->getActiveSheet()->getCell(\"B\".$i)->getValue();//选项A$data[$a][\'opt_B\'] = $objPHPExcel->getActiveSheet()->getCell(\"C\".$i)->getValue();//选项B$data[$a][\'opt_C\'] = $objPHPExcel->getActiveSheet()->getCell(\"D\".$i)->getValue();//选项C$data[$a][\'opt_D\'] = $objPHPExcel->getActiveSheet()->getCell(\"E\".$i)->getValue();//选项D$data[$a][\'opt_E\'] = $objPHPExcel->getActiveSheet()->getCell(\"F\".$i)->getValue();//选项E$data[$a][\'createtime\'] = time();//创建时间$data[$a][\'right\'] = $objPHPExcel->getActiveSheet()->getCell(\"H\".$i)->getValue();//正确答案$data[$a][\'knowledge\'] = $objPHPExcel->getActiveSheet()->getCell(\"I\".$i)->getValue();//知识点$data[$a][\'picture\'] = $objPHPExcel->getActiveSheet()->getCell(\"J\".$i)->getValue();$data[$a][\'music\'] = $objPHPExcel->getActiveSheet()->getCell(\"K\".$i)->getValue();$data[$a][\'analysis\'] = $objPHPExcel->getActiveSheet()->getCell(\"L\".$i)->getValue();// 这里的数据根据自己表格里面有多少个字段自行决定}$a++;}//往数据库添加数据// dump($data);$aa = Db(\'single\')->insertAll($data);if($aa){$res[\'code\']=1;$res[\'msg\'] = \'导入成功！\';}else{$res[\'code\']=0;$res[\'msg\'] = \'导入失败！\';}return $res;}}return $this->fetch();}}