PHPExcel
生成excel表格
目录
excel文件内表格的生成
<?php
// 引入配置文件
require_once('./../inc/init.inc.php');
include('./../lib/PHPExcel/PHPExcel.php');
include('./../lib/PHPExcel/PHPExcel/Writer/Excel2007.php');
// 命名要生成的excel文件
$filename = 'XXXXXXXXXX.xlsx'
########### // 初始化设置 start ######################################
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objExcel = $objPHPExcel->getActiveSheet();
// 设置字体
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
// 设置水平居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置垂直居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
############ // 初始化设置 end ########################################
############ 设置内容填充 start ##################################
# 表格头
// 定义第一列的列宽
$objExcel->getColumnDimension('A')->setWidth(18);
// 合并单元格
$objExcel->mergeCells('B2:L2');
// 设置单元格内容
$objExcel->setCellValue('B2', 'TOTAL');
// 字体加粗
$objExcel->getStyle('B2')->getFont()->setBold(true);
// 字体大小
$objExcel->getStyle('B2')->getFont()->setSize(12);
// 设置行高
$objExcel->getRowDimension('1')->setRowHeight(26);
// 设置颜色填充
$objPHPExcel->getActiveSheet()->getStyle('A2:V2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A2:V2')->getFill()->getStartColor()->setARGB('FFCCCCCC');
// 设置文本旋转
$objExcel->getStyle('A'.$secondLine)->getAlignment()->setTextRotation(90);
$objExcel->getStyle('A'.$secondLine)->getAlignment()->setWrapText(true);
// 边框线加粗
$styleThinBlackBorderInnerline = array(
'borders' => array(
'allborders' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN
),
),
);
$styleThinBlackBorderOutline = array(
'borders' => array(
'outline' => array(
'style' => \PHPExcel_Style_Border::BORDER_THICK
),
),
);
$objPHPExcel->getActiveSheet()->getStyle( 'A2:V'.($startLine+$num-1))->applyFromArray($styleThinBlackBorderInnerline);
$objPHPExcel->getActiveSheet()->getStyle( 'A2:V'.($startLine+$num-1))->applyFromArray($styleThinBlackBorderOutline);
// 设置工作表标题
$objPHPExcel->getActiveSheet()->setTitle('RESUME');
############ 设置内容填充 end ###################################
注意:getStyle
的值可以是单个单元格 也可以是从其实单元格到终止单元格(这样子的表示方法意味着这中间的全部单元格的样式统一)
excel文件内图表的生成(我的是柱状图)
<?php
/*
* $num 多少列
*
*/
// X 轴刻度
$xLabels = array(
new \PHPExcel_Chart_DataSeriesValues('String','CLIENT!$A$4:$A$'.($startLine+$num-1),null,$num),
);
$data = array();
for($i=0;$i<$last;$i++) {
// Y轴刻度
$labels[$i] = new \PHPExcel_Chart_DataSeriesValues('String','RESUME!$B$'.($line+$i),null,1); // tab
// 获取数据
$data[$i] = new \PHPExcel_Chart_DataSeriesValues('Number', 'RESUME!$C$'.($line+$i).':$N$'.($line + $i), null, 12);
}
// 构建图表框架
$series = new \PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_BARCHART,
PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,
range(0, count($labels)-1),
$labels,
$xLabels,
$data
);
// 设置绘图方向
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);// 垂直
// 在框架中设置绘图区域
$areas = new \PHPExcel_Chart_PlotArea(NULL,array($series));
// 设置图表图例走向
$legend = new \PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,NULL,false);// 向右延申
// 生成图表
$chart = new \PHPExcel_Chart(
$name,
null,
$legend,
$areas,
true,
false,
null,
null
);
// 图表定位
$chart->setTopLeftPosition('A'.($fourth+$last+2));
$chart->setBottomRightPosition('O'.($fourth+$last+22));
$chart->setBottomRightCell('P'.($fourth+$last+22));
// 将图表插入excel表格中
$objExcel->addChart($chart);
输出图表
<?php
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(true);
$objWriter->setOffice2003Compatibility(true);
header("Content-disposition: attachment; filename=".$filename."");
header("Content-Transfer-Encoding: application/vnd.ms-excel\n");
// header("Content-Transfer-Encoding: binary");
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Type: application/force-download");
// header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Pragma: no-cache");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
// $objWriter->save($filename);
$objWriter->save('php://output');