框架为tp5
namespace app\admin\controller;
use think\Controller;
use think\Request;
use think\Db;
use think\PHPExcel\PHPExcel;
use app\admin\model\ProjectModel; //获取项目信息
use app\admin\model\DealerModel; //获取经销商信息
use app\admin\model\CarModel; //获取车系信息
use app\admin\model\ChebhModel; //获取车百汇信息
use app\admin\model\ExcelModel; //获取车百汇信息
public function OutUserDeal()
{
$excel = new ExcelModel();
$user_table = input("param.user_table");//用户留资信息表
$deal_table = input("param.deal_table");//经销商表
$data = DB::name($user_table)->select();
//dump($data);die;
foreach ($data as $key => $val)
{
$data[$key]['time'] = date("Y-m-d H:i:s",$val['time']);
//如果经销商数据表不为空的话
if(!empty($deal_table))
{
if($deal_table == 'daccdealer') {
$data[$key]['dealer_name'] = Db::name($deal_table)->where('dealer_id', $val['dealer_name'])->field('dealer_name')->find()['dealer_name'];
$data[$key]['province'] = Db::name('daccdealerp')->where('dealer_id', $val['province'])->field('dealer_name')->find()['dealer_name'];
$data[$key]['city'] = Db::name('daccdealerc')->where('dealer_id', $val['city'])->field('dealer_name')->find()['dealer_name'];
}else{
//通用的导出
$data[$key]['province'] = Db::name($deal_table)->where('dealer_id',$val['province'])->field('dealer_name')->find()['dealer_name'];
$data[$key]['city'] = Db::name($deal_table)->where('dealer_id',$val['city'])->field('dealer_name')->find()['dealer_name'];
$data[$key]['dealer_name'] = Db::name($deal_table)->where('dealer_id',$val['dealer_name'])->field('dealer_name')->find()['dealer_name'];
}
}
//dump($data);die;
//判断查询来源渠道 这一块以后要进行封装 有点不合理现在
switch ($val['source'])
{
case 0:
$data[$key]['source'] = "未知来源";
break;
case 1:
$data[$key]['source'] = "PC端";
break;
case 2:
$data[$key]['source'] = "Wap移动端";
break;
case 3:
$data[$key]['source'] = "PC端(公平价)";
break;
case 4:
$data[$key]['source'] = "Wap移动端(公平价)";
break;
default:
$data[$key]['source'] = "未知来源";
break;
}
}
//dump($data);die;
//定制表头
$header = array('编号','姓名','性别','手机','省份','城市','经销商','感兴趣车型','来源渠道','注册时间','购车时间');
//dump($data);
foreach ($data as $key => $value)
{
$res = array_key_exists('buy_car_time',$value);
//dump($res);die;
if($value['sex'] ==1){
$sex = '男';
}elseif($value['sex'] ==2){
$sex = '女';
}elseif($value['sex'] == '男'){
$sex = '男';
}elseif($value['sex'] == '女'){
$sex = '女';
}else{
$sex= '未选择';
}
if(true == $res){
if($value['buy_car_time']==null){
$buy_car_time = '暂无';
}else{
$buy_car_time = $value['buy_car_time'];
}
$dataArr[] = array(
'user_id'=>$value['user_id'],
'name'=>$value['name'],
'sex'=>$sex,
'mobile'=>$value['mobile'],
'province'=>$value['province']?$value['province']:'暂无',
'city'=>$value['city']?$value['city']:'暂无',
'dealer_name'=>$value['dealer_name'],
'car_type'=>$value['car_type'],
'source'=>$value['source'],
'time'=>$value['time'],
'buy_car_time'=>$buy_car_time,
);
}else{
if($value['sex'] ==1){
$sex = '男';
}elseif($value['sex'] ==2){
$sex = '女';
}elseif($value['sex'] == '男'){
$sex = '男';
}elseif($value['sex'] == '女'){
$sex = '女';
}else{
$sex= '未选择';
}
if($value['car_type']==null){
$car_type='暂无';
}else{
$car_type=$value['car_type'];
}
// if($value['buy_car_time']==null){
// $buy_car_time = '暂无';
// }else{
// $buy_car_time = $value['buy_car_time'];
// }
$dataArr[] = array(
'user_id'=>$value['user_id'],
'name'=>$value['name'],
'sex'=>$sex,
'mobile'=>$value['mobile'],
// 'province'=>$value['province']?$value['province']:'暂无',
// 'city'=>$value['city']?$value['city']:'暂无',
// 'dealer_name'=>$value['dealer_name'],
'car_type'=>$car_type,
'source'=>$value['source'],
'time'=>$value['time'],
);
}
}
/*
* $dataArr的值如下
* array(3) {
[0] => array(7) {
["user_id"] => int(2)
["name"] => string(6) "张三"
["sex"] => string(9) "未选择"
["mobile"] => string(11) "18569365106"
["car_type"] => string(6) "丰田"
["source"] => string(5) "PC端"
["time"] => string(19) "2017-09-22 20:42:43"
}
[1] => array(7) {
["user_id"] => int(3)
["name"] => string(6) "李某"
["sex"] => string(9) "未选择"
["mobile"] => string(11) "13129009821"
["car_type"] => string(6) "宝马"
["source"] => string(5) "PC端"
["time"] => string(19) "2017-09-25 09:38:01"
}
[2] => array(7) {
["user_id"] => int(6)
["name"] => string(6) "魏辉"
["sex"] => string(3) "女"
["mobile"] => string(11) "15237362653"
["car_type"] => string(6) "奥迪"
["source"] => string(5) "PC端"
["time"] => string(19) "2017-10-11 17:52:29"
}
}
*/
//导出
$excel->writer($header,$dataArr);//导出
/*
* 模型里的write()方法如下
static function writer($header, $data,$name=false,$type = 0) {
//导出
$result = import("PHPExcel",EXTEND_PATH.'PHPExcel');
if(!$name){$name=date("Y-m-d-H-i-s",time());}
$objPHPExcel = new \PHPExcel();
$objProps = $objPHPExcel->getProperties();
//设置表头
$key = ord("A");
foreach($header as $v){
$colum = chr($key);
$objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum.'1', $v);
$key += 1;
}
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
$objActSheet->getRowDimension(1)->setRowHeight(20);
foreach($data as $key => $rows){ //行写入
$span = ord("A");
foreach($rows as $keyName=>$value) {// 列写入
$j = chr($span);
$objActSheet->getRowDimension($column)->setRowHeight(20);
$objActSheet->setCellValue($j.$column, $value);
$span++;
}
$column++;
}
$objPHPExcel->getActiveSheet()->setTitle('chen.data');
$objPHPExcel->setActiveSheetIndex(0);
$fileName = iconv("utf-8", "gb2312", './Data/excel/'.date('Y-m-d_', time()).time().'.xls');
$saveName = iconv("utf-8", "gb2312", $name.'.xls');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
if ($type == 0) {
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$saveName\"");
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
} else {
$objWriter->save($fileName);
return $fileName;
}
}
*/
}
