1. 云栖社区>
  2. PHP教程>
  3. 正文

PHPEXCEL实例-导出EXCEL

作者:用户 来源:互联网 时间:2017-12-01 20:58:14

excel导出实例phpexcel

PHPEXCEL实例-导出EXCEL - 摘要: 本文讲的是PHPEXCEL实例-导出EXCEL,   PHPExcel 是相当强大的 MS Office Excel 文档生成类库,当需要输出比较复杂格式数据的时候,PHPExcel 是个不错的选择。         声明:本文为刘兴(http://deepfuture.iteye.co


 


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


 


 


 


 


声明:本文为刘兴(http://deepfuture.iteye.com/)原创,如转载请注明来源


 


<?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;
?>

 

以上是云栖社区小编为您精心准备的的内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索excel , 导出 , 实例 phpexcel ,以便于您获取更多的相关知识。