PHP导出Excel表格及设置表格样式

首先引入excel类，用的TP框架

use thinkPHPExcel;

创建对象

 $objPHPExcel = new PHPExcel();

#显示错误信息

error_reporting(E_ALL);

设置标题之类属性

$objPHPExcel->getProperties()->setCreator("这砖有点沉")
   ->setLastModifiedBy("这砖有点沉")
   ->setTitle("数据EXCEL导出")
   ->setSubject("数据EXCEL导出")
   ->setDescription("导出数据")
   ->setKeywords("excel")
   ->setCategory("result file");

设置表头(自行加粗等)

$objPHPExcel->getActiveSheet()->setCellValue('A1', '时间');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '进线人数');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '注册人数');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '注册比');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '签约人数');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '成单比');

#表头合并并加粗

$objPHPExcel->getActiveSheet()->mergeCells("A1:F1");//合并
$objPHPExcel->getActiveSheet()->setCellValue("内容");//插入数据
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//加粗

#合并示例：

$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
$objPHPExcel->getActiveSheet()->mergeCells('A6:F6');
$objPHPExcel->getActiveSheet()->mergeCells('A11:B11');

#字体与样式

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);//字体大小
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(false);//是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//是是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);//第一行字体大小

#设置单元格行高

$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//设置默认行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);//设置第一行行高

#设置单元格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);//设置A列宽度

#设置垂直居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

#设置水平居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

#设置左对齐

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

插入数据(自行循环追加)

$objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', $data['a'])
                ->setCellValue('B1', $data['b'])
                ->setCellValue('C1', $data['c'])
                ->setCellValue('D1', $data['d']);

设置sheet页签名称

$objPHPExcel->getActiveSheet()->setTitle('数据统计-1');

将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开

$objPHPExcel->setActiveSheetIndex(0);

设置输出

$filename = '数据统计-' . $t;
//清空缓冲区并关闭输出缓冲
ob_end_clean();

header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-excel");
header('Content-Disposition:attachment;filename="'.$filename.'"');
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Pragma: no-cache");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

OVER~