thinkphp use Phpexcel to implement Excel data import export complete instances _php tips

The example described in this article is used in the thinkphp development framework, if the same method is used in other frameworks, many people may not be able to correctly implement the import and export of Excel, the problem is basically phpexcel of the core class reference path caused by errors, If you have a problem, be sure to test the Lu Jing reference correctly.

The specific steps are as follows:

(i) Import Excel

First, upload files in the foreground HTML page:

<form method= "post" action= "PHP file" enctype= "Multipart/form-data" >
  
 

Second, in the corresponding PHP file for the processing of files

 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, to determine whether the Excel file * * * * (Strtolower ($file _type)!= "xls") {$this->error (' Not Excel file, re-upload '); /* Set upload path/* $savePath = Site_path.
 '/public/upfile/excel/'; 
  * * With time to name the uploaded file * * * * $str = date (' Ymdhis '); $file _name = $str. "." .
  $file _type;
  /* Upload Success */if (! Copy ($tmp _file, $savePath. $file _name)) {$this->error (' upload failed '); * * * The upload of Excel data processing to generate programming data, this function will be in the next step of the Exceltoarray class in the following note: This call executes the third step inside the read function, the Excel into an array and return to $res, and then write the database * * * $re
 s = Service (' Exceltoarray ')->read ($savePath. $file _name); /* Important code to solve the problem that thinkphp m and D methods cannot call if the M, D method fails in thinkphp, add the following code///spl_autoload_register (Array (' See ', ' Auto
 Load '));
   /* Write the database 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 (' Import Database failed ');

 }
  }
 }
}

Third: Exceltoarrary class, used to reference phpexcel and work with Excel data
Note here: Exceltoarrary class is built under the root directory addons/services/ ExcelToArrary.class.php

 class Exceltoarrary extends service{public function __construct () {/* Import Phpexcel Core class Note: Your path and
 I am not the same can not directly copy/Include_once ('./excel/phpexcel.php ');
   /** * Read Excel $filename path filename $encode The encoding of the returned data defaults to UTF8 * The following basic do not modify the/Public function read ($filename, $encode = ' utf-8 ') { 
   $objReader = 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 + +) {$excel
   data[$row] = (string) $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue (); 
 } return $excelData; }  
 }

Four, the above is the full content of the import, Phpexcel package attached to the last.

(ii) Excel export (much simpler than import)

First, identify the database to generate Excel data, such as:

$data = M (' User ')->findall (); Identify
the data $name = ' excelfile ';//generated Excel file filename
$res =service (' exceltoarrary ')->push ($data, $name);

Second, the Exceltoarrary class is used to refer to the 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 and cannot be reproduced directly/include_o
nCE ('./excel/phpexcel.php ');
 /* Export Excel function */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, and so on * * $objPHPExcel->getproperties ()->setcreator ("Turn the Sun")->setlastmodifiedby ("Turn the Sun")- >settitle (data Excel Export)->setsubject ("Data Excel Export")->setdescription ("Backup Data")->setkeywords ("Excel")-&
 Gt;setcategory ("result file");
  /* The following is the processing of data in Excel, horizontal access to data, mainly this step, other basic do not change the * * foreach ($data as $k => $v) {$num = $k +1; $objPHPExcel->setactivesheetindex (0)//excel column A, the UID is the key value of the array you identified, 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

 }

Third, the above is the entire content of the export, phpexcel this site download address http://www.jb51.net/codes/194070.html

More interested in thinkphp related content readers can view the site topics: "thinkphp Introductory Course", "thinkphp Template Operation Skills Summary", "thinkphp Common Methods Summary", "Smarty Template Introductory Course" and "PHP template technology Summary."

I hope this article will help you with the PHP program design based on thinkphp framework.