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

ThinkPHP uses PHPExcel to import and export complete Excel data, thinkphpphpexcel. ThinkPHP uses PHPExcel to import and export complete Excel data. the example described in this article is used in the development framework of Thinkphp, if you use ThinkPHP in other frameworks and use PHPExcel to import and export Excel data, thinkphpphpexcel

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 */forea Ch ($ 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 exported all content, phpExcel site http://www.bkjia.com/codes/194070.html

When phpexcel is used in thinkphp to import an excel table, PHPExcel_IOFactory is not found.

The prompt is not correct. if it is not found, the path is incorrect. check the prompt.

When using phpexcel to upload data to the database through excel, thinkphp reports the following error:

It may be that your excel format is not standard
I also use a tp framework. there is no problem when operating excel.

The examples described in this article are used in the Thinkphp development framework, and the same is true for other frameworks...