PHPExcel实现上传excel文件导入数据库
项目中需要批量导入数据，感觉这个需求以后也会经常用，必须总结分享下：
引入jquery的第三方表单插件：
<scripttype="text/javascript"src="/js/lib/jquery.ajax.form.js"></script>
视图文件：goods_list.ctp(商品列表),
<div class="btnimport"> <form class='myupload' action="<?=$this->Html->url(array('controller'=>'Goods','action'=>'batchImport'))?>" method='post' enctype='multipart/form-data' id="form_<?php echo $good['Good']['id']?>"> <input class="fileupload" type="file" name="importExcel" data-id="<?php echo $good['Good']['id']?>"> </form> <span>批量导入</span></div><style type="text/css"> .btnimport{position: relative;overflow: hidden;margin-right: 4px;display:inline-block; *display:inline;padding:4px 10px 4px;font-size:14px;line-height:18px; *line-height:20px;color:#fff; text-align:center;vertical-align:middle;cursor:pointer;background:#5bb75b; border:1px solid #cccccc;background-color: #1fb5ad;; border-bottom-color:#b3b3b3;-webkit-border-radius:4px; border-color: #1fb5ad; -moz-border-radius:4px;border-radius:4px; padding: 5px 10px; font-size: 12px; line-height: 1.5; border-radius: 3px; } .btnimport input{position: absolute;top: 0; right: 0;margin: 0;border:solid transparent; opacity: 0;filter:alpha(opacity=0); cursor: pointer;}</style>
使用input样子太丑了，还有个长条框，于是添加CSS修改样子做的和其他按钮样子一样。每个上传对应一个form表单，多个商品对应各自的上传事件，于是在form中添加id="form_<?php echo $good['Good']['id']?>"，这样每个商品的导入就可以调用各自的ajaxSubmit事件。
传入的jquery, 使用ajaxsubmit来提交表单。Jquery表单插件ajaxForm用法详解
<script type="text/javascript"> $(function () { $(".fileupload").change(function(){ var btnimport = $(".btnimport span"); var id = $(this).data('id'); $("#form_" + id).ajaxSubmit({ dataType: 'json', data:{id:id}, success: function(data) { switch(data['code']){ case 0:{ alert("批量导入成功"); //btnimport.html("批量导入"); //按钮还原 window.location.reload(); break; } case -6000:{ btnimport.html("上传失败"); alert("上传失败!"); break; } case -6001:{ btnimport.html("上传失败"); alert("文件格式不正确!"); break; } default:{ alert("系统繁忙，请稍后再试！"); break; } } }, error:function(xhr){ btnimport.html("上传失败"); } }); }); }); </script>
action中的处理方法：
/** * [batchImport 批量导入] * @return [type] [description] */public function batchImport(){ $id = $this->request->data('id'); $DOMAIN = $_SERVER['HTTP_HOST']; $file = $_FILES; $filename = $file['importExcel']['name']; $file_temp_name =$file['importExcel']['tmp_name']; $dir = WWW_ROOT.'/files' . DS . 'xls'; $type = strstr($filename,'.'); if($type != '.xls' && $type != '.xlsx'){ $this->_err_ret(-6001,'sys err'); } if(is_uploaded_file($file_temp_name)){ $full_name = $dir.DS. date ( 'Y-m-d_h:i:s' ).'_'.$filename; $result = move_uploaded_file($file_temp_name, $full_name); //处理文件路径，便于访问 //$full_name = explode('webroot/', $full_name); //$full_name = 'http://' . $DOMAIN .'/aaa/bbb'.$full_name[1]; }else{ $this->_err_ret(-6000,'err'); } //如果上传文件成功，就执行导入 excel操作 $objReader = PHPExcel_IOFactory::createReaderForFile($full_name); $objPHPExcel = $objReader->load($full_name); $objPHPExcel->setActiveSheetIndex(0); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $test = $objPHPExcel->getActiveSheet()->getCell('A2')->getValue(); $data = array(); for ($i=2; $i <= $highestRow ; $i++) { $sn = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getValue(); $pwd = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getValue(); $this->Card->create(); $data = array( 'sn' => $sn, 'pwd' => $pwd, 'good_id' => $id, ); if(!$this->Card->save($data)){ $this->_err_ret(-6000,'err'); exit; } } $newNumber = (int)$this->Good->findById($id)['Good']['number']+(int)$highestRow-1; $this->Good->id = $id; if(!$this->Good->saveField('number',$newNumber)){ $this->_err_ret(-6000,'err'); } $this->_suc_ret($id); }
赞 (0)