xinyi_admin/application/common/service/ServiceExcel.php

<?php
/**
 * Created by PhpStorm.
 * User: vowkin
 * Date: 2017/5/22
 * Time: 21:49
 */

namespace app\common\service;


use SebastianBergmann\CodeCoverage\Report\PHP;

class ServiceExcel
{
    public static function excel_exchange($list, $excel_name, $code)
    {
        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        date_default_timezone_set('PRC');
        ini_set('memory_limit','1024M');//设置导出最大内存
        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');

        $excel = new ServiceExcel();
        $Ym = date('Y-m-d', time());
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中设置


        $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode("0");
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode("0");
        $obj = $objPHPExcel->getActiveSheet();
        $obj->setTitle($Ym . $excel_name);
        $AZ = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU',
        ];

        switch ($code) {
            case 'order_list':
                $excel->excel_order($obj, $list, $AZ);
                break;
            case 'financeOrder'://财务订单
                $excel->excel_financeOrder($obj, $list, $AZ);
                break;
            case 'interOrder'://接口订单
                $excel->excel_interOrder($obj, $list, $AZ);
                break;
            case 'logisticsOrder'://物流订单
                $excel->excel_logisticsOrder($obj, $list, $AZ);
                break;
            case 'userList':
                $excel->excel_userList($obj, $list, $AZ);
                break;
            case 'cashierOrder':
                $excel->excel_cashierList($obj, $list, $AZ);
                break;
            case 'doctorOrder':
                $excel->excel_doctorOrderList($obj, $list, $AZ);
                break;
        }
//        halt($objPHPExcel);
        $exportName=$excel_name.' '.date('Y-m-d His',time()).'.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$exportName.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    //导出商品信息列表
    private function excel_order($obj, $list, $AZ)
    {
        $i = 1;
        $j = 1;
        $col_index = [
            'parent' => ['add_time' => 1, 'order_no' => 2, 'order_status' => 3, 'product_number' => 4, 'total_number' => 5],
            'child' => ['product_name' => 5, 'erp_no' => 6,'product_price'=>7],
        ];//需要导出内容
        $titles = ['下单时间', '订单编号', '买家会员','商品名称', '货号', '数量', '单价', '总价', '运费', '应付总额',
            '返积分','实付金额','支付方式','支付时间','支付流水号','收货人','收货地址','联系方式','配送方式','下单门店',
            '绑定导购员','提成佣金','是否发生退款','退款总金额','退款商品金额'];

        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $info_arr){
            foreach($info_arr['child'] as $col=>$v){
                foreach ($info_arr as $c=>$info){
                    if(!isset($col_index['parent'][$c])){
                        continue;
                    }
                $obj->setCellValue($AZ[$col_index['parent'][$c]].$i, $info);
            }
            foreach ($v as $a=>$b){
                if(!isset($col_index['child'][$a])){
                    continue;
                }
                $obj->setCellValue($AZ[$col_index['child'][$a]].$i, $b);
            }
            $i++;
        }
//            if(!empty($info_arr['child'])){
//////                echo '开始：'.$q.'<br>';
////                dump('E'.$q.':E'.$i);
//                $obj->mergeCells('B'.$q.':B'.$i);
//                $q=$i+1;
////                $i=$q-1;
//            }
        }
//        exit;
    }

    //导出财务订单列表
    private function excel_financeOrder($obj, $list, $AZ)
    {
        $i = 1;
        $col_index = [
            'parent' => [
                'add_time' => 0, 'order_no' => 1, 'order_status' => 2, 'real_name' => 3,
                'total_price' => 8, 'logistics_price' => 9, 'Should_price' => 10, 'wx_price' => 11, 'pay_style' => 12, 'pay_time' => 13, 'pay_no' => 14,
                'consignee' => 15, 'order_address' => 16, 'address_mobile' => 17, 'order_type' => 18, 'store' => 19, 'point' => 21, 'sales_status' => 22, 'sales_price' => 23,'base_label_name'=>24
            ],
            'child' => ['add_time' => 0, 'order_no' => 1, 'order_status' => 2, 'real_name' => 3,'product_name' => 4, 'erp_no' => 5, 'product_number' => 6, 'product_price' => 7, 'doctor_no' => 20,'base_label_name'=>24],
            //这是为重复的字段定义的
            'child1' => ['add_time' => 0, 'order_no' => 1, 'order_status' => 2, 'real_name' => 3,
                'pay_style' => 12, 'pay_time' => 13, 'pay_no' => 14, 'consignee' => 15, 'order_address' => 16, 'address_mobile' => 17, 'order_type' => 18,
                'store' => 19,'base_label_name'=>24
                ],
        ];//需要导出内容
        $titles = ['下单时间', '订单编号','订单状态','买家会员','商品名称', '货号', '数量', '单价', '商品总价', '运费', '应付总额',
            '实付金额','支付方式','支付时间','支付流水号','收货人姓名','收货地址','联系方式','配送方式','下单门店',
            '绑定导购员','赠送会员积分','是否发生退款','退款总金额','商品分组标签'];
        $is_hb=[
            'add_time' => 1, 'order_no' => 1, 'order_status' => 0, 'real_name' => 0,
            'total_price' => 0, 'logistics_price' => 0, 'Should_price' => 0, 'wx_price' => 0, 'pay_style' => 0, 'pay_time' => 0, 'pay_no' => 0,
            'consignee' => 0, 'order_address' => 0, 'address_mobile' => 0, 'order_type' => 0, 'store' => 0, 'point' => 0, 'sales_status' => 0, 'sales_price' => 0,
            'product_name' => 1, 'erp_no' => 1, 'product_number' => 1, 'product_price' => 1, 'doctor_no' => 1
        ];
        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $k=>$info_arr) {
            foreach ($info_arr as $c => $info) {
                if (!isset($col_index['parent'][$c])) {
                    continue;
                }
                $obj->setCellValueExplicit($AZ[$col_index['parent'][$c]] . $i, ' '.$info);
            }

            foreach ($info_arr['child'] as $col => $v) {
                foreach ($info_arr as $c => $info) {
                    if (!isset($col_index['child1'][$c])) {
                        continue;
                    }
                    $obj->setCellValue($AZ[$col_index['child1'][$c]] . $i,' '.$info);
                    $a=$i+1;
                    $obj->setCellValue('I' . $a,0);
                    $obj->setCellValue('J' . $a,0);
                    $obj->setCellValue('K' . $a,0);
                    $obj->setCellValue('L' . $a,0);
                    $obj->setCellValue('K' . $a,0);
                }
                $v['product_price'] = $v['product_price'] / 100;
                $v['doctor_no'] = $v['doctor_no']==0?'':$v['doctor_no'];
                foreach ($v as $a => $b) {
                    if (!isset($col_index['child'][$a])) {
                        continue;
                    }
                    $obj->setCellValue($AZ[$col_index['child'][$a]] . $i,' '.$b);
                }
                $i++;
            }
        }
    }

    //导出接口订单列表
    private function excel_interOrder($obj, $list, $AZ)
    {
        $i = 1;
        $col_index = [
            'parent' => [
                'add_time' => 0, 'order_no' => 1, 'order_status' => 2,
                'total_price' => 7,'consignee' => 8,'address_mobile' => 9,'base_label_name'=>10
            ],
            'child' => ['product_name' => 3, 'erp_no' => 4, 'product_number' => 5, 'product_price' => 6,'base_label_name'=>10],
            'child1' => ['add_time' => 0, 'order_no' => 1, 'order_status' => 2,'consignee' => 8,'address_mobile' => 9,'base_label_name'=>10],
        ];//需要导出内容

        $titles = [
            '下单时间', '订单编号','订单状态','商品名称', '货号', '数量', '单价', '商品总价','收货人', '联系电话','商品分组标签'
        ];
        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $k=>$info_arr) {
            foreach ($info_arr as $c => $info) {
                if (!isset($col_index['parent'][$c])) {
                    continue;
                }
                $obj->setCellValue($AZ[$col_index['parent'][$c]] . $i, ' '.$info);
            }

            foreach ($info_arr['child'] as $col => $v) {
                foreach ($info_arr as $c => $info) {
                    if (!isset($col_index['child1'][$c])) {
                        continue;
                    }
                    $obj->setCellValue($AZ[$col_index['child1'][$c]] . $i, ' '.$info);
                    $a=$i+1;
                    $obj->setCellValue('H' . $a,0);
                }
                $v['product_price'] = $v['product_price'] / 100;
                $v['doctor_no'] = $v['doctor_no']==0?'':$v['doctor_no'];
                foreach ($v as $a => $b) {
                    if (!isset($col_index['child'][$a])) {
                        continue;
                    }
                    $obj->setCellValue($AZ[$col_index['child'][$a]] . $i, $b);
                }
                $i++;
            }
        }
    }

    //物流订单
    public function excel_logisticsOrder($obj, $list, $AZ){
        $i = 1;
        $j = 1;
        $col_index = [
            'order_no' => 0, 'consignee1' => 1, 'consignee2' => 2, 'address_mobile1' => 3, 'address_mobile2' => 4,'address'=>5,'total_number'=>6,'note'=>7,'admin_note'=>8
        ];//需要导出内容
        $titles = ['订单编号', '收货人姓名', '收货人姓名','收货人手机号', '收货人手机号', '收货地址', '订单的商品总数', '买家备注', '卖家备注'];
        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        $q=2;
        foreach($list as $info_arr){
            foreach ($info_arr as $c=>$info){
                if(!isset($col_index[$c])){
                    continue;
                }
                $obj->setCellValue($AZ[$col_index[$c]].$i, ' '.$info);
            }
            $i++;
        }
    }

    //会员信息导出
    public function excel_userList($obj, $list, $AZ){
        $i = 1;
        $col_index = [
            'user_no' => 0, 'real_name' => 1, 'user_name' => 2, 'mobile' => 3, 'gender' => 4,'age'=>5,'opend_id'=>6,'type'=>7,
            'total_score'=>8,'usable_score'=>9,'add_ts'=>10,'status'=>11,'birthday'=>12,'province'=>13,'lable'=>14
        ];//需要导出内容
        $titles = ['会员编号', '真实姓名', '会员名称','手机号码', '性别', '年龄', 'open_id', '会员类型', '会员总积分',
        '可用积分','注册时间','会员状态','出生年月','省市区','会员标签'
        ];

        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $info_arr){
            foreach ($info_arr as $c=>$info){
                if(!isset($col_index[$c])){
                    continue;
                }
                $obj->setCellValue($AZ[$col_index[$c]].$i, ' '.$info);
            }
            $i++;
        }
    }

    //收款信息导出
    public function excel_cashierList($obj, $list, $AZ){
        $i = 1;
        $col_index = [
            'add_ts' => 0, 'order_no' => 1, 'price' => 2, 'pay_price' => 3, 'refund_price' => 4,'note'=>5,'update_ts'=>6,
            'pay_type'=>7,'body'=>8,'pay_no'=>9,'status'=>10
        ];//需要导出内容
        $titles = ['收款时间', '订单编号', '应付金额','实付金额', '退款金额', '备注信息', '修改时间', '支付类型', '支付信息','商户编号','收款状态'];

        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $info_arr){
            foreach ($info_arr as $c=>$info){
                if(!isset($col_index[$c])){
                    continue;
                }
                $obj->setCellValue($AZ[$col_index[$c]].$i, ' '.$info);
            }
            $i++;
        }
    }

    //导购订单导出
    public function excel_doctorOrderList($obj, $list, $AZ){
        $i = 1;
        $col_index = [
            'parent' => [
                'add_time' => 0, 'order_no' => 1, 'order_status' => 2, 'real_name' => 3,
                'total_price' => 8, 'logistics_price' => 9, 'Should_price' => 10, 'wx_price' => 11, 'pay_style' => 12, 'pay_time' => 13,
            ],
            'child' => ['product_name' => 4, 'erp_no' => 5, 'product_number' => 6, 'actual_price' => 7, 'doctor_no' => 14,'doctor_name'=>15],
        ];//需要导出内容

        $titles = ['下单时间', '订单编号', '订单状态','买家会员', '商品名称', '货号', '数量', '导购单价', '商品总价','运费','应付金额','实付金额','支付方式','支付时间','导购编号','导购名称'];

        foreach ($titles as $index => $v) {//标题填充
            $obj->setCellValue($AZ[$index] . $i, $v);
        }
        $i = 2;
        foreach($list as $k=>$info_arr) {
            foreach ($info_arr as $c => $info) {
                if (!isset($col_index['parent'][$c])) {
                    continue;
                }
                $a[]=$info;
                $obj->setCellValueExplicit($AZ[$col_index['parent'][$c]] . $i, ' '.$info);
            }
            foreach ($info_arr as $c => $info) {
                if (!isset($col_index['child'][$c])) {
                    continue;
                }
                $a[]=$info;
                $obj->setCellValueExplicit($AZ[$col_index['child'][$c]] . $i, ' '.$info);
            }
            $i++;
        }
    }

    //excel表格导入信息
    public static function excel_import($file_name){
        $file = request()->file('file');
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['size'=>3145728,'ext'=>'xls,csv,xlsx'])->move(ROOT_PATH . 'public' . DS . 'uploads/'.$file_name);
        if($info) {
            $filename = $info->getSaveName();
            $filenames = ROOT_PATH . 'public' . DS . 'uploads/'.$file_name.'/' . $filename;
            vendor("PHPExcel.PHPExcel");
            $filetype = \PHPExcel_IOFactory::identify($filenames);//自动获取文件类型
            $objReader = \PHPExcel_IOFactory::createReader($filetype);//获取文件读取对象
            $objPHPExcel = $objReader->load($filenames);//加载excel文件
            $name = $objPHPExcel->getsheetNames();
            $sheetn = end($name);
            $objReader->setLoadSheetsOnly($sheetn);
            $objPHPExcel = $objReader->load($filenames);
            $sheetname = $objPHPExcel->getSheet();//获取当前sheet
            $data = $sheetname->toArray();
            if(!empty($data)){
                unset($data[0]);
                return $data;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
}