PHP class library phpExcel Help Manual Guide for Excel operations

Official website: http://phpexcel.codeplex.com/

The following describes the usage methods,

Include 'phpexcel. Php ';
Include 'phpexcel/Writer/excel2007.php ';
// Or include 'phpexcel/Writer/excel5.php'; for output. Xls
Create an excel file
$ ObjPHPExcel = new PHPExcel ();

Save the excel-2007 format
$ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
// Or $ objWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel); non-2007 format
$ ObjWriter-> save(“xxx.xlsx ");
Output directly to the browser
$ ObjWriter = new PHPExcel_Writer_Excel5 ($ objPHPExcel );
Header ("Pragma: public ");
Header ("Expires: 0 & Prime ;);
Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 & Prime ;);
Header ("Content-Type: application/force-download ");
Header ("Content-Type: application/vnd. ms-execl ");
Header ("Content-Type: application/octet-stream ");
Header ("Content-Type: application/download ");;
Header ('content-Disposition: attachment?filename=#resume.xls "');
Header ("Content-Transfer-Encoding: binary ");
$ ObjWriter-> save ('php: // output ');

---------------------------------------

PhpExcel Chinese manual
Set excel attributes:
Created
$ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw ");
Last modified
$ ObjPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw ");
Title
$ ObjPHPExcel-> getProperties ()-> setTitle ("Office 2007 XLSX Test Document ");
Question
$ ObjPHPExcel-> getProperties ()-> setSubject ("Office 2007 XLSX Test Document ");
Description
$ ObjPHPExcel-> getProperties ()-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes .");
Keywords
$ ObjPHPExcel-> getProperties ()-> setKeywords ("office 2007 openxml php ");
Type
$ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file ");
---------------------------------------
Set the current sheet
$ ObjPHPExcel-> setActiveSheetIndex (0 );

Set sheet name
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('simple ');

Set the cell value
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A1 & prime;, 'string ');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A2 & prime;, 12 );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A3 & prime;, true );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('c5 & prime;, '= SUM (C2: C4 )');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b8 & prime;, '= MIN (B2: C5 )');

Merge cells
$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: E22 & prime ;);

Separate cells
$ ObjPHPExcel-> getActiveSheet ()-> unmergeCells ('a28: B28 & prime ;);

Cell Protection
$ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSheet (true); // Needs to be set to true in order to enable any worksheet protection!
$ ObjPHPExcel-> getActiveSheet ()-> protectCells ('A3: E13 & prime;, 'phpexcel ');

Set format
// Set cell number formats
Echo date ('H: I: s'). "Set cell number formatsn ";
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e4 & prime;)-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE );
$ ObjPHPExcel-> getActiveSheet ()-> duplicateStyle ($ objPHPExcel-> getActiveSheet ()-> getStyle ('e4 & prime;), 'e5: E13 & prime ;);

Set width
// Set column widths
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (12 );

Set font
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFont ()-> setName ('candara ');
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFont ()-> setSize (20 );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFont ()-> setBold (true );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFont ()-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_WHITE );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e1 & prime;)-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_WHITE );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13 & prime;)-> getFont ()-> setBold (true );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13 & prime;)-> getFont ()-> setBold (true );

Set align
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d11 & prime;)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('D12 & prime;)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13 & prime;)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18 & prime;)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_JUSTIFY );
// Vertical center
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18 & prime;)-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );

Set the border of column
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A4 & prime;)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('B4 & prime;)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('C4 & prime;)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d4 & prime;)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e4 & prime;)-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

Set border color
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13 & prime;)-> getBorders ()-> getLeft ()-> getColor () -> setARGB ('ff993300 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13 & prime;)-> getBorders ()-> getTop ()-> getColor () -> setARGB ('ff993300 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13 & prime;)-> getBorders ()-> getBottom ()-> getColor () -> setARGB ('ff993300 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13 & prime;)-> getBorders ()-> getTop ()-> getColor () -> setARGB ('ff993300 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13 & prime;)-> getBorders ()-> getBottom ()-> getColor () -> setARGB ('ff993300 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13 & prime;)-> getBorders ()-> getRight ()-> getColor () -> setARGB ('ff993300 & prime ;);

Set fill color
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A1 & prime;)-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('A1 & prime;)-> getFill ()-> getStartColor ()-> setARGB ('ff808080 & prime ;);
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 & prime;)-> getFill ()-> getStartColor ()-> setARGB ('ff808080 & prime ;);

Add images
$ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
$ ObjDrawing-> setName ('logo ');
$ ObjDrawing-> setDescription ('logo ');
$ ObjDrawing-> setPath ('./images/officelogo.jpg ');
$ ObjDrawing-> setHeight (36 );
$ ObjDrawing-> setWorksheet ($ objPHPExcel-> getActiveSheet ());

$ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
$ ObjDrawing-> setName ('paid ');
$ ObjDrawing-> setDescription ('paid ');
$ ObjDrawing-> setPath ('./images/paid.png ');
$ ObjDrawing-> setCoordinates ('b15 & prime ;);
$ ObjDrawing-> setOffsetX (110 );
$ ObjDrawing-> setRotation (25 );
$ ObjDrawing-> getShadow ()-> setVisible (true );
$ ObjDrawing-> getShadow ()-> setDirection (45 );
$ ObjDrawing-> setWorksheet ($ objPHPExcel-> getActiveSheet ());

// Process Chinese output

The character string needs to be converted to UTF-8 encoding before normal output, otherwise the Chinese character will be output as blank, as follows:

$ Str = iconv ('gb2312 & prime;, 'utf-8 & prime;, $ str );

Alternatively, you can write a function to process Chinese strings:

Function convertUTF8 ($ str)
{
If (empty ($ str) return ";
Return iconv ('gb2312 & prime;, 'utf-8 & prime;, $ str );
}

// Data processing method output from the database

Reading data from a database is as follows:

$ Db = new Mysql ($ dbconfig );
$ SQL = "SELECT * FROM table name ";
$ Row = $ db-> GetAll ($ SQL); // $ row is a two-dimensional array.

$ Count = count ($ row );
For ($ I = 2; $ I <= $ count + 1; $ I ++ ){

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A'. $ I, convertUTF8 ($ row [$ i-2] [1]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('B'. $ I, convertUTF8 ($ row [$ i-2] [2]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('C'. $ I, convertUTF8 ($ row [$ i-2] [3]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('D'. $ I, convertUTF8 ($ row [$ i-2] [4]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('E '. $ I, convertUTF8 (date ("Y-m-d", $ row [$ i-2] [5]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('F'. $ I, convertUTF8 ($ row [$ i-2] [6]);

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('g'. $ I, convertUTF8 ($ row [$ i-2] [7]);
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('H'. $ I, convertUTF8 ($ row [$ i-2] [8]);

}

After the default sheet, create a worksheet
Echo date ('H: I: s'). "Create new Worksheet objectn ";
$ ObjPHPExcel-> createSheet ();

$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objExcel, 'excel5 & prime ;);
$ ObjWriter-save ('php: // output ');