PHP Excel operations-PHPExcel basic usage details

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)