PhpExcel export the excel + hyperlink instance code

Source: Internet
Author: User
PhpExcel export the excel + hyperlink instance code

  1. // Write an excel file

  2. // Include class

  3. Require_once ('classes/PHPExcel. php ');
  4. Require_once ('classes/PHPExcel/Writer/excel2007.php ');
  5. $ ObjPHPExcel = new PHPExcel ();

  6. /**

  7. * Export excel in phpExcel
  8. * By bbs.it-home.org
  9. */

  10. // Set properties

  11. $ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw ");
  12. $ ObjPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw ");
  13. $ ObjPHPExcel-> getProperties ()-> setTitle ("Office 2007 XLSX Test Document ");
  14. $ ObjPHPExcel-> getProperties ()-> setSubject ("Office 2007 XLSX Test Document ");
  15. $ ObjPHPExcel-> getProperties ()-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes .");
  16. $ ObjPHPExcel-> getProperties ()-> setKeywords ("office 2007 openxml php ");
  17. $ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file ");

  18. // Add some data Add data

  19. $ ObjPHPExcel-> setActiveSheetIndex (0 );
  20. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A1', 'Hello'); // you can specify the location
  21. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A2 ', true );
  22. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A3 ', false );
  23. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('B2', 'World! ');
  24. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b3', 2 );
  25. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('C1', 'Hello ');
  26. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('D2 ', 'World! ');
  27. // Loop
  28. For ($ I = 1; I I <200; $ I ++ ){
  29. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A'. $ I, $ I );
  30. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('B'. $ I, 'Test value ');
  31. }
  32. // Format the date
  33. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('d1 ', time ());
  34. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d1 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_DATE_YYYYMMDDSLASH );

  35. // Add comment

  36. $ ObjPHPExcel-> getActiveSheet ()-> getComment ('e11')-> setAuthor ('phpexcel ');
  37. $ ObjCommentRichText = $ objPHPExcel-> getActiveSheet ()-> getComment ('e11')-> getText ()-> createTextRun ('phpexcel :');
  38. $ ObjCommentRichText-> getFont ()-> setBold (true );
  39. $ ObjPHPExcel-> getActiveSheet ()-> getComment ('e11')-> getText ()-> createTextRun ("\ r \ n ");
  40. $ ObjPHPExcel-> getActiveSheet ()-> getComment ('e11')-> getText ()-> createTextRun ('total amount on the current invoice, excluding VAT .');

  41. // Add rich-text string Add text to set the style

  42. $ ObjRichText = new PHPExcel_RichText ($ objPHPExcel-> getActiveSheet ()-> getCell ('a18 ′));
  43. $ ObjRichText-> createText ('This invoice is ');
  44. $ ObjPayable = $ objRichText-> createTextRun ('Payable within thirty days after the end of the month ');
  45. $ ObjPayable-> getFont ()-> setBold (true );
  46. $ ObjPayable-> getFont ()-> setItalic (true );
  47. $ ObjPayable-> getFont ()-> setColor (new PHPExcel_Style_Color (PHPExcel_Style_Color: COLOR_DARKGREEN ));
  48. $ ObjRichText-> createText (', unless specified otherwise on the invoice .');

  49. // Merge cells Merge and separate cells

  50. $ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22 ′);
  51. $ ObjPHPExcel-> getActiveSheet ()-> unmergeCells ('a18: e22 ′);

  52. // Protect cells

  53. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSheet (true); // Needs to be set to true in order to enable any worksheet protection!
  54. $ ObjPHPExcel-> getActiveSheet ()-> protectCells ('A3: e13', 'phpexcel ');

  55. // Set cell number formats numeric formatting

  56. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e4 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE );
  57. $ ObjPHPExcel-> getActiveSheet ()-> duplicateStyle ($ objPHPExcel-> getActiveSheet ()-> getStyle ('e4 '), 'e5: e13 ′);

  58. // Set column widths to Set the column width

  59. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true );
  60. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (12 );

  61. // Set fonts to Set the font

  62. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFont ()-> setName ('candara ');
  63. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFont ()-> setSize (20 );
  64. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFont ()-> setBold (true );
  65. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFont ()-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
  66. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_WHITE );

  67. // Set alignments to Set alignments

  68. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d11')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
  69. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_JUSTIFY );
  70. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );
  71. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A3 ')-> getAlignment ()-> setWrapText (true );

  72. // Set column borders to Set the column border

  73. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
  74. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a10')-> getBorders ()-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
  75. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e10')-> getBorders ()-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
  76. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THICK );
  77. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getBorders ()-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THICK );

  78. // Set border colors to Set the border color

  79. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getLeft ()-> getColor ()-> setARGB ('ff993300 ′);
  80. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getTop ()-> getColor ()-> setARGB ('ff993300 ′);
  81. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getBottom ()-> getColor ()-> setARGB ('ff993300 ′);
  82. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getBorders ()-> getRight ()-> getColor ()-> setARGB ('ff993300 ′);

  83. // Set fills to Set filling

  84. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
  85. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getFill ()-> getStartColor ()-> setARGB ('ff808080 ′);

  86. // Add a hyperlink to the sheet Add link

  87. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('e26', 'www .phpexcel.net ');
  88. $ ObjPHPExcel-> getActiveSheet ()-> getCell ('e26')-> getHyperlink ()-> setUrl ('http: // www.phpexcel.net ');
  89. $ ObjPHPExcel-> getActiveSheet ()-> getCell ('e26')-> getHyperlink ()-> setTooltip ('navigate to website ');
  90. $ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e26')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );

  91. // Add a drawing to the worksheet to Add an image

  92. $ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
  93. $ ObjDrawing-> setName ('logo ');
  94. $ ObjDrawing-> setDescription ('logo ');
  95. $ ObjDrawing-> setPath ('./images/officelogo.jpg ');
  96. $ ObjDrawing-> setHeight (36 );
  97. $ ObjDrawing-> setCoordinates ('b15 ′);
  98. $ ObjDrawing-> setOffsetX (110 );
  99. $ ObjDrawing-> setRotation (25 );
  100. $ ObjDrawing-> getShadow ()-> setVisible (true );
  101. $ ObjDrawing-> getShadow ()-> setDirection (45 );
  102. $ ObjDrawing-> setWorksheet ($ objPHPExcel-> getActiveSheet ());

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

  104. $ ObjPHPExcel-> getActiveSheet ()-> insertNewRowBefore (6, 10 );
  105. $ ObjPHPExcel-> getActiveSheet ()-> removeRow (6, 10 );
  106. $ ObjPHPExcel-> getActiveSheet ()-> insertNewColumnBefore ('e', 5 );
  107. $ ObjPHPExcel-> getActiveSheet ()-> removeColumn ('e', 5 );

  108. // Add conditional formatting

  109. $ ObjConditional1 = new PHPExcel_Style_Conditional ();
  110. $ ObjConditional1-> setConditionType (PHPExcel_Style_Conditional: CONDITION_CELLIS );
  111. $ ObjConditional1-> setOperatorType (PHPExcel_Style_Conditional: OPERATOR_LESSTHAN );
  112. $ ObjConditional1-> setCondition ('0 ′);
  113. $ ObjConditional1-> getStyle ()-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_RED );
  114. $ ObjConditional1-> getStyle ()-> getFont ()-> setBold (true );

  115. // Set autofilter automatic filtering

  116. $ ObjPHPExcel-> getActiveSheet ()-> setAutoFilter ('A1: C9 ′);

  117. // Hide the column in the Hide "Phone" and "fax" column

  118. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('C')-> setVisible (false );
  119. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setVisible (false );

  120. // Set document security to Set document security

  121. $ ObjPHPExcel-> getSecurity ()-> setLockWindows (true );
  122. $ ObjPHPExcel-> getSecurity ()-> setLockStructure (true );
  123. $ ObjPHPExcel-> getSecurity ()-> setWorkbookPassword ("PHPExcel ");

  124. // Set sheet security to Set worksheet security

  125. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setPassword ('phpexcel ');
  126. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSheet (true); // This shocould be enabled in order to enable any of the following!
  127. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSort (true );
  128. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setInsertRows (true );
  129. $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setFormatCells (true );

  130. // Calculated data calculation

  131. Echo 'value of B14 [= COUNT (B2: B12)]: '. $ objPHPExcel-> getActiveSheet ()-> getCell ('b14')-> getCalculatedValue (). "\ r \ n ";

  132. // Set outline levels

  133. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setOutlineLevel (1 );
  134. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setVisible (false );
  135. $ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setCollapsed (true );

  136. // Freeze panes

  137. $ ObjPHPExcel-> getActiveSheet ()-> freezePane ('A2 ′);

  138. // Rows to repeat top

  139. $ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setRowsToRepeatAtTopByStartAndEnd (1, 1 );

  140. // Set data validation input value

  141. $ ObjValidation = $ objPHPExcel-> getActiveSheet ()-> getCell ('b3')-> getDataValidation ();
  142. $ ObjValidation-> setType (PHPExcel_Cell_DataValidation: TYPE_WHOLE );
  143. $ ObjValidation-> setErrorStyle (PHPExcel_Cell_DataValidation: STYLE_STOP );
  144. $ ObjValidation-> setAllowBlank (true );
  145. $ ObjValidation-> setShowInputMessage (true );
  146. $ ObjValidation-> setShowErrorMessage (true );
  147. $ ObjValidation-> setErrorTitle ('input error ');
  148. $ ObjValidation-> setError ('number is not allowed! ');
  149. $ ObjValidation-> setPromptTitle ('allowed input ');
  150. $ ObjValidation-> setPrompt ('only numbers between 10 and 20 are allowed .');
  151. $ ObjValidation-> setFormula1 (10 );
  152. $ ObjValidation-> setFormula2 (20 );
  153. $ ObjPHPExcel-> getActiveSheet ()-> getCell ('b3')-> setDataValidation ($ objValidation );

  154. // Create a new worksheet, after the default sheet creates a new work Tag

  155. $ ObjPHPExcel-> createSheet (); bbs.it-home.org
  156. $ ObjPHPExcel-> setActiveSheetIndex (1 );

  157. // Set header and footer. When no different headers for odd/even are used, odd header is assumed. header and footer

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

  160. // Set page orientation and size

  161. $ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup: ORIENTATION_LANDSCAPE );
  162. $ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: PAPERSIZE_A4 );

  163. // Rename sheet Rename the worksheet label

  164. $ ObjPHPExcel-> getActiveSheet ()-> setTitle ('simple ');

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

  166. $ ObjPHPExcel-> setActiveSheetIndex (0 );

  167. // Save Excel 2007 file Save

  168. $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
  169. $ ObjWriter-> save (str_replace ('. php', '.xlsx', _ FILE __));

  170. // Save Excel 5 file Save

  171. Require_once ('classes/PHPExcel/Writer/excel5.php ');
  172. $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel );
  173. $ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));

  174. // Save the new version 1.6.2

  175. Require_once ('classes/PHPExcel/IOFactory. php ');
  176. $ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007 ′);
  177. $ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));

  178. // Read excel

  179. // Include class
  180. Require_once ('classes/PHPExcel/Reader/excel2007.php ');
  181. $ ObjReader = new PHPExcel_Reader_Excel2007;

  182. $ ObjPHPExcel = $ objReader-> load(41005featuredemo.xlsx ");

  183. // Read/write csv

  184. Require_once ("05featuredemo. inc. php ");
  185. Require_once ('classes/PHPExcel/Writer/CSV. php ');
  186. Require_once ('classes/PHPExcel/Reader/CSV. php ');
  187. Require_once ('classes/PHPExcel/Writer/excel2007.php ');

  188. // Write to CSV format

  189. $ ObjWriter = new PHPExcel_Writer_CSV ($ objPHPExcel );
  190. $ ObjWriter-> setDelimiter (';');
  191. $ ObjWriter-> setEnclosure (");
  192. $ ObjWriter-> setLineEnding ("\ r \ n ");
  193. $ ObjWriter-> setSheetIndex (0 );
  194. $ ObjWriter-> save (str_replace ('. php', '.csv', _ FILE __));

  195. // Read from CSV format

  196. $ ObjReader = new PHPExcel_Reader_CSV ();
  197. $ ObjReader-> setDelimiter (';');
  198. $ ObjReader-> setEnclosure (");
  199. $ ObjReader-> setLineEnding ("\ r \ n ");
  200. $ ObjReader-> setSheetIndex (0 );
  201. $ ObjPHPExcelFromCSV = $ objReader-> load (str_replace ('. php', '.csv', _ FILE __));

  202. // Write to Excel2007 format

  203. $ Ob1_riter2007 = new PHPExcel_Writer_Excel2007 ($ objPHPExcelFromCSV );
  204. $ Ob1_riter2007-> save (str_replace ('. php', '.xlsx', _ FILE __));

  205. // Write html

  206. Require_once ("05featuredemo. inc. php ");
  207. Require_once ('classes/PHPExcel/Writer/HTML. php ');

  208. // Write to HTML format

  209. $ ObjWriter = new PHPExcel_Writer_HTML ($ objPHPExcel );
  210. $ ObjWriter-> setSheetIndex (0 );
  211. $ ObjWriter-> save (str_replace ('. php', '.htm', _ FILE __));

  212. // Write pdf

  213. Require_once ("05featuredemo. inc. php ");
  214. Require_once ('classes/PHPExcel/IOFactory. php ');

  215. // Write to PDF format

  216. $ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'PDF ');
  217. $ ObjWriter-> setSheetIndex (0 );
  218. $ ObjWriter-> save (str_replace ('. php', 'clerk', _ FILE __));
  219. // Echo memory peak usage
  220. Echo date ('H: I: s'). "Peak memory usage:". (memory_get_peak_usage (true)/1024/1024). "MB \ r \ n ";

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.