PHPExcel导出（横向，垂直向）、导入

最新推荐文章于 2024-07-26 14:40:51 发布

熊先生的博客

最新推荐文章于 2024-07-26 14:40:51 发布

阅读量5.6k

点赞数

分类专栏： PHP 文章标签： excel

本文链接：https://blog.csdn.net/xiojing825/article/details/70198579

版权

PHP 专栏收录该内容

36 篇文章 1 订阅

订阅专栏

PHPExcel是功能完善的Excel导入导出插件。Github地址：https://github.com/PHPOffice/PHPExcel

1.横向导出

效果图如下：
这里写图片描述

    //横向导出
    public function cross()
    {
        require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php';

        $objExcel = new PHPExcel();
        $objProps = $objExcel->getActiveSheet();

        $names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱'];

        //循环设置
        foreach ($names as $key=>$value) {
            // 设置水平居中
            $objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            // 设置垂直居中
            $objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设置宽
            $objProps->getColumnDimension($key)->setWidth(20);
            //设置列名
            $name = $key.'1';
            $objProps->setCellValue($name, $value);
        }
        //也可以不循环，单独设置
        // 设置宽度
        // $objProps->getColumnDimension('A')->setWidth(30);
        // $objProps->getColumnDimension('B')->setWidth(30);
        // $objProps->getColumnDimension('C')->setWidth(30);
        //设置列名
        // $objProps->setCellValue("A1", '姓名');
        // $objProps->setCellValue("B1", '区服');
        // $objProps->setCellValue("C1", '电话');

        $line = 2;

        $data = [
            ['name'=>'mike','phone'=>'123','email'=>'123@.com'],
            ['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
            ['name'=>'jack','phone'=>'789','email'=>'789@.com'],
        ];
        foreach ($data as $key => $value) {
            $objProps->setCellValue("A" . $line, $value['name']);
            $objProps->setCellValue("B" . $line, $value['phone']);
            $objProps->setCellValue("C" . $line, $value['email']);

            $line++;

        }

        //设输出格式
        $write = new PHPExcel_Writer_Excel5($objExcel);
        // $write = new PHPExcel_Writer_Excel2007($objExcel);

        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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
        header("Content-Transfer-Encoding:binary");

        // 保存
        $write->setPreCalculateFormulas(false);
        $write->save('php://output');

    }

2.垂直向

效果图

这里写图片描述

    //垂直向
    public function vertical11()
    {
        require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php';

        $objExcel = new PHPExcel();
        $objProps = $objExcel->getActiveSheet();

        $names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱'];

        //循环设置
        foreach ($names as $key=>$value) {
            // 设置水平居中
            $objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            // 设置垂直居中
            $objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设置宽
            $objProps->getColumnDimension($key)->setWidth(20);
            //设置列名
            $name = $key.'1';
            $objProps->setCellValue($name, $value);
        }

        $num = 1;

        //模拟数据
        $data = [
            ['name'=>'mike','phone'=>'123','email'=>'123@.com'],
            ['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
            ['name'=>'jack','phone'=>'789','email'=>'789@.com'],
        ];
        foreach ($data as $key => $value) {
            $data = array_values( $value );
            $length = count($data);

            $data[] = '';

            $name = ['姓名','电话','邮箱',''];
            reset($name); 
            foreach ($data as $k => $v) {
                $line = ($num-1)*$length + $k+1;
                if ($k ==0) {
                    $objProps->setCellValue("A" . $line, $num);
                } else {
                    $objProps->setCellValue("A" . $line, '');
                }
                if ($k ==$length-1) {
                    $objProps->setCellValue("B" . $line, '');
                    $objProps->setCellValue("C" . $line, '');
                } else {
                    $objProps->setCellValue("B" . $line, current($name));
                    $objProps->setCellValue("C" . $line, $v); 
                }

                next($name);

            }

            $num ++;

        }



        //设输出格式
        $write = new PHPExcel_Writer_Excel5($objExcel);
        // $write = new PHPExcel_Writer_Excel2007($objExcel);

        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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
        header("Content-Transfer-Encoding:binary");

        // 保存
        $write->setPreCalculateFormulas(false);
        $write->save('php://output');

    }

3.导入

public function import()
    {
        //引用PHPexcel 类  
        require_once '/www/assets/PHPExcel/Classes/PHPExcel.php';
        require_once '/www/assets/PHPExcel/Classes/PHPExcel/IOFactory.php';
        //接收前台文件  
         $ex = $_FILES['excel'];
        $game = $_POST['game'];

         //重设置文件名
         $suffix = substr($ex['name'],stripos($ex['name'],'.'));
         $filename = time().'-'.rand(10,99).$suffix;
         $path = '/www/data/excel/'.$filename;//设置移动路径
         move_uploaded_file($ex['tmp_name'],$path);
         if ($suffix=='.xlsx') {
             $type = 'Excel2007';
         } else if ($suffix=='.xls'){
             $type = 'Excel5';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
         } else {
             header('Content-type: application/json');
             echo json_encode(['msg'=>'BAD_PARAM','echo'=>'']);
             exit;
         }
//        $type = 'Excel5';
        $xlsReader = PHPExcel_IOFactory::createReader($type);
        $xlsReader->setReadDataOnly(true);
        $xlsReader->setLoadSheetsOnly(true);
        //测试用路径
//        $path = '/www/data/excel/1492046414-94.xls';
        $Sheets = $xlsReader->load($path);  
        //开始读取上传到服务器中的Excel文件，返回一个二维数组  
        $dataArray = $Sheets->getSheet(0)->toArray();


        $key_filed = ['游戏'=>'game','区服'=>'server','账号'=>'user','角色名'=>'role_name','角色ID'=>'role_id','充值金额'=>'pay_amount','充值日期'=>'pay_time','平台'=>'platform','是否分成'=>'profit','联系方式'=>'contact','备注'=>'info','提交状态'=>'sub_status','回访状态'=>'visit_status','录入人'=>'oprater','录入时间'=>'create_time','最后修改'=>'update_time'];
        //第一个数组为文档首行字段
        $excelField = [];//排序后的字段
        foreach ( $dataArray[0] as $key=>$value) {
            $excelField[] = $key_filed[$value];
        }
        for ($i=1;$i<count($dataArray);$i++) {
            $check_data = array_combine($excelField, $dataArray[$i]);
            $check_result = $this->checkField($check_data);
            if ($check_result) {
                //PHPExcel类读取Excel文件得到数组数据类型可能存在number，用array_map函数转换每个值成string
                $string_data = array_map([$this, "valueToString"], $dataArray[$i]);
                $data = array_combine($excelField, $string_data);
                $this->mongodb->selectDB("welfare_rebate");
                $rebateCollection = $this->mongodb->getCollection('rebate_record');
                $data['game'] = $game;
                $data['create_time'] = time();
                $data['update_time'] = time();
                $data['oprater'] = $this->authorize->getId2();
                $rebateCollection->insert($data);
            }
        }
        header('Content-type: application/json');
        echo json_encode(['msg'=>'OK']);
    }