记录一次PHP写多sheet的Excel public function dcsbExport(){ $param = Request::instance()->param(); $begin = strtotime($param['day1']); $end = strtotime($param['day2']); $stime = date('Y-m-d 00:00:01', $begin); $etime = date('Y-m-d H:00:00', $end + 86410); // halt($etime); //引入PHPExcel导出类 vendor("PHPExcel.PHPExcel"); $sheetIndex=0; $objPHPExcel = new \PHPExcel(); $sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++); $sheet->setTitle('同比水量日明细'); $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->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 水平居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// 垂直居中 $sheet->getDefaultColumnDimension()->setWidth(15);//设置默认列宽度 $sheet->getColumnDimension('A')->setWidth(30);//设置某列宽度 // AutoSize $objPHPExcel->getActiveSheet()->SetCellValue('A1', '电磁水表在装表统计'); $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');//合并单元格 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);//设置默认行高度 $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);//设置某一行高度 $objPHPExcel->getActiveSheet()->SetCellValue('A2', '单位'); $objPHPExcel->getActiveSheet()->mergeCells('A2:A3');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('B2', '电磁水表在装'.substr($stime,0,7).'前'); $objPHPExcel->getActiveSheet()->mergeCells('B2:D2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('B3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('C3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('D3', '小计'); $objPHPExcel->getActiveSheet()->SetCellValue('E2', '电磁水表新装'.substr($stime,0,7).'-'.substr($etime,0,7)); $objPHPExcel->getActiveSheet()->mergeCells('E2:G2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('E3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('F3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('G3', '小计'); $objPHPExcel->getActiveSheet()->SetCellValue('H2', '电磁水表在装新装总计'); $objPHPExcel->getActiveSheet()->mergeCells('H2:J2');//合并单元格 $objPHPExcel->getActiveSheet()->SetCellValue('H3', '肯特'); $objPHPExcel->getActiveSheet()->SetCellValue('I3', '拓安信'); $objPHPExcel->getActiveSheet()->SetCellValue('J3', '小计'); //查找在选择时间之前的表 //查出组织机构 $zzjg=db('management')->whereIn('id',[7,10,11,12,13,14,15])->select(); // $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', // 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH']; $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']; $line=4;//从第四行开始写数据 $zong=[]; foreach ($zzjg as $key => $value) { $lineData=[]; $lineData['A']=$value['names']; $lineData['B']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特')->where('a.AddTime','<',$stime)->count(); $lineData['C']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信')->where('a.AddTime','<',$stime)->count(); $lineData['D']=$lineData['B']+$lineData['C']; $lineData['E']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特') ->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count(); $lineData['F']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信') ->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count(); $lineData['G']=$lineData['E']+$lineData['G']; $lineData['H']=$lineData['B']+$lineData['E']; $lineData['I']=$lineData['C']+$lineData['F']; $lineData['J']=$lineData['D']+$lineData['G']; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $lineData[$zimu]);//计算值所在的位置 if($zimu!='A'){ $zong[$zimu]=$zong[$zimu]+$lineData[$zimu];//计算总计 } } $line++; } $zong['A']='总计'; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $zong[$zimu]);//计算值所在的位置 } //===================================================================================================================================================== foreach($zzjg as $zzjgInfo){ //创建新sheet表 $objPHPExcel->createSheet($sheetIndex); $sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++); $sheet->setTitle($zzjgInfo['names']);//单位名称 $sheet->getDefaultColumnDimension()->setWidth(20);//设置默认列宽度 $sheet->getColumnDimension('F')->setWidth(40);//设置默认列宽度 $objPHPExcel->getActiveSheet()->SetCellValue('A1', '序号'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', '仪表管理号'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', '点位名称'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', '仪表口径'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', '计量性质'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', '安装地址'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', '安装时间'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', '仪表分类'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', '管理单位'); $objPHPExcel->getActiveSheet()->SetCellValue('J1', '是否在用'); $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']; $meters=db('meter_equip2') ->alias('a') ->join('meter_info m','a.id = m.id') ->where('m.manageunit',$zzjgInfo['id']) // ->where('m.manageunit',11) ->whereIn('brand',['肯特','拓安信']) ->field('m.*,a.staname,a.AddTime') ->select(); $meterData=[]; $line=2;//从第二行开始写 foreach($meters as $meter){ $meterData['A']=$meter['ROW_NUMBER']; $meterData['B']=$meter['id']; $meterData['C']=$meter['staname']; $meterData['D']=$meter['caliber']; $meterData['E']=$meter['measurecon']; $meterData['F']=$meter['installpl']; $meterData['G']=$meter['AddTime']; $meterData['H']=$meter['brand']; $meterData['I']=$zzjgInfo['names']; $dic_status=[""=>"使用","0"=>"使用","1"=>"停用","2"=>"报废"]; $meterData['J']=$dic_status[$meter['ybstatus']]; foreach($title_array as $zimu){ $objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $meterData[$zimu]);//计算值所在的位置 } $line++; } } //====================================================================================================================================================== $objPHPExcel->setActiveSheetIndex(0); //设置sheet起始位置 ob_clean(); header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;'); header('Content-Disposition:attachment;filename=电磁水表在装表统计'. date('Y-m-d', $begin) . '-' . date('Y-m-d', $end) .'.xlsx'); $save_object = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $save_object->save('php://output'); }

thinkphp分组查询,并查出每组最新数据 $subsql = Db::table('value_meter_yj_message')->where(['read'=>0])->field('equip_id,max(time) as time1,type')->group('equip_id,type') ->buildSql(); $list=Db::table('value_meter_yj_message') ->alias('a') ->join([$subsql=> 'w'], 'a.equip_id = w.equip_id and a.time=w.time1') ->field('a.*') ->select();

sqlserver对字段相同的表进行合并查询 如果有一个项目每个月分一次表,字段是相同的,想进行正常查询用以下方法:/** * 分表后根据合并分表查询, * tp的union方法好像对sqlserver不好使 */ public function get_union_index($stime,$etime,$eqid,$field='*'){ //首先先查出日期所对应的月份,确认要查的表 $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->select(); return $sql; } /** * 分页版 */ public function get_union_page($stime,$etime,$eqid,$field='*'){ $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->paginate(); return $sql; } /** * 第一条或最后一条 */ public function get_union_first($stime,$etime,$eqid,$field='*',$order='desc'){ $sqls = self::join_instant_table($stime, $etime,$field); $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime',$order)->where(['EQTime' => ['between', [$stime, $etime]]]); $sql=$sql->field($field); $sql=$sql->find(); return $sql; } public function join_instant_table($begin, $end, $field = '*') { $tables = self::get_instant_tables($begin, $end); $sqls = []; foreach ($tables as $table) { //判断表是否存在 try { db::name($table)->find(); $sql = sprintf("select {$field} from value_%s", $table); $sqls[] = $sql; } catch (\Throwable $e) { continue; } } if ($sqls) { return '(' . implode(' union all ', $sqls) . ') i'; } return ''; } /** * @param begin string 开始时间 * @param end string 结束时间 * @return: */ public function get_instant_tables($begin,$end) { $table_basis = 'meter_instant'; $initial = strtotime('2022-12-01'); $begin = !empty($begin) ? strtotime($begin) : $initial; $end = !empty($end) ? strtotime($end) : time(); if ($end > time()) $end = time(); // if ($end < $initial) return [$table_basis.'_202212']; $end3 = strtotime(date('Y-m-01'));//2 // if ($begin >= $end3) return [$table_basis]; $tables = []; while ($begin <= $end) { if ($begin < $initial) { $tables[] = $table_basis.'_202212'; } else if ($begin >= $end3) { $tables[] = $table_basis; } else { $tables[] = $table_basis.'_'.date('Y',$begin).date('m',$begin); } $begin = strtotime('+1 month '.date('Y-m-01',$begin)); } return array_values(array_unique($tables)); }

sqlserver获取某个时间范围内,每天某个小时的数据 如题,想获取到某个时间范围内,每天某个小时的数据需要用到datepart函数SELECT SUM ( total6_v ) AS total6_v,SUM ( total7_v ) AS total7_v,SUM ( tx_v ) AS tx_v,EQID ,CONVERT( VARCHAR ( 100 ), DATEADD( hh,- 1, eqtime ), 23 ) as eqtime FROM (select * from value_meter_instant_202103) i WHERE [EQTime] BETWEEN '2021-03-09 00:00:01' AND '2021-03-12 00:00:00' AND datepart(hh,EQTime) >= 3 AND datepart(hh,EQTime) <= 23 GROUP BY CONVERT( VARCHAR ( 100 ), DATEADD( hh,- 1, eqtime ), 23 ) ,EQID/* datepart()函数的使用 * datepart()函数可以方便的取到时期中的各个部分 *如日期：2006-07--02 18：15：36.513 * yy:取年 2006 * mm:取月 7 * dd:取月中的天 2 * dy:取年中的天 183 * wk:取年中的周 27 * dw:取周中的天 1 * qq:取年中的季度 3 * hh:取小时 18 * mi:取分钟 15 * ss:取秒 36 * 以下简单的语句可以演示所取到的结果 */ select getdate() select datepart(mm,getdate()) select datepart(yy,getDate()) select datepart(dd,getdate()) select datepart(dy,getdate()) select datepart(wk,getdate()) select datepart(dw,getdate()) select datepart(qq,getdate()) select datepart(hh,getdate()) select datepart(mi,getdate()) select datepart(ss,getdate()) select datediff(dd,getdate(),'12/25/2006')　　 --计算从今天到12/25/2006还有多少天 select datediff(mm,getdate(),'12/25/2006')　　--计算从今天到12/25/2006还有多少个月 select datename(mm,getdate()) 　　 --以字符串而不是数字的形式得到今天的月份（07） select dateadd(dd,30,getdate()) 　　　 --在目前的日期日期上加30天 select dateadd(mm,3,getdate()) 　　　 --在目前的日期日期上加3个月 select dateadd(yy,1,getdate()) 　　　　 --在目前的日期日期上加1年

php生成excel文件 /** * Notes:导出数据库数据为excel，返回文件路由 * @param $list 数据 * @param $name 文件名称 * @param $fields 表头，与$list对应好 */ function exportUrl($list, $name, $fields) { set_time_limit(0); $excel_title = []; if ($list) { $keys = array_keys($list[0]); foreach ($fields as $key => $field) { if (in_array($key, $keys)) { $excel_title[] = $field; } } } else { //没有数据时导出表头 foreach ($fields as $k => $v) { $excel_title[] = $v; } } vendor("PHPExcel.PHPExcel"); $objPHPExcel = new \PHPExcel(); $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"); $lie1 = []; $last = 'A'; for ($i = 1; $i <= count($excel_title); $i++) { if (array_search($excel_title[$i - 1], $fields)) { $lie1[array_search($excel_title[$i - 1], $fields)] = numCovertLetter($i); $result = numCovertLetter($i) . '1'; $objPHPExcel->setActiveSheetIndex(0)->setCellValue($result, $excel_title[$i - 1]); $last = numCovertLetter($i); } } // 水平居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 垂直居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置行宽度 $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15); // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); //设置行高度 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); $objPHPExcel->getActiveSheet()->getStyle('A1:' . $last . '1')->getFont()->setBold(true); for ($j = 0; $j < count($list); $j++) { foreach ($lie1 as $key => $item) { $objPHPExcel->getActiveSheet(0)->setCellValue($item . ($j + 2), $list[$j][$key]); } } // 设置单元格换行 $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(True); //边框线 $style_array = array( 'borders' => array( 'allborders' => array( 'style' => \PHPExcel_Style_Border::BORDER_THIN ) )); $objPHPExcel->getActiveSheet()->getStyle('A2:' . $last . (count($list) + 1))->applyFromArray($style_array); $dir = '../public/tableData/excel/' . date('Ymd'); if (!is_dir($dir)) { mkdir($dir, 0777, true); } $fielName = $name . '_' . date('YmdHis') . mt_rand(1111, 9999); $url = $dir . '/' . $fielName . '.xlsx'; $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save($url); $new_url = substr($url, 9); $data = [ //'url' => 'http://' . $_SERVER['SERVER_NAME'] . ":" . $_SERVER['SERVER_PORT'] . $new_url, 'url' => $new_url, 'name' => $fielName . '.xlsx' ]; return setReturn(200, '', $data); }