PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。 可以使用它来读取、写入不同格式的电子表格  

PHPExcel简介

PHPExcel是一个生成excel的插件。插件封装了excel的读取、修改和生成。包括表格的样式、图表样式。也是php语言下比较成熟的excel操作插件，是php下生产小型报表的首选。

PHPExcel性能和其他语言比较

PHPExcel在生成一万行二百列以上的数据（区分服务器配置）就会变得非常慢。开始以为是php性能问题，后来尝试在python、java上生成excel，也同样出现这种问题。实际上是压缩成excel时消耗较大。
所以说，如果想在项目中生产报表（小数据量）或者使用到excel。phpexcel是可以胜任的。数据量不大的时候，生成是十分迅速。
PHPExcel常用属性使用实例如下：

设置header

$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

header("Pragma: no-cache");
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=down.xlsx');
header("Content-Transfer-Encoding:binary");

$objWriter->save('php://output');

 PHPExcel常用属性使用实例 设置文件属性信息

$objPHPExcel = new PHPExcel();
 
// 设置文件属性详细信息
    $objPHPExcel->getProperties()
            ->setTitle("标题")
            ->setSubject("主题")
            ->setKeywords("标记")
            ->setCategory("类别")
            ->setDescription("备注")
            ->setCreator("作者")
            ->setLastModifiedBy("最后一次保存者")
            ->setCompany('公司')
            ->setManager('管理者');

 PHPExcel创建工作表sheet、设置名称 属性使用实例

// 创建sheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($sheet_index);

//获取活动工作表
$objPHPExcel->getActiveSheet();
//获取活动工资表的索引号
$activeSheet_index=$objPHPExcel->getActiveSheetIndex();
//设置工作表名称
$objPHPExcel->getActiveSheet()->setTitle("工作表名称");

PHPExcel添加数据设置样式使用实例

//Add some data 添加数据
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '世界');

//日期格式化
$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
 
//Add comment 添加注释
$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
 
//Add rich-text string 添加文字 可设置样式
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
$objRichText->createText('This invoice is ');
$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
$objRichText->createText(', unless specified otherwise on the invoice.');

PHPExcel文档安全、工作表安全属性使用

//Set document security 设置文档安全 不能添加删除工作表
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");
 
//Set sheet security 设置工作表安全 不能编辑单元格内容
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

//Protect cells 保护单元格
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'mypwd');


// 标明不需要锁定保护的单元格
$objPHPExcel->getActiveSheet()->getStyle('A2：B2')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

// 给工作表设置锁定状态生效
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); 

PHPExcel合并 分离 单元格 属性使用实例

$objPHPExcel = new PHPExcel();
 
//Merge cells 合并分离单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

 PHPExcel 数字格式化

//Set cell number formats 数字格式化
$objPHPExcel->getActiveSheet()->getStyle('F4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('F4'), 'F5:F13' );

PHPExcel设置字体宽度 设置行高

$objPHPExcel = new PHPExcel();
 
//Set column widths 设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(100); //设置行高
 
//Set fonts 设置字体
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

PHPExcel设置对齐方式、换行

//设置对齐 Set alignments 
$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('A3')->getAlignment()->setWrapText(true);//单元格内换行 要换行的文字必须是双引号插入换行符"\n"

 PHPExcel 设置列边框

$objPHPExcel = new PHPExcel();
  
//设置列边框
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

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

PHPExcel 设置单元格填充色

$objPHPExcel = new PHPExcel(); 

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

 PHPExcel 添加链接

$objPHPExcel = new PHPExcel();

//Add a hyperlink to the sheet 添加链接
$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

PHPExcel   添加图片

$objPHPExcel = new PHPExcel();
 
//添加图片 Add a drawing to the worksheet 
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/myimg.jpg');
$objDrawing->setHeight(36);
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

 PHPExcel  插入或删除列和行

$objPHPExcel = new PHPExcel(); 
 
//插入行
$pBefore = 5; //插入（删除）的起始行
$pNumRows = 3; //插入（删除）的行数
$objPHPExcel->getActiveSheet()->insertNewRowBefore($pBefore, $pNumRows); 
$objPHPExcel->getActiveSheet()->removeRow($pBefore , $pNumRows);

$pBefore = 'F'; //插入（删除）的起始列（A、B、C、.....）
$pNumCols = 5;  //插入（删除）的列数
$objPHPExcel->getActiveSheet()->insertNewColumnBefore($pBefore, $pNumCols);
$objPHPExcel->getActiveSheet()->removeColumn($pBefore, $pNumCols);

PHPExcel冻结、锁定表头（行、列）

$objPHPExcel = new PHPExcel(); 
 
//需要锁定的行、列
$C_R='A2'; //将冻结单元格A2上方的行（即第1行）
$C_R='B1'; //将冻结单元格B1左侧的列（即A列）
$C_R='C3'; //将冻结单元格C3上方所有行和左侧的所有列（即第1行、第2行、A列、B列被锁定）
$objPHPExcel->getActiveSheet()->freezePane($C_R);

PHPExcel隐藏列、添加条件格式、自动过滤、计算、设置大纲 

$objPHPExcel = new PHPExcel();

//添加条件格式 Add conditional formatting 
$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$objConditional1->setCondition('0');
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->getFont()->setBold(true);
 
//Set autofilter 自动过滤
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');
 
//Hide "Phone" and "fax" column 隐藏列
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
 
 
//Calculated data 计算
echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . "\r\n";
 
//设置大纲级别 Set outline levels
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);

PHPExcel 列数超过26列可动态设置对应列

/*
* 使用phpexcel导出excel文件的时候，列数超过26列的问题(超出A---Z范围)
*/

$objPHPExcel = new PHPExcel();

//获取当前Sheet的总列数
$highestColumm = $objPHPExcel->getActiveSheet()->getHighestColumn();

//直接引用 PHPExcel_Cell 通过列的个数自动转化为对应的A列或BF列
PHPExcel_Cell::stringFromColumnIndex($column_key);

//获取B列字符串中的列索引
PHPExcel_Cell::columnIndexFromString('B');