node-red-contrib-prib-functions - npm Package Compare versions

Comparing version 0.13.0 to 0.14.0

package.json

 {
 	"name": "node-red-contrib-prib-functions",
-	"version": "0.13.0",
+	"version": "0.14.0",
 	"description": "Node-RED added node functions.",
@@ -8,3 +8,4 @@ "dependencies": {
 		"avsc": "*",
-		"fast-xml-parser": "*"
+		"fast-xml-parser": "*",
+		"xlsx": "*"
 	},
@@ -88,5 +89,8 @@ "devDependencies": {
 		"variance",
-		"weigthed",
+		"weighted",
 		"Weighted Moving Average",
 		"WMA",
+		"xlsx",
+		"XLSX",
+		"xcel",
 		"xml",
@@ -93,0 +97,0 @@ "XML",

README.md

@@ -77,2 +77,3 @@ # [node-red-contrib-prib-functions][2]
 *	Array to Messages
+*	Array to xlsx / xlsx object (excel uses [xlsx][7])  
 *	AVRO to JSON (uses [avsc][6]) 
@@ -95,2 +96,3 @@ *	Confluence to JSON
 *	JSON to String
+*	JSON to xlsx / xlsx object (excel uses [xlsx][7])  
 *	JSON to XML (uses [fast-xml-parser][4])
@@ -109,2 +111,3 @@ *	String to JSON
 *	snappy uncompress (uses [snappy][5], must install separately)
+*	xlsx / xlsx object to array/JSON (excel uses [xlsx][7])  
 *	XML to JSON (uses [fast-xml-parser][4])
@@ -114,2 +117,4 @@
 
+With xlsx object one can use the function in [xlsx][7] against the object in functions node.
+
 Example AVRO with schema
@@ -244,2 +249,4 @@
 
+0.14.0 add xlsx transformer
+
 0.13.0 Confluence AVRO message converter
@@ -311,1 +318,3 @@
 [6]: https://www.npmjs.com/package/avsc "avsc"
+
+[7]: https://www.npmjs.com/package/xlsx "xlsx"

test/transformTest.json

 [
     {
-        "id": "47f03d3d.36c1b4",
+        "id": "924bb2b7.03fd1",
         "type": "tab",
-        "label": "transform",
+        "label": "transform xlsx",
         "disabled": false,
@@ -10,6 +10,6 @@ "info": ""
     {
-        "id": "8ec55f9e.dcfa9",
+        "id": "2a9b4180.1b8ffe",
         "type": "inject",
-        "z": "47f03d3d.36c1b4",
-        "name": "",
+        "z": "924bb2b7.03fd1",
+        "name": "json xcel worksheets",
         "props": [
@@ -29,9 +29,9 @@ {
         "topic": "",
-        "payload": "{\"name\":\"testname\"}",
+        "payload": "{\"worksheet1\":[[11,12],[21,22],[31,32]],\"worksheet2\":[[11,12],[21,22]]}",
         "payloadType": "json",
         "x": 110,
-        "y": 100,
+        "y": 60,
         "wires": [
             [
-                "7ee97c55.b8e244"
+                "b99caf39.d57c9"
             ]
@@ -41,27 +41,10 @@ ]
     {
-        "id": "dab7ec27.2b1c8",
-        "type": "debug",
-        "z": "47f03d3d.36c1b4",
-        "name": "",
-        "active": true,
-        "tosidebar": true,
-        "console": false,
-        "tostatus": false,
-        "complete": "true",
-        "targetType": "full",
-        "statusVal": "",
-        "statusType": "auto",
-        "x": 810,
-        "y": 80,
-        "wires": []
-    },
-    {
-        "id": "7ee97c55.b8e244",
+        "id": "b99caf39.d57c9",
         "type": "transform",
-        "z": "47f03d3d.36c1b4",
+        "z": "924bb2b7.03fd1",
         "name": "",
-        "actionSource": "JSON",
-        "actionTarget": "AVRO",
+        "actionSource": "Array",
+        "actionTarget": "XLSX",
         "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
+        "targetProperty": "msg.xlsx",
         "topicProperty": "msg.topic",
@@ -74,11 +57,11 @@ "maxMessages": 1000,
         "delimiter": ",",
-        "x": 520,
-        "y": 100,
+        "x": 380,
+        "y": 60,
         "wires": [
             [
-                "dab7ec27.2b1c8",
-                "f29832ad.9b49a"
+                "e234842f.2946a8",
+                "7bde11d2.378e6"
             ],
             [
-                "dab7ec27.2b1c8"
+                "3db2b919.ea68d6"
             ]
@@ -88,10 +71,10 @@ ]
     {
-        "id": "f29832ad.9b49a",
+        "id": "7bde11d2.378e6",
         "type": "transform",
-        "z": "47f03d3d.36c1b4",
+        "z": "924bb2b7.03fd1",
         "name": "",
-        "actionSource": "AVRO",
-        "actionTarget": "JSON",
+        "actionSource": "Array",
+        "actionTarget": "XLSXObject",
         "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
+        "targetProperty": "msg.xlsxobject",
         "topicProperty": "msg.topic",
@@ -104,10 +87,10 @@ "maxMessages": 1000,
         "delimiter": ",",
-        "x": 520,
-        "y": 180,
+        "x": 360,
+        "y": 120,
         "wires": [
             [
-                "dab7ec27.2b1c8"
+                "8e6f5298.ccab4"
             ],
             [
-                "dab7ec27.2b1c8"
+                "3db2b919.ea68d6"
             ]
@@ -117,10 +100,10 @@ ]
     {
-        "id": "cd80e484.165808",
+        "id": "8e6f5298.ccab4",
         "type": "transform",
-        "z": "47f03d3d.36c1b4",
+        "z": "924bb2b7.03fd1",
         "name": "",
-        "actionSource": "snappy",
-        "actionTarget": "Compress",
-        "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
+        "actionSource": "XLSX",
+        "actionTarget": "Array",
+        "sourceProperty": "msg.xlsx",
+        "targetProperty": "msg.array",
         "topicProperty": "msg.topic",
@@ -133,11 +116,10 @@ "maxMessages": 1000,
         "delimiter": ",",
-        "x": 520,
-        "y": 360,
+        "x": 380,
+        "y": 180,
         "wires": [
             [
-                "dab7ec27.2b1c8",
-                "36299843.a28b98"
+                "3db2b919.ea68d6"
             ],
             [
-                "dab7ec27.2b1c8"
+                "3db2b919.ea68d6"
             ]
@@ -147,39 +129,10 @@ ]
     {
-        "id": "dac52dcb.e1038",
-        "type": "inject",
-        "z": "47f03d3d.36c1b4",
-        "name": "",
-        "props": [
-            {
-                "p": "payload"
-            },
-            {
-                "p": "topic",
-                "vt": "str"
-            }
-        ],
-        "repeat": "",
-        "crontab": "",
-        "once": false,
-        "onceDelay": 0.1,
-        "topic": "",
-        "payload": "a test",
-        "payloadType": "str",
-        "x": 70,
-        "y": 360,
-        "wires": [
-            [
-                "cd80e484.165808"
-            ]
-        ]
-    },
-    {
-        "id": "36299843.a28b98",
+        "id": "e234842f.2946a8",
         "type": "transform",
-        "z": "47f03d3d.36c1b4",
+        "z": "924bb2b7.03fd1",
         "name": "",
-        "actionSource": "snappy",
-        "actionTarget": "Uncompress",
-        "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
+        "actionSource": "XLSX",
+        "actionTarget": "XLSXObject",
+        "sourceProperty": "msg.xlsx",
+        "targetProperty": "msg.xlsx2xlsxObject",
         "topicProperty": "msg.topic",
@@ -192,93 +145,45 @@ "maxMessages": 1000,
         "delimiter": ",",
-        "x": 500,
-        "y": 440,
+        "x": 660,
+        "y": 60,
         "wires": [
             [
-                "dab7ec27.2b1c8"
+                "5312687f.3fff48"
             ],
-            [
-                "dab7ec27.2b1c8"
-            ]
+            []
         ]
     },
     {
-        "id": "236867e5.848bf8",
-        "type": "inject",
-        "z": "47f03d3d.36c1b4",
-        "name": "{\"schema\":1,\"data\":{\"name\":\"testname\"}}",
-        "props": [
-            {
-                "p": "payload"
-            },
-            {
-                "p": "topic",
-                "vt": "str"
-            }
-        ],
-        "repeat": "",
-        "crontab": "",
-        "once": false,
-        "onceDelay": 0.1,
-        "topic": "",
-        "payload": "{\"schema\":1,\"data\":{\"name\":\"testname\"}}",
-        "payloadType": "json",
-        "x": 180,
-        "y": 240,
-        "wires": [
-            [
-                "f3f80604.ea20c8"
-            ]
-        ]
+        "id": "5312687f.3fff48",
+        "type": "debug",
+        "z": "924bb2b7.03fd1",
+        "name": "base",
+        "active": true,
+        "tosidebar": true,
+        "console": false,
+        "tostatus": false,
+        "complete": "true",
+        "targetType": "full",
+        "statusVal": "",
+        "statusType": "auto",
+        "x": 870,
+        "y": 60,
+        "wires": []
     },
     {
-        "id": "f3f80604.ea20c8",
-        "type": "transform",
-        "z": "47f03d3d.36c1b4",
+        "id": "3db2b919.ea68d6",
+        "type": "debug",
+        "z": "924bb2b7.03fd1",
         "name": "",
-        "actionSource": "JSON",
-        "actionTarget": "Confluence",
-        "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
-        "topicProperty": "msg.topic",
-        "maxMessages": 1000,
-        "schema": "{\"1\":{\"type\":\"record\",\"fields\":[{\"name\":\"name\",\"type\":\"string\"}]}}",
-        "schemaType": "json",
-        "skipLeading": 0,
-        "skipTrailing": 0,
-        "delimiter": ",",
-        "x": 510,
-        "y": 240,
-        "wires": [
-            [
-                "c5459a2b.e5e1d8",
-                "dab7ec27.2b1c8"
-            ],
-            []
-        ]
-    },
-    {
-        "id": "c5459a2b.e5e1d8",
-        "type": "transform",
-        "z": "47f03d3d.36c1b4",
-        "name": "",
-        "actionSource": "Confluence",
-        "actionTarget": "JSON",
-        "sourceProperty": "msg.payload",
-        "targetProperty": "msg.payload",
-        "topicProperty": "msg.topic",
-        "maxMessages": 1000,
-        "schema": "{\"1\":{\"type\":\"record\",\"fields\":[{\"name\":\"name\",\"type\":\"string\"}]}}",
-        "schemaType": "json",
-        "skipLeading": 0,
-        "skipTrailing": 0,
-        "delimiter": ",",
-        "x": 520,
-        "y": 300,
-        "wires": [
-            [
-                "dab7ec27.2b1c8"
-            ],
-            []
-        ]
+        "active": true,
+        "tosidebar": true,
+        "console": false,
+        "tostatus": false,
+        "complete": "true",
+        "targetType": "full",
+        "statusVal": "",
+        "statusType": "auto",
+        "x": 870,
+        "y": 160,
+        "wires": []
     }
 ]

transform/transform.js

@@ -9,3 +9,3 @@ const logger = new (require("node-red-contrib-logger"))("transform");
 	process=require('process');
-let  avsc,snappy,xmlParser,json2xmlParser;
+let  avsc,snappy,xmlParser,json2xmlParser,XLSX;
 const {ISO8583BitMapId,ISO8583BitMapName}=require("./ISO8583BitMap");
@@ -44,2 +44,53 @@ let ISO8583,ISO8583message;
 }
+
+function addWorksheet2JSON(object,worksheet,workbook,options){
+	object[worksheet]=XLSX.utils.sheet_to_json(workbook.Sheets[worksheet],options);
+	if(options.header) object[worksheet].shift();
+	if(logger.active) logger.send({label:"addWorksheet2JSON",object:object,worksheet:worksheet})
+	return object;
+}
+function XLSXObjectToJSON(RED,node,msg,data){
+	return data.SheetNames.reduce((a,worksheet)=>addWorksheet2JSON(a,worksheet,data),{})
+}
+function XLSXToArray(RED,node,msg,data){
+	return XLSXObjectToArray(RED,node,msg,XLSXToXLSXObject(RED,node,msg,data));
+}
+function XLSXToJSON(RED,node,msg,data){
+	return XLSXObjectToJSON(RED,node,msg,XLSXToXLSXObject(RED,node,msg,data));
+}
+function XLSXToXLSXObject(RED,node,msg,data){
+	return XLSX.read(data, {raw:true,type: 'buffer' });
+}
+function XLSXObjectToArray(RED,node,msg,data){
+	return data.SheetNames.reduce((a,worksheet)=>addWorksheet2JSON(a,worksheet,data,{header:1,raw:true}),{})
+}
+function JSONToXLSX(RED,node,msg,data){
+	const workbook=JSONToXLSXObject(RED,node,msg,data);
+	return XLSX.write(workbook, {bookType:"xlsx", type:'buffer'}); 
+}
+function JSONToXLSXObject(RED,node,msg,data){
+	const workbook = XLSX.utils.book_new();
+	for(const worksheet in data) {
+		const  ws=XLSX.utils.json_to_sheet(data[worksheet]);
+		XLSX.utils.book_append_sheet(workbook, ws, worksheet);
+	}
+	return workbook;
+}
+function ArrayToXLSX(RED,node,msg,data){
+	const workbook=ArrayToXLSXObject(RED,node,msg,data);
+	return XLSX.write(workbook, {bookType:"xlsx", type:'buffer'}); 
+}
+function ArrayToXLSXObject(RED,node,msg,data){
+	const workbook = XLSX.utils.book_new();
+	if(Array.isArray(data)) {
+		const  ws=XLSX.utils.aoa_to_sheet(data);
+		XLSX.utils.book_append_sheet(workbook, ws,"worksheet 1");
+		return workbook;
+	} 
+	for(const worksheet in data) {
+		const  ws=XLSX.utils.aoa_to_sheet(data[worksheet]);
+		XLSX.utils.book_append_sheet(workbook, ws, worksheet);
+	}
+	return workbook;
+}
 function ConfluenceToJSON(RED,node,msg,data){
@@ -149,2 +200,4 @@ if(!Buffer.isBuffer(data)) data=Buffer.from(data);
 	},
+	ArrayToXLSX:ArrayToXLSX,
+	ArrayToXLSXObject:ArrayToXLSXObject,
 	AVROToJSON: (RED,node,msg,data)=>node.avroTransformer.fromBuffer(data), // = {kind: 'CAT', name: 'Albert'}
@@ -244,2 +297,4 @@ ConfluenceToJSON: ConfluenceToJSON,
 	JSONToString: (RED,node,msg,data)=>JSON.stringify(data),
+	JSONToXLSX:JSONToXLSX,
+	JSONToXLSXObject:JSONToXLSXObject,
 	JSONToXML: (RED,node,msg,data)=>json2xmlParser.parse(data),
@@ -280,2 +335,7 @@ StringToJSON: (RED,node,msg,data)=>JSON.parse(data),
 	},
+	XLSXToArray:XLSXToArray,
+	XLSXObjectToArray:XLSXObjectToArray,
+	XLSXToJSON:XLSXToJSON,
+	XLSXObjectToJSON:XLSXObjectToJSON,
+	XLSXToXLSXObject:XLSXToXLSXObject,
 	XMLToJSON: (RED,node,msg,data)=>xmlParser.parse(data,XMLoptions,true),
@@ -301,3 +361,5 @@ invalidArray:(v=>!Array.isArray(v))
 		try{
-			if(is(node,"AVRO") || is(node,"Confluence")) {
+			if(is(node,"XLSX")||is(node,"XLSXObject") ) {
+				if(!XLSX) XLSX=require('xlsx');
+			} else if(is(node,"AVRO") || is(node,"Confluence")) {
 				if(avsc==null) avsc=require('avsc');
@@ -359,3 +421,3 @@ try{
 		const typeValidate="invalid"+node.actionSource;
-		node.invalidSourceType=typeValidate in functions?functions[typeValidate]:(()=>false);
+		node.invalidSourceType=typeValidate in functions &! ["XLSX","XLSXObject"].includes(node.actionTarget)?functions[typeValidate]:(()=>false);
 		try {
@@ -362,0 +424,0 @@ node.transform=functions[node.actionSource+"To"+node.actionTarget];

New alerts

Wildcard dependency

Quality

Package has a dependency with a floating version range. This can cause issues if the dependency publishes a new major version.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

1213599

increased by0.25%

Number of package files

61

increased by1.67%

Lines of code

2333

increased by1.52%

Number of lines in readme file

315

increased by2.94%

Worsened metrics

Dependency count

4

increased by33.33%

Number of medium quality alerts

4

increased by33.33%

Dependency changes

• + Addedxlsx@*

• + Addedadler-32@1.3.1(transitive)

• + Addedcfb@1.2.2(transitive)

• + Addedcodepage@1.15.0(transitive)

• + Addedcrc-32@1.2.2(transitive)

• + Addedfrac@1.1.2(transitive)

• + Addedssf@0.11.2(transitive)

• + Addedwmf@1.0.2(transitive)

• + Addedword@0.3.0(transitive)

• + Addedxlsx@0.18.5(transitive)