PHPExcel导入excel数据到数据库中

绝地灬酷狼 2022-08-05 08:23 436阅读 0赞

以下内容是基于ThinkPHP框架。。

PHPExcel导入excel数据到数据库中

简述：导入的excel表格文件由四个工作表组成，其中前面三个工作表根据客户名称关联起来，它们共同构成数据库中12个表的每条数据，第四个工作表是独立的产品信息表。12个表都是关联表，含有其他表的外键。

数据库字段结构设计是全部不能为空，主键id都是自增。

1、总体实现思路

为每个数据库表创建一个数组来保存Excel表格中读取的数据，数组的键等于数据库字段名，数组的值等于excel对应的列的内容。先读取第一个工作表的数据，根据工作表行数增加来读取每条数据保存到数组中，然后使用

M()->add($data)方法添加数组到数据库中。通过PHPExcel类库中的$phpexcel->getSheet(0)方法可以读取每个工作表。

2、如何关联数据表的外键

TP中调用模型的add()方法添加数据成功后会返回插入的记录的主键id，保存该id。

一个客户名称存在于3个工作表的数据通过客户名称关联起来，也就是3个工作表中都有客户名称。所以插入其他工作表的数据到数据库时需要根据客户名称获得关联的表的主键。

$condition[‘name’] = $customer_name;

$res = M(‘customer_info’)->where($condition)->find();

$data_procurement_plan[‘customer_id’] = $res[‘id’];//关联customer_info表的id

3、添加相同的客户名称的记录

客户名称在客户信息表中是唯一存在的所以该字段设置唯一索引。Excel中含有数据库中已经存在的客户名称时更新记录，调用删除方法删除存在的12个表中的记录，再添加新记录。

4、excel有的列为空，但是数据库不能有空字段，设置默认值

定义一个函数对数组进行检查，有空的键给设默认值

public function setArrayDefault(&$arr){

foreach($arr a4 $k => $v){

arr[$k] = isset($v)?$v:’’;

}

return $arr;

}

5、数据库表中的每个字段在excel中都为空时，不插入该记录。例如无历史合作信息。

if( trim($data_old_cooperation[‘cooperatiom_time’]) !== ‘’)

|| trim($data_old_cooperation[‘cooperatiom_type’]) !== ‘’

|| trim($data_old_cooperation[‘cooperatiom_rank’]) !== ‘’ ){

// 添加记录代码

…

}

三个字段只要有一个有数据就插入，否则不插入。

trim() 函数是去除字符串两端的空格，防止excel中内容为空格的记录插入。

6、excel中的日期处理

很奇怪在excel中的日期2015-09-09在php代码中会变成42256,得不到正确的日期。

处理如下：首先在excel模版的有日期的列设置单元格格式，选中列头，右键->单元格格式->日期->格式码，设置为YYYY-MM-DD。

function formatData($data){

$data = trim($data);// 去除空格

if(empty($data)) return “0000-00-00”;

$n = intval(($data - 25569)*3600*24);// 转换成1970年以来的秒数

$format_data = gmdata(‘Y-m-d’,$n);// 格式化的时间

return $format_data;

}

$data 是从excel中读取的内容;

return “0000-00-00” 是设置自定义的默认值，如果excel内容为空的话，插入到数据库中得到的结果是1899-12-30,该值是输入日期为空时的默认时间。

注意事项：

1、数据库字段名称统一用小写，代码中字段名要可数据库中的大小写对应，否则可能插入数据失败。

2、PHPExcel插件放在Public目录下，没找到已经定义的指向public文件的路径，所以在index.php中定义了PUBLIC_PATH的路径。

完整代码如下：

  1. 控制器代码：
  2. <?php 
  3. namespace Home\Controller; 
  4. use Think\Controller; 
  5. class PhpexcelController extends Controller { 
  7.     private  static  function init(){ 
  8.         // 设置字符集编码 
  9.         header('Content-type:text/html;charset=utf-8'); 
  10.         // 加载类库 
  11.         include_once PHPEXCEL_PATH.'/PHPExcel.php'; 
  12.         include_once PHPEXCEL_PATH.'/PHPExcel/Style/Fill.php'; 
  13.     } 
  15.     /** 
  16.      * 导入一个excel表的多个sheet的数据 
  17.      */ 
  18.     public function importmoreexcel(){ 
  19.         self::init(); 
  20.         if($_FILES["morefile"]["error"] > 0){ 
  21.             echo 'ERROR:'.$_FILES["morefile"]["error"]."处错误<br/>请重新导入..."; 
  22.         }else{ 
  23.             // 注意：上传中文文件名出现乱码，暂时不能上传中文文件 
  24.             //$filename = iconv("GB2312","UTF-8",$_FILES["morefile"]["name"]); 
  25.             // 文件存放路径 
  26.             $filepath =UPLOAD_PATH."/".$_FILES["morefile"]["name"]; 
  27.             move_uploaded_file($_FILES["morefile"]["tmp_name"],$filepath); 
  28.             echo '文件保存在：'.$filepath."<br/>"; 
  30.             // 创建读文件对象 
  31.             $objReader = new \PHPExcel_Reader_Excel2007(); 
  32.             if(!$objReader->canRead($filepath)){ 
  33.                 $objReader = new \PHPExcel_Reader_Excel5(); 
  34.                 if(!$objReader->canRead($filepath)){ 
  35.                     echo '上传的不是excel文件...'; 
  36.                     return; 
  37.                 } 
  38.             } 
  40.             // 读取第一张表,客户基本信息excel表 
  41.             $phpexcel = $objReader->load($filepath); 
  42.             $current_excel = $phpexcel->getSheet(0); 
  43.             $allRow = $current_excel->getHighestRow();// 得到总行数 
  45.             // 插入记录成功的条数 
  46.             $count_success = 0; 
  47.             // 插入记录失败的条数 
  48.             $count_fail = 0; 
  49.             // 更新的记录树木 
  50.             $count_upload = 0; 
  51.             // 实例化模型 
  52.             $m_customer_info = M('customer_info'); 
  53.             $m_customer_data = M('customer_data'); 
  54.             $m_old_cooperation = M('old_cooperation'); 
  56.             for($row = 3;$row <= $allRow; $row++){ 
  57.                 $data_customer_data = array(); 
  58.                 $data_customer_info = array();// 客户基本信息 
  59.                 $data_old_cooperation = array(); 
  61.                 $data_customer_info['name'] = $current_excel->getCell('B'.$row)->getValue(); 
  62.                 $data_customer_info['user_name'] = $current_excel->getCell('C'.$row)->getValue(); 
  63.                 $data_customer_info['type'] = $current_excel->getCell('D'.$row)->getValue(); 
  64.                 $data_customer_info['account'] = $current_excel->getCell('E'.$row)->getValue(); 
  65.                 $data_customer_info['con_name'] = $current_excel->getCell('F'.$row)->getValue(); 
  66.                 $data_customer_info['phone'] = $current_excel->getCell('G'.$row)->getValue(); 
  67.                 $data_customer_info['qq'] = $current_excel->getCell('H'.$row)->getValue(); 
  68.                 $data_customer_info['wechat'] = $current_excel->getCell('I'.$row)->getValue(); 
  69.                 $data_customer_info['email'] = $current_excel->getCell('J'.$row)->getValue(); 
  70.                 $data_customer_info['company_address'] = $current_excel->getCell('K'.$row)->getValue(); 
  71.                 $data_customer_info['company_phone'] = $current_excel->getCell('L'.$row)->getValue(); 
  72.                 $data_customer_info['company_email'] = $current_excel->getCell('M'.$row)->getValue(); 
  73.                 $data_customer_info['web'] = $current_excel->getCell('N'.$row)->getValue(); 
  74.                 $data_customer_info['money'] = $current_excel->getCell('O'.$row)->getValue(); 
  75.                 $data_customer_info['is_listed'] = $current_excel->getCell('P'.$row)->getValue(); 
  76.                 $data_customer_info['market_value'] = $current_excel->getCell('Q'.$row)->getValue(); 
  77.                 $data_customer_info['sales_month'] = $current_excel->getCell('R'.$row)->getValue(); 
  78.                 $data_customer_info['storage_facilities'] = $current_excel->getCell('S'.$row)->getValue(); 
  79.                 $data_customer_info['it_systems'] = $current_excel->getCell('T'.$row)->getValue(); 
  81.                 // 客户附加信息 
  82.                 $data_customer_data['plan'] = $current_excel->getCell('U'.$row)->getValue(); 
  83.                 $data_customer_data['other_supplier'] = $current_excel->getCell('V'.$row)->getValue(); 
  84.                 $data_customer_data['cooperation_proportion'] = $current_excel->getCell('W'.$row)->getValue(); 
  86.                 $data_customer_info['create_time'] = date('Y-m-d h:i:s',time()); 
  87.                 $data_customer_info['description'] = $current_excel->getCell('X'.$row)->getValue(); 
  89.                 // 历史合作信息 
  90.                 $data_old_cooperation['cooperation_time'] = formatData($current_excel->getCell('Y'.$row)->getValue()); 
  92.                 $data_old_cooperation['cooperation_type'] = $current_excel->getCell('Z'.$row)->getValue(); 
  93.                 $data_old_cooperation['cooperation_rank'] = $current_excel->getCell('AA'.$row)->getValue(); 
  95.                 // 添加前判断 
  96.                 $this->setArrayDefault($data_customer_info); 
  97.                 $this->setArrayDefault($data_customer_data); 
  99.                 // 判断客户名称是否已经存在，存在则删除以前的记录，再添加记录 
  100.                 $condition_c_i['name'] = $data_customer_info['name']; 
  101.                 $result = $m_customer_info->where($condition_c_i)->find(); 
  102.                 if(!empty($result)){ 
  103.                     $this->delete($result['id']); 
  104.                     $count_upload ++; 
  105.                 } 
  108.                 // 插入数据到数据库中 
  109.                 // 插入到客户基本资料表 
  110.                 $customer_id = $m_customer_info->add($data_customer_info);// 保存插入的id，关联其他表 
  112.                 if($customer_id == false){ 
  113.                     $count_fail ++; 
  114.                 }else{ 
  115.                     $count_success ++; 
  116.                 } 
  117.                 // 插入数据到客户附加信息表 
  118.                 $data_customer_data['customer_id'] = $customer_id; 
  119.                 if($m_customer_data->add($data_customer_data) == false){ 
  120.                     $count_fail ++; 
  121.                 }else{ 
  122.                     $count_success ++; 
  123.                 } 
  125.                 /** 
  126.                  * 插入到历史合作信息 
  127.                  * 如果历史合作信息字段全没设置则不插入记录到数据库 
  128.                  */ 
  129.                 if(trim($data_old_cooperation['cooperation_time']) !== '' 
  130.                     || trim($data_old_cooperation['cooperation_type']) !== '' 
  131.                     || trim($data_old_cooperation['cooperation_rank']) !== ''){ 
  133.                     $this->setArrayDefault($data_old_cooperation); 
  134.                     $data_old_cooperation['customer_id'] = $customer_id; 
  135.                     if($m_old_cooperation->add($data_old_cooperation) == false){ 
  136.                         $count_fail ++; 
  137.                     }else{ 
  138.                         $count_success ++; 
  139.                     } 
  140.                 } 
  142.             } 
  145.             // 关键订单excel表 
  146.             $current_excel = $phpexcel->getSheet(1); 
  147.             $allRow = $current_excel->getHighestRow();// 得到总行数 
  149.             $m_procurement_plan = M('procurement_plan'); 
  150.             $m_key_order = M('key_order'); 
  151.             $m_supplier = M('supplier'); 
  152.             $m_r_order = M('r_order_supplier'); 
  153.             $m_r_plan = M('r_plan_product'); 
  155.             for($row = 3;$row <= $allRow; $row++){ 
  156.                 $data_procurement_plan = array(); 
  157.                 $data_key_order = array(); 
  158.                 $data_r_plan = array(); 
  159.                 $data_r_order = array(); 
  160.                 $data_supplier = array(); 
  162.                 // 得到客户名称 
  163.                 $customer_name = $current_excel->getCell('B'.$row)->getValue(); 
  165.                 // 采购计划与产品关联表 
  166.                 $data_r_plan['product_id'] = $current_excel->getCell('C'.$row)->getValue(); 
  168.                 // 采购计划 
  169.                 $data_procurement_plan['expect_price'] = $current_excel->getCell('D'.$row)->getValue(); 
  170.                 $data_procurement_plan['order_time'] = formatData($current_excel->getCell('E'.$row)->getValue()); 
  171.                 $data_procurement_plan['expect_delivery_date'] = formatData($current_excel->getCell('F'.$row)->getValue()); 
  172.                 $data_procurement_plan['pay'] = $current_excel->getCell('G'.$row)->getValue(); 
  173.                 $data_procurement_plan['delivery_address'] = $current_excel->getCell('H'.$row)->getValue(); 
  174.                 $data_procurement_plan['transport'] = $current_excel->getCell('I'.$row)->getValue(); 
  175.                 $data_procurement_plan['company_doc'] = $current_excel->getCell('J'.$row)->getValue(); 
  176.                 $data_procurement_plan['product_doc'] = $current_excel->getCell('K'.$row)->getValue(); 
  177.                 $data_procurement_plan['after_sales_agreement'] = $current_excel->getCell('L'.$row)->getValue(); 
  178.                 $data_procurement_plan['default_agreement'] = $current_excel->getCell('M'.$row)->getValue(); 
  179.                 $data_procurement_plan['is_cooperation'] = $current_excel->getCell('N'.$row)->getValue(); 
  181.                 // 供应商信息 
  182.                 $data_supplier['name'] = $current_excel->getCell('O'.$row)->getValue(); 
  183.                 $data_supplier['quote'] = $current_excel->getCell('P'.$row)->getValue(); 
  185.                 // 关键订单 
  186.                 $data_key_order['our_quote'] = $current_excel->getCell('Q'.$row)->getValue(); 
  187.                 $data_key_order['moq'] = $current_excel->getCell('R'.$row)->getValue(); 
  188.                 $data_key_order['profit_rate'] = $current_excel->getCell('S'.$row)->getValue(); 
  189.                 $data_key_order['type'] = $current_excel->getCell('T'.$row)->getValue(); 
  190.                 $data_key_order['status'] = $current_excel->getCell('U'.$row)->getValue(); 
  193.                 // 添加前判断 
  194.                 $this->setArrayDefault($data_procurement_plan); 
  195.                 $this->setArrayDefault($data_key_order); 
  196.                 $this->setArrayDefault($data_supplier); 
  197.                 $this->setArrayDefault($data_r_order); 
  198.                 $this->setArrayDefault($data_r_plan); 
  199.                 // 插入数据到数据库 
  200.                 /** 
  201.                  * 插入数据到采购计划表 
  202.                  * 查询客户基本信息表返回客户ID作为采购计划的客户id 
  203.                  */ 
  204.                 $condition['name'] = $customer_name; 
  205.                 $res = $m_customer_info->where($condition)->find(); 
  206.                 $data_procurement_plan['customer_id'] = $res['id']; 
  207.                 $procurement_plan_id = $m_procurement_plan->add($data_procurement_plan); 
  209.                 if($procurement_plan_id == false){ 
  210.                     $count_fail ++; 
  211.                 }else{ 
  212.                     $count_success ++; 
  213.                 } 
  214.                 // 插入数据到采购计划与产品关联表 
  216.                 // 插入数据到关键订单表 
  217.                 $data_key_order['plan_id'] = $procurement_plan_id; 
  218.                 $key_order_id = $m_key_order->add($data_key_order); 
  219.                 if($key_order_id == false){ 
  220.                     $count_fail ++; 
  221.                 }else{ 
  222.                     $count_success ++; 
  223.                 } 
  224.                 // 插入到供应商信息表 
  225.                 $supplier_id = $m_supplier->add($data_supplier); 
  226.                 if($supplier_id == false){ 
  227.                     $count_fail ++; 
  228.                 }else{ 
  229.                     $count_success ++; 
  230.                 } 
  231.                 // 插入到关键订单与供应商关联表 
  232.                 $data_r_order['order_id'] = $key_order_id; 
  233.                 $data_r_order['supplier_id'] = $supplier_id; 
  234.                 if($m_r_order->add($data_r_order) == false){ 
  235.                     $count_fail ++; 
  236.                 }else{ 
  237.                     $count_success ++; 
  238.                 } 
  239.                 // 插入到采购计划与产品关联表 
  240.                 $data_r_plan['plan_id'] = $procurement_plan_id; 
  241.                 if($m_r_plan->add($data_r_plan) == false){ 
  242.                     $count_fail ++; 
  243.                 }else{ 
  244.                     $count_success ++; 
  245.                 } 
  247.             } 
  250.             // 促销活动excel表 
  251.             $current_excel = $phpexcel->getSheet(2); 
  252.             $allRow = $current_excel->getHighestRow();// 得到总行数 
  254.             $m_activity = M('activity'); 
  255.             $m_r_activity = M('r_activity_product'); 
  256.             $m_our_prepare = M('our_prepare'); 
  258.             for($row = 3;$row <= $allRow; $row++){ 
  259.                 $data_activity = array(); 
  260.                 $data_our_prepare = array(); 
  261.                 $data_r_activity = array(); 
  263.                 // 得到客户名称 
  264.                 $customer_name = $current_excel->getCell('B'.$row)->getValue(); 
  265.                 // 活动情况 
  266.                 $data_activity['theme'] = $current_excel->getCell('C'.$row)->getValue(); 
  267.                 $data_activity['act_date'] = $current_excel->getCell('D'.$row)->getValue(); 
  268.                 $data_activity['act_addr'] = $current_excel->getCell('E'.$row)->getValue(); 
  269.                 $data_activity['act_desc'] = $current_excel->getCell('F'.$row)->getValue(); 
  270.                 $data_activity['description'] = $current_excel->getCell('G'.$row)->getValue(); 
  271.                 $data_activity['model'] = $current_excel->getCell('H'.$row)->getValue(); 
  272.                 $data_activity['traffic_sources'] = $current_excel->getCell('I'.$row)->getValue(); 
  273.                 $data_activity['conversion_rate'] = $current_excel->getCell('J'.$row)->getValue(); 
  275.                 // 活动与产品关联表 
  276.                 $data_r_activity['product_id'] = $current_excel->getCell('K'.$row)->getValue(); 
  278.                 $data_activity['quantity'] = $current_excel->getCell('L'.$row)->getValue(); 
  279.                 $data_activity['cycle'] = $current_excel->getCell('M'.$row)->getValue(); 
  280.                 $data_activity['expect_traffic'] = $current_excel->getCell('N'.$row)->getValue(); 
  281.                 $data_activity['hr_cs'] = $current_excel->getCell('O'.$row)->getValue(); 
  282.                 $data_activity['hr_it'] = $current_excel->getCell('P'.$row)->getValue(); 
  283.                 $data_activity['hr_art'] = $current_excel->getCell('Q'.$row)->getValue(); 
  284.                 $data_activity['hr_market'] = $current_excel->getCell('R'.$row)->getValue(); 
  285.                 $data_activity['hr_pr'] = $current_excel->getCell('S'.$row)->getValue(); 
  286.                 $data_activity['hr_other'] = $current_excel->getCell('T'.$row)->getValue(); 
  287.                 $data_activity['money'] = $current_excel->getCell('U'.$row)->getValue(); 
  288.                 $data_activity['goods'] = $current_excel->getCell('V'.$row)->getValue(); 
  289.                 $data_activity['storage_facilities'] = $current_excel->getCell('W'.$row)->getValue(); 
  290.                 $data_activity['it_systems'] = $current_excel->getCell('X'.$row)->getValue(); 
  291.                 $data_activity['price_strategy'] = $current_excel->getCell('Y'.$row)->getValue(); 
  292.                 $data_activity['ad_channel'] = $current_excel->getCell('Z'.$row)->getValue(); 
  293.                 $data_activity['ad_type'] = $current_excel->getCell('AA'.$row)->getValue(); 
  294.                 $data_activity['ad_money'] = $current_excel->getCell('AB'.$row)->getValue(); 
  295.                 $data_activity['order_feedback'] = $current_excel->getCell('AC'.$row)->getValue(); 
  296.                 $data_activity['after_sales_feedback'] = $current_excel->getCell('AD'.$row)->getValue(); 
  297.                 $data_activity['faq_feedback'] = $current_excel->getCell('AE'.$row)->getValue(); 
  298.                 $data_activity['sales_inventory_feedback'] = $current_excel->getCell('AF'.$row)->getValue(); 
  300.                 // 活动的准备 
  301.                 $data_our_prepare['inventory'] = $current_excel->getCell('AG'.$row)->getValue(); 
  302.                 $data_our_prepare['procurement_plan'] = $current_excel->getCell('AH'.$row)->getValue(); 
  303.                 $data_our_prepare['emergency'] = $current_excel->getCell('AI'.$row)->getValue(); 
  304.                 $data_our_prepare['hr_cs'] = $current_excel->getCell('AJ'.$row)->getValue(); 
  305.                 $data_our_prepare['hr_it'] = $current_excel->getCell('AK'.$row)->getValue(); 
  306.                 $data_our_prepare['hr_purchase'] = $current_excel->getCell('AL'.$row)->getValue(); 
  307.                 $data_our_prepare['hr_sales_manager'] = $current_excel->getCell('AM'.$row)->getValue(); 
  308.                 $data_our_prepare['hr_art'] = $current_excel->getCell('AN'.$row)->getValue(); 
  309.                 $data_our_prepare['hr_market'] = $current_excel->getCell('AO'.$row)->getValue(); 
  310.                 $data_our_prepare['hr_pr'] = $current_excel->getCell('AP'.$row)->getValue(); 
  311.                 $data_our_prepare['hr_other'] = $current_excel->getCell('AQ'.$row)->getValue(); 
  312.                 $data_our_prepare['money'] = $current_excel->getCell('AR'.$row)->getValue(); 
  313.                 $data_our_prepare['goods'] = $current_excel->getCell('AS'.$row)->getValue(); 
  314.                 $data_our_prepare['storage_facilities'] = $current_excel->getCell('AT'.$row)->getValue(); 
  315.                 $data_our_prepare['it_systems'] = $current_excel->getCell('AU'.$row)->getValue(); 
  316.                 $data_our_prepare['expect_order'] = $current_excel->getCell('AV'.$row)->getValue(); 
  317.                 $data_our_prepare['expect_revenue'] = $current_excel->getCell('AW'.$row)->getValue(); 
  318.                 $data_our_prepare['expect_quantity'] = $current_excel->getCell('AX'.$row)->getValue(); 
  319.                 $data_our_prepare['expect_popularity'] = $current_excel->getCell('AY'.$row)->getValue(); 
  320.                 $data_our_prepare['reach_coop'] = $current_excel->getCell('AZ'.$row)->getValue(); 
  321.                 $data_our_prepare['coop_status'] = $current_excel->getCell('BA'.$row)->getValue(); 
  323.                 // 添加前判断 
  324.                 $this->setArrayDefault($data_activity); 
  325.                 $this->setArrayDefault($data_r_activity); 
  326.                 $this->setArrayDefault($data_our_prepare); 
  327.                 // 插入到数据库 
  328.                 /** 
  329.                  * 插入到活动情况表 
  330.                  * 查询客户基本信息表返回客户ID作为活动情况的客户id 
  331.                  */ 
  332.                 $condition['name'] = $customer_name; 
  333.                 $res = $m_customer_info->where($condition)->find(); 
  334.                 $data_activity['customer_id'] = $res['id']; 
  336.                 $data_activity['traffic'] = 1; 
  337.                 $activity_id = $m_activity->add($data_activity); 
  338.                 if($activity_id == false){ 
  339.                     $count_fail ++; 
  340.                 }else{ 
  341.                     $count_success ++; 
  342.                 } 
  343.                 // 插入到活动与产品关联表 
  344.                 $data_r_activity['activity_id'] = $activity_id; 
  345.                 if($m_r_activity->add($data_r_activity) == false){ 
  346.                     $count_fail ++; 
  347.                 }else{ 
  348.                     $count_success ++; 
  349.                 } 
  351.                 // 插入到活动的准备表 
  352.                 $data_our_prepare['activity_id'] = $activity_id; 
  353.                 if($m_our_prepare->add($data_our_prepare) == false){ 
  354.                     $count_fail ++; 
  355.                 }else{ 
  356.                     $count_success ++; 
  357.                 } 
  359.             } 
  361.             // 产品信息excel表 
  362.             $current_excel = $phpexcel->getSheet(3); 
  363.             $allRow = $current_excel->getHighestRow();// 得到总行数 
  365.             $m_product = M('product'); 
  366.             for($row = 2;$row <= $allRow; $row++) { 
  367.                 $data_product = array(); 
  369.                 $data_product['category'] = $current_excel->getCell('B' . $row )->getValue(); 
  370.                 $data_product['brand'] = $current_excel->getCell('C' . $row)->getValue(); 
  371.                 $data_product['country'] = $current_excel->getCell('D' . $row)->getValue(); 
  372.                 $data_product['name'] = $current_excel->getCell('E' . $row)->getValue(); 
  373.                 $data_product['sku'] = $current_excel->getCell('F' . $row)->getValue(); 
  374.                 $data_product['model'] = $current_excel->getCell('G' . $row)->getValue(); 
  375.                 $data_product['quantity'] = $current_excel->getCell('H' . $row)->getValue(); 
  376.                 $data_product['unit'] = $current_excel->getCell('I' . $row)->getValue(); 
  378.                 // 添加前判断 
  379.                 $this->setArrayDefault($data_product); 
  380.                 // 插入到产品信息表 
  381.                 if($m_product->add($data_product) == false) { 
  382.                     $count_fail++; 
  383.                 }else{ 
  384.                     $count_success ++; 
  385.                 } 
  387.             } 
  389.             echo $count_success.'条记录插入成功'; 
  390.             echo "<br/>".$count_fail.'条记录失败'; 
  391.             echo "<br/>更新".$count_upload.'条记录'; 
  392.             $this->success('添加完成',U('Index/index'),3); 
  393.         } 
  395.     } 
  397.     /** 
  398.      * 给数组中值为空的键设置默认值 
  399.      * 
  400.      */ 
  401.     public function setArrayDefault(&$arr){ 
  403.         foreach($arr as $k => $v){ 
  404.             $arr[$k] = isset($v)?$v:''; 
  405.         } 
  406.         return $arr; 
  407.     } 
  409.     public function delete($id) 
  410.     { 
  411.         // 实例化模型对象 
  412.         $m_customer_info = M('customer_info'); 
  413.         $m_customer_data = M('customer_data'); 
  414.         $m_old_cooperation = M('old_cooperation'); 
  415.         $m_procurement_plan = M('procurement_plan'); 
  416.         $m_key_order = M('key_order'); 
  417.         $m_r_order = M('r_order_supplier'); 
  418.         $m_r_plan = M('r_plan_product'); 
  419.         $m_activity = M('activity'); 
  420.         $m_r_activity = M('r_activity_product'); 
  421.         $m_our_prepare = M('our_prepare'); 
  423.         // 获取关联customer_info表的activity的id 
  424.         $res = $m_activity->where('customer_id='.$id)->field('id')->find(); 
  425.         $activity_id = $res['id']; 
  426.         // 获取关联customer_info表的procurement_plan的id 
  427.         $res = $m_procurement_plan->where('customer_id='.$id)->field('id')->find(); 
  428.         $procurement_plan_id = $res['id']; 
  429.         // 获取关联key_order表的r_order_supplier的id 
  430.         $res = $m_key_order->where('plan_id='.$procurement_plan_id)->field('id')->find(); 
  431.         $key_order_id = $res['id']; 
  433.         $m_customer_info->where('id='.$id)->delete(); 
  435.         /** 
  436.          * 判断activity表中是否有关联数据 
  437.          * 无记录则跳过其他两个关联表的删除操作 
  438.          */ 
  439.         if(intval($activity_id)){ 
  440.             $m_activity->where('customer_id='.$id)->delete(); 
  441.             $m_our_prepare->where('activity_id='.$activity_id)->delete(); 
  442.             $m_r_activity->where('activity_id='.$activity_id)->delete(); 
  443.         } 
  445.         /** 
  446.          * 判断procurement_plan表中是否有关联数据 
  447.          * 无记录则跳过其他三个关联表的删除操作 
  448.          */ 
  449.         if(intval($procurement_plan_id)){ 
  450.             $m_procurement_plan->where('customer_id='.$id)->delete(); 
  451.             $m_r_plan->where('plan_id='.$procurement_plan_id)->delete(); 
  452.             $m_key_order->where('plan_id='.$procurement_plan_id)->delete(); 
  453.             $m_r_order->where('order_id='.$key_order_id)->delete(); 
  454.         } 
  456.         $m_customer_data->where('customer_id='.$id)->delete(); 
  457.         $m_old_cooperation->where('customer_id='.$id)->delete(); 
  459.     } 
  462. }
  465. 函数代码：functions.php
  467. function formatData($data){
  468.     $data = trim($data);
  469.     // 为空则返回默认值
  470.     if(empty($data))  return "0000-00-00";
  471.     $n = intval(($data - 25569) * 3600 * 24); //转换成1970年以来的秒数
  472.     $format_data = gmdate('Y-m-d', $n);//格式化时间
  473.     return $format_data;
  474. }

发表评论

表情：
评论列表 （有 0 条评论，436人围观）

还没有评论，来说两句吧...

相关阅读