Introduction to the use process of phpexcel

I'm using the thinkphp framework.

Encounter a scene, the consignor after the delivery of a user to send a notification message, efficiency is too low, so I thought of the template text messages,

To the problem of efficiency, I was designed like this, he put Excel into, I parse through Phpexcel, match to name, phone, and order number, order type

Then send a notification SMS to the user, according to the order type to send a different order query address and notification information

--------------------------------------------------------------------------------------------------------------- ---------------------------

This is the main record of the process of using Phpexcel.

This is not far create, is to see a lot of great God's post after finishing, even say copy of the code, but the specific text I did not record, not 1.1 points posted out

Write this article only for your own record of learning

--------------------------------------------------------------------------------------------------------------- -------------------------

1: Download Phpexcel

Can be downloaded from the official website, I also uploaded the document http://download.csdn.net/detail/fei003/9851672

2 put Phpexcel into the project

Put the phpexcel extracted files into the Thinkphp/library/vendor

3. Encapsulate the operation method into a function (or class library) to make it easy for you to use

Excel. PHP functions

<?php/** * Created by Phpstorm. * User: Fly * DATE:2017/6/13 * time:10:20 * Import Excel file, parse the table */function importexecl ($file, $filetype) {if (!file_exists (    $file) {return Array ("Error" =>0, ' message ' = ' File not found! ');    }//To determine the type of document, using the corresponding method, can parse a variety of files, which is only judged by two, the rest of their own judgment if ($filetype = = ' xlsx ') {$filetype = ' Excel2007 ';    }elseif ($filetype = = ' xls ') {$filetype = ' Excel5 ';    }//Introducing the Extended Vendor ("PHPExcel.PHPExcel.IOFactory");    $objReader = \phpexcel_iofactory::createreader ($filetype);    try{$PHPReader = $objReader->load ($file);    }catch (Exception $e) {} if (!isset ($PHPReader)) return Array ("Error" =>0, ' message ' = ' read error! ');    Get all the sheets forms $allWorksheets = $PHPReader->getallsheets ();    $i = 0; For sheet Table Traversal parsing foreach ($allWorksheets as $objWorksheet) {//Gets the title of the sheet table $sheetname = $objWorksheet->get        Title ();        Get total number of rows $allRow = $objWorksheet->gethighestrow (); $highestColumn = $Objworksheet->gethighestcolumn ();        Get the total number of columns $allColumn = \phpexcel_cell::columnindexfromstring ($highestColumn);        $array [$i] [Title] = $sheetname;        $array [$i] ["Cols"] = $allColumn;        $array [$i] ["Rows"] = $allRow;        $arr = Array ();        Parse the merged cells $isMergeCell = Array (); foreach ($objWorksheet->getmergecells () as $cells) {//merge cells foreach (\phpexcel_cell::extractallcellref            Erencesinrange ($cells) as $cellReference) {$isMergeCell [$cellReference] = true;            }} for ($currentRow = 1; $currentRow <= $allRow; $currentRow + +) {$row = array ();                for ($currentColumn =0; $currentColumn < $allColumn; $currentColumn + +) {;                $cell = $objWorksheet->getcellbycolumnandrow ($currentColumn, $currentRow);                $afCol = \phpexcel_cell::stringfromcolumnindex ($currentColumn + 1);  $bfCol = \phpexcel_cell::stringfromcolumnindex ($currentColumn-1);              $col = \phpexcel_cell::stringfromcolumnindex ($currentColumn);                $address = $col. $currentRow;                $value = $objWorksheet->getcell ($address)->getvalue ();                    if (substr ($value, 0,1) = = ' = ') {return array ("Error" =>0, ' message ' = ' can not use the formula! ');                Exit } if ($cell->getdatatype () ==\phpexcel_cell_datatype::type_numeric) {//$cellstyleforma                    t= $cell->getparent ()->getstyle ($cell->getcoordinate ())->getnumberformat ();                    $formatcode = $cellstyleformat->getformatcode (); if (Preg_match ('/^ ([$[a-z]*-[0-9a-f]*]) *[hmsdy]/i ', $formatcode)) {$value =gmdate ("y-m-d", \phpexce                    l_shared_date::exceltophp ($value));                    }else{$value =\phpexcel_style_numberformat::toformattedstring ($value, $formatcode);         }                }       if ($isMergeCell [$col. $currentRow]&& $isMergeCell [$afCol. $currentRow]&&!empty ($value)) {                $temp = $value;                    }elseif ($isMergeCell [$col. $currentRow]&& $isMergeCell [$col. ($currentRow-1)]&&empty ($value)) {                $value = $arr [$currentRow -1][$currentColumn];                    }elseif ($isMergeCell [$col. $currentRow]&& $isMergeCell [$bfCol. $currentRow]&&empty ($value)) {                $value = $temp;            } $row [$currentColumn] = $value;        } $arr [$currentRow] = $row;        } $array [$i] [Content] = $arr;    $i + +;    }//Spl_autoload_register (' Think ');//must, resolve thinkphp and Phpexcel conflicts unset ($objWorksheet);    Unset ($PHPReader);    Unset ($PHPExcel);    Unlink ($file); Return Array ("Error" =>1, "data" = $array);}

Note: Namespaces are added when instantiating

The use of the time is simple, the following is the use of code

<?phpnamespace phpexcel\controller;use think\controller;/** * Created by Phpstorm. * User: Fly * DATE:2017/6/7 * time:11:26 */class Indexcontroller extends controller{public    function Index ()    {
  
    $this->display ();    }    Public Function Importexcel ()    {//        form submit file over        //Get file path        $file = $_files[excel][tmp_name];        if (!file_exists ($file)) {            echo ' file does not exist ';            Exit;        }        $fileMessage = Explode ('. ', $_files[excel][name]);        $filename = $fileMessage [0];        Get file extension        $filetype = $fileMessage [1];        Use functions to get Excel data        $re = Importexecl ($file, $filetype);        $content = $re [' Data '][0][' content '];        P helper function, expand        p ($content); exit;/* Logic code */    }}
  

Then print the following on the page

From the page view, the data parsing is very good

Of course, after you get the data, do what you want to do ... Hey

---------------------------------------------------------------------------------------------------

Another scenario is to generate Excel tables with your own data

The code is as follows

Public Function Outportexcel () {//Introduce file Vendor ("Phpexcel.phpexcel");    Vendor (' phpexcel/phpexcel/writer/excel2007.php ');    $phpExcel = new \phpexcel ();    $phpExcel->getproperties ()->settitle ("Office" XLSX Test Document title ");    $phpExcel->getproperties ()->setsubject (Office "XLSX Test Document subject");    Add data separately $phpExcel->setactivesheetindex (0); $phpExcel->getactivesheet ()->setcellvalue (' A1 ', ' name ');//You can specify the location $phpExcel->getactivesheet (),    Setcellvalue (' B1 ', ' age ');    $phpExcel->getactivesheet ()->setcellvalue (' C1 ', ' sex ');    $phpExcel->getactivesheet ()->setcellvalue (' D1 ', ' family '); Loop add data (according to your logic) for ($i = 2; $i <200; $i + +) {$phpExcel->getactivesheet ()->setcellvalue (' A '. $i, ' Zhang Pengfei '. $        i);        $phpExcel->getactivesheet ()->setcellvalue (' B '. $i, Rand (25,28));        $phpExcel->getactivesheet ()->setcellvalue (' C '. $i, rand (0,1)); $phpExcel->getactivesheet ()->setcellvalue (' D '. $i, ' yes ');    } $objWriter = new \phpexcel_writer_excel2007 ($phpExcel);    File name $filename = './a.xlsx ';    Storage file $objWriter->save ($filename); Download file//force download function code please go to http://blog.csdn.net/fei003/article/details/54614097 download ('./a.xlsx ');}

OK, this is the process of using phpexcel