代码拉取完成，页面将自动刷新
<?php
include 'inc/conn.php';
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
// 将富文本编辑器里面的内容过滤
function html_to_text($string){
$string = trim($string);
$string = str_replace("<p>", "", $string);
$string = str_replace("<br>", chr(10), $string);
$string = str_replace("<br />", chr(10), $string);
$string = str_replace("</p>", chr(10), $string);
$string = str_replace("&", "&", $string);
$string = str_replace(" ", " ", $string);
$string = str_replace(">", ">", $string);
$string = str_replace(""", "\"", $string);
if(!strstr($string, '<img')){
$string = strip_tags($string);
}
return $string.PHP_EOL;
}
// 检查是否有图像的内容，有的话则攫取路径
function hasImg($string)
{
if(strstr($string, '<img')){
preg_match_all('/<img.*?src="(.*?)".*?>/is', $string, $result);
if(strlen($result[1][0])<5){
return "";
}else{
return str_replace("..", "", $result[1][0]);
}
}else{
return "";
}
}
// 获取当前的周别
function get_weeks_num(){
$strTime = date("Y-m-d");
$intWeek = ceil(((strtotime($strTime) - strtotime(date("Y")."-01-01 00:00:00")))/(7*86400));
return $intWeek;
}
// 设置边框的数组
$styleThinBlackBorderOutline = array(
'borders' => array(
'allborders' => array( //设置全部边框
'style' => \PHPExcel_Style_Border::BORDER_DASHDOTDOT //粗的是thick
),
),
);
// 定义要创建的表格名称
$Dept = Array("EE", "TE", "PE", "RT");
// 定义要写入数据的开始行
$rEE=5;
$rTE=5;
$rPE=5;
$rRT=5;
// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();
// 设置文档属性
$objPHPExcel->getProperties()->setCreator("TE")
->setLastModifiedBy("TE")
->setTitle("EE Weekly Report")
->setSubject("EE Weekly Report")
->setDescription("EE Weekly Report, generated using TE KS.")
->setKeywords("TE, http://10.100.160.166")
->setCategory("TE");
for($i=0;$i<4;$i++){
// Add common Title
$objPHPExcel->setActiveSheetIndex($i)
->setCellValue('A5', 'Item') // 填写单元格内容
->setCellValue('B5', 'Problem summary')
->setCellValue('C5', 'What Risk will...?')
->setCellValue('D5', 'What we did')
->setCellValue('E5', 'What we can do?')
->setCellValue('F5', 'Project')
->setCellValue('G5', 'Trigger date')
->setCellValue('H5', 'Issue date')
->setCellValue('I5', 'Owner')
->setCellValue('J5', 'Status')
->setCellValue('K5', 'Remark')->getStyle("A5:K5")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置单元格对齐
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(12); // 设置第二行的行高
$objPHPExcel->getActiveSheet()->getStyle("A5:K5")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // 设置字体颜色
$objPHPExcel->getActiveSheet()->getStyle("A5:K5")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5'); // 设置单元格背景色
$objPHPExcel->getActiveSheet()->getStyle("A1:K1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');
$objPHPExcel->getActiveSheet()->getStyle("A4:K4")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');
$objPHPExcel->getActiveSheet()->getStyle("F2:F3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffffff');
$objPHPExcel->getActiveSheet()->getStyle('A2:E3')->applyFromArray($styleThinBlackBorderOutline); // 设置表格边框
$objPHPExcel->getActiveSheet()->getStyle('G2:K3')->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->getStyle('A5:K5')->applyFromArray($styleThinBlackBorderOutline);
$objPHPExcel->getActiveSheet()->mergeCells('A2:A3')->setCellValue('A2', "Problem".chr(10)."status".chr(10)."indicator")->getStyle('A2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setName('Arial')->setSize(7); // 设置字体及大小
$objPHPExcel->getActiveSheet()->setCellValue('B2', '"C"')->getStyle('B2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('c00000');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Crisis and high risk');
$objPHPExcel->getActiveSheet()->setCellValue('C2', '"R"')->getStyle('C2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ff0000');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Critical item and Risk');
$objPHPExcel->getActiveSheet()->setCellValue('D2', '"Y"')->getStyle('D2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffff00');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Minor item and Yellow');
$objPHPExcel->getActiveSheet()->setCellValue('E2', '"T"')->getStyle('E2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e6b8b7');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Tracking');
$objPHPExcel->getActiveSheet()->mergeCells('G2:H2')->setCellValue('G2', "When?")->getStyle('G2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
$objPHPExcel->getActiveSheet()->mergeCells('G3:H3')->setCellValue('G3', "WK".get_weeks_num());
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'Report by?')->getStyle('I2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
$objPHPExcel->getActiveSheet()->setCellValue('I3', '=I6');
$objPHPExcel->getActiveSheet()->mergeCells('j2:k2')->setCellValue('J2', 'Report by which team?')->getStyle('J2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
$objPHPExcel->getActiveSheet()->mergeCells('j3:k3')->setCellValue('J3', $Dept[$i]); // 设置单元格合并
// 设置单元格自动换行
$objPHPExcel->getActiveSheet()->getStyle("A2:K3")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("B")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->getStyle("E")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->getStyle("F")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("H")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("I")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("J")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("K")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->getColumnDimension('A') -> setAutoSize(true); // 设置列的宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B') -> setWidth(17);
$objPHPExcel->getActiveSheet()->getColumnDimension('C') -> setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D') -> setWidth(56);
$objPHPExcel->getActiveSheet()->getColumnDimension('E') -> setWidth(47);
$objPHPExcel->getActiveSheet()->getColumnDimension('F') -> setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G') -> setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H') -> setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('I') -> setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('J') -> setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K') -> setWidth(12);
$objPHPExcel->getActiveSheet()->freezePane('A6'); // 设置冻结行
$objPHPExcel->getActiveSheet()->setAutoFilter('A5:K5');
/*
$autoFilter=$objPHPExcel->getActiveSheet()->getAutoFilter(); // 设置自动筛选固定调节？？不知道为何不执行
$autoFilter->getColumn('J')
->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER)
->createRule()
->setRule(
PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
'C')
->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
$autoFilter->showHideRows();
*/
$objPHPExcel->getActiveSheet()->setTitle($Dept[$i]); // 设置表格名称
if($i<3) $objPHPExcel->createSheet(); // 创建新的表格
}
$sheet=0;
$r=0;
// 进入数据库查询周报的全部内容
$db = new mysql();
$sql = "select * from weekly_report where Status in ('Crisis','Critical','Minor','Tracking')";
//$sql = "select * from weekly_report";
$db->query($sql);
$num = $db->db_num_rows();
if ($num == 0)
echo "No data was found!";
for($i=0; $i<$num; $i++){
$row = $db->fetch_assoc();
if($row['DEPT']=="EE"){$sheet=0; $rEE++; $r=$rEE;};
if($row['DEPT']=="TE"){$sheet=1; $rTE++; $r=$rTE;};
if($row['DEPT']=="PE"){$sheet=2; $rPE++; $r=$rPE;};
if($row['DEPT']=="RT"){$sheet=3; $rRT++; $r=$rRT;};
$j=$r-5;
$objPHPExcel->setActiveSheetIndex($sheet)
->setCellValue("A${r}", "${j}")
->setCellValue("B${r}", html_to_text(htmlspecialchars_decode($row['Issues'])))
->setCellValue("C${r}", html_to_text(htmlspecialchars_decode($row['Risks'])))
->setCellValue("D${r}", html_to_text(htmlspecialchars_decode($row['WeDid'])))
->setCellValue("E${r}", html_to_text(htmlspecialchars_decode($row['WillDo'])))
->setCellValue("F${r}", html_to_text(htmlspecialchars_decode($row['Project'])))
->setCellValue("G${r}", htmlspecialchars_decode($row['Trigger date']))
->setCellValue("H${r}", htmlspecialchars_decode($row['Issue date']))
->setCellValue("I${r}", htmlspecialchars_decode($row['Owner']))
->setCellValue("K${r}", html_to_text(htmlspecialchars_decode($row['Remark'])));
$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle("A${r}:K${r}")->applyFromArray($styleThinBlackBorderOutline);
if($row['Status']=='Crisis')
$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "C")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('c00000');
if($row['Status']=='Critical')
$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "R")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ff0000');
if($row['Status']=='Minor')
$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "Y")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('ffff00');
if($row['Status']=='Tracking')
$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "T")->getStyle("J${r}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('e6b8b7');
if($row['Status']=='Closed')
$objPHPExcel->getActiveSheet()->setCellValue("J${r}", "Closed");
$tmpStr=hasImg(htmlspecialchars_decode($row['WeDid']));
if(strlen($tmpStr)>=5){
$img=new PHPExcel_Worksheet_Drawing();
$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
$img->setHeight(200);//写入图片高度
$img->setWidth(380);//写入图片宽度
$img->setOffsetX(1);//写入图片在指定格中的X坐标值
$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
$img->setRotation(0);//设置旋转角度
$img->getShadow()->setVisible(false);
$img->getShadow()->setDirection(0);
$img->setCoordinates("D${r}");//设置图片所在表格位置
$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
}
$tmpStr=hasImg(htmlspecialchars_decode($row['WillDo']));
if(strlen($tmpStr)>=5){
$img=new PHPExcel_Worksheet_Drawing();
$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
$img->setHeight(200);//写入图片高度
$img->setWidth(380);//写入图片宽度
$img->setOffsetX(1);//写入图片在指定格中的X坐标值
$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
$img->setRotation(0);//设置旋转角度
$img->getShadow()->setVisible(false);
$img->getShadow()->setDirection(0);
$img->setCoordinates("E${r}");//设置图片所在表格位置
$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
}
$tmpStr=hasImg(htmlspecialchars_decode($row['Remark']));
if(strlen($tmpStr)>=5){
$img=new PHPExcel_Worksheet_Drawing();
$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
$img->setHeight(200);//写入图片高度
$img->setWidth(380);//写入图片宽度
$img->setOffsetX(1);//写入图片在指定格中的X坐标值
$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
$img->setRotation(0);//设置旋转角度
$img->getShadow()->setVisible(false);
$img->getShadow()->setDirection(0);
$img->setCoordinates("K${r}");//设置图片所在表格位置
$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
$objPHPExcel->setActiveSheetIndex($sheet)->setCellValue("L${r}", 'PIC');
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
}
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="EE_Weekly_Report-wk'.get_weeks_num().'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');
exit;
?>
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手