0

场景:
需要从excel中导入信息导数据库。要求如下:

  1. 同一订单号下,可以存在多条记录

  2. 检测“产品”在“使用产品”(在已有数据表中获取)内,方可导入

  3. 表中相同交易单号的多个产品,只要有一个产品在“使用产品”内,就全部导入,记成1条。如果一个都没有则不导入

现在我模拟数据如下:

打印出来的导入数组为:

array (size=4)
  '667283818213324' => 
    array (size=17)
      0 => 
        array (size=9)
          0 => string '2017年8月12日23:44:58' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112313' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 1
          7 => float 2
          8 => string '火星' (length=6)
      1 => 
        array (size=9)
          0 => string '2017年8月12日23:44:59' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112314' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 2
          7 => float 3
          8 => string '火星' (length=6)
      2 => 
        array (size=9)
          0 => string '2017年8月12日23:44:60' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112315' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 3
          7 => float 4
          8 => string '火星' (length=6)
      3 => 
        array (size=9)
          0 => string '2017年8月12日23:44:61' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112316' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 4
          7 => float 5
          8 => string '火星' (length=6)
      4 => 
        array (size=9)
          0 => string '2017年8月12日23:44:62' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112317' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 5
          7 => float 6
          8 => string '火星' (length=6)
      5 => 
        array (size=9)
          0 => string '2017年8月12日23:44:63' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112318' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 6
          7 => float 7
          8 => string '火星' (length=6)
      6 => 
        array (size=9)
          0 => string '2017年8月12日23:44:64' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112319' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 7
          7 => float 8
          8 => string '火星' (length=6)
      7 => 
        array (size=9)
          0 => string '2017年8月12日23:44:68' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112323' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 11
          7 => float 12
          8 => string '火星' (length=6)
      8 => 
        array (size=9)
          0 => string '2017年8月12日23:44:69' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112324' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 12
          7 => float 13
          8 => string '火星' (length=6)
      9 => 
        array (size=9)
          0 => string '2017年8月12日23:44:70' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112325' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 13
          7 => float 14
          8 => string '火星' (length=6)
      10 => 
        array (size=9)
          0 => string '2017年8月12日23:44:71' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112326' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 14
          7 => float 15
          8 => string '火星' (length=6)
      11 => 
        array (size=9)
          0 => string '2017年8月12日23:44:72' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112327' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 15
          7 => float 16
          8 => string '火星' (length=6)
      12 => 
        array (size=9)
          0 => string '2017年8月12日23:44:73' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112328' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 16
          7 => float 17
          8 => string '火星' (length=6)
      13 => 
        array (size=9)
          0 => string '2017年8月12日23:44:74' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112329' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 17
          7 => float 18
          8 => string '火星' (length=6)
      14 => 
        array (size=9)
          0 => string '2017年8月12日23:44:76' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112331' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 19
          7 => float 20
          8 => string '火星' (length=6)
      15 => 
        array (size=9)
          0 => string '2017年8月12日23:44:77' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112332' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 20
          7 => float 21
          8 => string '火星' (length=6)
      16 => 
        array (size=9)
          0 => string '2017年8月12日23:44:78' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213324' (length=15)
          4 => string 'ruizhaole112333' (length=15)
          5 => string 'java大法千秋万代' (length=22)
          6 => float 21
          7 => float 22
          8 => string '火星' (length=6)
  '667283818213325' => 
    array (size=1)
      0 => 
        array (size=9)
          0 => string '2017年8月12日23:44:65' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213325' (length=15)
          4 => string 'ruizhaole112320' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 8
          7 => float 9
          8 => string '火星' (length=6)
  '667283818213326' => 
    array (size=1)
      0 => 
        array (size=9)
          0 => string '2017年8月12日23:44:66' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213326' (length=15)
          4 => string 'ruizhaole112321' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 9
          7 => float 10
          8 => string '火星' (length=6)
  '667283818213327' => 
    array (size=2)
      0 => 
        array (size=9)
          0 => string '2017年8月12日23:44:67' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213327' (length=15)
          4 => string 'ruizhaole112322' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 10
          7 => float 11
          8 => string '火星' (length=6)
      1 => 
        array (size=9)
          0 => string '2017年8月12日23:44:75' (length=24)
          1 => string '李锐钊' (length=9)
          2 => string '满堂红' (length=9)
          3 => string '667283818213327' (length=15)
          4 => string 'ruizhaole112330' (length=15)
          5 => string '十天PHP从入门到放弃' (length=27)
          6 => float 18
          7 => float 19
          8 => string '火星' (length=6)

(我的思路是用订单号做key来存放数组,但是下一步中检测多个记录在不在产品表中存在至少一个相同记录不知道该如何写,没有头绪)

代码:

    // +-----------------------------------------------
    // | 导入xls至数据库
    // +-----------------------------------------------
    public function importXLS()
    {
        if ($this->request->isPost()) {
            $file = $this->request->param('file', '' ,'trim');
            $file = ROOT_PATH . 'public' . $file;
            if (empty($file)) return $this->error('不能导入空文件');
            if (!file_exists($file)) return $this->error('要导入的文件不存在');

            // 自动判断后缀名是xls 还是 xlsx,两种不同的后缀需要的类不一样
            $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
            if ($extension =='xlsx') {
                $objReader = new PHPExcel_Reader_Excel2007();
            } else if ($extension =='xls') {
                $objReader = new PHPExcel_Reader_Excel5();
            }
            $objPHPExcel = $objReader->load($file);
            $excelarray=$objPHPExcel->getsheet(0)->toArray();

            array_shift($excelarray);//将第一行移出数组
            if (empty($excelarray)) {
                return $this->error('没有检测到插入数据,请重新编写导入数据列表');
                unlink($file);
            }
            foreach($excelarray as $k=> $v){
                $taobaoLists[$v[3]][] = $v;
                /* $data[$k]['tb_rq']=$v[0];
                $data[$k]['tb_jbr']=$v[1];
                $data[$k]['tb_dp']=$v[2];
                $data[$k]['tb_orderno']=$v[3];
                $data[$k]['tb_ww']=$v[4];
                $data[$k]['tb_pro']=$v[5];
                $data[$k]['tb_prono']=$v[6];
                $data[$k]['tb_xsje']=$v[7];
                $data[$k]['tb_address']=$v[8]; */

                // $data[$k]['tb_import_type'] = 2; // 数据外部导入
            }
            // 取出所有使用产品列表
            $proLists = Db::name('sys_products')->where('pro_status', 1)->field('pro_name')->select();
            $proLists = array_column($proLists, 'pro_name');
            foreach ($taobaoLists as $orderNumber => $list) {
                if (count($list) > 1) {
                    foreach ($list as $arr) {
                        
                    }
                } else {
                    if (!in_array($list[0][5], $proLists)) unset($taobaoLists[$orderNumber]);
                }
            }
            // $excelarray = array_filter(filter_array($excelarray));
            dump($taobaoLists);exit;

            //  // 删除空行,避免用户操作不当造成过多空白数组
            if (count($data) < 1) {
                return $this->error('没有检测到插入数据,请重新编写导入数据列表');
                unlink($data); // 删除文件
            }

            // 用交易号为下标建立数组
            foreach ($data as $row) {
                $arrs[$row['tb_orderno']] = $row;
                $arrs[$row['tb_orderno']]['items'] = [];
            }

            foreach ($arrs as $key => $arr) {
                // if ($arr['tb_orderno'])
            }
            dump($proLists); exit;
            
        }
    }

先谢谢~

1 个回答

0

已采纳

摸索了一上午时间,把实际需要的怼了出来,不清楚有没有什么BUG,也没做优化。直接上代码:

    // +-----------------------------------------------
    // | 导入xls至数据库
    // +-----------------------------------------------
    public function importXLS()
    {
        if ($this->request->isPost()) {
            $file = $this->request->param('file', '' ,'trim');
            $file = ROOT_PATH . 'public' . $file;
            if (empty($file)) return $this->error('不能导入空文件');
            if (!file_exists($file)) return $this->error('要导入的文件不存在');
            // 自动判断后缀名是xls 还是 xlsx,两种不同的后缀需要的类不一样
            $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
            if ($extension =='xlsx') {
                $objReader = new PHPExcel_Reader_Excel2007();
            } else if ($extension =='xls') {
                $objReader = new PHPExcel_Reader_Excel5();
            }
            $objPHPExcel = $objReader->load($file);
            $excelarray=$objPHPExcel->getsheet(0)->toArray();
            array_shift($excelarray);
            if (empty($excelarray)) {
                return $this->error('没有检测到插入数据,请重新编写导入数据列表');
                unlink($file);
            }
            foreach($excelarray as $k=> $v){
                $taobaoLists[$v[3]][] = $v;
            }
            unset($excelarray);
            foreach ($taobaoLists as $order => $rows) {
                foreach ($rows as $item) {
                    $_pro[$order][] = $item[5]; // 生成订单号-产品数组
                }
            }
            $proLists = Db::name('sys_products')->where('pro_status', 1)->field('pro_name')->select();
            $proLists = i_array_column($proLists, 'pro_name');
            // 检测同一订单号下 至少需要一个产品在使用列表中才允许导入
            foreach ($_pro as $order => $pros) {
                $intersectArrs = array_intersect($pros, $proLists);
                if (empty($intersectArrs)) unset($taobaoLists[$order]);
            }
            unset($proLists);
            // 同一订单号下只写入一条
            foreach ($taobaoLists as $order => $lists) {
                foreach ($lists as $key => $item) {
                    if (count($item) > 1){
                        $taobaoLists[$order][$key][5] = implode(',', $_pro[$item[3]]);
                        if ($key > 0) unset($taobaoLists[$order][$key]);
                    }
                }
            }
            foreach ($taobaoLists as $order => $lists) {
                foreach ($lists as $item){
                    static $i = 0;
                    $data[$i]['tb_rq'] = $item[0];
                    $data[$i]['tb_jbr'] = $item[1];
                    $data[$i]['tb_dp'] = $item[2];
                    $data[$i]['tb_orderno'] = $item[3];
                    $data[$i]['tb_ww'] = $item[4];
                    $data[$i]['tb_pro'] = $item[5];
                    $data[$i]['tb_prono'] = $item[6];
                    $data[$i]['tb_xsje'] = $item[7];
                    $data[$i]['tb_address'] = $item[8];
                    $data[$i]['tb_import_type'] = 2; // 数据外部导入
                    $i ++;
                }
            }
            unset($taobaoLists);
            if (0 < $rows = Db::name('sys_tbaccounts')->insertAll($data)) {
                unlink($file); // 上传成功后删除原文件
                return $this->success('数据导入成功,本地共导入'.$rows . '条数据');
            }
            return $this->error('数据导入失败');
        }
        return $this->fetch();
    }

=====
希望有大神可以给出更好的方案,谢谢