fetch">
时间:2021-07-01 10:21:17 帮助过:7人阅读
query("select * from oa_event_sales"); while($a = $db->fetch_array($q)){ $list[] = $a; } $ce = new creatExcelDb(); $re = $ce->_run($list,'served_time','client_status','oid'); $all_nums=0; $num=array(); foreach($re as $k=>$v){ $num[$k]=count($re[$k]); $all_nums+=count($re[$k]); } $jq = array(); $title1 = client_status; $title2 = fin_confirm; $title3 = oid; //去除数组中相同的值 foreach($re as $key => $val){ if(true){ foreach($val as $key2 => $val2){ if(!in_array($key2,$jq)){ $jq[] = $key2; } } } } $arr_keys=array(); foreach($re as $k=>$v){ foreach($v as $k2=>$v2){ $arr_keys[]=$k2; } } $c=array_count_values($arr_keys); //++===++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // 创建一个处理对象实例 $objExcel = new PHPExcel(); // 创建文件格式写入对象实例, uncomment $objWriter = new PHPExcel_Writer_Excel5($objExcel); //设置文档基本属性/**似乎一般情况下用不到**/ $objProps = $objExcel->getProperties(); $objProps->setCreator("杨本木"); $objProps->setLastModifiedBy("杨本木"); $objProps->setTitle("杨本木"); $objProps->setSubject("杨本木"); $objProps->setDescription("杨本木"); $objProps->setKeywords("杨本木"); $objProps->setCategory("杨本木"); //************************************* //设置当前的sheet索引,用于后续的内容操作。 //一般只有在使用多个sheet的时候才需要显示调用。 //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0 $objExcel->setActiveSheetIndex(0); $objActSheet = $objExcel->getActiveSheet(); //设置当前活动sheet的名称 $objActSheet->setTitle('当前sheetname'); //设置宽度,这个值和EXCEL里的不同,不知道是什么单位,略小于EXCEL中的宽度 //$objActSheet->getColumnDimension('A')->setWidth(20); //$objActSheet->getRowDimension(1)->setRowHeight(30); //高度 //设置单元格的值 $objActSheet->setCellValue('A1', '总标题显示'); /* //设置样式 $objStyleA1 = $objActSheet->getStyle('A1'); $objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objFontA1 = $objStyleA1->getFont(); $objFontA1->setName('宋体'); $objFontA1->setSize(18); $objFontA1->setBold(true); //设置列居中对齐 $objActSheet->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); */ //============first=================================== $benmu=1; $objActSheet->setCellValue('A1', '行标签\列标签'); foreach($jq as $k=>$v){ $objActSheet->setCellValue(get_excel_row($benmu).'1', $v); $benmu+=1; } $objActSheet->setCellValue(get_excel_row($benmu).'1', '总计'); //设置宽度 for($i=0;$i<$benmu+1;$i++){ $objActSheet->getColumnDimension(get_excel_row($i))->setWidth(20); //宽度 $objActSheet->getStyle(get_excel_row($i)."1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->getStyle(get_excel_row($i))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //颜色 $objActSheet->getStyle(get_excel_row($i)."1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objActSheet->getStyle(get_excel_row($i)."1")->getFill()->getStartColor()->setARGB(COLOR1); } //==============content================= $y=2; foreach($re as $k1=>$v1){ //$k1全部放在A2。。。。后面,k1为电话号码、v1为person-》数字 $objActSheet->setCellValue('A'.$y, $k1); //颜色 $objActSheet->getStyle('A'.$y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objActSheet->getStyle('A'.$y)->getFill()->getStartColor()->setARGB(COLOR1); foreach($jq as $k2=>$v2){ //k2是0,v2是person foreach($v1 as $k3=>$v3){ //$k3为person,$v3是要的值 if($k3==$v2){ //$objActSheet->setCellValue(get_excel_row("1"+$k2).$y,$v1[$k3]); $objActSheet->setCellValueExplicit(get_excel_row("1"+$k2).$y,$v1[$k3],PHPExcel_Cell_DataType::TYPE_STRING); } } } $objActSheet->setCellValue(get_excel_row("1"+count($jq)).$y, $num[$k1]); $y+=1; } //=================last========== $objActSheet->setCellValue("A".$y,"总计"); //颜色 $objActSheet->getStyle("A".$y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objActSheet->getStyle("A".$y)->getFill()->getStartColor()->setARGB(COLOR1); foreach($jq as $k=>$v){ //k为person $objActSheet->setCellValue(get_excel_row("1"+$k).$y,$c[$v]); //颜色 $objActSheet->getStyle(get_excel_row("1"+$k).$y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objActSheet->getStyle(get_excel_row("1"+$k).$y)->getFill()->getStartColor()->setARGB(COLOR1); } $objActSheet->setCellValue(get_excel_row("1"+count($jq)).$y,$all_nums); //颜色 $objActSheet->getStyle(get_excel_row("1"+count($jq)).$y)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objActSheet->getStyle(get_excel_row("1"+count($jq)).$y)->getFill()->getStartColor()->setARGB(COLOR1); //=============================== //输出内容 $outputFileName =time().".xls"; header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download"); header('Content-Disposition:attachment;filename='.$outputFileName.''); header("Content-Transfer-Encoding:binary"); $objWriter->save('php://output'); ?>