Skip to content

强大的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生成效果图：
phpexcel_features

 
其它详例：

php代码详例:

  1. <?php
  2. /** Error reporting */
  3. error_reporting(E_ALL);
  4.  
  5. /** PHPExcel */
  6. require_once '../Classes/PHPExcel.php';
  7.  
  8.  
  9. $objPHPExcel = new PHPExcel();
  10.  
  11. // 设置文件属性
  12. $objPHPExcel->getProperties()->setCreator("C1G")
  13.                              ->setLastModifiedBy("C1G")
  14.                              ->setTitle("phpexcel Test Document")
  15.                              ->setSubject("phpexcel Test Document")
  16.                              ->setDescription("Test document for phpexcel, generated using PHP classes.")
  17.                              ->setKeywords("office 2007 openxml php c1gstudio")
  18.                              ->setCategory("Test");
  19.  
  20. //设置当前活动的sheet
  21. $objPHPExcel->setActiveSheetIndex(0);
  22.  
  23. //设置sheet名字
  24. $objPHPExcel->getActiveSheet()->setTitle('phpexcel demo');
  25.  
  26. //设置默认行高
  27. $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
  28.  
  29. //由PHPExcel根据传入内容自动判断单元格内容类型
  30. $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");
  31. $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");
  32. $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
  33. $objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax");
  34. $objPHPExcel->getActiveSheet()->setCellValue('E1', "Address");
  35. $objPHPExcel->getActiveSheet()->setCellValue('F1', "ZIP");
  36. $objPHPExcel->getActiveSheet()->setCellValue('G1', "DATE");
  37.  
  38.  
  39. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 8, 'firstname');
  40. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'lastname');
  41.  
  42.  
  43. // utf8格式字符
  44. $objPHPExcel->setActiveSheetIndex(0)
  45.             ->setCellValue('A15', 'Miscellaneous glyphs')
  46.             ->setCellValue('A16', 'éàèùâêîôûëïüÿäöüç')
  47.             ->setCellValue('A17', 'phpexcel演示');
  48.  
  49. $objPHPExcel->getActiveSheet()->setCellValue('A9', "502");
  50. $objPHPExcel->getActiveSheet()->setCellValue('B9', "99");
  51. $objPHPExcel->getActiveSheet()->setCellValue('C9', "=SUM(A9:B9)");
  52.  
  53. //设置列宽
  54. $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');
  55.  
  56.  
  57. // 设置行高
  58. $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(20);
  59.  
  60. // 加粗
  61. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  62.  
  63. // 中文
  64. $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
  65. $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");
  66.  
  67. // 设置单元格格式
  68. $objPHPExcel->getActiveSheet()->getCell('C2')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_NUMERIC);
  69.  
  70. // 日期
  71. $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
  72. $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
  73.  
  74. // 时间戳
  75. $time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
  76. $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
  77. $objPHPExcel->getActiveSheet()->getStyle('G3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
  78.  
  79. // url
  80. $objPHPExcel->getActiveSheet()->setCellValue('G11', 'blog.c1gstudio.com');
  81. $objPHPExcel->getActiveSheet()->getCell('G11')->getHyperlink()->setUrl('http://blog.c1gstudio.com');
  82.  
  83. // 另一个sheet
  84. $objPHPExcel->getActiveSheet()->setCellValue('G12', 'sheetb');
  85. $objPHPExcel->getActiveSheet()->getCell('G12')->getHyperlink()->setUrl("sheet://'sheetb'!A1");
  86.  
  87. // 水平居上
  88. $objPHPExcel->getActiveSheet()->getStyle('A9:B9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
  89.  
  90. // 单元格换行
  91. $objPHPExcel->getActiveSheet()->getStyle('G2:G3')->getAlignment()->setWrapText(true);
  92.  
  93. // 合并
  94. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
  95.  
  96.  
  97. // 隐藏D列
  98. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
  99.  
  100. //
  101. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
  102. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
  103. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setOutlineLevel(1);
  104. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);
  105. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setCollapsed(true);
  106.  
  107. // 固定第一行
  108. $objPHPExcel->getActiveSheet()->freezePane('A2');
  109.  
  110. // 保护工作表
  111. $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
  112. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
  113. $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
  114. $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
  115. $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
  116.  
  117. //设置边框
  118. $sharedStyle1 = new PHPExcel_Style();
  119. $sharedStyle1->applyFromArray(
  120.     array('borders' => array(
  121.                                 'left'        => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
  122.                             )
  123.          ));
  124. $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B1:B10");
  125.  
  126. // 创建一个新的工作表
  127. $objWorksheet1 = $objPHPExcel->createSheet();
  128. $objWorksheet1->setTitle('sheetb');
  129.  
  130.  
  131. $objPHPExcel->setActiveSheetIndex(1);
  132.  
  133.  
  134. // 创建一个图片
  135. $gdImage = @imagecreatetruecolor(200, 20) or die('Cannot Initialize new GD image stream');
  136. $textColor = imagecolorallocate($gdImage, 255, 255, 255);
  137. imagestring($gdImage, 1, 5, 5,  'Created with PHPExcel (c1gstudio.com)', $textColor);
  138.  
  139. // 把创建的图片添加到工作表
  140. $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
  141. $objDrawing->setName('Sample image');
  142. $objDrawing->setDescription('Sample image');
  143. $objDrawing->setImageResource($gdImage);
  144. $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
  145. $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
  146. $objDrawing->setHeight(36);
  147. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  148.  
  149. $objPHPExcel->setActiveSheetIndex(0);
  150.  
  151. // 保存
  152. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  153. $objWriter->save('testexcel'.time().'.xls');
  154.  
  155. /*
  156. //在浏览器导出
  157. header('Content-Type: application/vnd.ms-excel');
  158. header('Content-Disposition: attachment;filename="testexcel.xls"');
  159. header('Cache-Control: max-age=0');
  160.  
  161. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  162. $objWriter->save('php://output');
  163.  
  164. */
  165. /**
  166. * $Log$
  167. */
  168. ?>

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

Related Posts

Posted in PHP, 技术.

Tagged with , , .

By C1G – 2010/07/06

One Response

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. sjolzy says

    很强大，精简点的会考虑用。哈哈

    2011/06/06, 20:24



Some HTML is OK

or, reply to this post via trackback.

« »