Thinkphp implements the import and export of excel data (with a complete case), thinkphpexcel

Thinkphp implements the import and export of excel data (with a complete case), thinkphpexcel

Steps:

I.

Ii. excel Code Export

/** Method **/function index () {$ this-> display ();} public function exportExcel ($ expTitle, $ expCellName, $ expTableData) {$ xlsTitle = iconv ('utf-8', 'gb2312', $ expTitle); // file name $ fileName = $ _ SESSION ['account']. 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', 'AA', 'AB', 'ac', 'ad', 'AE', 'af', 'ag ', 'Ah', 'ai', 'aj ', 'ak', 'al', 'am', 'A', '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 = "'.w.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;}/***** export Excel */function expUser () {// export Excel $ xlsName = "User "; $ xlsCell = array ('id', 'account sequence'), array ('truename', 'name'), array ('sex', 'Gender '), Array ('res _ id', 'faculty'), array ('SP _ id', 'professional'), array ('class', 'class '), array ('Year', 'Graduation Time'), array ('city', 'location'), array ('company', 'unit '), array ('zhicheng ', 'title '), array ('zhiw', 'title'), array ('jibie', 'level'), array ('tel', 'tel '), array ('qq', 'qq'), array ('email ', 'mailbox'), array ('honor', 'honor '), array ('remark ', 'note'); $ xlsModel = M ('member'); $ xlsData = $ xlsModel-> Field ('Id, truename, sex, res_id, sp_id, class, year, cit Y, company, zhicheng, zhiwu, jibie, tel, qq, email, honor, remark')-> select (); foreach ($ xlsData as $ k => $ v) {$ xlsData [$ k] ['sex'] = $ v ['sex'] = 1? 'Male': 'female ';} $ this-> exportExcel ($ xlsName, $ xlsCell, $ xlsData );}

Third: Import excel Data Code

Function impUser () {if (! Empty ($ _ FILES) {import ("@. ORG. uploadFile "); $ config = array ('allowexts '=> array ('xlsx', 'xls'), 'savepath' => '. /Public/upload/', 'saverule' => 'time',); $ upload = new UploadFile ($ config); if (! $ Upload-> upload () {$ this-> error ($ upload-> getErrorMsg ();} else {$ info = $ upload-> getUploadFileInfo ();} vendor ("PHPExcel. PHPExcel "); $ file_name = $ info [0] ['savepath']. $ info [0] ['savename']; $ objReader = PHPExcel_IOFactory: createReader ('excel5'); $ objPHPExcel = $ objReader-> load ($ file_name, $ encode = 'utf-8'); $ sheet = $ objPHPExcel-> getSheet (0); $ highestRow = $ sheet-> getHighestRow (); // get the total number of rows $ highest Column = $ sheet-> getHighestColumn (); // obtain the total number of columns for ($ I = 3; $ I <= $ highestRow; $ I ++) {$ data ['account'] = $ data ['truename'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("B ". $ I)-> getValue (); $ sex = $ objPHPExcel-> getActiveSheet ()-> getCell ("C ". $ I)-> getValue (); // $ data ['res _ id'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("D ". $ I)-> getValue (); $ data ['class'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("E ". $ I)-> getVal Ue (); $ data ['Year'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("F ". $ I)-> getValue (); $ data ['city'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("G ". $ I)-> getValue (); $ data ['company'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("H ". $ I)-> getValue (); $ data ['zhicheng'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("I ". $ I)-> getValue (); $ data ['zhiwu'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("J ". $ I)-> getValue (); $ data ['Jibie'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("K ". $ I)-> getValue (); $ data ['honor'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("L ". $ I)-> getValue (); $ data ['tel'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("M ". $ I)-> getValue (); $ data ['qq'] = $ objPHPExcel-> getActiveSheet ()-> getCell ("N ". $ I)-> getValue (); $ data ['email '] = $ objPHPExcel-> getActiveSheet ()-> getCell ("O ". $ I)-> getValue (); $ data ['remark'] = $ objPHPE Xcel-> getActiveSheet ()-> getCell ("P". $ I)-> getValue (); $ data ['sex'] = $ sex = 'male '? 1:0; $ data ['res _ id'] = 1; $ data ['last _ login_time '] = 0; $ data ['create _ time'] = $ data ['last _ login_ip '] = $ _ SERVER ['remote _ ADDR']; $ data ['login _ count'] = 0; $ data ['join'] = 0; $ data ['Avatar '] = ''; $ data ['Password'] = md5 ('20140901'); M ('Member ')-> add ($ data);} $ this-> success ('import successful! ');} Else {$ this-> error ("select the uploaded file ");}}

Iv. template code

<Html> 
Last download: demo download

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.