PHPExcel导入excell写入数据库

作者:谢高升 发布:2017-12-26 浏览:3638次

PHPExcel导入excell写入数据库;

1:下载PHPExcel了扩展http://phpexcel.codeplex.com/

2:写一个导入按钮

<input type="button" class="btn btn-xs btn-success" data-toggle="modal" data-target="#myModal" 
id="import" value="导入excell" />

<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
   <form class="form-horizontal ajaxForm2" id='formadd'  method="post" action="{:U('imports')}">
      <div class="modal-dialog" >
         <div class="modal-content">
            <div class="modal-header">
               <button type="button" class="close" data-dismiss="modal"
                     aria-hidden="true">×
               </button>
               <h4 class="modal-title" id="myModalLabel">
                  导入Excell
               </h4>
            </div>
            
            <div class="modal-body">
               <div class="row">
                  <div class="col-xs-12">

                     
                     <div class="form-group">
                        <!-- <label class="col-sm-3 control-label no-padding-right" for="form-field-1"> 所属商户: </label> -->
                        <div class="col-sm-9">
                           <input type="file" name="excelData"   datatype="*4-50" />  
                              <span class="Validform_checktip"></span>
                             
                        </div>
                     </div>


                  </div>
               </div>
            </div>
            <div class="modal-footer">
               <button type="submit" id='formbtn'  class="btn btn-primary">
                  提交保存
               </button>
               <button type="button" class="btn btn-default" data-dismiss="modal">
                  关闭
               </button>
            </div>
         </div><!-- /.modal-content -->
      </div><!-- /.modal-dialog -->
   </form>
</div><!-- /.modal -->

image.png


3:PHP后台处理

    /**
     * Created by PhpStorm.
     * function: data_import
     * Description:导入数据
     * User: Xiaoxie
     * @param $filename
     * @param string $exts
     * @param $or
     *
     */
    public function data_import($filename, $exts = 'xls',$or)
    {


        //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
        vendor('PHPExcel.PHPExcel');
        //创建PHPExcel对象,注意,不能少了\
        $PHPExcel = new \PHPExcel();
        //如果excel文件后缀名为.xls,导入这个类
        if ($exts == 'xls') {
            Vendor('PHPExcel.PHPExcel.Reader.Excel5');
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } else if ($exts == 'xlsx') {
            Vendor('PHPExcel.PHPExcel.Reader.Excel2007');
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }


        //载入文件
        $PHPExcel = $PHPReader->load($filename);
        //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
        $currentSheet = $PHPExcel->getSheet(0);
        //获取总列数
        $allColumn = $currentSheet->getHighestColumn();
        //获取总行数
        $allRow = $currentSheet->getHighestRow();
        //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
        for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始,A表示第一列
            for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                //读取到的数据,保存到数组$data中
                $cell = $currentSheet->getCell($address)->getValue();

                if ($cell instanceof PHPExcel_RichText) {
                    $cell = $cell->__toString();
                }
                $data[$currentRow - 1][$currentColumn] = $cell;
                //  print_r($cell);
            }

        }
       // 写入数据库操作
        $this->insert_data($data);
        
    }


    /**
     * Created by PhpStorm.
     * function: insert_data
     * Description:写入数据库操作
     * User: Xiaoxie
     * @param $data
     *
     */
public function insert_data($data)
{
    $created_time = date('Y-m-d H:i:s');
    $apinfo = A('apinfo');
    foreach ($data as $k => $v) {

        if ($k != 0) {
            //shop信息
            $info['shop_name'] = $v['C'];
            $info['address']=$v['D'];
            $info['contact_name'] = $v['I'];
            $info['contact_phone'] = $v['J'];
            $info['lng'] = $v['G'];
            $info['lat'] = $v['H'];

            $info['shop_code'] = time().$k;
            $type_explain = $v['K'];
            $where['type_explain'] = array('like',"%$type_explain%");
            
            $info['type_code'] = 5;
            
            $info['wa_area'] = $v['L'];

            $id = M('shop')->add($info);//shop_id
            $info['insert_time'] = date('Y-m-d H:i:s');
            
            //开始添加device信息


            $infos['dev_no'] = $info['shop_code'];
            $infos['dev_code'] = $v['B'];
            $infos['dev_mac'] = strtolower(str_replace('-', '', $v['B'])) ;
            $infos['device_name'] = $v['C'];
            $infos['device_ip'] = $v['F'];
            $infos['location_id'] = '3397';
            $infos['area_code'] = $v['L'];
            $infos['address'] = $v['D'];
            $infos['device_address'] = $v['D'];

            $infos['agent_id'] = 1;
            $infos['customer_id'] = 1;
            $infos['shop_id'] = $id;
            $infos['lng'] = $v['G'];
            $infos['lat'] = $v['H'];
            $infos['pss'] = $v['M'];
            $infos['site_code'] = $apinfo->setWanganCode($v['L'],3,$info['type_code'],$id);

            $result = M('device')->add($infos);
            $apinfo->insertdevice($info,$infos,$id);
            $apinfo->apinfo_defaultoption($infos['dev_mac']);        
        }

    }

   $this->success('设备添加成功',U('apinfo/apinfo_list'),1);
      
}


    /**
     * Created by PhpStorm.
     * function: imports
     * Description:导入excell
     * User: Xiaoxie
     *
     */
public function imports()
{
    header("Content-Type:text/html;charset = utf-8");

    $upload = new \Think\Upload();// 实例化上传类
    $upload->maxSize = 3145728;// 设置附件上传大小
    $upload->exts = array('xls', 'xlsx');// 设置附件上传类
    $upload->rootPath  = './public/Uploads/'; // 设置附件上传目录
    // 上传文件
    $info = $upload->uploadOne($_FILES['excelData']);
    $filename = $upload->rootPath . $info['savepath'] . $info['savename'];
    $exts = $info['ext'];
    if (!$info) {// 上传错误提示错误信息
        $this->error($upload->getError());
    } else {// 上传成功
        $this->data_import($filename, $exts,3);
    }

}


标签: PHPExcel