获取API后将数据清洗后输出excle的实战案例

项目需求

1. 客户指定API，爬取数据格式如下

objid: 11405,
probe: "北师大附中",
group: "安防前端设施",
device: "C4办公室外北通道|192.168.10.10",
sensor: "PING 1",
status: "正常运行"
status_raw: 3

2. 如要将对应的JSON数据，自动保存为excle表格，其中device: "C4办公室外北通道|192.168.10.10",需要分拆成两个字段保存。即在excle保存成两列；
3. 后端php，前端js;

需求分析

1. 爬出JSON数据，保存为表格，使用JsonExportExcel.min.js插件；
2. 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));
		}
	}

1. 将获取的JSON转化为数组对象；
2. 将新的数组对象进行遍历，拆分 l i s t [ ′ s e n s o r s ′ ] [ list['sensors'][ list[′sensors′][i][‘device’] = explode(’|’, d a t a I n f o [ dataInfo[ dataInfo[i][‘device’])[0]和 l i s t [ ′ s e n s o r s ′ ] [ list['sensors'][ list[′sensors′][i][‘ip’] = explode(’|’, d a t a I n f o [ dataInfo[ dataInfo[i][‘device’])[1]；
3. 重新编码为JSON字符串；
4. 输入字符串；

前端导出

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();
        }

lockdatav Done!