MQTT南向

生成点位表

1.接收前端参数

@PostMapping("/variableName")
public void VariableName(
    @RequestPart("nameGateWay") String nameGateWay,
    @RequestPart("communication")String communication,
    @RequestPart("equipmentName") String equipmentName,
    @RequestPart("file") MultipartFile file, HttpServletResponse response)

格式:multipart/form-data;

获取工作表:

Workbook sheets = WorkbookFactory.create(file.getInputStream());

2.生成点位表

service:

 public List<String> variableExcel(VarVo varVo,Workbook file) {
        List<String> list = new ArrayList<>();
        // 获取excel的sheet页
        Sheet sheet = file.getSheetAt(0);
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //转换成工具需要的实体类
        VariableVo variableVo = new VariableVo();
        variableVo.setNameGateWay(varVo.getNameGateWay());
        variableVo.setCommunication(varVo.getCommunication());
        variableVo.setEquipmentName(varVo.getEquipmentName());
        //调用固定方法生成点位表实体类
        List<Variable> variables = varUtil.varExcel(variableVo, file);
        //调用方法生成点位表
        String variable = excelUtil.makeVariable(variables);
        list.add(variable);
        return list;
    }

pojo:

@Data
public class VariableVo {
    //网关名称
    private String nameGateWay;
    //通信链接
    private String communication;
    //通信设备名称
    private String equipmentName;
    //设备区域
    private String locations;
    //设备名称
    private List<String> name;
}
@Data
public class Variable {
    //网关名称
    private String nameGateWay;
    //通信链接
    private String communication;
    //通信设备名称
    private String equipmentName;
    //变量名称
    private String variableName;
    //描述
    private String describe;
    //类型
    private String type;
    //初始值
    private String initialValue;
    //单位
    private String company;
    //状态
    private String state;
    //地址
    private String address;
    //记录趋势值
    private String recordTrendValue;
    //寄存器类型
    private String registerType;
    //寄存器数据类型
    private String registerDataType;
    //上限有效性
    private String upperLimitValidity;
    //下限有效性
    private String lowerBoundValidity;
    //增量有效性
    private String incrementalEffectiveness;
    //偏移量
    private String offset;
}

VarUtil:

使用poi的jar包;

1,创建点位表对应的实体类集合,获取传入的X2B的网关获取的点位表,获取全部的行数

2,遍历每一行数据;

3,创建点位表每一行数据的实体类,将传进来的参数放进实体类里。

​ //网关名称
​ private String nameGateWay;
​ //通信链接
​ private String communication;
​ //通信设备名称
​ private String equipmentName;

4,根据X2B表中需要的数据,解析出来放进实体类:

变量名称:tcp_Channel_1_HBA716A_BI1

获取表中变量名称所在的位置的数据,转为字符串

Cell cell = row.getCell(1);
String variableName = getValue(cell).toString();

解析通过字符串的截取,获取我们想要的点位

获取第三个下划线后的字符串

String str1 = variableName.substring(variableName.indexOf("_",variableName.indexOf("_",variableName.indexOf("_") + 1) + 1)+1);

类型:

需要大写,将类型转为大写

Cell cell1 = row.getCell(4);
            String type = getValue(cell1).toString();
            if (type.equals("Boolean")){
                variable.setType("BOOLEAN");
            }else if (type.equals("Float")){
                variable.setType("DOUBLE");
            }

地址:

表中数据是整形,获取的字符串是浮点型,我们截取小数点前面的数值作为地址;

Cell cell2 = row.getCell(3);
            String address = getValue(cell2).toString();
            String substring = address.substring(0,address.indexOf("."));
            variable.setAddress(substring);

描述:

直接放进实体类不需要处理

状态:

统一成 NORMAL

记录趋势值:

统一为 true

寄存器类型:

根据获取到的值的不同,转为相应的类型;

Cell cell5 = row.getCell(5);
            String registerType = getValue(cell5).toString();
            switch(registerType) {
                case "1.0":
                    variable.setRegisterType("AO");
                    break;
                case "0.0":
                    variable.setRegisterType("AI");
                    break;
                case "3.0":
                variable.setRegisterType("BI");
                    break;
                case "4.0":
                variable.setRegisterType("BO");
                    break;
                default:
                    break;
        }

寄存器数据类型:

需要大写,将类型转为大写

Cell cell6 = row.getCell(4);
            String registerDataType = getValue(cell6).toString();
            if (registerDataType.equals("Boolean")){
                variable.setRegisterDataType("BOOLEAN");
            }else if (registerDataType.equals("Float")){
                variable.setRegisterDataType("FLOAT");
            }
            variables.add(variable);

其他数值为空,不做处理;

下面是完整的代码:

package com.grt.excel.util;
import com.grt.excel.pojo.Variable;
import com.grt.excel.pojo.VariableVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

import static io.reactivex.internal.util.NotificationLite.getValue;
@Component
public class VarUtil {
    public List<Variable> varExcel(VariableVo variableVo, Workbook file) {

        // 获取excel的sheet页
        Sheet sheet = file.getSheetAt(0);
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //创建一个点位表集合
        List<Variable> variables = new ArrayList<>();
        //遍历行
        for (int i = 1; i < rows; i++) {
        //将输入的信息放进实体类
        Variable variable = new Variable();
        variable.setNameGateWay(variableVo.getNameGateWay());
        variable.setCommunication(variableVo.getCommunication());
        variable.setEquipmentName(variableVo.getEquipmentName());
        // 读取左上端单元格
        Row row = sheet.getRow(i);
        // 行不为空
        if (row != null) {
            //获取到Excel文件中的所有的列
            int cells = row.getPhysicalNumberOfCells();
            //变量名称
            Cell cell = row.getCell(1);
            String variableName = getValue(cell).toString();
            String str1 = variableName.substring(variableName.indexOf("_",variableName.indexOf("_",variableName.indexOf("_") + 1) + 1)+1);//包含本身位置
            variable.setVariableName(str1);
            //类型
            Cell cell1 = row.getCell(4);
            String type = getValue(cell1).toString();
            if (type.equals("Boolean")){
                variable.setType("BOOLEAN");
            }else if (type.equals("Float")){
                variable.setType("DOUBLE");
            }
            //地址
            Cell cell2 = row.getCell(3);
            String address = getValue(cell2).toString();
            String substring = address.substring(0,address.indexOf("."));
            variable.setAddress(substring);
            //描述
            Cell cell3 = row.getCell(7);
            String describe = getValue(cell3).toString();
            variable.setDescribe(describe);
            //初始值
//                Cell cell4 = row.getCell(5);
//                String initialValue = getValue(cell4);
//                variable.setInitialValue(initialValue);
            //状态
            variable.setState("NORMAL");
            //记录趋势值
            variable.setRecordTrendValue("true");
            //寄存器类型
            Cell cell5 = row.getCell(5);
            String registerType = getValue(cell5).toString();
            switch(registerType) {
                case "1.0":
                    variable.setRegisterType("AO");
                    break;
                case "0.0":
                    variable.setRegisterType("AI");
                    break;

                case "3.0":
                    variable.setRegisterType("BI");
                    break;
                case "4.0":
                    variable.setRegisterType("BO");
                    break;
                default:
                    break;
            }
            //寄存器数据类型
            Cell cell6 = row.getCell(4);
            String registerDataType = getValue(cell6).toString();
            if (registerDataType.equals("Boolean")){
                variable.setRegisterDataType("BOOLEAN");
            }else if (registerDataType.equals("Float")){
                variable.setRegisterDataType("FLOAT");
            }
            variables.add(variable);
        }

        }
            return variables;
    }
}

调用生成点位表的工具:

传入点位表实体类的集合,遍历集合,依次将代表每一行的实体类放进表里,并生成;

package com.grt.excel.util;

import com.grt.excel.pojo.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import static io.reactivex.internal.util.NotificationLite.getValue;

//@author Z 张 P
//        package com.grt.excel.util;
@Component
public class ExcelUtil {
    //创建文件存放的路径,默认桌面
    public String temp = "D:";
    //描述设备名称去重
    public ListName nameSet(Sheet sheet, List<String> name,Integer integer){
        ListName listName = new ListName();
        // 获取excel的sheet页
        //一会提交用的集合
        List<String> list = new ArrayList<>();

        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //自动去重的集合
        Set<String> set = new HashSet<String>();
        //不去重的集合
        List<String> list1 = new ArrayList<>();
        Set<String> stringSet = new HashSet<>();
        //遍历行
        for (int i = 1; i < rows; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                //获取描述
                Cell cell = row.getCell(integer);
                String Description = getValue(cell).toString();
                String substring = Description.substring(0,Description.indexOf("/"));
                list1.add(substring);
                stringSet.add(substring);
                for (String s : name) {
                    if (s.equals(substring)){
                        set.add(substring);
                    }
                }

            }
        }
        //去重后的元素放进新的集合里
        set.forEach(s -> list.add(s));
        listName.setSet(list);
        listName.setList(list1);
        listName.setStringSet(stringSet);
        return listName;
    }
    //创建表工具
    public MakeXlsx makeXLSX(String[] heads) {
        //创建工作簿
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet = hssfWorkbook.createSheet();

        hssfWorkbook.setSheetName(0, "Sheet1");
        //创建表头
        XSSFRow head = sheet.createRow(0);

        for (int i = 0; i < heads.length; i++) {
            XSSFCell cell = head.createCell(i);
            cell.setCellValue(heads[i]);
        }
        MakeXlsx makeXlsx = new MakeXlsx();
        makeXlsx.setSheet(sheet);
        makeXlsx.setHead(head);
        return makeXlsx;
    }
    //创建表工具
    public String endMakeXLSX(XSSFWorkbook workbook,String s){

        //创建临时文件的目录
        File file = new File(temp);
        if(!file.exists()){
            file.mkdirs();
        }
        //临时文件路径/文件名
        String downloadPath = file + "\\"  + s + ".xlsx";
        OutputStream outputStream = null;
        try {
            //使用FileOutputStream将内存中的数据写到本地,生成临时文件
            outputStream = new FileOutputStream(downloadPath);
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //返回文件路径
        return downloadPath;
    }
    //生成设备属性表
    public String makeAttribute(List<Attribute> list){
        String[] heads = {"设备区域","设备名称","属性名称","关联点位","描述","用途"};

        MakeXlsx makeXlsx = makeXLSX(heads);
        for (int i = 1;i <= list.size();i++) {
            Attribute attribute = list.get(i - 1);
            //创建行,从第二行开始,所以for循环的i从1开始取
            XSSFRow row = makeXlsx.getSheet().createRow(i);
            //创建单元格,并填充数据
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(attribute.getLocation());
            cell = row.createCell(1);
            cell.setCellValue(attribute.getEquipmentName());

            cell = row.createCell(2);
            cell.setCellValue(attribute.getName());
            cell = row.createCell(3);
            cell.setCellValue(attribute.getVariableName());
            cell = row.createCell(4);
            cell.setCellValue(attribute.getDescribe());
            cell = row.createCell(5);
            cell.setCellValue(attribute.getPurpose());
        }
        XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
        String top = "设备属性表";
        return endMakeXLSX(workbook, top);
    }
    //生成设备表
    public String makeEquipment(List<Equipment> list){

        String[] heads = {"设备区域","设备名称","设备分类","设备供应商","供应商电话","设备维保商","维保商电话","设备寿命","采购价格","安装时间","维保时间","设备图片"};

        MakeXlsx makeXlsx = makeXLSX(heads);
        for (int i = 1;i <= list.size();i++) {
            Equipment equipment = list.get(i - 1);
            //创建行,从第二行开始,所以for循环的i从1开始取
            XSSFRow row = makeXlsx.getSheet().createRow(i);
            //创建单元格,并填充数据
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(equipment.getLocation());
            cell = row.createCell(1);
            cell.setCellValue(equipment.getName());
            cell = row.createCell(7);
            cell.setCellValue(equipment.getLife_span());
            cell = row.createCell(8);
            cell.setCellValue(equipment.getPurchase_price());
        }
        XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
        String top = "设备表";
       return endMakeXLSX(workbook,top);
    }
    //生成点位表
    public String makeVariable(List<Variable> list) {

        String[] heads = {"网关名称","通信连接","通信设备名称","变量名称","描述","类型","初始值","单位","状态","地址","记录趋势值","寄存器类型","寄存器数据类型","上限有效性","下限有效性","增量有效性","偏移量"};
        MakeXlsx makeXlsx = makeXLSX(heads);
        for (int i = 1;i <= list.size();i++) {
            Variable variable = list.get(i - 1);
            //创建行,从第二行开始,所以for循环的i从1开始取
            XSSFRow row = makeXlsx.getSheet().createRow(i);
            //创建单元格,并填充数据
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(variable.getNameGateWay());
            cell = row.createCell(1);
            cell.setCellValue(variable.getCommunication());
            cell = row.createCell(2);
            cell.setCellValue(variable.getEquipmentName());
            cell = row.createCell(3);
            cell.setCellValue(variable.getVariableName());
            cell = row.createCell(4);
            cell.setCellValue(variable.getDescribe());
            cell = row.createCell(5);
            cell.setCellValue(variable.getType());
            cell = row.createCell(6);
            cell.setCellValue(variable.getInitialValue());
            cell = row.createCell(7);
            cell.setCellValue(variable.getCompany());
            cell = row.createCell(8);
            cell.setCellValue(variable.getState());
            cell = row.createCell(9);
            cell.setCellValue(variable.getAddress());
            cell = row.createCell(10);
            cell.setCellValue(variable.getRecordTrendValue());
            cell = row.createCell(11);
            cell.setCellValue(variable.getRegisterType());
            cell = row.createCell(12);
            cell.setCellValue(variable.getRegisterDataType());
            cell = row.createCell(14);
            cell.setCellValue(variable.getUpperLimitValidity());
            cell = row.createCell(15);
            cell.setCellValue(variable.getLowerBoundValidity());
            cell = row.createCell(16);
            cell.setCellValue(variable.getIncrementalEffectiveness());
            cell = row.createCell(17);
            cell.setCellValue(variable.getOffset());
        }
        XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
        String top = "点位表";
        return endMakeXLSX(workbook,top);
    }
}

3.文件下载

downloadExcel.exportStu(response, "点位表.xlsx", excallName.get(0));

根据工具类,传入参数下载的文件名称,当前文件地址。提供下载

下载文件工具类:

package com.grt.excel.util;

import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.*;

//@author Z 张 P
//        package com.grt.excel.util;
@Component
public class DownloadExcel {
    public void exportStu(HttpServletResponse response, String name,String downloadPath){
        //设置默认的下载文件名
        try {
            //避免文件名中文乱码,将UTF8打散重组成ISO-8859-1编码方式
            name = new String (name.getBytes("UTF8"),"ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //设置响应头的类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //让浏览器下载文件,name是上述默认文件下载名
        response.addHeader("Content-Disposition","attachment;filename=\"" + name + "\"");
        InputStream inputStream=null;
        OutputStream outputStream=null;
        //在service层中已经将数据存成了excel临时文件,并返回了临时文件的路径
        //根据临时文件的路径创建File对象,FileInputStream读取时需要使用
        File file = new File(downloadPath);
        try {
            //通过FileInputStream读临时文件,ServletOutputStream将临时文件写给浏览器
            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            int len = -1;
            byte[] b = new byte[1024];
            while((len = inputStream.read(b)) != -1){
                outputStream.write(b);
            }
            //刷新
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭输入输出流
            try {
                if(inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if(outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
        //最后才能,删除临时文件,如果流在使用临时文件,file.delete()是删除不了的
        file.delete();
    }
}

JJM 发布于 2022-3-7 14:46

InfluxDB使用教程:Linux常用操作和命令

InfluxDB的在Linux下的常用的基础操作。

启动:

sudo service influxdb start

重启:

service influxdb restart

2.在Linux中如何使用:
进入influxDB,输入命令:

influx

查看InfluxDB状态:

SHOW STATS

创建一个数据库:

create database "db_name"

显示所有的数据库

show databases 

删除数据库
注:不区分大小写,会删除掉所有大小写不同,但名字相同的库

drop database "db_name" 

使用数据库

use db_name 

查看该数据库下所有表

show measurements 

创建表
注:直接在插入数据的时候指定表名,表自动创建,字段类型由传入的值决定。

insert test,host=127.0.0.1,monitor_name=test count=1 

删除表

drop measurement measurement_name

查询表

select * from database limit 10

添加条件:
注:条件中的字符串需要用单引号包裹

SELECT field1,field2 FROM "tableName"   WHERE time > '2018-08-15T02:29:20Z' AND time < '2018-08-15T02:30:20Z' Order by time DESC

查看series

show series from weather

创建保存策略:
注:保存策略即存储在数据库中的数据,多长时间删除一次。

create retention policy  "2_hours" on test_db duration 2h replication 1 default

解释:在数据test_db,添加了一个名字叫做 2_hours,duration为2小时,副本为1,并将其设置为默认策略。采用默认策略的表,将会执行相关配置。

修改完默认策略后,所有不是以“2_hours"为保留策略的表,都将不能直接查询,需要在表名measurement前,加上策略名,如:

select * from "defalut".weather

查看保存策略

show retention policies on test_db(数据库名)

修改保留策略:

ALTER  retention policy "default" on test_db DEFAULT(修改默认)

或者在关键字上加引号也可以:

ALTER  retention policy "default" on "test_db" duration 719h

删除保留策略:

drop retention policy xxxName  on test_db

3.在浏览器中如何使用
InfluxDB默认是通过Http请求访问数据库的,也支持UDP协议(默认关闭),所以可以通过浏览器直接查询或新增删除数据。

格式:数据库地址 + 端口 + query?db = 数据库名&q = 查询或删除或插入的SQL语句
1
举例:

http://10.10.1.2:8086/query?db=testdb&q=select * from measurement limit 10

JJM 发布于 2022-3-2 16:58

emqx 常用命令

命令 作用
emqx start 启动emqtt
emqx stop 关闭emqtt
emqx restart 重启emqtt
emqx_ctl status 查看emqtt状态

JJM 发布于 2022-3-1 16:09

time zone AsiaShanghai 报错解决方法

[该文章已加密,请点击标题输入密码访问]


JJM 发布于 2022-3-1 15:40

个人资料

    blogger

    JJM

    日常记录

搜索

日历