PHP转excel代码

public function reportings(){
$er=I("post.");
$time=$er["time"];
$times=strtotime("$time");
var_dump($times);
$time1=$er["time1"];
$times1=strtotime("$time1");
var_dump($er);

var_dump($times1);
$data=M("balance_user")->alias('a')->join("agency_user_list b ON b.user_id=a.user_id")->where("deal_time>$times and deal_time<$times1")->select();

$this->xls($data);


$this->assign("re",$re);
$this->display();
$this->xls($data);
}
//导出
public function dal(){
$data=I("post.");
var_dump($data);

$this->display();

}

 

//php转excel封装方法
function xls($data){
Vendor('PHPExcel.PHPExcel');
$phpexcel = new \PHPExcel();
$objWriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel2007');

//设置默认的字体和文字大小
$phpexcel->getDefaultStyle()->getFont()->setName( '微软雅黑');
$phpexcel->getDefaultStyle()->getFont()->setSize(12);


$objSheet = $phpexcel->getActiveSheet();
$objSheet->setCellValue('A1','编号')
->setCellValue('B1','用户名')
->setCellValue('C1','流水类型')
->setCellValue('D1','支付支出')
->setCellValue('E1','时间');

//自动换行
$objSheet->getStyle('K1')->getAlignment()->setWrapText(true);

foreach($data as $k=>$v){
if($v['type']==0){
$v['type']="充值";
}
if($v['type']==1){
$v['type']="支付";
}
if($v['type']==2){
$v['type']="提现";
}
if($v['type']==3){
$v['type']="返佣";
}
$num = $k+2;
$objSheet
->setCellValue('A'.$num,$v['balance_id'])
->setCellValue('B'.$num,$v['name'])
->setCellValue('C'.$num,$v['type'])
->setCellValue('D'.$num,$v['money'])
->setCellValue('E'.$num,date("Y年m月d日",$v['trade_time']));

}
$outfile = "workerList.xls";
$objSheet->setTitle('业绩表');
$phpexcel->setActiveSheetIndex(0);

//垂直居中
$objSheet->getStyle('A1:Q1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

//设置列宽
$objSheet->getColumnDimension('A:Q')->setWidth('20');

//设置单元格边框
$styleArray = array(
'borders' => array(
'allborders' => array(
//'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
//'color' => array('argb' => 'FFFF0000'),
),
),
);
$n = 500;
$objSheet->getStyle('A1:A'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('B1:B'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('C1:C'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('D1:D'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('E1:E'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('F1:F'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('G1:G'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('H1:H'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('I1:I'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('J1:J'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('K1:K'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('L1:L'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('M1:M'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('N1:N'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('O1:O'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('P1:P'.$n)->applyFromArray($styleArray);
$objSheet->getStyle('Q1:Q'.$n)->applyFromArray($styleArray);

//设置单元格背景

//设置字体颜色
$objSheet->getStyle('J1:J'.$n)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);

//水平居中
$objSheet->getStyle('A1:A'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('B1:B'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('C1:C'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('D1:D'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('E1:E'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('F1:F'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('G1:G'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('H1:H'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('I1:I'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('J1:J'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('K1:K'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('L1:L'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('M1:M'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('N1:N'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('O1:O'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('P1:P'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objSheet->getStyle('Q1:Q'.$n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//单元格宽度
$objSheet->getColumnDimension('A')->setWidth(23);
$objSheet->getColumnDimension('B')->setWidth(12);
$objSheet->getColumnDimension('C')->setWidth(12);
$objSheet->getColumnDimension('D')->setWidth(12);
$objSheet->getColumnDimension('E')->setWidth(22);

// 设置行高
$objSheet->getRowDimension('1')->setRowHeight(50);


ob_end_clean(); //清除缓冲区,避免乱码</span>
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$outfile.'"');
header("Content-Transfer-Encoding: binary");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
exit;
}

转载于:https://www.cnblogs.com/oy123/p/8144927.html