强大的Excel 文档生成类库PHPExcel

PHPExcel是强大的 MS Office Excel 文档生成类库，基于Microsoft’s OpenXML ,支持普通xls,Excel2007,pdf,csv,html等
官方网站http://phpexcel.codeplex.com/
最新版本为2010-05-31 Release 1.7.3c ,7.1M左右

注意插入的值是以’=’开头，默认会用作公式，而不是字符串导致出错。

当前支持的一些功能:

Set spreadsheet meta data (author, title, description, …)
支持文档属性设置
Add worksheets to spreadsheet
增加工作表
Add data and formulas to individual cells
填充单元格值
Merge cells
合并单元格
Protect ranges of cells with a password
密码保护单元格
Supports setting cell width and height
设定例宽和例高
Supports different fonts and font styles
设定字体及样式
Supports formatting, styles, cell borders, fills, gradients, …
设定格式，样式，边框
Supports hyperlinks
超链接格式
Supports different data types for individual cells
独立的单元格类型
Supports cell text wrapping
单元格折行
Supports conditional formatting
条件格式
Supports column auto-sizing
自适应列宽
Supports rich-text strings
富文本格式
Supports autofilter
自动筛选
Supports “freezing” cell panes
锁定单元格
Supports cell-level security
单元格级安全
Supports workbook-level security
工作簿级安全
Supports worksheet-level protection
工作表保护
Group rows/columns
分组
Cell data validation
数据验证
Insert/remove rows/columns
插入/移除行或列
Named ranges
指定范围
Worksheet references
工作表引用
Calculate formula values
公式计算
Add comments to a cell
单元格注释
Add images to your spreadsheet
添加图片
Set image styles
图片样式
Positioning
图片位置
Rotation
图片角度
Shadow
图片阴影
Set printing options
打印选项
Header
页头
Footer
页脚
Page margins
页边距
Paper size
打印页大小
Orientation
打印方向
Row and column breaks
行或列换行
Repeat rows at header / columns at left
重复行或列
Print area
区域打印
Output your spreadsheet object to different file formats
多种输出格式
Excel 2007 (spreadsheetML)
BIFF8 (Excel 97 and higher)
PHPExcel Serialized Spreadsheet
CSV (Comma Separated Values)
HTML
PDF
Read different file formats into your spreadsheet object
读取多种格式
Excel 2007 (spreadsheetML)
BIFF5 (Excel 5.0 / Excel 95), BIFF8 (Excel 97 and higher)
PHPExcel Serialized Spreadsheet
Excel 2003 XML format
Symbolic Link (SYLK)
CSV (Comma Separated Values)图片详例：

phpexcel生成效果图：

 
其它详例：

• 01simple.xlsx
• 02types.xlsx
• 03formulas.xlsx
• 04printing.xlsx
• 05featuredemo.xlsx
• 06largescale.xlsx
• 07reader.xlsx
• 08conditionalformatting.xlsx
• 09pagebreaks.xlsx
• 10autofilter.xlsx
• 11documentsecurity.xlsx
• 12serializedfileformat.xlsx
• 14excel5.xls
• 15datavalidation.xlsx

php代码详例:

getProperties()->setCreator(“C1G”)
->setLastModifiedBy(“C1G”)
->setTitle(“phpexcel Test Document”)
->setSubject(“phpexcel Test Document”)
->setDescription(“Test document for phpexcel, generated using PHP classes.”)
->setKeywords(“office 2007 openxml php c1gstudio”)
->setCategory(“Test”);

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

//设置sheet名字
$objPHPExcel->getActiveSheet()->setTitle(‘phpexcel demo’);

//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

//由PHPExcel根据传入内容自动判断单元格内容类型
$objPHPExcel->getActiveSheet()->setCellValue(‘A1’, “Firstname”);
$objPHPExcel->getActiveSheet()->setCellValue(‘B1’, “Lastname”);
$objPHPExcel->getActiveSheet()->setCellValue(‘C1’, “Phone”);
$objPHPExcel->getActiveSheet()->setCellValue(‘D1’, “Fax”);
$objPHPExcel->getActiveSheet()->setCellValue(‘E1’, “Address”);
$objPHPExcel->getActiveSheet()->setCellValue(‘F1’, “ZIP”);
$objPHPExcel->getActiveSheet()->setCellValue(‘G1’, “DATE”);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 8, ‘firstname’);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, ‘lastname’);

// utf8格式字符
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A15’, ‘Miscellaneous glyphs’)
->setCellValue(‘A16’, ‘éàèùâêîôûëïüÿäöüç’)
->setCellValue(‘A17’, ‘phpexcel演示’);

$objPHPExcel->getActiveSheet()->setCellValue(‘A9’, “502”);
$objPHPExcel->getActiveSheet()->setCellValue(‘B9’, “99”);
$objPHPExcel->getActiveSheet()->setCellValue(‘C9’, “=SUM(A9:B9)”);

//设置列宽
$objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(’20’);

// 设置行高
$objPHPExcel->getActiveSheet()->getRowDimension(‘9’)->setRowHeight(20);

// 加粗
$objPHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setBold(true);

// 中文
$objPHPExcel->getActiveSheet()->setCellValue(‘A2’, “小风”);
$objPHPExcel->getActiveSheet()->setCellValue(‘B2’, “wang”);

// 设置单元格格式
$objPHPExcel->getActiveSheet()->getCell(‘C2’)->setValueExplicit(‘861391327543258’, PHPExcel_Cell_DataType::TYPE_NUMERIC);

// 日期
$objPHPExcel->getActiveSheet()->setCellValue(‘G2’, ‘2008-12-31’);
$objPHPExcel->getActiveSheet()->getStyle(‘G2’)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// 时间戳
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$objPHPExcel->getActiveSheet()->setCellValue(‘G3’, PHPExcel_Shared_Date::PHPToExcel($time));
$objPHPExcel->getActiveSheet()->getStyle(‘G3’)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// url
$objPHPExcel->getActiveSheet()->setCellValue(‘G11’, ‘blog.c1gstudio.com’);
$objPHPExcel->getActiveSheet()->getCell(‘G11’)->getHyperlink()->setUrl(‘http://blog.c1gstudio.com’);

// 另一个sheet
$objPHPExcel->getActiveSheet()->setCellValue(‘G12’, ‘sheetb’);
$objPHPExcel->getActiveSheet()->getCell(‘G12′)->getHyperlink()->setUrl(“sheet://’sheetb’!A1”);

// 水平居上
$objPHPExcel->getActiveSheet()->getStyle(‘A9:B9’)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

// 单元格换行
$objPHPExcel->getActiveSheet()->getStyle(‘G2:G3’)->getAlignment()->setWrapText(true);

// 合并
$objPHPExcel->getActiveSheet()->mergeCells(‘A18:E22’);

// 隐藏D列
$objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setVisible(false);

//
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setCollapsed(true);

// 固定第一行
$objPHPExcel->getActiveSheet()->freezePane(‘A2’);

// 保护工作表
$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);

//设置边框
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
array(‘borders’ => array(
‘left’ => array(‘style’ => PHPExcel_Style_Border::BORDER_MEDIUM)
)
));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, “B1:B10”);

// 创建一个新的工作表
$objWorksheet1 = $objPHPExcel->createSheet();
$objWorksheet1->setTitle(‘sheetb’);

$objPHPExcel->setActiveSheetIndex(1);

// 创建一个图片
$gdImage = @imagecreatetruecolor(200, 20) or die(‘Cannot Initialize new GD image stream’);
$textColor = imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5, ‘Created with PHPExcel (c1gstudio.com)’, $textColor);

// 把创建的图片添加到工作表
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName(‘Sample image’);
$objDrawing->setDescription(‘Sample image’);
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->setActiveSheetIndex(0);

// 保存
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
$objWriter->save(‘testexcel’.time().’.xls’);

/*
//在浏览器导出
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”testexcel.xls”‘);
header(‘Cache-Control: max-age=0’);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
$objWriter->save(‘php://output’);

*/
/**
* $Log$
*/
?>

上面代码生成的excel文件.
testexcel1278403711.xls