php到处excel
转自:https://www.cnblogs.com/mfyngu/p/10864662.html
首先下载phpExcel 文件 https://pan.baidu.com/s/1ITnaVJ1n6pujku3YgMc-7w 提取码: nb4m
public function clubpeople(){
// 从数据库读取数据
$userModel = new UserModel;
$list = $userModel->getUserList();
//2.加载PHPExcle类库
$objPHPExcel = new \PHPExcel();
//4.激活当前的sheet表
$objPHPExcel->setActiveSheetIndex(0);
//5.设置表格头(即excel表格的第一行)
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '序列')
->setCellValue('B1', '用户ID')
->setCellValue('C1', '昵称')
->setCellValue('D1', '手机号')
->setCellValue('E1', '订单数')
->setCellValue('F1', '优惠券数')
->setCellValue('G1', '积分')
->setCellValue('H1', '余额')
->setCellValue('I1', '联系方式')
->setCellValue('J1', '备注')
->setCellValue('K1', '注册时间');
//设置A列水平居中 设置表格宽度
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1:K1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:K1')->getFont()->setBold(true); //第一行是否加粗
// $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('K')->setWidth(30);
for($i=0;$i<count($list);$i++){
$objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$i);
$objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$list[$i]['id']);//用户id
$objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$list[$i]['user_nickname']);//昵称
$objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$list[$i]['user_mobile']);//手机号
$objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$list[$i]['order_num']);//订单数
$objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$list[$i]['coupon_num']);//优惠券数
$objPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$list[$i]['user_integral']);//积分
$objPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$list[$i]['user_balance']);//余额
$objPHPExcel->getActiveSheet()->setCellValue('I'.($i+2),$list[$i]['contact_info']);//联系方式
$objPHPExcel->getActiveSheet()->setCellValue('J'.($i+2),$list[$i]['user_remarks']);//备注
$objPHPExcel->getActiveSheet()->setCellValue('K'.($i+2),$list[$i]['created_at']);//注册时间
}
//7.设置保存的Excel表格名称
$filename = '用户列表'.date('ymd',time()).'.xls';
//8.设置当前激活的sheet表格名称;
$objPHPExcel->getActiveSheet()->setTitle('人员名单');
//9.设置浏览器窗口下载表格
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$filename.'"');
//生成excel文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//下载文件在浏览器窗口
$objWriter->save('php://output');
exit;
}
// 字体和样式
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12); //字体大小
$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getFont()->setBold(false); //第二行是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); //第一行是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16); //第一行字体大小
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14); //第二行字体大小
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setSize(14); //第六行字体大小
$objPHPExcel->getActiveSheet()->getStyle('A11')->getFont()->setSize(14); //第十一行字体大小
// 设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3:G5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A7:G10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A12:B15')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置左对齐
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// 设置行高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); //第一行行高
//设置单元格宽度
$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()->mergeCells('A1:G1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
$objPHPExcel->getActiveSheet()->mergeCells('A6:G6');
$objPHPExcel->getActiveSheet()->mergeCells('A11:B11');
// 设置内容
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '利润核算统计')
->setCellValue('A2', '【用户充值】')
->setCellValue('A3', '费率')
->setCellValue('B3', '笔数 ')
->setCellValue('C3', '充值进账')
->setCellValue('D3', '手续费')
->setCellValue('E3', '利润')
->setCellValue('A4', '1.0%')
->setCellValue('B4', $pay_log_list['aa'])
->setCellValue('C4', $pay_log_list['bb'])
->setCellValue('D4', $pay_log_list['cc'])
->setCellValue('E4', $pay_log_list['dd'])
->setCellValue('A5', '合计')
->setCellValue('B5', $pay_log_list['ee'])
->setCellValue('C5', $pay_log_list['ff'])
->setCellValue('D5', $pay_log_list['gg'])
->setCellValue('E5', $pay_log_list['hh'])
->setCellValue('A6', '【用户提现】')
->setCellValue('A7', '类别')
->setCellValue('B7', '笔数')
->setCellValue('C7', '申请提现')
->setCellValue('D7', '手续费')
->setCellValue('E7', '快钱提现')
->setCellValue('F7', '手续费')
->setCellValue('G7', '利润')
->setCellValue('A8', '收取手续费')
->setCellValue('B8', $withdraw_list['ii'])
->setCellValue('C8', $withdraw_list['kk'])
->setCellValue('D8', $withdraw_list['ll'])
->setCellValue('E8', $withdraw_list['mm'])
->setCellValue('F8', $withdraw_list['nn'])
->setCellValue('G8', $withdraw_list['oo'])
->setCellValue('A9', '未收取手续费')
->setCellValue('B9', $withdraw_list['pp'])
->setCellValue('C9', $withdraw_list['qq'])
->setCellValue('D9', $withdraw_list['rr'])
->setCellValue('E9', $withdraw_list['ss'])
->setCellValue('F9', $withdraw_list['tt'])
->setCellValue('G9', $withdraw_list['ww'])
->setCellValue('A10', '合计')
->setCellValue('B10', $withdraw_list['uu'])
->setCellValue('C10', $withdraw_list['vv'])
->setCellValue('D10', $withdraw_list['xx'])
->setCellValue('E10', $withdraw_list['yy'])
->setCellValue('F10', $withdraw_list['zz'])
->setCellValue('G10', $withdraw_list['aaa'])
->setCellValue('A11', '【利润汇总】')
->setCellValue('A12', '充值手续费收')
->setCellValue('B12', $all_summary['bbb'])
->setCellValue('A13', '提现手续费支')
->setCellValue('B13', $all_summary['ccc'])
->setCellValue('A14', '快钱提现手续费支')
->setCellValue('B14', $all_summary['ddd'])
->setCellValue('A15', '利润')
->setCellValue('B15', $all_summary['eee']);
可参考博客:https://www.cnblogs.com/fps2tao/p/10723109.html
//写在处理的前面(了解表格基本知识,已测试)
// $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//所有单元格(行)默认高度
// $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//所有单元格(列)默认宽度
// $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);//设置行高度
// $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);//设置列宽度
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);//设置文字大小
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//设置是否加粗
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);// 设置文字颜色
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居左(HORIZONTAL_LEFT,默认值)中(HORIZONTAL_CENTER)右(HORIZONTAL_RIGHT)
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色
// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF7F24');//设置填充颜色
https://www.cnblogs.com/qwgshare/p/9782889.html
https://www.cnblogs.com/fps2tao/p/10723109.html