Use PHPExcel for Excel usage instance analysis

This article mainly introduces how to use PHPExcel to operate Excel. Examples of common skills such as using PHPExcel to read, write, and generate are analyzed, which is of great practical value, for more information, see the examples in this article. Share it with you for your reference. The specific analysis is as follows:

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.

1. header section:

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

2. write excel:

// 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; $ igetActiveSheet ()-> 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: FORMAT_DATE_YYYYMMDDSLASH ); // Add comment $ objPHPExcel-> getActiveSheet ()-> getC Omment ('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 on the current invoice, excl Uding 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 ena Ble 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'); $ obj Validation-> 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 __));

3. Read excel

//Include class require_once('Classes/PHPExcel/Reader/Excel2007.php'); $objReader = new PHPExcel_Reader_Excel2007; $objPHPExcel = $objReader->load("05featuredemo.xlsx");

4. read and write csv files

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 __));

5. write html

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__));

6. write pdf

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', '.pdf', __FILE__));//Echo memory peak usageecho date('H:i:s')." Peak memory usage: ".(memory_get_peak_usage(true) / 1024 / 1024)." MB\r\n";

The above section describes how to use PHPExcel to perform Excel usage instance Analysis _ php skills. For more information, see PHP Chinese website (www.php1.cn )!