Thinkphp using Phpexcel to implement Excel data import Export full instance

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  "  >  " file  "  name= " file_stu  " /> <input Type = "  " value="   import   "/></form> 

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 an Excel file, re-upload' ); }  /*Set 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 was successful*/   if(!copy ($tmp _file, $savePath. $file _name)) {     $ This->error ('Upload failed' ); }  /** Processing the uploaded Excel data to generate programming data, this function will be noted in the Exceltoarray class in 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, and then writes the database .*/$res= Service ('Exceltoarray'),read ($savePath. $file _name); /*important code resolves an issue where the 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 '));  /*writes a 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 ('failed to import database' ); }   }  }}

/services/exceltoarrary. In class. php

classExceltoarrary extends service{ Publicfunction __construct () {/*Import Phpexcel Core class Note: Your path is not the same as mine. cannot be copied 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*/ Publicfunction Read ($filename, $encode ='Utf-8') {$objReader= Phpexcel_iofactory::createreader ('Excel5'); $objReader->setreaddataonly (true); $objPHPExcel= $objReaderload ($filename); $objWorksheet= $objPHPExcelGetactivesheet (); $highestRow= $objWorksheetGethighestrow (); $highestColumn= $objWorksheetGethighestcolumn (); $highestColumnIndex=phpexcel_cell::columnindexfromstring ($highestColumn); $excelData=Array ();  for($row =1; $row <= $highestRow; $row + +) { for($col =0; $col < $highestColumnIndex; $col + +) {$excelData [$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 (more simple than import) first, find out the database to generate Excel data, such as:

$data = M ('User')->findall ();  // Find 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

classExceltoarrary extends service{ Publicfunction __construct () {/*Import Phpexcel Core class Note: Your path is not the same as mine. cannot be copied directly*/include_once ('./excel/phpexcel.php');}/*Export Excel Functions*/ Publicfunction push ($data, $name ='Excel') {error_reporting (E_all); Date_default_timezone_set ('Europe/london'); $objPHPExcel=NewPhpexcel ();/*Here are some of the settings, what the author title Ah and so on*/$objPHPExcel->getproperties ()->setcreator ("the sunshine of the turn")     ->setlastmodifiedby ("the sunshine of the turn")     ->settitle ("Data Excel Export")     ->setsubject ("Data Excel Export")     ->setdescription ("Backing up data")     ->setkeywords ("Excel")    ->setcategory ("result file"); /*The following is the processing of data in Excel, the data is taken sideways, mainly this step, the other basic do not change*/foreach($data as$k =$v) {$num= $k +1; $objPHPExcel->setactivesheetindex (0)  //In column A of Excel, the UID is the key value you find in the array, 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;}

Thinkphp using Phpexcel to implement Excel data import Export full instance