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');
1133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
