通过mysql+PHPExcel实现查询导出

通过php+mysql自建了几个zabbix事件查询的页面,不过后面在web页面上查询完,需要将结果导出到excel 时,感觉不怎么方便,当然可以通过python 实现快速导出到excel 。也可以通过mysql 官方出的插件,直接在excel 里进行SQL查询并返回。不过为了便于在页面上进行一键式的导出,这里通过<a href="https://github.com/PHPOffice" target="_blank" rel="noopener">PHPExcel</a> 模块实现一键式导出 。在操作过程中,遇到最难搞的问题就是,可以正常导入到本机,但通过web导出保存时,打开生成的excel 文件提示文件格式不正确或受损。后来通过将mysql conn 串直接写到该页面时,可以正常导出,后面又分出来,发现conn.php最后的 " ?> " 不加,可以正常,加上不成功 。最后也不知道改了什么地方,来回折腾了两天,又可以了。



实现代码如下:



<br />
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);
/* 直接导出到本地
// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx fileobjWriter = new PHPExcel_Writer_Excel2007(objPHPExcel);
// Write the Excel file to filename some_excel_file.xlsx in the current directoryobjWriter->save('some_excel_file.xlsx');
*/
// 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内容如下:



<br />
connect_errno) {
    printf("Connect failed: %s\n", mysqli->connect_error);
    exit();
}mysqli->query("SET NAMES 'utf8'");
最后通过URL打开该链接后,会自动下载excel 文件,打开后,格式如下:



<img src="https://www.361way.com/wp-content/uploads/2017/03/phpexcel-disk.jpg" title="phpexcel" alt="phpexcel" height="126" width="840" />



<strong>写在最后:</strong>



PHPExcel 相对重量级,可以设置的项也比较多,如果想使用轻量级的,可以使用 <a href="https://github.com/mk-j/PHP_XLSXWriter" target="_blank" rel="noopener">PHP_XLSXWriter</a> 。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注