PHP Excel operations? Basic usage of PHPExcel

Source: Internet
Author: User
Tags getcolor php excel
1. header [php] header (& quot; Content-Type: application/vnd. ms-excel & quot;); header (& quot; Content-Disposition: attachment?filename=sample.xls & quot;); header (& quot; Pragm

1. header
[Php]
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]
// Include class
Require_once ('classes/PHPExcel. php ');
Require_once ('classes/PHPExcel/Writer/excel2007.php ');
$ ObjPHPExcel = new PHPExcel ();
 
// Set properties
$ 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 Add data
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('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 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: FORMAT_DATE_YYYYMMDDSLASH );
 
// Add comment
$ ObjPHPExcel-> 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 on the current invoice, excluding VAT .');
 
// Add rich-text string Add text to set the style
$ 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 numeric 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 Set alignments
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d11')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: 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 an image
$ 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 ()-> insertNewRowBefore (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_Conditional: OPERATOR_LESSTHAN );
$ ObjConditional1-> setCondition ('0 ');
$ ObjConditional1-> getStyle ()-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_RED );
$ ObjConditional1-> getStyle ()-> getFont ()-> setBold (true );
 
// Set autofilter automatic filtering
$ ObjPHPExcel-> getActiveSheet ()-> setAutoFilter ('A1: C9 ');
 
// Hide the column in Hide "Phone" and "fax" column
$ 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-> getActiveSheet ()-> 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 calculation
Echo 'value of B14 [= COUNT (B2: B12)]: '. $ objPHPExcel-> getActiveSheet ()-> getCell ('b14')-> getCalculatedValue (). "\ r \ n ";
 
// Set outline levels
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('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 ()-> setRowsToRepeatAtTopByStartAndEnd (1, 1 );
 
// Set data validation input value
$ ObjValidation = $ objPHPExcel-> getActiveSheet ()-> getCell ('b3')-> getDataValidation ();
$ ObjValidation-> setType (PHPExcel_Cell_DataValidation: TYPE_WHOLE );
$ ObjValidation-> setErrorStyle (PHPExcel_Cell_DataValidation: 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
$ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup: ORIENTATION_LANDSCAPE );
$ ObjPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: 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 Excel 2007 file Save
$ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
$ ObjWriter-> save (str_replace ('. php', '.xlsx', _ FILE __));
 
// Save Excel 5 file Save
Require_once ('classes/PHPExcel/Writer/excel5.php ');
$ ObjWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel );
$ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));
 
// Save the new version 1.6.2
Require_once ('classes/PHPExcel/IOFactory. php ');
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007 ');
$ ObjWriter-> save (str_replace ('. php', '.xls', _ FILE __));

Read excel
[Php]
// Include class
Require_once ('classes/PHPExcel/Reader/excel2007.php ');
$ ObjReader = new PHPExcel_Reader_Excel2007;
$ ObjPHPExcel = $ objReader-> load ("05featuredemo.xlsx ");

Read/write csv
[Php]
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
$ 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
$ 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 PHPExcel_Writer_Excel2007 ($ objPHPExcelFromCSV );
$ Ob1_riter2007-> save (str_replace ('. php', '.xlsx', _ FILE __));

Write html
[Php]
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]
Require_once ("05featuredemo. inc. php ");
Require_once ('classes/PHPExcel/IOFactory. php ');
// Write to PDF format www.2cto.com
$ 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)

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.