PHPEXCEL实例-导出EXCEL

浏览数：28 / 时间：2015年06月09日

PHPExcel 是相当强大的 MS Office Excel 文档生成类库，当需要输出比较复杂格式数据的时候，PHPExcel 是个不错的选择。

Php代码

<?php
/*
* 导出EXCEL
* 程序：刘兴
* 时间:2010-6
*/
?>
<?php
require_once("connDB.php");//引入数据库连接参数
require_once("PExcel/PHPExcel.php");
?>
<?php
$year=$_POST[year];
$jd=$_POST[jd];
set_time_limit(900);
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
$sqlxm="select count(*) as cxm from khxm where lb=1 and isyx=0";
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm);
$c1=$rowxm[‘cxm‘];
$sqlxm="select count(*) as cxm from khxm where lb=2 and isyx=0";
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm);
$c2=$rowxm[‘cxm‘];
$sqlxm="select count(*) as cxm from khxm where lb=3 and isyx=0";
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm);
$c3=$rowxm[‘cxm‘];
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("gxds");
$objPHPExcel->getProperties()->setLastModifiedBy("gxds");
$objPHPExcel->getProperties()->setTitle("gxdskhtj");
$objPHPExcel->getProperties()->setSubject("gxdskhtj");
$objPHPExcel->getProperties()->setDescription("gxdskhtj, gxds.");
$objPHPExcel->getProperties()->setKeywords("gxdskhtj");
$objPHPExcel->getProperties()->setCategory("gxtj result file");
// Add some data
$noshow = array("year", "time", "peopleid", "bm","kbm");
$objPHPExcel->setActiveSheetIndex(0);
$objActSheet = $objPHPExcel->getActiveSheet();
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 3);
$db=new mysqli("localhost","ssss","aaaaa","bbbb");
mysqli_query($db,"SET NAMES utf8");
$result=$db->query("call gxtj($year,$jd)");
$i=4;
while( $row = $result->fetch_array(MYSQLI_ASSOC))
{
$c=0;
$zt=1;
$totalfs=100;
$tfs=0;
$isstart=false;
$j=65;
$asc_cell=chr($j);
while ($key=key($row)){
$ex_title=$asc_cell."3";
$ex_cell=$asc_cell.$i;
$value=current($row);
if (in_array($key, $noshow)) {
next($row);
continue;
}
if (is_numeric($value) or is_null($value) ){
if (is_null($value)) $value=‘0‘;
if (!$isstart) {
$isstart=true;
$startasc=$asc_cell;
$endasc=$asc_cell;
}
}
if ($isstart){
$c++;
if ($zt==1){
if ($c>$c1) {
$zt++;
$ge=$startasc.‘2:‘.$endasc.‘2‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue($startasc.‘2‘,"岗位职责、效能考核");
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)
->getFont()->setBold(true);
$startasc=$asc_cell;
$endasc=$asc_cell;
$totalfs-=$tfs;
$tfs=0;
$c=1;
}
else{
$endasc=$asc_cell;
}
$tfs+=$value;
if ($tfs>50) {
$tfs=50;
}
}
if ($zt==2){
if ($c>$c2) {
$zt++;
$ge=$startasc.‘2:‘.$endasc.‘2‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue($startasc.‘2‘,"考试");
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)
->getFont()->setBold(true);
$startasc=$asc_cell;
$endasc=$asc_cell;
$totalfs-=$tfs;
$tfs=0;
$c=1;
}
else{
$endasc=$asc_cell;
}
$tfs+=$value;
if ($tfs>30) {
$tfs=30;
}
}
if ($zt==3){
if ($c=$c3) {
$zt++;
$ge=$startasc.‘2:‘.$endasc.‘2‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue($startasc.‘2‘,"科长考核");
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
$objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)
->getFont()->setBold(true);
$startasc=$asc_cell;
$endasc=$asc_cell;
$totalfs-=$tfs;
$tfs=0;
$c=0;
}
else{
$endasc=$asc_cell;
}
$tfs+=$value;
if ($tfs>20) {
$tfs=20;
}
}
}
$objActSheet->setCellValue($ex_title,$key);
$objActSheet->setCellValue($ex_cell,$value);
//设置单元格宽度
$objActSheet->getColumnDimension($asc_cell)->setWidth(16);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置填充格式及字体颜色
$objPHPExcel->getActiveSheet()->getStyle($ex_cell)
->getNumberFormat()->setFormatCode(‘#,##0.00‘);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()
->getStartColor()->setARGB(‘0DB0E50A1‘);
$objPHPExcel->getActiveSheet()->getStyle($ex_title)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//设置字体粗体
$objPHPExcel->getActiveSheet()->getStyle($ex_title)
->getFont()->setBold(true);
$j++;
if ($j>90) {
$j=65;
$asc_cell="AA" ;
}
elseif (strlen($asc_cell)>1) {
$asc_cell=substr($asc_cell,0,strlen($asc_cell)-1).chr($j);
}
else{
$asc_cell=chr($j);
}
next($row);
}
$ge=$asc_cell.‘2:‘.$asc_cell.‘3‘;
$objActSheet->mergeCells("$ge");
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objActSheet->setCellValue($asc_cell.‘2‘,"总分");
$objActSheet->setCellValue($ex_cell,$totalfs);
//设置填充格式及字体颜色
$objPHPExcel->getActiveSheet()->getStyle($ex_cell)
->getNumberFormat()->setFormatCode(‘#,##0.00‘);
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
//设置字体颜色
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)
->getFont()->setBold(true);
$i++;
}
//标题栏合并单元格，并设置居中
$objActSheet->setCellValue(‘A1‘,$year."年".$jd."季度绩效考核情况统计表");
$value=$objPHPExcel->getActiveSheet()->getCell(‘A1‘)->getValue();;
$ge=‘A1:‘.$asc_cell.‘1‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue(‘A1‘,$value);
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)
->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)
->getFont()->setSize(26);
//姓名栏合并单元格，并设置居中
$value=$objPHPExcel->getActiveSheet()->getCell(‘A3‘)->getValue();;
$ge=‘A2:A3‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue(‘A2‘,$value);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)
->getFont()->setBold(true);
//科室栏合并单元格，并设置居中
$value=$objPHPExcel->getActiveSheet()->getCell(‘B3‘)->getValue();;
$ge=‘B2:B3‘;
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue(‘B2‘,$value);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getFill()
->getStartColor()->setARGB(‘0DB0E59FF‘);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle(‘B2‘)
->getFont()->setBold(true);
//单元格边框及颜色
$objPHPExcel->getActiveSheet()->getStyle(‘A2:‘.$ex_cell)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(‘A2:‘.$ex_cell)->getBorders()->getAllBorders()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
// sheet名称
$objPHPExcel->getActiveSheet()->setTitle(‘绩效考核统计‘);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a clients web browser (Excel5)通知下载
$fn="gxtj-$year-$jd.xls";
header(‘Content-Type: application/vnd.ms-excel; charset=utf-8‘);
header("Content-Disposition: attachment;filename=$fn");
header(‘Cache-Control: max-age=0‘);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘);
$objWriter->save(‘php://output‘);
exit;
?>