Introduction to the use process of PHPExcel

I am using the thinkphp framework

I encountered a scenario where the deliveryman needed to send notification text messages to users one by one after delivering the goods. The efficiency was too low, so I thought of template text messages,

When it comes to efficiency issues, I designed it like this. He imported Excel, I parsed it through phpExcel, matched the name, phone number, order number, order type

and then sent a notification text message to the user. ,Send different order query addresses and notification information according to the order type

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

This article mainly records my process of using PHPExcel

This article is not Yuanchuang, it was compiled after reading many posts by great masters, and even copied. Code, but I did not record the specific original text, so I will not post it bit by bit.

This article is only for my own recording and study purposes

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

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 decompressed file of PHPExcel into Thinkphp/Library /Vendor

3. Encapsulate the operation method into a function (or class library) to facilitate your own use

excel.PHP function

<?php
/**
 * Created by PhpStorm.
 * User: 飞
 * Date: 2017/6/13
 * Time: 10:20
 * 导入excel文件，对表格进行解析
 */
function importExecl($file,$filetype){
    if(!file_exists($file)){
        return array("error"=>0,&#39;message&#39;=>&#39;file not found!&#39;);
    }
    // 判断文档类型，使用相应的方法，可以解析多种文件，这只是判断两个，其余的自己判断
    if($filetype == &#39;xlsx&#39;){
        $filetype = &#39;Excel2007&#39;;
    }elseif($filetype == &#39;xls&#39;){
        $filetype = &#39;Excel5&#39;;
    }
    // 引入扩展
    Vendor("PHPExcel.PHPExcel.IOFactory");
    $objReader = \PHPExcel_IOFactory::createReader($filetype);

    try{
        $PHPReader = $objReader->load($file);
    }catch(Exception $e){}
    if(!isset($PHPReader)) return array("error"=>0,&#39;message&#39;=>&#39;read error!&#39;);
    // 获得所有的sheets表格
    $allWorksheets = $PHPReader->getAllSheets();
    $i = 0;
    //对sheet表格遍历分析
    foreach($allWorksheets as $objWorksheet){
        // 获得sheet表格的标题
        $sheetname=$objWorksheet->getTitle();
        // 获得总行数
        $allRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        // 获得总列数
        $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn);
        $array[$i]["Title"] = $sheetname;
        $array[$i]["Cols"] = $allColumn;
        $array[$i]["Rows"] = $allRow;
        $arr = array();
        // 对合并的单元格进行分析
        $isMergeCell = array();
        foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells
            foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($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)==&#39;=&#39;){
                    return array("error"=>0,&#39;message&#39;=>&#39;can not use the formula!&#39;);
                    exit;
                }
                if($cell->getDataType()==\PHPExcel_Cell_DataType::TYPE_NUMERIC){
                    // $cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                    // $formatcode=$cellstyleformat->getFormatCode();
                    if (preg_match(&#39;/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i&#39;, $formatcode)) {
                        $value=gmdate("Y-m-d", \PHPExcel_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(&#39;Think&#39;);//must, resolve ThinkPHP and PHPExcel conflicts
    unset($objWorksheet);
    unset($PHPReader);
    unset($PHPExcel);
    unlink($file);
    return array("error"=>1,"data"=>$array);
}

Note: When instantiating, add \

# to the namespace. It’s easy to use, as follows Use the code

<?php
namespace PhpExcel\Controller;
use Think\Controller;

/**
 * Created by PhpStorm.
 * User: 飞
 * Date: 2017/6/7
 * Time: 11:26
 */
class IndexController extends Controller
{
    public function index()
    {
        $this->display();
    }

    public function importExcel()
    {
        // 表单提交文件过来
        // 获得文件路径
        $file = $_FILES[excel][tmp_name];
        if(!file_exists($file)){
            echo &#39;文件不存在&#39;;
            exit;
        }
        //
        $fileMessage = explode(&#39;.&#39;,$_FILES[excel][name]);
        // $filename = $fileMessage[0];
        // 获得文件扩展名
        $filetype = $fileMessage[1];
        //使用函数，获得excel数据
        $re = importExecl($file,$filetype);
        $content = $re[&#39;data&#39;][0][&#39;Content&#39;];
        // P助手函数，自己扩展
        P($content);exit; /*逻辑代码*/
    }
}

and then print it on the page as follows

From the page, the data analysis is very good

Of course, take After getting the data, you can do whatever you want. . . hey-hey

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

Another scenario is to generate your own data into an excel table

The code is as follows

public function outPortExcel()
{
    // 引入文件
    Vendor("PHPExcel.PHPExcel");
    vendor('PHPExcel/PHPExcel/Writer/Excel2007.php');
    $phpExcel = new \PHPExcel();
    $phpExcel->getProperties()->setTitle("Office 2007 XLSX Test Document title");
    $phpExcel->getProperties()->setSubject("Office 2007 XLSX Test Document subject");
    //单独添加数据
    $phpExcel->setActiveSheetIndex(0);
    $phpExcel->getActiveSheet()->setCellValue('A1', '姓名');//可以指定位置
    $phpExcel->getActiveSheet()->setCellValue('B1', '年龄');
    $phpExcel->getActiveSheet()->setCellValue('C1', '性别');
    $phpExcel->getActiveSheet()->setCellValue('D1', '家庭');
    //循环添加数据（根据自己的逻辑）
    for($i = 2;$i<200;$i++) {
        $phpExcel->getActiveSheet()->setCellValue(&#39;A&#39; . $i, &#39;张鹏飞&#39;.$i);
        $phpExcel->getActiveSheet()->setCellValue(&#39;B&#39; . $i, rand(25,28));
        $phpExcel->getActiveSheet()->setCellValue(&#39;C&#39; . $i, rand(0,1));
        $phpExcel->getActiveSheet()->setCellValue(&#39;D&#39; . $i, &#39;yes&#39;);
    }
    $objWriter = new \PHPExcel_Writer_Excel2007($phpExcel);
    // 文件名
    $filename = &#39;./a.xlsx&#39;;
    // 存储文件
    $objWriter->save($filename);

    // 下载文件
    // 强制下载函数 代码请转至 http://blog.csdn.net/fei003/article/details/54614097
    download(&#39;./a.xlsx&#39;);
}

ok, this is the process of using PHPExcel

The above is the detailed content of Introduction to the use process of PHPExcel. For more information, please follow other related articles on the PHP Chinese website!