Hi,
I used node-red-contrib-spreadsheet-in nodes to extract information from an Excel file, but the flow only works with spreadsheets with less than eight thousand cells, and I need one that works with 20 thousand cells. Does anyone know what could be happening and how it could be fixed?
Here are two examples :
[{"id":"734b049e.0d0bcc","type":"inject","z":"31837efd.198bb2","name":"triger","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":260,"wires":[["cf75b5dc.c470f8"]]},{"id":"cf75b5dc.c470f8","type":"file in","z":"31837efd.198bb2","name":"read excel file","filename":"C:\Users\Aluno\Desktop\kauan\GRAFICOS_TEMPERATURA\SMO-35\teste_excel3.xlsx","format":"stream","chunk":false,"sendError":false,"encoding":"none","x":340,"y":260,"wires":[["d6a2d0a5.0c7ec","f96d9a8a.ad7e28"]]},{"id":"f96d9a8a.ad7e28","type":"book","z":"31837efd.198bb2","name":"EXCEL","x":560,"y":260,"wires":[["7dbe9897.cf9b48","565c7adf.a2e3c4"]]},{"id":"7dbe9897.cf9b48","type":"debug","z":"31837efd.198bb2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":570,"y":300,"wires":},{"id":"d6a2d0a5.0c7ec","type":"debug","z":"31837efd.198bb2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":310,"y":300,"wires":},{"id":"7a2819a2.ede038","type":"sheet-to-json","z":"31837efd.198bb2","name":"","raw":"false","range":"A1:A4067","header":"1","blankrows":false,"x":950,"y":260,"wires":[["4308dfdf.c1782","81b13a5a.eb33b8"]]},{"id":"4308dfdf.c1782","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":970,"y":300,"wires":},{"id":"565c7adf.a2e3c4","type":"sheet","z":"31837efd.198bb2","name":"Aba","sheetName":"Plan1","x":730,"y":260,"wires":[["7a2819a2.ede038","f0bdefcb.e1c2f"]]},{"id":"f0bdefcb.e1c2f","type":"debug","z":"31837efd.198bb2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":770,"y":300,"wires":},{"id":"61374d90.4be054","type":"inject","z":"31837efd.198bb2","name":"triger","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":440,"wires":[["fce02eda.d8a85"]]},{"id":"fce02eda.d8a85","type":"file in","z":"31837efd.198bb2","name":"read excel file","filename":"C:\Users\Aluno\Desktop\kauan\GRAFICOS_TEMPERATURA\SMO-35\teste_excel2.xlsx","format":"stream","chunk":false,"sendError":false,"encoding":"none","x":340,"y":440,"wires":[["bc1959ad.911f78","40b7c7bb.811ca8"]]},{"id":"40b7c7bb.811ca8","type":"book","z":"31837efd.198bb2","name":"EXCEL","x":540,"y":440,"wires":[["2a08372d.94f8a8","1cf9a00b.4d77c"]]},{"id":"2a08372d.94f8a8","type":"debug","z":"31837efd.198bb2","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":550,"y":480,"wires":},{"id":"bc1959ad.911f78","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":310,"y":480,"wires":},{"id":"1fa63b14.4e9725","type":"sheet-to-json","z":"31837efd.198bb2","name":"","raw":"false","range":"A1:B4067","header":"1","blankrows":false,"x":950,"y":440,"wires":[["66aaf4c.a4cda0c","a814dc16.1f632"]]},{"id":"66aaf4c.a4cda0c","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":950,"y":480,"wires":},{"id":"1cf9a00b.4d77c","type":"sheet","z":"31837efd.198bb2","name":"Aba","sheetName":"Plan1","x":710,"y":440,"wires":[["1fa63b14.4e9725","be0e9149.7d17f"]]},{"id":"be0e9149.7d17f","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":750,"y":480,"wires":},{"id":"a814dc16.1f632","type":"function","z":"31837efd.198bb2","name":"Creating array","func":"msg.datastored = ;\n\nfor(i=0;i<4067 ;i++){\n \n //############\n //reorgazizando a data de "05/10/17" para "2017, 10, 05"\n a = msg.payload[i][0];\n \n a1 = a.split('/'); // editando o ano\n a1[2] = '20' + a1[2];\n \n a1[1] = String(Number(a1[1]) - 1); // editando o mês; 0-11 CORRETO\n \n a = a1[2] + ', ' + a1[1] + ', ' + a1[0];\n \n \n //##########\n //reorganizando o horario de "01H30min0s" para "01, 30"\n b = msg.payload[i][1];\n d = b.replace('H',', ');\n e = d.replace('min0s','');\n \n \n //juntando a data com o horário\n c = a + ', ' + e; //forma --> "2017, 10, 05, 01, 30"\n \n //criando uma lista\n c1 = c.split(', '); //forma --> [2017, 10, 05, 01, 30]\n \n //convertendo para formato Date.\n g = new Date(c1[0], c1[1], c1[2], c1[3], c1[4]);\n //convertendo para formato Timestamp adequado para o Dashboard.\n f = g.getTime();\n \n msg.datastored[i] = f;\n\n}\n\nreturn msg;\n\n","outputs":1,"noerr":0,"x":1180,"y":440,"wires":[["3391ec4b.e3c274","9f16f209.88084","bbffab8a.486238"]]},{"id":"3391ec4b.e3c274","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":480,"wires":},{"id":"9f16f209.88084","type":"change","z":"31837efd.198bb2","name":"Set x axis dada","rules":[{"t":"set","p":"dada.x","pt":"flow","to":"datastored","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1360,"y":440,"wires":[]},{"id":"81b13a5a.eb33b8","type":"function","z":"31837efd.198bb2","name":"Creating array","func":"msg.ydada = ;\n\n\nfor(i=0;i<4067 ;i++){\n \n msg.ydada[i] = Number(msg.payload[i][0]);//{"x": msg.temp1[i], "y": msg.payload[i]}\n \n}\n\n\nreturn msg;","outputs":1,"noerr":0,"x":1180,"y":260,"wires":[["c12fd5bf.4ef558","edac335a.784d5","f14bc0bc.5e02d"]]},{"id":"c12fd5bf.4ef558","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":300,"wires":},{"id":"edac335a.784d5","type":"change","z":"31837efd.198bb2","name":"Set y axis dada","rules":[{"t":"set","p":"dada.y","pt":"flow","to":"ydada","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1360,"y":260,"wires":[]},{"id":"85f5bf53.66a9d","type":"function","z":"31837efd.198bb2","name":"Data editing for CHART","func":"msg.temp = ;\n\nfor(i=0;i<4067 ;i++){\n \n msg.temp[i] = {"x": msg.payload.x[i], "y": Number(msg.payload.y[i])};\n \n} \n\nmsg.payload = [{\n"series": ["SMO-35"],\n"data": [msg.temp],\n"labels": [""]\n}]\n\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":780,"wires":[["20da54e4.93f25c","ed7da7a9.fdf5c8"]]},{"id":"bc7dbda8.8b8d9","type":"inject","z":"31837efd.198bb2","name":"Import x,y dada","topic":"","payload":"dada","payloadType":"flow","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":780,"wires":[["85f5bf53.66a9d"]]},{"id":"ed7da7a9.fdf5c8","type":"ui_chart","z":"31837efd.198bb2","name":"CHART","group":"98448bdc.e0f088","order":0,"width":"0","height":"0","label":"Dados da Contenção","chartType":"line","legend":"true","xformat":"Y-M-D","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"50","removeOlder":"20","removeOlderPoints":"20000","removeOlderUnit":"1","cutout":0,"useOneColor":false,"colors":["#c6160d","#0707f8","#ff7f0e","#962e9e","#5fec67","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"outputs":1,"x":640,"y":780,"wires":[]},{"id":"20da54e4.93f25c","type":"debug","z":"31837efd.198bb2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":370,"y":820,"wires":},{"id":"8fb66484.942bb8","type":"comment","z":"31837efd.198bb2","name":"","info":"Esse flow converte os dados de um arquivo excel em dados legiveis ao nó CHART-graphic do DASHBOARD.\n\n/obs: os dois flows que extraem informações do arquivo excel (.xslx) são separados pois cada nó EXCEL consegue carregar em torno de 8000 mil células somente. ","x":660,"y":160,"wires":},{"id":"f14bc0bc.5e02d","type":"change","z":"31837efd.198bb2","name":"Set y axis dada","rules":[{"t":"set","p":"dada.SMO35.y","pt":"global","to":"ydada","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1360,"y":200,"wires":[]},{"id":"bbffab8a.486238","type":"change","z":"31837efd.198bb2","name":"Set x axis dada","rules":[{"t":"set","p":"dada.SMO35.x","pt":"global","to":"datastored","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1360,"y":380,"wires":[]},{"id":"98448bdc.e0f088","type":"ui_group","z":"","name":"Temperature 2018-2019","tab":"","disp":true,"width":"6","collapse":false}]
Att,
Kauan
