php导出excel操作,PHPExcel学习-利用插件PHPExcel操作和导出excel文件

/**

* 利用插件PHPExcel操作和导出excel文件

*/

// 引入phpexcel核心类文件

require_once dirname(__FILE__) . '/classes/PHPExcel.php';

// 实例化phpexcel类

$objPHPExcel = new PHPExcel();

// 操作第一个工作表

$objPHPExcel->setActiveSheetIndex(0);

// 设置sheet名

$objPHPExcel->getActiveSheet()->setTitle('工作表1');

// 设置表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

// 列名表头文字加粗

$objPHPExcel->getActiveSheet()->getStyle('1:1')->getFont()->setBold(true);

// 列表头文字水平居中

$objPHPExcel->getActiveSheet()

->getStyle('1:1')

->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//设置字体大小

$objPHPExcel->getActiveSheet()

->getStyle('1:1')

->getFont()->setSize(16);

// 设置行高

$objPHPExcel->getActiveSheet()

->getRowDimension(1)

->setRowHeight(30);

// 给列名赋值

$objPHPExcel->getActiveSheet()->setCellValue('A1', '姓名');

$objPHPExcel->getActiveSheet()->setCellValue('B1', '出生日期');

$objPHPExcel->getActiveSheet()->setCellValue('C1', '成绩');

// 设置数据起始行

$row_num = 2;

//创建一个3行3列的数组

$dataarr=array(array('张三','1991-01-01',84),array('李四','1991-03-05',100),array('王五','1991-7-12',79));

// 设置所有垂直居中和水平居中getStyle(2:5),可以发现第一行并没有设置垂直居中

$objPHPExcel->getActiveSheet()

->getStyle($row_num.':'.($row_num+count($dataarr,0)))

->getAlignment()

->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()

->getStyle($row_num.':'.($row_num+count($dataarr,0)))

->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// 向单元格插入数据

foreach($dataarr as $key=>$rowvalue){

foreach($rowvalue as $key=>$value){

$objPHPExcel->getActiveSheet()->setCellValue(chr($key+65) . $row_num, $value);

}

$row_num++;

}

$outputFileName = 'register_' . time() . '.xls';

header("Content-Type: application/force-download");//请求该页面就会出现下载保存窗口。

//header("Content-Type: application/octet-stream");//二进制流，不判断文件类型

//header("Content-Type: application/download");//提示用户将当前文件保存到本地

header('Content-Disposition:attachment;filename="' . $outputFileName . '"');//作为附件下载

header("Content-Transfer-Encoding: binary");//原始数据传输

//header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

//header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

//header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

//header("Pragma: no-cache");

$xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

$xlsWriter->save("php://output");