thinkphp Import and export of Excel data (complete case included)

This article mainly introduces thinkphp implementation of Excel data import and export, with a certain reference value, interested in small partners can refer to.

Implementation steps:

A: In http://phpexcel.codeplex.com/download the latest phpexcel put to Vendor, note location: thinkphp\extend\vendor\phpexcel\phpexcel.php.

Two: Export Excel code implementation

/** 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 according to your own circumstances $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 ', ' an ', ' AO ', ' APs ', ' AQ ', ' AR ', ' as ', ' at '        , ' AU ', ' AV ', ' AW ', ' AX ', ' AY ', ' AZ '); $objPHPExcel->getactivesheet (0)->mergecells (' A1: '. $cellName [$cellNum-1]. ' 1 ');//Merge cell//$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 + +) {$objPHPE Xcel->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= '. $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 ', ' faculties '), ARRA Y (' sp_id ', ' professional '), Array (' Class ', ' class '), Array (' Year ', ' Graduation Time '), Array (' City ', ' location '), Array (' Company ', ' unit '), arr Ay (' Zhicheng ', ' title '), Array (' Zhiwu ', ' title '), Array (' Jibie ', ' level '), Array (' tel ', ' phone '), Array (' QQ ', ' QQ '), Array (' email ', ' email '), array (' Honor ', ' honor '), array (' remark ', ' notes ')    );      $xlsModel = M (' Member '); $xlsData = $xlsModel->field (' Id,truename,sex,res_id,sp_id,class,year,city,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 ' =&gt ;'      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 (); Total number of rows obtained $highestColumn = $sheet->gethighestcolumn (); Get total number of columns for ($i =3; $i <= $highestRow; $i + +) {$data [' account ']= $data [' truename '] = $objPHPExcel-&     Gt;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)->getvalue ();          $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->getactivesh()->getcell ("M". $i)->getvalue ();          $data [' QQ ']= $objPHPExcel->getactivesheet ()->getcell ("N". $i)->getvalue ();          $data [' email ']= $objPHPExcel->getactivesheet ()->getcell ("O". $i)->getvalue ();          $data [' Remark ']= $objPHPExcel->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 (' 123456 ');             M (' Member ')->add ($data); } $this->success (' Import succeeded!    ');      }else {$this->error ("Please select the uploaded file"); }         }

Four, template code

Last Download: Demo download