PHPExcel-related operations and phpexcel-related operations

PHPExcel-related operations and phpexcel-related operations

The following are 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 ″);
Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 ″);
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 ');
---------------------------------------
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', 'string ');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('a2 ', 12 );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('a3 ', true );
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('c5 ',' = SUM (C2: C4 )');
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b8', '= MIN (B2: C5 )');
Merge Cells
$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22 ');
Separate Cells
$ ObjPHPExcel-> getActiveSheet ()-> unmergeCells ('a28: b28 ');

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', 'phpexcel ');
Set format
// Set cell number formats
Echo date ('H: I: s'). "Set cell number formats \ n ";
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e4 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE );
$ ObjPHPExcel-> getActiveSheet ()-> duplicateStyle ($ objPHPExcel-> getActiveSheet ()-> getStyle ('e4 '), 'e5: e13 ');
Set width
// Set column widths
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('B')-> setAutoSize (true );
$ ObjPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setWidth (12 );
Set 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 );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e1 ')-> getFont ()-> getColor ()-> setARGB (PHPExcel_Style_Color: COLOR_WHITE );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getFont ()-> setBold (true );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getFont ()-> setBold (true );
Set align
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d11')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d12')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d13')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_JUSTIFY );
// Vertical center
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );
Set the border of column
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('c4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('d4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e4 ')-> getBorders ()-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
Set 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 ()-> getTop ()-> getColor ()-> setARGB ('ff993300 ');
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getBorders ()-> getBottom ()-> getColor ()-> setARGB ('ff993300 ');
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('e13')-> getBorders ()-> getRight ()-> getColor ()-> setARGB ('ff993300 ');
Set fill color
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1')-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('a1')-> getFill ()-> getStartColor ()-> setARGB ('ff808080 ');
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFill ()-> getStartColor ()-> setARGB ('ff808080 ');
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 ');
$ 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', 'utf-8', $ str );
Alternatively, you can write a function to process Chinese strings:
Function convertUTF8 ($ str)
{
If (empty ($ str) return '';
Return iconv ('gb2312', 'utf-8', $ 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 object \ n ";
$ ObjPHPExcel-> createSheet ();
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objExcel, 'excel5 ');
$ ObjWriter-save ('php: // output ');