[PHP知识库]PHP Excel导出功能封装（基于PHPExcel类）

1.封装方法

<?php 
namespace app\service;

class Excel 
{
	/** Excel导出
	 * @param $list_arr 导出的数据
	 * @param $title_arr 标题（一维数组）
	 * @param $width_arr 标题的宽度（一维数组，和$title_arr顺序对应）
	 * @param $field_arr 标题对应字段名（一维数组，和$title_arr顺序对应）
	 * @param $excel_title 导出标题
     * @param $excel_total 最后合计
     * @param $count_all_arr 每一竖行统计
	 */
	function set_excel($list_arr,$title_arr,$width_arr=array(),$field_arr,$excel_title,$excel_total="",$count_all_arr=''){
		if(empty($list_arr) || empty($title_arr)){
			die('参数错误');
		}
		$num = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
			'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
			'BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ'
		);
		require_once Env::get('ROOT_PATH')."vendor/PHPExcel/PHPExcel.php";
		require_once Env::get('ROOT_PATH')."vendor/PHPExcel/PHPExcel/Worksheet/Drawing.php";
		require_once Env::get('ROOT_PATH')."vendor/PHPExcel/PHPExcel/Writer/Excel2007.php";
		$objPHPExcel = new \PHPExcel();
		$objActSheet = $objPHPExcel->getActiveSheet();
		for($i=0;$i<count($title_arr);$i++){
			$objPHPExcel->setActiveSheetIndex(0)->getStyle($num[$i])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objPHPExcel->getActiveSheet()->getColumnDimension($num[$i])->setWidth($width_arr[$i]?$width_arr[$i]:30);
			$objActSheet->setCellValue($num[$i].'1', $title_arr[$i]);
		}
		$objPHPExcel->getActiveSheet()->getStyle('A1:'.$num[count($title_arr)-1].'1')->applyFromArray(array('font' => array('bold' => true)));
		foreach ($list_arr as $k=>$v){
			$k += 2;
			for($j=0;$j<count($title_arr);$j++){
				$objActSheet->setCellValue($num[$j] . $k,  $v[$field_arr[$j]]);
			}
		}
		if($count_all_arr){			
			for($l=0;$l<count($count_all_arr);$l++){
				$number = count($list_arr)+2;
				$objActSheet->setCellValue($num[$l] . $number,  $count_all_arr[$l]);
			}
		}
		if($excel_total){
            $objActSheet->setCellValue('A'.(count($list_arr)+2),  "合计");
		    $cell = "B".(count($list_arr)+2).":D".(count($list_arr)+2);
            $objActSheet->setCellValue('B'.(count($list_arr)+2),  $excel_total)->mergeCells($cell);
        }

		$date = date("Y-m-d H:i:s", time());
		$fileName = iconv("utf-8", "gb2312", $excel_title . "_{$date}.xls");
		$objPHPExcel->setActiveSheetIndex(0);
		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'); //文件通过浏览器下载
	}
	
	
}

2.调用方法

<?php

function excel(){
    $title_arr = ['订单编号','餐厅名称','分账类型','分账用户','发生金额','分账金额','当前结余','状态','交易时间','分账时间'];
	$width_arr = ['25','25','15','15','10','10','10','10','20','20'];
	$field_arr = ['order_id','restaurant_name','fz_type_name','username','order_money','fz_money','current_balance','state_name','created_time','use_time'];
	$excel = new Excel();
	$excel->set_excel($list, $title_arr, $width_arr, $field_arr, '分账记录','');

}