ExecelPHP类简单导入功能 tp3.1.3

jerry thinkphp 2015年11月19日收藏
public function import($file =''){
        
        vendor('Excel.PHPExcel');
        vendor('Excel.PHPExcel.IOFactory');
        vendor('Excel.PHPExcel.Reader.Excel5');
                
        $objReader = new PHPExcel_Reader_Excel5;
        
        $objPHPExcel = $objReader->load($file);
                
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        
        $count = 0;
        $s_count = 0;
        $t_count = 0;
        $b_count = 0;
        $m_count = 0;
        $string = '';
        
        for($j =2; $j <= $highestRow; $j ++){
            
            $haipin_id = trim($objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue()); //海品编号
            $goods_name = trim($objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue()); //商品名称
            $common_name = trim($objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue()); //通用名
            $license_number = trim($objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue()); //批准文号
            $spec = trim($objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue()); //规格
            $manufacturer = trim($objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue()); //厂家
            $brand = trim($objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue()); //品牌
            $type = trim($objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue()); //类型
            $cate = trim($objPHPExcel->getActiveSheet()->getCell("U".$j)->getValue()); //分类
            $keywords = trim($objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue()); //关键词
            $effect = trim($objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue()); //功效
            $is_drug = trim($objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue()); //处方药
            $drug_description = trim($objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue()); //处方药说明
            $form = trim($objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue()); //剂型
            $material = trim($objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue()); //原料
            $leading_effect = trim($objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue()); //作用
            $crowd = trim($objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue()); //适用人群
            $instructions = trim($objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue()); //用法用量
            $is_import = trim($objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue()); //是否进口
            $department = trim($objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue()); //部门名称
            $haipin = trim($objPHPExcel->getActiveSheet()->getCell("W".$j)->getValue());  //海品药店
            $zhimeilan = trim($objPHPExcel->getActiveSheet()->getCell("X".$j)->getValue());  //植玫兰
            $off_line = trim($objPHPExcel->getActiveSheet()->getCell("Y".$j)->getValue()); //线下
            
            $goods_data = array();
            $empty_field = 0;
            
            if(empty($cate)){
                continue;
            }else{
    
                $cate_id = A('Admin/Cate')->check_cate_exists($cate);
                
                if(!$cate_id){
                    $string .= $cate. "分类不存在分类列表,请先输入.<br/> ";
                    continue;
                }else{
                    $goods_data['cate_id'] = (int)$cate_id;
                }
            }
            
            if(empty($department)){
                $goods_data['department_id'] = 0;
            }else{
                
                $department_id = $this->check_department_exists($department);
                
                if(!$department_id){
                    $goods_data['department_id'] = 0;
                }else{
                    $goods_data['department_id'] = (int)$department_id;
                }
            }
            
            if(empty($brand)){
                //continue;
                $goods_data['brand_id'] = 0;
                $empty_field = 1;
                
            }else{
                
                $brand_id = A('Admin/Brand')->check_brand_exists($brand);
                
                if(!$brand_id){
                    $data = array(
                        'brand_name' => $brand,
                    );
                    $insert_brand_id = M('Brand')->add($data);
                    if($insert_brand_id !== false){
                        $b_count ++;
                        $goods_data['brand_id'] = $insert_brand_id;
                    }else{
                        continue;
                    }
                }else{
                    $goods_data['brand_id'] = $brand_id;
                }
            }
            
            if(empty($type)){
                //continue;
                $goods_data['type_id'] = 0;
            }else{
                
                $type_id = A('Admin/GoodsType')->check_type_exists($type);
                
                if(!$type_id){
                    $data = array(
                        'type_name' => $type,
                    );
                    $insert_type_id = M('Goods_type')->add($data);
                    if($insert_type_id !== false){
                        $t_count ++;
                        $goods_data['type_id'] = $insert_type_id;
                    }else{
                        continue;
                    }
                }else{
                    $goods_data['type_id'] = $type_id;
                }
            }
            
            if(empty($spec)){
                //continue;
                $goods_data['spec_id'] = 0;
                $empty_field = 1;
            }else{
                
                $spec_id = A('Admin/GoodsSpec')->check_goods_spec_exists($spec);
                
                if(!$spec_id){
                    $data = array(
                        'spec_name' => $spec,
                    );
                    $insert_spec_id = M('Goods_spec')->add($data);
                    if($insert_spec_id !== false){
                        $s_count ++;
                        $goods_data['spec_id'] = $insert_spec_id;
                    }else{
                        continue;
                    }
                }else{
                    $goods_data['spec_id'] = $spec_id;
                }
                
            }
            
            if(empty($manufacturer)){
                //continue;
                $goods_data['manufacturer_id'] = 0;
                $empty_field = 1;
            }else{
                
                $manufacturer_id = A('Admin/Manufacturer')->check_manufacturer_exists($manufacturer);
                
                if(!$manufacturer_id){
                    $data = array(
                        'manufacturer_name' => $manufacturer,
                    );
                    $insert_manufacturer_id = M('Manufacturer')->add($data);
                    if($insert_manufacturer_id !== false){
                        $m_count ++;
                        $goods_data['manufacturer_id'] = $insert_manufacturer_id;
                    }else{
                        continue;
                    }
                }else{
                    $goods_data['manufacturer_id'] = $manufacturer_id;
                }
            }
            
            $goods_data['is_drug'] = array_search(strtoupper($is_drug), C(IS_DRUG));
            if(empty($goods_data['is_drug'])){
                $goods_data['is_drug'] = 0;
                $empty_field = 1;
            }

            $goods_id = $this->is_exist_goods($goods_name, $goods_data['brand_id'], $goods_data['is_drug'], $goods_data['spec_id'], $goods_data['manufacturer_id']);
                    
            if(!$goods_id){
                
                $goods_data['haipin_id'] = $haipin_id;
                $goods_data['goods_name'] = $goods_name;
                $goods_data['common_name'] = $common_name;
                $goods_data['license_number'] = $license_number;
                $goods_data['keywords'] = $keywords;
                $goods_data['effect'] = $effect;
                //$goods_data['is_drug'] = array_search(strtoupper($is_drug), C(IS_DRUG));
                $goods_data['drug_description'] = $drug_description;
                $goods_data['form'] = $form;
                $goods_data['material'] = $material;
                $goods_data['leading_effect'] = $leading_effect;
                $goods_data['crowd'] = $crowd;
                $goods_data['instructions'] = $instructions;
                $goods_data['is_import'] = empty($is_import)? 0: 1;
                $goods_data['add_time'] = time();
                $goods_data['user_id'] = session('uid');

                $goods_model = M('Goods');
                $goods_model->startTrans();
                $goods_id = $goods_model->add($goods_data); 
                if($goods_id !== false){
                    
                    $g_new_goods_sn = '';
                    $g_new_goods_sn .= str_pad((string)$goods_data['cate_id'], 1, "0" ,STR_PAD_LEFT);
                    $g_new_goods_sn .= str_pad((string)$goods_id, 7, "0" ,STR_PAD_LEFT);
                    $goods_model->save(array('new_goods_sn'=>$g_new_goods_sn, 'goods_id'=>$goods_id));
                    
                    //处理网站和商品关系
                    if($haipin){
                        $s_data = array(
                            'site_id' =>2,
                            'goods_id' =>$goods_id,
                            'type' =>1,
                        );
                        $this->deal_with_goods_to_site($s_data);
                    }
                    if($zhimeilan){
                        $s_data = array(
                            'site_id' =>3,
                            'goods_id' =>$goods_id,
                            'type' =>1,
                        );
                        $this->deal_with_goods_to_site($s_data);
                    }
                    if($off_line){
                        $s_data = array(
                            'site_id' =>4,
                            'goods_id' =>$goods_id,
                            'type' =>1,
                        );
                        $this->deal_with_goods_to_site($s_data);
                    }
                    
                    if(!$empty_field){
                        $goods_sn = '';
                        $cate_res = M('Cate')->field('alias_name')->where(array('cate_name'=>$cate))->find();
                        $goods_sn .= substr($cate_res['alias_name'], 0, 2);
                        $goods_sn .= str_pad((string)$goods_data['brand_id'], 5, "0" ,STR_PAD_LEFT);
                        $goods_sn .= str_pad((string)$goods_data['is_drug'], 3, "0" ,STR_PAD_LEFT);
                        $goods_sn .= str_pad((string)$goods_id, 5, "0" ,STR_PAD_LEFT);
                        $goods_sn .= str_pad((string)$goods_data['manufacturer_id'], 5, "0" ,STR_PAD_LEFT);
                        $goods_sn .= str_pad((string)$goods_data['spec_id'], 5, "0" ,STR_PAD_LEFT);
                        $data = array(
                            'goods_id' => $goods_id,
                            'goods_sn' => $goods_sn,
                        );
                        $res = $goods_model->save($data);
                        
                        if($res !== false){
                            $goods_model->commit();
                        }else{
                            $goods_model->rollback();    
                        }
                        
                    }else{
                        $goods_model->commit();
                    }
                    
                    $count ++;
                    
                }else{
                    $goods_model->rollback();
                    $string .= $haipin_id.'/'.$goods_name. "录入失败.<br/> ";
                }
                
            }else{
                $string .= $haipin_id.'/'.$goods_name. "已经存在.<br/> ";
            }
        }
        
        $o_string = '';
        $o_string .= '本次操作录入'. $count. '个产品, ';
        $o_string .= '录入'. $s_count. '个规格, ';
        $o_string .= '录入'. $t_count. '个类型, ';
        $o_string .= '录入'. $b_count. '个品牌, ';
        $o_string .= '录入'. $m_count. "个厂家.<br/><br/> ";
        
        return '<div style="font-size;12px">'.$o_string . $string.'</div>';
    }