您好，欢迎访问本站博客!联系QQ：1343013953
  • 四象限时间管理法则：①重要且紧急的事；②重要但不紧急的事；③不重要但紧急的事；④不重要且不紧急的事
> Thinkphp > Thinkphp使用PHPexcel导出数据到表格，并且合并表头单元格。

Thinkphp使用PHPexcel导出数据到表格，并且合并表头单元格。

Thinkphp wujinhua 2018-07-28 1575 次浏览 0个评论
网站分享代码

  1. 下载PHPexcel包放在TP的/ThinkPHP/Library/Org/Util/。

  2. phpexceldom.rar

  3. PHPExcel.class.php放在/ThinkPHP/Library/Org/Util/目录下面。

  4. 控制器里面写上导出代码

  5. public function dcdkytjb(){
            //$color='0xCC000000';
        $list=M('dkytjb')->group('itemname')->select();//根据单位名称进行合并
           //循环查询出来的合并单位并且循环。
           foreach($list as $key=>$value){
            $cztype['pid']=$value['item_id'];  
            $item_id=M('item')->where($cztype)->field('id')->select();
            //dump($item_id);
            foreach($item_id as $itemid){
                //dump($itemid['id']);
                $strs=$strs.$itemid['id'].',';
            }
            $strss=$strs.$value['item_id'];
            $strend=trim($strss,',');//去除最后个逗号
            $where['item_id']=array('in',$strend);
            
            $gs=M('dkytjb')->where($where)->sum('czlzkxmsize');
            //echo M('dkytjb')->getLastSql();  
           // $itemname=M('dkytjb')->where($where)->field('itemname')->select();
            
            $gs1=M('dkytjb')->where($where)->sum('czlzkxmmoney');
            $gs2=M('dkytjb')->where($where)->sum('czlzkxmlr');
            $gs3=M('dkytjb')->where($where)->sum('syxzkxmsize');
            $gs4=M('dkytjb')->where($where)->sum('syxzkxmmoney');
            $gs5=M('dkytjb')->where($where)->sum('syxzkxmlr');
            $gs6=M('dkytjb')->where($where)->sum('dzjsmoney');
            $gs7=M('dkytjb')->where($where)->sum('dzjslr');
            $gs8=M('dkytjb')->where($where)->sum('dzfzhjpcmoney');
            $gs9=M('dkytjb')->where($where)->sum('dzfzhjpclr');
            $gs10=M('dkytjb')->where($where)->sum('chmoney');
            $gs11=M('dkytjb')->where($where)->sum('chlr');
            $gs12=M('dkytjb')->where($where)->sum('gckcmoney');
            $gs13=M('dkytjb')->where($where)->sum('gckclr');
            $gs14=M('dkytjb')->where($where)->sum('kykfmoney');
            $gs15=M('dkytjb')->where($where)->sum('kykflr');
            $gs16=M('dkytjb')->where($where)->sum('qtmoney');
            $gs17=M('dkytjb')->where($where)->sum('qtlr');
            $list[$key]['gs']=$gs;
            $list[$key]['gs1']=round($gs1, 2);
            $list[$key]['gs2']=round($gs2, 2);
            $list[$key]['gs3']=$gs3;
            $list[$key]['gs4']=round($gs4, 2);
            $list[$key]['gs5']=round($gs5, 2);
            $list[$key]['gs6']=round($gs6, 2);
            $list[$key]['gs7']=round($gs7, 2);
            $list[$key]['gs8']=round($gs8, 2);
            $list[$key]['gs9']=round($gs9, 2);
            $list[$key]['gs10']=round($gs10, 2);
            $list[$key]['gs11']=round($gs11, 2);
            $list[$key]['gs12']=round($gs12, 2);
            $list[$key]['gs13']=round($gs13, 2);
            $list[$key]['gs14']=round($gs14, 2);
            $list[$key]['gs15']=round($gs15, 2);
            $list[$key]['gs16']=round($gs16, 2);
            $list[$key]['gs17']=round($gs17, 2);
            $gs18=$gs1+$gs4+$gs6+$gs8+$gs10+$gs12+$gs14+$gs16;
            $gs19=$gs2+$gs5+$gs7+$gs9+$gs11+$gs13+$gs15+$gs17;
            $list[$key]['czzj']=round($gs18, 2);
            $list[$key]['llzj']=round($gs19, 2);
            $list[$key]['dwmc']=$value['itemname'];
            //$list[$key]['dwmc']=$itemname;
            }
            
           import("Org.Util.PHPExcel");
           import("Org.Util.PHPExcel.Writer.Excel5");
           import("Org.Util.PHPExcel.IOFactory.php");
            // 创建对象
            $objPHPExcel = new \PHPExcel();  
            // 显示错误信息
            error_reporting(E_ALL);
            // 设置宽度   
             $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);  
            //$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);  
            //$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);  
            // $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(60);
            // 设置行高度   
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);    //第一行行高
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);    //第二行行高
            // 字体和样式  
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);   //字体大小
            $objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getFont()->setBold(false); //第二行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);    //第一行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);         //第一行字体大小
            // 设置垂直居中
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);      
            $objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
          //$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
            // 设置水平居中   
            $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);        
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('P')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('Q')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $objPHPExcel->getActiveSheet()->getStyle('R')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('S')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('T')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
            $objPHPExcel->getActiveSheet()->getStyle('U')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            // $objPHPExcel->getActiveSheet()->getStyle('A')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
            // 合并  
            $objPHPExcel->getActiveSheet()->mergeCells('A1:U1');
            $objPHPExcel->getActiveSheet()->mergeCells('B2:I2');
            
            $objPHPExcel->getActiveSheet()->mergeCells('J2:K2');
            $objPHPExcel->getActiveSheet()->mergeCells('J3:K3');
            $objPHPExcel->getActiveSheet()->mergeCells('J2:J3');
            $objPHPExcel->getActiveSheet()->mergeCells('K2:K3');
            
            
            $objPHPExcel->getActiveSheet()->mergeCells('L2:M2');
            $objPHPExcel->getActiveSheet()->mergeCells('L3:M3');
            $objPHPExcel->getActiveSheet()->mergeCells('L2:L3');
            $objPHPExcel->getActiveSheet()->mergeCells('M2:M3');
            
            $objPHPExcel->getActiveSheet()->mergeCells('N2:O2');
            $objPHPExcel->getActiveSheet()->mergeCells('N3:O3');
            $objPHPExcel->getActiveSheet()->mergeCells('N2:N3');
            $objPHPExcel->getActiveSheet()->mergeCells('O2:O3');
            
            
            $objPHPExcel->getActiveSheet()->mergeCells('P2:Q2');
            $objPHPExcel->getActiveSheet()->mergeCells('P3:Q3');
            $objPHPExcel->getActiveSheet()->mergeCells('P2:P3');
            $objPHPExcel->getActiveSheet()->mergeCells('Q2:Q3');
            
            $objPHPExcel->getActiveSheet()->mergeCells('R2:S2');
            $objPHPExcel->getActiveSheet()->mergeCells('R3:S3');
            $objPHPExcel->getActiveSheet()->mergeCells('R2:R3');
            $objPHPExcel->getActiveSheet()->mergeCells('S2:S3');
            
            $objPHPExcel->getActiveSheet()->mergeCells('T2:U2');
            $objPHPExcel->getActiveSheet()->mergeCells('T3:U3');
            $objPHPExcel->getActiveSheet()->mergeCells('T2:T3');
            $objPHPExcel->getActiveSheet()->mergeCells('U2:U3');
            
            
            $objPHPExcel->getActiveSheet()->mergeCells('B3:D3');
            $objPHPExcel->getActiveSheet()->mergeCells('E3:G3');
            $objPHPExcel->getActiveSheet()->mergeCells('H3:I3');
            
            $objPHPExcel->getActiveSheet()->mergeCells('A2:A4');
            
            // 表头  
            $objPHPExcel->setActiveSheetIndex(0)  
                    ->setCellValue('A1', '地矿业统计表')
                    ->setCellValue('B2', '地质项目')                
                    ->setCellValue('A2', '单位名称')  
                    ->setCellValue('J2', '地灾防治及环境评价')
                    ->setCellValue('L2', '测绘')
                    ->setCellValue('N2', '工程勘察')
                    ->setCellValue('P2', '矿业开发')
                    ->setCellValue('R2', '其它')
                    ->setCellValue('T2', '总计')
                    
                    ->setCellValue('B3', '财政类地质找矿项目')
                    ->setCellValue('E3', '商业性地质找矿项目')
                    ->setCellValue('H3', '地质技术服务项目')
                    ->setCellValue('B4', '个数')
                    ->setCellValue('C4', '产值(万)')                
                    ->setCellValue('D4', '利润(万)')
                    ->setCellValue('E4', '个数')
                    ->setCellValue('F4', '产值(万)')                
                    ->setCellValue('G4', '利润(万)')
                    ->setCellValue('H4', '产值(万)')                
                    ->setCellValue('I4', '利润(万)')
                    ->setCellValue('J4', '产值(万)')                
                    ->setCellValue('K4', '利润(万)')
                    ->setCellValue('L4', '产值(万)')                
                    ->setCellValue('M4', '利润(万)')
                    ->setCellValue('N4', '产值(万)')                
                    ->setCellValue('O4', '利润(万)')
                    ->setCellValue('P4', '产值(万)')                
                    ->setCellValue('Q4', '利润(万)')
                    ->setCellValue('R4', '产值(万)')                
                    ->setCellValue('S4', '利润(万)')
                    ->setCellValue('T4', '产值(万)')                
                    ->setCellValue('U4', '利润(万)')
                    ;
            //重构内容-文章名称录入
            for ($i = 0, $len = count($newData); $i < $len; $i++) {  
                $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 3), $newData[$i]);  
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':D' . ($i + 3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
                //左对齐
                $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                //边框设置
                //$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':D' . ($i + 3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
                // $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);  //重构行高
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':D' . ($i + 3))->applyFromArray($styleArray);
            }  
            $ii=5;//从第三行开始的
            for ($i = 0, $len = count($list); $i < $len; $i++) {
                if(count($list[$i]['dwmc'])>0){
                    //合并 注意-1，因为当前单位有内容且是第一篇
                    //录入记录
                    
                    
                    $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
            );
             $objPHPExcel->getActiveSheet()->getStyle( 'A1:U'.($ii))->applyFromArray($styleThinBlackBorderOutline);
            
                    
                    
                    
                    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($ii), $list[$i]['dwmc']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($ii), $list[$i]['gs']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($ii), $list[$i]['gs1']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($ii), $list[$i]['gs2']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($ii), $list[$i]['gs3']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($ii), $list[$i]['gs4']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($ii), $list[$i]['gs5']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($ii), $list[$i]['gs6']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($ii), $list[$i]['gs7']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('J' . ($ii), $list[$i]['gs8']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('K' . ($ii), $list[$i]['gs9']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($ii), $list[$i]['gs10']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('M' . ($ii), $list[$i]['gs11']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('N' . ($ii), $list[$i]['gs12']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('O' . ($ii), $list[$i]['gs13']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('P' . ($ii), $list[$i]['gs14']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('Q' . ($ii), $list[$i]['gs15']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('R' . ($ii), $list[$i]['gs16']);
                    $objPHPExcel->getActiveSheet(0)->setCellValue('S' . ($ii), $list[$i]['gs17']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('T' . ($ii), $list[$i]['czzj']);    
                    $objPHPExcel->getActiveSheet(0)->setCellValue('U' . ($ii), $list[$i]['llzj']);                    
                    $ii=$ii-1+count($list[$i]['dwmc']);
                    $ii++;
                }else{
                    //录入记录
                    $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($ii), $i+1);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($ii), $data[$i]['xm_name']);  
                    $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($ii), count($data[$i]['licence']));  
                    $ii++;
                }
            }  
            $objPHPExcel->getActiveSheet()->setTitle('地矿业统计表');
            // Set active sheet index to the first sheet, so Excel opens this as the first sheet    
            $objPHPExcel->setActiveSheetIndex(0);  
            // 输出  
            header('Content-Type: application/vnd.ms-excel');  
            header('Content-Disposition: attachment;filename="' . '供稿单位' . '.xls"');  
            header('Cache-Control: max-age=0');  
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
            $objWriter->save('php://output');  
            exit;
        }    
       

本文标签：

已有 1575 位网友参与，快来吐槽：

发表评论

站点统计