Common Instructions on using PHPExcel and integration of PHPExcel into the CI framework

This article provides a detailed description of PHPExcel and the methods for integrating PHPExcel into the CI framework. For more information, see

This article provides a detailed description of PHPExcel and the methods for integrating PHPExcel into the CI framework. For more information, see

Write and generate excel files:

The Code is as follows:

Include 'phpexcel. php ';
Include 'phpexcel/Writer/excel2007.php ';
// Or include 'phpexcel/Writer/excel5.php'; for output. xls
Include 'phpexcel/IOFactory. php'; // PHPExcel factory class
// Create an excel file
$ ObjPHPExcel = new PHPExcel ();
// Save the excel-2007 format
$ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel );
// You can also use
// $ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, "Excel2007 ");
// 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 ');
// Directly generate a file
$ Ob1_riterr-> save ('filename ');
// Set excel attributes:
// Creator
$ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw ");
// Last Modifier
$ 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 .");
// Keyword
$ ObjPHPExcel-> getProperties ()-> setKeywords ("office 2007 openxml php ");
// Type
$ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file ");
// Set the current sheet
$ ObjPHPExcel-> setActiveSheetIndex (0 );
// Set the 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 ');
// Protect the cell
$ 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 the 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 the 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 an image
$ 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 string needs to be converted to UTF-8 encoding, In order to normal output, Hong Kong Space, Hong Kong server lease, Otherwise Chinese characters will be output as blank, the following processing:
$ 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 );
}

Read excel
1. the simplest way to import an Excel file is to use PHPExel's IO Factory and call the static load method of the PHPExcel_IOFactory class. The Hong Kong server can automatically identify the document format, including Excel2007, Excel2003XML, OOCalcSYLK, Gnumeric, and CSV. Returns an instance of PHPExcel.

The Code is as follows: