ThinkPHP uses PHPExcel to import and export Excel data to a complete instance

This article mainly introduces ThinkPHP's use of PHPExcel to import and export Excel data. it is a very practical function. For more information, see PHPExcelThinkPHP.

The examples described in this article are used in the Thinkphp development framework. if the same method is used in other frameworks, can many people correctly import and export Excel files, the problem is basically caused by errors in the reference path of the phpExcel core class. if there is a problem, you must check whether the reference of the path is correct.

The procedure is as follows:

(1) import Excel

First, upload files on the front-end html page: for example:

Second, process the corresponding php file

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];/* it is not a. xls file, determine if it is an excel file */if (strtolower ($ file_type )! = "Xls") {$ this-> error ('not an Excel file, re-upload');}/* Set the upload path */$ savePath = SITE_PATH. '/public/upfile/Excel/';/* name the uploaded file by time */$ str = date ('ymdhis '); $ file_name = $ str. ". ". $ file_type;/* indicates whether the upload is successful */if (! Copy ($ tmp_file, $ savePath. $ file_name) {$ this-> error ('upload failed');}/** process uploaded Excel data to generate programming data, this function will be noted in the ExcelToArray class in step 3 below: Here, the read function in step 3 is called and executed to convert Excel into an array and return it to $ res, then write the database */$ res = Service ('exceltoarray')-> read ($ savePath. $ file_name ); /* important code solves the problem that Thinkphp M and D methods cannot be called. if M and D methods fail in thinkphp, add the following code * // spl_autoload_register (array (' think ', 'autoload');/* write the database to the generated array */foreach ($ res as $ k =>$ V) {if ($ k! = 0) {$ data ['uid'] = $ v [0]; $ data ['password'] = sha1 ('123 '); $ data ['email '] = $ v [1]; $ data ['uname'] = $ v [3]; $ data ['center'] = $ v [4]; $ result = M ('user')-> add ($ data); if (! $ Result) {$ this-> error ('database import failed ');}}}}

Third: ExcelToArrary class, used to reference phpExcel and process Excel data
Note:The ExcelToArrary class is created in addons/services/ExcelToArrary. class. php under the root directory.

Class ExcelToArrary extends Service {public function _ construct () {/* import phpExcel core class note: If your path is different from mine, you cannot directly copy */include_once ('. /Excel/PHPExcel. php ');}/*** read excel $ filename path file name $ encode returned data encoding: utf8 by default * Do not modify the following basics */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 ++) {$ excelData [$ row] [] = (string) $ objWorksheet-> getCellByColumnAndRow ($ col, $ row)-> getValue () ;}} return $ excelData ;}}

Fourth, the above is all the imported content, and the phpExcel package is appended to the end.

(2) exporting Excel (easier than importing)

First, find the data in the database that needs to generate Excel, such:

$ Data = M ('user')-> findAll (); // locate the data $ name = 'excelfile '; // The generated Excel file name $ res = service ('exceltoarrary ')-> push ($ data, $ name );

Second, the ExcelToArrary class is used to reference phpExcel and process data.

Class ExcelToArrary extends Service {public function _ construct () {/* import phpExcel core class note: If your path is different from mine, you cannot directly copy */include_once ('. /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 and author titles */$ objPHPExcel-> getProperties () -> setCreator ("Sunshine in turns")-> setLastModifiedBy ("Sunshine in turns")-> setTitle ("EXCEL export data")-> setSubject ("EXCEL export data ") -> setDescription ("backup data")-> setKeywords ("excel")-> setCategory ("result file");/* The following describes how to process data in Excel, this step is used to retrieve data in a horizontal manner. do not change the other steps */foreach ($ data as $ k => $ v) {$ num = $ k + 1; $ objPHPExcel-> setActiveSheetIndex (0) // Column A of Excel, uid is the key value of the array you found, 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 = "'.w.name.'.xls"'); header ('cache-Control: max-age = 0 '); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output'); exit ;}

Third, the above is all exported content, phpExcel site