phpexcel单表格公共导出

通用导出方法，传入对应参数

 /**
     * 单表格公共导出excel
	 * $title 表格标题
	 * $rule 导出规则(array('列头1','字段',宽度,对齐方式,对齐参数生效最后一行),array('列头2','字段',宽度),array('列头3','字段',宽度)),数组元素个数就是列数
	 * $list 要导出的数据，二维数组
     * 做了部分不完全扩展，如有疑问联系关松鹤
     **/
    public static function _export_common_excel($title,$rule,$list){
        $objPHPExcel = new Phpexcel();
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
		
		//获取列函数
		$columncount = count($rule);
		$loop = self::loop($columncount);
		$lastcolumn = $loop[($columncount-1)];
		
		foreach($rule as $ki => $vo){
			// set width
			$objPHPExcel->getActiveSheet()->getColumnDimension($loop[$ki])->setWidth($vo[2]);
			
		}
		
		// 字体和样式
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2:'.$lastcolumn.'2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
//echo 44;exit;
        // 设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastcolumn.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastcolumn.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:'.$lastcolumn.'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2:'.$lastcolumn.'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		
		//  合并
        $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$lastcolumn.'1');
        // $objPHPExcel->getActiveSheet()->mergeCells('B2:D2');
		
		
		$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1',$title);
		foreach($rule as $ki => $vo){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($loop[$ki].'2', $vo[0]);
            
		}
		
		foreach ($list as $k => $v) {

            $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':'.$lastcolumn.($k+3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($k+3).':'.$lastcolumn.($k+3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($lastcolumn.($k+3))->getAlignment()->setShrinkToFit(true);
			foreach($rule as $key => $val){
				$objPHPExcel->getActiveSheet(0)->setCellValueExplicit($loop[$key] . ($k + 3), $v[$val[1]], PHPExcel_Cell_DataType::TYPE_STRING);
                
            }
        }
        foreach($rule as $ki => $vo){
            if(isset($vo[3]) && ($vo[3] == 'left')){
                $end = $vo[4]?$vo[4]:(count($list)+3);
                $objPHPExcel->getActiveSheet()->getStyle($loop[$ki].(0+3).':'.$loop[$ki].($end+0))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            }
            
		}
		
		
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle($title);
        $objPHPExcel->setActiveSheetIndex(0);


        // 输出
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        $title = iconv("utf-8", "gb2312", $title);
        header("Content-Disposition:attachment;filename=".$title.".xlsx");
        header("Content-Transfer-Encoding:binary");
        $xlsWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

        $xlsWriter->save('php://output');
    }
	
	/**
	 * 生成excel头数组
	 */
	public function loop($maxnum){
		$loop = 0;
		$charnum = 65; 
		$loop_arr = array();
		for(; $loop < $maxnum; $loop++){
			$quotient = intval($loop / 26);
			$remainder = $loop % 26;

			$f = $quotient>0? chr($charnum+$quotient-1) : '';
			$s = $remainder>=0? chr($charnum+$remainder) : '';
			$loop_arr[$loop] = $f . $s;
		}
		
		return $loop_arr;
    }

 

转载于:https://my.oschina.net/AlingyunA/blog/1829360