Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how tp implements the import and export of excel". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

One: download the third-party class library of phpExcel

Download address: http://www.thinkphp.cn/topic/14005.html download official website

Second: after the download is successful, put the file under Vendor: ThinkPHP\ Extend\ Vendor\ PHPExcel\ PHPExcel.php.

3: configure the information operation of the database (mine is to add the following configuration under the home folder (application/home/config/config.php):

Four: template code:

Export data and generate excel

The controller method to realize the function:

1 > implementation of the method of exporting data:

Function expUser () {/ / Export Excel / / get the header name: $xlsName = "evaluate_check"; / / get the specific data in the file $xlsCell = array ('teacher check assignment table', 'function', 'permission', 'student', 'student answer') / / connect to the database to find the query table $xlsModel = M ('evaluate_check'); / / find the fields in the data table $xlsData = $xlsModel- > Field (' id,tep_id,tet_id,student_id,student_anwser')-> select () by calling the select method field; $this- > exportExcel ($xlsName,$xlsCell,$xlsData) } public function exportExcel ($expTitle,$expCellName,$expTableData) {/ / grab the file name to convert the character encoding to the utf8 format $xlsTitle = iconv ('utf-8',' gb2312', $expTitle); / / the file name / / name the file name of the Excel file, and then scramble $fileName = $_ SESSION ['account'] .date (' _ YmdHis') through the time function / / or $xlsTitle file name can be set according to your own situation / / the number of lines of the field can be obtained through the count function $cellNum = count ($expCellName); / / get the specific value $dataNum = count ($expTableData); / / var_dump (count ($expTableData)); exit; / / introduce a specific class library vendor ("PHPExcel.PHPExcel"); / / echo '1111dfgfhj exit / / instantiate the method and get all the methods $objPHPExcel = new\ PHPExcel () / / set the number of rows in a cell $cellName = array ('Achilles, pyrrons, pyrrons, packs, sails, squares, vases, washes, and washes. 'AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ') / / var_dump ($cellName); exit;// var_dump ($objPHPExcel- > getActiveSheet (0)-> mergeCells ('A1:'.$cellName [$cellNum-1].' 1')); exit; $objPHPExcel- > getActiveSheet (0)-> mergeCells ('A1:'.$cellName [$cellNum-1].' 1'); / / merge cell / / echo '1111111 exit / / var_dump ($objPHPExcel- > getActiveSheet (0)-> mergeCells ('A1:'.$cellName [$cellNum-1].' 1'); exit;// $objPHPExcel- > setActiveSheetIndex (0)-> setCellValue ('A1Qing, $expTitle.' Export time:'.date ('Y-m-d Y-m-d isetCellValue'); / / A pair of obtained line numbers are looped and assigned for ($iDeployisetActiveSheetIndex (0)-> setCellValue ($cellName [$I]. '2percent, $expCellName [$I]);} $objPHPExcel- > getActiveSheet ()-> setTitle ($expTitle); $objPHPExcel- > setActiveSheetIndex (0)-> setCellValue (' A1century title)) / / Miscellaneous glyphs, UTF-8// var_dump ($dataNum); exit; / / A pair of acquired columns for traversal assignment output for ($cellName [$j]. ($iTun3), $a [$j]);}} ob_end_clean (); / / clear buffering to avoid garbled header ('pragma:public') / / set the header information header ('Content-type:application/vnd.ms-excel;charset=utf-8;name= ". $xlsTitle.'.xls"); header ("Content-Disposition:attachment;filename=$fileName.xls"); / / attachment new window print inline this window print / / write the value to $objWriter =\ PHPExcel_IOFactory::createWriter ($objPHPExcel,' Excel5') by calling the following method to create the Excel / / Save and download $objWriter- > save ('php://output'); exit;}

3 > implementation of data import method:

(1) Select the xls file you want to import and perform the operation

(2) the code is as follows:

Function impUser () {/ / determine whether your file is empty if (! empty ($_ FILES)) {/ / introduce the class library vendor ("PHPExcel.PHPExcel"); / / get the file name of the stored file $file_name=$_FILES ['import'] [' tmp_name'] / / call the following method to read the information in your table $objReader =\ PHPExcel_IOFactory::createReader ('Excel5'); / / var_dump ($objReader); exit; / / read the temporary file name information $objPHPExcel = $objReader- > load ($file_name,$encode='utf-8') / / get the number of columns in the Excel table $sheet = $objPHPExcel- > getSheet (0); $highestRow = $sheet- > getHighestRow (); / / get the total number of rows $highestColumn = $sheet- > getHighestColumn (); / / get the total number of columns / / write the value to for ($item3 × columns ActiveSheet ()-> getCell ("B". $I)-> getValue () $sex = $objPHPExcel- > getActiveSheet ()-> getCell ("C". $I)-> getValue (); / / $data ['res_id'] = $objPHPExcel- > getActiveSheet ()-> getCell ("D". $I)-> getValue (); $data [' class'] = $objPHPExcel- > getActiveSheet ()-> getCell ("E". $I)-> getValue () $data ['year'] = $objPHPExcel- > getActiveSheet ()-> getCell ("F". $I)-> getValue (); $data [' city'] = $objPHPExcel- > getActiveSheet ()-> getCell ("G". $I)-> getValue (); $data ['company'] = $objPHPExcel- > getActiveSheet ()-> getCell ("H". $I)-> getValue () $data ['zhicheng'] = $objPHPExcel- > getActiveSheet ()-> getCell ("I". $I)-> getValue (); $data [' zhiwu'] = $objPHPExcel- > getActiveSheet ()-> getCell ("J". $I)-> getValue (); $data ['jibie'] = $objPHPExcel- > getActiveSheet ()-> getCell ("K". $I)-> getValue () $data ['honor'] = $objPHPExcel- > getActiveSheet ()-> getCell ("L". $I)-> getValue (); $data [' tel'] = $objPHPExcel- > getActiveSheet ()-> getCell ("M". $I)-> getValue (); $data ['qq'] = $objPHPExcel- > getActiveSheet ()-> getCell ("N". $I)-> getValue () $data ['email'] = $objPHPExcel- > getActiveSheet ()-> getCell ("O". $I)-> getValue (); $data [' remark'] = $objPHPExcel- > getActiveSheet ()-> getCell ("P". $I)-> getValue (); $data ['sex'] = $sex==' Man'? 1getCell 0; $data ['res_id'] = 1; $data [' last_login_time'] = 0 $data ['create_time'] = $data [' last_login_ip'] = $_ SERVER ['REMOTE_ADDR']; $data [' login_count'] = 0; $data ['join'] = 0; $data [' avatar'] =''; $data ['password'] = md5 (' 123456') / / add values to the database in the following ways M ('Member')-> add ($data);} $this- > success (' imported successfully!') ;} else {$this- > error ("Please select a file to upload");}}

Note: the final database information is mainly based on your specific table information, in M ('indicate')-> add ($data)

The data in $data is also based on your real table.

This is the end of the introduction of "how tp implements the import and export of excel". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.