PHPexcel手册
程序员文章站
2022-05-23 09:06:46
...
之前写过一篇有关PHP Excel类的教程《PHPExcel使用（导出和导入Excel文件）教程》，可是最近发现这篇文章似乎介绍的有点太简单了，所以在网上找到了《PHPExcel中文帮助手册》,在本文末尾再据此给出一个新的示例。
1.header
[php] header("Content-Type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=product.xls");
header("Pragma:no-cache");
header("Expires:0");[/php]
2.PHPExcel 相关资料
http://www.codeplex.com/PHPExcel
http://www.phpexcel.net
初始化PHPExcel
//Include class
[php]require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
/*使用PHPExcel 导出 Excel 2007文件时要注意将PHP配置文件中如下功能开启：
*
* PHP version 5.2.0 or higher
* PHP extension php_zip enabled **
* PHP extension php_xml enabled
* PHP extension php_gd2 enabled (if not compiled in)
* php_zip is only needed by PHPExcel_Reader_Excel2007,PHPExcel_Writer_Excel2007,
* PHPExcel_Reader_OOCalc. In other words, if you need PHPExcel to handle .xlsx or .ods files
* you will need the zip extension, but otherwise not.
*/
$objPHPExcel = new PHPExcel();[/php]
//Set properties 设置文件属性
[php]$objPHPExcel->getProperties()->
setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->
setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->
setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->
setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->
setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->
setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->
setCategory("Test result file");[/php]
//设置全表的默认样式
[php]$objPHPExcel = new PHPExcel;
// 设置默认的字体样式
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
// 设置表格字体的大小
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);[/php]
//批量修改单元格样式
[php]$style_array = array(
'borders' => array(
'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true
)
);
/*获取当前所有包含内容的单元格的最大范围*/
$highestColumn = $objSheet->getHighestColumn();
$highestRow = $objSheet->getHighestRow();
//applyFromArray()的第二个参数设置为false，给一定范围内的所有单元格都加上数组里的样式
$objSheet->getStyle('A1:' . $highestColumn . $highestRow)->applyFromArray($style_array,false);[/php]
//Add some data 添加数据
[php]$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置
$objPHPExcel->getActiveSheet()->setCellValue('A2', true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', false);
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
//数据的值可以使用Excel的函数，比如：
$objSheet->setCellValue('F3','=SUM(F4:F7)');
$objSheet->setCellValue('F13','=SUM(F3,G8,F9,G13)');[/php]
//循环
[php]for($i = 1;$i $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');
}[/php]
//日期格式化
[php]$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->
setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);[/php]
//Add comment 添加注释
[php]$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.');[/php]
//Add rich-text string 添加文字，可设置特定一段内容的样式，比如下面的例子中'payable within thirty days after the end of the month'这句话将会变成深绿色。
[php]$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
$objRichText->createText('This invoice is ');
//只有使用createTextRun()方法才能添加样式。
$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.');[/php]
//Merge cells 合并分离单元格
[php]$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');[/php]
//Protect cells 保护单元格
[php]$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
//Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');[/php]
//Set cell number formats 数字格式化
[php]$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()
->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
//保留两位小数
$PHPExcel -> getStyle($colString."6:".$colString.$row)
-> getNumberFormat() -> setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle($str)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
//导出的数据位日期格式，但NumberFormat.php给出的的日期格式没有（前后加空格）
$objPHPExcel->getActiveSheet()->getStyle($str)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->setCellValue($str," ".$arr[$j]." ");
//千分位科学计数法
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('[Blue][>=3000]$#,##0;[Red][
//Set column widths 设置列宽度
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);[/php]
//Set fonts 设置字体
[php]$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);[/php]
//Set alignments 设置对齐
[php]$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);[/php]
//Set column borders 设置列边框
[php]$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()
->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()
->setBorderStyle(PHPExcel_Style_Border::BORDER_DOUBLE);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);[/php]
//Set border colors 设置边框颜色
[php]$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');[/php]
//Set fills 设置填充
[php]/*Fill Type 和 StartColor需要同时写*/
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFill()->getStartColor()->setARGB('FF808080');
/*
*或者
*$objPHPExcel->getActiveSheet()->getStyle('A1')
* ->getFill()->getStartColor()->setRGB('FF8080');
*/
//也可以使用数组的形式填充
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FF8080')
)
)
);[/php]
//Add a hyperlink to the sheet 添加链接
[php]$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);[/php]
//Add a drawing to the worksheet 添加图片
[php]$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.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());[/php]
//Play around with inserting and removing rows and columns 插入、删除行或者列
[php]$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);[/php]
//Add conditional formatting 添加格式
[php]$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);[/php]
//Set autofilter 自动过滤
[php]$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');[/php]
//Hide "Phone" and "fax" column 隐藏列
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);[/php]
//Set document security 设置文档安全
[php]$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");[/php]
//Set sheet security 设置工作表安全
[php]$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);[/php]
//Calculated data 计算
[php]echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()
->getCell('B14')->getCalculatedValue() . "\r\n";[/php]
//Set outline levels
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);[/php]
//Freeze panes，冻结单元格
[php]$objPHPExcel->getActiveSheet()->freezePane('C6');
//表示冻结第6行（不包括第6行）以上，C列往左（不包括C列）的所有单元格。[/php]
//Rows to repeat at top,下拉重复
[php]$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);[/php]
//Set data validation 验证输入值
[php]$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Number is not allowed!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation);[/php]
//Create a new worksheet, after the default sheet 创建新的工作标签
[php]$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);[/php]
//Set header and footer. When no different headers for odd/even are used, odd header is assumed.页眉页脚
[php]$objPHPExcel->getActiveSheet()->getHeaderFooter()
->setOddHeader('&C&HPlease treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()
->setOddFooter('&L&B'.$objPHPExcel->getProperties()->getTitle().'&RPage &P of &N');[/php]
//Set page orientation and size 方向大小
[php]$objPHPExcel->getActiveSheet()->getPageSetup()
->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()
->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);[/php]
//Rename sheet 重命名工作标签
[php]$objPHPExcel->getActiveSheet()->setTitle('Simple');
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);[/php]
//Save Excel 2007 file 保存
[php]$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));[/php]
//Save Excel 5 file 保存
[php]require_once('Classes/PHPExcel/Writer/Excel5.php');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));[/php]
//1.6.2新版保存
[php]require_once('Classes/PHPExcel/IOFactory.php');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xls', __FILE__));[/php]
读excel
[php]//Include class
require_once('Classes/PHPExcel/Reader/Excel2007.php');
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("05featuredemo.xlsx");[/php]
读写csv
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/CSV.php');
require_once('Classes/PHPExcel/Reader/CSV.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');[/php]
//Write to CSV format 写
[php]$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.csv', __FILE__));[/php]
//Read from CSV format 读
[php]$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));[/php]
//Write to Excel2007 format
[php]$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));[/php]
写html
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/HTML.php');[/php]
//Write to HTML format
[php]$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.htm', __FILE__));[/php]
写pdf
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/IOFactory.php');
//Write to PDF format
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.pdf', __FILE__));
//Echo memory peak usage
echo date('H:i:s')." Peak memory usage: ".(memory_get_peak_usage(true) / 1024 / 1024)." MB\r\n";
//=============================中文手册部分结束====================================//[/php]
1.header
[php] header("Content-Type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=product.xls");
header("Pragma:no-cache");
header("Expires:0");[/php]
2.PHPExcel 相关资料
http://www.codeplex.com/PHPExcel
http://www.phpexcel.net
初始化PHPExcel
//Include class
[php]require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
/*使用PHPExcel 导出 Excel 2007文件时要注意将PHP配置文件中如下功能开启：
*
* PHP version 5.2.0 or higher
* PHP extension php_zip enabled **
* PHP extension php_xml enabled
* PHP extension php_gd2 enabled (if not compiled in)
* php_zip is only needed by PHPExcel_Reader_Excel2007,PHPExcel_Writer_Excel2007,
* PHPExcel_Reader_OOCalc. In other words, if you need PHPExcel to handle .xlsx or .ods files
* you will need the zip extension, but otherwise not.
*/
$objPHPExcel = new PHPExcel();[/php]
//Set properties 设置文件属性
[php]$objPHPExcel->getProperties()->
setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->
setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->
setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->
setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->
setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->
setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->
setCategory("Test result file");[/php]
//设置全表的默认样式
[php]$objPHPExcel = new PHPExcel;
// 设置默认的字体样式
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
// 设置表格字体的大小
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);[/php]
//批量修改单元格样式
[php]$style_array = array(
'borders' => array(
'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true
)
);
/*获取当前所有包含内容的单元格的最大范围*/
$highestColumn = $objSheet->getHighestColumn();
$highestRow = $objSheet->getHighestRow();
//applyFromArray()的第二个参数设置为false，给一定范围内的所有单元格都加上数组里的样式
$objSheet->getStyle('A1:' . $highestColumn . $highestRow)->applyFromArray($style_array,false);[/php]
//Add some data 添加数据
[php]$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置
$objPHPExcel->getActiveSheet()->setCellValue('A2', true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', false);
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
//数据的值可以使用Excel的函数，比如：
$objSheet->setCellValue('F3','=SUM(F4:F7)');
$objSheet->setCellValue('F13','=SUM(F3,G8,F9,G13)');[/php]
//循环
[php]for($i = 1;$i $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');
}[/php]
//日期格式化
[php]$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->
setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);[/php]
//Add comment 添加注释
[php]$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.');[/php]
//Add rich-text string 添加文字，可设置特定一段内容的样式，比如下面的例子中'payable within thirty days after the end of the month'这句话将会变成深绿色。
[php]$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
$objRichText->createText('This invoice is ');
//只有使用createTextRun()方法才能添加样式。
$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.');[/php]
//Merge cells 合并分离单元格
[php]$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');[/php]
//Protect cells 保护单元格
[php]$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
//Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');[/php]
//Set cell number formats 数字格式化
[php]$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()
->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
//保留两位小数
$PHPExcel -> getStyle($colString."6:".$colString.$row)
-> getNumberFormat() -> setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle($str)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
//导出的数据位日期格式，但NumberFormat.php给出的的日期格式没有（前后加空格）
$objPHPExcel->getActiveSheet()->getStyle($str)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->setCellValue($str," ".$arr[$j]." ");
//千分位科学计数法
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('[Blue][>=3000]$#,##0;[Red][
//Set column widths 设置列宽度
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);[/php]
//Set fonts 设置字体
[php]$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);[/php]
//Set alignments 设置对齐
[php]$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);[/php]
//Set column borders 设置列边框
[php]$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()
->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()
->setBorderStyle(PHPExcel_Style_Border::BORDER_DOUBLE);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);[/php]
//Set border colors 设置边框颜色
[php]$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');[/php]
//Set fills 设置填充
[php]/*Fill Type 和 StartColor需要同时写*/
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFill()->getStartColor()->setARGB('FF808080');
/*
*或者
*$objPHPExcel->getActiveSheet()->getStyle('A1')
* ->getFill()->getStartColor()->setRGB('FF8080');
*/
//也可以使用数组的形式填充
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FF8080')
)
)
);[/php]
//Add a hyperlink to the sheet 添加链接
[php]$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);[/php]
//Add a drawing to the worksheet 添加图片
[php]$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.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());[/php]
//Play around with inserting and removing rows and columns 插入、删除行或者列
[php]$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);[/php]
//Add conditional formatting 添加格式
[php]$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);[/php]
//Set autofilter 自动过滤
[php]$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');[/php]
//Hide "Phone" and "fax" column 隐藏列
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);[/php]
//Set document security 设置文档安全
[php]$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");[/php]
//Set sheet security 设置工作表安全
[php]$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);[/php]
//Calculated data 计算
[php]echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()
->getCell('B14')->getCalculatedValue() . "\r\n";[/php]
//Set outline levels
[php]$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);[/php]
//Freeze panes，冻结单元格
[php]$objPHPExcel->getActiveSheet()->freezePane('C6');
//表示冻结第6行（不包括第6行）以上，C列往左（不包括C列）的所有单元格。[/php]
//Rows to repeat at top,下拉重复
[php]$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);[/php]
//Set data validation 验证输入值
[php]$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Number is not allowed!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation);[/php]
//Create a new worksheet, after the default sheet 创建新的工作标签
[php]$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);[/php]
//Set header and footer. When no different headers for odd/even are used, odd header is assumed.页眉页脚
[php]$objPHPExcel->getActiveSheet()->getHeaderFooter()
->setOddHeader('&C&HPlease treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()
->setOddFooter('&L&B'.$objPHPExcel->getProperties()->getTitle().'&RPage &P of &N');[/php]
//Set page orientation and size 方向大小
[php]$objPHPExcel->getActiveSheet()->getPageSetup()
->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()
->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);[/php]
//Rename sheet 重命名工作标签
[php]$objPHPExcel->getActiveSheet()->setTitle('Simple');
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);[/php]
//Save Excel 2007 file 保存
[php]$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));[/php]
//Save Excel 5 file 保存
[php]require_once('Classes/PHPExcel/Writer/Excel5.php');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));[/php]
//1.6.2新版保存
[php]require_once('Classes/PHPExcel/IOFactory.php');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xls', __FILE__));[/php]
读excel
[php]//Include class
require_once('Classes/PHPExcel/Reader/Excel2007.php');
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("05featuredemo.xlsx");[/php]
读写csv
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/CSV.php');
require_once('Classes/PHPExcel/Reader/CSV.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');[/php]
//Write to CSV format 写
[php]$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.csv', __FILE__));[/php]
//Read from CSV format 读
[php]$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));[/php]
//Write to Excel2007 format
[php]$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));[/php]
写html
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/HTML.php');[/php]
//Write to HTML format
[php]$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.htm', __FILE__));[/php]
写pdf
[php]require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/IOFactory.php');
//Write to PDF format
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.pdf', __FILE__));
//Echo memory peak usage
echo date('H:i:s')." Peak memory usage: ".(memory_get_peak_usage(true) / 1024 / 1024)." MB\r\n";
//=============================中文手册部分结束====================================//[/php]
示例：
我们将使用PHPExcel创建一个如下图所示的Excel文件（销售报告），使用Calibri字体，默认大小 8pt。单位则使用Euro 欧元。
[caption id="attachment_916" align="aligncenter" width="434"] PHP Excel[/caption]
全部代码如下：
[php]
// include PHPExcel
require('PHPExcel.php');
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
require('PHPExcel/IOFactory.php'); //phpexcel工厂类
require('PHPExcel/Writer/Excel2007.php');
// create new PHPExcel object
$objPHPExcel = new PHPExcel;
// set default font
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
// set default font size
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
// create the writer
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
//也可以使用 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
/*输出PDF格式*/
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "PDF");
/**
* Define currency and number format.
*/
// currency format, € with getActiveSheet();
// rename the sheet
$objSheet->setTitle('My sales report');
// let's bold and size the header font and write the header
// as you can see, we can specify a range of cells, like here: cells from A1 to A4
$objSheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(12);
// write header
$objSheet->getCell('A1')->setValue('Product');
$objSheet->getCell('B1')->setValue('Quanity');
$objSheet->getCell('C1')->setValue('Price');
$objSheet->getCell('D1')->setValue('Total Price');
// we could get this data from database, but for simplicty, let's just write it
$objSheet->getCell('A2')->setValue('Motherboard');
$objSheet->getCell('B2')->setValue(10);
$objSheet->getCell('C2')->setValue(5);
$objSheet->getCell('D2')->setValue('=B2*C2');
$objSheet->getCell('A3')->setValue('Processor');
$objSheet->getCell('B3')->setValue(6);
$objSheet->getCell('C3')->setValue(3);
$objSheet->getCell('D3')->setValue('=B3*C3');
$objSheet->getCell('A4')->setValue('Memory');
$objSheet->getCell('B4')->setValue(10);
$objSheet->getCell('C4')->setValue(2.5);
$objSheet->getCell('D4')->setValue('=B4*C4');
$objSheet->getCell('A5')->setValue('TOTAL');
$objSheet->getCell('B5')->setValue('=SUM(B2:B4)');
$objSheet->getCell('C5')->setValue('-');
$objSheet->getCell('D5')->setValue('=SUM(D2:D4)');
// bold and resize the font of the last row
$objSheet->getStyle('A5:D5')->getFont()->setBold(true)->setSize(12);
// set number and currency format to columns
$objSheet->getStyle('B2:B5')->getNumberFormat()->setFormatCode($numberFormat);
$objSheet->getStyle('C2:D5')->getNumberFormat()->setFormatCode($currencyFormat);
// create some borders
// first, create the whole grid around the table
$objSheet->getStyle('A1:D5')->getBorders()->
getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
// create medium border around the table
$objSheet->getStyle('A1:D5')->getBorders()->
getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
// create a double border above total line
$objSheet->getStyle('A5:D5')->getBorders()->
getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_DOUBLE);
// create a medium border on the header line
$objSheet->getStyle('A1:D1')->getBorders()->
getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
// autosize the columns
$objSheet->getColumnDimension('A')->setAutoSize(true);
$objSheet->getColumnDimension('B')->setAutoSize(true);
$objSheet->getColumnDimension('C')->setAutoSize(true);
$objSheet->getColumnDimension('D')->setAutoSize(true);
// 写入文件中
$objWriter->save('test.xlsx');
//输出PDF格式文件
//$objWriter->save('test.pdf');
[/php]
下一篇： PHPCrawl PHP爬虫类