您好,欢迎来到互劦cms 官方网站-内容管理系统,让你体验不一样的建站方式,更简单,更安全,更灵活
  • 登陆-注册
  • 使用手册
  • 常见问题
  • 全国服务热线:18173153117

    软件名称:thinkphp 5.1x excel导出导入插件
    软件大小: 8.82M
    支持系统:windows/linux
    下载地址:本地下载
    在K:\xampp\htdocs\tp51\目录下执行:
    composer require phpoffice/phpspreadsheet

    composer require phpoffice/phpexcel


    1. use PhpOffice\PhpSpreadsheet\Spreadsheet;
    2. use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    3. use PhpOffice\PhpSpreadsheet\IOFactory;


    use PHPExcel_IOFactory;
    use PHPExcel;


    /**
    * @throws \PHPExcel_Exception
    * @throws \PHPExcel_Reader_Exception
    * 导出数据为excel文件
    */
    public function outportExcel(){
    $param=new Param();
    $getParam=request()->get();
    if(!$param->check($getParam)){
    return Returninfo::return_info($param->getError(),0);
    }
    if(empty($getParam['store_id'])){
    return Returninfo::return_info('需要参数(门店id)如:url?store_id=门店id',0);
    }
    if(empty($getParam['hotel_id'])){
    return Returninfo::return_info('需要参数(酒店id)如:url?hotel_id=酒店id',0);
    }
    if(empty($getParam['closing_period'])){
    return Returninfo::return_info('需要参数(结算周期日期)如:url?closing_period=结算周期日期',0);
    }
    $where[]=['store_id','eq',intval($getParam['store_id'])];
    $where[]=['hotel_id','eq',intval($getParam['hotel_id'])];
    $where[]=[['pay_datetime','egt',$getParam['closing_period']." 00:00:00"],['pay_datetime','elt',$getParam['closing_period']." 23:59:59"]];
    $data=mdod::order_out_account_detail($where,0,'id desc','id,order_no,goods_status,goods_name,small_routine_name,receiving_phone,user_name,receiving_name,store_name,pay_amount,create_datetime,pay_datetime,store_id,hotel_id,goods_num,unitprice');
    $objPHPExcel = new \PHPExcel();
    $objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
    $objPHPExcel->getActiveSheet()->mergeCells('C1:D1');
    $objPHPExcel->getActiveSheet()->mergeCells('E1:F1');
    $objPHPExcel->getActiveSheet()->mergeCells('G1:H1');
    $objPHPExcel->getActiveSheet()->mergeCells('A2:B2');
    $objPHPExcel->getActiveSheet()->mergeCells('C2:D2');
    $objPHPExcel->getActiveSheet()->mergeCells('E2:F2');
    $objPHPExcel->getActiveSheet()->mergeCells('G2:H2');
    $objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
    $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
    $objPHPExcel->getActiveSheet()->mergeCells('E3:F3');
    $objPHPExcel->getActiveSheet()->mergeCells('G3:H3');
    $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
    $objPHPExcel->getActiveSheet()->mergeCells('C4:D4');
    $objPHPExcel->getActiveSheet()->mergeCells('E4:F4');
    $objPHPExcel->getActiveSheet()->mergeCells('G4:H4');
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);//行宽
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getStyle('A1:K1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('C1:D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('E1:F1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('G1:H1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('A1:K1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中

    $objPHPExcel->getActiveSheet()->getStyle('A2:B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('C2:D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('E2:F2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('G2:H2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中

    $objPHPExcel->getActiveSheet()->getStyle('A3:B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('C3:D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('E3:F3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('G3:H3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中

    $objPHPExcel->getActiveSheet()->getStyle('A4:B4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('C4:D4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('E4:F4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//水平居右
    $objPHPExcel->getActiveSheet()->getStyle('G4:H4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//水平居左
    $objPHPExcel->getActiveSheet()->getStyle('A4:K4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中


    $objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
    $objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
    $objPHPExcel->getActiveSheet()->setCellValue('A' . 1,"结算周期:");
    $objPHPExcel->getActiveSheet()->setCellValue('C' . 1,$data['data']['oacdetail']['closing_period']);
    $objPHPExcel->getActiveSheet()->setCellValue('E' . 1,"订单金额:");
    $objPHPExcel->getActiveSheet()->setCellValue('G' . 1,$data['data']['oacdetail']['order_amount']);

    $objPHPExcel->getActiveSheet()->setCellValue('A' . 2,"状态:");
    $objPHPExcel->getActiveSheet()->setCellValue('C' . 2, OutAccount_status_numtocn($data['data']['oacdetail']['status']));
    $objPHPExcel->getActiveSheet()->setCellValue('E' . 2,"酒店佣金:");
    $objPHPExcel->getActiveSheet()->setCellValue('G' . 2,$data['data']['oacdetail']['hotel_commission']);

    $objPHPExcel->getActiveSheet()->setCellValue('A' . 3,"对账方名称:");
    $objPHPExcel->getActiveSheet()->setCellValue('C' . 3,$data['data']['oacdetail']['store_name']);
    $objPHPExcel->getActiveSheet()->setCellValue('E' . 3,"平台佣金:");
    $objPHPExcel->getActiveSheet()->setCellValue('G' . 3,$data['data']['oacdetail']['platform_commissions']);

    $objPHPExcel->getActiveSheet()->setCellValue('A' . 4,"对账方id:");
    $objPHPExcel->getActiveSheet()->setCellValue('C' . 4,$data['data']['oacdetail']['store_id']);
    $objPHPExcel->getActiveSheet()->setCellValue('E' . 4,"结算金额:");
    $objPHPExcel->getActiveSheet()->setCellValue('G' . 4,$data['data']['oacdetail']['settlement_amount']);
    //$objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true);//配合换号\n字符需要双引号包含才可以换行
    $objPHPExcel->getActiveSheet()->setCellValue('A' . 5,'订单创建时间');
    $objPHPExcel->getActiveSheet()->setCellValue('B' . 5,'订单号');
    $objPHPExcel->getActiveSheet()->setCellValue('C' . 5,"商品名称");
    $objPHPExcel->getActiveSheet()->setCellValue('D' . 5,"商品类型");
    $objPHPExcel->getActiveSheet()->setCellValue('E' . 5,"商品数量");
    $objPHPExcel->getActiveSheet()->setCellValue('F' . 5,"商品单价");
    $objPHPExcel->getActiveSheet()->setCellValue('G' . 5,"买家用户名");
    $objPHPExcel->getActiveSheet()->setCellValue('H' . 5,"买家电话");
    $objPHPExcel->getActiveSheet()->setCellValue('I' . 5,"酒店佣金");
    $objPHPExcel->getActiveSheet()->setCellValue('J' . 5,"平台佣金");
    $objPHPExcel->getActiveSheet()->setCellValue('K' . 5,"结算金额");
        $objPHPExcel->getActiveSheet()->getStyle('A5:K5')->getFill()->applyFromArray(array(
    'type' => \PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array(
    'rgb' => 'CCCCCC'
    )
    ));//设置背景颜色
    $k=6;
    foreach($data['data']['order_list'] as $sk=>$v){
    $objPHPExcel->getActiveSheet()->getStyle('A'.$k.':k'.$k)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居右
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $k,$v['create_datetime']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $k,$v['order_no']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $k,$v['goods_name']);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $k,$v['cn_goods_status']);
    $objPHPExcel->getActiveSheet()->setCellValue('E' . $k,$v['goods_num']);
    $objPHPExcel->getActiveSheet()->setCellValue('F' . $k,$v['unitprice']);
    $objPHPExcel->getActiveSheet()->setCellValue('G' . $k,$v['user_name']);
    $objPHPExcel->getActiveSheet()->setCellValue('H' . $k,$v['receiving_phone']);
    $objPHPExcel->getActiveSheet()->setCellValue('I' . $k,$v['hotel_commission']);
    $objPHPExcel->getActiveSheet()->setCellValue('J' . $k,$v['platform_commissions']);
    $objPHPExcel->getActiveSheet()->setCellValue('K' . $k,$v['settlement_amount']);
    $k+=1;
    }
    $objPHPExcel->getActiveSheet()->setTitle('user'); //设置sheet的名称
    $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
    $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
    header('Content-Disposition: attachment;filename="汽车运价信息表.xlsx"');
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }

    站内地图

    Copyright 2018-2028 hlcms. All Rights Reserved. 湘ICP备13008873号-1