PHP ExcelPHPExcel-php Tutorial

PHP operations ExcelPHPExcel detailed explanation to the header

header("Content-Type:application/vnd.ms-excel");header("Content-Disposition:attachment;filename=sample.xls");header("Pragma:no-cache");header("Expires:0");

2. PHPExcel

Http://www.codeplex.com/PHPExcel
Http://www.phpexcel.net
The Tests Directory of the development kit provides detailed examples that support Chinese characters. Note that the file encoding file is saved as UTF-8.

Write excel

[Php] view plain copy

// Include class require_once ('classes/PHPExcel. php '); require_once ('classes/PHPExcel/Writer/excel2007.php'); $ objPHPExcel = new PHPExcel (); // Set properties to Set File attributes $ 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 "); // Add some data to Add data $ objPHPExcel-> setActiveSheetIndex (0); $ objPHPExcel-> getActiveSheet ()-> setCellValu E ('A1', 'Hello'); // you can specify the location $ 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! '); // Loop for ($ I = 1; $ I <200; $ I ++) {$ objPHPExcel-> getActiveSheet ()-> setCellValue ('A '. $ I, $ I); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('B '. $ I, 'Test value');} // format the date $ objPHPExcel-> getActiveSheet ()-> setCellValue ('d1 ', time ()); $ objPHPExcel-> getActiveSheet ()-> getStyle ('d1 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: Encrypted); // Add comment to Add comments $ objPHPEx Cel-> 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 o N the current invoice, excluding VAT. '); // Add rich-text string the style can be set to $ objRichText = new PHPExcel_RichText ($ objPHPExcel-> getActiveSheet ()-> getCell ('a18 ')); $ objRichText-> createText ('This invoice is '); $ 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. '); // Merge cells Merge and separate cells $ objPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22'); $ objPHPExcel-> getActiveSheet () -> unmergeCells ('a18: e22'); // Protect cells $ objPHPExcel-> getActiveSheet ()-> getProtection ()-> setSheet (true ); // Needs to be Set to true in order to enable any worksheet protection! $ ObjPHPExcel-> getActiveSheet ()-> protectCells ('A3: e13', 'phpexcel '); // Set cell number formats number formatting $ objPHPExcel-> getActiveSheet () -> getStyle ('e4 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE); $ objPHPExcel-> getActiveSheet () -> duplicateStyle ($ objPHPExcel-> getActiveSheet ()-> getStyle ('e4 '), 'e5: e13 '); // Set column widths to Set the column width $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true); $ objPHPExcel-> getActiveSheet () -> getColumnDimension ('D')-> setWidth (12); // Set fonts to Set the font $ 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 ); // Set alignments to align $ objPHPExcel-> getActiveSheet ()-> getStyle ('d11')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT ); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setHorizontal (metadata: HORIZONTAL_JUSTIFY); $ objPHPExcel-> getActiveSheet () -> getStyle ('a18')-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A3 ') -> getAlignment ()-> setWrapText (true); // Set column borders to Set the column border $ objPHPExcel-> getActiveSheet ()-> getStyle ('A4 ')-> getBorders () -> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a10')-> getBorders ()-> getLeft () -> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); $ objPHPExcel-> getActiveSheet ()-> getStyle ('e10')-> getBorders ()-> getRight () -> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); $ objPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getBorders ()-> getLeft () -> setBorderStyle (PHPExcel_Style_Border: BORDER_THICK); $ objPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getBorders ()-> getBottom () -> setBorderStyle (PHPExcel_Style_Border: BORDER_THICK); // Set border colors to Set the border color $ 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 '); // Set fills to Set filling $ objPHPExcel-> getActiveSheet ()-> getStyle ('A1 ') -> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getFill ()-> getStartColor () -> setARGB ('ff808080'); // Add a hyperlink to the sheet Add link $ 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 ); // Add a drawing to the worksheet to Add images $ 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 (); // Play around with inserting and removing rows and columns $ objPHPExcel-> getActiveSheet ()-> insertNewR OwBefore (6, 10); $ objPHPExcel-> getActiveSheet ()-> removeRow (6, 10); $ objPHPExcel-> getActiveSheet ()-> insertNewColumnBefore ('e ', 5); $ objPHPExcel-> getActiveSheet ()-> removeColumn ('e', 5); // Add conditional formatting $ objConditional1 = new PHPExcel_Style_Conditional (); $ objConditional1-> setConditionType (PHPExcel_Style_Conditional: CONDITION_CELLIS); $ objConditional1-> setOperatorType (PHPExcel_Style_Con Ditional: OPERATOR_LESSTHAN); $ objConditional1-> setCondition ('0'); $ objConditional1-> getStyle ()-> getFont ()-> getColor ()-> setARGB (encoding:: COLOR_RED); $ objConditional1-> getStyle ()-> getFont ()-> setBold (true); // Set autofilter to automatically filter $ objPHPExcel-> getActiveSheet () -> setAutoFilter ('A1: C9 '); // Hide "Phone" and "fax" column hidden columns $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('C ') -> setVisible (False); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setVisible (false); // Set document security to Set document security $ objPHPExcel-> getSecurity () -> setLockWindows (true); $ objPHPExcel-> getSecurity ()-> setLockStructure (true); $ objPHPExcel-> getSecurity ()-> setWorkbookPassword ("PHPExcel "); // Set sheet security to Set worksheet security $ objPHPExcel-> getActiveSheet ()-> getProtection ()-> setPassword ('phpexcel '); $ objPHPExcel-> ge TActiveSheet ()-> getProtection ()-> setSheet (true); // This shocould 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); // Calculated data calculates echo 'value of B14 [= COUNT (B2: B12)]: '. $ objPHPExcel-> getActiveSheet ()-> getCell ('b14')-> getCalculatedValue (). "\ r \ n"; // Set outline levels $ objPHPExcel-> getActiveSheet ()-> g EtColumnDimension ('e')-> setOutlineLevel (1); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setVisible (false ); $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('e')-> setCollapsed (true); // Freeze panes $ objPHPExcel-> getActiveSheet () -> freezePane ('A2 '); // Rows to repeat top $ objPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setrowstorepeattopbystartandend (1, 1 ); // Set data validation fails. Value $ objValidation = $ objPHPExcel-> getActiveSheet ()-> getCell ('b3')-> getDataValidation (); $ objValidation-> setType (PHPExcel_Cell_DataValidation: TYPE_WHOLE ); $ objValidation-> setErrorStyle (Rule: 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); // Create a new worksheet, after the default sheet creates a new work Tag $ objPHPExcel-> createSheet (); $ objPHPExcel-> setActiveSheetIndex (1 ); // Set header and footer. when no different headers for odd/even are used, odd header is assumed. header and footer $ objPHPExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddHeader ('& C & HPlease treat this document as confidential! '); $ ObjPHPExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddFooter (' & L & B '. $ objPHPExcel-> getProperties ()-> getTitle (). '& RPage & P of & N'); // Set page orientation and size direction size $ objPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup :: ORIENTATION_LANDSCAPE); $ objPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (partition: PAPERSIZE_A4); // Rename sheet Rename the worksheet label $ objPHPExcel-> getActiveSheet () -> setTitle ('simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $ objPHPExcel-> setActiveSheetIndex (0 ); // Save the Excel 2007 file to save $ objWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel); $ objWriter-> Save (str_replace ('. php ', '.xlsx', _ FILE _); // Save Excel 5 file to Save require_once ('classes/PHPExcel/Writer/excel5.php '); $ objWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel); $ objWriter-> save (str_replace ('. php ', '.xls', _ FILE _); // 1.6.2 save require_once ('classes/PHPExcel/IOFactory. php '); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007'); $ objWriter-> save (str_replace ('. php ', '.xls', _ FILE __));

Read excel

[Php] view plain copy

// Include class require_once ('classes/PHPExcel/Reader/excel2007.php'); $ objReader = new PHPExcel_Reader_Excel2007; $ objPHPExcel = $ objReader-> load ("plugin ");

Read/write csv

[Php] view plain copy

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'); // Write to CSV format to Write $ objWriter = new PHPExcel_Writer_CSV ($ objPHPExcel ); $ objWriter-> setDelimiter (';'); $ objWriter-> setEnclosure (''); $ objWriter-> setLineEnding (" \ r \ n "); $ objWriter-> setSheetIndex (0); $ objWriter-> save (str_replace ('. php ', '.csv', _ FILE _); // Read from CSV format Read $ objReader = new PHPExcel_Reader_CSV (); $ objReader-> setDelimiter (';'); $ objReader-> setEnclosure (''); $ objReader-> setLineEnding (" \ r \ n "); $ objReader-> setSheetIndex (0 ); $ objPHPExcelFromCSV = $ objReader-> load (str_replace ('. php ', '.csv', _ FILE _); // Write to Excel2007 format $ ob1_riter2007 = new partition ($ objPHPExcelFromCSV); $ ob1_riter2007-> save (str_replace ('. php ', '.xlsx', _ FILE __));

Write html

[Php] view plain copy

Require_once ("05featuredemo. inc. php "); require_once ('classes/PHPExcel/Writer/HTML. php '); // Write to HTML format $ objWriter = new PHPExcel_Writer_HTML ($ objPHPExcel); $ objWriter-> setSheetIndex (0); $ objWriter-> save (str_replace ('. php ', '.htm', _ FILE __));

Write pdf

[Php] view plain copy

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', 'clerk', _ FILE _); // Echo memory peak usage echo date ('H: I: s '). "Peak memory usage :". (memory_get_peak _usage (true)/1024/1024 ). "MB \ r \ n ";