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 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, otherwise the Chinese character 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. it can automatically identify the document format, including Excel2007, Excel2003XML, OOCalcSYLK, Gnumeric, and CSV. Returns an instance of PHPExcel.

The code is as follows:

// Load the factory class
Include 'phpexcel/IOFactory. php ';
// Path of the xls file to be read
$ InputFileName = './sampleData/example1.xls ';
/** Use the load method of PHPExcel_IOFactory to obtain the excel operation object **/
$ ObjPHPExcel = PHPExcel_IOFactory: load ($ inputFileName );
// Obtain the current active table and call the toArray method to obtain the two-dimensional array of the table.
$ SheetData = $ objPHPExcel-> getActiveSheet ()-> toArray (null, true );
Var_dump ($ sheetData );

1. create an ExcelReader to load an Excel file
If you know the format of this Excel document, you can create a Reader to load the Excel document to be read. However, if you load an incorrect document type, unexpected errors may occur.

The code is as follows:

$ InputFileName = './sampleData/example1.xls ';
/** Create a new Excel5 Reader **/
$ ObjReader = new PHPExcel_Reader_Excel5 ();
// $ ObjReader = new PHPExcel_Reader_Excel2007 ();
// $ ObjReader = new PHPExcel_Reader_Excel2003XML ();
// $ ObjReader = new PHPExcel_Reader_OOCalc ();
// $ ObjReader = new PHPExcel_Reader_SYLK ();
// $ ObjReader = new PHPExcel_Reader_Gnumeric ();
// $ ObjReader = new PHPExcel_Reader_CSV ();
/** Load $ inputFileName to a PHPExcel Object **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );
// Get the current active sheet
$ CurSheet = $ objPHPExcel-> getActiveSheet ();
// Return the data of the table in the form of a two-dimensional array
$ SheetData = $ curSheet-> toArray (null, true );
Var_dump ($ sheetData );

You can also use the createReader method of PHPExcel_IOFactory to obtain a Reader object without knowing the format of the file to be read.

The code is as follows:

$ InputFileType = 'excel5 ';
// $ InputFileType = 'excel2007 ';
// $ InputFileType = 'excel2003xml ';
// $ InputFileType = 'oocalc ';
// $ InputFileType = 'syk ';
// $ InputFileType = 'gnumeric ';
// $ InputFileType = 'csv ';
$ InputFileName = './sampleData/example1.xls ';
/** Create a new Reader of the type defined in $ inputFileType **/
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
/** Load $ inputFileName to a PHPExcel Object **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );
// Get the current active sheet
$ CurSheet = $ objPHPExcel-> getActiveSheet ();
// Return the data of the table in the form of a two-dimensional array
$ SheetData = $ curSheet-> toArray (null, true );
Var_dump ($ sheetData );

If the file format is unknown before reading the file, you can use the identify () method of IOFactory to obtain the file type, and then use the createReader () method to wear the reader.

The code is as follows:

$ InputFileName = './sampleData/example1.xls ';
/** Determine the format of the input file **/
$ InputFileType = PHPExcel_IOFactory: identify ($ inputFileName );
/** Wear reader **/
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
/** Load the file to be read **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );

2. set the Excel reading option
Before using the load () method to load a file, you can set the read option to control the load behavior.
2.1.ReadingOnly Data from a Spreadsheet File
SetReadDataOnly () method. when configuring the reader to ignore the data type of table data, all data types are returned in string format.

The code is as follows:

$ InputFileType = 'excel5 ';
$ InputFileName = './sampleData/example1.xls ';
/** Create a new Reader of the type defined in $ inputFileType **/
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
/** Configure cell data to be returned as a string **/
$ ObjReader-> setReadDataOnly (true );
/** Load $ inputFileName to a PHPExcel Object **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );
$ SheetData = $ objPHPExcel-> getActiveSheet ()-> toArray (null, true );
Var_dump ($ sheetData );

Returned data:

The code is as follows:

Array (8 ){
[1] =>
Array (6 ){
["A"] =>
String (15) "Integer Numbers"
["B"] =>
String (3) 123"
["C"] =>
String (3) 234"
["D"] =>
String (4) "-345"
["E"] =>
String (3) 456"
["F"] =>
NULL
}
[2] =>
Array (6 ){
["A"] =>
String (22) "Floating PointNumbers"
["B"] =>
String (4) 1.23"
["C"] =>
String (5) "23.45"
["D"] =>
String (10) "maid + 0.00"
["E"] =>
String (6) "-45.68"
["F"] =>
String (7) "£56. 78"
}
[3] =>
Array (6 ){
["A"] =>
String (7) "Strings"
["B"] =>
String (5) "Hello"
["C"] =>
String (5) "World"
["D"] =>
NULL
["E"] =>
String (8) "PHPExcel"
["F"] =>
NULL
}
[4] =>
Array (6 ){
["A"] =>
String (8) "Booleans"
["B"] =>
Bool (true)
["C"] =>
Bool (false)
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[5] =>
Array (6 ){
["A"] =>
String (5) "Dates"
["B"] =>
String (16) "19 December 1960"
["C"] =>
String (15) "10 October 2010"
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[6] =>
Array (6 ){
["A"] =>
String (5) "Times"
["B"] =>
String (4) "9: 30"
["C"] =>
String (5) "23:59"
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[7] =>
Array (6 ){
["A"] =>
String (8) "Formulae"
["B"] =>
String (3) 468"
["C"] =>
String (7) "-20.998"
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[8] =>
Array (6 ){
["A"] =>
String (6) "Errors"
["B"] =>
String (4) "# N/"
["C"] =>
String (7) "# DIV/0! "
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
}
If this parameter is not set, return:
Array (8 ){
[1] =>
Array (6 ){
["A"] =>
String (15) "Integer Numbers"
["B"] =>
Float (123)
["C"] =>
Float (234)
["D"] =>
Float (-1, 345)
["E"] =>
Float (456)
["F"] =>
NULL
}
[2] =>
Array (6 ){
["A"] =>
String (22) "Floating Point Numbers"
["B"] =>
Float (1.23)
["C"] =>
Float (23.45)
["D"] =>
Float (3.45E-6)
["E"] =>
Float (-1, 45.678)
["F"] =>
Float (56.78)
}
[3] =>
Array (6 ){
["A"] =>
String (7) "Strings"
["B"] =>
String (5) "Hello"
["C"] =>
String (5) "World"
["D"] =>
NULL
["E"] =>
String (8) "PHPExcel"
["F"] =>
NULL
}
[4] =>
Array (6 ){
["A"] =>
String (8) "Booleans"
["B"] =>
Bool (true)
["C"] =>
Bool (false)
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[5] =>
Array (6 ){
["A"] =>
String (5) "Dates"
["B"] =>
Float (22269)
["C"] =>
Float (40461)
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[6] =>
Array (6 ){
["A"] =>
String (5) "Times"
["B"] =>
Float (0.39583333333333)
["C"] =>
Float (0.99930555555556)
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[7] =>
Array (6 ){
["A"] =>
String (8) "Formulae"
["B"] =>
Float (468)
["C"] =>
Float (-1, 20.99799655)
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
[8] =>
Array (6 ){
["A"] =>
String (6) "Errors"
["B"] =>
String (4) "# N/"
["C"] =>
String (7) "# DIV/0! "
["D"] =>
NULL
["E"] =>
NULL
["F"] =>
NULL
}
}

Reading Only Data from a SpreadsheetFile applies to Readers:
Excel2007 YES Excel5 YES Excel2003XML YES
OOCalc yes sylk no Gnumeric YES
CSV NO

2.2.ReadingOnly Named WorkSheets from a File
SetLoadSheetsOnly (), set the worksheet to be read, and accept the name of the worksheet as the parameter.

The code is as follows:

/** PHPExcel_IOFactory */
Include 'phpexcel/IOFactory. php ';
$ InputFileType = 'excel5 ';
// $ InputFileType = 'excel2007 ';
// $ InputFileType = 'excel2003xml ';
// $ InputFileType = 'oocalc ';
// $ InputFileType = 'gnumeric ';
$ InputFileName = './sampleData/example1.xls ';
$ Sheetname = 'data Sheet #2 ';

Echo 'loading file', pathinfo ($ inputFileName, PATHINFO_BASENAME), 'using IOFactory with a definedreader type of ', $ inputFileType ,'
';
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
Echo 'loading Sheet "', $ sheetname,'" only
';
$ ObjReader-> setLoadSheetsOnly ($ sheetname );
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );
Echo'

';
Echo $ objPHPExcel-> getSheetCount (), 'worksheet ', ($ objPHPExcel-> getSheetCount () = 1 )? '': 'S'), 'loaded

';
$ LoadedSheetNames = $ objPHPExcel-> getSheetNames ();
Foreach ($ loadedSheetNames as $ sheetIndex => $ loadedSheetName ){
Echo $ sheetIndex, '->', $ loadedSheetName ,'
';
}

If you want to read multiple worksheet, you can pass an array

The code is as follows:

$ InputFileType = 'excel5 ';
$ InputFileName = './sampleData/example1.xls ';
$ Sheetnames = array ('data Sheet # 1', 'data Sheet #3 ');
/** Create a new Reader of the type defined in $ inputFileType **/
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
/** Advise the Reader of which WorkSheets we want to load **/
$ ObjReader-> setLoadSheetsOnly ($ sheetnames );
/** Load $ inputFileName to a PHPExcel Object **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );

To read all worksheets, call setLoadAllSheets ().