public function setExcel($start,$end,$id){
$OrdersData=M('received')->where(array('redpack_id' => $id))->order('add_time desc')->order('open_id desc')->limit($start,$end)->select();
$order = M('received')->where(array('redpack_id' => $id))->order('open_id desc')->field('count(open_id) as total_count,sum(money) as total_money,open_id')->group('open_id')->limit($start,$end)->select();
foreach ($OrdersData as $k=>$v){
foreach ($order as $k1=>$v1){
if($v['open_id']==$v1['open_id']){
$v['total_count'] = $v1['total_count'];
$v['total_money'] = $v1['total_money'];
$OrdersData[$k] = $v;
}
}
}
$OrdersData = $this->my_sort($OrdersData,'total_money',SORT_DESC);
//如果没有选中任何,则导出所有数据
trace($OrdersData);
//导入phpexcel类方法
Vendor('PHPExcel.Classes.PHPExcel');
Vendor('PHPExcel.PHPExcel.Classes.IOFactory');
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
// 水平居中(位置很重要,建议在最初始位置)
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//
// 合并 A1 I1
$objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '领取记录汇总表 时间:'.date('Y-m-d H:i:s')) //A的第一行
->setCellValue('A2', '用户ID')//第二行
->setCellValue('B2', '用户open_ID')
->setCellValue('C2', '用户名')
->setCellValue('D2', '头像链接')
->setCellValue('E2', '红包金额')
->setCellValue('F2', '红包类型')
->setCellValue('G2', '语音链接')
->setCellValue('H2', '性别')
->setCellValue('I2', '领取时间')
->setCellValue('J2', '总领取次数')
->setCellValue('K2', '总领取金额');
//set width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12);
// Miscellaneous glyphs, UTF-8
// for($i=0;$i<count($OrdersData)-1;$i++){
// $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $OrdersData[$i]['uid']);
// $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['user_name']);
// $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['pwd']);
// $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $OrdersData[$i]['name']);
// $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
// $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':D'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
// $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
// }
foreach ($OrdersData as $k => $v) {
$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($k+3), $v['id']);
$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($k+3), $v['open_id']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($k+3), $v['nickname']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($k+3), $v['img']);
$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($k+3), $v['money']);
$redType = $v['red_type']==0?'真心寄语':($v['red_type']==1?'口令红包':'你说我猜');
$objPHPExcel->getActiveSheet(0)->setCellValue('F'.($k+3),$redType);
$objPHPExcel->getActiveSheet(0)->setCellValue('G'.($k+3), $v['audio_url']);
$sex = $v['sex']==1?'男士':'女士';
$objPHPExcel->getActiveSheet(0)->setCellValue('H'.($k+3), $sex);
$addTime = date('Y-m-d H:i:s',$v['add_time']);
$objPHPExcel->getActiveSheet(0)->setCellValue('I'.($k+3), $addTime);
$objPHPExcel->getActiveSheet(0)->setCellValue('I'.($k+3), $addTime);
$objPHPExcel->getActiveSheet(0)->setCellValue('J'.($k+3), $v['total_count']);
$objPHPExcel->getActiveSheet(0)->setCellValue('K'.($k+3), $v['total_money']);
}
// sheet命名
$objPHPExcel->getActiveSheet()->setTitle('领取记录汇总表');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// excel头参数
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="领取记录汇总表('.date('Ymd-His').').xls"'); //日期为文件名后缀
header('Cache-Control: max-age=0');
ob_clean();//关键
flush();//关键
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式,excel2007为xlsx格式
$objWriter->save('php://output');
}
php excel 引入
最新推荐文章于 2023-12-11 00:02:40 发布