thinkphp Import and export Excel form data

In PHP projects you often import and export Excel forms.

First download the Phpexcel class library file and put it in the appropriate location.

My position in the thinkphp frame is thinkphp/library/org/util/

Import

Upload Excel file in the page to the background, using form form bill of lading can

<formMethod= "POST"enctype= "Multipart/form-data"Action="" >  <inputtype= "Submit"value= "Import" />                               <inputtype= "File"name= "Excel" />                    </form>  

Submit to the background for upload processing, this is a way to perform the upload

 Public functiondata () {if(is_post) {//set encoding to prevent garbled characters        Header("Content-type:text/html;charset=utf-8"); $upload=New\think\upload ();//instantiating an upload class            $upload->maxsize = 3145728;//set attachment upload size            $upload->exts =Array(' xls ', ' xlsx ', ' csv ');//Set attachment upload class            $upload->rootpath = ' uploads/'; $upload->savepath = ";//Set Attachments upload directory//upload files            $info=$upload->uploadone ($_files[' Excel ']); $filename= ' uploads/'.$info[' Savepath '].$info[' Savename ']; $exts=$info[' ext ']; //Var_dump ($info);            if(!$info) {//Upload error message                  $this->error ($upload-GetError ()); }Else{//Upload Successful                  $this->data_import ($filename,$exts); }                  }    }

The format of the uploaded file is in Excel format, Xls,xlsx,csv can support

Then read the contents of the uploaded file to import, the following is a read method.

protected functionData_import ($filename,$exts= ' xls ')    {        //Import the Phpexcel class library because Phpexcel does not use a namespace, only inport importImport ("Org.Util.PHPExcel"); //Create a Phpexcel object, note that there is no less \        $PHPExcel=New\phpexcel (); //if the Excel file suffix name is. xls, import this class        if($exts= = ' xls ') {Import ("Org.Util.PHPExcel.Reader.Excel5"); $PHPReader=New\phpexcel_reader_excel5 (); }Else if($exts= = ' xlsx ') {Import ("Org.Util.PHPExcel.Reader.Excel2007"); $PHPReader=New\phpexcel_reader_excel2007 (); }Else if($exts= = ' csv ') {Import ("Org.Util.PHPExcel.Reader.CSV"); $PHPReader=New\phpexcel_reader_csv (); }         //var_dump ($PHPReader);d ie; Loading files        $PHPExcel=$PHPReader->load ($filename); //gets the first sheet in the table, if you want to get the second one, change 0 to 1, and so on        $currentSheet=$PHPExcel->getsheet (0); //get total number of columns        $allColumn=$currentSheet-Gethighestcolumn (); //get total number of rows        $allRow=$currentSheet-Gethighestrow (); //loops through the data in the table, $currentRow represents the current row, which row starts reading the data, and the index value starts at 0         for($currentRow= 1;$currentRow<=$allRow;$currentRow++){            //from which column, a represents the first column             for($currentColumn= ' A ';$currentColumn<=$allColumn;$currentColumn++){                //Data coordinates                $address=$currentColumn.$currentRow; //the data to be read, saved to the array $arr                $data[$currentRow][$currentColumn]=$currentSheet->getcell ($address),GetValue (); }         }        $this->save_import ($data); }

Use the above method to get the data in Excel, and then use the Save_import to store the data obtained in the database

//Save Import data     Public functionSave_import ($data)    {        //Print_r ($data); exit;         $bath= M (' Data '); //when inserting new data, first empty the original table data, without this need can annotate the following steps//m (' Mobile ')->where (' 1=1 ')->delete ();                 foreach($data  as $k=$v){                //$mobile = $v [' A ']; Note: ****** (1)                $info[' a '] =$v[' A ']; $info[' B '] =$v[' B ']; $info[' C ']=$v[' C ']; $info[' D_water ']=$v[' D ']; $info[' E ']=$v[' E ']; $info[' F ']=$v[' F ']; $arr[] =$info; }        $result=$bath->addall ($arr); if($result){            $this->success (' Data import succeeded '); }Else{            $this->error (' Data import failed '); }        //Print_r ($info);}

Exporting is much easier than importing, and reading data from a database makes the appropriate processing

 Public functionExcel () {//Introducing the Phpexcel library fileVendor (' PHPExcel.Classes.PHPExcel '); //Creating Objects        $excel=NewPhpexcel (); //Excel table format, where 8 columns are briefly written        $letter=Array(' A ', ' B ', ' C ', ' D ', ' E ', ' f ', ' f ', ' G '); //Table Header Array        $tableheader=Array(' Serial number ', ' Inn name ', ' Inn address ', ' landline ', ' phone ', ' QQ ', ' email ')); //Populating header information         for($i= 0;$i<Count($tableheader);$i++) {            $excel->getactivesheet ()->setcellvalue ("$letter[$i]1 ","$tableheader[$i]"); }        //Table Array        $data=Array(                Array(' 1 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 2 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 3 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 4 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 5 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 6 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),Array(' 7 ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G '),        ); //Populating table Information         for($i= 2;$i<=Count($data) + 1;$i++) {            $j= 0; foreach($data[$i-2] as $key=$value) {                $excel->getactivesheet ()->setcellvalue ("$letter[$j]$i","$value"); $j++; }        }        //Create an Excel input object        $write=NewPhpexcel_writer_excel5 ($excel); Header("Pragma:public"); Header("expires:0"); Header("Cache-control:must-revalidate, post-check=0, pre-check=0"); Header("Content-type:application/force-download"); Header("Content-type:application/vnd.ms-execl"); Header("Content-type:application/octet-stream"); Header("Content-type:application/download");; Header(' content-disposition:attachment;filename= ' Inn information. xls "'); Header("Content-transfer-encoding:binary"); $write->save (' Php://output '); 

The front page only needs to execute this method

thinkphp Import and export Excel form data