public function export(){ $name= '每日统计' .date( "Y-m-d H-i-s" ,time()); // $data=[['aa','aa','cc','dd','ee'],['bb','bb','cc','dd','ee']]; // ?工地考勤 $data[ 'kaoqin' ] = Db::name( 'pt_gd_gdrz rz' ) ->field( "DATE_FORMAT(FROM_UNIXTIME(ctime),'%Y-%m-%d') as date,u.name,rz.title,rz.content" ) -> where ([ 'p.appid' =>session( 'pt_res.appid' )]) -> join ( 'zf_pt_post p' , 'p.id=rz.gd_id' ) -> join ( 'zf_user u' , 'rz.openid = u.openid' ) ->whereTime( 'ctime' , 'today' ) -> select (); 工地打卡 $data[ 'daka' ] = Db::name( 'pt_gd_gddk rz' ) ->field( "DATE_FORMAT(FROM_UNIXTIME(ctime),'%Y-%m-%d') as date,u.name,p.title" ) -> where ([ 'p.appid' =>session( 'pt_res.appid' )]) -> join ( 'zf_pt_post p' , 'p.id=rz.gd_id' ) -> join ( 'zf_user u' , 'rz.openid = u.openid' ) ->whereTime( 'ctime' , 'today' ) -> select (); // 新用户 $data[ 'yonghu' ] = Db::name( 'user' )-> where ([ 'appid' =>session( 'pt_res.appid' )])->field( "DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d %H:%i:%s') as date,name" ) ->whereTime( 'create_time' , 'today' ) -> select (); // 预约 $data[ 'yuyue' ] = Db::name( 'yuyue y' ) ->field( "DATE_FORMAT(FROM_UNIXTIME(ctime),'%Y-%m-%d') as date,u.name,y.phone" ) -> where ([ 'u.appid' =>session( 'pt_res.appid' )]) ->whereTime( 'ctime' , 'today' ) -> join ( 'zf_user u' , 'u.id=y.uid' ) -> select (); //设置表头: $head[ 'kaoqin' ] = [ '时间' , '姓名' , '标题' , '内容' ]; //数据中对应的字段,用于读取相应数据: $keys[ 'kaoqin' ] = [ 'date' , 'name' , 'title' , 'content' ]; $count[ 'kaoqin' ] = count($head[ 'kaoqin' ]); //计算表头数量 $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet()->setTitle( '工地考勤' ); for ($i = 65; $i < $count[ 'kaoqin' ] + 65; $i++) { //数字转字母从65开始,循环设置表头: $sheet->setCellValue(strtoupper(chr($i)) . '1' , $head[ 'kaoqin' ][$i - 65]); } /*--------------开始从数据库提取信息插入Excel表中------------------*/ foreach ($data[ 'kaoqin' ] as $key => $item) { //循环设置单元格: //$key+2,因为第一行是表头,所以写到表格时 从第二行开始写 for ($i = 65; $i < $count[ 'kaoqin' ] + 65; $i++) { //数字转字母从65开始: $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[ 'kaoqin' ][$i - 65]]); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } //工地打卡 //方式2 Create a new worksheet called "My Data" $myWorkSheet[ 'daka' ] = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, '工地打卡' ); //与下面的配合使用 $spreadsheet->addSheet($myWorkSheet[ 'daka' ], 0); //将“My Data”工作表作为电子表格对象中的第一个工作表附加 $head[ 'daka' ] = [ '时间' , '姓名' , '标题' ]; //数据中对应的字段,用于读取相应数据: $keys[ 'daka' ] = [ 'date' , 'name' , 'title' ]; $count[ 'daka' ] = count($head[ 'daka' ]); //计算表头数量 for ($i = 65; $i < $count[ 'daka' ] + 65; $i++) { //数字转字母从65开始,循环设置表头: $myWorkSheet[ 'daka' ]->setCellValue(strtoupper(chr($i)) . '1' , $head[ 'daka' ][$i - 65]); } /*--------------开始从数据库提取信息插入Excel表中------------------*/ foreach ($data[ 'daka' ] as $key => $item) { //循环设置单元格: //$key+2,因为第一行是表头,所以写到表格时 从第二行开始写 for ($i = 65; $i < $count[ 'daka' ] + 65; $i++) { //数字转字母从65开始: $myWorkSheet[ 'daka' ]->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[ 'daka' ][$i - 65]]); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } // 新用户 //方式2 Create a new worksheet called "My Data" $myWorkSheet[ 'yonghu' ] = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, '新用户' ); //与下面的配合使用 $spreadsheet->addSheet($myWorkSheet[ 'yonghu' ], 0); //将“My Data”工作表作为电子表格对象中的第一个工作表附加 $head[ 'yonghu' ] = [ '时间' , '姓名' ]; //数据中对应的字段,用于读取相应数据: $keys[ 'yonghu' ] = [ 'date' , 'name' ]; $count[ 'yonghu' ] = count($head[ 'yonghu' ]); //计算表头数量 for ($i = 65; $i < $count[ 'yonghu' ] + 65; $i++) { //数字转字母从65开始,循环设置表头: $myWorkSheet[ 'yonghu' ]->setCellValue(strtoupper(chr($i)) . '1' , $head[ 'yonghu' ][$i - 65]); } /*--------------开始从数据库提取信息插入Excel表中------------------*/ foreach ($data[ 'yonghu' ] as $key => $item) { //循环设置单元格: //$key+2,因为第一行是表头,所以写到表格时 从第二行开始写 for ($i = 65; $i < $count[ 'yonghu' ] + 65; $i++) { //数字转字母从65开始: $myWorkSheet[ 'yonghu' ]->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[ 'yonghu' ][$i - 65]]); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } // 预约 //方式2 Create a new worksheet called "My Data" $myWorkSheet[ 'yuyue' ] = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, '预约' ); //与下面的配合使用 $spreadsheet->addSheet($myWorkSheet[ 'yuyue' ], 0); //将“My Data”工作表作为电子表格对象中的第一个工作表附加 $head[ 'yuyue' ] = [ '时间' , '姓名' , '电话' ]; //数据中对应的字段,用于读取相应数据: $keys[ 'yuyue' ] = [ 'date' , 'name' , 'phone' ]; $count[ 'yuyue' ] = count($head[ 'yuyue' ]); //计算表头数量 for ($i = 65; $i < $count[ 'yuyue' ] + 65; $i++) { //数字转字母从65开始,循环设置表头: $myWorkSheet[ 'yuyue' ]->setCellValue(strtoupper(chr($i)) . '1' , $head[ 'yuyue' ][$i - 65]); } /*--------------开始从数据库提取信息插入Excel表中------------------*/ foreach ($data[ 'yuyue' ] as $key => $item) { //循环设置单元格: //$key+2,因为第一行是表头,所以写到表格时 从第二行开始写 for ($i = 65; $i < $count[ 'yuyue' ] + 65; $i++) { //数字转字母从65开始: $myWorkSheet[ 'yuyue' ]->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[ 'yuyue' ][$i - 65]]); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } header( 'Content-Type: application/vnd.ms-excel' ); header( 'Content-Disposition: attachment;filename="' . $name . '.xlsx"' ); header( 'Cache-Control: max-age=0' ); $writer = new Xlsx($spreadsheet); $writer->save( 'php://output' ); //删除清空: $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; } |