用PHP处理EXCEL的利器:PHPExcel

这几天在帮单位里开发一个小程序.

既然是为单位开发的,那么一定会涉及到报表...

毕竟,公文流转类的程序其实在现实中一直缺乏意义,作为领导而言,他们永远更加信任纸质文件而并非是看得见摸不着的电子档.

所以,这次的任务主要是开发一个业务的数据管理系统.涉及到Excel文件的处理基本上成为了必然.于是上网查找,很多人说自己写函数,因为Excel2007采用的开源的文件格式,而且现在已经成为了ISO,但是单位里更多需要处理的是EXCEL2003,同时,自己编写类无疑太麻烦了.于是锁定了现有的一个强大的PHP类:PHPExcel.

这里是它的官方地址,我看到最新的版本是1.72,虽然它的名字依然叫做PHPExcel,但是它的处理文档的能力是显然超过了Excel文件的,这个版本可以读取和生成.XLS,XLSX,PDF,CSV等...官方也提供了详细的文本和说明.我列举一下普通需要使用到的就好了.

<?php

include "PHPExcel.php";

include "PHPExcel/Writer/Excel5.php"; 

//如果需要处理XLSX文件,那么 include "PHPExcel/Writer/Excel2007.php";


//创建excel
$objPHPExcel = new PHPExcel();

//如果你需要直接写入硬盘,那么使用以下方法:

//$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);//for XLSX


//EXCEL文件属性

//创建人
$objPHPExcel->getProperties()->setCreator("创建人");

//最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("最后修改人");

// 标题
$objPHPExcel->getProperties()->setTitle("标题");

//题目
$objPHPExcel->getProperties()->setSubject("题目");

//描述
$objPHPExcel->getProperties()->setDescription("描述");

//关键字
$objPHPExcel->getProperties()->setKeywords("关键字");

//分类
$objPHPExcel->getProperties()->setCategory("分类");

 

// 编辑Sheet

//设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);

//设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle('名字');

//设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue('A1','字符串');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A3', true);
$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');

//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

//分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');

//设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

// 设置默认行高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
//设置特定行高
$objPHPExcel->getActiveSheet()->getRowDimension('D')->setRowHeight(22);

//设置字体
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

//设置水平位置
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//设置垂直位置
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//设置边框
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

//设置边框颜色
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');

//设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

//填加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

//输出到浏览器
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="resume.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');

?>