代码拉取完成，页面将自动刷新
<?php
include 'inc/conn.php';
session_start();
extract($_POST);
extract($_GET);
unset($_POST,$_GET);
if(!isset($dept))
{
if(empty($_SESSION['dept'])){
$dept='TE'; // default is TE Daily report
}else{
$dept=$_SESSION['dept'];
}
}
/** 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);
$string = str_replace("'", "'", $string);
if(!strstr($string, '<img')){
$string = strip_tags($string);
}
return trim($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_THIN //粗的是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("TE Daily Report")
->setSubject("TE Daily Report")
->setDescription("TE Daily Report, generated using TE KS.")
->setKeywords("TE, http://10.100.160.166")
->setCategory("TE");
for($i=0;$i<1;$i++){
// Add common Title
$objPHPExcel->setActiveSheetIndex($i)
->setCellValue('A2', 'Date') // 填写单元格内容
->setCellValue('B2', 'Dept.') // 填写单元格内容
->setCellValue('C2', 'Jobs')
->setCellValue('D2', 'Owner')
->setCellValue('E2', 'Issue day')
->setCellValue('F2', 'Update day')
->setCellValue('G2', 'Status')->getStyle("A2:G2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 设置单元格对齐
$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // 设置字体颜色
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(10); // 设置第一行的行高
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30); // 设置第二行的行高
$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->applyFromArray($styleThinBlackBorderOutline);
// 设置单元格自动换行
$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);
$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_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->getColumnDimension('A') -> setWidth(11);; // 设置列的宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B') -> setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension('C') -> setWidth(100);
$objPHPExcel->getActiveSheet()->getColumnDimension('D') -> setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('E') -> setWidth(11);
$objPHPExcel->getActiveSheet()->getColumnDimension('F') -> setWidth(11);
$objPHPExcel->getActiveSheet()->getColumnDimension('G') -> setWidth(8);
$objPHPExcel->getActiveSheet()->setTitle("TE"); // 设置表格名称
}
$sheet=0;
$r=3;
//进入数据库查询周报的全部内容
$db = new mysql();
$sql = "select * from daily_table where UID in (select ID from users where Department='${dept}') and (`add_date`>= '".date("Y-m-d")."' or `update_date`>='".date("Y-m-d")."') order by UNAME";
$db->query($sql);
$num = $db->db_num_rows();
if ($num == 0){
//echo "没有今日的日报内容!";
$num=1; // to create a null sheet
}
for($i=0; $i<$num; $i++){
$row = $db->fetch_assoc();
$objPHPExcel->setActiveSheetIndex($sheet)
->setCellValue("C${r}", html_to_text(htmlspecialchars_decode($row['daily_content'])))
->setCellValue("D${r}", html_to_text(htmlspecialchars_decode($row['UNAME'])))
->setCellValue("E${r}", substr(html_to_text(htmlspecialchars_decode($row['add_date'])),0,10))
->setCellValue("F${r}", substr(html_to_text(htmlspecialchars_decode($row['update_date'])),0,10))
->setCellValue("G${r}", html_to_text(htmlspecialchars_decode($row['status'])));
$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle("A${r}:G${r}")->applyFromArray($styleThinBlackBorderOutline);
if(!$row['update_date']) {
$objPHPExcel->getActiveSheet()->setCellValue("F${r}", "N/A");
}
if($row['status']=="Open") {
$objPHPExcel->getActiveSheet()->getStyle("G${r}")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
}
if($row['status']=="Tracking") {
$objPHPExcel->getActiveSheet()->getStyle("G${r}")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
}
$tmpStr=hasImg(htmlspecialchars_decode($row['daily_content']));
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("C${r}");//设置图片所在表格位置
$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
}
$r++;
}
$mergRow=$r-1;
$objPHPExcel->getActiveSheet()->mergeCells("A3:A${mergRow}")->setCellValue('A3', date("Y-m-d"));
$objPHPExcel->getActiveSheet()->mergeCells("B3:B${mergRow}")->setCellValue('B3', ${dept})->getStyle("B3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);;
// 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="Daily_Report-'.${dept}.'-'.date("md").'.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 助手