通過php+mysql自建了幾個zabbix事件查詢的頁面,不過後面在web頁面上查詢完,需要将結果導出到excel 時,感覺不怎麼友善,當然可以通過python 實作快速導出到excel 。也可以通過mysql 官方出的插件,直接在excel 裡進行SQL查詢并傳回。不過為了便于在頁面上進行一鍵式的導出,這裡通過PHPExcel 子產品實作一鍵式導出 。在操作過程中,遇到最難搞的問題就是,可以正常導入到本機,但通過web導出儲存時,打開生成的excel 檔案提示檔案格式不正确或受損。後來通過将mysql conn 串直接寫到該頁面時,可以正常導出,後面又分出來,發現conn.php最後的 " ?> " 不加,可以正常,加上不成功 。最後也不知道改了什麼地方,來回折騰了兩天,又可以了。
實作代碼如下:
include 'lib/PHPExcel.php';
include 'conn.php';
//include 'lib/PHPExcel/IOFactory.php';
//include 'lib/PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', '主機名')
->setCellValue('B1', '觸發ID')
->setCellValue('C1', '描述資訊')
->setCellValue('D1', '時間');
$sql="select host,triggerid,description,value,time from newevent
where description like '%磁盤%' and value=1
and eventid in (select max(eventid) from newevent group by triggerid )";
$q=$mysqli->query($sql);
$rowCount=2;
while($row=$q->fetch_assoc()){
//print_r($row);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rowCount, $row['host']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['triggerid']);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['description']);
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['time']);
$rowCount++;
}
//$q->close();
$q->free();
$mysqli->close();
//标題填充
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFill()->getStartColor()->setARGB('00ffff00');
//Set column widths 設定列寬度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
// Redirect output to a client’s web browser (Excel2007) 通過web頁面導出
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="disk.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 2016 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');
$objWriter->save('php://output');
exit;
?>
其中conn.php内容如下:
$mysqli = new mysqli('10.211.137.173','report','password','report');
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
$mysqli->query("SET NAMES 'utf8'");
最後通過URL打開該連結後,會自動下載下傳excel 檔案,打開後,格式如下:
寫在最後:
PHPExcel 相對重量級,可以設定的項也比較多,如果想使用輕量級的,可以使用 PHP_XLSXWriter 。