php带出excel表格,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;

}