phpoffice的导入导出使用

导出

 $market=[];
        foreach ($markets as $key=>$item){
            $market[$key]['market_name'] = $item['market_name'];
            $market[$key]['measure_of_area'] = $item['measure_of_area'];
            $market[$key]['flow_number'] = Db::name('visitors_sum')->where($flow_where)->sum('n_in_visitors');
            $market[$key]['booth_number'] = Db::name('stall')->where('market_id',$item['id'])->count();
            $market[$key]['merchant_number'] = Db::name('merchant')->where('market_id',$item['id'])->count();
            $market[$key]['transction_number'] = Db::name('trade')->where('market_id',$item['id'])->where($transction_number_where)->sum("total_amount");//交易额
            $market[$key]['order_number'] = Db::name('trade')->where('market_id',$item['id'])->where($transction_number_where)->count();//订单数
            $market[$key]['weight_number'] = Db::name('trade_detail')->where('market_id',$item['id'])->where($weight_number_where)->sum('weight');//称重量
        }
        $this->assign('type',$type);
        $headArray = ['市场名称','面积（平方）','客流量','摊位数','商户数','交易额','订单数','称重量（公斤）'];
        $keyArray = ['market_name','measure_of_area','flow_number','booth_number','merchant_number','transction_number','order_number','weight_number'];
        $fileName = '客流量数据统计';
        PhpOffice::exportInfo($market,$headArray,$keyArray,$fileName);

<?php
/**
 * Created by PhpStorm.

 * Date: 2019/5/25
 * Time: 17:16
 */

namespace app\nmadmin\service;

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class PhpOffice
{
    /**
     * @param $list 要导出数据
     * @param $head 表头 例['订单编号','收货人','手机号','收货地址','订单类型','商品名称','下单数量']
     * @param $keys 表头对应字段['order_sn', 'consignee', 'phone', 'userinfo', 'ordertype','goodinfo','num']
     * @param $fileName 导出文件的名字
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    static function exportInfo($list, $head, $keys, $fileName){
        @ini_set("memory_limit","1024M");
        @ini_set("max_execution_time","40");
        ob_end_clean();
        ob_start();
        $spreadsheet  = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        //设置表头
        //$head = ['订单编号','收货人','手机号','收货地址','订单类型','商品名称','下单数量'];
        //数据中对应的字段，用于读取相应数据：
        //$keys = ['order_sn', 'consignee', 'phone', 'userinfo', 'ordertype','goodinfo','num'];
        $count = count($head);  //计算表头数量
        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }
        /*--------------开始从数据库提取信息插入Excel表中------------------*/


        foreach ($list as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写

            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $spreadsheet->getActiveSheet(0)->setCellValueExplicit(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]],DataType::TYPE_STRING);
//                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName. date('Y-m-d H-i-s') . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}

其它需要下载的phpoffice插件自己下载。
//导入

    public function upload()
    {

        //获取表格的大小，限制上传表格的大小5M
        $file_size = $_FILES['file']['size'];

        if ( $file_size > 5 * 1024 * 1024 ) {
            $this->error('文件大小不能超过5M');

        }
        $marketId = $this->marketIdAndCompanyId();
        //限制上传表格类型
        $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);

        //application/vnd.ms-excel  为xls文件类型
        if ( $fileExtendName != 'xlsx' ) {
            $this->error('必须为excel表格，且必须为xlsx格式！');
//            exit();
        }

        if ( is_uploaded_file($_FILES['file']['tmp_name']) ) {
            // 有Xls和Xlsx格式两种
            $objReader = IOFactory::createReader('Xlsx');
//            $objReader= new Xls();

            $filename    = $_FILES['file']['tmp_name'];
            $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
            $sheet       = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow  = $sheet->getHighestRow();       // 取得总行数
//            var_dump($highestRow);die;
            $objReader->getReadEmptyCells(false);
            // $highestColumn = $sheet->getHighestColumn();   // 取得总列数


//        var_dump($highestRow);die;
            $fail =0;
            $total = $highestRow -2;
            for ($j = 3; $j <= $highestRow; $j++) {
                $stall_number = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getFormattedValue();
                $floor_name   = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getFormattedValue();
                $area_name   = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getFormattedValue();

                $floor = model('stall_category')->where(['type'=>'楼层','market_id'=>$marketId['market_id'],'name'=>$floor_name])->field('id')->find();
                $area = model('stall_category')->where(['type'=>'业态','market_id'=>$marketId['market_id'],'name'=>$area_name])->field('id')->find();
                if(empty($floor) || empty($area)){
                    $fail++;
                    file_put_contents($marketId['market_id']."摊位号导入失败的摊位.txt", "时间：".date('Y-m-d H:i:s')."市场：".$marketId['market_id']."stall_number:".$stall_number.'floor_id:'.''.'area_id:'.''.PHP_EOL, FILE_APPEND);
                    continue;
                }
                if ($stall_number && $floor_name && $area_name){
                    $stall_is_exists = model('stall')->where('market_id',$marketId['market_id'])->where(['stall_number'=>$stall_number,'floor_id'=>$floor->id,'area_id'=>$area->id])->find();
                    if($stall_is_exists){
                        $fail++;
                        file_put_contents($marketId['market_id']."摊位号导入失败的摊位.txt", "时间：".date('Y-m-d H:i:s')."市场：".$marketId['market_id']."stall_number:".$stall_number.'floor_id:'.$floor->id.'area_id:'.$area->id.PHP_EOL, FILE_APPEND);
                        continue;
                    }
                    $data =[
                        'stall_number'=>$stall_number,
                        'floor_id'=>$floor->id,
                        'area_id'=>$area->id,
                        'market_id'=>$marketId['market_id'],
                    ];
                    model('stall')::create($data);
                    file_put_contents($marketId['market_id']."摊位号导入成功的摊位.txt", "时间：".date('Y-m-d H:i:s')."市场：".$marketId['market_id']."stall_number:".$stall_number.'floor_id:'.$floor->id.'area_id:'.$area->id.PHP_EOL, FILE_APPEND);
                }else{
                    file_put_contents($marketId['market_id']."摊位号导入失败的摊位.txt", "时间：".date('Y-m-d H:i:s')."市场：".$marketId['market_id']."stall_number:".$stall_number.'floor_id:'.$floor['id'].'area_id:'.$area['id'].PHP_EOL, FILE_APPEND);
                    continue;
                }
            }
            $this->success('导入成功,总条数为：'.$total.'失败了：'.$fail.'条');
        }else{
            $this->error('文件过大或格式不正确导致上传失败-_-!');
        }
    }