ThinkPHP + PHPExcel [import] [Export] Implementation Method

Provides various official and user-released code examples. For code reference, you are welcome to exchange and learn the following is my own implementation method, there may be many shortcomings, you are welcome to propose improvements...
Steps:
I. Go to the official website.
2. Add the following two functions to CommonAction. class. php: /**
+ ----------------------------------------------------------
* Export Excel | 2013.08.23
* Author: HongPing
+ ----------------------------------------------------------
* @ Param $ expTitle string File name
+ ----------------------------------------------------------
* @ Param $ expCellName array Column name
+ ----------------------------------------------------------
* @ Param $ expTableData array Table data
+ ----------------------------------------------------------
*/
Public function exportExcel ($ expTitle, $ expCellName, $ expTableData ){
$ XlsTitle = iconv ('utf-8', 'gb2312', $ expTitle); // file name
$ FileName = $ _ SESSION ['loginaccount']. date ('_ YmdHis'); // or $ xlsTitle file name can be set as needed
$ CellNum = count ($ expCellName );
$ DataNum = count ($ expTableData );
Vendor ("PHPExcel. PHPExcel ");
$ ObjPHPExcel = new PHPExcel ();
$ CellName = array ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'h ', 'I', 'J', 'k', 'l', 'M', 'n', 'O', 'P', 'Q', 'R ', 'S ', 't', 'U', 'V', 'w', 'x', 'y', 'z', 'A',' AB ', 'ac', 'ad', 'AE', 'af', 'ag', 'Ah', 'ai', 'aj ', 'ak', 'al ', 'am', 'any', 'ao', 'ap ', 'aq', 'ar ', 'as', 'at', 'au', 'av ', 'aw', 'ax ', 'ay', 'az ');

$ ObjPHPExcel-> getActiveSheet (0)-> mergeCells ('a1: '. $ cellName [$ cellNum-1]. '1'); // merge Cells
$ ObjPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('a1', $ expTitle. 'export time :'. date ('Y-m-d H: I: s '));
For ($ I = 0; $ I <$ cellNum; $ I ++ ){
$ ObjPHPExcel-> setActiveSheetIndex (0)-> setCellValue ($ cellName [$ I]. '2', $ expCellName [$ I] [1]);
}
// Miscellaneous glyphs, UTF-8
For ($ I = 0; $ I <$ dataNum; $ I ++ ){
For ($ j = 0; $ j <$ cellNum; $ j ++ ){
$ ObjPHPExcel-> getActiveSheet (0)-> setCellValue ($ cellName [$ j]. ($ I + 3), $ expTableData [$ I] [$ expCellName [$ j] [0]);
}
}

Header ('pragma: public ');
Header ('content-type: application/vnd. ms-excel; charset = UTF-8; name = "'.20.xlstitle.'.xls "');
Header ("Content-Disposition: attachment?filename==filename.xls"); // attachment new window print inline this window print
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save ('php: // output ');
Exit;
}

/**
+ ----------------------------------------------------------
* Import Excel | 2013.08.23
* Author: HongPing
+ ----------------------------------------------------------
* @ Param $ file upload file $ _ FILES
+ ----------------------------------------------------------
* @ Return array ("error", "message ")
+ ----------------------------------------------------------
*/
Public function importExecl ($ file ){
If (! File_exists ($ file )){
Return array ("error" => 0, 'message' => 'file not found! ');
}
Vendor ("PHPExcel. PHPExcel. IOFactory ");
$ ObjReader = PHPExcel_IOFactory: createReader ('excel5 ');
Try {
$ PHPReader = $ objReader-> load ($ file );
} Catch (Exception $ e ){}
If (! Isset ($ PHPReader) return array ("error" => 0, 'message' => 'read error! ');
$ AllWorksheets = $ PHPReader-> getAllSheets ();
$ I = 0;
Foreach ($ allWorksheets as $ objWorksheet ){
$ Sheetname = $ objWorksheet-> getTitle ();
$ AllRow = $ objWorksheet-> getHighestRow (); // how many rows
$ HighestColumn = $ objWorksheet-> getHighestColumn (); // how many columns
$ AllColumn = PHPExcel_Cell: columnIndexFromString ($ highestColumn );
$ Array [$ I] ["Title"] = $ sheetname;
$ Array [$ I] ["Cols"] = $ allColumn;
$ Array [$ I] ["Rows"] = $ allRow;
$ Arr = array ();
$ IsMergeCell = array ();
Foreach ($ objWorksheet-> getMergeCells () as $ cells) {// merge cells
Foreach (PHPExcel_Cell: extractAllCellReferencesInRange ($ cells) as $ cellReference ){
$ IsMergeCell [$ cellReference] = true;
}
}
For ($ currentRow = 1; $ currentRow <= $ allRow; $ currentRow ++ ){
$ Row = array ();
For ($ currentColumn = 0; $ currentColumn <$ allColumn; $ currentColumn ++ ){;
$ Cell = $ objWorksheet-> getCellByColumnAndRow ($ currentColumn, $ currentRow );
$ AfCol = PHPExcel_Cell: stringFromColumnIndex ($ currentColumn + 1 );
$ BfCol = PHPExcel_Cell: stringFromColumnIndex ($ currentColumn-1 );
$ Col = PHPExcel_Cell: stringFromColumnIndex ($ currentColumn );
$ Address = $ col. $ currentRow;
$ Value = $ objWorksheet-> getCell ($ address)-> getValue ();
If (substr ($ value, 0, 1) = '){
Return array ("error" => 0, 'message' => 'can not use the formula! ');
Exit;
}
If ($ cell-> getDataType () = PHPExcel_Cell_DataType: TYPE_NUMERIC ){
$ Cellstyleformat = $ cell-> getParent ()-> getStyle ($ cell-> getCoordinate ()-> getNumberFormat ();
$ Formatcode = $ cellstyleformat-> getFormatCode ();
If (preg_match ('/^ ([$ [A-Z] *-[0-9A-F] *]) * [hmsdy]/I', $ formatcode )){
$ Value = gmdate ("Y-m-d", PHPExcel_Shared_Date: ExcelToPHP ($ value ));
} Else {
$ Value = PHPExcel_Style_NumberFormat: toFormattedString ($ value, $ formatcode );
}
}
If ($ isMergeCell [$ col. $ currentRow] & $ isMergeCell [$ afCol. $ currentRow] &! Empty ($ value )){
$ Temp = $ value;
} Elseif ($ isMergeCell [$ col. $ currentRow] & $ isMergeCell [$ col. ($ currentRow-1)] & empty ($ value )){
$ Value = $ arr [$ currentRow-1] [$ currentColumn];
} Elseif ($ isMergeCell [$ col. $ currentRow] & $ isMergeCell [$ bfCol. $ currentRow] & empty ($ value )){
$ Value = $ temp;
}
$ Row [$ currentColumn] = $ value;
}
$ Arr [$ currentRow] = $ row;
}
$ Array [$ I] ["Content"] = $ arr;
$ I ++;
}
Spl_autoload_register (array ('think', 'autoload'); // must, resolve ThinkPHP and PHPExcel conflicts
Unset ($ objWorksheet );
Unset ($ PHPReader );
Unset ($ PHPExcel );
Unlink ($ file );
Return array ("error" => 1, "data" => $ array );
} Usage
Import: Function impUser (){
If (isset ($ _ FILES ["import"]) & ($ _ FILES ["import"] ["error"] = 0 )){
$ Result = $ this-> importExecl ($ _ FILES ["import"] ["tmp_name"]);
If ($ result ["error"] = 1 ){
$ Execl_data = $ result ["data"] [0] ["Content"];
Foreach ($ execl_data as $ k => $ v ){
... Write your business code here ..
}
}
}
}Export: Function expUser () {// export Excel
$ XlsName = "User ";
$ XlsCell = array (
Array ('id', 'account sequence '),
Array ('account', 'logon account '),
Array ('nickname', 'account name ')
);
$ XlsModel = M ('post ');
$ XlsData = $ xlsModel-> Field ('Id, account, nickname')-> select ();
$ This-> exportExcel ($ xlsName, $ xlsCell, $ xlsData );
}

AD: truly free, domain name + VM + enterprise mailbox = 0 RMB