当前位置:Gxlcms > PHP教程 > phpexecl导出循环时间问题

phpexecl导出循环时间问题

时间:2021-07-01 10:21:17 帮助过:21人阅读

做php导出,需要循环输出数据到execl中,一开始写了一个,觉得时间有点长,就缩短了下,发现时间还变多了,求教这是为什么啊?谢谢了先。

原先的代码片段:

//归总每个一级分类下的二级分类
foreach($first_cate_array as $fkey => $fvalue){
    foreach($cate as $ck=>$cv){                             
        if($cv['parentid'] == $fvalue['id']){
            $first_cate_array[$fkey]['second_cate'][] = $cv;
        }
    }

    //获得每个一级分类下二级分类的数目
    $second_count = count($first_cate_array[$fkey]['second_cate']); 
    $first_cate_array[$fkey]['second_count'] = $second_count == 0 ? 1 : $second_count;  
}

/*************************************************************************/
foreach($first_cate_array as $fk => $fv){
    //一级目录起始行数
    if($fk == 0){
        $stapos = $num;
        $en_stapos = $stapos+$cate_count;
    }else{
        $stapos = $stapos+$first_cate_array[$fk-1]['second_count'];
        $en_stapos = $stapos+$cate_count;
    }

    foreach($fv['second_cate'] as $sck => $scv){
        //二级目录起始行数
        $stapos1 = $stapos+$sck;
        $en_stapos1 = $en_stapos+$sck;
        foreach($scv['pro'] as $spk => $spv){
            $str1 = 70+$spk*3;
            $str1 = chr($str1);
            $str2 = 71+$spk*3;
            $str2 = chr($str2);
            $str3 = 72+$spk*3;
            $str3 = chr($str3);
            //中文
            $collect = $spv['name'].'('.$spv['description'].')';
            $objActSheet->setCellValue($str1.$stapos1, $collect);
            $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
            $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
            //英文
            $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
            $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
            $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
            $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
        }
        //中文
        $objActSheet->setCellValue('E'.$stapos1, $scv['category']);
        //英文
        $objActSheet->setCellValue('E'.$en_stapos1, $scv['en_category']);
    }
    //中文
    $objActSheet->setCellValue('D'.$stapos, $fv['category']);
    $stopos = $stapos + $fv['second_count'] - 1;
    $mergecellstr = 'D'.$stapos.':D'.$stopos;
    $objActSheet->mergeCells($mergecellstr);
    //英文
    $objActSheet->setCellValue('D'.$en_stapos, $fv['en_category']);
    $en_stopos = $en_stapos + $fv['second_count'] - 1;
    $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
    $objActSheet->mergeCells($en_mergecellstr);
}

修改以后的片段:

$second_count = 0;
//归总每个一级分类下的二级分类
foreach($first_cate_array as $fkey => $fvalue){
    //一级目录起始行数
    $stapos = $num+$second_count;
    $en_stapos = $stapos+$cate_count;
    $second_stapos = 0;
    foreach($cate as $ck=>$cv){
        if($cv['parentid'] == $fvalue['id']){
            //二级目录起始行数
            $stapos1 = $stapos+$second_stapos;
            $en_stapos1 = $en_stapos+$second_stapos;                                    
            foreach($cv['pro'] as $spk => $spv){
                $str1 = 70+$spk*3;
                $str1 = chr($str1);
                $str2 = 71+$spk*3;
                $str2 = chr($str2);
                $str3 = 72+$spk*3;
                $str3 = chr($str3);
                //中文
                $collect = $spv['name'].'('.$spv['description'].')';
                $objActSheet->setCellValue($str1.$stapos1, $collect);
                $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
                $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
                //英文
                $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
                $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
                $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
                $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
            }
            //中文
            $objActSheet->setCellValue('E'.$stapos1, $cv['category']);
            //英文
            $objActSheet->setCellValue('E'.$en_stapos1, $cv['en_category']);
            $second_stapos ++ ;
            $second_count ++ ;
        }
    }                           
    //中文
    $objActSheet->setCellValue('D'.$stapos, $fvalue['category']);
    $stopos = $num + $second_count - 1;
    $mergecellstr = 'D'.$stapos.':D'.$stopos;
    $objActSheet->mergeCells($mergecellstr);
    //英文
    $objActSheet->setCellValue('D'.$en_stapos, $fvalue['en_category']);
    $en_stopos = $num + $cate_count + $second_count - 1;
    $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
    $objActSheet->mergeCells($en_mergecellstr);
}

我觉得原先的代码多一个上面的循环,时间应该多一点。。。查询1700条数据耗时84s。。。 下面附上全文代码:

    /**
     * 导出Execl
     */
    public function export(){
        import("@.ORG.PhpExcel");
        //import("Lib.ORG.PHPExcel.IOFactory",APP_PATH,".php");
        import("Lib.ORG.PHPExcel.Writer.Excel5",APP_PATH,".php");
        import("Lib.ORG.PHPExcel.Writer.Excel2007",APP_PATH,".php");

        // 创建一个处理对象实例   
        $objPHPExcel = new PHPExcel();   

        // 创建文件格式写入对象实例, uncomment   
        //$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);                // 用于其他版本格式   
        // or   
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);               // 用于 2007 格式   
        //$objWriter->setOffice2003Compatibility(true);   

        //*************************************   
        //设置文档基本属性   
//      $objProps = $objPHPExcel->getProperties();   
//      $objProps->setCreator("Test");                                          //创建人
//      $objProps->setLastModifiedBy("Test");                                   //最后修改人
//      $objProps->setTitle("Office XLS Test Document");                        //标题
//      $objProps->setSubject("Office XLS Test Document, Demo");                //题目
//      $objProps->setDescription("Test document, generated by PHPExcel.");     //描述
//      $objProps->setKeywords("office excel PHPExcel");                        //关键字
//      $objProps->setCategory("Test");                                         //种类

        //*************************************   
        //设置当前的sheet索引,用于后续的内容操作。   
        //一般只有在使用多个sheet的时候才需要显示调用。   
        //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0   
        $objPHPExcel->setActiveSheetIndex(0);   

        $objActSheet = $objPHPExcel->getActiveSheet();   

        //设置当前活动sheet的名称   
        //$objActSheet->setTitle('测试Sheet');

        //*************************************  
        //设置单元格内容   
        //   
        //由PHPExcel根据传入内容自动判断单元格内容类型   
        //$objActSheet->setCellValue('A1', '字符串内容');                        // 字符串内容   
        //$objActSheet->setCellValue('A2', 26);                                 // 数值   
        //$objActSheet->setCellValue('A3', true);                               // 布尔值   
        //$objActSheet->setCellValue('A4', '=SUM(A2:A2)');                      // 公式     

        //填入主标题
        $objActSheet->setCellValue('A1', '电子地图');
        $objActSheet->mergeCells('A1:AC1');
        //设置表头行高
        $objActSheet->getRowDimension(1)->setRowHeight(20);

        $objActSheet->setCellValue('A2', '序号');
        $objActSheet->mergeCells('A2:A4');   
        $objActSheet->setCellValue('B2', '公司名称');
        $objActSheet->mergeCells('B2:B4');  
        $objActSheet->setCellValue('C2', '简称');
        $objActSheet->mergeCells('C2:C4'); 
        $objActSheet->setCellValue('D2', '产品与技术');
        $objActSheet->mergeCells('D2:N2'); 
        $objActSheet->setCellValue('O2', '相关信息');
        $objActSheet->mergeCells('O2:S2');
        $objActSheet->setCellValue('T2', '联系信息');
        $objActSheet->mergeCells('T2:AB2');
        $objActSheet->setCellValue('AC2', '描述');
        $objActSheet->mergeCells('AC2:AC4');

        $objActSheet->setCellValue('D3', '领域');
        $objActSheet->mergeCells('D3:E3');
        $objActSheet->setCellValue('F3', '产品');
        $objActSheet->mergeCells('F3:N3');
        $objActSheet->setCellValue('O3', '技术');
        $objActSheet->mergeCells('O3:O4');
        $objActSheet->setCellValue('P3', '应用');
        $objActSheet->mergeCells('P3:P4');
        $objActSheet->setCellValue('Q3', '性质');
        $objActSheet->mergeCells('Q3:Q4');
        $objActSheet->setCellValue('R3', '成立时间');
        $objActSheet->mergeCells('R3:R4');
        $objActSheet->setCellValue('S3', '员工数');
        $objActSheet->mergeCells('S3:S4');
        $objActSheet->setCellValue('T3', '地址');
        $objActSheet->mergeCells('T3:T4');
        $objActSheet->setCellValue('U3', '电话');
        $objActSheet->mergeCells('U3:U4');
        $objActSheet->setCellValue('V3', '传真');
        $objActSheet->mergeCells('V3:V4');
        $objActSheet->setCellValue('W3', 'eMail');
        $objActSheet->mergeCells('W3:W4');
        $objActSheet->setCellValue('X3', '网站');
        $objActSheet->mergeCells('X3:X4');
        $objActSheet->setCellValue('Y3', '经度');
        $objActSheet->mergeCells('Y3:Y4');
        $objActSheet->setCellValue('Z3', '纬度');
        $objActSheet->mergeCells('Z3:Z4');
        $objActSheet->setCellValue('AA3', '园区');
        $objActSheet->mergeCells('AA3:AA4');
        $objActSheet->setCellValue('AB3', '城市');
        $objActSheet->mergeCells('AB3:AB4');

        $objActSheet->setCellValue('D4', '一级');
        $objActSheet->setCellValue('E4', '二级');
        $objActSheet->setCellValue('F4', '1');
        $objActSheet->setCellValue('G4', '品名');
        $objActSheet->setCellValue('H4', '描述');
        $objActSheet->setCellValue('I4', '2');
        $objActSheet->setCellValue('J4', '品名');
        $objActSheet->setCellValue('K4', '描述');
        $objActSheet->setCellValue('L4', '3');
        $objActSheet->setCellValue('M4', '品名');     
        $objActSheet->setCellValue('N4', '描述');

        //*************************************   
        //搜索条件
        $company_where = 1;
        $keyword = trim($_GET['keyword']);
        if($_GET['sub_category'] || $_GET['first_cid']){
            $c_uid =getuidbycate();
        }
        if($_GET['product_id']){
            $p_uid =getuidbypro($_GET['product_id']);
        }
        $uid_arr = $p_uid?array_intersect($c_uid,$p_uid):$c_uid;
        $uid_str = implode(',',$uid_arr);
        if($_GET['sub_category'] || $_GET['first_cid'] || $_GET['product_id']){
            $company_where .= ' and uid in('.$uid_str.')';
        }
        if($keyword){
            $company_where .= " and company like '%$keyword%'";
        }

        $num = 5;       //初始行数

        //公司信息  
        $company = $this->Company->field('')->where($company_where)->select();
        if($company){
            foreach((array)$company as $k => $v){
                if($v['uid']){
                    //获取产品
                    $pro = $this->Product->table(tname('product').' AS pr')->join(tname('company_product').' AS cp ON pr.id=cp.pid')->field('')->where('cp.uid='.$v['uid'])->group('pr.id')->select();
                    $company[$k]['pid'] = $pro;
                    //获取二级分类
                    $join = tname('company_category').' AS cc ON ca.id=cc.cid';
                    $field = ''; 
                    $where = "cc.uid=".$v['uid'];
                    $cate = $this->Category->table(tname('category').' AS ca')->join($join)->field($field)->where($where)->group('ca.id')->select();
                    if($cate){
                        $cate_count = count($cate);
                        $second_cate = '';
                        $first_cate_array = '';
                        foreach($cate as $k1=>$v1){
                            //获取一级分类
                            $first_cate = $this->Category->where('id='.$v1['parentid'])->find();
                            //归总公司的一级分类
                            if(!in_array($first_cate,$first_cate_array)){
                                $first_cate_array[] = $first_cate;
                            }
                            //归类二级分类下的产品
                            foreach($pro as $pk => $pv){
                                if($pv['cid'] == $v1['cid']){
                                    $cate[$k1]['pro'][] = $pv;
                                }
                            }
                        }

                        /**************************************************************************/
//                      $second_count = 0;
//                      //归总每个一级分类下的二级分类
//                      foreach($first_cate_array as $fkey => $fvalue){
//                          //一级目录起始行数
//                          $stapos = $num+$second_count;
//                          $en_stapos = $stapos+$cate_count;
//                          $second_stapos = 0;
//                          foreach($cate as $ck=>$cv){
//                              if($cv['parentid'] == $fvalue['id']){
//                                  //二级目录起始行数
//                                  $stapos1 = $stapos+$second_stapos;
//                                  $en_stapos1 = $en_stapos+$second_stapos;                                    
//                                  foreach($cv['pro'] as $spk => $spv){
//                                      $str1 = 70+$spk*3;
//                                      $str1 = chr($str1);
//                                      $str2 = 71+$spk*3;
//                                      $str2 = chr($str2);
//                                      $str3 = 72+$spk*3;
//                                      $str3 = chr($str3);
//                                      //中文
//                                      $collect = $spv['name'].'('.$spv['description'].')';
//                                      $objActSheet->setCellValue($str1.$stapos1, $collect);
//                                      $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
//                                      $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
//                                      //英文
//                                      $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
//                                      $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
//                                      $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
//                                      $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
//                                  }
//                                  //中文
//                                  $objActSheet->setCellValue('E'.$stapos1, $cv['category']);
//                                  //英文
//                                  $objActSheet->setCellValue('E'.$en_stapos1, $cv['en_category']);
//                                  $second_stapos ++ ;
//                                  $second_count ++ ;
//                              }
//                          }                           
//                          //中文
//                          $objActSheet->setCellValue('D'.$stapos, $fvalue['category']);
//                          $stopos = $num + $second_count - 1;
//                          $mergecellstr = 'D'.$stapos.':D'.$stopos;
//                          $objActSheet->mergeCells($mergecellstr);
//                          //英文
//                          $objActSheet->setCellValue('D'.$en_stapos, $fvalue['en_category']);
//                          $en_stopos = $num + $cate_count + $second_count - 1;
//                          $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
//                          $objActSheet->mergeCells($en_mergecellstr);
//                      }

                        /**************************************************************************/

                        //归总每个一级分类下的二级分类
                        foreach($first_cate_array as $fkey => $fvalue){
                            foreach($cate as $ck=>$cv){                             
                                if($cv['parentid'] == $fvalue['id']){
                                    $first_cate_array[$fkey]['second_cate'][] = $cv;
                                }
                            }

                            //获得每个一级分类下二级分类的数目
                            $second_count = count($first_cate_array[$fkey]['second_cate']); 
                            $first_cate_array[$fkey]['second_count'] = $second_count == 0 ? 1 : $second_count;  
                        }

                        /*************************************************************************/
                        foreach($first_cate_array as $fk => $fv){
                            //一级目录起始行数
                            if($fk == 0){
                                $stapos = $num;
                                $en_stapos = $stapos+$cate_count;
                            }else{
                                $stapos = $stapos+$first_cate_array[$fk-1]['second_count'];
                                $en_stapos = $stapos+$cate_count;
                            }

                            foreach($fv['second_cate'] as $sck => $scv){
                                //二级目录起始行数
                                $stapos1 = $stapos+$sck;
                                $en_stapos1 = $en_stapos+$sck;
                                foreach($scv['pro'] as $spk => $spv){
                                    $str1 = 70+$spk*3;
                                    $str1 = chr($str1);
                                    $str2 = 71+$spk*3;
                                    $str2 = chr($str2);
                                    $str3 = 72+$spk*3;
                                    $str3 = chr($str3);
                                    //中文
                                    $collect = $spv['name'].'('.$spv['description'].')';
                                    $objActSheet->setCellValue($str1.$stapos1, $collect);
                                    $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
                                    $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
                                    //英文
                                    $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
                                    $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
                                    $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
                                    $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
                                }
                                //中文
                                $objActSheet->setCellValue('E'.$stapos1, $scv['category']);
                                //英文
                                $objActSheet->setCellValue('E'.$en_stapos1, $scv['en_category']);
                            }
                            //中文
                            $objActSheet->setCellValue('D'.$stapos, $fv['category']);
                            $stopos = $stapos + $fv['second_count'] - 1;
                            $mergecellstr = 'D'.$stapos.':D'.$stopos;
                            $objActSheet->mergeCells($mergecellstr);
                            //英文
                            $objActSheet->setCellValue('D'.$en_stapos, $fv['en_category']);
                            $en_stopos = $en_stapos + $fv['second_count'] - 1;
                            $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
                            $objActSheet->mergeCells($en_mergecellstr);
                        }
                        $company[$k]['first_cid'] = $first_cate_array;
                    }
                    //$company[$k]['first_count'] = count($company[$k]['first_cid']);
                    //获得二级分类的总数目
                    $company[$k]['total_rows'] = count($cate);

                    //园区
                    if($v['park']){
                        $park = my_explode($v['park']);
                        $company[$k]['park'] = my_arrytostr(array_unique($park),'Park','name');
                        $company[$k]['en_park'] = my_arrytostr(array_unique($park),'Park','en_name');
                    }else{
                        $v['park'] = '';
                        $v['en_park'] = '';
                    }
                    //城市
                    if($v['city']){
                        $city = C('CITY');
                        foreach($city as $ck=>$cv){
                            if($v['city'] == $cv['id']){
                                $v['city'] = $cv['name'];
                                $v['en_city'] = $cv['en_name'];
                            }
                        }
                    }

                    $total_rows = $company[$k]['total_rows'] == 0 ? 1 : $company[$k]['total_rows'];
                    //中文*************************************   
                    $stop = $num - 1 + $total_rows;

                    $objActSheet->setCellValue('A'.$num, $v['uid']);
                    $objActSheet->mergeCells('A'.$num.':A'.$stop);
                    $objActSheet->setCellValue('B'.$num, $v['company']);
                    $objActSheet->mergeCells('B'.$num.':B'.$stop);
                    $objActSheet->setCellValue('C'.$num, $v['com']);
                    $objActSheet->mergeCells('C'.$num.':C'.$stop);
//
//                  $objActSheet->setCellValue('D'.$num, $v['com']);
//                  $objActSheet->setCellValue('E'.$num, $v['uid']);
//                  $objActSheet->setCellValue('F'.$num, $v['uid']);
//                  $objActSheet->setCellValue('G'.$num, $v['uid']);
//                  $objActSheet->setCellValue('H'.$num, $v['uid']);
//                  $objActSheet->setCellValue('I'.$num, $v['uid']);
//                  $objActSheet->setCellValue('J'.$num, $v['uid']);
//                  $objActSheet->setCellValue('K'.$num, $v['uid']);
//                  $objActSheet->setCellValue('L'.$num, $v['uid']);
//                  $objActSheet->setCellValue('M'.$num, $v['uid']);
//                  $objActSheet->setCellValue('N'.$num, $v['uid']);
//
                    $objActSheet->setCellValue('O'.$num, $v['technology']);
                    $objActSheet->mergeCells('O'.$num.':O'.$stop);
                    $objActSheet->setCellValue('P'.$num, $v['application']);
                    $objActSheet->mergeCells('P'.$num.':P'.$stop);
                    $objActSheet->setCellValue('Q'.$num, $v['nature']);
                    $objActSheet->mergeCells('Q'.$num.':Q'.$stop);
                    $objActSheet->setCellValue('R'.$num, $v['established']);
                    $objActSheet->mergeCells('R'.$num.':R'.$stop);
                    $objActSheet->setCellValue('S'.$num, $v['employees']);
                    $objActSheet->mergeCells('S'.$num.':S'.$stop);
                    $objActSheet->setCellValue('T'.$num, $v['address']);
                    $objActSheet->mergeCells('T'.$num.':T'.$stop);
                    $objActSheet->setCellValue('U'.$num, $v['phone']);
                    $objActSheet->mergeCells('U'.$num.':U'.$stop);
                    $objActSheet->setCellValue('V'.$num, $v['fax']);
                    $objActSheet->mergeCells('V'.$num.':V'.$stop);
                    $objActSheet->setCellValue('W'.$num, $v['email']);
                    $objActSheet->mergeCells('W'.$num.':W'.$stop);
                    $objActSheet->setCellValue('X'.$num, $v['website']);
                    $objActSheet->mergeCells('X'.$num.':X'.$stop);
                    $objActSheet->setCellValue('Y'.$num, $v['longitude']);
                    $objActSheet->mergeCells('Y'.$num.':Y'.$stop);
                    $objActSheet->setCellValue('Z'.$num, $v['latitude']);
                    $objActSheet->mergeCells('Z'.$num.':Z'.$stop);
                    $objActSheet->setCellValue('AA'.$num, $v['park']);
                    $objActSheet->mergeCells('AA'.$num.':AA'.$stop);
                    $objActSheet->setCellValue('AB'.$num, $v['city']);
                    $objActSheet->mergeCells('AB'.$num.':AB'.$stop);
                    $objActSheet->setCellValue('AC'.$num, $v['description']);
                    $objActSheet->mergeCells('AC'.$num.':AC'.$stop);

                    //英文*************************************   
                    $en_start = $num + $total_rows;
                    $en_stop = $en_start - 1 + $total_rows;

                    $objActSheet->setCellValue('A'.$en_start, $v['uid']);
                    $objActSheet->mergeCells('A'.$en_start.':A'.$en_stop);
                    $objActSheet->setCellValue('B'.$en_start, $v['en_company']);
                    $objActSheet->mergeCells('B'.$en_start.':B'.$en_stop);
                    $objActSheet->setCellValue('C'.$en_start, $v['en_com']);
                    $objActSheet->mergeCells('C'.$en_start.':C'.$en_stop);
//
//                  $objActSheet->setCellValue('D'.$en_start, $v['com']);
//                  $objActSheet->setCellValue('E'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('F'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('G'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('H'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('I'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('J'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('K'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('L'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('M'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('N'.$en_start, $v['uid']);
//
                    $objActSheet->setCellValue('O'.$en_start, $v['en_technology']);
                    $objActSheet->mergeCells('O'.$en_start.':O'.$en_stop);
                    $objActSheet->setCellValue('P'.$en_start, $v['en_application']);
                    $objActSheet->mergeCells('P'.$en_start.':P'.$en_stop);
                    $objActSheet->setCellValue('Q'.$en_start, $v['en_nature']);
                    $objActSheet->mergeCells('Q'.$en_start.':Q'.$en_stop);
                    $objActSheet->setCellValue('R'.$en_start, $v['established']);
                    $objActSheet->mergeCells('R'.$en_start.':R'.$en_stop);
                    $objActSheet->setCellValue('S'.$en_start, $v['employees']);
                    $objActSheet->mergeCells('S'.$en_start.':S'.$en_stop);
                    $objActSheet->setCellValue('T'.$en_start, $v['en_address']);
                    $objActSheet->mergeCells('T'.$en_start.':T'.$en_stop);
                    $objActSheet->setCellValue('U'.$en_start, $v['phone']);
                    $objActSheet->mergeCells('U'.$en_start.':U'.$en_stop);
                    $objActSheet->setCellValue('V'.$en_start, $v['fax']);
                    $objActSheet->mergeCells('V'.$en_start.':V'.$en_stop);
                    $objActSheet->setCellValue('W'.$en_start, $v['email']);
                    $objActSheet->mergeCells('W'.$en_start.':W'.$en_stop);
                    $objActSheet->setCellValue('X'.$en_start, $v['website']);
                    $objActSheet->mergeCells('X'.$en_start.':X'.$en_stop);
                    $objActSheet->setCellValue('Y'.$en_start, $v['longitude']);
                    $objActSheet->mergeCells('Y'.$en_start.':Y'.$en_stop);
                    $objActSheet->setCellValue('Z'.$en_start, $v['latitude']);
                    $objActSheet->mergeCells('Z'.$en_start.':Z'.$en_stop);
                    $objActSheet->setCellValue('AA'.$en_start, $v['en_park']);
                    $objActSheet->mergeCells('AA'.$en_start.':AA'.$en_stop);
                    $objActSheet->setCellValue('AB'.$en_start, $v['en_city']);
                    $objActSheet->mergeCells('AB'.$en_start.':AB'.$en_stop);
                    $objActSheet->setCellValue('AC'.$en_start, $v['en_description']);
                    $objActSheet->mergeCells('AC'.$en_start.':AC'.$en_stop);

                    $num += $company[$k]['total_rows'] * 2;                         //下一公司的起始行数
                }
            }
        }
        //*************************************   
        //设置单元格样式   
        //

        //设置主标题字体  
        $objStyleA1 = $objActSheet->getStyle('A1'); 
        $objFontA1 = $objStyleA1->getFont();   
        $objFontA1->setName('宋体');   
        $objFontA1->setSize(14);   
        //$objFontA1->setBold(false);   
        //$objFontA1->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);   
        //$objFontA1->getColor()->setARGB('FF999999');   
        //************************************* 
        //设置表头字体   
        $objStyleA2 = $objActSheet->getStyle('A2:AC4');
        $objFontA2 = $objStyleA2->getFont();   
        $objFontA2->setName('Cambria');   
        $objFontA2->setSize(10);

        //设置填充颜色   
        $objFillA2 = $objStyleA2->getFill();   
        $objFillA2->setFillType(PHPExcel_Style_Fill::FILL_SOLID);   
        $objFillA2->getStartColor()->setARGB('B7B7B7');  

        //从指定的单元格复制样式信息.   
        //$objActSheet->duplicateStyle($objStyleA2, 'A2:AC4');
        //************************************* 
        //设置表格字体

        $objActSheet->getStyle('A1:AC'.($num-1))->getFont()->setName('Cambria');
        $objActSheet->getStyle('A1:AC'.($num-1))->getFont()->setSize(9);

        //显示网格线: 
        $objActSheet->setShowGridlines(true);

        //设置自动换行
        $objActSheet->getStyle('A1:AC'.($num-1))->getAlignment()->setWrapText(true);

        //所有垂直居中
        $objActSheet->getStyle('A1:AC'.($num-1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //前4行水平居中
        $objActSheet->getStyle('A1:AC4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //后面水平左对齐
        $objActSheet->getStyle('A5:AC'.($num-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        //设置单元格边框
        $objActSheet->getStyle('A1:AC'.($num-1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A1:AC'.($num-1))->getBorders()->getAllBorders()->getColor()->setARGB('000000');

        //*************************************   
        //
输出内容 //下面的路径按照你PHPExcel的路径来修改 // $outputFileName = "output.xlsx"; //$outputFileName = "output.xls"; //到文件 //$objWriter->save($outputFileName); //or //到浏览器 header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$outputFileName.'"'); header("Content-Transfer-Encoding: binary"); header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $objWriter->save('php://output'); }

回复内容:

做php导出,需要循环输出数据到execl中,一开始写了一个,觉得时间有点长,就缩短了下,发现时间还变多了,求教这是为什么啊?谢谢了先。

原先的代码片段:

//归总每个一级分类下的二级分类
foreach($first_cate_array as $fkey => $fvalue){
    foreach($cate as $ck=>$cv){                             
        if($cv['parentid'] == $fvalue['id']){
            $first_cate_array[$fkey]['second_cate'][] = $cv;
        }
    }

    //获得每个一级分类下二级分类的数目
    $second_count = count($first_cate_array[$fkey]['second_cate']); 
    $first_cate_array[$fkey]['second_count'] = $second_count == 0 ? 1 : $second_count;  
}

/*************************************************************************/
foreach($first_cate_array as $fk => $fv){
    //一级目录起始行数
    if($fk == 0){
        $stapos = $num;
        $en_stapos = $stapos+$cate_count;
    }else{
        $stapos = $stapos+$first_cate_array[$fk-1]['second_count'];
        $en_stapos = $stapos+$cate_count;
    }

    foreach($fv['second_cate'] as $sck => $scv){
        //二级目录起始行数
        $stapos1 = $stapos+$sck;
        $en_stapos1 = $en_stapos+$sck;
        foreach($scv['pro'] as $spk => $spv){
            $str1 = 70+$spk*3;
            $str1 = chr($str1);
            $str2 = 71+$spk*3;
            $str2 = chr($str2);
            $str3 = 72+$spk*3;
            $str3 = chr($str3);
            //中文
            $collect = $spv['name'].'('.$spv['description'].')';
            $objActSheet->setCellValue($str1.$stapos1, $collect);
            $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
            $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
            //英文
            $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
            $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
            $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
            $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
        }
        //中文
        $objActSheet->setCellValue('E'.$stapos1, $scv['category']);
        //英文
        $objActSheet->setCellValue('E'.$en_stapos1, $scv['en_category']);
    }
    //中文
    $objActSheet->setCellValue('D'.$stapos, $fv['category']);
    $stopos = $stapos + $fv['second_count'] - 1;
    $mergecellstr = 'D'.$stapos.':D'.$stopos;
    $objActSheet->mergeCells($mergecellstr);
    //英文
    $objActSheet->setCellValue('D'.$en_stapos, $fv['en_category']);
    $en_stopos = $en_stapos + $fv['second_count'] - 1;
    $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
    $objActSheet->mergeCells($en_mergecellstr);
}

修改以后的片段:

$second_count = 0;
//归总每个一级分类下的二级分类
foreach($first_cate_array as $fkey => $fvalue){
    //一级目录起始行数
    $stapos = $num+$second_count;
    $en_stapos = $stapos+$cate_count;
    $second_stapos = 0;
    foreach($cate as $ck=>$cv){
        if($cv['parentid'] == $fvalue['id']){
            //二级目录起始行数
            $stapos1 = $stapos+$second_stapos;
            $en_stapos1 = $en_stapos+$second_stapos;                                    
            foreach($cv['pro'] as $spk => $spv){
                $str1 = 70+$spk*3;
                $str1 = chr($str1);
                $str2 = 71+$spk*3;
                $str2 = chr($str2);
                $str3 = 72+$spk*3;
                $str3 = chr($str3);
                //中文
                $collect = $spv['name'].'('.$spv['description'].')';
                $objActSheet->setCellValue($str1.$stapos1, $collect);
                $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
                $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
                //英文
                $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
                $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
                $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
                $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
            }
            //中文
            $objActSheet->setCellValue('E'.$stapos1, $cv['category']);
            //英文
            $objActSheet->setCellValue('E'.$en_stapos1, $cv['en_category']);
            $second_stapos ++ ;
            $second_count ++ ;
        }
    }                           
    //中文
    $objActSheet->setCellValue('D'.$stapos, $fvalue['category']);
    $stopos = $num + $second_count - 1;
    $mergecellstr = 'D'.$stapos.':D'.$stopos;
    $objActSheet->mergeCells($mergecellstr);
    //英文
    $objActSheet->setCellValue('D'.$en_stapos, $fvalue['en_category']);
    $en_stopos = $num + $cate_count + $second_count - 1;
    $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
    $objActSheet->mergeCells($en_mergecellstr);
}

我觉得原先的代码多一个上面的循环,时间应该多一点。。。查询1700条数据耗时84s。。。 下面附上全文代码:

    /**
     * 导出Execl
     */
    public function export(){
        import("@.ORG.PhpExcel");
        //import("Lib.ORG.PHPExcel.IOFactory",APP_PATH,".php");
        import("Lib.ORG.PHPExcel.Writer.Excel5",APP_PATH,".php");
        import("Lib.ORG.PHPExcel.Writer.Excel2007",APP_PATH,".php");

        // 创建一个处理对象实例   
        $objPHPExcel = new PHPExcel();   

        // 创建文件格式写入对象实例, uncomment   
        //$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);                // 用于其他版本格式   
        // or   
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);               // 用于 2007 格式   
        //$objWriter->setOffice2003Compatibility(true);   

        //*************************************   
        //设置文档基本属性   
//      $objProps = $objPHPExcel->getProperties();   
//      $objProps->setCreator("Test");                                          //创建人
//      $objProps->setLastModifiedBy("Test");                                   //最后修改人
//      $objProps->setTitle("Office XLS Test Document");                        //标题
//      $objProps->setSubject("Office XLS Test Document, Demo");                //题目
//      $objProps->setDescription("Test document, generated by PHPExcel.");     //描述
//      $objProps->setKeywords("office excel PHPExcel");                        //关键字
//      $objProps->setCategory("Test");                                         //种类

        //*************************************   
        //设置当前的sheet索引,用于后续的内容操作。   
        //一般只有在使用多个sheet的时候才需要显示调用。   
        //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0   
        $objPHPExcel->setActiveSheetIndex(0);   

        $objActSheet = $objPHPExcel->getActiveSheet();   

        //设置当前活动sheet的名称   
        //$objActSheet->setTitle('测试Sheet');

        //*************************************  
        //设置单元格内容   
        //   
        //由PHPExcel根据传入内容自动判断单元格内容类型   
        //$objActSheet->setCellValue('A1', '字符串内容');                        // 字符串内容   
        //$objActSheet->setCellValue('A2', 26);                                 // 数值   
        //$objActSheet->setCellValue('A3', true);                               // 布尔值   
        //$objActSheet->setCellValue('A4', '=SUM(A2:A2)');                      // 公式     

        //填入主标题
        $objActSheet->setCellValue('A1', '电子地图');
        $objActSheet->mergeCells('A1:AC1');
        //设置表头行高
        $objActSheet->getRowDimension(1)->setRowHeight(20);

        $objActSheet->setCellValue('A2', '序号');
        $objActSheet->mergeCells('A2:A4');   
        $objActSheet->setCellValue('B2', '公司名称');
        $objActSheet->mergeCells('B2:B4');  
        $objActSheet->setCellValue('C2', '简称');
        $objActSheet->mergeCells('C2:C4'); 
        $objActSheet->setCellValue('D2', '产品与技术');
        $objActSheet->mergeCells('D2:N2'); 
        $objActSheet->setCellValue('O2', '相关信息');
        $objActSheet->mergeCells('O2:S2');
        $objActSheet->setCellValue('T2', '联系信息');
        $objActSheet->mergeCells('T2:AB2');
        $objActSheet->setCellValue('AC2', '描述');
        $objActSheet->mergeCells('AC2:AC4');

        $objActSheet->setCellValue('D3', '领域');
        $objActSheet->mergeCells('D3:E3');
        $objActSheet->setCellValue('F3', '产品');
        $objActSheet->mergeCells('F3:N3');
        $objActSheet->setCellValue('O3', '技术');
        $objActSheet->mergeCells('O3:O4');
        $objActSheet->setCellValue('P3', '应用');
        $objActSheet->mergeCells('P3:P4');
        $objActSheet->setCellValue('Q3', '性质');
        $objActSheet->mergeCells('Q3:Q4');
        $objActSheet->setCellValue('R3', '成立时间');
        $objActSheet->mergeCells('R3:R4');
        $objActSheet->setCellValue('S3', '员工数');
        $objActSheet->mergeCells('S3:S4');
        $objActSheet->setCellValue('T3', '地址');
        $objActSheet->mergeCells('T3:T4');
        $objActSheet->setCellValue('U3', '电话');
        $objActSheet->mergeCells('U3:U4');
        $objActSheet->setCellValue('V3', '传真');
        $objActSheet->mergeCells('V3:V4');
        $objActSheet->setCellValue('W3', 'eMail');
        $objActSheet->mergeCells('W3:W4');
        $objActSheet->setCellValue('X3', '网站');
        $objActSheet->mergeCells('X3:X4');
        $objActSheet->setCellValue('Y3', '经度');
        $objActSheet->mergeCells('Y3:Y4');
        $objActSheet->setCellValue('Z3', '纬度');
        $objActSheet->mergeCells('Z3:Z4');
        $objActSheet->setCellValue('AA3', '园区');
        $objActSheet->mergeCells('AA3:AA4');
        $objActSheet->setCellValue('AB3', '城市');
        $objActSheet->mergeCells('AB3:AB4');

        $objActSheet->setCellValue('D4', '一级');
        $objActSheet->setCellValue('E4', '二级');
        $objActSheet->setCellValue('F4', '1');
        $objActSheet->setCellValue('G4', '品名');
        $objActSheet->setCellValue('H4', '描述');
        $objActSheet->setCellValue('I4', '2');
        $objActSheet->setCellValue('J4', '品名');
        $objActSheet->setCellValue('K4', '描述');
        $objActSheet->setCellValue('L4', '3');
        $objActSheet->setCellValue('M4', '品名');     
        $objActSheet->setCellValue('N4', '描述');

        //*************************************   
        //搜索条件
        $company_where = 1;
        $keyword = trim($_GET['keyword']);
        if($_GET['sub_category'] || $_GET['first_cid']){
            $c_uid =getuidbycate();
        }
        if($_GET['product_id']){
            $p_uid =getuidbypro($_GET['product_id']);
        }
        $uid_arr = $p_uid?array_intersect($c_uid,$p_uid):$c_uid;
        $uid_str = implode(',',$uid_arr);
        if($_GET['sub_category'] || $_GET['first_cid'] || $_GET['product_id']){
            $company_where .= ' and uid in('.$uid_str.')';
        }
        if($keyword){
            $company_where .= " and company like '%$keyword%'";
        }

        $num = 5;       //初始行数

        //公司信息  
        $company = $this->Company->field('')->where($company_where)->select();
        if($company){
            foreach((array)$company as $k => $v){
                if($v['uid']){
                    //获取产品
                    $pro = $this->Product->table(tname('product').' AS pr')->join(tname('company_product').' AS cp ON pr.id=cp.pid')->field('')->where('cp.uid='.$v['uid'])->group('pr.id')->select();
                    $company[$k]['pid'] = $pro;
                    //获取二级分类
                    $join = tname('company_category').' AS cc ON ca.id=cc.cid';
                    $field = ''; 
                    $where = "cc.uid=".$v['uid'];
                    $cate = $this->Category->table(tname('category').' AS ca')->join($join)->field($field)->where($where)->group('ca.id')->select();
                    if($cate){
                        $cate_count = count($cate);
                        $second_cate = '';
                        $first_cate_array = '';
                        foreach($cate as $k1=>$v1){
                            //获取一级分类
                            $first_cate = $this->Category->where('id='.$v1['parentid'])->find();
                            //归总公司的一级分类
                            if(!in_array($first_cate,$first_cate_array)){
                                $first_cate_array[] = $first_cate;
                            }
                            //归类二级分类下的产品
                            foreach($pro as $pk => $pv){
                                if($pv['cid'] == $v1['cid']){
                                    $cate[$k1]['pro'][] = $pv;
                                }
                            }
                        }

                        /**************************************************************************/
//                      $second_count = 0;
//                      //归总每个一级分类下的二级分类
//                      foreach($first_cate_array as $fkey => $fvalue){
//                          //一级目录起始行数
//                          $stapos = $num+$second_count;
//                          $en_stapos = $stapos+$cate_count;
//                          $second_stapos = 0;
//                          foreach($cate as $ck=>$cv){
//                              if($cv['parentid'] == $fvalue['id']){
//                                  //二级目录起始行数
//                                  $stapos1 = $stapos+$second_stapos;
//                                  $en_stapos1 = $en_stapos+$second_stapos;                                    
//                                  foreach($cv['pro'] as $spk => $spv){
//                                      $str1 = 70+$spk*3;
//                                      $str1 = chr($str1);
//                                      $str2 = 71+$spk*3;
//                                      $str2 = chr($str2);
//                                      $str3 = 72+$spk*3;
//                                      $str3 = chr($str3);
//                                      //中文
//                                      $collect = $spv['name'].'('.$spv['description'].')';
//                                      $objActSheet->setCellValue($str1.$stapos1, $collect);
//                                      $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
//                                      $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
//                                      //英文
//                                      $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
//                                      $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
//                                      $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
//                                      $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
//                                  }
//                                  //中文
//                                  $objActSheet->setCellValue('E'.$stapos1, $cv['category']);
//                                  //英文
//                                  $objActSheet->setCellValue('E'.$en_stapos1, $cv['en_category']);
//                                  $second_stapos ++ ;
//                                  $second_count ++ ;
//                              }
//                          }                           
//                          //中文
//                          $objActSheet->setCellValue('D'.$stapos, $fvalue['category']);
//                          $stopos = $num + $second_count - 1;
//                          $mergecellstr = 'D'.$stapos.':D'.$stopos;
//                          $objActSheet->mergeCells($mergecellstr);
//                          //英文
//                          $objActSheet->setCellValue('D'.$en_stapos, $fvalue['en_category']);
//                          $en_stopos = $num + $cate_count + $second_count - 1;
//                          $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
//                          $objActSheet->mergeCells($en_mergecellstr);
//                      }

                        /**************************************************************************/

                        //归总每个一级分类下的二级分类
                        foreach($first_cate_array as $fkey => $fvalue){
                            foreach($cate as $ck=>$cv){                             
                                if($cv['parentid'] == $fvalue['id']){
                                    $first_cate_array[$fkey]['second_cate'][] = $cv;
                                }
                            }

                            //获得每个一级分类下二级分类的数目
                            $second_count = count($first_cate_array[$fkey]['second_cate']); 
                            $first_cate_array[$fkey]['second_count'] = $second_count == 0 ? 1 : $second_count;  
                        }

                        /*************************************************************************/
                        foreach($first_cate_array as $fk => $fv){
                            //一级目录起始行数
                            if($fk == 0){
                                $stapos = $num;
                                $en_stapos = $stapos+$cate_count;
                            }else{
                                $stapos = $stapos+$first_cate_array[$fk-1]['second_count'];
                                $en_stapos = $stapos+$cate_count;
                            }

                            foreach($fv['second_cate'] as $sck => $scv){
                                //二级目录起始行数
                                $stapos1 = $stapos+$sck;
                                $en_stapos1 = $en_stapos+$sck;
                                foreach($scv['pro'] as $spk => $spv){
                                    $str1 = 70+$spk*3;
                                    $str1 = chr($str1);
                                    $str2 = 71+$spk*3;
                                    $str2 = chr($str2);
                                    $str3 = 72+$spk*3;
                                    $str3 = chr($str3);
                                    //中文
                                    $collect = $spv['name'].'('.$spv['description'].')';
                                    $objActSheet->setCellValue($str1.$stapos1, $collect);
                                    $objActSheet->setCellValue($str2.$stapos1, $spv['name']);
                                    $objActSheet->setCellValue($str3.$stapos1, $spv['description']);
                                    //英文
                                    $en_collect = $spv['en_name'].'('.$spv['en_description'].')';
                                    $objActSheet->setCellValue($str1.$en_stapos1, $en_collect);
                                    $objActSheet->setCellValue($str2.$en_stapos1, $spv['en_name']);
                                    $objActSheet->setCellValue($str3.$en_stapos1, $spv['en_description']);
                                }
                                //中文
                                $objActSheet->setCellValue('E'.$stapos1, $scv['category']);
                                //英文
                                $objActSheet->setCellValue('E'.$en_stapos1, $scv['en_category']);
                            }
                            //中文
                            $objActSheet->setCellValue('D'.$stapos, $fv['category']);
                            $stopos = $stapos + $fv['second_count'] - 1;
                            $mergecellstr = 'D'.$stapos.':D'.$stopos;
                            $objActSheet->mergeCells($mergecellstr);
                            //英文
                            $objActSheet->setCellValue('D'.$en_stapos, $fv['en_category']);
                            $en_stopos = $en_stapos + $fv['second_count'] - 1;
                            $en_mergecellstr = 'D'.$en_stapos.':D'.$en_stopos;
                            $objActSheet->mergeCells($en_mergecellstr);
                        }
                        $company[$k]['first_cid'] = $first_cate_array;
                    }
                    //$company[$k]['first_count'] = count($company[$k]['first_cid']);
                    //获得二级分类的总数目
                    $company[$k]['total_rows'] = count($cate);

                    //园区
                    if($v['park']){
                        $park = my_explode($v['park']);
                        $company[$k]['park'] = my_arrytostr(array_unique($park),'Park','name');
                        $company[$k]['en_park'] = my_arrytostr(array_unique($park),'Park','en_name');
                    }else{
                        $v['park'] = '';
                        $v['en_park'] = '';
                    }
                    //城市
                    if($v['city']){
                        $city = C('CITY');
                        foreach($city as $ck=>$cv){
                            if($v['city'] == $cv['id']){
                                $v['city'] = $cv['name'];
                                $v['en_city'] = $cv['en_name'];
                            }
                        }
                    }

                    $total_rows = $company[$k]['total_rows'] == 0 ? 1 : $company[$k]['total_rows'];
                    //中文*************************************   
                    $stop = $num - 1 + $total_rows;

                    $objActSheet->setCellValue('A'.$num, $v['uid']);
                    $objActSheet->mergeCells('A'.$num.':A'.$stop);
                    $objActSheet->setCellValue('B'.$num, $v['company']);
                    $objActSheet->mergeCells('B'.$num.':B'.$stop);
                    $objActSheet->setCellValue('C'.$num, $v['com']);
                    $objActSheet->mergeCells('C'.$num.':C'.$stop);
//
//                  $objActSheet->setCellValue('D'.$num, $v['com']);
//                  $objActSheet->setCellValue('E'.$num, $v['uid']);
//                  $objActSheet->setCellValue('F'.$num, $v['uid']);
//                  $objActSheet->setCellValue('G'.$num, $v['uid']);
//                  $objActSheet->setCellValue('H'.$num, $v['uid']);
//                  $objActSheet->setCellValue('I'.$num, $v['uid']);
//                  $objActSheet->setCellValue('J'.$num, $v['uid']);
//                  $objActSheet->setCellValue('K'.$num, $v['uid']);
//                  $objActSheet->setCellValue('L'.$num, $v['uid']);
//                  $objActSheet->setCellValue('M'.$num, $v['uid']);
//                  $objActSheet->setCellValue('N'.$num, $v['uid']);
//
                    $objActSheet->setCellValue('O'.$num, $v['technology']);
                    $objActSheet->mergeCells('O'.$num.':O'.$stop);
                    $objActSheet->setCellValue('P'.$num, $v['application']);
                    $objActSheet->mergeCells('P'.$num.':P'.$stop);
                    $objActSheet->setCellValue('Q'.$num, $v['nature']);
                    $objActSheet->mergeCells('Q'.$num.':Q'.$stop);
                    $objActSheet->setCellValue('R'.$num, $v['established']);
                    $objActSheet->mergeCells('R'.$num.':R'.$stop);
                    $objActSheet->setCellValue('S'.$num, $v['employees']);
                    $objActSheet->mergeCells('S'.$num.':S'.$stop);
                    $objActSheet->setCellValue('T'.$num, $v['address']);
                    $objActSheet->mergeCells('T'.$num.':T'.$stop);
                    $objActSheet->setCellValue('U'.$num, $v['phone']);
                    $objActSheet->mergeCells('U'.$num.':U'.$stop);
                    $objActSheet->setCellValue('V'.$num, $v['fax']);
                    $objActSheet->mergeCells('V'.$num.':V'.$stop);
                    $objActSheet->setCellValue('W'.$num, $v['email']);
                    $objActSheet->mergeCells('W'.$num.':W'.$stop);
                    $objActSheet->setCellValue('X'.$num, $v['website']);
                    $objActSheet->mergeCells('X'.$num.':X'.$stop);
                    $objActSheet->setCellValue('Y'.$num, $v['longitude']);
                    $objActSheet->mergeCells('Y'.$num.':Y'.$stop);
                    $objActSheet->setCellValue('Z'.$num, $v['latitude']);
                    $objActSheet->mergeCells('Z'.$num.':Z'.$stop);
                    $objActSheet->setCellValue('AA'.$num, $v['park']);
                    $objActSheet->mergeCells('AA'.$num.':AA'.$stop);
                    $objActSheet->setCellValue('AB'.$num, $v['city']);
                    $objActSheet->mergeCells('AB'.$num.':AB'.$stop);
                    $objActSheet->setCellValue('AC'.$num, $v['description']);
                    $objActSheet->mergeCells('AC'.$num.':AC'.$stop);

                    //英文*************************************   
                    $en_start = $num + $total_rows;
                    $en_stop = $en_start - 1 + $total_rows;

                    $objActSheet->setCellValue('A'.$en_start, $v['uid']);
                    $objActSheet->mergeCells('A'.$en_start.':A'.$en_stop);
                    $objActSheet->setCellValue('B'.$en_start, $v['en_company']);
                    $objActSheet->mergeCells('B'.$en_start.':B'.$en_stop);
                    $objActSheet->setCellValue('C'.$en_start, $v['en_com']);
                    $objActSheet->mergeCells('C'.$en_start.':C'.$en_stop);
//
//                  $objActSheet->setCellValue('D'.$en_start, $v['com']);
//                  $objActSheet->setCellValue('E'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('F'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('G'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('H'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('I'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('J'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('K'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('L'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('M'.$en_start, $v['uid']);
//                  $objActSheet->setCellValue('N'.$en_start, $v['uid']);
//
                    $objActSheet->setCellValue('O'.$en_start, $v['en_technology']);
                    $objActSheet->mergeCells('O'.$en_start.':O'.$en_stop);
                    $objActSheet->setCellValue('P'.$en_start, $v['en_application']);
                    $objActSheet->mergeCells('P'.$en_start.':P'.$en_stop);
                    $objActSheet->setCellValue('Q'.$en_start, $v['en_nature']);
                    $objActSheet->mergeCells('Q'.$en_start.':Q'.$en_stop);
                    $objActSheet->setCellValue('R'.$en_start, $v['established']);
                    $objActSheet->mergeCells('R'.$en_start.':R'.$en_stop);
                    $objActSheet->setCellValue('S'.$en_start, $v['employees']);
                    $objActSheet->mergeCells('S'.$en_start.':S'.$en_stop);
                    $objActSheet->setCellValue('T'.$en_start, $v['en_address']);
                    $objActSheet->mergeCells('T'.$en_start.':T'.$en_stop);
                    $objActSheet->setCellValue('U'.$en_start, $v['phone']);
                    $objActSheet->mergeCells('U'.$en_start.':U'.$en_stop);
                    $objActSheet->setCellValue('V'.$en_start, $v['fax']);
                    $objActSheet->mergeCells('V'.$en_start.':V'.$en_stop);
                    $objActSheet->setCellValue('W'.$en_start, $v['email']);
                    $objActSheet->mergeCells('W'.$en_start.':W'.$en_stop);
                    $objActSheet->setCellValue('X'.$en_start, $v['website']);
                    $objActSheet->mergeCells('X'.$en_start.':X'.$en_stop);
                    $objActSheet->setCellValue('Y'.$en_start, $v['longitude']);
                    $objActSheet->mergeCells('Y'.$en_start.':Y'.$en_stop);
                    $objActSheet->setCellValue('Z'.$en_start, $v['latitude']);
                    $objActSheet->mergeCells('Z'.$en_start.':Z'.$en_stop);
                    $objActSheet->setCellValue('AA'.$en_start, $v['en_park']);
                    $objActSheet->mergeCells('AA'.$en_start.':AA'.$en_stop);
                    $objActSheet->setCellValue('AB'.$en_start, $v['en_city']);
                    $objActSheet->mergeCells('AB'.$en_start.':AB'.$en_stop);
                    $objActSheet->setCellValue('AC'.$en_start, $v['en_description']);
                    $objActSheet->mergeCells('AC'.$en_start.':AC'.$en_stop);

                    $num += $company[$k]['total_rows'] * 2;                         //下一公司的起始行数
                }
            }
        }
        //*************************************   
        //设置单元格样式   
        //

        //设置主标题字体  
        $objStyleA1 = $objActSheet->getStyle('A1'); 
        $objFontA1 = $objStyleA1->getFont();   
        $objFontA1->setName('宋体');   
        $objFontA1->setSize(14);   
        //$objFontA1->setBold(false);   
        //$objFontA1->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);   
        //$objFontA1->getColor()->setARGB('FF999999');   
        //************************************* 
        //设置表头字体   
        $objStyleA2 = $objActSheet->getStyle('A2:AC4');
        $objFontA2 = $objStyleA2->getFont();   
        $objFontA2->setName('Cambria');   
        $objFontA2->setSize(10);

        //设置填充颜色   
        $objFillA2 = $objStyleA2->getFill();   
        $objFillA2->setFillType(PHPExcel_Style_Fill::FILL_SOLID);   
        $objFillA2->getStartColor()->setARGB('B7B7B7');  

        //从指定的单元格复制样式信息.   
        //$objActSheet->duplicateStyle($objStyleA2, 'A2:AC4');
        //************************************* 
        //设置表格字体

        $objActSheet->getStyle('A1:AC'.($num                    

人气教程排行