thinkphp 3.2.3 using Phpexcel to work with Excel tables

Download Phpexcel (https://github.com/PHPOffice/PHPExcel)

Unzip the downloaded zip package to./thinkphp/library/vendor

One, import Excel data

Controller custom, here with IndexController.class.php

<?phpnamespace home\controller;use Think\controller;class Indexcontroller extends Controller {//import Excel Public        function Excel () {header (' content-type:text/html; Charset=utf-8 ');        Vendor (' PHPExcel.Classes.PHPExcel ');        $file = './data/example.xlsx ';        $Excel = new \phpexcel ();        If the Excel file suffix name is. xls//Vendor ("PHPExcel.Classes.PHPExcel.Reader.Excel5");        If the Excel file suffix is named. xlsx Vendor ("PHPExcel.Classes.PHPExcel.Reader.Excel2007");        $PHPReader = new \phpexcel_reader_excel2007 ();        Load file $Excel = Load ($file) $PHPReader        Gets the first sheet in the table, if you want to get the second one, change 0 to 1, and so on $currentSheet = $Excel, Getsheet (0);        Gets the total number of columns $allColumn = $currentSheet-Gethighestcolumn ();        Gets the total number of rows $allRow = $currentSheet-Gethighestrow ();            Loops through the data in the table, $currentRow represents the current row, starting from which row to read the data, the index value starts at 0 for ($currentRow = 1; $currentRow <= $allRow; $currentRow + +) { from which column, a indicates the firstA column for ($currentColumn = ' A '; $currentColumn <= $allColumn; $currentColumn + +) {//data coordinates            $address = $currentColumn. $currentRow;                                                 The data is read, saved to the array $arr $arr [$currentRow] [$currentColumn] = $currentSheet            Getcell ($address), GetValue ();        }} echo ' <pre> ';    Var_export ($arr); }}

Output:

Array (1 = = Array (' a ' = ' = ' branch name ', ' B ' + = ' name ', ' C ' + = ' amount '), 2 = = Array (' a ' = =  ' A branch ', ' b ' = ' Juanjuan Zhao ', ' c ' and ' = 1100, ' 3 ' + ' array (' a ' = ' B ' branch ', ' b ' = ' = ' Kongjian ', ' c ' = = 1100,), 4 = = Array (' a ' = = ' C branch ', ' B ' = ' Wang Hua fa ', ' C ' = 1300,), 5 = = Array (' A ' =& Gt ' C branch ', ' b ' = ' sean ', ' c ' = ' + ', ' 6 ' + ' array (' A ' = ' B ' branch ', ' b ' = ' Huafa ', ' c ' = ' + 1  (+), 7 = = Array (' a ' = = ' A branch ', ' B ' + = ' Zhao Germany ', ' C ' + = '), 8 = = Array (' A ' = ' B branch ', ' b ' = ' Shen Fanghong ', ' c ', ' = ', ' 9 ' + ' array (' A ' = ' C ' branch ', ' b ' = ' + ' Zhou Hongyu ', ' C ' =&gt ; 1100,), ten = = Array (' a ' = = ' A branch ', ' B ' + = ' fragrance ', ' C ' = +), one-by-one array (' a ' =& Gt ' A branch ', ' b ' = ' Ching ', ' C ' and ' 1100, ', ' a ' + ' = ' B ', ' b ' = ' = ' Chanyi ', ' C ' =&gt ; 1400,), 13 = = Array (' a ' = = ' B branch ', ' b ' = = ' Chen Huahui ', ' C ' = = ', '), + = Array (' a ' = = ' C ' branch '),  ' B ' = ' Cao Shang ', ' c ' and ' = 1400, ' and ' a ', ' a ' + ' a branch ', ' b ' = ' zheng Hong ', ' c ' and ' = 600, ), A + = Array (' a ' = = ' A branch ', ' B ' = = ' Wang Hongren ', ' C ' = + +), + = Array (' a ' = = ' C Branch ', ' B ' = ' Ho Danmei ', ' C ' = 1300,),

　　

The Excel file you need to import is as follows:

　　

Second, export Excel

    Exporting Excel Public Function export () {Vendor (' PHPExcel.Classes.PHPExcel ');        $Excel = new \phpexcel ();  $arr = Array (1 = = Array (' a ' = ' = ' branch name ', ' b ' = ' = ' name ', ' C ' + = ' amount '), 2 = = Array (' a ' = = ' A branch ', ' B ') = ' Juanjuan Zhao ', ' C ' and ' 1100, ', 3 = = Array (' a ' = = ' B branch ', ' b ' = = ' Kongjian ', ' c ' and ' = 1100, '), 4 = = Array (' A ' = > ' C branch ', ' B ' + ' Wang Hua ', ' c ' and ' = 1300, ' 5 ' + ' array (' A ' = ' C ' branch ', ' b ' = ' = ' sean ', ' c ' = ' + '), 6 =&G T Array (' a ' = ' = ' B branch ', ' b ' = = ' Huafa ', ' c ' = ' = ' 1400, '), 7 = = Array (' a ' = = ' A branch ', ' B ' + = ' Zhao Germany ', ' c ' + = (a), 8 = = Array (' a ' = = ' B branch ', ' b ' = ' = ' Shen Fanghong ', ' C ' = + '), 9 = = Array (' a ' = = ' C branch ', ' b ' = = ') Zhou Hongyu ', ' c ' and ' = 1100, '), ten = = Array (' a ' = = ' A branch ', ' B ' = ' Apply fragrance ', ' c ' = ' + '), one by one = Array (' A ' =  ' A branch ', ' b ' = ' Ching ', ' C ' and ' 1100, ', ' a ' a ' + = ' B branch ', ' b ' = ' = ' Chanyi ', ' c ' and ' = 1400 ', ' = ' Array (' A '= ' B branch ', ' b ' = ' Chen Huahui ', ' c ' = = ', ', ' (' A ' = ' = ' C ' branch ', ' b ' = ' = ' Cao Shang ', ' c ' and ' = 1400, '), 15 = = Array (' a ' = = ' A branch ', ' b ' = = ' Zheng Hong ', ' c ' = + '), ' + = ' array (' a ' = = ' A branch ', ' b ' = ' + ' Wang Hongren ', ' C        ' = + ', ' + + ' array (' A ' = = ' C branch ', ' B ' = ' Ho Danmei ', ' c ' = 1300, '); Set $Excel->getproperties ()->setcreator ("Dee")->setlastmodifiedby ("D EE ")->settitle (" Data Excel Export ")->setsubject (" Data Excel Export ")->setdescription (" Data Excel        Export ")->setkeywords (" Excel ")->setcategory (" Result file ");            foreach ($arr as $key + $val) {//Note that key is starting from 0 or 1, here is 0//$num = $key + 1; $Excel->setactivesheetindex (0)//excel column A, the UID is the key value of the array you isolated, and so on->setcellvalue (' A ').                $key, $val [' A '])->setcellvalue (' B '. $key, $val [' B '])  ->setcellvalue (' C '. $key, $val [' C ']);        } $Excel->getactivesheet ()->settitle (' Export ');        $Excel->setactivesheetindex (0);         $name = ' example_export.xlsx ';        Header (' Content-type:application/vnd.ms-excel ');        Header (' content-disposition:attachment; Filename= '. $name);        Header (' cache-control:max-age=0 ');        $ExcelWriter = \phpexcel_iofactory::createwriter ($Excel, ' Excel2007 ');        $ExcelWriter->save (' php://output ');            Exit }

Export of Example_export.xlsx

　　

Right--Properties:

Reference:

thinkphp3.2 Integrated Phpexcel

Import and export of Excel data using Phpexcel (full step detailed parsing)

thinkphp 3.2.3 using Phpexcel to work with Excel tables