php/www/protected/services/ExcelToArrayService.php

<?php
/**
 * Created by PhpStorm.
 * User: PHP
 * Date: 2016/11/28
 * Time: 16:20
 */
class ExcelToArrayService extends Service
{


    /**
     * @param $data 数据
     * @param string $name 导出的excel名称
     * @param $export 导出的类型  execute_export 执行订单汇总 order订单明细 tech保洁师明细
     */
    public function push($data, $name = 'Excel',$export) {
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $channel_option = ROrder::$channel_option;
        $objPHPExcel = new PHPExcel();
        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("north")
            ->setLastModifiedBy("north")
            ->setTitle("数据EXCEL导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("备份数据")
            ->setKeywords("excel")
            ->setCategory("result file");
        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        $num = 1;
        if($export == 'execute_order'){
            $objPHPExcel->setActiveSheetIndex(0)
                //Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, '服务日期')
                ->setCellValue('B' . $num, '时间')
                //->setCellValue('C' . $num, '数量')
                //->setCellValue('D' . $num, '使用的代金券')
                ->setCellValue('C' . $num, '联系人')
                ->setCellValue('D' . $num, '联系方式')
                //->setCellValue('G' . $num, 'charge_id')
                ->setCellValue('E' . $num, '所在区')
                ->setCellValue('F' . $num, '地址')
                //->setCellValue('F' . $num, '订单处理时间')
                ->setCellValue('G' . $num, '服务项目')
                ->setCellValue('H' . $num, '居室')
                ->setCellValue('I' . $num, '保洁师')
                ->setCellValue('J' . $num, '备注')
                ->setCellValue('K'.$num,'支付方式')
                ->setCellValue('L'.$num,'本单实收')
                ->setCellValue('M'.$num,'本单营收')
            ;
            $num += 1;
            foreach($data as $key => $value){
                if(!empty($value['pay_channel'])){
                    $pay_option = $channel_option[$value['pay_channel']]['name'];
                } else {
                    $pay_option = '';
                }


                $technicians = '';
                $server_date = date('Y-m-d',$value['booking_time']);
                $server_time = date('H:i:s',$value['booking_time']);

                foreach($value['technicians'] as $k => $v){
                    $technicians .= $v['technician_name'].'  ';
                }
                if (!empty($value['products'][0]['extra']['type']) && !empty($value['products'][0]['extra']['type'])){
                    $product_str = $value['products_str'].$value['products'][0]['extra']['type'].$value['products'][0]['extra']['price'];
                } else {
                    $product_str = $value['products_str'];
                }
                //var_dump();exit;.
                $objPHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，uid是你查出数组的键值，下面以此类推
                    ->setCellValue('A' . $num, $server_date)
                    ->setCellValue('B' . $num, $server_time)
                    ->setCellValue('C' . $num, $value['address']['name'])
                    // ->setCellValue('D' . $num, $coupons)
                    //->setCellValue('C' . $num, $value['address']['city'].$value['address']['area'].$value['address']['poi']['name']
                    //.$value['address']['detail'].$value['address']['name'].'，手机号'.$value['address']['mobile'])
                    ->setCellValue('D' . $num, $value['address']['mobile'])
                    // ->setCellValue('G' . $num, $value['charge_id'])
                    ->setCellValue('E' . $num, $value['address']['area'])
                    ->setCellValue('F' . $num, $value['address']['poi']['name'].$value['address']['detail'])
                    ->setCellValue('G' . $num, $value['products_str'])
                    ->setCellValue('H' . $num, $value['products'][0]['extra']['type'])
                    ->setCellValue('I' . $num, $technicians)
                    ->setCellValue('J' . $num, $value['memo'].$value['remark'])
                    ->setCellValue('K'.$num,$pay_option)
                    ->setCellValue('L'.$num,$value['sum_price'])
                    ->setCellValue('M'.$num,$value['sum_price'])
                ;
                $num += 1;
            }
        }
        else if($export == 'tech'){
            $objPHPExcel->setActiveSheetIndex(0)
                //Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, '服务日期')
                ->setCellValue('B' . $num, '时间')
                ->setCellValue('C' . $num, '地址')
                ->setCellValue('D' . $num, '服务项目')
                ->setCellValue('E' . $num, '保洁师')
                ->setCellValue('F' . $num, '备注')
                ->setCellValue('G'.$num,'每人实收')
                ->setCellValue('H'.$num,'每人营收')
                ->setCellValue('I'.$num,'车费')
            ;
            $num += 1;
            foreach($data as $key => $value){
                $technicians = '';
                $server_date = date('Y-m-d',$value['booking_time']);
                $server_time = date('H:i:s',$value['booking_time']);
                $tech_count = count($value['technicians'])>0?count($value['technicians']):1;
                foreach($value['technicians'] as $k => $v){
                    $technicians .= $v['technician_name'].'  ';

                }
                $objPHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，uid是你查出数组的键值，下面以此类推
                    ->setCellValue('A' . $num, $server_date)
                    ->setCellValue('B' . $num, $server_time)
                    ->setCellValue('C' . $num, $value['address']['poi']['name'].$value['address']['detail'])
                    ->setCellValue('D' . $num, $value['products_str'])
                    ->setCellValue('E' . $num, $technicians)
                    ->setCellValue('F' . $num, $value['memo'].$value['remark'])
                    ->setCellValue('G' . $num, $value['sum_price']/$tech_count)
                    ->setCellValue('H' . $num, $value['sum_price']/$tech_count)
                    ->setCellValue('I' . $num, $value['tech_money'])
                ;
                $num += 1;
            }
        }
        else if($export == 'order'){
            $objPHPExcel->setActiveSheetIndex(0)
                //Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, 'id')
                ->setCellValue('B' . $num, '订单号')
                ->setCellValue('C' . $num, '服务日期')
                ->setCellValue('D' . $num, '下单日期')
                ->setCellValue('E' . $num, '联系人')
                ->setCellValue('F' . $num, '联系方式')
                ->setCellValue('G' . $num, '所在区')
                ->setCellValue('H' . $num, '地址')
                ->setCellValue('I' . $num, '保洁师')
                ->setCellValue('J' . $num, '备注')
            ;
            $num += 1;
            foreach($data as $key => $value){

                $technicians = '';
                $server_date = date('Y-m-d',$value['booking_time']);
                $server_time = date('H:i:s',$value['booking_time']);

                $order_id =$value['products'][0]['product']['type'].'-'.$value['order_time'];
                foreach($value['technicians'] as $k => $v){
                    $technicians .= $v['technician_name'].'  ';
                }

                //var_dump();exit;.
                $objPHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，uid是你查出数组的键值，下面以此类推
                    ->setCellValue('A' . $num, $value['id'])
                    ->setCellValue('B' . $num, $order_id)
                    ->setCellValue('C' . $num, $server_date)
                    ->setCellValue('D' . $num, $value['order_time_str'])
                    ->setCellValue('E' . $num, $value['address']['name'])
                    ->setCellValue('F' . $num, $value['address']['mobile'])
                    ->setCellValue('G' . $num, $value['address']['area'])
                    ->setCellValue('H' . $num, $value['address']['poi']['name'].$value['address']['detail'])
                    ->setCellValue('I' . $num, $technicians)
                    ->setCellValue('J' . $num, $value['memo'].$value['remark'])

                ;
                $num += 1;
            }
        }
        else if ($export == 'reserve') {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $num, '用户名');
                //Excel的第A列，uid是你查出数组的键值，下面以此类推

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $num, '预产期');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $num, '手机号');
            ;

            $num += 1;
            foreach($data as $key => $value){

                $objPHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，uid是你查出数组的键值，下面以此类推
                    ->setCellValue('A' . $num, $value['user_name'])
                    ->setCellValue('B' . $num, $value['num'])
                    ->setCellValue('C' . $num, $value['mobile'])
                ;
                $num += 1;
            }
        }
        else if ($export == 'porder') {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $num, '工作人员');
            //Excel的第A列，uid是你查出数组的键值，下面以此类推

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $num, '进房时间');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $num, '出房时间');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $num, '工作时长(小时)');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $num, '派单时间');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $num, '服务类型');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $num, '工单状态');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $num, '楼号');
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $num, '房号');
            ;

            $num += 1;
            foreach($data as $key => $value){

                $objPHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，uid是你查出数组的键值，下面以此类推
                    ->setCellValue('A' . $num, $value['user_name'])
                    ->setCellValue('B' . $num, $value['in_room_time'])
                    ->setCellValue('C' . $num, $value['out_room_time'])
                    ->setCellValue('D' . $num, $value['time'])
                    ->setCellValue('E' . $num, $value['order_time'])
                    ->setCellValue('F' . $num, $value['service_standards'])
                    ->setCellValue('G' . $num, $value['status'])
                    ->setCellValue('H' . $num, $value['floor'])
                    ->setCellValue('I' . $num, $value['room'])
                ;
                $num += 1;
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle('User');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

}