项目需求
- 客户指定API，爬取数据格式如下
objid: 11405, probe: "北师大附中", group: "安防前端设施", device: "C4办公室外北通道|192.168.10.10", sensor: "PING 1", status: "正常运行" status_raw: 3
- 如要将对应的JSON数据，自动保存为excle表格，其中
device: "C4办公室外北通道|192.168.10.10",需要分拆成两个字段保存。即在excle保存成两列； - 后端php，前端js;
需求分析
- 爬出JSON数据，保存为表格，使用
JsonExportExcel.min.js插件； - device数据分列，需要在获取数据后，对数据进行遍历循环，分列成两列即可；
解决方案
数据并进行格式转化
public function getObjReport() { global $res, $CONF; $group_id = get_param("id"); if ($group_id && $group_id != "0") { $url = $CONF["apiDomain"] . "api/table.json?content=sensors&output=json&columns=objid,probe,group,device,sensor,status&id=" . $group_id . "&username=" . $CONF["apiUser"] . "&Passhash=" . $CONF["apiPass"]; $res = str_replace("prtg-version", "prtg_version", getAPI($url)); $reArr = json_decode(getAPI($url), true); $dataInfo = $reArr["sensors"]; //var_dump($dataInfo); //遍历数组将device分拆; $list = array(); for ($i = 0; $i < count($dataInfo); $i++) { $list['sensors'][$i]['objid'] = $dataInfo[$i]['objid']; $list['sensors'][$i]['probe'] = $dataInfo[$i]['probe']; $list['sensors'][$i]['group'] = $dataInfo[$i]['group']; $list['sensors'][$i]['device'] = explode('|', $dataInfo[$i]['device'])[0]; $list['sensors'][$i]['ip'] = explode('|', $dataInfo[$i]['device'])[1]; $list['sensors'][$i]['sensor'] = $dataInfo[$i]['sensor']; $list['sensors'][$i]['status'] = $dataInfo[$i]['status']; $list['sensors'][$i]['status_raw'] = $dataInfo[$i]['status_raw']; } //var_dump($list); die(json_encode_lockdata($list)); } else { $res["sensors"] = ""; die(json_encode_lockdata($res)); } }
前端导出
ajax调用接口
layui.use(['form', 'layer', 'laydate'], function () { var $ = layui.jquery; var form = layui.form, layer = layui.layer; form.on('submit(add)', function (data) { $.ajax({ type: "get", url: "api/api.php?act=getObjReport&token=3cab7ce4142608c0f40c785b5ab5ca24", async: true, data: { id: $('#id').val(), }, dataType: "json", success: function (res) { console.log(res); if (res.sensors) { //生成报表; JsonToExcel('客户信息', res.sensors); } else { layer.alert("筛选条件无数据", {icon: 6}); } } }); return false; }); }
封装函数
//导出报表; function JsonToExcel(filename, actData) { var option = {}; option.fileName = filename; option.datas = [ { sheetData: actData, sheetName: 'sheet1', sheetFilter: ['objid', 'probe', 'group', 'device', 'ip', 'sensor', 'status', 'status_raw'], sheetHeader: ['设备ID', '单位', '设备归属', '设备名称', '设备IP', '传感器类型', '现在状态', '现在状态代号'] } ]; var toExcel = new ExportJsonExcel(option); toExcel.saveExcel(); }
ockdatav Done!
