无限极分类导入mysql导出excel（Thinkphp5,无限极分类+商品导入mysql导出excel）

分类表字段： id（主键），name（分类名称）， pid（空为顶级，不为空为上级id）atlant模板 【我的表名：qr_domelamp_node】

产品表： no，name，spec ...根据自己的产品表填写 【我的表名：qr_domelamp_node】

关联表：id（主键），domelamp（分类表id）， domelamp_part（产品表id）  【我的表名：qr_domelamp_bind】

下载PHPExcel 我的放在vendor 目录下面

备注：（此处树形结构插件zTree）

 

HTML代码

<button onclick="ExportExcel()" class="btn btn-danger" data-toggle="dropdown" style="margin-left: 30px;"> </span>导出数据到XLS</button>

JS代码

function ExportExcel(){
    location.href = "{:url('Shopmodel/ExportExcel')}";
}

php 代码 

class ShopmodelController extends BaseController

{

    private $smallarr = array();
   private $classnumber = array();
   private $alllist = array();


    /*
     *
     * 查看数据格式（excel导出）
     *
     * */

    public function ExportExcelview(){
        $filename = "售后商品数据格式_" . date("Y_m_d", time()) . ".xls";              //名称
        $header = array('一级菜单','二级菜单','三级菜单','四级菜单','五级菜单','配件名称','配件编号','规格说明','配件单位','配件颜色','配件材质','销售价格');
        $data = [
            ['测试','test'],
            ['名称','name']
        ];
        excelExport($filename,$header,$data,'format');
    }

    /*
     *
     * 导出所有数据到xls
     *
     * */

public function ExportExcel(){

    $data = $this->getlist();
    $maxnumber = $data['max_classnumber'];//获取最大分类的数量
    $classname = '';
    for($i=1;$i<=$maxnumber;$i++){
        $classname .= "$i"."级分类".',';
    }
    $classname = rtrim($classname,',');
    $classmenu= explode(',',$classname);
    $arr = array('配件编号','配件名称','规格说明','配件单位','配件颜色','配件材质','销售价格'); //组合excel标题
    $header = array_merge($classmenu,$arr);
    $filename = "售后商品数据_" . date("Y_m_d", time()) . ".xls";
    excelExport($filename,$header,$data,'format');

}

    /*
    *
    * 导出xls 查询所有配件信息
    *
    * */
    public function getalllist($cid){
        $info = Db::name('DomelampBind')
            ->alias('a')
            ->join('DomelampNode b','a.domelamp = b.id')
            ->join('DomelampPart c','a.domelamp_part = c.no')
            ->where('a.domelamp','in',$cid)
            ->field('c.code,c.name,c.spec,c.unit,c.type,c.category,c.price')
            ->select();
        if(isset($this->number[$cid]['contentlist'])){//配件数量
            $this->contentnumber[$cid] = count($info);
        }else{
            $this->contentnumber[$cid]= count($info);
        }
      foreach($info as $k=>$v){
         $this->alllist[] = array_merge($this->smallarr[$cid],$v);
      }
      unset($this->smallarr[$cid]);
    }

    /*
     *
     * 导出xls 获取最底层数据
     *
     * */
    public function getexcelsmallarr($pid = 0){
        if($pid == 0){
            $where = 'pid is null';
        }else{
            $where = 'pid = '.$pid;
        }
        $arr = Db::name('DomelampNode')->where($where)->select();
        if(empty($arr)){
         $info = Db::name('DomelampNode')->where('id',$pid)->find();
         
            if($pid != 0)$this->smallarr[$info['id']]['class'][] = $info['name'];
         if(!isset($this->classnumber[$info['id']])){//分类数量
            $this->classnumber[$info['id']]=1; 
         }
            $this->getallarr($info['id'],$info['pid']);
            $this->getalllist($info['id']); //配件信息
        }else{
            foreach($arr as $k=>$v){
                $this->getexcelsmallarr($v['id']);
         }
        }
    }

    /*
     *
     * 导出xls 获取完整数据
     *
     * */
    public function getallarr($k,$id = 0){
        $info = Db::name('DomelampNode')->where('id',$id)->find();
      if(isset($this->classnumber[$k])){//分类数量
         $this->classnumber[$k]++; 
      }
      array_unshift($this->smallarr[$k]['class'],$info['name']);
        if($info['pid']>0){
            $this->getallarr($k,$info['pid']);
        }
    }

    /*
     *
     * 导出xls 获取方式（调用方法）
     *
     * */
    public function getlist(){
        $this->smallarr = array();
        $this->getexcelsmallarr();
      return ['max_classnumber'=> max($this->classnumber),'list'=>$this->alllist];
    }


}

/* *
 * 导出excel方法 个人定义在common文件下面

 * Excel表格导出全部数据 可规定格式

 * $format = format 全部分类数据导出，已修改数据格式

 * $format = '' 一般数据导出， 数据为二维数组即可
 
 * EXCEL 列如ABC 宽度个数 可根据实际情况设置

 * */
function excelExport($fileName = '', $headArr = [], $data = [] ,$format = '') {

    vendor ('PHPExcel.PHPExcel');
    $objPHPExcel = new \PHPExcel();

    $objPHPExcel->getProperties();

    $key = ord("A"); // 设置表头

    foreach ($headArr as $v) {

        $colum = chr($key);

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

        //设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth('20');
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth('20');

        //下面注释的这行代码是让表头拥有筛选功能，根据需要取消注释即可

        //$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

        $key += 1;

    }

    $column = 2;

    $objActSheet = $objPHPExcel->getActiveSheet();

    if($format == 'allinfo'){
        $classMax = $data['max_classnumber'];
        $list = $data['list'];
        foreach ($list as $k=>$row){
            $class = $row['class'];
            $number = count($class);
            if($number<$classMax){
                $need = $classMax - $number;
                for($i=0;$i<$need;$i++){
                    array_push($class,'');
                }
            }
            unset($row['class']);

            $list[$k] = array_merge($class,$row);

            $span = ord("A");

            foreach ($list[$k] as $keyName => $value) { // 列写入
                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;
        }
    }

    if($format == 'format'){
        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }
    }


    $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

    $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

    header('Content-Type: application/vnd.ms-excel');

    header("Content-Disposition: attachment;filename='$fileName'");

    header('Cache-Control: max-age=0');

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

    $objWriter->save('php://output'); // 文件通过浏览器下载

    exit();

}

/*
 *
 * 导入数据到数据库
 *
 * */
public function ImportExcel(Request $request){

    vendor('PHPExcel.PHPExcel');//获取PHPExcel类
    vendor('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');
    vendor('PHPExcel.PHPExcel.Reader.Excel5');
    $obj_PHPExcel = new \PHPExcel();

    //上传信息
    $dir = "upload/excel/";

    $file = $request->file('file');

    $info = $file->move($dir);


    // 启动事务
    Db::startTrans();
    if (!empty($info)) {

        try{

        $excelPath = $info->getSaveName();

        $file_name = $dir. $excelPath;
        $file_name = str_replace("\\", "/", $file_name);

        $suffix = explode(".", $excelPath);

        $suf = array_pop($suffix);

        if ($suf == "xlsx") {
            $objReader = \PHPExcel_IOFactory::createReader("Excel2007");
        } else {
            $objReader = \PHPExcel_IOFactory::createReader("Excel5");
        }

        $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
        $excel_array = $obj_PHPExcel->getsheet(0)->toArray();
        array_shift($excel_array);

        //循环excel组合新的数组
        foreach ($excel_array as $key => $value) {

            $count_excel = count($value)-7;//统计数组列数 仅用于测试用  根据excel表格设置

            for($i=0;$i<$count_excel;$i++){
                if(!empty($value[$i])){
                    $data[$key][] = $value[$i];
                }
            }
            //产品数组
            $data[$key]['code'] = $value[$count_excel];    //名称
            $data[$key]['name'] = $value[$count_excel+1];    //编号
            $data[$key]['spec'] = $value[$count_excel+2];    //规格
            $data[$key]['unit'] = $value[$count_excel+3];    //单位
            $data[$key]['category'] = $value[$count_excel+4];    //材质
            $data[$key]['type'] = $value[$count_excel+5];    //颜色
            $data[$key]['price'] = $value[$count_excel+6];   //价格
        }

        //循环新的数组插入数据库

        foreach ($data as $k => $v) {
            if(isset($v[0])){
                $id = Db::name("DomelampNode")->where('name',$v[0])->value('id');
                if(!empty($id)){
                    $insertGetId_domelamp_bottom = $id;
                }else{
                    $domelamp['name'] = $v[0];
                    $domelamp['pid'] = null;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp);
                }
            }
            if(isset($v[1])){
                $name = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_one = Db::name("DomelampNode")->where('name',$v[1])->where('pid',$name)->value('id');
               // $id_one = Db::name("DomelampNode")->where('name',$v[1])->value('id');
                if(!empty($id_one)){
                    $insertGetId_domelamp_bottom = $id_one;
                }else{
                    $domelamp_level_one['name'] = $v[1];
                    $domelamp_level_one['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_one);
                }
            }
            if(isset($v[2])){
                $name_two = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_two = Db::name("DomelampNode")->where('name',$v[2])->where('pid',$name_two)->value('id');
                //$id_two = Db::name("DomelampNode")->where('name',$v[2])->value('id');
                if(!empty($id_two)){
                    $insertGetId_domelamp_bottom = $id_two;
                }else{
                    $domelamp_level_two['name'] = $v[2];
                    $domelamp_level_two['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_two);
                }
            }
            if(isset($v[3])){
                $name_three = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_three = Db::name("DomelampNode")->where('name',$v[3])->where('pid',$name_three)->value('id');
                //$id_three = Db::name("DomelampNode")->where('name',$v[3])->value('id');
                if(!empty($id_three)){
                    $insertGetId_domelamp_bottom = $id_three;
                }else{
                    $domelamp_level_three['name'] = $v[3];
                    $domelamp_level_three['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_three);
                }
            }
            if(isset($v[4])){
                $name_four = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_four = Db::name("DomelampNode")->where('name',$v[4])->where('pid',$name_four)->value('id');
                //$id_four = Db::name("DomelampNode")->where('name',$v[4])->value('id');
                if(!empty($id_four)){
                    $insertGetId_domelamp_bottom = $id_four;
                }else{
                    $domelamp_level_four['name'] = $v[4];
                    $domelamp_level_four['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_four);
                }
            }
            if(isset($v[5])){
                $name_five = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_five = Db::name("DomelampNode")->where('name',$v[5])->where('pid',$name_five)->value('id');
                //$id_five = Db::name("DomelampNode")->where('name',$v[5])->value('id');
                if(!empty($id_five)){
                    $insertGetId_domelamp_bottom = $id_five;
                }else{
                    $domelamp_level_five['name'] = $v[5];
                    $domelamp_level_five['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_five);
                }
            }
            if(isset($v[6])){
                $name_six = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_six = Db::name("DomelampNode")->where('name',$v[6])->where('pid',$name_six)->value('id');
                //$id_six = Db::name("DomelampNode")->where('name',$v[6])->value('id');
                if(!empty($id_six)){
                    $insertGetId_domelamp_bottom = $id_six;
                }else{
                    $domelamp_level_six['name'] = $v[6];
                    $domelamp_level_six['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_six);
                }
            }
            if(isset($v[7])){
                $name_seven = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_seven = Db::name("DomelampNode")->where('name',$v[7])->where('pid',$name_seven)->value('id');
                //$id_seven = Db::name("DomelampNode")->where('name',$v[7])->value('id');
                if(!empty($id_seven)){
                    $insertGetId_domelamp_bottom = $id_seven;
                }else{
                    $domelamp_level_seven['name'] = $v[7];
                    $domelamp_level_seven['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_seven);
                }
            }
            if(isset($v[8])){
                $name_eight = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_eight = Db::name("DomelampNode")->where('name',$v[8])->where('pid',$name_eight)->value('id');
                //$id_eight = Db::name("DomelampNode")->where('name',$v[8])->value('id');
                if(!empty($id_eight)){
                    $insertGetId_domelamp_bottom = $id_eight;
                }else{
                    $domelamp_level_eight['name'] = $v[8];
                    $domelamp_level_eight['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_eight);
                }
            }
            if(isset($v[9])){
                $name_nine = Db::name("DomelampNode")->where('id',$insertGetId_domelamp_bottom)->value('id');
                $id_nine = Db::name("DomelampNode")->where('name',$v[9])->where('pid',$name_nine)->value('id');
                //$id_nine = Db::name("DomelampNode")->where('name',$v[9])->value('id');
                if(!empty($id_nine)){
                    $insertGetId_domelamp_bottom = $id_nine;
                }else{
                    $domelamp_level_nine['name'] = $v[9];
                    $domelamp_level_nine['pid'] = $insertGetId_domelamp_bottom;
                    $insertGetId_domelamp_bottom = Db::name('DomelampNode')->insertGetId($domelamp_level_nine);
                }
            }

            //插入产品表，返回插入的id 用于插入关联表
            $goods['name'] = $v['name'];
            $goods['code'] = $v['code'];
            $goods['spec'] = $v['spec'];
            $goods['unit'] = $v['unit'];
            $goods['category'] = $v['category'];
            $goods['type'] = $v['type'];
            $goods['price'] = $v['price'];
            $goods['datetime'] = date("Y-m-d H:i:s",time());;

            $insertGetId_part = Db::name('DomelampPart')->insertGetId($goods);

            $bind['domelamp'] = $insertGetId_domelamp_bottom;
            $bind['domelamp_part'] = $insertGetId_part;
            Db::name('DomelampBind')->insertGetId($bind);
        }
            Db::commit();
            $r = ['status' => 1, 'message' =>  "导入成功"];
            return json($r);
        } catch (\Exception $e) {
            // 回滚事务
            Db::rollback();
            $r = ['status' => 0, 'message' =>  "导入失败"];
            return json($r);
        }
    }else{
        // 回滚事务
        Db::rollback();
        //错误
        $message = $file->getError();
        $r = ['status' => 0, 'message' =>  $message];
        return json($r);
    }

}