ThinkPHP3.2 download, Import, Export function design and implementation, thinkphp3.2 Export

ThinkPHP3.2 download, Import, Export function design and implementation, thinkphp3.2 Export
Download: 1. Introduce the namespace:

use Org\Net\Http;

2. Set the root directory in the entry file:

// Define the absolute address of the ROOT directory define ('root', str_replace ("\", "/", dirname (_ FILE __))); // define the address define ('upload _ path', str_replace ("\\","/", dirname (_ FILE __))));

 

3. Operations in the controller:

Public function downloadss () {$ work = D ('work'); // instantiate the custom basic model class $ id = I ('get. id'); $ file = $ work-> getWorkMsg ($ id ); // obtain the file field name and the downloaded file name based on the ID. // The complete path of the file to be downloaded $ fileurl = ROOT. '/'. 'public '. '/'. $ file ['file field name']; // ROOT. $ file; // blow up the path with a dot to get the file suffix $ arr = explode ('. ', $ file ['file field name']); // get the last Suffix of the path, json_encode () the second parameter in is used to solve the problem of Chinese transcoding. $ name = json_encode ($ file ['name']. '. '. $ arr [count ($ arr)-1], JSON_UNESCAPED_UNICODE); $ down = new Http (); // instantiate the download class $ down-> download ($ fileurl, $ name); // download path and name}

 

4. Method in the model class: the user obtains the name of the file field to be downloaded and the name of the file to be downloaded Based on the ID (for example, the image field: picurl, and the image name: picurl_name)

public function getWorkMsg($id){    $where['w.stu_id']=$id;    $list=$this->alias('w')        ->where($where)        ->join('oa_student_info s on w.stu_id=s.id','left')        ->field('w.offer,s.name')        ->find();    return $list;}

 

Import

Put the configuration file YiXian in ThinkPHP/Library/Org, And put PHPExcel in the Vendor file.

1. View Layer

<Form method = "post" action = "<{: U ('home/StudentInfo/eximport ')}> "enctype =" multipart/form-data "> <input type =" file "name =" excel "/> <input type =" submit "value =" Import student "class = "dao dao1"/> </form>

2. Controller Layer

// Before importing excelpublic function eximport () {// before the import operation, you must first perform the upload operation $ Upload = new Upload (); // use Think \ upload ;) $ upload-> maxSize = 3145728; $ upload-> exts = array ('xls ', 'csv', 'xlsx'); $ upload-> rootPath = '. /Public '; $ upload-> savePath ='/excel/'; $ info = $ upload-> upload (); if (! $ Info) {$ this-> error ($ upload-> getError ();} else {// the address of the uploaded file is obtained after the upload is spliced. $ filename = ROOT. '/public '. $ info ['excel '] ['savepath']. $ info ['excel '] ['savename'];/** introduce the underlying class file required for the import operation * file path ThinkPHP/Library/Org/YiXian/ExcelReader. class. php */import ("Org. yiXian. excelReader "); // instantiate the import class $ ExcelReader = new \ ExcelReader (); // call reader_excel (file path) in the import class) method $ arr = $ ExcelReader-> reader_excel ($ filename); $ gArr = M ('userinfo')-> sele Ct (); // query the owner information $ cArr = M ('classinfo')-> select (); // query the class information/** final goal: assemble it into an array $ data []; * $ arr [$ key] ['1']: data of 2nd columns in an Excel table * $ arr [$ key] ['2']: Data of 3rd columns in an Excel table (the following data is incremented one by one) ** $ data ['name']: A custom array with a value (and so on) ** add (): Call the add () method, add data to the database table */foreach ($ arr as $ key => $ value) {$ data ['name'] = $ arr [$ key] ['1']; /** because the gender field in the student table accesses numbers, you must first judge "male or female ", * Then add the corresponding number to the database (** here, the method I used is a three-object operation, or ** use other judgment methods ***)* Is the following tuition fee paid in a similar way */$ data ['sex'] = ($ arr [$ key] ['2'] = 'male ')?; // The gender to be imported is converted to a number by the three-object operation. The class field in the student table stores the class ID, therefore, you must first query the data in the class table, and then perform Array Processing (the following "owner" is similar to this operation) */foreach ($ cArr as $ kk => $ vv) {if ($ vv ['class _ Code'] = $ arr [$ key] ['3']) $ data ['class _ id'] = $ vv ['id'];} $ data ['has _ pay'] = ($ arr [$ key] ['4'] = 'submitted ')? ;/** Because the date format in the Excel table is stored in the time format, it is converted to the timestamp format to be saved to the database * so the strtotime () method called here * The date format is encountered afterwards, use this method to convert */$ data ['birth _ date'] = strtotime ($ arr [$ key] ['5']); $ data ['age'] = $ arr [$ key] ['6'];/** because the education field in the student table accesses a number, you must first make a judgment, then, store * corresponding numbers in the database (** here, I use the switch method, or ** other methods ***) */switch ($ arr [$ key] ['7']) {case 'intermediate and below ': $ data ['edu'] = 1; break; case 'high ': $ data ['edu'] = 2; break; case '': $ data ['edu'] = 3; break; case' ': $ data ['edu'] = 4; break; case 'undergraduate ': $ data ['edu'] = 5; break; case 'master ': $ data ['edu'] = 6; break; case 'shid': $ data ['edu'] = 7; break; case 'postdoc ': $ data ['edu'] = 8; break;} $ data ['school '] = $ arr [$ key] ['8']; $ data ['bloodsion'] = $ arr [$ key] ['9']; $ data ['phone'] = $ arr [$ key] ['10']; // owner foreach ($ gArr as $ kk => $ vv) {if ($ vv ['name'] = $ arr [$ key] ['11']) $ data ['userg _ id'] = $ vv ['id'];} $ data ['create _ time'] = strtotime ($ arr [$ key] ['12']); $ dataArr [] = $ data ;} $ this-> studentinfo-> addAll ($ dataArr); // redirection after successful data import $ this-> success ('imported successfully ', U ('studentinfo/index_yx '), 3 );}}

Export:

First, put PHPExcel in the Vendor folder.

Create a functions. php file under Application/Common.

Functions. php

<? Php/** put this function in the public function. in php (application directory/Common), * function called in Excel table Export */function exportExcel ($ xlsName, $ expCellName, $ expTableData) {$ fileName = $ xlsName. date ('_ YmdHis'); // or $ xlsTitle file name can be set as needed $ cellNum = count ($ expCellName ); // obtain the header length $ dataNum = count ($ expTableData); // obtain the content length vendor ("PHPExcel. PHPExcel "); // introduce the EXCEL class package $ objPHPExcel = new \ PHPExcel (); // instantiate the class $ cellName = array ('A', 'B', 'C ', 'D', 'E', 'F', 'G', 'h', 'I', 'J', 'k', 'l', 'M ', 'N', 'O', 'P', 'Q', 'R', 's', 't', 'U', 'V', 'w ', 'X', 'y', 'z', 'AA', 'AB', 'ac', 'ad', 'AE', 'af', 'ag ', 'Ah', 'ai', 'aj ', 'ak', 'al', 'am', 'A', 'ao', 'ap, 'aq ', 'ar ', 'as', 'at', 'au', 'av', 'aw', 'ax ', 'ay', 'az '); $ objPHPExcel-> getActiveSheet (0)-> mergeCells ('a1 :'. $ cellName [$ cellNum-1]. '1'); // merge cells $ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ('a1', $ fileName. 'Student table'); // enter the title $ objPHPExcel-> setActiveSheetIndex (0)-> getStyle ('a1')-> getAlignment ()-> setHorizontal (\ PHPExcel_Style_Alignment :: HORIZONTAL_CENTER); // set the horizontal cell alignment format $ objPHPExcel-> setActiveSheetIndex (0)-> getStyle ('a1')-> getAlignment ()-> setVertical (\ PHPExcel_Style_Alignment :: VERTICAL_CENTER); // sets the vertical cell alignment format. // output the title bar for ($ I = 0; $ I <$ cellNum; $ I ++) {$ objPHPExcel-> setActiveSheetIndex (0)-> setCellValue ($ cellName [$ I]. '2', $ expCellName [$ I] [1]);} // output content bar for ($ I = 0; $ I <$ dataNum; $ I ++) {for ($ j = 0; $ j <$ cellNum; $ j ++) {$ objPHPExcel-> getActiveSheet (0)-> setCellValue ($ cellName [$ j]. ($ I + 3), $ expTableData [$ I] [$ expCellName [$ j] [0]) ;}// export header ('pragma: public '); header ('content-type: application/vnd. ms-excel; charset = UTF-8; name = "'.w.filename.'.xls" '); header ("Content-Disposition: Disposition"); $ objWriter = \ PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output'); exit ;}

 

1. View Layer

<Form action = "<{: U ('home/StudentInfo/export')}> "method =" post "class =" dao_form "> <input type =" hidden "name =" sn "value =" <{$ sname}> "/> <input type = "hidden" name = "st" value = "<{$ tel}>"/> <button type = "submit" target = "_ blank" class = "dao"> Export </button> </form>

 

 

2. Controller

// Export Excel public function export () {/** export operation: export the data read from the database to the Excel table * $ map: search criteria */$ map ['del'] = 1; $ map ['status'] = 2; if (I ('Post. sn ','')! = '') $ Map ['name'] = array ('like', '% '. I ('Post. sn ',''). '%'); if (I ('Post. st ','')! = '') $ Map ['phone'] = array ('eq ', I ('Post. st', ''); // set the encoding to UTF-8 header ('content-type: text/html; charset = UTF-8 '); // name of the exported Excel table $ xlsName = "student list"; // header of the exported Excel table $ xlsCell = array ('id', 'sequence number '), array ('name', 'name'), array ('sex', 'Gender '), array ('class _ id', 'class '), array ('has _ pay', 'tuition status'), array ('birth _ date', 'birthdate'), array ('age', 'admission age '), array ('edu', 'diploma '), array ('school', 'School of graduation '), array ('education sion ',' Professional '), array ('phone', 'Contact phone number '), array ('userg _ id', 'owner'), array ('create _ time ', 'creation Time'),); // search for data in the database based on search criteria $ xlsData = $ this-> studentinfo-> where ($ map)-> select (); $ gArr = M ('userinfo')-> select (); // query the owner information $ cArr = M ('classinfo')-> select (); // query the class information // Add the data foreach to the Excel table ($ xlsData as $ k => $ v) {/** because the gender field in the student table accesses numbers, you must first judge "male or female ", * Then, add the corresponding simplified Chinese characters to the Excel table (** here, I use the trigger operation method, or ** use other judgment methods ***) * Are the following tuition fees paid? The field is also a similar solution */$ xlsData [$ k] ['sex'] = ($ v ['sex'] = 1 )? 'Male': 'female '; $ xlsData [$ k] ['has _ pay'] = ($ v ['has _ pay'] = 1 )? 'Submitted': 'not submitted';/** because the education field in the student table accesses a number, you must first determine the "Education level ", * Then add the corresponding simplified Chinese characters to the Excel table (** here, I use the switch method, or ** use other methods ***) */switch ($ v ['edu']) {case 1: $ xlsData [$ k] ['edu'] = 'intermediate and lower '; break; case 2: $ xlsData [$ k] ['edu'] = 'high school'; break; case 3: $ xlsData [$ k] ['edu'] = ''; break; case 4: $ xlsData [$ k] ['edu'] =' '; break; case 5: $ xlsData [$ k] ['edu'] = ''; break; case 6: $ xlsData [$ k] ['edu'] = 'master'; break; case 7: $ xlsData [$ k] ['edu'] = 'shid'; break; case 8: $ xlsData [$ k] ['edu'] = 'postdoc '; break ;} /**, data processing * because the owner field in the student table accesses the employee ID, you must first query the data in the employee table, * Then perform Array Processing (the following "class" is similar to this operation) */foreach ($ gArr as $ kk => $ vv) {if ($ vv ['id'] = $ v ['userg _ id']) $ xlsData [$ k] ['userg _ id'] = $ vv ['name'];} // class foreach ($ cArr as $ kk => $ vv) {if ($ vv ['id'] = $ v ['class _ id']) $ xlsData [$ k] ['class _ id'] = $ vv ['class _ Code'];}/** because the date in the database table stores the time format, to save the data to Excel, convert it to the time format *. Therefore, the date () method called here * has encountered a timestamp format since then, convert */$ xlsData [$ k] ['birth _ date'] = date ("Y-m-d ", $ v ['birth _ date']); $ xlsData [$ k] ['create _ time'] = date ("Y-m-d ", $ v ['create _ time']);}/** call the function of the public function file. in php, The exportExcel () function * exportExcel () is the * function used for the export operation. location of the PHP file: Root/application directory/Common/* $ xlsName: name of the Excel table * $ xlsCell: header of the Excel table * $ xlsData: excel table content */exportExcel ($ xlsName, $ xlsCell, $ xlsData );}