Home > Backend Development > PHP Tutorial > Basic usage of phpexcel class library, a complete collection of phpexcel introductory examples

Basic usage of phpexcel class library, a complete collection of phpexcel introductory examples

WBOY
Release: 2016-07-25 08:51:36
Original
965 people have browsed it
  1. //写入excel文件

  2. //Include class
  3. require_once('Classes/PHPExcel.php');
  4. require_once('Classes/PHPExcel/Writer/Excel2007.php');
  5. $objPHPExcel = new PHPExcel();

  6. //Set properties 设置文件属性

  7. $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
  8. $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
  9. $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
  10. $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
  11. $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
  12. $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
  13. $objPHPExcel->getProperties()->setCategory("Test result file");

  14. //Add some data 添加数据

  15. $objPHPExcel->setActiveSheetIndex(0);
  16. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置
  17. $objPHPExcel->getActiveSheet()->setCellValue('A2', true);
  18. $objPHPExcel->getActiveSheet()->setCellValue('A3', false);
  19. $objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
  20. $objPHPExcel->getActiveSheet()->setCellValue('B3', 2);
  21. $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
  22. $objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');

  23. //循环

  24. for($i = 1;$i<200;$i++) {
  25. $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i);
  26. $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');
  27. }

  28. //日期格式化

  29. $objPHPExcel->getActiveSheet()->setCellValue('D1', time());
  30. $objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

  31. //Add comment 添加注释

  32. $objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
  33. $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
  34. $objCommentRichText->getFont()->setBold(true);
  35. $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("rn");
  36. $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');

  37. //Add rich-text string 添加文字 可设置样式

  38. $objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
  39. $objRichText->createText('This invoice is ');
  40. $objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
  41. $objPayable->getFont()->setBold(true);
  42. $objPayable->getFont()->setItalic(true);
  43. $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
  44. $objRichText->createText(', unless specified otherwise on the invoice.');

  45. //Merge cells 合并分离单元格

  46. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
  47. $objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

  48. //Protect cells 保护单元格

  49. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needs to be set to true in order to enable any worksheet protection!
  50. $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

  51. //Set cell number formats 数字格式化

  52. $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
  53. $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

  54. //Set column widths 设置列宽度

  55. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  56. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

  57. //Set fonts 设置字体

  58. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
  59. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
  60. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
  61. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  62. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

  63. //Set alignments 设置对齐

  64. $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  65. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
  66. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  67. $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);

  68. //Set column borders 设置列边框

  69. $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  70. $objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  71. $objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  72. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
  73. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

  74. //Set border colors 设置边框颜色

  75. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
  76. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  77. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  78. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

  79. //Set fills 设置填充

  80. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  81. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

  82. //Add a hyperlink to the sheet 添加链接

  83. $objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
  84. $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
  85. $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
  86. $objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

  87. //Add a drawing to the worksheet 添加图片

  88. $objDrawing = new PHPExcel_Worksheet_Drawing();
  89. $objDrawing->setName('Logo');
  90. $objDrawing->setDescription('Logo');
  91. $objDrawing->setPath('./images/officelogo.jpg');
  92. $objDrawing->setHeight(36);
  93. $objDrawing->setCoordinates('B15');
  94. $objDrawing->setOffsetX(110);
  95. $objDrawing->setRotation(25);
  96. $objDrawing->getShadow()->setVisible(true);
  97. $objDrawing->getShadow()->setDirection(45);
  98. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

  99. //Play around with inserting and removing rows and columns

  100. $objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
  101. $objPHPExcel->getActiveSheet()->removeRow(6, 10);
  102. $objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
  103. $objPHPExcel->getActiveSheet()->removeColumn('E', 5);

  104. //Add conditional formatting

  105. $objConditional1 = new PHPExcel_Style_Conditional();
  106. $objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
  107. $objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
  108. $objConditional1->setCondition('0');
  109. $objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
  110. $objConditional1->getStyle()->getFont()->setBold(true);

  111. //Set autofilter 自动过滤

  112. $objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');

  113. //Hide "Phone" and "fax" column 隐藏列

  114. $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
  115. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

  116. //Set document security 设置文档安全

  117. $objPHPExcel->getSecurity()->setLockWindows(true);
  118. $objPHPExcel->getSecurity()->setLockStructure(true);
  119. $objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");

  120. //Set sheet security 设置工作表安全

  121. $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
  122. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled in order to enable any of the following!
  123. $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
  124. $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
  125. $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

  126. //Calculated data 计算

  127. echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . "rn";

  128. //Set outline levels

  129. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
  130. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
  131. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);

  132. //Freeze panes

  133. $objPHPExcel->getActiveSheet()->freezePane('A2');

  134. //Rows to repeat at top

  135. $objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);

  136. //Set data validation 验证输入值

  137. $objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();
  138. $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
  139. $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
  140. $objValidation->setAllowBlank(true);
  141. $objValidation->setShowInputMessage(true);
  142. $objValidation->setShowErrorMessage(true);
  143. $objValidation->setErrorTitle('Input error');
  144. $objValidation->setError('Number is not allowed!');
  145. $objValidation->setPromptTitle('Allowed input');
  146. $objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
  147. $objValidation->setFormula1(10);
  148. $objValidation->setFormula2(20);
  149. $objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation);

  150. //Create a new worksheet, after the default sheet 创建新的工作标签

  151. $objPHPExcel->createSheet();
  152. $objPHPExcel->setActiveSheetIndex(1);

  153. //Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚

  154. $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');
  155. $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

  156. //Set page orientation and size 方向大小

  157. $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
  158. $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

  159. //Rename sheet 重命名工作表标签

  160. $objPHPExcel->getActiveSheet()->setTitle('Simple');

  161. //Set active sheet index to the first sheet, so Excel opens this as the first sheet

  162. $objPHPExcel->setActiveSheetIndex(0);

  163. //Save Excel 2007 file 保存

  164. $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
  165. $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
  166. //Save Excel 5 file 保存
  167. require_once('Classes/PHPExcel/Writer/Excel5.php');
  168. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  169. $objWriter->save(str_replace('.php', '.xls', __FILE__));

  170. //1.6.2新版保存

  171. require_once('Classes/PHPExcel/IOFactory.php');
  172. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  173. $objWriter->save(str_replace('.php', '.xls', __FILE__));
  174. 读excel

  175. //Include class

  176. require_once('Classes/PHPExcel/Reader/Excel2007.php');
  177. $objReader = new PHPExcel_Reader_Excel2007;
  178. $objPHPExcel = $objReader->load("05featuredemo.xlsx");
  179. 读写csv

  180. require_once("05featuredemo.inc.php");

  181. require_once('Classes/PHPExcel/Writer/CSV.php');
  182. require_once('Classes/PHPExcel/Reader/CSV.php');
  183. require_once('Classes/PHPExcel/Writer/Excel2007.php');
  184. //Write to CSV format 写
  185. $objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
  186. $objWriter->setDelimiter(';');
  187. $objWriter->setEnclosure('');
  188. $objWriter->setLineEnding("rn");
  189. $objWriter->setSheetIndex(0);
  190. $objWriter->save(str_replace('.php', '.csv', __FILE__));
  191. //Read from CSV format 读
  192. $objReader = new PHPExcel_Reader_CSV();
  193. $objReader->setDelimiter(';');
  194. $objReader->setEnclosure('');
  195. $objReader->setLineEnding("rn");
  196. $objReader->setSheetIndex(0);
  197. $objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));
  198. //Write to Excel2007 format
  199. $objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
  200. $objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));
  201. 写html

  202. require_once("05featuredemo.inc.php");

  203. require_once('Classes/PHPExcel/Writer/HTML.php');
  204. //Write to HTML format
  205. $objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
  206. $objWriter->setSheetIndex(0);
  207. $objWriter->save(str_replace('.php', '.htm', __FILE__));
  208. 写pdf

  209. require_once("05featuredemo.inc.php");

  210. require_once('Classes/PHPExcel/IOFactory.php');
  211. //Write to PDF format
  212. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
  213. $objWriter->setSheetIndex(0);
  214. $objWriter->save(str_replace('.php', '.pdf', __FILE__));
  215. //Echo memory peak usage
  216. echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MBrn";

复制代码


source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template