thinkphp Import and export of Excel data using Phpexcel

This article mainly introduces thinkphp using Phpexcel to achieve Excel data import and export, very practical features, the need for friends can refer to the following

The example described in this article is used in the development framework of thinkphp, if used in other frameworks is the same method, many people may not correctly implement the import and export of Excel, the problem is basically phpexcel core class reference path error caused, If there is a problem, be sure to test whether the brutishness reference is correct.

The following are the steps:

(i) Import Excel

First, upload the file in the foreground HTML page: for example:

<form method= "post" action= "PHP file" enctype= "Multipart/form-data" > 
Second, in the corresponding PHP file for file processing

 if (! empty ($_files [' File_stu '] [' name ']) {$tmp _file = $_files [' File_stu '] [' tmp_name ']; $file _types = Explode ( ".", $_files [' File_stu '] [' name ']);  $file _type = $file _types [count ($file _types)-1];  /* discriminant is not an. xls file, discriminant is not an Excel file */if (Strtolower ($file _type)! = "xls") {$this->error (' Not Excel file, re-upload '); }/* Set the upload path */$savePath = Site_path. '/public/upfile/excel/';   /* Name the uploaded file in time */$str = date (' Ymdhis '); $file _name = $str. "." .  $file _type;  /* Whether the upload succeeds */if (copy ($tmp _file, $savePath. $file _name)) {$this->error (' upload failed '); }/* * To process the uploaded Excel data to generate programming data, this function will be noted in the Exceltoarray class of the third step below: This call executes the Read function in the third step class, converts Excel into an array and returns it to $res, then writes the database */$res = Service (' Exceltoarray ')->read ($savePath. $file _name); /* Important code to solve the problem that thinkphp m, D method cannot be called if the M, D method fails in thinkphp, add the following code *///spl_autoload_register (Array (' Think ', ' autoload ' ) );   /* Write database writes to the generated array */foreach ($res as $k + = $v) {if ($k! = 0) {$data [' uid '] = $v [0]; $data [' PasSword '] = SHA1 (' 111111 ');   $data [' email '] = $v [1];   $data [' uname '] = $v [3];   $data [' institute '] = $v [4];   $result = M (' user ')->add ($data);   if (! $result) {$this->error (' failed to import database '); }  } }}
 Third: The Exceltoarrary class, which is used to reference phpexcel and process Excel data 
 Note:  Exceltoarrary class is built under the root directory addons/services/ ExcelToArrary.class.php in  

class exceltoarrary extends service{public function __construct () {/* Import Phpexcel Core class Note: Your path is not the same as I can not copy directly */include_once ('./excel/phpexcel.php '); }/*** read Excel $filename path file name $encode return data encoding defaults to utf8* the following basic do not modify */Public function read ($filename, $encode = ' utf-8 ') {$objR    Eader = Phpexcel_iofactory::createreader (' Excel5 ');    $objReader->setreaddataonly (TRUE);    $objPHPExcel = $objReader->load ($filename); 　　　 $objWorksheet = $objPHPExcel->getactivesheet (); 　　　 $highestRow = $objWorksheet->gethighestrow (); 　　 $highestColumn = $objWorksheet->gethighestcolumn ();  　　 $highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);  　　　$excelData = Array (); for ($row = 1; $row <= $highestRow; $row + +) {for ($col = 0; $col < $highestColumnIndex; $col + +) {$excelDa   ta[$row] [] = (string) $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ();  }} return $excelData; }   }
The above is the whole content of the import, the Phpexcel package is attached to the last.

(b) Excel export (much simpler than import)

First, find out the data in the database to generate Excel, such as:

$data = M (' User ')->findall (); Identify data $name= ' excelfile '; The generated Excel file filename $res=service (' exceltoarrary ')->push ($data, $name);
Second, the Exceltoarrary class, used to refer to phpexcel and process the data

Class Exceltoarrary extends Service{public function __construct () {/* Import Phpexcel Core class Note: Your path is not the same as mine. */Include_once ( './excel/phpexcel.php ');} /* Export Excel functions */public function push ($data, $name = ' Excel ') {error_reporting (E_all); Date_default_timezone_set (' europe/ London '); $objPHPExcel = new Phpexcel ();/* The following are some settings, what author title Ah or something like * * $objPHPExcel->getproperties ()->setcreator ("Sunshine of the Turn") Setlastmodifiedby ("Turn Sunshine")->settitle ("Data Excel Export")->setsubject ("Data Excel Export")->setdescription ("Backup Data")-&G T;setkeywords ("Excel")->setcategory ("Result file");  /* The following is the processing of data in Excel, the data is horizontal, mainly this step, the other basic do not change */foreach ($data as $k and $v) {$num = $k +1; $objPHPExcel->setactivesheetindex (0)//excel column A, the UID is the key value of the array you have isolated, and so on->setcellvalue (' A '. $num, $v [' uid '])- >setcellvalue (' B '. $num, $v [' email '])->setcellvalue (' C ' $num, $v [' Password ']} $objPHPExcel Getactivesheet ()->settitle (' User ');  $objPHPExcel->setactivesheetindex (0); Header (' Content-type:application/vnd.ms-excel'); Header (' Content-disposition:attachment;filename= '. $name. '.  XLS "');  Header (' cache-control:max-age=0 ');  $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');  $objWriter->save (' php://output '); Exit;}
 The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!